Question

This code must be in SQL -

Run the script provided with the assignment. This will create the tables used for this assignment. Write the necessary SQL commands to perform the required actions. Run the queries to obtain results from the database. (Make sure all columns returned have proper headings.)

3

1. 2

   In a single row show the following values: how many unique manufacturers are in the products table. What is the most expensive price per unit in the products tables and the least expensive product in the products table.
   
2. What is the average sales from sales reps who work from home?
   

3. 2

   Show each Customer number and the total amount spent on Orders to date.

3

4. Display each Sales Rep's Name followed by a count of how many customers they have.

3

5. Show us the total amount of sales to date for each Product. Note that a Product is a combination of Manufacturer and Product. Please order your results by the largest total sales first.

4

6. A Manufacturer has many products. (Example: ACI has around 7 different Products) Show us a listing of all the Manufacturers and a count of their products. NOTE: we do not care about products worth less than $0.50, remove those products from your counts. Additionally, we only want to see manufacturers if they have at least 4 different products.

4

7. Using a non-correlated SUBQUERY, Show us the value of the highest credit limit between all customers of Sue Smith and Paul Cruz.

4

8. Use a correlated SUBQUERY to solve this question. We want to see products that are selling well. Show us all of products (Mfr/Product) and their description. But only show us products where the total amount sold to date is more than 20 times the current price of a single product.
   

Example:
ACI 41002 is one of the products you should display because…
Price per unit is $0.76, 20 times that value is $15.20
The sum of all orders containing ACI 41002 is $48.64, which is larger than $15.20!

5

9. Use a SUBQUERY and the key word EXISTS. Show all the Companies who have placed an order between January 1st, 2000 and January 1st, 2001

Answer 1

Since you have not provided the table schema and column names, it is impossible for me to provide you correct working SQL Queries.

For time being, I am guessing the table and column names and providing you the queries. Hence, you might need to change the names accordingly in order to make them wrong. In case you need any help, reach out to me via comments section.

1. SELECT count(DISTINCT manufacturer), max(unitPrice), min(unitPrice) FROM Products;

2. SELECT avg(sales) FROM SalesRep WHERE work_location = 'Home';

3. SELECT CustNum, sum (amount) FROM Customer c JOIN Orders o ON c.CustNum = o.CustNum GROUP BY o.CustNum;

NOTE: As per Chegg policy, I am allowed to answer only 3 questions (including sub-parts) on a single post. Kindly post the remaining questions separately and I will try to answer them. Sorry for the inconvenience caused.