Which Clause is Similar to Having in MySQL

About MySQL

MySQL is an Open-Source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL) to manage RDBs. Initially developed by MySQL AB in 1994, it has been adopted by over 5,000 companies, including Uber, Netflix, Pinterest, Amazon, Airbnb, and Twitter.

Features of MySQL

Easy to access:
Since MySQL is open-source, any person can download, use, and modify the software. It can be easy to use and provided free of cost. MySQL's source code can be accessed for study and customization according to needs. It makes use of the GPL, or GNU General Public License, which provides restrictions for what is permissible and not permissible with the program.
Rapid and Trustworthy:
MySQL effectively saves information in memory to ensure consistency and prevent duplication. MySQL enables rapid access to and manipulation of data.
Adaptable:
The capacity of a system to work well with large or small groups of machines and other types of data is referred to as scalability. The MySQL server was created for handling big databases.
Data Formats:
Numerous data types are supported, including float (FLOAT), double (DOUBLE), character (CHAR), variable character (VARCHAR), text, date, time, datetime, timestamp, year, signed and unsigned integers, and many more.
Character Groups:
It is compatible with other character sets, such as German, Ujis, Latin 1 (cp1252 character encoding), other Unicode character sets, and so forth.
Be protected:
As a result of its customizable password system that verifies the password according to the host before allowing access to the database, it offers a safe interface. When the password is being connected to the server, it is encrypted.
Support with big databases:
Large databases, with up to 5,000,000,000 rows, 150,000-200,000 tables, and 40-50 million records, are supported by this software.

SQL Clause

SQL stands for Structured Query Language. A clause is a portion or a Keyword in a SQL statement. It is used to filter, sort, or manipulate data from a database. A SQL statement can have one or more clauses. Some clauses are

SELECT Clause:
This Clause is used to retrieve data from any particular column in a table from a database.
FROM Clause:
This Clause is used to retrieve data from any particular table from a database.
WHERE Clause:
This Clause is used to filter data from any row from the database depending on the condition.
GROUP BY Clause:
This Clause is used to group rows that have the same value.
HAVING Clause:
This Clause is used to filter the results of aggregate functions applied to grouped data.
ORDER BY Clause:
This Clause is used to sort data in ascending or descending.
LIMIT Clause:
This Clause is used to merge two or more tables.
JOIN Clause:
This Clause is used to join two or more tables depending on the condition given.
UPDATE Clause:
This Clause is used to modify any record or values in a table.
DELETE Clause:
This Clause is used to delete rows from a table.

Example

Student_ID	Student_Name	Student_Fees	Student_Subject	Student_Class
1	Ritama Sen	3500	Maths	5
2	Sneha Roy	6500	Chemistry	10
3	Sucharita Das	2300	Social Science	3
4	SK Shahin	4500	Maths	6
5	Rupam Chatterjee	3000	Geography	4
6	Sharbani Dey	2500	History	3
7	Mayuri Pandit	4500	Social Science	5

Let us execute the following command.

Output:

Student_ID	Student_Name	Student_Fees	Student_Subject	Student_Class
3	Sucharita Das	2300	Social Science	3
5	Rupam Chatterjee	3000	Geography	4
6	Sharbani Dey	2500	History	3

See the table that Student_Fees are all below 3500.

Importance of SQL Clause

SQL Clauses play a vital role in data retrieving.

In the case of data retrieval, SQL Clause SELECT helps us to retrieve data from any specific column.
While filtering data, the WHERE Clause is helpful.
If someone wishes to organize data in ascending or descending order, the ORDER BY Clause is used.
By using the SQL Clause JOIN, we can join two or more tables depending on the condition and the foreign key.
When it comes to the GROUP BY Clause, it is very useful as it helps to group the same data.
Each SQL Cluse has its importance.

GROUP BY Clause

The group by Clause is used to group the same or identical data.

Features:

The SELECT statement must be there with the GROUP BY clause.
GROUP BY Clause should be placed after the WHERE Clause and before the ORDER BY Clause and HAVING Clause.
The condition must be placed with the HAVING Clause.

Syntax:

Following is the basic Syntax:

