Question

1. Saul wants to buy a new writer's desk, but he doesn't want to spend more than $350. Write a select statement to find products matching these requirements
2. For each customer, list the customer name and city and state in this format: City, State (one column)

3. Display only those orders where more than one different product has been ordered

4. Find the average price of bookcases

5. A. List all the customers and list the number of different shipping addresses for each. Customers with more shipping addresses should appear at the top.
   B. Now list only customers with 2 or more locations

6. A. How many different materials are needed to assemble an Oak Computer Desk
   B. Find the total dollar amount in materials required to assemble an Oak Computer Desk

7. Report the OrderID and OrderDate for all orders for Salespersons from New York
   A. use a join
   B. Use a noncorrelated Subquery
   C. use a correlated subquery
   D. use where exists
8. Report the ID and Name of employees (managers) who are managing employees with more than one skill

9. Find the names of all the customers who have purchased items from either the Antique or Classical lines.
   a. Use a set operator (UNION, INTERSECT, OR MINUS)
   b. Do not use a set operator (UNION, INTERSECT, OR MINUS)

10. List the order id for all orders where the customer has purchased both a product from the Antique and Modern lines in the same order
    a. Use a set operator (UNION, INTERSECT, OR MINUS)
    b. Do not use a set operator (UNION, INTERSECT, OR MINUS)

11. Functions:
    a. Create a function that has the Customer_id as input and returns a number that represents the total cost of orders for this customer. Notice that you will need to multiply the Order_Qty from table order_details and the Unit_Price from table items to get the total cost of each order.
    b. Now use the function: Write a select statement that selects all the customers and the total cost of the orders they have ever made.

12. Procedure:
    a. Write a procedure that has as input the customer_id. Then the procedure should delete the corresponding customer from table customers.
    b. Notice that customer_id is a foreign key on table orders. Therefore, the customer cannot be deleted if he has placed orders. To delete a record from customers you should first delete the corresponding orders and to do that you need to first delete the corresponding order_details.
    c. Now execute the procedure to delete customer with id 1. If you have solved part 01 a record will be inserted into Customer_Archives.
    d. ROLLBACK; and all these changes will be undone!

Answer 1

Answering first four subparts. For others, please create seperate question.

Kindly upvote if this helped you.

Q1 - Saul wants to buy a new writer's desk, but he doesn't want to spend more than $350. Write a select statement to find products matching these requirements

A1 - SELECT * FROM <table_name> WHERE PROD_TYPE = 'DESK' AND cost<='350';
- Assumption made
- Please give table name as per the statement. This is just a template
- The relation/table has a column called PROD_TYPE and its value is 'DESK' for desk products
- The relation/table has a column called cost which tells the cost of the item.


Q2 - For each customer, list the customer name and city and state in this format: City, State (one column)

A2 - SELECT CONCAT(CITY,",",STATE) , NAME FROM <table_name>;
- Assumption made
- Please give table name as per the statement. This is just a template
- The relation/table has a column called NAME and its value is customers name


Q3 - Display only those orders where more than one different product has been ordered

A3 -  SELECT ORDER_ID, COUNT(DISTINCT PROD_ID) as count FROM <customer_table> AS C
INNER JOIN <orders_table> AS O ON C.customer_ID = O.customer_ID
GROUP BY C.customer_ID, customer_name
HAVING COUNT(DISTINCT PROD_ID) > 1

- Assumption made
- Please give table name as per the statement. This is just a template
- There are two tables customer and orders. Order info we have in customer table and the corresponding products in the orders we have in prod table. We are joining the tables and based on distinct prod id, we are saying that particular order has different products.



Q4 - Find the average price of bookcases

A4 - SELECT AVG(BOOKCASES) FROM <table_name>;
- Assumption made
- Please give table name as per the statement. This is just a template
- The relation/table has a column called BOOKCASES and its value is price of bookcase