DQL COMMAND– SELECT command
One of the most important tasks when working with SQL is to generate Queries and retrieve data. A Query is a command given to get a desired result from the database table. The SELECT command is used to query or retrieve data from a table in the database. It is used to retrieve a subset of records from one or more tables. The SELECT command can be used in various forms:
Syntax of SELECT command :
· Table-name is the name of the table from which the information is retrieved.
· Column-list includes one or more columns from which data is retrieved.
For example to view only admission number and name of students from the Student table the command is given as follows:\
If the Student table has the following data:
SELECT Admno, Name FROM Student;
The above SELECT command will display the following data:
To view all the fields and rows of the table the SELECT command can be given as SELECT * FROM STUDENT;
The DISTINCT keyword is used along with the SELECT command to eliminate duplicate rows in the table. This helps to eliminate redundant data. For Example:
SELECT DISTINCT Place FROM Student;
Will display the following data as follows :
SELECT * FROM Student;
In the above output you can see, there would be no duplicate rows in the place field. When the keyword DISTINCT is used, only one NULL value is returned, even if more NULL values occur.
The ALL keyword retains duplicate rows. It will display every row of the table without considering duplicate entries.
SELECT ALL Place FROM Student;
The above command will display all values of place field from every row of the table without considering the duplicate entries.
The WHERE clause in the SELECT command specifies the criteria for getting the desired result. The general form of SELECT command with WHERE Clause is:
SELECT <column-name>[,<column-name>,….] FROM <table-name>WHERE condition>;
For example to display the students admission number and name of only those students who belong to Chennai, the SELECT command is used in the following way :
SELECT Admno, Name, Place FROM Student WHERE Place =”Chennai”;
SELECT Admno, Name, Age FROM Student WHERE Age >= 18;
The relational operators like =, <, <=, >, >=, <> can be used to compare two values in the SELECT command used with WHERE clause. The logical operaors OR, AND and NOT can also be used to connect search conditions in the WHERE clause. For example :
SELECT Admno, Name, Age, Place FROM Student WHERE (Age>=18 AND Place = "Delhi");
The SELECT command can also be used in the following ways:
SELECT Admno, Name, Age, Place FROM Student WHERE (Age>=18 OR Place ="Delhi"); SELECT Admno, Name, Place FROM Student WHERE (NOT Place ="Delhi");
The BETWEEN keyword defines a range of values the record must fall into to make the condition true. The range may include an upper value and a lower value between which the criteria must fall into.
SELECT Admno, Name, Age, Gender FROM Student WHERE Age BETWEEN 18 AND 19;
The NOT BETWEEN is reverse of the BETWEEN operator where the records not satisfying the condition are displayed.
SELECT Admno, Name, Age FROM Student WHERE Age NOT BETWEEN 18 AND 19;
The IN keyword is used to specify a list of values which must be matched with the record values. In other words it is used to compare a column with more than one value. It is similar to an OR condition.
For example :
SELECT Admno, Name, Place FROM Student WHERE Place IN (“Chennai”, “Delhi”);
The NOT IN keyword displays only those records that do not match in the list.
SELECT Admno, Name, Place FROM Student WHERE Place NOT IN (“Chennai”, “Delhi”);
will display students only from places other than “Chennai” and “Delhi”.
NULL Value :
The NULL value in a field can be searched in a table using the IS NULL in the WHERE clause. For example to list all the students whose Age contains no value, the command is used as:
SELECT * FROM Student WHERE Age IS NULL;
The ORDER BY clause in SQL is used to sort the data in either ascending or descending based on one or more columns.
1. By default ORDER BY sorts the data in ascending order.
2. We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.
The ORDER BY clause is used as :
SELECT <column-name>[,<column-name>,….] FROM <table-name>ORDER
BY <column1>,<column2>,…ASC| DESC ;
For example :
To display the students in alphabetical order of their names, the command is used as
SELECT * FROM Student ORDER BY Name;
The above student table is arranged as follows :
The WHERE clause is used to filter the records. It helps to extract only those records which satisfy a given condition. For example in the student table, to display the list of students of age18 and above in alphabetical order of their names, the command is given as below:
SELECT * FROM Student WHERE Age>=18 ORDER BY Name;
To display the list of students in the descending order of names of those students of age 18 and above the command is given as :
SELECT * FROM Student WHERE Age>=18 ORDER BY Name DESC;
(vii) GROUP BY clause
The GROUP BY clause is used with the SELECT statement to group the students on rows or columns having identical values or divide the table in to groups. For example to know the number of male students or female students of a class, the GROUP BY clause may be used. It is mostly used in conjunction with aggregate functions to produce summary reports from the database.
The syntax for the GROUP BY clause is
SELECT <column-names> FROM <table-name> GROUP BY <column-name>HAVING condition];
To apply the above command on the student table :
SELECT Gender FROM Student GROUP BY Gender;
The following command will give the below given result:
The point to be noted is that only two results have been returned. This is because we only have two gender types ‘Male’ and ‘Female’. The GROUP BY clause grouped all the ‘M’ students together and returned only a single row for it. It did the same with the ‘F’ students.
For example to count the number of male and female students in the student table, the following command is given :
SELECT Gender, count(*) FROM Student GROUP BY Gender;
The GROUP BY applies the aggregate functions independently to a series of groups that are defined by having a field value in common. The output of the above SELECT statement gives a count of the number of Male and Female students.
The HAVING clause can be used along with GROUP BY clause in the SELECT statement to place condition on groups and can include aggregate functions on them. For example to count the number of Male and Female students belonging to Chennai .
SELECT Gender , count(*) FROM Student GROUP BY Gender HAVING Place = ‘Chennai’;
The above output shows the number of Male and Female students in Chennai from the table student.