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

Equi-Join

1.      א. הציגו טבלה המתבססת על טבלאות: Employees , Departments  אשר מציגה את שמו הפרטי של העובד, שם משפחתו, מספר מחלקתו ושם מחלקתו.

ב. סננו את הנתונים עבור העובדים הנמצאים במחלקה 90 ו-50 בלבד ומיינו לפי שמות המשפחה שלהם (סדר עולה).

2.      א. הציגו טבלה המתבססת על טבלאות: Departments , Locations  אשר מציגה את שמה של המחלקה, העיר והמדינה (state_province)בה היא נמצאת.

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

ג. הציגו את הנתונים רק עבור העובדים אשר יש להם בשם המשפחה את האות  a.

3.      הציגו טבלה המתבססת על טבלאות : Departments , Locations  ו- Employees  

הטבלה תציג את שם משפחתו של העובד, שמו הפרטי ועירו עבור העובדים אשר יש להם את האות eבשם משפחתם.

Non Equi-Join

4.      הציגו טבלה המתבססת על טבלאות Employees , Job_Gradesאשר תציג את שם העובד, משכורתו ודרגת השכר שלו.

Outer-Join

5.      א. הציגו טבלה המתבססת על טבלאות: Employees , Departments  אשר מציגה את שמו הפרטי של העובד, שם משפחתו, מספר מחלקתו ושם מחלקתו גם עבור אותם עובדים אשר אין להם מחלקה.
ב. הציגו אותם הנתונים גם עבור אותן מחלקות אשר אין בהן עובדים.

Self-Join

6.      א. הציגו טבלה המתבססת על טבלת Employees  אשר מציגה את שם העובד ואת שם מנהלו (תנו לעמודות כינויים מתאימים).

ב. הציגו אותם הנתונים גם עבור אותם עובדים ללא מנהלים.

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

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

 

פתרונות SQL Server


הפתרונות הבאים תקפים לפלטפורמת מיקרוסופט, לפתרונות עבור פלטפורמת אורקל לחץ כאן.

1. 
-- a 
SELECT emp.first_name , emp.last_name , 
       emp.department_id, dep.department_name 
FROM employees emp JOIN departments dep 
ON emp.department_id = dep.department_id 
-- b 
SELECT emp.first_name , emp.last_name , 
       emp.department_id,  dep.department_name 
FROM employees emp JOIN departments dep 
ON emp.department_id = dep.department_id 
AND
      emp.department_id IN (50 , 90)
ORDER BY emp.last_name 
2.
-- a 
SELECT dep.department_name , loc.city , loc.state_province
FROM   departments dep JOIN locations loc 
ON  dep.location_id = loc.location_id
 -- b 
SELECT emp.last_name + ' '+ emp.first_name AS 'FULL_NAME', 
       dep.department_name , loc.city , loc.state_province
FROM   employees emp JOIN departments dep  
ON     emp.department_id = dep.department_id  
JOIN   locations loc
ON     dep.location_id = loc.location_id 
-- c 
SELECT emp.last_name + ' '+ emp.first_name AS 'FULL_NAME', 
       dep.department_name , loc.city , loc.state_province
FROM   employees emp , departments dep , locations loc 
WHERE         emp.department_id = dep.department_id  
AND
              dep.location_id = loc.location_id 
AND           emp.last_name LIKE  '%a%'
3. 
SELECT emp.last_name , emp.first_name,  loc.city 
FROM   employees emp JOIN departments dep 
ON     emp.department_id = dep.department_id  
JOIN   locations loc 
ON     dep.location_id = loc.location_id 
AND     emp.last_name LIKE '%e%'
4. 
SELECT emp.last_name , emp.salary , job_g.grade_level
FROM   employees emp JOIN job_grades job_g
ON  emp.salary BETWEEN job_g.lowest_sal AND job_g.highest_sal
5.
-- a 
SELECT emp.first_name , emp.last_name , emp.department_id ,  dep.department_name 
FROM employees emp LEFT OUTER JOIN departments dep 
ON  emp.department_id = dep.department_id 
-- b 
SELECT emp.first_name , emp.last_name , emp.department_id ,  dep.department_name 
FROM employees emp RIGHT OUTER JOIN departments dep 
ON emp.department_id  = dep.department_id 
6.
-- a 
SELECT emp.last_name AS 'EMPLOYEE_NAME', 
       mng.last_name AS 'MANAGER_NAME'
