Create 4 database schemas (constructco, fact, saleco, and largeco). For the homework you will submit a SINGLE SQL script. To use each of the database schema you need to set the schema that your following statements are to work on. The statement is "USE CONSTRUCTCO;" to access the tables in the constructco schema. Make sure to place your name in the script and the question numbers commented out. Use the Fact schema (Page 341 in the book) for the following questions

1. Write a query to display the Product SKU, Product description and the Product Price as US currency (2 decimal points) sorted by Product Price. Label your column as SKU, Description and Price.
2. Write a query to display the last name, first name, title, department number for all employees who were hired during the calendar year of 2015. Sort the output by department number, last name, and first name.
3. Write a query to display the vender ID, name and Product SKU for ALL vendors, including those who do NOT provide us with products currently.
4. Write a query to display the vender ID and vender name for those vendors who do not currently supply us with products.
5. Write a query to display the employee number, last name, title, email and department name of each employee whose job title ends in the word “ASSOCIATE” (case insensitive). Sorted by department number and employee title.
6. Write a query to display the brand ID, brand name, brand type, and average price of product for the brand that has the largest average product price.
7.   

Transcribed Image Text:

CHECKOUT PK Check Num FK1 Book_Num FK2 Pat_ID Check Out Date Check Due Date Check_In_Date BOOK PK Book Num Book Title Book Year Book_Cost Book Subject FK1 Pat_ID ----H- PATRON PK Pat ID Pat FName Pat LName Pat_Type WRITES PK,FK1 Book Num PK,FK2 Au ID AUTHOR PK AU ID Au FName Au_LName Au_BirthYear CHECKOUT PK Check Num FK1 Book_Num FK2 Pat_ID Check Out Date Check Due Date Check_In_Date BOOK PK Book Num Book Title Book Year Book_Cost Book Subject FK1 Pat_ID ----H- PATRON PK Pat ID Pat FName Pat LName Pat_Type WRITES PK,FK1 Book Num PK,FK2 Au ID AUTHOR PK AU ID Au FName Au_LName Au_BirthYear

Answer rating: 100% (QA)

Solution Homework SQL Script by Your Name Question 1 Create the constructco schema and tables CREATE SCHEMA constructco USE constructco CREATE TABLE P... View the full answer