SQL CLAUSES


SQL Clauses | SQL Tutorial | Minigranth

SQL Clauses : Introduction

  • Clauses means “Conditions or Part or Section”. SQL clauses are used in SQL queries to extract or update or manipulate data in/from the database in order to generate minimal unique records.
  • It means, to reduce the size of output or to generate specific output, SQL clauses are used. There are six different SQL clauses which are used while generating SQL queries.
This image describes the classification of various sql clauses that can be used according to the requirement in sql.
SQL Clauses : Classification

SQL Clauses : The “FROM” Clause

  • The FROM clause is the most basic clause and is widely used in almost all the SQL queries. The from clause is used to fetch data in form of results from the database or tables.
Syntax : SELECT * FROM Table_Name;

For Example : Consider the table of student_details. FROM clause can be used to fetch all the data present in the student_details table. Below is the query.

Table : Student_details

This a sample table that can be used for sql FROM clause.
Sample Student_Details Table : FROM Clause

Query : SELECT * FROM student_details;

This image describes the output of FROM Clause.
Output

SQL Clauses : The WHERE Clause

  • The WHERE clause in SQL is an abstract form/type of FROM clause. The WHERE clause is added along with FROM clause in order to generate most filtered/summarized result.
Syntax : SELECT * FROM Table_Name WHERE “Condition”;

For example :  Consider the table of student_details. WHERE clause can be used to fetch specific data present in the student_details table. Below is the query.

Table : student_details

This a sample table that can be used for sql WHERE clause.
Sample Student_Details Table : WHERE Clause

Query : SELECT * FROM student_table WHERE Roll_No = 1;

This image describes the output of WHERE Clause.
Output

SQL Clauses : The DISTINCT Clause

  • The DISTINCT clause in SQL is used to fetch unique and non-redundant data/records every time the query is executed. It is used with SELECT clause.
Syntax : SELECT DISTINCT Column_Name1, Column_Name2,… FROM Table_Name;

For example : Consider the table of student_details. Distinct clause can be used to fetch unique data present in the student_ details table. Below is the query.

Table : student_details 
This a sample table that can be used for sql DISTINCT clause.
Sample Student_Details Table : DISTINCT Clause

Query : SELECT DISTINCT City FROM student_details;

This image describes the output of DISTINCT Clause.
Output

SQL Clauses : The GROUP BY Clause

  • The GROUP BY clause in SQL is used with SQL aggregate functions such as “Min”, “Max”, “Avg”, “Sum” and “Count”. The GROUP BY clause will return the summarized data into possible categories according to the fixed query.
Syntax : SELECT Column1, Column2,… FROM Table_Name GROUP By Column1;

For example : Consider the table of student_details. GROUP BY clause can be used to fetch summarized data present in the student_ details table on the basis of gender. Below is the query.

Table : student_details

This a sample table that can be used for sql GROUP BY clause.
Sample Student_Details Table : GROUP BY Clause

Query : SELECT Count(Roll_No), Gender FROM student_details GROUP By Gender;

This image describes the output of GROUP BY Clause.
Output

SQL Clauses : The HAVING Clause

  • HAVING clause in SQL is also used with SQL aggregate functions such as Min, Max, Avg, Sum and Count. Whenever a condition marked with HAVING clause is true, then only the query will return the result. Also, HAVING clause is used with GROUP BY clause always.
Syntax : SELECT Column1, Column2,… FROM Table_Name GROUP By “Condition” HAVING “Condition”;

For example : Consider the table of student_details. HAVING clause can be used to fetch data present in the student_ details table on the basis of location. Below is the query.

Table : student_details

This a sample table that can be used for sql HAVING clause.
Sample Student_Details Table : HAVING Clause

Query : SELECT Count(Roll_No), Location FROM student_details GROUP BY Location HAVING Count(Roll_No) >= 2;

This image describes the output of HAVING Clause.
Output

SQL Clauses : The ORDER BY Clause

  • ORDER BY clause is a simple keyword which can be used with SQL aggregate function or HAVING clause or GROUP BY clause. The main function of ORDER BY clause is to sort the result in either ascending or descending order.
  • It means, sorting the result requires ORDER BY clause. This can be done using either of the two keywords.
    • ASC – For sorting results in ascending order.
    • DESC – For sorting results in descending order.
  • If we do not place either “ASC or DESC” at the end of the query, by default it query will sort data in ascending order.
Syntax : SELECT Column1, Cloumn2,… FROM Table_Name ORDER BY Column_Name ; (Default/Ascending);

OR

Syntax : SELECT Column1, Cloumn2,… FROM Table_Name ORDER BY Column_Name ASC;  (For Ascending Order);

OR

Syntax : SELECT Column1, Cloumn2,… FROM Table_Name ORDER BY Column_Name DESC;  (For Descending Order);

Example-1 : Consider the table of student_details. To sort the data according to the location to which student belongs can be done by executing the below query.

Table : student_details

This a sample table that can be used for sql HAVING clause.
Sample Student_Details Table : ORDER By Clause

Query : SELECT * FROM student_details ORDER BY Location DESC;

This image describes the output of ORDER BY Clause.

NOTE : Sorting based upon alphabets of location i.e. “N” for Noida, “G” for Ghaziabad and “D” for Delhi. Similarly above sorting can be done in ascending order too using “ASC” in place of “DESC”.

 Example-2 : ORDER BY clause also supports SQL aggregate functions. Consider the same table used above and below is the sample query for ORDER BY with SQL aggregate functions.

Query : SELECT Count(Roll_No), Location From student_details GROUP BY Location ORDER BY Count(Roll_No) DESC;

This image describes the output of ORDER BY Clause.
Output

Previous Next