Queries in SQL

Queries are a group of commands used to extract data from tables in a database using some SQL constructs. Queries are made mostly using SELECT statement in SQL with some other constructs like FROM, WHERE, GROUP BY, HAVING etc.

SELECT
FROM
WHERE
GROUP BY
ORDER BY
HAVING

SELECT command

SELECT command retrieves data from a table. This command also provides the query capability. This means that by the execution of SELECT statement, information currently in table will be shown on the screen.

Suppose you want to retrieve all the data sored in any table (for example STUDENTS table) then you have to type following command

SELECT * FROM STUDENTS;

On execution of this command you will see the details of all the students available in the STUDENTS table.

Note that SELECT is the single most powerful query command in SQL. Most of the query operations can be performed using this SELECT command. 

FROM in SELECT Query command:

FROM is the keyword in SQL, which must be present in every SELECT query. It is followed by a blank space  and then the name of the table being used as the source of the information.

The syntax is: 

SELECT  <COLUMN NAME(s) > FROM  < TABLE NAME >

The SELECT clause is followed by the column list, and FROM clause specifies the table from which the columns are to be extracted. To select all the columns in a table, Star(*) is used in place of the column list.

For example to display NAME, and PHONE Number of all the students from STUDENTS TABLE we created earlier in CREATE TABLE COMMAND for this we have to type following statement 

SELECT NAME, PHONE FROM STUDENTS; 

WHERE in SELECT Query command

We selected, all the rows of table STUDENTS so far. But suppose we want to see some specific rows that contain specific values. For that we need to place WHERE clause along with the select statement 

The syntax for this is :

SELECT < column names >  FROM  < table name >  WHERE  < COLUMN NAMES >  < OPERATOR > < VALUE >

Now here suppose if we want to see only those students are studying in class BCA from table STUDENTS type following statement

SELECT ROLL_NO, NAME, CLASS, PHONE FROM STUDENTS WHERE CLASS = 'BCA';

ORDER BY

The rows displayed from a query do not have any specific order either ascending or descending. But if you want them to be shown in ascending or descending order in a particular field, then you can control this order for the selected rows. This is done by adding the clause ORDER BY to the SELECT command.

Syntax is as follows

SELECT < COLUMN NAME >  FROM < TABLE NAME > ORDER BY < column to be ordered > [ < ASC/DESC > ];

Using the command ORDER BY will sort the rows as specified. For example the clause ASC sorts and displays in the ascending order. Even if you do not specify the ASC clause, still the sorting of the rows would be in the ascending order by default. In other words, SQL will automatically order the output rows from lowest to the highest order, unless you specify the clause DESC. The order DESC sorts and displays rows in descending order of the specified attributes or column.

GROUP BY 

Assume that you want to know the total salary offered to each Department which are groups in the students table then the clause GROUP BY can be used. The GROUP BY clause allows you to form groups based on the given conditions.

The syntax for using GROUP BY command is:

SELECT < column >, FUNCTION(< column >) FROM <TABLE NAME> GROUP BY <column>;

HAVING

You might not always want or need to see all the sub-groups in a table in a single report. Suppose you want to see the list of only those classes where the total fee is greater than 10000. In such a case, you cannot use the aggregate functions, namely SUM, MAX etc. in the WHERE clause. It means that you could not do something like following:

SELECT NAME, SUM(FEE) FROM STUDENTS WHERE SUM(FEE) > 10000 GROUP BY ROLL_NO;

This command would be rejected. To see the total fee over 10000, you have to use the HAVING clause. The HAVING clause defines criteria used to eliminate certain groups from the output, 

The correct command would be :

SELECT NAME, SUM(FEE) FROM STUDENTS GROUP BY ROLL_NO HAVING SUM(FEE) >10000;

HAVING and WHERE clauses work in a similar manner. The difference is that clause WHERE works on rows, while clause HAVING works on groups.