Book a Demo!
CoCalc Logo Icon
StoreFeaturesDocsShareSupportNewsAboutPoliciesSign UpSign In
DanielBarnes18
GitHub Repository: DanielBarnes18/IBM-Data-Science-Professional-Certificate
 Path: blob/main/06. Databases and SQL for Data Science with Python/06. Bonus Module - Advanced SQL for Data Engineering/JOIN_Solution_Script.sql
6532 views
1


2
--- Query1A ---

3
select E.F_NAME,E.L_NAME, JH.START_DATE 

4
	from EMPLOYEES as E 

5
	INNER JOIN JOB_HISTORY as JH on E.EMP_ID=JH.EMPL_ID 

6
	where E.DEP_ID ='5'

7
;	

8
--- Query1B ---	

9
select E.F_NAME,E.L_NAME, JH.START_DATE, J.JOB_TITLE 

10
	from EMPLOYEES as E 

11
	INNER JOIN JOB_HISTORY as JH on E.EMP_ID=JH.EMPL_ID 

12
	INNER JOIN JOBS as J on E.JOB_ID=J.JOB_IDENT

13
	where E.DEP_ID ='5'

14
;

15
--- Query 2A ---

16
select E.EMP_ID,E.L_NAME,E.DEP_ID,D.DEP_NAME

17
	from EMPLOYEES AS E 

18
	LEFT OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP

19
;	

20
--- Query 2B ---

21
select E.EMP_ID,E.L_NAME,E.DEP_ID,D.DEP_NAME

22
	from EMPLOYEES AS E 

23
	LEFT OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP 

24
	where YEAR(E.B_DATE) < 1980

25
;

26
--- alt Query 2B ---

27
select E.EMP_ID,E.L_NAME,E.DEP_ID,D.DEP_NAME

28
	from EMPLOYEES AS E 

29
	INNER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP 

30
	where YEAR(E.B_DATE) < 1980

31
;

32
--- Query 2C ---

33
select E.EMP_ID,E.L_NAME,E.DEP_ID,D.DEP_NAME

34
	from EMPLOYEES AS E 

35
	LEFT OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP 

36
	AND YEAR(E.B_DATE) < 1980

37
;

38
--- Query 3A ---

39


40
select E.F_NAME,E.L_NAME,D.DEP_NAME

41
	from EMPLOYEES AS E 

42
LEFT OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP

43


44
UNION

45


46
select E.F_NAME,E.L_NAME,D.DEP_NAME

47
	from EMPLOYEES AS E 

48
RIGHT OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP

49
--- Query 3B ---

50
select E.F_NAME,E.L_NAME,D.DEPT_ID_DEP, D.DEP_NAME

51
	from EMPLOYEES AS E 

52
	LEFT OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP AND E.SEX = 'M'

53


54


55
UNION

56


57
select E.F_NAME,E.L_NAME,D.DEPT_ID_DEP, D.DEP_NAME

58
	from EMPLOYEES AS E 

59
	RIGHT OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP AND E.SEX = 'M'

60
;

61
--- alt Query 3B ---

62
select E.F_NAME,E.L_NAME,D.DEPT_ID_DEP, D.DEP_NAME

63
	from EMPLOYEES AS E 

64
	LEFT OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP AND E.SEX = 'M'

65
;

66