Scribd Logo
Upload

Welcome to Scribd!

  • 32 views

    Ip PPT (Viva)

    Uploaded by

    Harshita Rajput
    The document discusses various SQL aggregate functions and SQL join operations. It defines aggregate functions as functions that return a single value by calculating over multiple values from a column. The five main aggregate functions are listed as COUNT, SUM, AVG, MIN, and MAX. Various types of SQL joins are also described such as inner join, outer join, cross join, natural join, and equi join along with examples of each.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd

    Ip PPT (Viva)

    Uploaded by

    Harshita Rajput
    32 views11 pages
    The document discusses various SQL aggregate functions and SQL join operations. It defines aggregate functions as functions that return a single value by calculating over multiple values from a column. The five main aggregate functions are listed as COUNT, SUM, AVG, MIN, and MAX. Various types of SQL joins are also described such as inner join, outer join, cross join, natural join, and equi join along with examples of each.

    Original Title

    IP PPT (VIVA)

    Copyright

    © © All Rights Reserved

    Available Formats

    PPTX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document discusses various SQL aggregate functions and SQL join operations. It defines aggregate functions as functions that return a single value by calculating over multiple values from a column. The five main aggregate functions are listed as COUNT, SUM, AVG, MIN, and MAX. Various types of SQL joins are also described such as inner join, outer join, cross join, natural join, and equi join along with examples of each.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    32 views11 pages

    Ip PPT (Viva)

    Uploaded by

    Harshita Rajput
    The document discusses various SQL aggregate functions and SQL join operations. It defines aggregate functions as functions that return a single value by calculating over multiple values from a column. The five main aggregate functions are listed as COUNT, SUM, AVG, MIN, and MAX. Various types of SQL joins are also described such as inner join, outer join, cross join, natural join, and equi join along with examples of each.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    of 11

    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;

    You might also like