You have relation Orders(OrderID, CustomerID, NumberOfItems, ItemsTotal), Custom

Question

You have relation Orders(OrderID, CustomerID, NumberOfItems, ItemsTotal), Customer(CID, Name, State), StateTax(State, SalesTaxRate).

a) Create SQL-DDL to create these tables (with foreign keys: CustomerID -> CID) and fill them with a small number of records. E.g. in State you could have ('Illinois', 6.25) and ('Michigan', 6)

b) Write a query that lists all orders (by OrderID) and the expected price the customer has to pay. Calculate the expected price according to the following rules

The product cost of all items in the order together is ItemsTotal.

To this you need to add shipping costs; shipping costs are calculated as follows: each item by itself costs $0.99 in shipping. Items are bundled in shipments containing (up to) 4 items. Each shipment is $3.99. So two items incur a cost of $5.97 = 2*$0.99 + $3.99, and 5 items $12.93 = 5*$0.99 + 2*$3.99.)

Tax is calculated based on ItemsTotal. Everybody pays a basic sales tax of 4%. We also collect tax based on the customer's state's tax-rate (as recorded in StateTax) . So an Illinois customer pays an additional 6.25% on ItemsTotal (for a total of 10.25% in sales tax) and a Michigan customer 6% (a total of 10%).

c) Improve the query to deal with the following situation: Sometimes our order records are incomplete (i.e. contain null values). If we don't have ItemsTotal, the query should not get an expected price for the order (i.e. it should be null). If NumberOfItems is missing, we assume it is 1. If there's a null entry for a state's sales tax in the table we assume it is 0. (Hint: Recall the coalesce function). Hand in your solution of c) separately from the solution to b).

Explanation / Answer

Solution:

Note: The first question is done as per Chegg guidelines, please repost others.?

a)

CREATE TABLE Orders(
OrderID int NOT NULL,
CustomerID varchar(20);

NumberOfItems varchar(20);

ItemsTotal int(20)
    PRIMARY KEY (OrderID)
);

CREATE TABLE Customer(
CID int NOT NULL,
Name varchar(20),
State varchar(20),
PRIMARY KEY (CID),
);

CREATE TABLE StateTax(
    State varchar,
StateTax double,

);

The given values are not enough to insert them into the database.

please use the below queries to insert values

INSERT INTO table_name(columnName, ...)

VALUES(value1, value2, ....)

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.