HackerRankSQL/List the Course Names at main · bellaiam/HackerRankSQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
Write a query to return a list of professor names and their associated courses. The results can be in any order but must not contain duplicate rows.


Schema
PROFESSOR
Name	Type	Description
ID	Integer	unique id, primary key
NAME	String
DEPARTMENT_ID	Integer	This is a foreign key to DEPARTMENT.ID.
SALARY	Integer
DEPARTMENT
Name	Type	Description
ID	Integer	unique id, primary key
NAME	String
COURSE
Name	Type	Description
ID	Integer	unique id, primary key
NAME	String
DEPARTMENT_ID	Integer	foreign key to DEPARTMENT.ID
CREDITS	Integer
SCHEDULE
Name	Type	Description
PROFESSOR_ID	Integer	foreign key to PROFESSOR.ID
COURSE_ID	Integer	foreign key to COURSE.ID
SEMESTER	Integer
YEAR	Integer
Sample Data Tables


PROFESSOR
ID	NAME	DEPARTMENT_ID	SALARY
1	Alex Burton	5	7340
8	Jordan Diaz	1	17221
9	Drew Hicks	5	16613
2	Tyler Matthews	2	14521
10	Blake Foster	4	28526
3	Spencer Peters	1	10487
4	Ellis Marshall	3	6353
7	Morgan Lee	2	25796
5	Riley Peterson	1	35678
6	Peyton Fields	5	26648
DEPARTMENT
ID	NAME
3	Biological Sciences
5	Technology
6	Humanities & Social Sciences
2	Clinical Medicine
4	Arts and Humanities
1	Physical Sciences
COURSE
ID	NAME	DEPARTMENT_ID	CREDITS
9	Clinical Biochemistry	2	3
4	Astronomy	1	6
10	Clinical Neuroscience	2	5
1	Pure Mathematics and Mathematical Statistics	1	3
6	Geography	1	7
8	Chemistry	1	1
5	Physics	1	8
3	Earth Science	1	7
7	Materials Science and Metallurgy	1	5
2	Applied Mathematics and Theoretical Physics	1	5
SCHEDULE
PROFESSOR_ID	COURSE_ID	SEMESTER	YEAR
5	3	6	2012
7	3	1	2013
5	7	6	2010
2	10	2	2004
5	1	1	2011
2	9	4	2005
7	10	6	2009
5	6	4	2007
7	9	1	2014
9	9	5	2011


Sample Output

Tyler Matthews Clinical Biochemistry
Tyler Matthews Clinical Neuroscience
Drew Hicks Clinical Biochemistry
Morgan Lee Clinical Biochemistry
Morgan Lee Clinical Neuroscience
Morgan Lee Earth Science
Riley Peterson Earth Science
Riley Peterson Geography
Riley Peterson Materials Science and Metallurgy
Riley Peterson Pure Mathematics and Mathematical Statistics


// CODE

SELECT DISTINCT professor.name, course.name
FROM professor
JOIN schedule
ON professor.id = schedule.professor_id
JOIN course
ON course.id = schedule.course_id;