Project 1: Writing SQL Queries in Oracle

Database Systems
Project 1: Writing SQL Queries in Oracle
The following normalized tables from the Student Registration System (some tables have been simplified)
will be used in this project:
Students(sid, firstname, lastname, status, gpa, email)
 Courses(dept_code, course#, title)
Course_credit(course#, credits)
Classes(classid, dept_code, course#, sect#, year, semester, limit, class_size)
 Enrollments(sid, classid, lgrade)
Grades(lgrade, ngrade)
As a general clarification, we assume that no student takes the same course (including different sections of
the same course) more than once. If you have questions about these tables, please contact the instructor for
clarification.
1. Preparation
Save the sql script file proj1_tables_script20.txt as proj1_tables_script20.sql in your bingsuns account.
To run the above script from your Oracle account, use
SQL> start proj1_tables_script20
Then check whether all tables are created correctly in your Oracle account.
2. Query Statements
There are 20 statements in this project and you are asked to write one or more SQL query for each statement.
Each query is 5%.
Your queries should consider that the tuples currently in the database may change. In other words, your
queries must be correct for any valid state of every table. For this project, it is required that each query
returns no identical results (i.e., identical rows in the result must be removed). Meanwhile, it is required that
you don’t use unnecessary “select distinct” for your query. The query (or queries) for each statement is worth
5 points. Unremoved duplicates or unnecessary use of “select distinct” will result in 1-point deduction. No
other partial credit will be given in general. You are encouraged to use ways other than “select distinct” to
remove/avoid duplicates.
It is suggested that you first test each query individually and save each query in a different file (with
extension .sql). After all queries have been tested to your satisfaction, you can run all the queries in a
sequence and save the entire session in a spool file. Suppose you have saved your queries in files query1.sql,
..., query20.sql. Follow the steps below to generate the spool file after you have logged on Oracle:
 SQL> set echo on
 SQL> spool project1.txt
 SQL> start query1
 .......
 SQL> start query20
 SQL> spool off
Now create project1.txt, add your name(s) at the beginning, convert it into pdf, and upload to gradescope. If
you work in a team, write both of your names and turn in a single pdf file. Your pdf file must list each SQL
query followed by the result of executing the query. When you upload it to gradescope, mark where your
queries are in the pdf file.
The 20 query statements follow:
1. Find the dept_code, course# and title of each course that was offered in the Fall semester of 2020. In the
output, dept_code and course# of each course should be concatenated under a new column header
course_id.
2. Find the last name of each student who has taken at least one CS course and at least one math course.
3. Find the dept_code and course# of each course that was not offered in 2020.
4. Find the sid, lastname and GPA of each undergraduate student who has received a B for at least one
course he/she has taken.
5. Find the firstname of each student who has never received a C for any course he/she has taken. If you
write a nested query, make sure the subquery is uncorrelated.
6. Find the sid and firstname of each student who has received an A for every course he/she has taken.
Count only classes for which he/she received a non-null grade. GPA information is not permitted to be
used in this query.
7. Find the dept_code and course# of each course that has been offered the most number of times (each
record in the classes table corresponds to a course offering). Note that it is possible that more than one
course may satisfy this query condition; in this case, all such courses should be retrieved.
8. Find the classid, dept_code and course# of each class offered in Fall 2020 that is not full and for each
such class, also list the number of seats available (computed by limit – class_size) under the header
“seats_available’.
9. Find the sid and full name of every student who has taken more than 3 classes.
10. Find every class (all attributes are needed) that is offered by the CS department in the Fall semester of
2020 and has less than 3 students enrolled. For this query, you are not allowed to use the size
information from the classes table.
11. Find the sid and first name of every student who has taken all 300-level Math courses. Here we are
referring to courses, not classes. (A course, e.g., CS 240, can be offered in different semesters where the
different implementations of the course are different classes.)
12. Find the title of each course that has been taken by student B003 but not by student B005.
13. Find the first name of every student who has taken at least one course that has been taken by student
B005. Note that here we are talking about taking the same course, not just the same class. (Clearly,
student B005 satisfies the condition and his first name should be included in the output.)
14. Find the dept_code and course# of each course that has two or more classes in the same semester of the
same year. The query should also show the semester and year information for each qualified course.
15. Find the sid and firstname of each student who has received at least one highest grade in one of the
classes he/she has taken. Suppose all possible grades are (A, B, C, D, F, I). Note that the highest grade
given to students in a class is not necessarily A, for example, when all students in the class did poorly.
16. List the dept_code, course# and title of each course that has been taken by student B005. For each such
course, also list the grade the student received. If no grade has been assigned for a course, output “to be
assigned” as the grade information for the course.
17. Find the dept_code, course# and title of each course whose title contains “Data” and that has been taken
by all students whose GPA is higher than 3.5. Note that even though a qualified course is required to be
taken by all students whose GPA is higher than 3.5, it may also be taken by some students whose GPA is
not higher than 3.5.
18. Find the sid, lastname and the total number of credits that have been earned by each student. If a student
has not taken any course, he/she is assumed to have earned zero credits and zero credits should be
reported for such a student. Don’t count the credits when no grade is given for a class.
19. Find the average total number of credits earned by students (the average is over all students and only one
value should be computed) who have taken at least one course. Don’t count the credits when no grade is
given for a class.
20. Compute the GPA for each student from the student’s number grades (ngrade) for all the courses he/she
has taken (ignore the GPA values already in the students table). The GPA of a student is computed by
dividing the sum of his/her number grades by the number of classes he/she has taken and received a nonnull number grade. If a student has not received any non-null grade yet, the student’s GPA will be null.
For each student, the sid of the student and the computed GPA (name column head as cgpa) should be
displayed. Display the results in descending non-null GPA values.