T01 SQLite Tutorial

Instruction Table

name    sql
classroom    
CREATE TABLE classroom
    (
    building        varchar(15),
         room_number        varchar(7),
         capacity        numeric(4,0),
         primary key (building, room_number)
    )
department    
CREATE TABLE department
    (dept_name        varchar(20), 
     building        varchar(15), 
     budget                numeric(12,2) check (budget > 0),
     primary key (dept_name)
    )
course    
CREATE TABLE course
    (course_id        varchar(8), 
     title            varchar(50), 
     dept_name        varchar(20),
     credits        numeric(2,0) check (credits > 0),
     primary key (course_id),
     foreign key (dept_name) references department
        on delete set null
    )
instructor    
CREATE TABLE instructor
    (ID            varchar(5), 
     name            varchar(20) not null, 
     dept_name        varchar(20), 
     salary            numeric(8,2) check (salary > 29000),
     primary key (ID),
     foreign key (dept_name) references department
        on delete set null
    )
section    
CREATE TABLE section
    (course_id        varchar(8), 
         sec_id            varchar(8),
     semester        varchar(6)
        check (semester in ('Fall', 'Winter', 'Spring', 'Summer')), 
     year            numeric(4,0) check (year > 1701 and year < 2100), 
     building        varchar(15),
     room_number        varchar(7),
     time_slot_id        varchar(4),
     primary key (course_id, sec_id, semester, year),
     foreign key (course_id) references course
        on delete cascade,
     foreign key (building, room_number) references classroom
        on delete set null
    )
teaches    
CREATE TABLE teaches
    (ID            varchar(5), 
     course_id        varchar(8),
     sec_id            varchar(8), 
     semester        varchar(6),
     year            numeric(4,0),
     primary key (ID, course_id, sec_id, semester, year),
     foreign key (course_id,sec_id, semester, year) references section
        on delete cascade,
     foreign key (ID) references instructor
        on delete cascade
    )
student    
CREATE TABLE student
    (ID            varchar(5), 
     name            varchar(20) not null, 
     dept_name        varchar(20), 
     tot_cred        numeric(3,0) check (tot_cred >= 0),
     primary key (ID),
     foreign key (dept_name) references department
        on delete set null
    )
takes    
CREATE TABLE takes
    (ID            varchar(5), 
     course_id        varchar(8),
     sec_id            varchar(8), 
     semester        varchar(6),
     year            numeric(4,0),
     grade                varchar(2),
     primary key (ID, course_id, sec_id, semester, year),
     foreign key (course_id,sec_id, semester, year) references section
        on delete cascade,
     foreign key (ID) references student
        on delete cascade
    )
advisor    
CREATE TABLE advisor
    (s_ID            varchar(5),
     i_ID            varchar(5),
     primary key (s_ID),
     foreign key (i_ID) references instructor (ID)
        on delete set null,
     foreign key (s_ID) references student (ID)
        on delete cascade
    )
time_slot    
CREATE TABLE time_slot
    (time_slot_id        varchar(4),
     day            varchar(1),
     start_hr        numeric(2) check (start_hr >= 0 and start_hr < 24),
     start_min        numeric(2) check (start_min >= 0 and start_min < 60),
     end_hr            numeric(2) check (end_hr >= 0 and end_hr < 24),
     end_min        numeric(2) check (end_min >= 0 and end_min < 60),
     primary key (time_slot_id, day, start_hr, start_min)
    )
prereq    
CREATE TABLE prereq
    (course_id        varchar(8), 
     prereq_id        varchar(8),
     primary key (course_id, prereq_id),
     foreign key (course_id) references course
        on delete cascade,
     foreign key (prereq_id) references course
    )

Exercise

Quiz 1
Write the following queries in SQL, using the university schema. 
a. Find the titles of courses in the Comp. Sci. department that have 3 credits.
b. Find the IDs of all students who were taught by an instructor named Einstein; make sure there are no duplicates in the result.
c. Find the highest salary of any instructor.
d. Find all instructors earning the highest salary (there may be more than one with the same salary).
e. Find the enrollment of each section that was offered in Fall 2017. (Change Mistake)
f. Find the maximum enrollment, across all sections, in Fall 2017.(Change Mistake)
g. Find the sections that had the maximum enrollment in Fall 2017.(Change Mistake)
Quiz 2
Write the following inserts, deletes or updates in SQL, using the university schema.
a. Increase the salary of each instructor in the Comp. Sci. department by 10%.
b. Delete all courses that have never been offered (that is, do not occur in the section relation).
c. Insert every student whose tot cred attribute is greater than 100 as an instructor in the same department, with a salary of $10,000.

