Scribd Logo
Upload

Welcome to Scribd!

  • 5 views

    ASSIGNMENT Database

    Uploaded by

    Ayesha Kashif

    Copyright:

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

    ASSIGNMENT Database

    Uploaded by

    Ayesha Kashif
    5 views6 pages

    Original Title

    ASSIGNMENT database

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

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

    ASSIGNMENT Database

    Uploaded by

    Ayesha Kashif

    Copyright:

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

    ASSIGNMENT # 2

    Q1. What are joins in SQL?


    Joins in SQL are used to combine rows from two or more tables
    based on a related column between them. Joins allow you to
    retrieve data that is spread across multiple tables in a database.
    Example:
    Let's look at a selection from the "Orders" table:

    Order ID Customer ID Order Date


    10308 2 1996-09-18
    10309 37 1996-09-19
    10310 77 1996-09-20
    Then, look at a selection from the "Customers" table:

    Customer ID Customer Name Contact Name Country


    1 Ali Khan Ali Lahore
    2 Hamza Malik Hamza Multan
    3 Faizan Mughal Faizan Lahore
    Then, we can create the following SQL statement (that contains
    an INNER JOIN), that selects records that have matching values
    in both tables:
    SELECT Orders.OrderID, Customers.CustomerName,
    Orders.OrderDate
    FROM Orders
    INNER JOIN Customers ON
    Orders.CustomerID=Customers.CustomerID;
    Output:
    Order ID Customer Name Order Date
    10308 Ali Khan 9/18/1996
    10365 Hamza Khan 11/27/1996
    10383 Faizan Mughal 12/16/1996

    Differences between Left Outer Join, Right


    Outer Join, and Full Outer Join:
    Left Outer Join:
     Returns all rows from the left table, and the matched rows
    from the right table.
     If there are any rows in the left table that do not have
    matches in the right table, those rows are also included in
    the result set.
     The unmatched rows from the right table are filled with
    NULL values.
     The syntax for a left outer join is:
    SELECT column1, column2, ...
    FROM table1
    LEFT JOIN table2
    ON table1.column = table2.column;

    Here's an example to illustrate the differences:


    Let's say we have two tables: customers and orders.
    customers table:
    customer_id name

    1 John

    2 Jane

    3 Bob

    4 Alice
    orders table:
    order_id customer_id order_amount

    1 1 100

    2 1 200

    3 2 50

    4 4 75

    Example of Left Outer Join:


    SELECT customers.name, orders.order_amount
    FROM customers
    LEFT JOIN orders
    ON customers.customer_id = orders.customer_id;
    This query will return:

    name order_amount

    John 100

    John 200

    Jane 50

    Bob NULL

    Alice 75

    Right Outer Join:


     Returns all rows from the right table, and the matched
    rows from the left table.
     If there are any rows in the right table that do not have
    matches in the left table, those rows are also included in
    the result set.
     The unmatched rows from the left table are filled with
    NULL values.
     The syntax for a right outer join is:
    SELECT column1, column2, ...
    FROM table1
    RIGHT JOIN table2
    ON table1.column = table2.column;

    Example of Right Outer Join:


    SELECT customers.name, orders.order_amount
    FROM customers
    RIGHT JOIN orders
    ON customers.customer_id = orders.customer_id;
    This query will return:

    name order_amount

    John 100

    John 200

    Jane 50

    Alice 75

    Full Outer Join:


     Returns all rows from both the left and right tables.
     If there are any rows in either table that do not have
    matches in the other table, those rows are also included in
    the result set.
     The unmatched rows from both tables are filled with NULL
    values.
     The syntax for a full outer join is:
    SELECT column1, column2, ...
    FROM table1
    FULL JOIN table2
    ON table1.column = table2.column;

    Example of Full Outer Join:


    SELECT customers.name, orders.order_amount
    FROM customers
    FULL JOIN orders
    ON customers.customer_id = orders.customer_id;
    This query will return:
    name order_amount

    John 100

    John 200

    Jane 50

    Bob NULL

    Alice 75

    You might also like