عندي أسأله وأبغالها حل

[asim902]

الأسئلة في الملف المرفق SQL
حليت بعض منها واللأخرى لم أستطع حلها



p table
pno descr color
P1 Widget Blue
P2 Widget Red
P3 Dongle Green


s table
sno name city
S1 Pierre Paris
S2 John London
S3 Mario Rome



sp table
sno pno qty
S1 P1 NULL
S2 P1 200
S3 P1 1000
S3 P2 200

1. Assume that the DB tables have many tuples and write SQL queries to do the following:
a. Write an SQL query to display/find all Red parts:

b. Write an SQL query to display/find all Blue parts that are supplied by suppliers located in London (i.e., suppliers who have city=London). Show
supplier number, name, part number, description and color.
c. Write an SQL query to display/find for each part the total (sum) of quantities it is on order, i.e., for each part number appearing in the sp table,
display the total sum of the values in attribute qty.
d. Write an SQL query to display/find for each Blue part the total (sum) of quantities it is on order. Display the part number and sum of qty.

e. As above, but display only those Red parts for which sum of qty is greater than 1000.

2. What would be display if the following queries were issued against the DB in Figure 1? (Write your answers directly below the questions.)
a. SELECT sp.*, p.* FROM p, sp WHERE sp.qty > 200 AND color = “Red”;

b. SELECT s.*, sp.* FROM s, sp WHERE s.city = “London” AND s.sno = sp.sno;

c. SELECT pno, COUNT(*) FROM sp, GROUP BY pno;

d. SELECT pno, SUM(qty) FROM sp WHERE sno = “S3” GROUP BY pno;

e. SELECT pno, SUM(qty) FROM sp WHERE sno = “S3” GROUP BY pno HAVING SUM(qty) > 300;