SELECT col_1, col_2, col_3
FROM tableName
WHERE put_the_condition
GROUP BY col_1, col_3;

Example:

SELECT stu_name, AVG (marks)
FROM Student
WHERE Stu_class > 10
GROUP BY Stu_Dept;

Following is the extended Syntax:

SELECT col_1, col_2, col_3
FROM tableName
WHERE put_the_condition
GROUP BY col_1, col_3
HAVING any_aggregate_function(col_5) put_the_condition
ORDER BY col_2 ASC;

Example:

SELECT stu_name, AVG (marks)
FROM Student
WHERE Stu_class > 10
GROUP BY Stu_Dept
HAVING AVG (marks) > 85
ORDER BY AVG (marks) DESC;

Explain WHERE Clause

In SQL, WHERE is used to filter data from the data set. Operators are also used in WHERE conditions. Following is the list of operators.

Operators	Description with example
>	Greater Than Example- SELECT * FROM student WHERE stu_marks > 85
>=	Greater Than or Equal to Example- SELECT * FROM student WHERE stu_marks >= 85
<	Less Than Example- SELECT * FROM employee WHERE salary <10000
<=	Less Than or Equal to Example- SELECT * FROM employee WHERE salary <=10000
=	Equal to Example- SELECT stu_name FROM student WHERE stu_marks=100
<>	Not equal to Example- SELECT * FROM sales where amount <> 500
BETWEEN	Predefined Range Example- SELECT stu_name FROM student WHERE stu_marks BETWEEN 80 and 90
LIKE	Matching pattern Example- SELECT * FROM employee WHERE emp_name LIKE '%S%'
IN	Check multiple values Example- SELECT emp_name FROM employee WHERE salary IN (5000,10000)

Syntax:

SELECT col_1, col_2 
FROM table_name
WHERE condition;

Explain HAVING Clause

HAVING Clause is used in conjunction with the GROUP BY Clause.

Features:

The condition given after HAVING is used to filter data.
ORDER BY Clause is placed after the HAVING Clause.
In the case of column operation, HAVING is used.
HAVING is used after the GROUP BY Clause.

Syntax:

SELECT col_1, (any_aggregate_function) col_2, col_3
FROM tableName
WHERE put_the_condition
GROUP BY col_1, col_3
HAVING any_aggregate_function(col_5) put_the_condition
ORDER BY (any_aggregate_function) col_2 ASC;

Example:

SELECT stu_name, AVG (marks)
FROM Student
WHERE Stu_class > 10
GROUP BY Stu_Dept
HAVING AVG (marks) > 85
ORDER BY AVG (marks) DESC;

Implement aggerate function with HAVING Clause

Let us create a table.

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_category VARCHAR (50),
    sales_amount DECIMAL (10, 2),
    quantity_sold INT
);
INSERT INTO sales VALUES
(1, 'Electronics', 500.50, 10),
(2, 'Clothing', 300.75, 15),
(3, 'Electronics', 700.25, 8),
(4, 'Clothing', 200.00, 20),
(5, 'Electronics', 450.50, 12);

COUNT Function:

SELECT product_category, COUNT (*) AS TotalSales
FROM Sales
GROUP BY product_category
HAVING TotalSales >2;

SUN Function:

SELECT product_category, SUM (sales_amount) AS TotalSalesAmount
FROM Sales
GROUP BY product_category
HAVING TotalSalesAmount >1000;

AVG Function:

SELECT product_category, AVG (quantity_sold) AS AvgQuantitySold
FROM Sales
GROUP BY product_category
HAVING AvgQuantitySold >10;

Distinguish between HAVING and WHERE Clause

WHERE Clause	HAVING Clause
The WHERE Clause specifies the criteria that individual records must meet to be selected by a query. It can be used without the GROUP BY Clause.	The HAVING clause cannot be used without the GROUP BY clause.
The WHERE Clause is used to select rows before grouping.	The HAVING clause selects rows after grouping.
The WHERE Clause is not used with aggregate functions.	The HAVING clause can contain aggregate functions.
The WHERE Clause is used with the SELECT condition.	The HAVING Clause is used to impose conditions on the GROUP Function and is used after the GROUP BY clause in the query.