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

חלק א – שימוש בפונקציות קבוצה על כל הטבלה

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

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

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

4.      הציגו מתוך טבלת Employees  את מספר הרשומות הקיימות בעמודת commission_pct.

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

6.      הציגו מתוך טבלת Employees  את השכר הגבוה ביותר, הנמוך ביותר והממוצע.

 

חלק ב – שימוש בפונקציות קבוצה תוך שילוב GROUP BY  ו- HAVING

7.      א. הציגו מתוך טבלת Employees  את מספר המחלקה ואת השכר הממוצע לכל אחת מן המחלקות.

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

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

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

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

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

9.      הציגו מתוך טבלת Employees  את מספר המחלקה, את קוד המשרה ואת השכר הממוצע לכל אחת מן המחלקות ולכל אחת מהמשרות השונות בתוך אותה מחלקה (קיבוץ לפי 2 עמודות).

10.   א. הציגו מתוך טבלת Employees  את השכר הגבוה ביותר לפי כל מספר מנהל .

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

11.   א. הציגו מתוך טבלת Employees  את השכר הנמוך ביותר עבור כל קוד משרה

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

12.   הציגו מתוך טבלת Employees  את השכר הממוצע עבור כל מספר מחלקה, רק עבור המחלקות שמספרן נע בין 20-80 ורק עבור אלו שהשכר הממוצע בהן גבוה מ 9000.

 

פתרונות


הפתרונות הבאים תקפים לסביבת מיקרוסופט ואורקל

1. 
SELECT MIN(last_name)
FROM employees 
2.  
SELECT MAX(last_name)
FROM employees 
3.  
SELECT COUNT(*) FROM employees 
4. 
SELECT COUNT(commission_pct) FROM employees 
5. 
SELECT COUNT(*) - COUNT(commission_pct) FROM employees
6. 
SELECT MIN(salary) , MAX(salary) , AVG(salary)
FROM employees 
7.  
-- a 
SELECT department_id , AVG(salary)
FROM employees 
GROUP BY department_id 
-- b 
SELECT department_id , AVG(salary)
FROM employees 
WHERE department_id IN (50, 80)
GROUP BY department_id 
-- c 
-- If we're aiming to select "regular" columns along with the group function, the GROUP BY clause is mandatory 
SELECT department_id , AVG(salary)
FROM employees 
WHERE department_id = 50
GROUP BY department_id 
-- Otherwise (if we're displaying the group function only), the GROUP BY clause is not necessary 
SELECT AVG(salary)
FROM employees 
WHERE department_id = 50 
8.  
-- a 
SELECT job_id , COUNT|(*)
FROM employees 
GROUP BY job_id 
-- b 
SELECT job_id , COUNT(*)
FROM employees 
WHERE salary > 10000
GROUP BY job_id 
-- c 
SELECT job_id , COUNT(*)
FROM employees 
GROUP BY job_id 
HAVING count(*) > 2 
9.  
SELECT AVG(salary) , department_id , job_id 
FROM employees 
GROUP BY department_id , job_id 
10.
-- b 
SELECT MAX(salary) , manager_id 
FROM employees 
GROUP BY manager_id 
-- b 
SELECT MAX(salary) , manager_id 
FROM employees 
GROUP BY manager_id 
HAVING MAX(salary) > 10000
11. 
-- a 
SELECT MIN(salary) , job_id 
FROM employees 
GROUP BY job_id
 -- b 
SELECT MIN(salary) , job_id 
FROM employees 
GROUP BY job_id
HAVING MIN(salary) > 7000   
12.
SELECT AVG(salary) , department_id 
FROM employees 
WHERE department_id BETWEEN 20 AND 80 
GROUP BY department_id 
HAVING AVG(salary) > 9000