Scribd Logo
Upload

Welcome to Scribd!

  • 12 views

    Northwind Exercises

    Uploaded by

    Allaa Mahad Bourahla

    Copyright:

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

    Northwind Exercises

    Uploaded by

    Allaa Mahad Bourahla
    12 views2 pages

    Original Title

    Northwind-Exercises

    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
    12 views2 pages

    Northwind Exercises

    Uploaded by

    Allaa Mahad Bourahla

    Copyright:

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

    Northwind-Exercises.

    md 2/27/2019

    Northwind Exercises
    Exercise Simple SQL Queries
    Use the Northwind Demo Database. Write the queries for the following:

    1. Get all columns from the tables Customers, Orders and Suppliers
    2. Get all Customers alphabetically, by Country and name
    3. Get all Orders by date
    4. Get the count of all Orders made during 1997
    5. Get the names of all the contact persons where the person is a manager, alphabetically
    6. Get all orders placed on the 19th of May, 1997

    Exercise SQL Queries for JOINS


    Using the Northwind Database, write the queries for the following:

    1. Create a report for all the orders of 1996 and their Customers (152 rows)
    2. Create a report that shows the number of employees and customers from each city that has
    employees in it (5 rows)
    3. Create a report that shows the number of employees and customers from each city that has
    customers in it (69 rows)
    4. Create a report that shows the number of employees and customers from each city (71
    rows)

    Exercise SQL Queries for HAVING


    1. Create a report that shows the order ids and the associated employee names for orders that
    shipped after the required date (37 rows)
    2. Create a report that shows the total quantity of products (from the Order_Details table)
    ordered. Only show records for products for which the quantity ordered is fewer than 200 (5
    rows)
    3. Create a report that shows the total number of orders by Customer since December 31,
    1996. The report should only return rows for which the total number of orders is greater
    than 15 (5 rows)

    Exercise SQL Inserting Records


    (Hint: use transactions)

    1. Insert yourself into the Employees table


    Include the following fields: LastName, FirstName, Title, TitleOfCourtesy, BirthDate,
    HireDate, City, Region, PostalCode, Country, HomePhone, ReportsTo
    2. Insert an order for yourself in the Orders table
    Include the following fields: CustomerID, EmployeeID, OrderDate, RequiredDate
    3. Insert order details in the Order_Details table
    Include the following fields: OrderID, ProductID, UnitPrice, Quantity, Discount

    1/2
    Northwind-Exercises.md 2/27/2019

    Exercise SQL Updating Records


    (Hint: use transactions)

    1. Update the phone of yourself (from the previous entry in Employees table) (1 row)
    2. Double the quantity of the order details record you inserted before (1 row)
    3. Repeat previous update but this time update all orders associated with you (1 row)

    Exercise SQL Deleting Records


    (Hint: use transactions)

    1. Delete the records you inserted before. Don't delete any other records!

    Exercise Advances SQL queries


    1. What were our total revenues in 1997 (Result must be 617.085,27)

    2. What is the total amount each customer has payed us so far (Hint: QUICK-Stop has payed us
    110.277,32)

    3. Find the 10 top selling products (Hint: Top selling product is "Côte de Blaye")

    4. Create a view with total revenues per customer

    5. Which UK Customers have payed us more than 1000 dollars (6 rows)

    6. How much has each customer payed in total and how much in 1997. We want one result set
    with the following columns:

    CustomerID
    CompanyName
    Country
    Customer's total from all orders
    Customer's total from 1997 orders

    You can try this with views, subqueries or temporary tables. Try using [Order Subtotals]
    view that already exists in database. (91 rows, Customer "Centro comercial Moctezuma" has
    100,80 total revenues and zero (0) revenues in 1997 )

    2/2

    You might also like