תרגול זה מסתמך על מאגר הנתונים ACDB. לתרגולים נוספים בנושאים שונים ניתן להשתמש במדריך ה SQL.

   חלק ראשון – שימוש בסיסי בפונקציות קבוצה

1.      הציגו מתוך טבלת Customers  את שם המשפחה הקטן ביותר מבחינה אלפאבתית.

2.      הציגו מתוך טבלת Packages את מחיר החבילה הממוצע ביותר

3.      הציגו מתוך טבלת Customers  את שם המשפחה הגדול ביותר מבחינה אלפאבתית.

4.      הציגו מתוך טבלת Packages את מספר חבילות הגלישה

5.      הציגו מתוך טבלת Customers  את מספר הרשומות בטבלה.

6.      הציגו מתוך טבלת Customers את מספר המדינות הייחודיות בטבלה

7.      הציגו מתוך טבלת Packagesאת מספר המהירויות הייחודיות בטבלה.

8.      הציגו מתוך טבלת Customers  את מספר הרשומות הקיימות בעמודת fax

9.      הציגו מתוך טבלת Customers  את מספר הרשומות הלא קיימות (אלו שיש בהן NULL) בעמודת fax

10.   הציגו מתוך טבלת Customers  את הסכום ההנחה החודשית הגבוה ביותר, הנמוך ביותר והממוצע.

חלק שני – עבודה עם תתי קבוצות

11.   הציגו מתוך טבלת Customers  את כמות הלקוחות בכל אחת מהמדינות השונות

12.   הציגו מתוך טבלת Packages את מחירה הממוצע של כל חבילת גלישה לפי מהירות

13.   הציגו מתוך טבלת Customers את כמות הערים הייחודיות בכל מדינה

14.   הציגו מתוך טבלת Packages את מחירה המקסימלי של כל חבילת גלישה לפי סקטור

15.   א. הציגו מתוך טבלת Customers  את מספר החבילת גלישה ואת הסכום ההנחה החודשית הממוצעת לכל אחת מן החבילות גלישה.

ב. הריצו את השאילתה שוב כך שתציג  את הנתונים עבור חבילות גלישה 22 ו- 13 בלבד.
ג. האם יש צורך להשתמש במשפט ה-
GROUP BYכאשר תתבקשו להציג את הממוצע של חבילת גלישה 18 בלבד?

16.   הציגו מתוך טבלת Packages את מחירה הממוצע, המקסימלי והמינימלי של כל מהירות גלישה

17.   א. הציגו מתוך טבלת Customers  את מספר החבילה ומספר הלקוחות המשוייכים אליה.

ב. הריצו את השאילתה שוב כך שתציג את הנתונים עבור הלקוחות אשר סכום ההנחה החודשית גבוה מ- 20.

ג. הריצו את סעיף א' שוב, הציגו את הנתונים רק עבור החבילות להן יותר מ- 100 לקוחות.

18.   הציגו מתוך טבלת Customers  את המדינה, העיר ואת כמות הלקוחות לפי כל מדינה ועיר

19.   א. הציגו מתוך טבלת Customers  את ממוצע סכום ההנחה החודשית לפי כל עיר .

ב. הריצו את השאילתה שנית כך שתציג את הנתונים עבור אלו אשר ממוצע סכום ההנחה גדול מ- 20.

20.   א. הציגו מתוך טבלת Customers  את סכום ההנחה החודשית הנמוך ביותר עבור כל מדינה

ב. הריצו את השאילתה שנית, הציגו את הנתונים עבור הלקוחות אשר סכום ההנחה חודשית המינימלי גדול מ- 10.

21.   הציגו מתוך טבלת Packages  את כמות החבילות לפי כל מהירות גלישה, רק עבור המהירויות להן יותר מ 8 חבילות

פתרונות

-- 1
SELECT MIN(last_name) FROM customers 

-- 2
SELECT AVG(monthly_payment) FROM packages 

-- 3
SELECT MAX(last_name) FROM customers

-- 4
SELECT COUNT(*) FROM packages  

-- 5
SELECT COUNT(*) FROM customers 

-- 6
SELECT COUNT(DISTINCT state) FROM customers 

-- 7
SELECT COUNT(DISTINCT speed) FROM packages 

-- 8
SELECT COUNT(fax) FROM customers 

-- 9
SELECT COUNT(*) - COUNT(fax)
FROM customers
-- or
SELECT COUNT(*)
FROM customers
WHERE fax IS NULL 

-- 10
SELECT	MIN(monthly_discount) ,
				MAX(monthly_discount) ,
				AVG(monthly_discount)
FROM customers 

-- 11
SELECT COUNT(*) , state
FROM customers
GROUP BY state 

-- 12
SELECT AVG(monthly_payment) , speed
FROM packages
GROUP BY speed

-- 13
SELECT COUNT(DISTINCT city) , state
FROM customers
GROUP BY state 

-- 14
SELECT MAX(monthly_payment) , sector_id
FROM packages
GROUP BY sector_id 

 -- 15
 -- A
 SELECT pack_id , AVG(monthly_discount)
 FROM customers
 GROUP BY pack_id 

 -- B
  SELECT pack_id , AVG(monthly_discount)
 FROM customers
 WHERE pack_id IN (13, 22)
 GROUP BY pack_id 

 -- C
 SELECT AVG(monthly_discount)
 FROM customers
 WHERE pack_id = 18

 -- 16
 SELECT speed , MIN(monthly_payment) , MAX(monthly_payment) , AVG(monthly_payment)
 FROM   packages
 GROUP BY speed  

 -- 17
 -- A
 SELECT pack_id , COUNT(*)
 FROM customers
 GROUP BY pack_id 

  -- B
 SELECT pack_id , COUNT(*)
 FROM customers
 WHERE monthly_discount > 20
 GROUP BY pack_id 

 -- C
 SELECT pack_id , COUNT(*)
 FROM customers
 GROUP BY pack_id
 HAVING COUNT(*) > 100 

 -- 18
 SELECT state, city, COUNT(*)
 FROM customers
 GROUP BY state, city
 ORDER BY state, city

-- 19
 -- A
 SELECT AVG(monthly_discount) , city
 FROM customers
 GROUP BY city

  -- B
 SELECT AVG(monthly_discount) , city
 FROM customers
 WHERE monthly_discount > 20
 GROUP BY city

 -- 20
 -- A
 SELECT MIN(monthly_discount) , state
 FROM customers
 GROUP BY state 

 -- B
  SELECT MIN(monthly_discount) , state
 FROM customers
 GROUP BY state
 HAVING min(monthly_discount) > 10

 -- 21
 SELECT COUNT(*) , speed
 FROM packages
 GROUP BY speed
 HAVING COUNT(*) > 8