In a database

table, we can add

rules to a column
known as SQL
constrains. These
rules control the
data that can be
stored in a
Aggregate Functions
 GROUP BY CLAUSE
The GROUP BY CLAULSE in MYSQL is used to group rows that have
the same values in one or more specified columns in a summary row.
It is typically used in conjunction with aggregate functions to perform
calculations on each group.

Syntax:

SELECT column1,column2,….,columnN, aggregate_function(columnZ)

FROM table_name
WHERE condition
GROUP BY column1,column2,…, column_n;

Aggregate FUNCTION :- COUNT( ), SUM( ), AVG( ), MAX( ), MIN( ) , etc.

Parameters
column1, column2, ... column_n: It specifies the expressions or columns that are not
encapsulated within an aggregate function and must be included in the GROUP BY
clause.

aggregate_function: It specifies a function such as SUM, COUNT, MIN, MAX, or AVG

etc.

tables: It specifies the tables, from where you want to retrieve the records. There must
be at least one table listed in the FROM clause.

WHERE conditions: It is optional. It specifies the conditions that must be fulfilled for
the records to be selected.
 MySQL GROUP BY Clause with COUNT function
Consider a table named "officers" table, having the following records.

Now, let's count repetitive number of cities in the column address.

Execute the following query:

1. SELECT address, COUNT(*)

2. FROM officers
3. GROUP BY address;

Output:
 (i) MySQL GROUP BY Clause with SUM
function
Let's take a table "employees" table, having the following data.

Now, the following query will GROUP BY the example using the SUM function and return
the emp_name and total working hours of each employee.

Execute the following query:

1. SELECT emp_name, SUM(working_hours) AS "Total working hours"

2. FROM employees
3. GROUP BY emp_name;

Output:

(ii) MySQL GROUP BY Clause with MIN

function
The following example specifies the minimum working hours of the employees form the
table "employees".

Execute the following query:

1. SELECT emp_name, MIN(working_hours) AS "Minimum working hour"

2. FROM employees
3. GROUP BY emp_name;

Output:
 (iii) MySQL GROUP BY Clause with MAX
function
The following example specifies the maximum working hours of the employees form the
table "employees".

Execute the following query:

1. SELECT emp_name, MAX (working_hours) AS "Minimum working hour"

2. FROM employees
3. GROUP BY emp_name;

Output:
 (iv) MySQL GROUP BY Clause with
AVG function
The following example specifies the average working hours of the employees form the table
"employees".

Execute the following query:

1. SELECT emp_name, AVG(working_hours) AS "Average working hour"

2. FROM employees
3. GROUP BY emp_name;

Output:
 MySQL HAVING Clause
MySQL HAVING Clause is used with GROUP BY clause. It always returns the rows where
condition is TRUE.

Syntax:

1. SELECT column1, column2, ... column_n,

2. aggregate_function (column_z)
3. FROM tables
4. [WHERE conditions]
5. GROUP BY column1, column2, ... column_n
6. HAVING condition;

Parameters
aggregate_function: It specifies any one of the aggregate function such as SUM,
COUNT, MIN, MAX, or AVG.

column1, column2, ... column_n: It specifies the columns that are not encapsulated
within an aggregate function and must be included in the GROUP BY clause.

WHERE conditions: It is optional. It specifies the conditions for the records to be

selected.

HAVING condition: It is used to restrict the groups of returned rows. It shows only
those groups in result set whose conditions are TRUE.
 HAVING Clause with SUM function
Consider a table "employees" table having the following data.

Here, we use the SUM function with the HAVING Clause to return the emp_name and
sum of their working hours.

Execute the following query:

1. SELECT emp_name, SUM(working_hours) AS "Total working hours"

2. FROM employees
3. GROUP BY emp_name
4. HAVING SUM(working_hours) > 5;

Simply, it can also be used with COUNT, MIN, MAX and AVG functions
Normalization
3N
F
 SQL FOREIGN KEY Constraint
In SQL, the FOREIGN KEY constraint is used to create a relationship between two tables.
A foreign key is defined using the FOREIGN KEY and REFERENCES keywords.

syntax:-
CREATE TABLE table_name (
Column1 datatype,
Column2 datatype,
….,
CONSTRAINT foreign_key_name
FOREIGN KEY ( column_name)
REFERENCES
referenced_table(referenced_column)
);
 Displaying the constraints
To display the current constraints applied on the table, use the
following syntax.
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME = ‘table_name’;

Delete foreign key constraint

To delete a foreign key constraint using MYSQL server, use the
following
syntax.
ALTER TABLE table_name
DROP FOREIGN KEY foreign_key_name;

To delete a foreign key constraint using SQL server, use the following
syntax.

ALTER TABLE table_name

DROP CONSTRAINT constraint_name;
Check:

Add Foreign key to existing table-mysql

