Utilizing the Holt Distributors database design below, write MS SQL Server statements that will accurately and efficiently complete the following requests:

1. Create each table with the correct attributes and keys as indicated in the Entity Listing

2. Load at least one row in every table.

3. Load only "Mary" as Customer Name and required fields in the Customer Table.

4. Change the name from "Mary" to "Michelle" for the customer entered in #3.

5. Add Chicago for the City and 62201 as the Zip to the customer entered in #3.

6. Remove the customer entered in #3.

7. Change the commission rate for all sales reps to 17%.

Holt Distributors Database Design ER Diagram

Entity Listing

Territory (Territory Number, Territory Name, Region, Quota)

Sales Rep (EmployeeID, MTD Sales, YTD Sales, MTD Commission, YTD Commission,Commission Rate, Territory Number) FK Territory Number ? Territory FK EmployeeID ? Employee

Employee (EmployeeID, EmpName,EmpAddress1, EmpAdress2,EmpCity,EmpState,EmpZip,EmpPhone)

Customer (Customer Number, FName, LName, Address1, Address2, City, State, Zip Code, PhoneNum, MTD Sales, YTD Sales, Current Balance, Credit Limit, Ship Name, Ship Address1, Ship Address2, Ship City, Ship State, Ship Zip Code, Current Invoice Total, Current Payment Total, Current Amount, Over 30, Over 60, Over 90, Previous Balance, EmployeeID) FK EmployeeID ? Sales Rep

Part ( Part Number,Vendor Number,, Part Description, Unit Price, MTD Sales, YTD Sales, Units On Hand, Units Allocated, Reorder Point, Vendor Price, Minimum Order Quantity, Expected Lead Time)FK Vendor Number ? Vendor

Vendor (Vendor Number, Name, Address, City, State, Zip Code,Phonenum,Credit_limit)

Order (Order Number, Order Date, Customer Number, Customer PO Number, Order Total, Order Status, Order Special Charges, Order Special Charge Description, ShipName,ShipAddress1,ShipAdress2,ShipCity,ShipState,ShipZIp) FK Customer Number ? Customer

OrderDetail (Order Number, SEQ Number, Part Number, Vendor Number, NumberOrdered, Quoted Price, Line Total, Comments) FK Order Number ? Order FK Part Number, Vendor Number? Part

Invoice (Invoice Number, Ship Date, Freight, Ship Status)

InvoiceDetail (Invoice Number, Order Number, SEQ Number, QuantityShipped) FK Order Number, SEQ Number? OrderDetail FK Invoice Number ? Invoice

Payment (Payment Number, Customer Number, Payment Date, Amount) FK Customer Number ? Customer

Transcribed Image Text:

Employee Sales Rep Customer 1 Payment M 1 1 M Territory Order MT Order Detail M Invoice Detail M Invoice M 1 Part M Vendor Employee Sales Rep Customer 1 Payment M 1 1 M Territory Order MT Order Detail M Invoice Detail M Invoice M 1 Part M Vendor

Answer rating: 100% (QA)

CREATE TABLE statement is used to create a new table in a database Syntax CREATE TABLE tablename column1 datatype column2 datatype Constraints Primary Key constraint uniquely identifies each record in ... View the full answer