Question

Please help I promise positive feedback

Part IV. Formulate SQL queries that follow for the database with the following tables: CUSTOMER, PRODUCT, SALES, and ITEM-SOLD.

Table: CUSTOMER

Primary Key: Account-No

Account-No	Customer-Name	Customer-City
1	A	NYC
2	B	NYC
3	C	MIA

Table: PRODUCT

Primary Key: Item-No

Item-No	Price
1	$1.00
2	$2.00
3	$3.00
4	$4.00

Table: SALES

Primary Key: Receipt-No

Foreign Key: Account-No References CUSTOMER

Receipt-No	Account-No	Sales-Person
1	1	S1
2	1	S1
3	2	S2
4	3	S3

Table: ITEM-SOLD

Primary Key: Receipt-No + Item-No

Foreign Key: Receipt-No References SALES

Foreign Key: Item-No References PRODUCT

Receipt-No	Item-No	Quantity
1	1	10
1	2	20
1	3	30
2	1	10
2	2	20
3	1	10
4	1	10
4	4	40

1. List the Receipt-No and Customer-Name for all sales transactions involving customers in NYC.
2. For each Receipt-No list the total amount as Total-Amount. Total-Amount is computed as the sum of the product of quantity and price for all items sold in that sales transaction (Receipt-no). For example, the Total-Amount for Receipt-No 1 is 10 x 1.00 + 20 x 2.00 + 30 x 3.00 = $140.00.
3. List the Item-No for products that were sold to every customer.
4. For each Product list the total quantity sold as Total-Quantity-Sold.

Answer 1

a) SELECT s.Receipt-No, c.Customer-Name FROM CUSTOMER c JOIN SALES s ON c.Account-No = s.Account-No WHERE c.Customer-City = 'NYC';

b) SELECT is.Receipt-No, sum(is.Quantity * p.Price) FROM ITEM-SOLD is JOIN PRODUCT p ON is.Item-No = p.Item-No;

d) SELECT sum(Quantity) AS 'Total-Quantity-Sold' FROM ITEM-SOLD GROUP BY Item-No;

NOTE: As per Chegg policy, I am allowed to answer only 3 questions (including sub-parts) on a single post. Kindly post the remaining questions separately and I will try to answer them. Sorry for the inconvenience caused.