Background on SQL Server: Microsoft SQL Server is a popular enterprise-class DBMS that has been around for many years. SQL Server really handles SQL well, better than simpler tools such as Microsoft Access. You will find that there are several different installation packages available. The base version of SQL Server is a database engine without a graphical interface. SQL Server Management Studio is the user-friendly, graphical interface management utility that runs along with SQL Server. 1 The Management Studio is the particular application that we will be using in this course. Use the instructor-provided instructions to install SQL Server Express and SQL Server Management Studio on your computer. Otherwise, use a Mihaylo lab computer or http://vcl.fullerton.edu. Instructions: To load the database to complete this assignment, you must first do the following: (If you are in an online class, watch the intro to SQL Server video for more information on these steps. If you are in a regular class, the instructor should have demonstrated how to open Management Studio and run SQL scripts.) Open SQL Server Management Studio. Create a new database (in Object Explorer on left, right-click and select New Database) called QACS. Open the QACS-Create-Tables .sql file shared by your instructor. Execute the SQL statement to create the tables. Open the QACS-Insert-Data .sql file shared by your instructor. Execute the SQL statement to populate the tables. See the last page of this document for a diagram of the database that you have just imported to SQL Server. This should help you, particularly on the questions where youll need to join tables together. Please work through the examples in the textbook before attempting this homework. Please read carefully! Create SQL statements for each of the following. Make sure to test each file (using the Parse button) before executing it. When you are done, copy and paste all of your SQL statements into a single query window in SQL Server. Make sure the statements are in the same order as the questions listed here, and save your work as a .sql script. Submit your .sql script on TITANium. If TITANium does not allow you to upload a .sql file, then right-click the file, choose Send to and then click Zipped Folder and upload the zipped folder instead.

/********************************************************************************/

/* */

/* Kroenke and Auer - Database Concepts (7th Edition) Chapter 03 */

/* */

/* The Queen Anne Curiousity Shop Database Create Tables */

/* */

/* These are the Microsoft SQL Server 2012/2014 SQL code solutions */

/* */

/********************************************************************************/

/* */

/* NOTE: These SQL Statements use the following surrogate keys: */

/* */

/* CUSTOMER: Start at 1, Increment by 1 - IDENTITY (1, 1) */

/* EMPLOYEE: Start at 1, Increment by 1 - IDENTITY (1, 1) */

/* VENDOR: Start at 1, Increment by 1 - IDENTITY (1, 1) */

/* ITEM: Start at 1, Increment by 1 - IDENTITY (1, 1) */

/* SALE: Start at 1, Increment by 1 - IDENTITY (1, 1) */

/* */

/********************************************************************************/

USE QACS

GO

CREATE TABLE CUSTOMER(

CustomerID Int NOT NULL IDENTITY (1, 1),

LastName Char(25) NOT NULL,

FirstName Char(25) NOT NULL,

[Address] Char(35) NULL,

City Char(35) NULL,

[State] Char(2) NULL,

ZIP Char(10) NULL,

Phone Char(12) NOT NULL,

Email VarChar(100) NULL,

CONSTRAINT CUSTOMER_PK PRIMARY KEY(CustomerID)

);

CREATE TABLE EMPLOYEE(

EmployeeID Int NOT NULL IDENTITY (1, 1),

LastName Char(25) NOT NULL,

FirstName Char(25) NOT NULL,

Phone Char(12) NULL,

Email VarChar(100) NOT NULL UNIQUE,

CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EmployeeID)

);

CREATE TABLE VENDOR(

VendorID Int NOT NULL IDENTITY (1, 1),

CompanyName Char(100) NULL,

ContactLastName Char(25) NOT NULL,

ContactFirstName Char(25) NOT NULL,

Address Char(35) NULL,

City Char(35) NULL,

State Char(2) NULL,

ZIP Char(10) NULL,

Phone Char(12) NOT NULL,

Fax Char(12) NULL,

Email VarChar(100) NULL,

CONSTRAINT VENDOR_PK PRIMARY KEY(VendorID)

);

/***** ITEM Table As Used in Chapter03 **************************************/

CREATE TABLE ITEM(

ItemID Int NOT NULL IDENTITY (1,1),

ItemDescription VarChar(255) NOT NULL,

PurchaseDate Date NOT NULL,

ItemCost Numeric(9,2) NOT NULL,

ItemPrice Numeric(9,2) NOT NULL,

VendorID Int NOT NULL,

CONSTRAINT ITEM_PK PRIMARY KEY (ItemID),

CONSTRAINT ITEM_VENDOR_FK FOREIGN KEY (VendorID)

REFERENCES VENDOR(VendorID)

ON UPDATE NO ACTION

ON DELETE NO ACTION

);

