Solution Library

Write Queries To Retrieve Data For TSQL

Question

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.
      o    Include the Order Date and the Required Date. Order the results by Company Name in ascending order.
      o    830 rows returned.
2.    For each order, list the OrderID, Orderdate, Product Name, UnitPrice, Quantity Ordered, and Total Cost. Label the column 'Product Cost.'
      o    For each product (including the discount), only show those orders that were placed in March of 1997.
      o    Sort the data by the OrderID, then the Product Name.
      o    77 rows returned.
3.    For each order, display the OrderID, ShipName, and the Employee First and Last Name.
      o    Order the results by Employee Last Name in descending order, then by Employee First Name in ascending Order, then by OrderID in ascending order.
      o    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.
     o    832 rows returned.
5.    List all customers (include CustomerId and Company Name) who have placed less than 5 orders.
     o    Include those customers who have placed 0 orders. Name the count field "OrderCount."
     o    Order by number of placed orders in reverse order.
     o    18 rows returned.
6.    Display the ProductName, and UnitPrice of all products that have a unit price larger than Tarte au Sucre.
     o    Order the results by UnitPrice in descending order.
     o    7 rows returned.
7.    Display the Customer Name of all customers who have placed orders in 1996.
     o    Order the results by Company Name in ascending order.
     o    67 rows returned.
8.    Display the OrderID of all orders that where placed after all orders placed by Bottom-Dollar Markets.
     o    Order the result by OrderID in ascending order.
     o    28 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.
     o    Order the results by Company Name.
     o    10 rows returned.
10.    Display the Product Names of all products that were placed by customers in CA.
      o    Order the result by Product Name in ascending order.
      o    Eliminate duplicate rows in the results.
      o    10 Rows Returned.

 

Summary

The question belongs to Computer Science and it discusses about writing SQL queries for retrieving data in TSQL.

Total Word Count 338

Download Full Solution

Comments

  • HWA
    Rasha

    this is a very good website

  • HWA
    maani

    I have 50 questions for the same test your page is showing only 28

  • HWA
    joeanne

    hi can you please help or guide me to answer my assignments. thanks

  • HWA
    joeanne

    hi can anyone help or guide me to my assignments. thanks

  • HWA
    Monik


  • HWA
    Cristina

    This solution is perfect ...thanks

  • HWA
    Janete

    Hello Allison,I love the 2nd image that you did! I also, had never heard of SumoPaint, is something that I will have to exolpre a bit! I understand completely the 52 (or so) youtube videos that you probably watched. Sometimes they have what you want, sometimes they don't! However, it is always satisfying when you are able to produce something that you have taught yourself. Great job!Debra 0 likes

  • HWA
    Sandeep

    Perfect bank of solution. 

  • HWA
    Oxana

    great !

  • HWA
    Paul Brandon-Fritzius

    thanks for the quick response. the solution looks good. :)

  • HWA
    tina Johnson

    thnx for the answer. it was perfect. just the way i wanted it. 

  • HWA
    Giuseppe

    works fine.