Select DML (Data Manipulation Language) statement in SQL i.e. It retrieves the records from the database and shows to the user.

In order to retrieve records from the table we query the below-mentioned query:

Syntax: Select * from [db].[Schema].[Table ]

If we are already in selected database and schema we can directly run the below query.

SELECT * FROM Student

SQL server returns the query result.

Here (*) means that return all the column records of the specified table. In order to retrieve the selective columns records from the DB, we generally specify those records in the query. Here we are only retrieving Name and Email from the Student table.

For performance benefit, we should mention column names rather than * in a Select query.

SELECT NAME,EMAIL FROM Student

Different criteria of Select

• Returning distinct rows from the table based on a column which may be duplicate and you want them to appear once. In above table, we have duplicate Course in our table so in order to return only the distinct Course we will just write a distinct query.

What if we use distinct along with another column as shown below:

SELECT DISTINCT COURSE, NAME FROM STUDENT

When we use distinct with another column we are specifying SQL server that value should be distinct across both the column selected. In order to demonstrate the same let’s take an example, we have following records in our Student table.

So now if we try to run the distinct query along with Name it will return only one row for Vipin and MCA.

SELECT DISTINCT COURSE, NAME FROM STUDENT

• Where Clause filtering
  

In the majority of cases, we use Where clause which helps us to filter the records as per our condition. We have following new records in order Student table and now I want to query the Student table show me all the records where Course is BTECH.

SELECT * FROM Student WHERE Course=‘BTECH’

Result:

There are a variety of wildcard operators that we can use in our where clause in order to get desired output.

AND clause

And clause is used along with where clause where we want to filter the records on where and other condition. I want to retrieve record where course= BTECH and student name is JAMES

SELECT * FROM Student WHERE Course=‘BTECH’ and Name=‘JAMES’

Order by

Order by clause is used to sort the result of the query based on ascending or descending of a column. Now we want all the students in the table with email in ascending order of the alphabet.

SELECT * FROM STUDENT ORDER BY EMAIL ASC

If we don’t specify the sorting order by default it’s ascending order.

Top

Top keyword helps us to retrieve top records from the table this keyword is quite helpful.

SELECT TOP(4)* FROM STUDENT

Top with Percent

I actually never used Top expression percent. But when I found that there is [percent] parameter present in count function.  Let’s explore the same.

The percent indicates that the query should return only the first expression top(100) percent of rows from the result set. Fractional values returned will be rounded up.

The person table contains 3005 records. Now if try to execute top with percent it will perform percent on the number of rows present in the table.