‘Select’ Command in SQL
SQL SELECT command is widely used in SQL database server. It is very important command through which we have to perform many task in SQL server database. Select command is used for select data from database table and result is stored in result table, called result-set.
Why and When Use SELECT Command in SQL Server:
Select command is basically used to select data from table into database. When we want to select data from table either only retrieves data or modifies data then we use this data query language command in SQL server.
Select command is used with many SQL clauses, SQL statements, SQL keyword, SQL aggregate function, SQL operator etc. Let’s see the brief demonstration of SQL SELECT command which is given as follows.
SQL SELECT command with FROM clause:
FROM clause used with Select command, from clause produces the tabular structure and this tabular structure referred to result-set of form clause.
Syntax: Using SELECT command
// SELECT SPECIFIED COLUMN_NAME FROM [TABLE_NAME]
SELECT [column_name1], [column_name2]………. [column_nameN] FROM [Table Name]
// SELECT ALL COLUMN_NAME FROM <TABLENAME>
// we have to use ‘*’ for select all column name of table
SELECT * FROM <TABLE_NAME>
Example: SQL SELECT command with FORM clause
----SELECT SPECIFIED COLUMN DATA FIELD FROM TABLE USERLOGININFO
SELECT [ID]FROM USERLOGININFO
---- SELECT ALL DATA FIELD FROM USERLOGININFO TABLE IN DATABASE ARUNSINGH
SELECT * FROM USERLOGININFO
SQL SELECT command with WHERE clause
“WHERE” clause is used for select data from table at specified condition. Where condition is true data field is retrieved.
Syntax: SELECT command with WHERE clause
SELECT [ALL] [or] [Specified column name] FROM <Table Name> WHERE <condition>
Example: SELECT command with WHERE clause
---- SELECT DATA FIELD IN TABLE ‘USERLOGININFO’ WHERE CONDITION IS TRUE
SELECT * FROM USERLOGININFO WHERE ID = 102
SQL SELECT command with TOP clause
TOP clause is used for retrieve number of N rows from table into SQL database. That is The TOP clause is used to specify the number of records to return. The TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance.
Syntax: SELECT command with TOP clause
SELECT TOP [NUMBER OF CLOUMN] | [COLUMN ANME] | [ALL] FROM <TABLE NAME>
Syntax: SELECT command with TOP and WHERE both clause
SELECT TOP [NUMBER OF CLOUMN] | [COLUMN ANME] | [ALL] FROM <TABLE NAME> WHERE <condition>
---- SELECT NUMBER OF DATA FIELD IN TABLE USERLOGININFO
SELECT TOP 2 NAME FROM USERLOGININFO
---- SELECT NUMBER OF DATA FIELD IN TABLE USERLOGININFO WHERE CONDITION IS TRUE
SELECT TOP 2 * FROM USERLOGININFO WHERE ID = 102
SELECT Command with ‘Order By’ keyword:
The ORDER BY keyword is used to sort the result-set by a specified column. The ORDER BY keyword sorts the records in ascending order by default. If you want to sort the records in a descending order, you can use the DESC keyword.
Syntax: SQL SELECT command with ORDER BY keyword
SELECT * FROM <TABLE_NAME> ORDER BY <COLUMN_NAME> <CONDITION ASCENDING OR DESCENDING>
---- SELECT USERLOGININFO DATA FILED IN DESCENDING ORDER
SELECT * FROM USERLOGININFO ORDER BY UID DESC
SELECT command with HAVING clause:
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions so resolved this problem SQL provide an alternation HAVING class in place of WHERE clause. With having clause you must be use GROUP BY statements.
SELECT [SPECIFIED_COLUMN_NAME] | [ALL], [AGGREGATE FUNCTION] FROM <TABLE NAME>GROUP BY [COLUMN_NAME] HAVING <CONDITION>
---- CALCULATE SUM OF SALARY GROUP BY USER NAME USING HAVING CLAUSE
SELECT NAME,SUM(SALARY) FROM STUDENT_MARKS GROUP BY NAME HAVING SUM(SALARY) <120000