Query writing and relational algebra Expression
Write the following sql queries using the Cape Codd DB.
1. Write a SQL statement to show sku and Description for all products having a SKU description that includes the word ‘foot'.
2. Write a SQL statement to display the warehouse and a count of QuantityOnHand grouped by warehouse.
3. Write a SQL statement to show the SKU and SKU_Description for all items stored in a warehouse managed by each manager.
4. Find out the total revenue collected by each store.
5. Which department is associated with the highest revenue?
6. Which department is associated with the lowest revenue?
7. Find out the storenumber and zipcode of the store that sold the maximum number of products.
8. Find out other warehouses whose average quantity on hand is not smaller than That of Atlanta warehouse.
9. List sku's and descriptions for all products whose sku starts with a "2". Use the BETWEEN operator. You may assume all sku's have 6 digits.
10. List the warehouses that currently have on average less than 225 items in stock of the products they carry. Use the HAVING operator.
11. List all order items for products currently out of stock in Atlanta. Use a subquery.
12. Get a list of buyers and their departments for any products out of stock (at any warehouse, not all warehouses). Use a join.
13. Get a list of departments and the total items on order for each department, sorted from largest to smallest quantity. Use a join.
Use the PC Manufacturer sample DB to write these additional queries as well as relational algebra expression.
1. Find the model number and price of all products (of any type) made by maker B.
2. Find those makers that sell laptops, not PCs.
3. Find those makers of at least two different computers.
All queries should be contained in a single plain text file with a sql extension. Queries must be in the order above. Don't forget to separate each query with a semi-colon.