-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathHomework4.sql
210 lines (181 loc) · 6.7 KB
/
Homework4.sql
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
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
# BRYAN KIM
# CPSC 3300
# HOMEWORK 4
# CREATED A DATABASE REPRESENTING A STUDENT ROSTER AND APPLIED QUERIES ACCORDING
# TO SPECIFICATIONS
CREATE DATABASE Homework4;
USE Homework4;
#1)
#CREATE STUDENTS TABLE
CREATE TABLE STUDENTS (
STUDENT_ID CHAR(11),
FIRST_NAME VARCHAR(20) NOT NULL,
LAST_NAME VARCHAR(20) NOT NULL,
GENDER CHAR(1) NOT NULL,
DATE_OF_BIRTH DATE NOT NULL,
PRIMARY KEY(STUDENT_ID)
);
#CREATE COURSES TABLE
CREATE TABLE COURSES (
COURSE_CODE VARCHAR(6),
COURSE_NAME VARCHAR(70) NOT NULL,
COURSE_LEVEL CHAR(2),
CREDITS INT NOT NULL,
PRIMARY KEY(COURSE_CODE)
);
#CREATE REGISTRATION TABLE
CREATE TABLE REGISTRATION (
STUDENT_ID CHAR(11),
COURSE_CODE VARCHAR(6),
GRADE DECIMAL(2,1) NOT NULL,
FOREIGN KEY(STUDENT_ID) REFERENCES STUDENTS(STUDENT_ID) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY(COURSE_CODE) REFERENCES COURSES(COURSE_CODE) ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY(STUDENT_ID,COURSE_CODE)
);
#2)
#INSERT VALUES INTO STUDENTS TABLE
INSERT INTO STUDENTS VALUES('861103-2438','Adam','Johnson','M','1990-10-01');
INSERT INTO STUDENTS VALUES('911212-1746','Eva','Smith','F','1991-08-20');
INSERT INTO STUDENTS VALUES('950829-1848','Anna','Washington','F','1993-09-26');
INSERT INTO STUDENTS VALUES('123456-0980','Eric','Alonzo','M','1990-05-26');
INSERT INTO STUDENTS VALUES('908023-2456','Bo','Ek','M','1992-03-15');
INSERT INTO STUDENTS VALUES('098735-3456','Danny','Goss','M','1992-02-01');
INSERT INTO STUDENTS VALUES('124345-3430','Mike','White','M','1995-06-10');
INSERT INTO STUDENTS VALUES('124568-1345','Emily','Young','F','1995-04-28');
INSERT INTO STUDENTS VALUES('908409-0010','Cathy','Lee','F','1993-10-06');
INSERT INTO STUDENTS VALUES('124587-9088','Ben','Woo','M','1992-11-30');
INSERT INTO STUDENTS VALUES('120953-0909','Anna','Washington','F','1990-10-09');
INSERT INTO STUDENTS VALUES('120449-1008','John','Goss','M','1995-10-26');
#INSERT VALUES INTO COURSES TABLE
INSERT INTO COURSES VALUES('CS056','Database Systems','G1',5);
INSERT INTO COURSES VALUES('CS010','C++','U1',5);
INSERT INTO COURSES VALUES('ENG111','English','U1',3);
INSERT INTO COURSES VALUES('FIN052','Finance','G1',5);
INSERT INTO COURSES VALUES('PHY210','Physics','U2',5);
INSERT INTO COURSES VALUES('CHE350','Chemistry','U3',5);
INSERT INTO COURSES VALUES('BIO001','Biology','U1',3);
INSERT INTO COURSES VALUES('CS052','Operating Systems','G1',5);
#INSERT VALUES INTO REGISTRATION TABLE
INSERT INTO REGISTRATION VALUES('861103-2438','CS056',4.0);
INSERT INTO REGISTRATION VALUES('861103-2438','CS010',4.0);
INSERT INTO REGISTRATION VALUES('861103-2438','PHY210',3.5);
INSERT INTO REGISTRATION VALUES('911212-1746','ENG111',2.0);
INSERT INTO REGISTRATION VALUES('950829-1848','CHE350',3.0);
INSERT INTO REGISTRATION VALUES('950829-1848','BIO001',2.5);
INSERT INTO REGISTRATION VALUES('123456-0980','CS052',3.5);
INSERT INTO REGISTRATION VALUES('123456-0980','CS056',4.0);
INSERT INTO REGISTRATION VALUES('908023-2456','PHY210',3.0);
INSERT INTO REGISTRATION VALUES('908023-2456','CS056',1.0);
INSERT INTO REGISTRATION VALUES('908023-2456','CS010',2.0);
INSERT INTO REGISTRATION VALUES('124345-3430','FIN052',2.5);
INSERT INTO REGISTRATION VALUES('124345-3430','CHE350',4.0);
INSERT INTO REGISTRATION VALUES('908409-0010','CS052',2.0);
INSERT INTO REGISTRATION VALUES('124587-9088','BIO001',4.0);
INSERT INTO REGISTRATION VALUES('124587-9088','CS052',3.5);
#3)
#ORDERS STUDENTS BY LAST_NAME, FIRST_NAME
SELECT LAST_NAME, FIRST_NAME
FROM STUDENTS
ORDER BY LAST_NAME, FIRST_NAME;
#4)
#SELECTS FEMALE STUDENTS WHO WERE BORN BEFORE 1992-12-31
SELECT STUDENT_ID, LAST_NAME, FIRST_NAME, DATE_OF_BIRTH
FROM STUDENTS
WHERE GENDER = 'F' AND DATE_OF_BIRTH < '1992-12-31';
#5)
#COUNTS HOW MANY STUDENTS ARE TAKING PHY210
SELECT COUNT(*) AS STUDENTS_TAKING_PHY210
FROM REGISTRATION
WHERE COURSE_CODE = 'PHY210';
#6)
#COUNTS HOW MANY COURSES ARE OFFERED BY THE CS DEPARTMENT
SELECT COUNT(*) AS COURSES_OFFERED_BY_CSDEPT
FROM COURSES
WHERE COURSE_CODE LIKE 'CS%';
#7)
#SELECTS COURSES THAT ARE LEVEL G1 AND OFFER 5 CREDITS
SELECT COURSE_CODE, COURSE_NAME
FROM COURSES
WHERE COURSE_LEVEL = 'G1' AND CREDITS = 5;
#8)
#COUNTS HOW MANY COURSES ARE IN G1
SELECT COUNT(*) AS COURSES_IN_G1
FROM COURSES
WHERE COURSE_LEVEL = 'G1';
#COUNTS HOW MANY COURSES ARE IN U1
SELECT COUNT(*) AS COURSES_IN_U1
FROM COURSES
WHERE COURSE_LEVEL = 'U1';
#COUNTS HOW MANY COURSES ARE IN U2
SELECT COUNT(*) AS COURSES_IN_U2
FROM COURSES
WHERE COURSE_LEVEL = 'U2';
#COUNTS HOW MANY COURSES ARE IN U3
SELECT COUNT(*) AS COURSES_IN_U3
FROM COURSES
WHERE COURSE_LEVEL = 'U3';
#9)
#FINDS THE AVG GRADE FROM STUDENT 861103-2438
SELECT AVG(GRADE)
FROM REGISTRATION
WHERE STUDENT_ID = '861103-2438';
#10)
#SELECTS THE STUDENTS THAT HAVE THE HIGHEST GRADE IN CS052
SELECT STUDENT_ID
FROM REGISTRATION
WHERE COURSE_CODE = 'CS052'
AND GRADE = (SELECT MAX(GRADE)
FROM REGISTRATION
WHERE COURSE_CODE = 'CS052');
#USED SUBQUERY TO FIND THE MAX GRADE FOR CS052 SPECIFICALLY
#11)
#SELECTS THE COURSES IN COMMON BETWEEN 861103-2438 AND 123456-0980
SELECT COURSE_CODE
FROM REGISTRATION
WHERE STUDENT_ID = '861103-2438' OR STUDENT_ID = '123456-0980'
GROUP BY COURSE_CODE
HAVING COUNT(DISTINCT STUDENT_ID) >= 2;
#12)
#FINDS WHICH STUDENTS HAVE NOT REGISTERED IN A COURSE YET
SELECT STUDENTS.STUDENT_ID
FROM STUDENTS LEFT JOIN REGISTRATION ON STUDENTS.STUDENT_ID = REGISTRATION.STUDENT_ID
WHERE REGISTRATION.STUDENT_ID IS NULL;
#13)
#FINDS STUDENT_ID, SUM OF GRADE, AND AVG OF GRADE IF THEIR GPA IS < 3
SELECT REGISTRATION.STUDENT_ID, SUM(GRADE), AVG(GRADE)
FROM STUDENTS LEFT JOIN REGISTRATION ON STUDENTS.STUDENT_ID = REGISTRATION.STUDENT_ID
GROUP BY REGISTRATION.STUDENT_ID
HAVING AVG(GRADE) < 3.0;
#14)
#SELECTS THE STUDENT ENROLLED IN THE MOST CLASSES (1ST INSTANCE ONLY)
SELECT STUDENT_ID
FROM REGISTRATION
GROUP BY STUDENT_ID
ORDER BY COUNT(DISTINCT COURSE_CODE) DESC
LIMIT 1;
#15)
#SELECTS STUDENTS WHO HAVE TAKEN PHY210 OR BIO001
SELECT STUDENT_ID
FROM REGISTRATION
WHERE COURSE_CODE = 'PHY210' OR COURSE_CODE = 'BIO001';
#16)
#FINDS THE YOUNGEST STUDENTS NAME
SELECT LAST_NAME, FIRST_NAME
FROM STUDENTS
WHERE DATE_OF_BIRTH = (SELECT MIN(DATE_OF_BIRTH) FROM STUDENTS);
#17)
#COUNTS HOW MANY STUDENTS HAVE NOT TAKEN CS056
SELECT COUNT(DISTINCT STUDENTS.STUDENT_ID) AS STUDENTS_NOT_TAKEN_CSO56
FROM STUDENTS LEFT JOIN REGISTRATION ON STUDENTS.STUDENT_ID = REGISTRATION.STUDENT_ID
WHERE NOT (REGISTRATION.COURSE_CODE = 'CS056');
#18)
#UPDATE ALL CS COURSES THAT ARE 5 CREDITS TO BE 6 CREDITS
UPDATE COURSES
SET CREDITS = 6
WHERE CREDITS = 5 AND COURSE_CODE LIKE 'CS%';
#19)
#DELETE THE ROW FROM REGISTRATION TABLE WHERE STUDENT_ID IS 908409-0010
DELETE FROM REGISTRATION
WHERE STUDENT_ID = '908409-0010';
#DROP DATABASE SO THAT THE DATABASE AND TABLE CREATION WORKS FROM THE BEGINNING
DROP DATABASE HOMEWORK4;