Question

1: Produce an unduplicated list of all product IDs for all Products that have been sold. Sort the List.
2: Show the basket ID, Product ID, Product name, and description for all items. (Do it two ways-one with an ANSI join and one with a traditional Join.)
3: Modify all queries in step 2 to include the customer's last name.
4: Display all orders (basket ID, Shopper ID, and date ordered) placed in February 2012. The date should be displayed in this format: February 12, 2012.
5: Display the total quantity sold by the product ID.
6: Modify the query in step 5 to show only products that have sold less than a quantity of 3.
7: List all active coffee products (product ID, name, and price) for all coffee items priced above the overall average of coffee items.
8: Create a table named CONTACTS that includes the following columns.

Answer 1

Fig.1. Database diagram.

1: Produce an unduplicated list of all product IDs for all Products that have been sold. Sort the List.

SELECT Product_ID FROM Product WHERE Status='sold' ORDER BY Product_ID

2: Show the basket ID, Product ID, Product name, and description for all items. (Do it two ways-one with an ANSI join and one with a traditional Join.)

First:

SELECT pr.Product_ID,bas.Basket_ID,pr.Product_Name,pr.Description FROM Product AS pr,Basket AS bas

Second:

SELECT pr.Product_ID,bas.Basket_ID,pr.Product_Name,pr.Description FROM Product AS pr JOIN Basket AS bas ON pr.Product_ID!=null

3: Modify all queries in step 2 to include the customer's last name.

First:

SELECT pr.Product_ID,bas.Basket_ID,pr.Product_Name,pr.Description,sh.First_Name FROM Product AS pr,Basket AS bas, Shopper As sh

Second:

SELECT sh.First_Name, pr.Product_ID, bas.Basket_ID, pr.Product_Name, pr.Description FROM Product AS pr JOIN Basket AS bas JOIN Shopper AS sh ON sh.Shopper_ID! = 0 ON pr.Product_ID! = 0

4: Display all orders (basket ID, Shopper ID, and date ordered) placed in February 2012. The date should be displayed in this format: February 12, 2012.

SELECT bas.Date, bas.Basket_ID, pr.Description, sh.Shopper_ID FROM Product AS pr, Basket AS bas, Shopper As sh WHERE bas.Date LIKE 'February__2012'

5: Display the total quantity sold by the product ID.

SELECT Count(Product_ID) AS total_quantity FROM Product WHERE Status='sold' GROUP BY Product_ID

6: Modify the query in step 5 to show only products that have sold less than a quantity of 3.

SELECT Count(Product_ID) AS total_quantity FROM Product WHERE Status='sold' GROUP BY Product_ID HAVING Count(Product_ID)<3

7: List all active coffee products (product ID, name, and price) for all coffee items priced above the overall average of coffee items.

SELECT Product_ID, Product_Name, Price FROM Product WHERE Price > (SELECT AVG(Price) FROM Product) AND Product_Name='coffee%'

8: Create a table named CONTACTS that includes the following columns.

/// what following columns you want?

CREATE TABLE CONTACTS (
ID int Primary Key,
First_Name nvarchar(50) NOT NULL,
Last_Name nvarchar(50) NOT NULL
)