SQL Clauses

SQL clauses are the core components of SQL queries that define how data is retrieved, filtered, grouped, and organized from a database. These clauses work alongside SELECT, UPDATE, DELETE, and INSERT queries to refine results and ensure efficient data handling.

• Filter and retrieve specific records from a database.
• Group data based on certain attributes.
• Sort query results in ascending or descending order.
• Limit the number of records displayed in a query result.

SQL Clauses Example

Consider the below Students table, which is used as a reference for all the examples that are mentioned below. The Students table contains information about students, including their ID, name, fees, subject, age, and class. This table helps in performing various SQL operations such as filtering, grouping, and sorting records.

Example 1: Using the WHERE Clause

The WHERE clause is used to filter records that meet a specific condition. In this example, we retrieve students whose fees are less than 3500.

Query:

SELECT * FROM Students
WHERE stu_fees < 3500; 

Output:

Explanation:

• The query filters students whose stu_fees is less than 3500.
• Students with fees 4500 and 4000 are excluded from the result.
• The remaining students whose fees meet the condition are displayed.

Example 2: Using the GROUP BY Clause

The GROUP BY clause is used to group records with the same values in a column and perform aggregate functions such as SUM(), COUNT(), etc. This example calculates the total student fees per class.

Query:

SELECT stu_class, SUM(stu_fees) AS total_fees
FROM Students
GROUP BY stu_class;

Output:

Explanation:

• The query groups students based on their stu_class.
• It calculates the total fees per class using the SUM(stu_fees) function.
• For example, in class 10, the total fees sum up to 9000, and for class 9, it sums up to 7500.

Example 3: Using the ORDER BY Clause

The ORDER BY clause is used to sort query results in ascending or descending order. This example sorts students by their fees in ascending order.

Query:

SELECT * FROM Students
ORDER BY stu_fees ASC;

Output:

Explanation:

• The ORDER BY clause sorts the students based on their stu_fees in ascending order (lowest to highest).
• The student with the lowest fees (2000) appears first, while the student with the highest fees (4500) appears last.
• Sorting helps in analyzing data in a structured way, such as identifying the most or least expensive students.

Example 4: Using the HAVING Clause

The HAVING clause filters groups created by the GROUP BY clause, working on aggregate values (like sums or counts). It is essentially the WHERE clause for groups, applied after the aggregation is performed.

Query:

SELECT stu_subject, COUNT(*) AS num_students
FROM Students
GROUP BY stu_subject
HAVING COUNT(*) > 1;

Output:

Example 5: Using the LIMIT Clause

Used to limit the number of rows returned.

Query:

SELECT stu_name, stu_fees
FROM Students
ORDER BY stu_fees DESC
LIMIT 3;

Output:

Example 6: Using the FROM Clause

The FROM clause is mandatory in a SELECT statement to specify the table you are retrieving data from.

Query:

SELECT stu_name, stu_class, stu_age 
FROM Students_Table;

Output:

Example 7: Using the LIKE Operator

The LIKE operator is used within a WHERE clause to search for a specified pattern in a column. It is typically used with wildcards such as:

• "%" : matches zero or more characters
• "_" : matches a single character

SELECT stu_name, stu_subject FROM 
Students_Table WHERE stu_name LIKE '%Patil';

Output:

Example 8: Using the AND Operator

The AND logical operator combines multiple conditions in a WHERE clause, and a record is only included if all conditions are true.

Query:

SELECT stu_name, stu_class, stu_age FROM 
Students_Table WHERE stu_class = 9 AND stu_age = 16;

Output:

Types of SQL Clauses