Scribd Logo
Upload

Welcome to Scribd!

  • 13 views

    SQL DAY-4

    Uploaded by

    RAA GHAV
    The document discusses different SQL query concepts including UNION, UNION ALL, various types of JOINS (INNER, OUTER, SELF), and SUBQUERIES. It provides examples of each concept. UNION returns distinct rows from multiple queries while UNION ALL includes duplicates. Types of joins include INNER, LEFT OUTER, RIGHT OUTER, and FULL OUTER joins for retrieving data across tables. SELF JOINs correlate rows within a single table. SUBQUERIES allow querying results of a nested SELECT statement.

    Copyright:

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

    SQL DAY-4

    Uploaded by

    RAA GHAV
    13 views10 pages
    The document discusses different SQL query concepts including UNION, UNION ALL, various types of JOINS (INNER, OUTER, SELF), and SUBQUERIES. It provides examples of each concept. UNION returns distinct rows from multiple queries while UNION ALL includes duplicates. Types of joins include INNER, LEFT OUTER, RIGHT OUTER, and FULL OUTER joins for retrieving data across tables. SELF JOINs correlate rows within a single table. SUBQUERIES allow querying results of a nested SELECT statement.

    Original Title

    sql DAY-4

    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 different SQL query concepts including UNION, UNION ALL, various types of JOINS (INNER, OUTER, SELF), and SUBQUERIES. It provides examples of each concept. UNION returns distinct rows from multiple queries while UNION ALL includes duplicates. Types of joins include INNER, LEFT OUTER, RIGHT OUTER, and FULL OUTER joins for retrieving data across tables. SELF JOINs correlate rows within a single table. SUBQUERIES allow querying results of a nested SELECT statement.

    Copyright:

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

    SQL DAY-4

    Uploaded by

    RAA GHAV
    The document discusses different SQL query concepts including UNION, UNION ALL, various types of JOINS (INNER, OUTER, SELF), and SUBQUERIES. It provides examples of each concept. UNION returns distinct rows from multiple queries while UNION ALL includes duplicates. Types of joins include INNER, LEFT OUTER, RIGHT OUTER, and FULL OUTER joins for retrieving data across tables. SELF JOINs correlate rows within a single table. SUBQUERIES allow querying results of a nested SELECT statement.

    Copyright:

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

    DAY-4

    CHAPTER 5

    ADVANCED QUERY CONCEPTS


    union
    Union operator returns all distinct rows selected by both queries
    (eliminates duplicate rows)
    eg:
    select deptno from emp union select deptno from dept;
    Example
    Employees_Norway:
    Employee_ID E_NAME
    01 Hansen, ola
    02 Svendson, tove
    03 svendson,Stephen
    04 Pettersen, kari
    Employees_USA:
    Employee_ID E_NAME
    01 turner, sally
    02 kent, clark
    03 svendson, Stephen
    04 scott, Stephen
    Example
    List all different employee names in Norway and USA:
    SELECT E_Name FROM Employees_Norway
    UNION
    SELECT E_Name FROM employees_USA
    Result
    • Name
    • Hansen, ola
    • Svendson, tove
    • Svendson, Stephen
    • Pettersen, kari
    • Turner,sally
    • Kent, clark
    • Scott, Stephen
    union all
    The union all returns all rows selected by either query including duplicates
    eg:
    select deptno from emp union all select deptno from dept;
    Example
    List all employees in Norway and USA
    SELECT E_Name FROM Employees_Norway
    UNION ALL
    SELECT E_Name from Employees_USA
    Result
    • Hansen, ola
    • Svendson, tove
    • Svendson, Stephen
    • Pattersen, kari
    • Turner, sally
    • Kent, clark
    • Svendson, Stephen
    • Scott, Stephen
    Joins:
    Joins are used to retrieve and manipulate data from more than one table in
    a single SELECT statement.
    Types:
    • inner join
    • outer join
    • cross join
    Inner joins:
    • Inner join connect two tables into a third table that includes only the rows
    that satisfy the join condition. This type of join returns rows from both
    tables only if their related columns match.
    • SELECT tablename.columnname, tablename.columnname…. FROM
    tablename INNER JOIN tablename on condition
    To list only the publishers and their titles:
    • SELECT pubname, title FROM books INNER JOIN publishers ON books.pubid
    = publishers.pubid
    OUTER JOINS
    An outer join return all the rows from one table and some rows
    from another table. An outer join is only possible between 2
    tables. Outer join is used to retain the information that does not
    match the query conditions in the results set of a join.
    SELECT tablename, columnname, ….
    FROM tablename LEFT | RIGHT | FULL
    OUTER JOIN tablename
    ON
    Tablename.columnname join_operator tablename.columnname
    Join operator can be,
    =, >, <, >=, <=, <>
    Types of outer joins
    • LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN
    LEFT OUTER JOIN:
    • Displays all rows from the first table (the table on the left of the expression)
    • List all the rows from books table with bcode, title and no.of each title sold.
    • SELECT B.bcode, title, qty FROM books B lEFT OUTER JOIN sales s ON b.code
    = S. Bcode
    RIGHT OUTER JOIN:
    • Retrieves all rows from the second table (right table).
    • SELECT B.bcode, title, Qty from books B RIGHT OUTER JOIN sales s ON
    B.bcode = s.bcode
    • Lists all the rows from sales table with book code, book title and number of
    each title sold.
    FULL OUTER JOIN:
    • Retrieves all non-matching rows for both the left side table and the right side
    table.
    • SELECT B.bcode, title, qty FROM books B FULL OUTER JOIN sales S ON
    B.bcode = S.bcode
    SELF JOIN
    • Joining a table to itself. Joins can also be sued
    within a single table. A self join correlates rows of a
    table with other rows within that same table. The
    compared columns must be the same or compatible
    datatype.
    • A self-join that returns publishers located in the
    same pincode area of Chennai city.
    • SELECT p1.pubname, p2.pubname, p2.pcode FROM
    publishers p2 ON p1.pcode = p2.pcode WHERE
    p1.city = ‘Chennai’ AND p1.pibid < p2.pubid
    SUB QUERIES
    A SUBQUERY is a SELECT statement nested INSIDE a SELECT,
    INSERT, UPDATE or DELETE statement, or inside another
    subquery. A SUBQUERY is a nested query.
    Listing those employees whose designation is same as that of
    bill:
    • SELECT * FROM employee WHERE JOB = (SELECT job from
    employee WHERE empname = ‘bill’)
    Subquery that select list of values: IN, NOT IN
    • SELECT * FROM employee WHERE JOB IN (SELECT job from
    employee WHERE detno = 20)
    • SELECT empid,empName,Salary from employee WHERE
    Salary = (SALARY MIN(Salary) FROM employee)

    You might also like