Use the sample database (four tables invoice, Product, LineItem, and Customer) t
ID: 3591044 • Letter: U
Question
Use the sample database (four tables invoice, Product, LineItem, and Customer) to answer the following questions.
1. Give a SQL query that lists all customers in California and Nevada.
2. Give a SQL query that lists all products that have been purchased by a customer in California.
3. Give a SQL query that lists all line items that are part of invoice number 11731.
4. Give a SQL update statement that raises all prices by ten percent.
Invoice Table:
Invoice_Number
Customer_Number
Payment
INTEGER
INTEGER
DECIMAL(10,2)
11731
3175
0
11732
3176
249.50
11733
3175
0
LineItem Table:
Invoice_Number
Product_Cod
Quantity
INTEGER
CHAR(7)
INTEGER
11731
116-064
3
11731
257-535
1
11731
643-119
2
11732
116-064
10
11733
116-064
2
11733
643-119
1
Product Table:
Product_Code
Description
Price
CHAR(7)
VARCHAR(40)
DECIMAL(10,2)
116-064
Toaster
24.95
257-535
Hair Dryer
29.95
643-119
Car Vacuum
19.99
Customer Table
Customer_Number
Name
Address
City
State
ZIP
INTEGER
VARCHAR(40)
VARCHAR(40)
VARCHAR(30)
CHAR(2)
CHAR(5)
3175
Sam’s Small Appliances
100 Main Street
Anytown
CA
98765
3176
Electronics Unlimited
1175 Liberty Ave
Pleasantville
MI
45066
Invoice_Number
Customer_Number
Payment
INTEGER
INTEGER
DECIMAL(10,2)
11731
3175
0
11732
3176
249.50
11733
3175
0
Explanation / Answer
1.
Explanation:
We need to select customer's name either from California or Nevada.
Query:
SELECT Name FROM Customer WHERE City="California" OR City="Nevada";
2.
Explanation:
we need to list all products (description) purchased by a customer in california.
we can find description using product_code.
we can relate invoice number and customer using customer table and invoice table.
we can relate invoice number and product_code using invoice and line items tables.
we can relate product code and description using line items and product table.
In a simple manner find all invoice numbers of customers from california. Then find product code for that invoice number. Then find description for that product code.
Query:
SELECT Description FROM Product WHERE Product_Code IN (SELECT Product_Code FROM LineItem WHERE Invoice_Number IN (SELECT Invoice_Number FROM Invoice JOIN Customer ON Invoice.Customer_Number=Customer.Customer_Number AND Customer.City="California"));
3.
Explanation:
We need to list all rows from LineItem table having invoicenumber 11731
Query:
SELECT * FROM LineItem WHERE Invoice_Number=11731;
4.
Explanation:
We need to increase price of product by 10% (price*1.1)
Query:
UPDATE Product SET Price=Price*1.1;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.