Hello, I am working on a database using SQL, in the Microsoft SQL Server 2014 Ma
ID: 3596253 • 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.
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 1 query with in this database using SQL.
1. Which books are associated with each publisher?
I appreciate if someone could help me out
many Thank you's
Explanation / Answer
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
)
To get the books associated with each publisher, you need to use the table Book which contains the title of the book (Book_Title) and also publisher name (Book_Pub). For that you need to sort this table using publisher name. Then all the books are arranged based on the alphabetically value of the publisher.
SELECT Book_Title FROM Book
ORDER BY Book_Pub;
**Comment for any further queries.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.