FROM   employees emp JOIN employees mng
ON  emp.manager_id = mng.employee_id
-- b 
SELECT emp.last_name AS 'EMPLOYEE_NAME', mng.last_name AS 'MANAGER_NAME'
FROM   employees emp LEFT OUTER JOIN employees mng
ON  emp.manager_id = mng.employee_id 
7.
SELECT emp.last_name , emp.first_name , emp.department_id 
FROM   employees emp JOIN employees specific_employee
ON  emp.department_id = specific_employee.department_id
AND    specific_employee.last_name = 'King'
-- AND emp.last_name <> ‘King’
8.
SELECT emp.last_name , emp.salary 
FROM   employees emp JOIN employees specific_emp
ON  emp.salary < specific_emp.salary 
AND    specific_emp.employee_id = 103

פתרונות Oracle

1. 
-- a 
SELECT emp.first_name , emp.last_name , 
       emp.department_id ,    dep.department_name 
FROM employees emp , departments dep 
WHERE emp.department_id = dep.department_id 
-- b 
SELECT emp.first_name , emp.last_name , 
       emp.department_id ,  dep.department_name 
FROM employees emp , departments dep 
WHERE emp.department_id = dep.department_id 
AND
      emp.department_id IN (50 , 90)
ORDER BY emp.last_name 
2.
-- a 
SELECT dep.department_name , loc.city , loc.state_province
FROM   departments dep , locations loc 
WHERE  dep.location_id = loc.location_id
 -- b 
SELECT emp.last_name || ' '|| emp.first_name AS "FULL_NAME", 
       dep.department_name , loc.city , loc.state_province
FROM   employees emp , departments dep , locations loc 
WHERE         emp.department_id = dep.department_id  
AND
              dep.location_id = loc.location_id 
-- c 
SELECT emp.last_name || ' '|| emp.first_name AS "FULL_NAME", 
       dep.department_name , loc.city , loc.state_province
FROM   employees emp , departments dep , locations loc 
WHERE         emp.department_id = dep.department_id  
AND
              dep.location_id = loc.location_id 
AND           emp.last_name LIKE  '%a%'
3. 
SELECT emp.last_name , emp.first_name,  loc.city 
FROM   employees emp , departments dep , locations loc 
WHERE         emp.department_id = dep.department_id  
AND
              dep.location_id = loc.location_id 
AND           emp.last_name LIKE '%e%'
4. 
SELECT emp.last_name , emp.salary , job_g.grade_level
FROM   employees emp , job_grades job_g
WHERE  emp.salary BETWEEN job_g.lowest_sal AND job_g.highest_sal
5.
-- a 
SELECT emp.first_name , emp.last_name , emp.department_id ,  dep.department_name 
FROM employees emp , departments dep 
WHERE emp.department_id = dep.department_id (+)
-- b 
SELECT emp.first_name , emp.last_name , emp.department_id ,  dep.department_name 
FROM employees emp , departments dep 
WHERE emp.department_id (+) = dep.department_id 
6.
-- a 
SELECT emp.last_name AS “EMPLOYEE_NAME”, 
       mng.last_name AS “MANAGER_NAME”
FROM   employees emp , employees mng
WHERE  emp.manager_id = mng.employee_id
-- b 
SELECT emp.last_name AS “EMPLOYEE_NAME”, mng.last_name AS “MANAGER_NAME”
FROM   employees emp , employees mng
WHERE  emp.manager_id = mng.employee_id (+)
7.
SELECT emp.last_name , emp.first_name , emp.department_id 
FROM   employees emp , employees specific_employee
WHERE  emp.department_id = specific_employee.department_id
AND    specific_employee.last_name = ‘King’
-- AND emp.last_name <> ‘King’
8.
SELECT emp.last_name , emp.salary 
FROM   employees emp , employees specific_emp
WHERE  emp.salary < specific_emp.salary 
AND    specific_emp.employee_id = 103