NAME :- HARSHITA
CLASS :- 12 –
SCIENCE
ROLL NO :- 30
SUBJECT :- IP
AGGREGATE FUNCTIONS
An aggregate function in SQL returns one value after
calculating multiple values of a column. We often use
aggregate functions with the GROUP BY and HAVING
clauses of the SELECT statement.
There are 5 types of SQL aggregate functions:
• Count()
• Sum()
• Avg()
• Min()
• Max()
• WHERE CLAUSE is • HAVING CLAUSE is
used to filter the records used to filter the records
from the table or used from the groups based on
while joining more than the given condition in the
one table. Only those HAVING Clause. Those
records will be extracted groups who will satisfy
who are satisfying the the given condition will
specified condition in appear in the final result.
WHERE clause. It can be HAVING Clause can only
used with SELECT, be used
UPDATE, DELETE with SELECT statement.
statements.
Eg of where clause:- Eg of having clause:-
Let us consider table Let us consider
“Student” . “Student” table.
SELECT Age,
SELECT S_Name, Age
COUNT(Roll_No) AS
FROM Student WHERE No_of_Students FROM
Age >=18; Student GROUP BY Age
HAVING
COUNT(Roll_No) > 1;
EXAMPLES OF ALL TYPES OF
AGGREGATE FUNCTIONS
Let us consider table EMPOLYEE
1. SUM :-
SELECT SUM(SALARY) FROM EMPOLYEE;
2. MIN :-
SELECT MIN(SALARY) FROM EMPOLYEE;
3. MAX :-
SELECT MAX(SALARY) FROM EMPOLYEE;
4. AVG :-
SELECT AVG(SALARY) FROM EMPOLYEE;
5. COUNT :-
OPERATIONS ON RELATIONS
A set of operations takes one or two relations as
input and produces a new relation as the output.
It describes all the operations you can perform
on a table.
There are three types of operations
Union (U)
Intersection
Set Difference/Minus (-)
TYPES OF OPERATIONS
UNION :- The UNION operator is used to combine the
data from the result of two or more SELECT command
queries into a single distinct result set. This operator
removes any duplicates present in the results being
combined.
Eg:-
SELECT * FROM STUDENT
UNION
SELECT * FROM EMPOLYEE;
Intersection :- It finds the tuples that are common
to both the relations.
Eg :- SELECT * FROM STUDENT
INTERSECT
SELECT * FROM EMPOLYEE;
Set Minus :- It allows finding tuples that are in one
relation but not in another.
Eg :- SELECT * FROM STUDENT
MINUS
SELECT * FROM EMPOLYEE;
SQL JOINS
A JOIN clause is used to combine rows from two or
more tables, based on a related column between them.
Types of sql joins are :-
o Cross join
o Equi join
o Inner join
o Right outer join
o Self join
o Non-equi join
o Natural join
TYPES OF SQL JOIN
Cross Join :- The CROSS JOIN keyword returns all records
from both tables (table1 and table2).
Eg:- SELECT * FROM STUD CROSS JOIN GAMES;
Equi Join :- It uses the equal to sign as a comparison operator
for defining a relationship between two tables on the basis of primary
and secondary key.
Eg:- SELECT A.ROLLNO, A.NAME,
B.FEE FROM STUDENT A, FEES B
WHERE A.ROLLNO = B.ROLLNO;
Inner Join :- The INNER JOIN keyword selects records that have
matching values in both tables.
Eg:- SELECT A.ROLLNO, A.NAME, B.FEE
FROM STUDENT A, FEES B
WHERE A.ROLLNO = B.ROLLNO
ORDER BY A.ROLLNO;
Right Outer Join :- The RIGHT JOIN keyword returns all records from
the right table (table2), and the matching records from the left table
(table1). The result is 0 records from the left side, if there is no match.
Eg:- SELECT A.ROLLNO, A.NAME, B.FEE
FROM STUDENT A, FEES B
WHERE A.ROLLNO = B.ROLLNO
ORDER BY B.FEE DESC;
Natural Join:- Natural Join in SQL refers to joining two or more tables
based on common columns, which have the same name and data type.
Eg:- SELECT * FROM STUDENT NATURAL JOIN FEES;
