asim902 بتاريخ: 29 أكتوبر 2009 تقديم بلاغ مشاركة بتاريخ: 29 أكتوبر 2009 الأسئلة في الملف المرفق SQLحليت بعض منها واللأخرى لم أستطع حلها p tablepno descr color P1 Widget Blue P2 Widget Red P3 Dongle Green s tablesno name city S1 Pierre Paris S2 John London S3 Mario Rome sp tablesno 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; اقتباس رابط هذا التعليق شارك المزيد من خيارات المشاركة
Recommended Posts
انضم إلى المناقشة
يمكنك المشاركة الآن والتسجيل لاحقاً. إذا كان لديك حساب, سجل دخولك الآن لتقوم بالمشاركة من خلال حسابك.