Write the following queries in SQL, using the university schema. (You should actually check the queries you write on the database.)
(a) to find the names of all instructors whose salary is more than the 90% of the department average salary.
(b) for each course section offered in 2009, find the average total credits (tot cred) of all students enrolled in the section, if the section had at least 2 students
(c) to insert into instructors those students who have earned more than 140 credits with salary 90000 to their respective departments.
(d) to raise salary of instructors by 10% unless the salary becomes greater than 90,000; in such cases, give only a 3% raise.
(e) to delete all instructors whose salary is less than half of the average salary of all instructors.
This is the schema:
create table classroom
(building varchar(15),
room_number varchar(7),
capacity numeric(4,0),
primary key (building, room_number)
);
create table department
(dept_name varchar(20),
building varchar(15),
budget numeric(12,2)check (budget > 0),
primary key (dept_name)
);
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
);
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
);
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) referencesclassroom
on delete set null
);
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
);
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
);
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
);
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
);
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)
);
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
);