Scribd Logo
Upload

Welcome to Scribd!

  • 30 views

    Worksheet Week6

    Uploaded by

    vbxfgh
    The document discusses different types of joins in SQL including inner joins, left outer joins, right outer joins, and full outer joins. It provides the syntax for each type of join and examples. It also includes a lab task that provides tables for Department, Employee, and asks the student to write SQL queries using outer joins to answer various questions about the data.

    Copyright:

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

    Worksheet Week6

    Uploaded by

    vbxfgh
    30 views4 pages
    The document discusses different types of joins in SQL including inner joins, left outer joins, right outer joins, and full outer joins. It provides the syntax for each type of join and examples. It also includes a lab task that provides tables for Department, Employee, and asks the student to write SQL queries using outer joins to answer various questions about the data.

    Original Title

    Worksheet_week6

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document discusses different types of joins in SQL including inner joins, left outer joins, right outer joins, and full outer joins. It provides the syntax for each type of join and examples. It also includes a lab task that provides tables for Department, Employee, and asks the student to write SQL queries using outer joins to answer various questions about the data.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    30 views4 pages

    Worksheet Week6

    Uploaded by

    vbxfgh
    The document discusses different types of joins in SQL including inner joins, left outer joins, right outer joins, and full outer joins. It provides the syntax for each type of join and examples. It also includes a lab task that provides tables for Department, Employee, and asks the student to write SQL queries using outer joins to answer various questions about the data.

    Copyright:

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

    DBMS Lab Task

    WEEK-6 OUTER JOINS


    The SQL JOIN joins two tables based on a common column, and selects records that have matching values in
    these columns.
    The SQL LEFT JOIN joins two tables based on a common column, and selects records that have matching
    values in these columns and remaining rows from the left table.
    The SQL RIGHT JOIN joins two tables based on a common column, and selects records that have matching
    values in these columns and remaining rows from the right table.
    The SQL FULL OUTER JOIN joins two tables based on a common column, and selects records that have
    matching values in these columns and remaining rows from both of the tables.
    Different Types of SQL JOINs

    Here are the different types of the JOINs in SQL:

     (INNER) JOIN or EQUI JOIN: Returns records that have matching values in both tables
     LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right
    table
     RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left
    table
     FULL (OUTER) JOIN: Returns all records when there is a match in either left or right tab le

    Dept. of CSE, NIT Andhra Pradesh 1


    LEFT OUTER JOIN:
    Syntax :
    SELECT column_list FROM table1 LEFT JOIN table2 ON [(join_condition)]

    Ex 1:
    select * from emp e LEFT JOIN dept d on e.empno=d.deptno;
    Ex 2:
    SELECT Customers.customer_id, Customers.first_name, Orders.amount FROM Customers
    LEFT JOIN Orders ON Customers.customer_id = Orders.customer;

    RIGHT OUTER JOIN:


    Syntax :
    SELECT column_list FROM table1 RIGHT JOIN table2 ON [(join_condition)]
    Ex 1:
    select * from emp e RIGHT JOIN dept d on e.empno=d.deptno;
    Ex 2:
    SELECT Customers.customer_id, Customers.first_name, Orders.amount FROM Customers
    RIGHT JOIN Orders ON Customers.customer_id = Orders.customer;

    FULL OUTER JOIN:


    Syntax :
    SELECT column_list FROM table1 FULL OUTER JOIN table2 ON [(join_condition)]
    Ex 1:
    select * from emp e FULL OUTER JOIN dept d on e.empno=d.deptno;
    Ex 2:
    SELECT Customers.customer_id, Customers.first_name, Orders.amount FROM Customers
    FULL OUTER JOIN Orders ON Customers.customer_id = Orders.customer;

    Dept. of CSE, NIT Andhra Pradesh 2


    1. Create a tables called Dept, Employee and Answer the following Questions using OUTER
    JOINS.
    Note: deptno in employee table is foreign key references deptno in dept table

    INSERT Following into dept table


    50 HR California
    60 Web&Network San Francisco

    INSERT following records into Employee table


    7769 Sam Clerk 20-May-81 7839 2000 0 NULL
    7469 Ram Analyst 10-June-82 7698 3000 0 NULL
    7596 Rahul Clerk 13-Nov-81 7639 2500 200 NULL
    7367 Rajesh Salesman 11-Aug-82 7769 3200 500 NULL
    7473 Aman Analyst 07July-82 7839 2700 300 NULL
    7639 Vishal Salesman 08-Dec-81 7473 2000 0 NULL

    Dept. of CSE, NIT Andhra Pradesh 3


    a. Display All employee details with dept details including Employees who have not allotted with any deptno.
    b. Display employee details with dept details who have not allotted with any deptno Only.
    c. Display employees count who have not allotted with any deptno.
    d. Display All Clerks who donot have deptno having salary greater than 2000.
    e. Display employee details who have not allotted with any deptno and whose commission is null.
    f. Display All Salesman details who donot have deptno , joined in 1981.
    g. Display employee details who joined in 1981 and have not allotted with any deptno.
    h. Display sum of Salaries of employees who have not allotted with any deptno.
    i. Display All employee details with dept details including Dept details who do not have any employees.
    j. Display dept details including Dept details who do not have any employees Only.
    k. Display Employee name and empno who donot have any dept name and location.
    l. Display All employee details who have not allotted with any deptno and Dept details who don’t have any
    employees.
    m. Count no of dept’s who donot have any employees.
    n. Display deptno wise employee count including dept’s who donot have employees.
    o. Display All Clerks details who have not allotted with any deptno and Dept details who don’t have any
    employees.
    p. Display All Salesman details who have not allotted with any deptno and Dept details who don’t have any
    employees.
    q. Display All Analysts who donot have deptno having salary less than 3000.

    Dept. of CSE, NIT Andhra Pradesh 4

    You might also like