The attached figure shows a relational schema of a grocery store. This is from a

Question

The attached figure shows a relational schema of a grocery store. This is from an Access database. Notice similarities and differences in the notations: primary key is indicated with a yellow key symbol and foreign key is indicated with the infinity sign instead of (FK). Study the structure and answer the following questions:

Create a star schema for a data warehouse which can be used to analyze units sold for daily orders of each customer.

Write CREATE SQL statements to create the above data warehouse.

Write INSERT INTO ... VALUES to populate each table in the above data warehouse with at least two records.

Write the following SELECT query: Display total units sold for each month. Output must have two columns: Total Units Sold and Month

Write the following SELECT query: Display total units sold for each customer. Output must have two columns: Total Units Sold and Customer Last name

Write a series of DROP SQL statements that will erase the data warehouse.

Customers Customer ID Products Last Name First Name Product ID Product Name Product Quantity Product Price Orders ddress City State Zip Order ID Customer O Date Order Line Item Order ID Product ID Quantity Email Address Credit Card

Explanation / Answer

Creating customer table:

CREATE TABLE Customers (
CustomerID int NOT NULL,
FirstName VARCHAR(50),
LastName VARCHAR(50),
address VARCHAR(50),
City VARCHAR(50),
state VARCHAR(50),
Zip int NOT NULL,
Pone NUMBER,
Email Address VARCHAR(50),
Credit Card Details VARCHAR(50),
PRIMARY KEY (CustomerID),
  
);

Inserting values:

INSERT INTO Customers VALUES (101, 'Mustafa','shaik','1-45/153- varahagitri nagar','Vizag', 'AP', 533045,8106060680, 'musthaffamail,'HDFC' ...);
INSERT INTO Customers VALUES (101, 'Hari','Edava','1-45/153- yendada','Vizag', 'AP', 533065,8106060450, 'musharimail.com','HDFC' ...);

CREATE TABLE Orders (
OrderID int NOT NULL,
CustomerID int NOT NULL,
Date DATE
PRIMARY KEY (OrderID),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
INSERT INTO Orders VALUES (1001,101,13-8-2017);
INSERT INTO Orders VALUES (1002,102,13-8-2017);

CREATE TABLE OrdersLineItem (
OrderID int NOT NULL,
ProductID int NOT NULL,
Quantity INT,
PRIMARY KEY (OrderID),
FOREIGN KEY (ProductID) REFERENCES Persons(ProductID)  
);

INSERT INTO OrdersLineItem VALUES (1001,201,450);
INSERT INTO OrdersLineItem VALUES (1002,202,790);

CREATE TABLE Products (
ProductID int NOT NULL,
Product Name VARCHAR(50),
Product Quantity INT,
PRIMARY KEY (ProductID),
  
);

INSERT INTO Products VALUES (201,paste,2067);
INSERT INTO Products VALUES (1002,salt,5890);

Display total units sold for each customer. Output must have two columns: Total Units Sold and Customer Last name?
SELECT CustomerID,
COUNT(OrderID) "Total Units Sold ",
FROM Orders
GROUP BY Orders;

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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