Exercise Answer

Question 1

a. Find the titles of courses in the Comp. Sci. department that have 3 credits.

-- a. Find the titles of courses in 
-- the Comp. Sci. department that have 3 credits.
select title, credits
from course
where dept_name = "Comp. Sci." and credits == 3

b. Find the IDs of all students who were taught by an instructor named Einstein; make sure there are no duplicates in the result.

-- b. Find the IDs of all students 
-- who were taught by an instructor named Einstein; 
-- make sure there are no duplicates in the result.
select ID,course_id,grade
from takes
where (course_id, sec_id, semester, year) in 
    (select course_id, sec_id, semester, year
     from teaches, instructor
     where teaches.ID = instructor.ID and name= "Einstein")

The correct answer (b)

select distinct student.ID
from (student join takes using(ID))  
    join (instructor join teaches using (ID))
    using (course_id, sec_id, semester, year)
where instructor.name = 'Einstein'

c. Find the highest salary of any instructor.

-- c. Find the highest salary of any instructor.
select max(salary) as MAX, name, dept_name
from instructor
group by dept_name
select max(salary), name
from instructor

d. Find all instructors earning the highest salary (there may be more than one with the same salary).

-- d. Find all instructors earning 
-- the highest salary (there may be more than one with the same salary).
select ID, name
from instructor
where salary = (
  select max(salary)
  from instructor
)

The correct answer (d)

select ID, name
from instructor
where salary = (select max(salary) from instructor)

e. Find the enrollment of each section that was offered in Fall 2017.

-- e. Find the enrollment of each section that was offered in Autumn 2009.
select *
from section
where (course_id,sec_id,semester,year) in
    (select course_id, sec_id, semester, year
     from takes
    where semester = "Fall" and year = "2017")

The correct answer (e)

select course_id, sec_id, count(ID)
from section natural join takes
where semester = 'Fall'
            and year = '2017'
group by course_id, sec_id

f. Find the maximum enrollment, across all sections, in Fall 2017.

-- f. Find the maximum enrollment, across all sections, in Autumn 2009.  
select course_id, count(course_id) as Num
from takes
where semester = "Fall" and year = "2017"
group by "course_id"
order by course_id asc

The correct answer (f)

select max(enrollment)
from (select count(ID) as enrollment
      from section natural join takes
      where (semester = 'Fall'
             and year = '2017')
      group by course_id, sec_id)

g. Find the sections that had the maximum enrollment in Fall 2017.

-- g. Find the sections that had the maximum enrollment in Autumn 2009.  
select course_id, count(course_id) as Num
from takes
where year = "2017" and semester = "Fall"
group by "course_id"
order by course_id asc

The correct answer (g)

with sec_enrollment as (
  select course_id, sec_id, count(ID) as enrollment
  from section natural join takes
  where (semester = 'Fall'
         and year = '2017')
  group by course_id, sec_id)
select course_id, sec_id
from sec_enrollment
where enrollment = (select max(enrollment) from sec_enrollment)

Question 2

Quiz 2
Write the following inserts, deletes or updates in SQL, using the university schema.

a. Increase the salary of each instructor in the Comp. Sci. department by 10%.

-- a. Increase the salary of each instructor in the Comp. Sci. department by 10%.
update instructor
set salary = salary * 1.10
where dept_name = 'Comp. Sci.'

b. Delete all courses that have never been offered (that is, do not occur in the section relation).

-- b. Delete all courses that have never been offered 
-- (that is, do not occur in the section relation).
delect from course
where course_id not in (
  select course_id from section)

c. Insert every student whose tot_cred attribute is greater than 100 as an instructor in the same department, with a salary of $10,000.

-- c. Insert every student whose tot cred attribute is greater than 100 
-- as an instructor in the same department, with a salary of $10,000.
insert into instructor
select ID, name, dept_name, 30000
from student
where tot_cred > 100
-- Error >> 
-- Uncaught Error: CHECK constraint failed: instructor
-- need to focus on constraint. (salary > 29000)