Oct 15, 2016

Interview Questions and answers - SQL Server Day 10

Question: Can we sort a column using a column alias in SQL Server?

Answer: Yes, we can sort a column using a column alias in SQL Server.
Example: Execute following query


CREATE TABLE #TEMP
(
       FNAME VARCHAR(100)
       ,LNAME VARCHAR(100)
       ,CREATEDDATE DATETIME,
)

INSERT INTO #TEMP
SELECT 'DILIP','SINGH',GETDATE()
UNION ALL
SELECT 'ANIL','SINGH',GETDATE()
UNION ALL
SELECT 'ASHISH','PRATAP', GETDATE()

-- In following query FNAME alias is FISRT_NAME. We can apply Order by caluse on alias as below
SELECT FNAME AS FIRST_NAME,LNAME AS LAST_NAME FROM #TEMP
ORDER BY FIRST_NAME ASC

DROP TABLE #TEMP


Question: What is difference between NULL value, zero and blank space in SQL Server? Is they are same?

Answer: NULL value is difference from zero and blank space, NULL value is unsigned, unavailable, unknown or not applicable while zero is number and blank space is character.

Question: If a table contains duplicates rows how can we eliminate from query result?

Answer: We can eliminate duplicates rows using DISTINCT keywords.
For Example: Execute following query.


CREATE TABLE #TEMP
(
       FNAME VARCHAR(100)
       ,LNAME VARCHAR(100)
       ,CREATEDDATE DATETIME,
)

INSERT INTO #TEMP
SELECT 'DILIP','SINGH',GETDATE()
UNION ALL
SELECT 'DILIP','SINGH',GETDATE()
UNION ALL
SELECT 'ASHISH','PRATAP', GETDATE()


SELECT DISTINCT FNAME LNAME FROM #TEMP

DROP TABLE #TEMP


Question: What is default sorting ordering of ORDER BY CLAUSE in SQL Server?

Answer:  Default sorting order of ORDER By clause is ascending.

Question: Is following query will run or not in SQL Server?


SELECT FNAME LNAME, COUNT(*) FROM #TEMP


Answer: No, I will give an error '#TEMP.FNAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.’

Right query is.


SELECT FNAME LNAME, COUNT(*) FROM #TEMP
GROUP BY FNAME