Assignment 3: SQL Queries & Interpreter Exercises

AI Assistance Disclosure

Sections of this assignment were completed with the assistance of AI-based tools such as *ChatGPT* for conceptual clarification and query guidance. All SQL queries were reviewed, tested, and validated by the author.

1. Open the Online SQL Interpreter: (https://www.db-book.com/university-lab-dir/sqljs.html)

2. Write SQL codes to get a list of:

select * from student
ID name dept_name tot_cred
00128 Zhang Comp. Sci. 102
12345 Shankar Comp. Sci. 32
19991 Brandt History 80
23121 Chavez Finance 110
44553 Peltier Physics 56
45678 Levy Physics 46
54321 Williams Comp. Sci. 54
55739 Sanchez Music 38
70557 Snow Physics 0
76543 Brown Comp. Sci. 58
76653 Aoi Elec. Eng. 60
98765 Bourikas Elec. Eng. 98 
select * from instructor
ID name dept_name salary
10101 Srinivasan Comp. Sci. 65000
12121 Wu Finance 90000
15151 Mozart Music 40000
22222 Einstein Physics 95000
32343 El Said History 60000
33456 Gold Physics 87000
45565 Katz Comp. Sci. 75000
58583 Califieri History 62000
76543 Singh Finance 80000
76766 Crick Biology 72000
83821 Brandt Comp. Sci. 92000
98345 Kim Elec. Eng. 80000 
select * from department
dept_name building budget
Biology Watson 90000
Comp. Sci. Taylor 100000
Elec. Eng. Taylor 85000
Finance Painter 120000
History Painter 50000
Music Packard 80000
Physics Watson 70000

3. Write in SQL codes to do the following queries:

3.a: My original code was this: Select * from Student ID >= Comp. Sci., which is wrong because I didn’t include “where”. I also did not connect the student, takes, and course join tables. With ChatGPT’s assistance, I got the correct code:

select distinct s.ID, s.name
from student s
join takes t on s.ID = t.ID
join course c on t.course_id = c.course_id
where c.dept_name = 'Comp. Sci.';
ID name
00128 Zhang
12345 Shankar
45678 Levy
54321 Williams
76543 Brown
98765 Bourikas

3.b: Just like the previous query, I add the takes table for the grades, which looks like this:

select distinct s.ID, s.name, t.grade
from student s
join takes t on s.ID = t.ID
join course c on t.course_id = c.course_id
where c.dept_name = 'Comp. Sci.';
ID name grade
00128 Zhang A
00128 Zhang A-
12345 Shankar C
12345 Shankar A
45678 Levy F
45678 Levy B+
45678 Levy B
54321 Williams A-
54321 Williams B+
76543 Brown A
98765 Bourikas C-
98765 Bourikas B

3.c: In this query, I used ChatGPT as an assistant tool . I added the student table, where I included the ID and name. Additionally, I added the takes table, where the ID and year is included as well. The main part of this query is the not in command, where it only keeps student ID’s that did not take any course before 2017.

select s.ID, s.name
from student s
where s.ID not in (
  select t.ID
  from takes t
  where t.year < 2017
);
ID name
00128 Zhang
12345 Shankar
19991 Brandt
23121 Chavez
44553 Peltier
45678 Levy
54321 Williams
55739 Sanchez
70557 Snow
76543 Brown
76653 Aoi
98765 Bourikas
98988 Tanaka

3.d: This query focuses on the instructor relation and group instructors by department names. The max command is finally applied within each group to calculate and compute the maximum salary per department.

select dept_name, MAX(salary) AS maximum_salary
from instructor
group by dept_name;
dept_name maximum_salary
Biology 72000
Comp. Sci. 92000
Elec. Eng. 80000
Finance 90000
History 62000
Music 40000
Physics 95000

3.e: For this part, I initially created this code: select names from min(max_salary) as lowest_of_department from ( select dept_name, max(salary) as max_salary from instructor group by dept_name ) dm;. I turned out I was incorrect. I learned from ChatGPT that Just like the previous query, I add the min command, which calculates the lowest salary of all departments of the per-department maximum salary.

select min(max_salary) as lowest_of_department
from (
  select dept_name, max(salary) as maximum_salary
  from instructor
  group by dept_name
) dm;
lowest_of_department
40000

3.f: Similar to the last query, I used ChatGPT to identify which department(s) makes the lowest salary (40000). I add the inner aggregation, where I select the department name and the maximum salary from the instructor table grouped by the department names. I then select the lowest salary and match the department to that value.

select dm.dept_name, dm.maximum_salary
from (
  select dept_name, max(salary) as maximum_salary
  from instructor
  group by dept_name
) dm
where dm.maximum_salary = (
  select min(x.maximum_salary)
  from (
    select dept_name, max(salary) as maximum_salary
    from instructor
    group by dept_name
  ) x
);
dept_name maximum_salary
Music 40000

5. Write SQL query to find the number of students in each section. The result columns should appear in the order “courseid, secid, year, semester, num”. You do not need to output sections with 0 students.

select course_id, sec_id, year, semester, count(ID) as num
from takes
group by course_id, sec_id, year, semester;
course_id sec_id year semester num
BIO-101 1 2017 Summer 1
BIO-301 1 2018 Summer 1
CS-101 1 2017 Fall 6
CS-101 1 2018 Spring 1
CS-190 2 2017 Spring 2
CS-315 1 2018 Spring 2
CS-319 1 2018 Spring 1
CS-319 2 2018 Spring 1
CS-347 1 2017 Fall 2
EE-181 1 2017 Spring 1
FIN-201 1 2018 Spring 1
HIS-351 1 2018 Spring 1
MU-199 1 2018 Spring 1
PHY-101 1 2017 Fall 1