SQL Database Management Studio- Select Statements (Week 3)

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.

Hints & Requirements:

· Inner Join on CustomerID from [Orders] and [Customers]. The order of the ON statement will change results

· Refer to pages 126 – 127

· 830 rows returned.

{paste your SQL text here}

{paste your screen shot that proves your SQL successfully executed}

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.

Hints & Requirements:

· Join on orderid from [Orders] and [Order Details] tables.

· Join on productID from [Products] and [Order Details] tables.

· ‘Product cost’ is a calculated field which includes *(1.0-discount)

· Refer to pages 96 – 97, 110 – 111, 136 – 137

· 77 rows returned

{paste your SQL text here}

{paste your screen shot that proves your SQL successfully executed}

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

Hints & Requirements:

· Join on EmployeeID from [Orders] and [Employees] tables.

· Refer to pages 126 -- 127

· 830 rows returned.

{paste your SQL text here}

{paste your screen shot that proves your SQL successfully executed}

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

Hints & Requirements:

· Left outer join on CustomerID field from [Customers] and [Orders] tables.

· Refer to pages 126 –127

· 832 rows returned.

{paste your SQL text here}

{paste your screen shot that proves your SQL successfully executed}

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 (ie OrderCount) in reverse order.

Hints & Requirements:

· Left outer join on customerid field from customers and orders table. Must use count(orderid) in query, and later in the SQL statement. Use a GROUP BY and HAVING.

· Refer to pages 140 –143, 160 – 161, 164 – 169

· 18 rows returned

{paste your SQL text here}

{paste your screen shot that proves your SQL successfully executed}

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.

Hints & Requirements:

· Two WHERE clauses required. Sub query required in the first WHERE clause

· Refer to pages 104 – 105, 184 -- 189

· 7 rows returned.

{paste your SQL text here}

{paste your screen shot that proves your SQL successfully executed}

Display the Customer Name of all customers who have placed orders in 1996.

· Order the results by Company Name in ascending order.

Hints & Requirements:

· Join CUSTOMERS and ORDERS.

· Use the keyword DISTINCT.

· Refer to pages 112 – 113, 126 – 127

· 67 rows returned

{paste your SQL text here}

{paste your screen shot that proves your SQL successfully executed}

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.

Hints & Requirements:

· Three WHERE clauses required for this query.

· First WHERE clause checks for OrderDate and uses a sub query with ALL keyword.

· Second WHERE clause use equals and sub query.

· Third WHERE clause uses equal and company name.

· Refer to pages 192 – 193

· 28 rows returned.

{paste your SQL text here}

{paste your screen shot that proves your SQL successfully executed}

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.

Hints & Requirements:

· Use the CUSTOMERS and EMPLOYEES tables. Use two WHERE clauses. First WHERE clause uses reverse(NOT) set notation and sub-query. Second WHERE clause is a compound statement testing country and null.

· Refer to pages 188 - 189

· 10 rows returned

{paste your SQL text here}

{paste your screen shot that proves your SQL successfully executed}

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.

Hints & Requirements:

· Join Customer, Orders and [Order Details]

· Use the keyword: DISTINCT (look it up)

· Refer to pages 184 – 189

· 10 rows returned.

{paste your SQL text here}

{paste your screen shot that proves your SQL successfully executed}