Select Command in SQL SERVER 2012

DQL (Data Query Language)/DRL (Data Retrieval Language) has one command "SELECT" which is used to display a set of rows from one table/more than one table.

We can use SELECT command by using different options as shown below:

1.Version1 of select:
syntax:

SELECT * FROM <TABLE_NAME>

Example:

SELECT * FROM EMPLOYEE

By using the above syntax and example we can able to get all the columns in a table and and all the rows from table.

2.Version2 of Select:

syntax:

SELECT <COLUMNS_LIST> FROM <TABLE_NAME>

Examples:

1.SELECT ENAME FROM EMPLOYEE

2.SELECT ENAME,SAL FROM EMPLOYEE

3.SELECT EID,SAL FROM EMPLOYEE

4.SELECT EID,ENAME,SAL FROM EMPLOYEE

all the above are valid.

By using the above syntax or examples we can able to get specific columns from a table/more than one table with all the row values of a specific columns.

3.Version3 of Select:

syntax:

SELECT <COLUMNS_LIST>/ * FROM <TABLE_NAME>

WHERE <CONDITION>

Examples:

1.SELECT * FROM  EMPLOYEE
   WHERE EID=1000

2.SELECT * FROM  EMPLOYEE
   WHERE EID=1001

3.SELECT * FROM  EMPLOYEE
   WHERE ENAME='srinivas'

4.SELECT ENAME FROM  EMPLOYEE
   WHERE EID=1000

5.SELECT ENAME FROM  EMPLOYEE
   WHERE EID=1001

6.SELECT SAL FROM  EMPLOYEE
   WHERE EID=1002

By using the above syntax or examples we can able to get all the columns /few columns from table or from more than one table (using joins) but we can only the rows which are satisfied by the where clause.

4.Version 4 of Select:

syntax:

 SELECT <COLUMNS_LIST>/ * FROM <TABLE_NAME>
[WHERE <CONDITION>]
ORDER BY <COLUMN_NAME> [DESC]  

Examples:

1.SELECT * FROM EMP

   ORDER BY SAL

2.SELECT * FROM EMP

   ORDER BY SAL DESC,ENAME

3.SELECT * FROM EMP

   ORDER BY ENAME

4.SELECT * FROM EMP

   ORDER BY ENAME DESC

5.SELECT * FROM EMP

   WHERE JOB='CLERK'

   ORDER BY SAL DESC

6.SELECT * FROM EMP

   ORDER BY JOB,ENAME

7.SELECT * FROM EMP

   ORDER BY JOB,ENAME DESC

8.SELECT * FROM EMP

   WHERE DEPTNO=10

   ORDER BY SAL DESC

ORDER By clause is used to sort any one or more columns of a result set in ascending or descending order by default  ascending order will come. 

NOTE:if we specify two column names in order by clause the first column will come in ascending or descending order for the column if any equal values came more than once for them second column will perform sorting and give the order.

5.Version 5 of Select:

Syntax:

SELECT TOP N <COLUMNS_LIST>/ * FROM <TABLE_NAME>
[WHERE CLAUSE ]
[ORDER BY ]

it will return top n rows from result set

Examples:

1.SELECT TOP 2 * FROM EMP

2.SELECT TOP 3 * FROM EMP
ORDER BY SAL DESC

3.SELECT TOP 3 * FROM EMP
ORDER BY SAL

top clause is the last one which will execute along with the query once after all the clauses are executed and result set is prepared then top clause will be applied on result set and that will come as final result set.

watch this space for more versions of SELECT command and let me know if you have any doubts

Thanks

GSV