Scribd Logo
Upload

Welcome to Scribd!

  • 140 views

    NorthwindQueries AnswersTo7and9

    Uploaded by

    aamir
    The document describes 10 SQL queries for the Northwind database: 1. Lists products that are out of stock. 2. Lists products that are out of stock and not discontinued, including supplier names. 3. Lists products where stock levels are below reorder amounts. 4. Lists products ordered most to least frequently by order. 5. Lists products ordered with most to fewest actual items. 6. Lists product categories and supplying vendors. 7. Lists all customers with or without orders including order details. 8. Lists customers and number of orders placed. 9. Identifies customer with most orders placed. 10. Allows user input of city for customers/suppliers query.

    Copyright:

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

    NorthwindQueries AnswersTo7and9

    Uploaded by

    aamir
    140 views5 pages
    The document describes 10 SQL queries for the Northwind database: 1. Lists products that are out of stock. 2. Lists products that are out of stock and not discontinued, including supplier names. 3. Lists products where stock levels are below reorder amounts. 4. Lists products ordered most to least frequently by order. 5. Lists products ordered with most to fewest actual items. 6. Lists product categories and supplying vendors. 7. Lists all customers with or without orders including order details. 8. Lists customers and number of orders placed. 9. Identifies customer with most orders placed. 10. Allows user input of city for customers/suppliers query.

    Original Title

    NorthwindQueries_AnswersTo7and9.doc

    Copyright

    © © All Rights Reserved

    Available Formats

    DOC, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document describes 10 SQL queries for the Northwind database: 1. Lists products that are out of stock. 2. Lists products that are out of stock and not discontinued, including supplier names. 3. Lists products where stock levels are below reorder amounts. 4. Lists products ordered most to least frequently by order. 5. Lists products ordered with most to fewest actual items. 6. Lists product categories and supplying vendors. 7. Lists all customers with or without orders including order details. 8. Lists customers and number of orders placed. 9. Identifies customer with most orders placed. 10. Allows user input of city for customers/suppliers query.

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    140 views5 pages

    NorthwindQueries AnswersTo7and9

    Uploaded by

    aamir
    The document describes 10 SQL queries for the Northwind database: 1. Lists products that are out of stock. 2. Lists products that are out of stock and not discontinued, including supplier names. 3. Lists products where stock levels are below reorder amounts. 4. Lists products ordered most to least frequently by order. 5. Lists products ordered with most to fewest actual items. 6. Lists product categories and supplying vendors. 7. Lists all customers with or without orders including order details. 8. Lists customers and number of orders placed. 9. Identifies customer with most orders placed. 10. Allows user input of city for customers/suppliers query.

    Copyright:

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

    Northwind Database Queries

    Name:

    The relationships for the Northwind Database are shown below.

    Paste the SQL statements for the queries below the query statements.

    1. OutOfStockProducts: Make a list of products that are out of stock. (My query
    yielded 5 records.)

    2. OutOfStockProductsNotDiscontinued: Make a list of products that are out of


    stock and have not been discontinued. Include the suppliers’ names. (My query
    yielded 1 record.)

    3. ReorderProducts: Make a list of products that need to be re-ordered i.e. where the
    units in stock and the units on order is less than the reorder level. (My query
    yielded 2 records.)
    4. ProductPopularity: Make a list of products and the number of orders in which the
    product appears. Put the most frequently ordered item at the top of the list and so
    on to the least frequently ordered item. (My query yielded 77 records; Raclette
    Courdavault was at the top and Mishi Kobe Niku was at the bottom.)

    5. ProductPopularity2: Make a list of products and total up the number of actual


    items ordered. Put the most frequently ordered item at the top of the list and so on
    to the least frequently ordered item. (My query yielded 77 records; Camembert
    Pierrot was at the top and Mishi Kobe Niku was at the bottom.)

    6. CategorySuppliers: Make a list of categories and suppliers who supply products


    within those categories. (My query yielded 49 records.)

    7. CustomerOrders: Make a complete list of customers, the OrderID and date of any
    orders they have made. Include customers who have not placed any orders. (My
    query yielded 832 records.)

    Add the Customer and Order Tables to your query. Select the CustomerName, OrderID
    and OrderDate fields. Right click on the relationship line (diagonal part) and choose Join
    Properties.

    Ordinarily one does a “natural join” which only keeps entity occurrences actually
    participating in the relationship. In order to keep customers who have not placed orders,
    select the join from the choices available in the Join Properties dialog box.
    8. CustomerNumberOfOrders: Make a complete list of customers along with the
    number of orders they have placed. (My query yielded 91 records.)

    9. CustomerWithMaxNumberOfOrders: Create a query that determines the customer


    who has placed the maximum number of orders. (My query yielded Save-a-lot
    Markets with 31 orders.)

    Instead of adding a table to begin designing a query, we can begin by adding a previous
    query.
    Use the previous order-counting query to find the maximum count.

    Now design a new query and bring in both previous queries.


    Establish a relationship between CountOfOrderID and MaxOfCountOfOrderID by
    dragging one field over to the other. Choose the fields you want displayed.

    10. CustomerAndSuppliersParameterizedByCity: Create a parameterized query that


    has the user enter a city and then list the customers or suppliers from that city.
    You might want to use their union query as a starting point. (London had 6
    customers and 1 supplier.)

    You might also like