Scribd Logo
Upload

Welcome to Scribd!

  • 339 views

    DBMS Lab 5 (JOINS)

    Uploaded by

    Musab Hussain
    This document contains instructions for 14 SQL queries to be performed using various databases like Pubs and Northwind. The queries involve joining multiple tables to retrieve customer names, order details, product details, employee details, author details and other related information. Inner joins are used to link tables based on primary and foreign keys and retrieve the specified columns in the result.

    Copyright:

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

    DBMS Lab 5 (JOINS)

    Uploaded by

    Musab Hussain
    339 views7 pages
    This document contains instructions for 14 SQL queries to be performed using various databases like Pubs and Northwind. The queries involve joining multiple tables to retrieve customer names, order details, product details, employee details, author details and other related information. Inner joins are used to link tables based on primary and foreign keys and retrieve the specified columns in the result.

    Original Description:

    Contains all lab tasks of lab 5 ... Just take help don't copy paste . please

    Original Title

    DBMS Lab 5 ( JOINS )

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document contains instructions for 14 SQL queries to be performed using various databases like Pubs and Northwind. The queries involve joining multiple tables to retrieve customer names, order details, product details, employee details, author details and other related information. Inner joins are used to link tables based on primary and foreign keys and retrieve the specified columns in the result.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    339 views7 pages

    DBMS Lab 5 (JOINS)

    Uploaded by

    Musab Hussain
    This document contains instructions for 14 SQL queries to be performed using various databases like Pubs and Northwind. The queries involve joining multiple tables to retrieve customer names, order details, product details, employee details, author details and other related information. Inner joins are used to link tables based on primary and foreign keys and retrieve the specified columns in the result.

    Copyright:

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

    DATA MANGEMENT SYSTEM

    BSCS 4B SPRING 2014



    PRACTICAL LAB FILE



    LAB # 5 JOIN




    DEPARTMENT OF COMPUTER SCIENCE
    BAHRIA UNIVERSITY, KARACHI CAMPUS

    Using Pubs and Northwind database solve the following queries.
    1) Get Shippers Company name for an order
    SELECT
    s.CompanyName , o.OrderID
    FROM
    Orders o
    INNER JOIN
    Shippers s
    ON
    o.ShipVia=s.ShipperID;
    2) Display Products name and there Suppliers name
    SELECT
    p.ProductName , s.ContactName
    FROM
    Products p
    INNER JOIN
    Suppliers s
    ON
    p.SupplierID=s.SupplierID;

    3) Display Which Customers were served by which Employees
    SELECT
    c.ContactName , e.FirstName
    FROM
    Orders o
    INNER JOIN
    Employees e
    ON
    e.EmployeeID=o.EmployeeID
    INNER JOIN
    Customers c
    ON
    c.CustomerID=o.CustomerID;






    4) Write a Query to display the following table
    Customer Name Order Id Order Date

    SELECT
    c.ContactName , o.OrderID , o.OrderDate
    FROM
    Orders o
    INNER JOIN
    Customers c
    ON
    o.CustomerID=c.CustomerID;
    5) Write a query to display the following table for any order
    Customer Name Product Name Unit Price Quantity Discount

    SELECT
    c.ContactName , p.ProductName , od.UnitPrice , od.Quantity ,
    od.Discount
    FROM
    Orders o
    INNER JOIN
    Customers c
    ON
    o.CustomerID=c.CustomerID
    INNER JOIN
    [Order Details] od
    ON
    o.OrderID=od.OrderID
    INNER JOIN
    Products p
    ON
    p.ProductID=od.ProductID;






    6) Make a list of all the titles and there authors.
    SELECT
    a.au_fname , t.title
    FROM
    titleauthor ta
    INNER JOIN
    titles t
    ON
    ta.title_id=t.title_id
    INNER JOIN
    authors a
    ON
    ta.au_id=a.au_id;

    7) Name the store that offer initial customer discount.
    SELECT
    s.stor_name
    FROM
    discounts d
    INNER JOIN
    stores s
    ON
    d.stor_id=s.stor_id;

    8) Name the Titles that have been sold the most.
    SELECT
    t.title
    FROM
    (
    select *
    from sales
    where qty = (
    select max(qty)
    from sales
    )
    ) s
    INNER JOIN
    titles t
    ON
    s.title_id=t.title_id;
    9) List all the employees working for New Moon Books.
    SELECT
    e.emp_id,e.fname
    FROM
    employee e
    INNER JOIN
    ( SELECT * FROM
    publishers
    WHERE
    pub_name='New Moon Books'
    ) p
    ON
    e.pub_id=p.pub_id;

    10) Who is the production manager of Lucerne Publishing?
    SELECT
    e.fname as [Production Manger of Lucerne Publishing]
    FROM
    (
    SELECT
    pub_id,pub_name
    FROM
    publishers
    WHERE
    pub_name='Lucerne Publishing'

    ) p
    INNER JOIN
    employee e
    ON
    p.pub_id=e.pub_id
    INNER JOIN
    (
    SELECT
    job_id,job_desc
    FROM
    jobs
    WHERE
    job_desc='Productions Manager'

    ) j
    ON
    e.job_id=j.job_id;


    11) Make the list of all Authors sales.
    SELECT
    a.au_id,a.au_fname,s.qty,s.ord_date
    FROM
    sales s
    INNER JOIN
    titleauthor ta
    ON
    s.title_id=ta.title_id
    INNER JOIN
    authors a
    ON
    a.au_id=ta.au_id;
    12) Name the publisher that has authors whose name ends with e.
    SELECT
    p.pub_name,a.au_lname
    FROM
    titles t
    INNER JOIN
    publishers p
    ON
    t.pub_id=p.pub_id
    INNER JOIN
    titleauthor ta
    ON
    t.title_id=ta.title_id
    INNER JOIN
    (
    SELECT
    au_id,au_lname
    FROM
    authors
    WHERE
    au_lname LIKE '%e'
    ) a
    ON
    a.au_id=ta.au_id;





    13) Which publisher has got most titles in store in Seattle?
    SELECT
    p.pub_name,t.[Most title]
    FROM
    (
    SELECT TOP 1
    Count(title_id) as [Most title],pub_id
    FROM
    titles
    GROUP BY
    pub_id
    ORDER BY
    COUNT(*) desc
    ) t
    INNER JOIN
    publishers p
    ON
    t.pub_id=p.pub_id;
    14) List all the authors available in Barnums store.
    SELECT
    a.au_fname
    FROM
    sales sa
    INNER JOIN
    (
    SELECT
    stor_id
    FROM
    stores
    WHERE
    stor_name='Barnum''s'
    ) st
    ON
    sa.stor_id=st.stor_id
    INNER JOIN
    titleauthor ta
    ON
    ta.title_id=sa.title_id
    INNER JOIN
    authors a
    ON
    a.au_id=ta.au_id;

    You might also like