ALTER TABLE table_name
ADD FOREIGN KEY (column_name)
CONSTRAINT foreign_key_name
REFERENCES
referenced_table(referenced_column);

check:
The MYQL ON DELETE CASCADE Clause
The ON DELETE CASCADE is an option used with a foreign key
constraint in MYSQL. It ensures that when a row in the parent table is
deleted, all related rows in the child table are automatically deleted as
well. This constraint helps maintain referential integrity between tow
tables that are connected through a foreign key relationship.

If you do not specify this option, then you will get an error when you
attempt to delete rows in the parent table, saying that “Child rows
exist”.
 The MYSQL ON DELETE SET NULL Clause
The DELETE SET NULL is an option used with a foreign key constraint
in MYSQL. When a record in the parent table is deleted, the
corresponding record in the child table in the foreign key value field
will be set to NULL. This is another way to maintain reference
integrity, allowing the child records to exist but with the NULL
reference if the parent record is deleted.
The MYSQL ON DELETE RESTRICT / NO ACTION Clause
The ON DELETE RESTRIC or ON DELETE NO ACTION is an option
used with a foreign key constraint in MYSQL to prevent the deletion of
a record/row in the parent table if there are related rows in the child
table.
Actually, ON DELETE NO ACTION /RESTRICT is the default option
for foreign keys in MSQL server, so if we don’t specify this option at all,
then this is what is used.
 The MYSQL ON UPDATE CASCADE Clause
The ON UPDATE CASCADE is an option that we can apply to foreign
key constraints. It automatically updates foreign key values in child
tables when the corresponding primary key in the parent table is
updated.
 The MYSQL ON UPDATE SET NULL Clause
The ON UPDATE SET NULL is a foreign key clause in MSQL that
automatically sets the foreign key column in the child table to NULL
when the corresponding primary key in the parent table is updated.
Syntax:
CREATE TABLE Child_table (
Column1 datatype,
Column2 datatype,
….,
CONSTRAINT foreign_key_name
FOREIGN KEY ( column_name)
REFERENCES
Parent_table(parent_column)
ON UPDATE SET NULL
);

The MYSQL ON UPDATE RESTRICT / NO ACTION Clause

The ON UPDATE RESTRIC or ON UPDATE NO ACTION is an option
used with a foreign key constraint in MYSQL to prevent the updation of
a record/row in the parent table if there are related rows in the child
table.
Actually, ON UPDATE NO ACTION /RESTRICT is the default option
for foreign keys in MSQL server, so if we don’t specify this option at all,
then this is what is used.
Syntax:
CREATE TABLE Child_table (
Column1 datatype,
Column2 datatype,
….,
CONSTRAINT foreign_key_name
FOREIGN KEY ( column_name)
REFERENCES
Parent_table(parent_column)
ON UPDATE RESTRICT/ON UPDATE NO ACTION
);
 Composit key
Composite key is a combination of two or more columns in a table that
uniquely identify each row in the table. When these columns are
combined, then the uniqueness of a row is guaranteed, but when the
columns are taken individually, then it does not guarantee the
uniqueness.
It can also be called a primary key made by the combination of two or
more columns to uniquely identify every record in a table.
 SQL CROSS JOIN
The CROSS JOIN is used to generate a paired combination of each
row of the first table with each row of the second table. This joint type
is also known as cartesian join.
 Syntax:

SELECT Column1,Column2,…,ColumnN FROM table1 CROSS JOIN table2;

Or ,
SELECT Column1, Column2,..,ColumnN FROM table1, table2;
Or,
SELECT * FROM table1, table2;
 SQL INNER JOIN
The SQL INNER JOIN statement joins two tables based on a common
column and selects rows that have matching values in these columns.
 Syntax:

SELECT columns FROM table1

INNER JOIN table2

ON table1.column_name = table2.column_name;

Key Terms:
• Columns : specific columns we want to retrieve.
• table1 and table2 : tables being joined.
• Column_name : columns used for matching values.
 TYPES OF INNER JOIN
There are three main types of INNER JOIN based on how you write
them or use them in SQL.
[Link]-join (=) : This is the most common join type and is used to
combine data from two or more tables by matching values in a
common column using the equal(=) [Link] returns only the rows
that have matching values in both tables.
Syntax:

SELECT columns FROM table1

INNER JOIN table2

ON table1.column_name = table2.column_name;

Note: above examples are of Equi-join operation.

[Link]-Equi join (<>): This is a type of INNER JOIN where the condition
does not use the equal (=) operator, instead, it uses comparison
operators such as >,<,<=,>=,<>, or BETWEEN to combine rows from
two tables.
Syntax:

SELECT columns FROM table1

INNER JOIN table2

ON table1.column_name <> table2.column_name;

[Link] join : A Natural join automatically combines rows from two
or more tables based on columns with the same name and same type
of data.
It works just like Equi-join, but the difference is that it automatically
matches same-name columns, while Equi-join needs a condition using
the equal(=) operator.
Syntax:

