Hello, I am working on a database using SQL, in the Microsoft SQL Server 2014 Ma
ID: 3596068 • Letter: H
Question
Hello,
I am working on a database using SQL, in the Microsoft SQL Server 2014 Management.
I have created seven tables
CREATE TABLE [Book Store]
(
Store_ID numeric(3,0) not null PRIMARY KEY, “Store ID”
Store_Add Varchar(40) not null, “Store Address”
Store_Phone numeric(10,0) not null
)
CREATE TABLE Book
(
ISBN numeric (10,0) NOT NULL PRIMARY KEY, “Book serial number”
Book_Title varchar (MAX) NOT NULL,
Auth_FName varchar (40) NOT NULL,
Auth_LName varchar (40) NOT NULL, “Authors last name”
Book_Pub varchar (70) NOT NULL, “Publisher”
Book_Genre varchar (30) NOT NULL,
Book_Price money NOT NULL
)
CREATE TABLE Customers
(
Cust_ID numeric (5,0) not null PRIMARY KEY,
Cust_FName varchar (20) not null,
Cust_LName varchar (30) not null,
Cust_Add varchar (60) not null,
Cust_Zip numeric (5,0) not null,
Cust_Email varchar (30) not null
)
CREATE TABLE Employees
(
Emp_ID numeric (3,0) not null PRIMARY KEY,
Emp_FName varchar (20) not null,
Emp_LName varchar (20) not null,
Emp_Add varchar (60) not null,
Emp_Phone numeric (10,0) not null,
Emp_Email varchar (30) not null,
Emp_NCSD date not null
)
CREATE TABLE [Order Detail]
(
Order_ID numeric (10,0) not null PRIMARY KEY,
Order_Date date not null,
Cust_ID numeric (5,0) not null FOREIGN KEY REFERENCES Customers(Cust_ID),
Prod_ID varchar (10) not null FOREIGN KEY REFERENCES Products (Prod_ID),
Emp_ID numeric (3,0) not null FOREIGN KEY REFERENCES Employees(Emp_ID),
Store_ID numeric (3,0) not null FOREIGN KEY REFERENCES [Book Store] (Store_ID)
)
CREATE TABLE Products
(
Prod_ID varchar (10) not null PRIMARY KEY, “Product ID”
Prod_Detail varchar (80) not null,
Prod_Qty numeric (5,0) not null,
Prod_Price money not null
)
CREATE TABLE Transactions
(
Trax_ID numeric(10,0) not null PRIMARY KEY, “Transaction ID”
Pay_Type varchar(30) not null,
Order_ID numeric(10,0) not null FOREIGN KEY REFERENCES [Order Detail] (Order_ID)
)
Please use the record names in the tables above for the queries.
Please use the Column names in the tables above for the queries.
The Values of sample data to make it easier for you to cut and paste.
Sample Data:
INSERT INTO BookStore VALUES (1, 'Store1', 'XYZ', 2076109477);
INSERT INTO BookStore VALUES (2, 'Store2', 'XYZ', 2079902889);
INSERT INTO BookStore VALUES (3, 'Store3', 'XYZ', 2074531371);
INSERT INTO BookStore VALUES (4, 'Store4', 'XYZ', 2076228899);
INSERT INTO BookStore VALUES (5, 'Store5', 'XYZ', 2075967556);
INSERT INTO Book VALUES (0030719631, 'Title1', 'AuthF1', 'AuthL1', 'BookPub1', 'BookGenre', 15.81, 1);
INSERT INTO Book VALUES (071484578, 'Title2', 'AuthF2', 'AuthL2', 'BookPub2', 'BookGenre', 55.95, 2);
INSERT INTO Book VALUES (0810981130, 'Title3', 'AuthF3', 'AuthL3', 'BookPub1', 'BookGenre', 55.42, 3);
INSERT INTO Book VALUES (0898863058, 'Title4', 'AuthF1', 'AuthL1', 'BookPub1', 'BookGenre', 14.43, 4);
INSERT INTO Book VALUES (1884101003, 'Title5', 'AuthF2', 'AuthL2', 'BookPub2', 'BookGenre', 21.24, 5);
INSERT INTO Customers VALUES (1, 'Thomas', 'Fuzia', 'XYZ', 13027, 'thomas.fuzia);
INSERT INTO Customers VALUES (2, 'Pernel', 'Charles', 'XYZ', 04401, 'p.charles');
INSERT INTO Customers VALUES (3, 'Johnny', 'Rocket', 'XYZ', 04609, 'j.rocket');
INSERT INTO Customers VALUES (4, 'Beatrice', 'Ramos', 'XYZ', 44609, 'bnito1952');
INSERT INTO Customers VALUES (5, 'Julian', 'Baros', 'XYZ', 04841, 'pimpjulian');
INSERT INTO Employees VALUES (100, 'Thomas', 'Fuzia', 'XYZ', 2076102233, 'thomas.fuzia', now());
INSERT INTO Employees VALUES (101, 'Pernel', 'Charles', 'XYZ', 2076102234, 'p.charles', now());
INSERT INTO Employees VALUES (102, 'Johnny', 'Rocket', 'XYZ', 2076102235, 'j.rocket', now());
INSERT INTO Employees VALUES (103, 'Beatrice', 'Ramos', 'XYZ', 2076102236, 'bnito1952', now());
INSERT INTO Employees VALUES (104, 'Julian', 'Baros', 'XYZ', 2076102237, 'pimpjulian', now());
INSERT INTO Products VALUES (1, 'Product1', 32, 15.81);
INSERT INTO Products VALUES (2, 'Product2', 6, 55.95);
INSERT INTO Products VALUES (3, 'Product3', 12, 55.42);
INSERT INTO Products VALUES (4, 'Product4', 210, 1.25);
INSERT INTO Products VALUES (5, 'Product5', 165, 0.75);
INSERT INTO Products VALUES (6, 'Product4', 210, 1.25);
INSERT INTO Products VALUES (7, 'Product5', 165, 0.75);
INSERT INTO OrderDetail VALUES (101, now(), 1, 1, 100, 1, 2);
INSERT INTO OrderDetail VALUES (102, now(), 2, 6, 100, 1, 3);
INSERT INTO OrderDetail VALUES (103, now(), 1, 7, 101, 1, 1);
INSERT INTO OrderDetail VALUES (104, now(), 3, 4, 102, 2, 1);
INSERT INTO OrderDetail VALUES (105, now(), 3, 5, 100, 3, 1);
INSERT INTO Transactions VALUES (1, 'visa 3111', 101);
INSERT INTO Transactions VALUES (2, 'mc 3111', 102);
INSERT INTO Transactions VALUES (3, 'cash', 103);
INSERT INTO Transactions VALUES (4, 'cash', 104);
INSERT INTO Transactions VALUES (5, 'visa 3111', 105);
Please use SQL only, I am working with MS SMS 2014.
I need to be able to answer 5 queries with in this database using SQL.
1. How many books are sold each month by the publisher?
2. Which authors are the biggest sellers of books in our stores?
3. Which authors are the biggest sellers of books in our stores?
4. What are the most popular products besides books that are sold in each store?
5. From what region(s) (by ZIP code) do customers visit our stores?
I appreciate if someone could help me out.
AGAIN PLEASE USE SQL THAT SSMS 2014 CAN READ.
SCREEN SHOTS WOULD BE APPRECIATED AS WELL.
100 % Results Messages Store_Phone 2076109477 2079902889 2074531371 2076228899 2075967556 Store ID Store_Nam Store_Add 1171 Bar Harbor RD, Bar Harbor, ME 04609 547 Main St, Bangor, ME 04401 Book R'Us Great Books, USA Purple Cow Books n Stuff 6 Skowhegan Rd, Faifield, ME 04937 Riverfront Books Rockland Books 300 Water St, Augusta, ME 04330 441 Main St, Rockland, ME 04841Explanation / Answer
1)
# The sum of Order_Qty is used for finding the no. of books sold and group by is done based on publisher and month.
SELECT b.Book_Pub, MONTH(od.Order_Date), SUM(od.Order_Qty)
FROM [Order Detail] AS od INNER JOIN Book AS b
ON od.Prod_ID = b.ISBN
INNER JOIN Products AS p
ON p.Prod_ID = od.Prod_ID
GROUP BY b.Book_Pub, MONTH(Order_Date);
2)
# Same as above but group by is done based on Store_ID and Author Name
SELECT od.Store_ID, b.Auth_FName, Auth_LName , SUM(od.Order_Qty) AS total_product
FROM [Order Detail] AS od INNER JOIN Book AS b
ON od.Prod_ID = b.ISBN
INNER JOIN Products AS p
ON p.Prod_ID = od.Prod_ID
GROUP BY od.Store_ID, b.Auth_FName, b.Auth_LName
ORDER BY total_product;
4)
# Most popular products are determined by sum of order quantity. For finding popular items other than book, ISBN from Books are excluded.
SELECT od.Store_ID, p.Prod_ID, p.Prod_Detail , SUM(od.Order_Qty) AS total_product
FROM [Order Detail] AS od
INNER JOIN Products AS p
ON p.Prod_ID = od.Prod_ID
WHERE od.Prod_ID NOT IN (SELECT ISBN FROM Book)
GROUP BY od.Store_ID, p.Prod_ID, p.Prod_Detail
ORDER BY total_product;
5)
# Customers who made the order are considered to be visited customers of a particular store, so the join is done between the Customers relation and [Order Details] relation on Cust_ID.
SELECT od.Store_ID, c.Cust_Zip
FROM Customers AS c INNER JOIN [Order Details] AS od
ON c.Cust_ID = od.Cust_ID;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.