Explore BrainMass
Share

Explore BrainMass

    Programming SQL Statements to Retrieve Data from a Database

    This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

    Please Complete in Script.

    Write SQL statements that will retrieve the following data from a database, using Subqueries and Joins. Using the Northwind database, write a SQL SELECT statement that will retrieve the data for the following questions:

    1. For each order, display the Company Name, City, and the Country for the customer who placed the order.
    - Include the Order Date and the Required Date. Order the results by Company Name in ascending order.
    - 830 rows returned.

    2. For each order, list the OrderID, Orderdate, Product Name, UnitPrice, Quantity Ordered, and Total Cost. Label the column 'Product Cost.'
    - For each product (including the discount), only show those orders that were placed in March of 1997.
    - Sort the data by the OrderID, then the Product Name.

    3. For each order, display the OrderID, ShipName, and the Employee First and Last Name.
    - Order the results by Employee Last Name in descending order, then by Employee First Name in ascending Order, then by OrderID in ascending order.
    - 830 rows returned.

    4. Modify the first query to list the orders with customer details, but include all customers even if they have not placed an order.
    - 832 rows returned.

    5. List all customers (include CustomerId and Company Name) who have placed less than 5 orders.
    - Include those customers who have placed 0 orders. Name the count field "OrderCount."
    - Order by number of placed orders in reverse order.
    - 5 rows returned.

    6. Display the ProductName, and UnitPrice of all products that have a unit price larger than Tarte au Sucre.
    - Order the results by UnitPrice in descending order.
    - 7 rows returned.

    7. Display the Customer Name of all customers who have placed orders in 1996.
    - Order the results by Company Name in ascending order.
    - 67 rows returned.

    8. Display the OrderID of all orders that where placed after all orders placed by Bottom-Dollar Markets.
    - Order the result by OrderID in ascending order.
    - 67 rows returned.

    9. List the Company Name of all U.S.-based customers who are NOT located in the same state (or region) as any of the employees.
    - Order the results by Company Name.
    - 10 rows returned.

    10. Display the Product Names of all products that were placed by customers in CA.
    - Order the result by Product Name in ascending order.
    - Eliminate duplicate rows in the results.
    - 10 Rows Returned.

    © BrainMass Inc. brainmass.com October 10, 2019, 5:01 am ad1c9bdddf
    https://brainmass.com/computer-science/sql/programming-sql-statements-retrieve-data-database-489149

    Solution Summary

    This solution is provided in the attached .sql file. The expert examines programming a SQl statement.

    $2.19