Consider the following ‘university’ database schema :

i. department (dept_name, building, budget) ii. instructor (id, name, dept_name, salary) iii. student (id, name, dept_name, tot_cred) iv. course (id, title, dept_name, credits) v. section (course_id, sec_id, semester, year, building, room_number, time_slot_id) vi. teaches (instructor_id, course_id, sec_id, semester, year) vii. takes (student_id, course_id, sec_id, semester, year, grade)

Write SQL statements for the following queries :

1) Create the table department and instructor with appropriate data types and integrity constraints.

2) Create the table department and student with appropriate data types and integrity constraints.

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

4) Fine the names of instructors in the Biology department who earns more than 100000. 5) Find the IDs and titles of all courses which were taught by an instructor named Einstein. Make sure there are no duplicates in the result.

6) Find the IDs and titles of all courses which were taken by a student named Knuth. Make sure there are no duplicates in the result.

7) Find all course IDs which start with ‘CSE’.

8) Find all course titles which have a string ‘Introduction’.

9) Find the highest salary of any instructor.

10) Find the lowest salary of any instructor.

11) Find the course titles that were offered in Summer 2014 but not in Fall 2014.

12) Find the course titles that were offered in Summer 2014 or Fall 2014.

13) For each department, find the maximum salary of instructors in that department.

14) For each department, find the total credit hours of courses in that department.

15) Find the enrollment (number of students) of each section that was offered in Fall 2009.

16) Find the total number of instructors from Comp. Sci. department taken a course that was offered in Fall 2009.

17) Increase (update) the salary of each instructor in the Comp. Sci. department by 10% if their current salary is in between 0 and 25000.

18) Increase (update) the salary of each instructor in the Comp. Sci. department by 5% if their current salary is in between 25001 and 50000. salary computed by the preceding query.

19) Delete instructors of the Physics department.

20) Delete courses below 2 credits.

Solutions (Odd no.):

-->> 01.create table department(

dept_name varchar2(10) primary key,building varchar2(15) not null,budget number not null

);

create table instructor(

id char(5) primary key,name varchar2(20) not null,dept_name varchar2(10) references department,salary number not null

);

-->> 03.select title from course where dept_name = 'Comp. Sci.' and credits = 3;

-->> 05.select course.id,title from course,instructor,teaches where course.id=teaches.course_id and instructor.id = teaches.instructor_id and instructor.name='Einstein';

-->> 07.select id from course where id like 'CSE%';

-->> 09.select max(salary) from instructor;

-->> 11.(select title.course from course,section where course.id=section.course_id and semester = 'Summer' and year = '2014') minus (select title.course from course,section where course.id=section.course_id and semester ='Fall' and year = '2014');

-->> 13.select dept_name, max(salary) from instructor group by dept_name;

-->> 15.select sec_id,count(student_id) from takes group by sec_id having semester ='Fall' and year ='2009';

-->> 17.update instructor set salary = salary*1.1 where dept_name ='Comp. Sci' and salary between 0 and 25000;

-->> 19.delete from instructor where dept_name = 'Physics';