Need Help making querie statements in SQL. Hello, I am working on a database usi

Question

Need Help making querie statements in SQL.

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_Add Varchar(40) not null,

Store_Phone numeric(10,0) not null

)

CREATE TABLE Book

(

ISBN numeric (10,0) NOT NULL PRIMARY KEY,

Book_Title varchar (MAX) NOT NULL,

Auth_FName varchar (40) NOT NULL,

Auth_LName varchar (40) NOT NULL,

Book_Pub varchar (70) NOT NULL,

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,

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,

Pay_Type varchar(30) not null,

Order_ID numeric(10,0) not null FOREIGN KEY REFERENCES [Order Detail] (Order_ID)

)

I need to be able to answer 6 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?

6. What customer data must be stored for the e-commerce portion of the website?

I appreciate if someone could help me out.

Explanation / Answer

1) SELECT month(Date) AS month, COUNT publisher
FROM Sales detail
GROUP BY month(Date) ORDER BY month;

2)SELECT Author(COUNT) AS BiggestSellers , Title From PRODUCT
Where DateSold.PUBLISHER = between "#1/1/2016" AND "#31/1/2016" and BiggestSellers=MAX;

3)SELECT Author(COUNT) AS BiggestSellers , Title From PRODUCT
Where DateSold.PUBLISHER = between "#1/1/2016" AND "#31/1/2016" and BiggestSellers=MAX;

4)SELECT Category(Count) AS Popular FROM PRODUCT
WHERE Category NOT = “books” AND Popular = MAX;

5)SELECT CustomerNo, CustomerName, CustomerCity From CUSTOMER WHERE CustomerZip=1;

6)SELECT CustomerAddress, CustomerCity, CustomerName, CustomerZip, CustomerEmail,
OrderDate.ORDERS AS DateOfOrder, OrderedItems.Orders As ItemsOrdered
FROM CUSTOMER

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

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