CREATE TABLE SALE(

SaleID Int NOT NULL IDENTITY (1, 1),

CustomerID Int NOT NULL,

EmployeeID Int NOT NULL,

SaleDate Date NOT NULL,

SubTotal Numeric(15,2) NULL,

Tax Numeric(15,2) NULL,

Total Numeric(15,2) NULL,

CONSTRAINT SALE_PK PRIMARY KEY (SaleID),

CONSTRAINT SALE_CUSTOMER_FK FOREIGN KEY (CustomerID)

REFERENCES Customer(CustomerID)

ON UPDATE NO ACTION

ON DELETE NO ACTION,

CONSTRAINT SALE_EMPLOYEE_FK FOREIGN KEY(EmployeeID)

REFERENCES Employee(EmployeeID)

ON UPDATE NO ACTION

ON DELETE NO ACTION

);

/***** SALE_ITEM Table As Used in Chapter03 *********************************/

CREATE TABLE SALE_ITEM(

SaleID Int NOT NULL,

SaleItemID Int NOT NULL,

ItemID Int NOT NULL,

ItemPrice Numeric(9,2) NOT NULL,

CONSTRAINT SALE_ITEM_PK PRIMARY KEY (SaleID, SaleItemID),

CONSTRAINT SALE_ITEM_SALE_FK FOREIGN KEY (SaleID)

REFERENCES SALE(SaleID)

ON UPDATE NO ACTION

ON DELETE CASCADE,

CONSTRAINT SALE_ITEM_ITEM_FK FOREIGN KEY (ItemID)

REFERENCES ITEM(ItemID)

ON UPDATE NO ACTION

ON DELETE NO ACTION

);

/********************************************************************************/

/********************************************************************************/

/* */

/* Kroenke and Auer - Database Concepts (7th Edition) Chapter 03 */

/* */

/* The Queen Anne Curiousity Shop [QACS] Database Data */

/* */

/* These are the Microsoft SQL Server 2012/2014 SQL code solutions */

/* */

/********************************************************************************/

USE QACS

GO

/***** CUSTOMER DATA ********************************************************/

/***** The first 6 items are based on data in Figures in Chapter 02 ***********/

INSERT INTO CUSTOMER VALUES(

'Shire', 'Robert', '6225 Evanston Ave N', 'Seattle', 'WA', '98103',

'206-524-2433', 'Robert.Shire@somewhere.com');

INSERT INTO CUSTOMER VALUES(

'Goodyear', 'Katherine', '7335 11th Ave NE', 'Seattle', 'WA', '98105',

'206-524-3544', 'Katherine.Goodyear@somewhere.com');

INSERT INTO CUSTOMER VALUES(

'Bancroft', 'Chris', '12605 NE 6th Street', 'Bellevue', 'WA', '98005',

'425-635-9788', 'Chris.Bancroft@somewhere.com');

INSERT INTO CUSTOMER VALUES(

'Griffith', 'John', '335 Aloha Street', 'Seattle', 'WA', '98109',

'206-524-4655', 'John.Griffith@somewhere.com');

INSERT INTO CUSTOMER VALUES(

'Tierney', 'Doris', '14510 NE 4th Street', 'Bellevue', 'WA', '98005',

'425-635-8677', 'Doris.Tierney@somewhere.com');

INSERT INTO CUSTOMER VALUES(

'Anderson', 'Donna', '1410 Hillcrest Parkway', 'Mt. Vernon', 'WA', '98273',

'360-538-7566', 'Donna.Anderson@elsewhere.com');

INSERT INTO CUSTOMER VALUES(

'Svane', 'Jack', '3211 42nd Street', 'Seattle', 'WA', '98115',

'206-524-5766', 'Jack.Svane@somewhere.com');

INSERT INTO CUSTOMER VALUES(

'Walsh', 'Denesha', '6712 24th Avenue NE', 'Redmond', 'WA', '98053',

'425-635-7566', 'Denesha.Walsh@somewhere.com');

INSERT INTO CUSTOMER VALUES(

'Enquist', 'Craig', '534 15th Street', 'Bellingham', 'WA', '98225',

'360-538-6455', 'Craig.Enquist@elsewhere.com');

