Assignment Name: Unit 3 Individual Project
Deliverable Length: 10 SQL Statements
Details:
|
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.
|
Features:
ITCO331, NorthWind, SQL Server , Alternate Queries Also Given
Preview Screen Shots:
Preview SQL Script.
Query 5 with 18 rows
Query 10 with 10 rows
Asked: $20
Email: syedjahangirb@gmail.com to get assignment
Files in Solution:
Northwind_DB_Queries.zip (1K)[
Northwind_DB_Queries.sql
]
ITCO331 IP 3.doc (31K) (Preview)
No comments:
Post a Comment