1. Use the sample database (four tables Invoice, Product, LineItem, and Customer
ID: 3724820 • Letter: 1
Question
1. Use the sample database (four tables Invoice, Product, LineItem, and Customer) in Page W1022 of CH24 to answer the following questions:
a. Give a SQL query that lists all customers in California or Nevada.
b. Give a SQL query that lists all products that have been purchased by a customer in California.
c. Give a SQL query that lists all line items that are part of invoice number 11731.
W1022 Chapter 24 Relational Databases Invoice Customer Number CodeQuantity DECDAL(10·2) CHARC 3173 3176 3175 257-535 43-119 116 061 116 061 643-119 1733 11731 Price DECIMALC0, 2) 24.95 WCHAR (40) 11733 116-064 Hair dryer 643-119 Car vacuum 19.99 Customer City 3175 Sam Small Applia 100 Mai SeAnyton 3176 Electionis Ualimited 1 Liberty Ae Pleasatville ML Figure 8 A Sample Database 24.2.1 Simple Queries lUse the snu vuIn SQL, you use tbe SELECT cosue querics Foreample, thend to select all data trom the Customer table is a darabase. SELECT FRON Custeer The resultis City 3175 S 100 Main SueeAyoa CA 98765 3176 Electonics 1175 Libety Ae Pleasantville 5066Explanation / Answer
Answer:
a)SELECT * FROM Customers
WHERE City='California' OR City='Nevada'; /*This query will give you details abut all customers in California or Nevada*/
b)select pr.Product_Code,pr.Description,cu.City
from Product pr join Lineitem li on li.Product_Code = pr.Product_Code
join Invoice iv on iv.Invoice_Number = li.Invoice_Number
join Customer cu on cu.Customer_Number = iv.Customer_Number
where cu.city = 'California'
group by pr.Product_Code;/*This query will list all products that have been purchased by a customer in California.*/
c)select li.Invoice_Number,li.Product_Code,pr.Description
from Lineitem li join Product pr on pr.Product_Code = li.Product_Code
where Invoice_Number =='11731'
group by Invoice_Number;/*This query will lists all line items that are part of invoice number 11731.*/
d)update Product set Price = Price + (Price * 0.1); /*This query will update all prices by ten percent. Percentage converted into decimals*/
Let me know if you have any doubts in the queries.,
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.