SELECT columns FROM table1

NATURAL JOIN table2;

 SQL Outer Joins
An Outer Join returns all rows from one of the tables, and the matching
rows from the other table. If there is no match, the result will still
include the row, but show NULL for missing data.

There are three main types of Outer joins in SQL:

1. LEFT OUTER JOIN (LEFT JOIN)
2. RIGHT OUTER JOIN (RIGHT JOIN)
3. FULL OUTER JOIN (FULL JOIN)
 LEFT OUTER JOIN (LEFT JOIN)
A left join returns all rows from the left table, and the matching rows
from the right table. If there is no match, it shows NULL for columns
from the right table.

LEFT JOIN = All rows from the left table + matching from right

Syntax:

SELECT columns FROM left_table

LEFT JOIN right_table

ON left_table.column_name = right_table.column_name;
 RIGHT OUTER JOIN (RIGHT JOIN)
A Right join returns all rows from the right table, and the matching
rows from the left table. If there is no match, it shows NULL for columns
from the left table.
RIGHT JOIN = All rows from the right table + matching from left

Syntax:

SELECT columns FROM left_table

RIGHT JOIN right_table

ON left_table.column_name = right_table.column_name;
 FULL OUTER JOIN (FULL JOIN)
The FULL JOIN in SQL combines the result of both LEFT JOIN and
RIGHT JOIN. It returns all rows from both tables, including matched
rows and filling unmatched rows with NULL values in the columns from
the table that does not have a match.
FULL JOIN = LEFT JOIN + RIGHT JOIN

Syntax:

SELECT columns FROM left_table

FULL JOIN right_table

ON left_table.column_name = right_table.column_name;

Syntax:

SELECT columns FROM table1

FULL JOIN table2

ON table1.column_name = table2.column_name;

Note: MYSQL does not support FULL JOIN directly. To get the same
result, use a combination of LEFT JOIN and RIGHT JOIN with UNION.
 SELF JOIN
A SELF JOIN is used to join a table to itself as if the table were two
separate tables. To carry this out(to do it), aliases are used to give
temporary names to the same table so that SQL can treat them as two
different tables during the join.

A Self join in SQL is a type of Inner join where a table is joined with
itself to compare rows within the same table.

Syntax:

SELECT columns FROM table As alias1

JOIN table As alias2

ON alias1.column_name = alias2.column_name;

Syntax:

SELECT columns alias FROM table alias1

JOIN table alias2

ON alias1.column_name = alias2.column_name;

Syntax:

SELECT [Link] alias FROM table alias1

INNER JOIN table alias2

ON alias1.column_name = alias2.column_name;

Syntax:
OLD STYLE
SELECT columns alias FROM table alias1, table alias2

WHERE alias1.column_name = alias2.column_name;

SELFT JOIN = CROSS JOIN + CONDITION

empman e empman m
Student s1 Student s2
SELFT JOIN = CROSS JOIN + CONDITION
 NULL FUNCTIONS
NULL functions are provided to perform operations on NULL values that
are stored in our database tables.
A NULL value is a placeholder in database that represents missing,
unknown, or not applicable data in a table.
To handle these NULL values in a database table, SQL provides various
NULL functions. They are listed as follows:-
1. ISNULL()
2. IFNULL()
3. COALESCE()
4. NULLIF()
1. ISNULL() : The ISNULL function has different uses in SQL and
MYSQL.
In SQL server, ISNULL(value,replacement) function is used to
replace NULL values with a specified replacement. It takes only two
argument.

In MYSQL, ISNULL(column_name) function is used to test whether an

expression is null or not. If the expression is NULL, it returns TRUE(1),
else FALSE(0).
2. IFNULL(value,replacement) :This function is used to replace NULL
values with a specified replacement. This is available in MYSQL and
not in SQL server or oracle. It takes only two argument.

3. COALESCE(value1,value2,…valueN) : This function is used to return

the first occurred non-null value among its arguments. If all the
expressions or arguments are NULL, then this function will return
NULL. It can accept more than two arguments. It works in sql, mysql.
4. NULLIF() : This function accepts two arguments. If both expressions
are equal, it returns NULL. Otherwise, the first argument is returned. It
is universal,means it is supported by all major SQL servers .
ORDER BY : This keyword is used to sort the result-set in ascending(ASC)
or descending(DESC) order.

Syntax:

SELECT column1,column2,…..

FROM table_name

ORDER BY column_name

ASC|DESC; NOTE : UNION removes duplicate

values automatically.
 UNION ALL
A UNION ALL combines the results of two or more quires into one table
including all duplicates values.
The rules for UNION ALL are exactly the same as for UNION except it
includes all duplicate values.
UNION : Removes duplicate values;
UNION ALL : Includes all duplicate values;
THE END