Given the following relational schemas: Client (cid: integer, cname: string, add

Question

Given the following relational schemas: Client (cid: integer, cname: string, address: string, area: string) Item (Iid: integer, Iname: string, price: real, inStock: intger) Delivery ( did: integer, cid: integer, shipdate: date) Delivered_Item( did: integer, Iid: integer, amount: integer) Write quires in SQL to : 1. Find the total value of Items in the stock. 2. For all Clients in Hawaliy, list the Client name, Item name, and amount for all Deliveries. 3. Find the Items name whose name contains ‘iPad Air’ with price more than the average prices of all Items. 4. Find the Client names and total sales of Item delivered to each Client. Only show Clients with sales over 150 KD 5. Find the number of Deliveries to Client with the first name ‘Sara’

Explanation / Answer

Hi,

Please find the SQL queries to all the five questions below:-

1.Find the total value of Items in the stock

SELECT SUM (price) AS TotalValue FROM Item where inStock <>0

2.For all Clients in Hawaliy, list the Client name, Item name, and amount for all Deliveries.

Select Client. cid,Iname,amount

From Client, Item, Delivery,Delivered Item

Where Client.cid=Delivery.cid and Delivery. did=Delivered_item.did and Address=”Hawaliy”;

3.Find the Items name whose name contains ‘iPad Air’ with price more than the average prices of all Items.

SELECT Iname,

FROM Item

WHERE price>(SELECT AVG(price) FROM Item)

And Iname LIKE “%iPad Air%”;

4.Find the Client names and total sales of Item delivered to each Client. Only show Clients with sales over 150 KD.

Select Client. cid,amount as total sale

From Client, Item, Delivery, Delivered Item

Where Client.cid=Delivery.cid and Delivery. did=Delivered_item.did and

Amount >150;

5.Find the number of Deliveries to Client with the first name ‘Sara’;

Select count (did) as deliveries

From Delivery, Client

Where Client.cid=Delivery.cid and

cname LIKE “Sara%”;

==================================================================================

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

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