-
Notifications
You must be signed in to change notification settings - Fork 0
/
TYLERSMITHSQL5.txt
75 lines (65 loc) · 2.09 KB
/
TYLERSMITHSQL5.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
1. SELECT first_name, last_name, phone
FROM student s JOIN zipcode z
ON s.zip = z.zip
WHERE z.city = 'Newark'
ORDER BY last_name, first_name;
2. ;
3. SELECT c.course_no, description
FROM course c JOIN section s
ON c.course_no = s.course_no
JOIN instructor i
ON s.instructor_id = i.instructor_id
WHERE i.first_name = 'Charles' AND (c.course_no BETWEEN 100 AND 199)
ORDER BY c.course_no;
4.SELECT g.grade_type_code, description, COUNT(*) AS NUMBER_PER_SECTION
FROM grade g JOIN grade_type_weight gtw
ON g.grade_type_code = gtw.grade_type_code
JOIN grade_type gt
ON gtw.grade_type_code = gt.grade_type_code
JOIN section
ON gtw.section_id = section.section_id
WHERE section.course_no = 144
GROUP BY g.grade_type_code, description;
5. SELECT DISTINCT last_name|| ', ' ||first_name AS STUDENT_NAME
FROM student s JOIN enrollment e
ON s.student_id = e.student_id
JOIN grade g
ON e.student_id = g.student_id
HAVING AVG(g.numeric_grade) >=93
Group BY last_name, first_name
ORDER BY STUDENT_NAME;
6. SELECT first_name, last_name, street_address, z.city, z.state, z.zip
FROM instructor i JOIN zipcode z
ON i.zip = z.zip
JOIN section s
ON i.instructor_id = s.instructor_id
HAVING COUNT(*) <10
GROUP BY first_name, last_name, street_address, z.city, z.state, z.zip;
7. SELECT c.course_no, COUNT(*) AS ENROLLED
FROM section s JOIN enrollment e
ON s.section_id = e.section_id
JOIN course c
ON s.course_no = c.course_no
WHERE c.prerequisite IS NULL
GROUP BY c.course_no
ORDER BY COUNT(*) DESC;
8. SELECT DISTINCT first_name, last_name, phone
FROM student s JOIN enrollment e
ON s.student_id = e.student_id
JOIN zipcode z
ON s.zip = z.zip
WHERE e.enroll_date < To_Date('FEB 02 07 10:20AM' , 'Mon DD YY HH:MIAM')
AND z.city = 'Flushing' ;
9. SELECT DISTINCT course_no
FROM section s JOIN grade_type_weight gtw
ON s.section_id = gtw.section_id
JOIN grade g
ON gtw.section_id = g.section_id
WHERE g.grade_type_code = 'PJ'
ORDER BY course_no;
10. SELECT MAX(numeric_grade)
FROM grade g JOIN grade_type_weight gtw
ON g.section_id = gtw.section_id
JOIN section s
ON gtw.section_id = s.section_id
WHERE s.course_no = 145 AND g.grade_type_code = 'FI';