INSERT INTO CUSTOMER VALUES(

'Anderson', 'Rose', '6823 17th Ave NE', 'Seattle', 'WA', '98105',

'206-524-6877', 'Rose.Anderson@elsewhere.com');

/***** EMPLOYEE DATA ********************************************************/

/***** The first ZERO items are based on data in Figures in Chapter 02 ********/

INSERT INTO EMPLOYEE VALUES(

'Stuart', 'Anne', '206-527-0010', 'Anne.Stuart@QACS.com');

INSERT INTO EMPLOYEE VALUES(

'Stuart', 'George', '206-527-0011', 'George.Stuart@QACS.com');

INSERT INTO EMPLOYEE VALUES(

'Stuart', 'Mary', '206-527-0012', 'Mary.Stuart@QACS.com');

INSERT INTO EMPLOYEE VALUES(

'Orange', 'William', '206-527-0013', 'William.Orange@QACS.com');

INSERT INTO EMPLOYEE VALUES(

'Griffith', 'John', '206-527-0014', 'John.Griffith@QACS.com');

/***** VENDOR DATA **********************************************************/

/***** The first 6 items are based on data in Figures in Chapter 02 ***********/

INSERT INTO VENDOR VALUES(

'Linens and Things', 'Huntington', 'Anne', '1515 NW Market Street',

'Seattle', 'WA', '98107', '206-325-6755', '206-329-9675', 'LAT@business.com');

INSERT INTO VENDOR VALUES(

'European Specialties', 'Tadema', 'Ken', '6123 15th Avenue NW',

'Seattle', 'WA', '98107', '206-325-7866', '206-329-9786', 'ES@business.com');

INSERT INTO VENDOR VALUES(

'Lamps and Lighting', 'Swanson', 'Sally', '506 Prospect Street',

'Seattle', 'WA', '98109', '206-325-8977', '206-329-9897', 'LAL@business.com');

INSERT INTO VENDOR

(ContactLastName, ContactFirstName, Address, City, State, ZIP, Phone, Email)

VALUES(

'Lee', 'Andrew', '1102 3rd Street',

'Kirkland', 'WA', '98033', '425-746-5433', 'Andrew.Lee@somewhere.com');

INSERT INTO VENDOR

(ContactLastName, ContactFirstName, Address, City, State, ZIP, Phone, Email)

VALUES(

'Harrison', 'Denise', '533 10th Avenue',

'Kirkland', 'WA', '98033', '425-746-4322', 'Denise.Harrison@somewhere.com');

INSERT INTO VENDOR VALUES(

'New York Brokerage', 'Smith', 'Mark', '621 Roy Street',

'Seattle', 'WA', '98109', '206-325-9088', '206-329-9908', 'NYB@business.com');

INSERT INTO VENDOR

(ContactLastName, ContactFirstName, Address, City, State, ZIP, Phone, Email)

VALUES(

'Walsh', 'Denesha', '6712 24th Avenue NE',

'Redmond', 'WA', '98053', '425-635-7566', 'Denesha.Walsh@somewhere.com');

INSERT INTO VENDOR

(ContactLastName, ContactFirstName, Address, City, State, ZIP, Phone, Fax, Email)

VALUES(

'Bancroft', 'Chris', '12605 NE 6th Street',

'Bellevue', 'WA', '98005', '425-635-9788', '425-639-9978', 'Chris.Bancroft@somewhere.com');

INSERT INTO VENDOR VALUES(

'Specialty Antiques', 'Nelson', 'Fred', '2512 Lucky Street',

'San Francisco', 'CA', '94110', '415-422-2121', '415-429-9212', 'SA@business.com');

INSERT INTO VENDOR VALUES(

'General Antiques', 'Garner', 'Patty', '2515 Lucky Street',

'San Francisco', 'CA', '94110', '415-422-3232', '415-429-9323', 'GA@business.com');

/***** ITEM DATA ************************************************************/

/***** ITEM Table As Used in Chapter03 **************************************/

/***** The first 11 items are based on data in Figures in Chapter02 ***********/

INSERT INTO ITEM VALUES(

'Antique Desk', '07-Nov-13', 1800.00, 3000.00, 2);

INSERT INTO ITEM VALUES(

'Antique Desk Chair', '10-Nov-13', 300.00, 500.00, 4);

INSERT INTO ITEM VALUES(

'Dining Table Linens', '14-Nov-13', 600.00, 1000.00, 1);

