-
Notifications
You must be signed in to change notification settings - Fork 1
/
TYLERSMITHSQL7.txt
130 lines (119 loc) · 3.1 KB
/
TYLERSMITHSQL7.txt
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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
1. SELECT last_name, first_name
FROM instructor
WHERE instructor_id NOT IN
(SELECT DISTINCT instructor_id
FROM grade g Join enrollment e
ON g.section_id = e.section_id
AND g.student_id = e.student_id
JOIN section s
ON s.section_id = e.section_id
WHERE g.grade_type_code = 'PJ')
Order by last_name;
2. SELECT COUNT(*) AS BELOW_AVERAGE
FROM grade
WHERE grade_type_code ='FI'
AND section_id = 86
AND numeric_grade <
(SELECT AVG(numeric_grade)
FROM grade
WHERE grade_type_code = 'FI'
AND section_id = 86);
3. SELECT city, state
FROM enrollment e JOIN student s
ON e.student_id = s.student_id
JOIN zipcode z
ON s.zip = z.zip
GROUP BY city, state
Having COUNT(*) =
(SELECT MAX(y.maxcity)
FROM
(SELECT COUNT(*) AS maxcity
FROM enrollment e JOIN student s
ON e.student_id = s.student_id
JOIN zipcode z
ON s.zip = z.zip
GROUP BY city, state) y );
4. SELECT s.student_id, first_name, last_name, g.numeric_grade
FROM student s JOIN enrollment e
ON s.student_id = e.student_id
JOIN grade g
ON e.student_id = g.student_id
AND e.section_id = g.section_id
WHERE g.section_id = 81
AND g.grade_type_code = 'FI'
AND g.numeric_grade =
(SELECT MIN(numeric_grade)
FROM grade
WHERE grade_type_code = 'FI'
AND section_id = 81);
5. SELECT student_id, last_name, first_name
FROM student
WHERE student_id IN
(SELECT student_id
FROM
(SELECT COUNT(*) AS maxcount, student_id, s.course_no
FROM enrollment e JOIN section s
ON e.section_id = s.section_id
GROUP BY student_id, s.course_no
ORDER BY COUNT(*)) y
WHERE y.maxcount >1)
ORDER by last_name;
6. SELECT st.student_id, st.last_name, st.first_name
FROM student st JOIN enrollment e
ON st.student_id = e.student_id
JOIN section s
ON e.section_id = s.section_id
WHERE s.course_no LIKE '2%'
HAVING COUNT(*) =
(SELECT MAX(y.maxe)
FROM
(SELECT student_id , COUNT(*) AS maxe
FROM enrollment e JOIN section s
ON e.section_id = s.section_id
WHERE s.course_no LIKE '2%'
GROUP BY student_id) y)
GROUP BY st.student_id, st.last_name, st.first_name;
7. SELECT c.course_no, c.description
FROM course c JOIN section s
ON c.course_no = s.course_no
JOIN enrollment e
ON s.section_id = e.section_id
HAVING COUNT(*) =
(SELECT MAX(x.max2)
FROM
(SELECT COUNT(*) AS max2
FROM section s JOIN enrollment e
ON s.section_id = e.section_id
GROUP BY s.course_no) x)
GROUP BY c.course_no, c.description;
8. SELECT first_name, last_name
FROM student s JOIN enrollment e
ON s.student_id = e.student_id
WHERE e.section_id IN
(SELECT section_id
FROM section
WHERE To_char(start_date_time, 'HH:MI') = '10:30');
9. SELECT first_name, last_name
FROm student s JOIN enrollment e
ON s.student_id = e.student_id
JOIN grade g
ON e.section_id = g.section_id
AND e.student_id = g.student_id
WHERE g.section_id = 87
AND grade_type_code = 'FI'
AND g.numeric_grade <
(SELECT AVG(numeric_grade)
FROM grade
WHERE grade_type_code = 'FI'
AND section_id = 87)
ORDER BY last_name;
10. SELECT first_name, last_name, phone
FROM student st JOIN enrollment e
ON st.student_id = e.student_id
JOIN section s
ON e.section_id = s.section_id
JOIN course c
ON s.course_no = c.course_no
WHERE c.description LIKE 'Database%' OR c.description LIKE 'DB%' OR c.description
LIKE '%SQL%'
ORDER BY last_name;