T02 Tutorial 02

Question 01

Write the following queries in SQL, using the university schema.
a. Find the ID and name of each student who has taken at least one Comp. Sci. course;
make sure there are no duplicate names in the result.

-- a. Find the ID and name of each student who has taken at least 
-- one Comp. Sci. course
-- make sure there are no duplicate names in the result
select * from student limit 3;
select * from takes limit 3;
select * from course limit 3;
select distinct ID, name
from student natural join takes natural join course
where course.dept_name = 'Comp. Sci.'

b. Find the ID and name of each student who has not taken any course offered before 2017.

-- b. Find the ID and name of each student 
-- who has not taken any course offered before 2017.
select * from student limit 5;
select * from takes limit 7;
select *
from student natural join takes
limit 7;
select ID, name
from student
except
select ID, name
from student natural join takes
where year < 2017

c. For each department, find the maximum salary of instructors in that department.
You may assume that every department has at least one instructor.

-- c. For each department, find the maximum salary of instructors in that department. 
-- You may assume that every department has at least one instructor.
select * from instructor limit 3;
select max(salary) as MAXIMUM, name, ID,dept_name
from instructor
group by dept_name
order by MAXIMUM

d. Find the lowest, across all departments, of the per-department maximum salary computed by the preceding query.

-- d. Find the lowest, across all departments, 
-- of the per-department maximum salary computed by the preceding query.
select dept_name, max(salary) as MAXIMUM
from instructor
group by dept_name
order by MAXIMUM asc;
select MIN(MAXIMUM),dept_name,name
from (
  select dept_name, max(salary) as MAXIMUM, name
  from instructor
  group by dept_name
  order by MAXIMUM asc);

Question 02

Write the SQL statements using the university schema to perform the following operations:
a. Create a new course “CS-001”, titled “Weekly Seminar”, with 0 credits.

-- a. Create a new course “CS-001”, titled “Weekly Seminar”, with 0 credits.
select * from course limit 3;
insert into course(course_id, title,dept_name,credits)
values ('CS-001', 'Weekly Seminar', 'Comp. Sci.', 0);
select *
from course

b. Create a section of this course in Fall 2017, with sec id of 1,
and with the location of this section not yet specified.

-- b. Create a section of this course in Fall 2017, with sec id of 1, 
-- and with the location of this section not yet specified.
select * from section limit 3;
insert into section(course_id, sec_id, semester, year, building, room_number)
values ('CS-001','1','Fall',2009,'E4','1001');
select * from section;

c. Enroll every student in the Comp. Sci. department in the above section.

-- c. Enroll every student in the Comp. Sci. department in the above section.
insert into takes(ID, course_id, sec_id, semester, year)
  select ID,'CIS-001','1','Fall',2017 
  from student
  where dept_name = 'Comp. Sci.';

d. Delete enrollments in the above section where the student’s ID is 12345.

-- d. Delete enrollments in the above section where the student’s ID is 12345.
select * from takes;
delete from takes
where (course_id = 'CIS-001') and (sec_id = '1') and (semester = 'Fall') and (year = 2017)
    and (ID in (
      select ID
      from student
      where ID = 12345
      )
         );
select * from takes

e. Delete the course CS-001. What will happen if you run this delete statement without first deleting offerings (sections) of this course?

/*
e. Delete the course CS-001. What will happen 
-- if you run this delete statement without 
-- first deleting offerings (sections) of this course?
*/
delete from course where course_id = 'CS-001';
select * from section;
select * from course;
select name, sql
from sqlite_master
where name = 'section'
/* 
Comment - no any error. After delete the course,         
`section` has foreign key, course_id, but that foreign key has 
'ON DELETE cascade' constraints.
When deleting CS-001 from course which is referenced by `section`, 
tuples in `section` that have course_id as 'CS-001' will be automatically deleted.
*/

f. Delete all takes tuples corresponding to any section of any course with the word “advanced” as a part of the title; ignore case when matching the word with the title.

/*
f. Delete all takes tuples corresponding to any section of 
any course with the word “advanced” as a part of the title; 
ignore case when matching the word with the title.
*/
select * from course where title like '%Intro. to%';
select * 
from takes natural join course
where title like '%tics%';
delete from takes
where course_id in (
  select course_id
  from course
  where title like '%advanced'
);