Introduction to SELECT statement in SQL Server

Introduction to SELECT statement:

It is a command in DQL and is used to fetch the data from database.

Below are the most commonly used keywords that we use while fetching the data:

  SEELCT

  FROM

  WHERE

  ORDER BY

We will have mainly two parts in SELECT statement:

  List of columns

  List of tables from which you want to retrieve these columns

Employee table:

Attributes/ Columns

EmployeeID

EmployeeName

DepartmentID

City

Country

SELECT …....... FROM Clause:

If you want to select:

all the columns from a table, use * in SELECT statement

Example:

            SELECT * FROM dbo.Employee – Here Employee is the table name

few columns from a table:

            SELECT

                        EmployeeID

                        ,EmployeeName

            FROM

                        dbo.Employee

           

WHERE.....

This is used for filtering out the data from your table and is useful when you want only part of the data.

Example:

If we need only the employees who are staying in India then we can get by using the below query

SELECT

       *

FROM

       dbo.Employee

WHERE

       Country='India'

If we don't want to see any employees who are from India then we can write:

SELECT

       *

FROM

       dbo.Employee

WHERE

       Country<>'India'

Order By:

            Used for sorting the data in ascending or descending format.

SQL is providing ASC and DESC for this purpose. ASC is the default sorting means id we don't mention anything it will order the data in ascending order.

SELECT

       *

FROM

       dbo.Employee

ORDER BY EmployeeName

If we see the result, data is sorted based on EmployeeName in ascending order as we didn't mention any ordering in Order BY clause.

SELECT

       *

FROM

       dbo.Employee

ORDER BY EmployeeName DESC

In the above result set, data sorted by using employee name in descending order.

SQL Server - DDL, DML, DQL, DCL and TCL

Today we are going to learn different categories of languages available in SQL:

We have four categories of languages in SQL:

DDL – Data Definition Language:

It is used to modify the structure of the table

      CREATE, ALTER, DROP, TRUNCATE

There are too many posts where we are seeing whether TRUNCATE is DDL/DML command.

Its DDL command because

a. It internally performs DROP and CREATE of the table

b. If you want to TRUNCATE the table you require ALTER permissions not only Write permissions

c. As it is DDL command, it will reset IDENTITY property of the table

DML – Data Manipulation Language:

It is used to manipulate the data available in table

      INSERT, UPDATE, DELETE

DQL – Data Query Language:

It is used to fetch the data from table

     SELECT

DCL – Data Control Language:

It is used to create roles, permissions, and referential integrity and also to control access to database by securing it

     GRANT, REVOKE

TCL – Transaction Control Language:

It is used to control/manage different transactions performed on a table

     COMMIT, ROLLBACK