Assignment: Business Database Technology- Structured Query Language - SQL
Single Table Queries
A. List material ID, name, material, standard price, and thickness for raw materials made of cherry, pine, or walnut. Order the list by material, standard price, and thickness
B. Display the product line ID and the average standard price for all products in each product line ID.
C. List customer ID and total orders placed by each customer in 2018.
D. For every territory having more than one salesperson, display SalesTerritoryID and the number of salespersons in the territory.
E. What is the least expensive product finish? (Note: Measure it using the average product standard price. Order the results ascendingly so easily the least expensive product can be identified.)
F. List each vendor's minimum supply unit price except vendorID 3.
G. For each product ID (except for product IDs 6 and 18), list all materials (material ID), the required quantity of each material used to create the product, and whose required quantity is greater than 3.
H. List ProductID, ProductDescription, ProductFinish, and ProductStandardPrice for Oak products with a price > 400 or Cherry products with a price < 300 Page 2 of 2
Multiple Table Queries
I. List product line names, the number of products in each of the product lines, and the average of product standard prices.
J. List the names and number of employees supervised (label this value HeadCount) for all the supervisors who supervise more than one employee.
K. List each customer (ID and name) who has bought computer desks and the number of units bought by each customer.
L. List all work center IDs and the locations where the company has an employee(s) with skill "QC1".
M. Show the total cost (measured by multiplying quantity and price) of orderID 1.
N. List the product ID, description, and standard price of all products that cost less than the average product price in their product line.
O. List the employee ID and employee name for those employees who do not possess the skill Router. Display the results in order by EmployeeName.
P. Display the salesperson's name, product finish, and total quantity sold (label as TotSales) for each finish by each salesperson.