Scribd Logo
Upload

Welcome to Scribd!

  • 5 views

    Table and Integrity Constraints

    Uploaded by

    rayin58259

    Copyright:

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

    Table and Integrity Constraints

    Uploaded by

    rayin58259
    5 views19 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    PPTX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

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

    Table and Integrity Constraints

    Uploaded by

    rayin58259

    Copyright:

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

    Tables & Integrity Constraints

    XII-IP
    Tables Integrity Constraints

    Relations Rules Conditions


    What is Grouping of Records?
    • Combining of all those records that have identical
    values in a particular field or a group of fields.
    • The GROUP BY Clause is used to make grouping of
    records.
    • The group by clause is used in Select statements to
    divide the table into groups

    • Ex. Select Sum(Salary) from employee;


    Select post, count(*) from employee
    Group by post;
    Group Functions
    • Group functions or aggregate functions are
    used to make group. These functions are also
    called multiple row functions.
    • Ex. AVG(), MAX(), MIN(),COUNT(),SUM()
    • Ex. Select Count(*) ‘Total Number of Employee’
    from employee;
    • Ex. Select Max(Salary) from employee;
    Types of Function
    • Single Row Functions : - work with a single row at a
    time and returns a result for every row of a table. e.g.
    Round(), Year() etc.

    • Multiple Row Functions :- work with data of multiple


    rows at a time and return aggregated value. e.g.
    Sum(), Max(). etc.
    Nested Groups
    • Nested Grouping :- GROUP BY Clause is also used to create
    group with in group, which is known as nested group.
    • Ex: Select DeptName, Count(empcode)
    from employee
    GROUP BY DeptName ;

    Role of DISTINCT and ALL


    • DISTINCT :- This option causes a group function to consider
    only distinct values of the argument expression. (No duplicate
    values)
    • ALL:- This option causes a group function to consider all
    values including all duplicates.
    HAVING Clause
    • The Having Clause places the conditions on groups in
    contrast to Where clause.

    Ex.- Select Job, Count(*) from employee


    Group By Job
    Having Count(*) < 5;

    • Difference between Where and Having Clause:


    1. Where clause places conditions on individual rows. While
    Having Clause places the conditions on groups.
    2. Where conditions cannot include aggregate functions,
    Having conditions can do so.
    Joins
    • Joins:- A Join is a query that combines rows from
    two or more tables. The function of combining data
    from multiple tables is called joining.

    • Ex: Select * from employee, department;

    Employee Department
    Type of Join
    There are different types of joins available in SQL:
    • INNER JOIN (Equi): returns rows when there is a match in both
    tables.
    • LEFT JOIN: returns all rows from the left table, even if there are
    no matches in the right table.
    • RIGHT JOIN: returns all rows from the right table, even if there are
    no matches in the left table.
    • FULL JOIN: returns rows when there is a match in one of the
    tables.
    • SELF JOIN: is used to join a table to itself as if the table were two
    tables, temporarily renaming at least one table in the SQL
    statement.
    • CARTESIAN JOIN: returns the Cartesian product of the sets of
    records from the two or more joined tables.
    Type of Join
    Procedure to join tables
    1. Create two table as given structure and insert values :
    Inner Join
     I NNER J OI N: Returns all rows when there is at least one match in
    BOTH tables. The I NNER J OI N keyword selects all rows from both
    tables as long as there is a match between the columns in both tables.

    SELECT column_name(s) SELECT Orders.OrderID,


    FROM table1 Customers.CustomerName, Orders.OrderDate
    INNER JOIN table2 FROM Orders
    ON table1.column_name=table2.colum INNER JOIN Customers
    n_name;
    ON Orders.CustomerID=Customers.CustomerID;

    Equi JOIN (INNER)


    returns rows when there is a match in both tables.
    Non-Inner Join
    Non-Equi JOIN
    returns rows when there is a match based on another condition
    other than equality in both tables.
    Left Join
     LEFT J OI N: Return all rows from the left table, and the matched rows
    from the right table. The LEFT J OI N keyword returns all rows from the
    left table (table1), with the matching rows in the right table (table2).
    The result is NULL in the right side when there is no match.
    SELECT column_name(s) SELECT Customers.CustomerName,
    FROM table1 Orders.OrderID
    LEFT JOIN table2 FROM Customers
    ON table1.column_name=table2 LEFT JOIN Orders
    .column_name;
    ON
    Customers.CustomerID=Orders.CustomerID
    ORDER BY Customers.CustomerName;
    Right Join
     RI GHT J OI N: Return all rows from the right table, and the matched
    rows from the left table. The RI GHT J OI N keyword returns all rows
    from the right table (table2), with the matching rows in the left table
    (table1). The result is NULL in the left side when there is no match.
    SELECT column_name(s) SELECT Orders.OrderID,
    FROM table1 Employees.FirstName
    RIGHT JOIN table2 FROM Orders
    ON table1.column_name=table2 RIGHT JOIN Employees
    .column_name;
    ON
    Orders.EmployeeID=Employees.EmployeeID
    ORDER BY Orders.OrderID;
    Full Join
     FULL J OI N: Return all rows when there is a match in ONE of the
    tables. The FULL OUTER J OI N keyword returns all rows from the left
    table (table1) and from the right table (table2).
    The FULL OUTER J OI N keyword combines the result of both LEFT and RI GHT
    joins.
    SELECT column_name(s) SELECT Customers.CustomerName,
    FROM table1 Orders.OrderID
    FULL OUTER JOIN table2 FROM Customers
    ON table1.column_name=table2 FULL OUTER JOIN Orders
    .column_name; ON Customers.CustomerID=Orders.CustomerID
    ORDER BY Customers.CustomerName;
    Natural Join
    Natural Join
    Natural join is a type of equi join which occurs implicitly by comparing all the same
    names columns in both tables. The join result have only one column for each pair of
    equally named columns.
    1. SELECT * FROM tblEmp NATURAL JOIN tblDept
    Equi Join
    Equi join is a special type of join in which we use only equality operator. Hence, when
    you make a query for join using equality operator then that join query comes under Equi
    join.
    Equi Join Example
    1. SELECT * FROM tblEmp JOIN tblDept
    2. ON tblEmp.DeptID = tblDept.DeptID;
    3. --Using Clause is not supported by SQL Server
    4. --Oracle and MySQL Query
    5. SELECT * FROM tblEmp INNER JOIN tblDept USING(DeptID)
    Cross Join
    CARTESIAN JOIN: returns the Cartesian product of the sets
    of records from the two or more joined tables.
    Role of ON and USING in Join Clause
    • ON:- On requires a complete join condition and this
    sub-clause produces equi-join.

    • USING:- Using clause requires just name of the join


    fields. Using sub-clause produces natural-join.

    You might also like