SQL SERVER – How To Retrieve the Records from a Table

Select is the Data Retrieval Statement which can be used to retrieve rows from the database and enables the selection of one or many rows or columns from one or many tables or views.

We will see the syntax of the Simple Select statement :

Syntax :

<SELECT statement> ::=

<query_expression>                                                                         [ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }   [ ,...n ] ]                                                                          [ COMPUTE   { { AVG | COUNT | MAX | MIN | SUM } ( expression ) } [ ,...n ]                                                                                          [ BY expression [ ,...n ] ]     ]                                                           [ <FOR Clause>]                                                                              [ OPTION ( <query_hint> [ ,...n ] ) ]                               

<query_expression> ::=                                                  { <query_specification> | (<query_expression> ) }                                                 [ { UNION [ ALL ] | EXCEPT | INTERSECT } <query_specification> | ( <query_expression> ) [...n ] ]

<query_specification> ::=                                                SELECT [ ALL |DISTINCT ] [TOP ( expression ) [PERCENT] [ WITH TIES ] ]                                                             <select_list>                                                                 [INTO  new_table ]                                                        [ FROM { <table_source> } [ ,...n ] ]                                                                     [ WHERE <search_condition> ]                                                      [<GROUP BY> ]                                                             [ HAVING < search_condition > ]

 

[ ALL | DISTINCT ]

        Specifies whether to retrieve All or Distinct records from the table

TOP ( expression ) [ PERCENT ]

Specifies the number or percent of rows that will be updated. expression can be either a number or a percent of the rows.

< select_list >

        Specifies the columns of the table that we want to retrieve

[ INTO new_table ] 

        Specifies the new table name into which the records will be inserted

[ FROM { <table_source> } [ ,...n ] ]

        Specifies the table name or view name from which we want to retrieve the records

[ WHERE <search_condition> ] 

          Specifies the search condition for the rows returned by the query.

[ <GROUP BY> ] 

          Groups a selected set of rows into a set of summary rows by the values of one or more columns or expressions

[ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }   [ ,...n ] ] 

          Specifies the sort order used on columns returned in a SELECT statement

[ HAVING < search_condition > ]

          Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement

Example :

Let us see the example for retrieving the distinct Employees whose salary is greater than 50000 in the Employee Table

Select Distinct Empid,Salary From Employee                                                         
Where MgrId is Not NULL                                          Group by  Empid,Salary                             
Having Salary > 50000