INSERT INTO ITEM VALUES(

'Candles', '14-Nov-13', 30.00, 50.00, 1);

INSERT INTO ITEM VALUES(

'Candles', '14-Nov-13', 27.00, 45.00, 1);

INSERT INTO ITEM VALUES(

'Desk Lamp', '14-Nov-13', 150.00, 250.00, 3);

INSERT INTO ITEM VALUES(

'Dining Table Linens', '14-Nov-13', 450.00, 750.00, 1);

INSERT INTO ITEM VALUES(

'Book Shelf', '21-Nov-13', 150.00, 250.00, 5);

INSERT INTO ITEM VALUES(

'Antique Chair', '21-Nov-13', 750.00, 1250.00, 6);

INSERT INTO ITEM VALUES(

'Antique Chair', '21-Nov-13', 1050.00, 1750.00, 6);

INSERT INTO ITEM VALUES(

'Antique Candle Holders', '28-Nov-13', 210.00, 350.00, 2);

INSERT INTO ITEM VALUES(

'Antique Desk', '05-Jan-14', 1920.00, 3200.00, 2);

INSERT INTO ITEM VALUES(

'Antique Desk', '05-Jan-14', 2100.00, 3500.00, 2);

INSERT INTO ITEM VALUES(

'Antique Desk Chair', '06-Jan-14', 285.00, 475.00, 9);

INSERT INTO ITEM VALUES(

'Antique Desk Chair', '06-Jan-14', 339.00, 565.00, 9);

INSERT INTO ITEM VALUES(

'Desk Lamp', '06-Jan-14', 150.00, 250.00, 10);

INSERT INTO ITEM VALUES(

'Desk Lamp', '06-Jan-14', 150.00, 250.00, 10);

INSERT INTO ITEM VALUES(

'Desk Lamp', '06-Jan-14', 144.00, 240.00, 3);

INSERT INTO ITEM VALUES(

'Antique Dining Table', '10-Jan-14', 3000.00, 5000.00, 7);

INSERT INTO ITEM VALUES(

'Antique Sideboard', '11-Jan-14', 2700.00, 4500.00, 8);

INSERT INTO ITEM VALUES(

'Dining Table Chairs', '11-Jan-14', 5100.00, 8500.00, 9);

INSERT INTO ITEM VALUES(

'Dining Table Linens', '12-Jan-14', 450.00, 750.00, 1);

INSERT INTO ITEM VALUES(

'Dining Table Linens', '12-Jan-14', 480.00, 800.00, 1);

INSERT INTO ITEM VALUES(

'Candles', '17-Jan-14', 30.00, 50.00, 1);

INSERT INTO ITEM VALUES(

'Candles', '17-Jan-14', 36.00, 60.00, 1);

/***** SALE DATA ************************************************************/

/***** The first 9 items are based on data in Figures in Chapter 02 ***********/

INSERT INTO SALE VALUES(1, 1, '14-Dec-13', 3500.00, 290.50, 3790.50);

INSERT INTO SALE VALUES(2, 2, '15-Dec-13', 1000.00, 83.00, 1083.00);

INSERT INTO SALE VALUES(3, 1, '15-Dec-13', 50.00, 4.15, 54.15);

INSERT INTO SALE VALUES(4, 3, '23-Dec-13', 45.00, 3.74, 48.74);

INSERT INTO SALE VALUES(1, 5, '05-Jan-14', 250.00, 20.75, 270.75);

INSERT INTO SALE VALUES(5, 5, '10-Jan-14', 750.00, 62.25, 812.25);

INSERT INTO SALE VALUES(6, 4, '12-Jan-14', 250.00, 20.75, 270.75);

INSERT INTO SALE VALUES(2, 1, '15-Jan-14', 3000.00, 249.00, 3249.00);

INSERT INTO SALE VALUES(5, 5, '25-Jan-14', 350.00, 29.05, 379.05);

INSERT INTO SALE VALUES(7, 1, '04-Feb-14', 14250.00, 1182.75, 15432.75);

INSERT INTO SALE VALUES(8, 5, '04-Feb-14', 250.00, 20.75, 270.75);

INSERT INTO SALE VALUES(5, 4, '07-Feb-14', 50.00, 4.15, 54.15);

INSERT INTO SALE VALUES(9, 2, '07-Feb-14', 4500.00, 373.50, 4873.50);

INSERT INTO SALE VALUES(10, 3, '11-Feb-14', 3675.00, 305.03, 3980.03);

