Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

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. CHECKOUT PK Check Num FK1 Book Num FK2 Pat_ID Check Out Date Check Due Date Check_in_Date BOOK PK Book Num LGCUSTOMER PK Cust Code Cust Frame Cust Lnanse Cust Street Cust City Cust State Cust ZIP Cust Balance 

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

Step by Step Solution

There are 3 Steps involved in it

Step: 1

Solution Homework SQL Script by Your Name Question 1 Create the constructco schema and tables CREATE SCHEMA constructco USE constructco CREATE TABLE P... blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Database Concepts

Authors: David M. Kroenke, David J. Auer

7th edition

133544621, 133544626, 0-13-354462-1, 978-0133544626

More Books

Students also viewed these Databases questions

Question

How does original markup differ from maintained markup? AppendixLO1

Answered: 1 week ago

Question

write an overview about Tableau(Software) learned about 1-2 pages.

Answered: 1 week ago

Question

Define project scope.

Answered: 1 week ago

Question

How do you create and run an SQL query in MySQL?

Answered: 1 week ago