Essay --

NAME : SRINIVAS GADDI ID # 700604772 HOME WORK-III SUBJECT: DATA BASE THEORY AND APPLICATIONS 3.11) a) select distinct name from student natural join takes natural join course where dept_name="comp.sci". b) (select name, id from student natural join takes) minus (select id, name from student natural join takes where year c) select dept_name, max(salary) from instructor group by dept_name. d) select min(max_salary) from (select dept_name, max(salary) max_salary from instructor group by dept_name). 3.12 a) insert into course ( title, course_id, credits ,dept_name) values ('weekly seminar','CS-001',0,'comp.sci"). b) insert into section (sec_id,couse_id,year,semester) values('1','CS-001,2009 ,'autumn'). c) insert into takes(course_id,id,sec_id, year ,semester ) select 'cs-001',id,'1',2009,'autumn' from student where dept_name='comp.sci'.; d) delete from takes where (sec_id = '1') and (course_id = 'CS-001') and (year = 2009) and (semester = 'Fall') and (id in (select id from student where name = 'Chavez' )); e) delete from course where course_id ='cs-001'. this will executed successfully because section has foreign key which has on delete cascade constraints so when we try to delete any course it will not affect any table . f) delete from takes where course_id in (select course_id from course where lower(title) like '%database%'); 3.13) creation of person table:- create table person (driver_id varchar(25), name varchar(25), address varchar(25), primary key (driver_id)); creation of car table:- create table car (license varchar(25), model varchar(25), year int, primary key (license)); creation of accident table:- create table accident (rep... ...t title) from course)) 3.23) The similar attributes of takes table and section table form a foreign key of takes, referencing section. due to this , each takes column should match at most one section column ,and there should not be any extra columns in any group. These attributes cannot take on the null value, since they are part of the primary key of takes. Thus, joining section in the from clause should not reflect any loss of columns in any group. As a result, there would be no change in the result. 3.24) Use sub queries in place of where clause, with one of the sub queries having a second level sub query in the from clause as below. select distinct dept_name as D from instructor as A where (select sum(salary) from instructor where dept_name = D) >= (select avg (B) from (select sum(salary) as A from instructor group by dept_name));