INSERT INTO SALE VALUES(2, 2, '11-Feb-14', 800.00, 66.40, 866.40);

/***** SALE_ITEM DATA *******************************************************/

/***** SALE_ITEM Table As Used in Chapter03 *********************************/

/***** The first 11 items are based on data in Figures in Chapter03 ***********/

INSERT INTO SALE_ITEM VALUES(1, 1, 1, 3000.00);

INSERT INTO SALE_ITEM VALUES(1, 2, 2, 500.00);

INSERT INTO SALE_ITEM VALUES(2, 1, 3, 1000.00);

INSERT INTO SALE_ITEM VALUES(3, 1, 4, 50.00);

INSERT INTO SALE_ITEM VALUES(4, 1, 5, 45.00);

INSERT INTO SALE_ITEM VALUES(5, 1, 6, 250.00);

INSERT INTO SALE_ITEM VALUES(6, 1, 7, 750.00);

INSERT INTO SALE_ITEM VALUES(7, 1, 8, 250.00);

INSERT INTO SALE_ITEM VALUES(8, 1, 9, 1250.00);

INSERT INTO SALE_ITEM VALUES(8, 2, 10, 1750.00);

INSERT INTO SALE_ITEM VALUES(9, 1, 11, 350.00);

INSERT INTO SALE_ITEM VALUES(10, 1, 19, 5000.00);

INSERT INTO SALE_ITEM VALUES(10, 2, 21, 8500.00);

INSERT INTO SALE_ITEM VALUES(10, 3, 22, 750.00);

INSERT INTO SALE_ITEM VALUES(11, 1, 17, 250.00);

INSERT INTO SALE_ITEM VALUES(12, 1, 24, 50.00);

INSERT INTO SALE_ITEM VALUES(13, 1, 20, 4500.00);

INSERT INTO SALE_ITEM VALUES(14, 1, 12, 3200.00);

INSERT INTO SALE_ITEM VALUES(14, 2, 14, 475.00);

INSERT INTO SALE_ITEM VALUES(15, 1, 23, 800.00);

/********************************************************************************/

1. Write an SQL statement to list all columns for customers with a Seattle address.

2. Write an SQL statement to list all cities where customers live. Make sure the result does not include duplicates.

3. Write an SQL statement to list the first and last name of all employees who have the last name of Stuart or the first name of John.

4. Write an SQL statement to list the item description and item cost of all items with a cost between $595 and $705.

5. Write an SQL statement to list the company names and fax numbers of all vendors who have a fax number listed in the table. Sort the result in descending order by company name.

6. Write an SQL statement to list the minimum, maximum, and average item price of all items with an item cost greater than $2000.

7. Write an SQL statement to count the number of items that have a price above $4000. 1 If you choose to install SQL Server on your own machine, make sure that you download the Management Studio in addition to the base SQL Server software.

8. Write an SQL statement to list the item description of all items whose description starts with Antique.

9. Write an SQL statement to list ItemID and Description for all items that cost $1000 or more and were purchased from a vendor whose CompanyName starts with the letters New.

10.Using comment notation, write a comment stating My name is (fill in the blank) and I did not copy/paste my answers from anyone else.

11.(2 pts) Write an SQL statement to list LastName, FirstName, and Phone of the customer who made the purchase with SaleID 1. Use a subquery.

12.(2 pts) Answer the previous question but use a join using JOIN ON syntax instead of a subquery.

13.(2 pts) Write an SQL statement to list LastName, FirstName, and Phone of the customers who made the purchase with SaleIDs 1, 2, and 3. Use a subquery.

14.(2 pts) Write an SQL statement to list LastName, FirstName, and Phone of the customers who made the purchase with SaleIDs 1, 2, and 3. Use a join using JOIN ON syntax.

15.(2 pts) Write an SQL statement to show the sum of SubTotal for each customer. List CustomerID, LastName, FirstName, Phone, and the calculated result. Name the sum of SubTotal as SumOfSubTotal and sort the results by CustomerID, in descending order.

Extra question (more difficult) for potential bonus point. Please note that on this assignment, it is not possible to earn more than 100%. If you do the problem below correctly, you will get an extra point only to make up for any point missed on a question above.

16.Write an SQL statement to show all items and the customers who have purchased them. The output from this statement should include items (if any) that have not been purchased by a customer. The SQL statement output should list CustomerID, LastName, FirstName, SaleID, SaleItemID, ItemID, and ItemDescription.