Utilizing the Holt Distributors database design below, write MS SQL Server statements that will accurately and efficiently
Question:
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
Database Systems Design Implementation and Management
ISBN: 978-1285196145
11th edition
Authors: Carlos Coronel, Steven Morris