Use AP database to write SQL statements using subqueries:

1) (5 points) Write a SELECT statement that returns the same result set as this SELECT statement, but don't use a join. Instead, use a subquery in a WHERE clause that uses the IN keyword.

SELECT DISTINCT vendor_name

FROM vendors JOIN invoices

ON vendors.vendor_id = invoices.vendor_id ORDER BY vendor_name

 

2) (5 points) Write a SELECT statement that answers this question: Which invoices have a payment total that's greater than the average payment total for all invoices with a payment total greater than 0? Return the invoice_number and invoice_total columns for each invoice. This should return 20 rows. Sort the results by the invoice_total column in descending order.

 

3) (5 points) Write a SELECT statement that returns two columns from the General_Ledger_Accounts table: account_number and account_description. Return one row for each account number that has never been assigned to any line item in the Invoice_Line_Items table. To do that, use a subquery intro-duced with the NOT EXISTS operator. This should return 54 rows.

 

Use AdventureWorks database to write SQL statements using subqueries

4) (5 points) What is the maximum total due amount per order? Can you find what is the sales order number?

 

5) (10 points) What is average Sales quantity per order (use Sales order detail) which products have max quantity per order below overall average quantity. List product names for these products. Use CTE to make your query more readable

 

6) (10 points) Write a question that would require a SQL statement with subquery in 'Having' clause. Then write this query and run it to answer your question.