I got SQL question regarding to table above. Give a SQL query that lists all cus
ID: 3590355 • Letter: I
Question
I got SQL question regarding to table above.
Give a SQL query that lists all customers in California or Nevada.
Give a SQL query that lists all products that have been purchased by a customer in California.
Give a SQL query that lists all line items that are part of invoice number 11731.
Give a SQL update statement that raises all prices by ten percent.
Invoice Lineltem Invoice_ Customer_ Invoice_ Product Payment Quantity Number INTEGER 11731 11732 11733 Number INTEGER 3175 3176 3175 Number INTEGER 11731 11731 11731 11732 11733 11733 Code CHAR(7) 116-064 257-535 643-119 116-064 116-064 643-119 DECIMAL (10, 2) INTEGER 249.50 Product 10 Price DECIMAL (10, 2) 24.95 29.95 19.99 Product_Co de Description 2 CHAR (7) 116-064 257-535 643-119 VARCHAR (40) Toaster Hair dryer Car vacuum Customer Customer City Address VARCHAR (40) 100 Main Street State CHAR (2) CA MI Zip CHAR(5) 98765 45066 Name Number INTEGER 3175 3176 VARCHAR (40) VARCHAR (30) Sam's Small Appliances Anytown Electronics Unlimited 1175 Liberty AvePleasantville Figure 8 A Sample DatabaseExplanation / Answer
Sample database:
Customer table:
Customer_Number
Name
Address
City
State
Zip
3175
Sam's Small Appliances
100 Main Street
Anytown
CA
98765
3176
Elelctronics Unlimited
175 Liberty Ave
Pleasantville
MI
45066
1234
Electronics wiring
110 normal ville
California
CF
23765
5678
Large appliances
5667 kotlin Ave
Nevada
ND
44566
Product table:
Product_Code
Description
Price
116-064
Toaster
24.95
257-535
Hair dryer
29.95
643-119
Car vaccum
19.99
lineitem table:
Invoice_Number
Product_Code
Quantity
11731
116-064
3
11731
116-064
3
11731
257-535
1
11731
643-119
2
11732
116-064
10
11733
116-064
2
11733
649-119
1
Invoice table:
Invoice_Number
Customer_Number
Payment
11731
3175
0
11732
3176
249.5
11733
3175
0
11732
1234
249.5
Part 1:
Querry to execute:
select customer_number, name from customer where city="California" or city="Nevada";
resultant table:
Customer_Number
Name
1234
Electronics wiring
5678
Large appliances
Part 2:
Querry to execute:
select description from product where product_code in (select product_code from lineitem where invoice_number in (select invoice_number from invoice
where customer_number in (select customer_number from customer where city="California")));
resultant table:
Description
Toaster
Part 3:
Querry to execute:
select description from product where product_code in (select product_code from lineitem where invoice_number=11731);
resultant table:
Description
Toaster
Hair dryer
Car vaccum
Part 4:
Querry to execute:
update product set price=price*1.1;
resultant table:
Product_Code
Description
Price
116-064
Toaster
27.445
257-535
Hair dryer
32.945
643-119
Car vaccum
21.989
Customer_Number
Name
Address
City
State
Zip
3175
Sam's Small Appliances
100 Main Street
Anytown
CA
98765
3176
Elelctronics Unlimited
175 Liberty Ave
Pleasantville
MI
45066
1234
Electronics wiring
110 normal ville
California
CF
23765
5678
Large appliances
5667 kotlin Ave
Nevada
ND
44566
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.