create table student(regno varchar(20) primary key,name varchar(20),major varchar(20),bdate date);
create table course(courseno int primary key,cname varchar(20),dept varchar(20));
create table enroll(regno varchar(20),courseno int,sem int,marks int,primary key(regno,courseno,sem),foreign key(regno) references student(regno),foreign key(courseno) references course(courseno));
create table text(bookisbn int,booktitle varchar(20),publisher varchar(20),author varchar(20),primary key(bookisbn));
create table booka(courseno int,sem int,bookisbn int,primary key(courseno,sem),foreign key(courseno) references course(courseno),foreign key(bookisbn) references text(bookisbn));

insert into student values('1ms02cs001','lakshman','cs','01-jan-2005');
insert into student values('1ms02me001','dinesh','me','01-jan-2005');
insert into student values('1ms02is001','abhinav','is','01-jan-2005');
insert into student values('1ms02cs002','aashish','cs','01-jan-2005');
insert into student values('1ms02cs003','abhijit','cs','01-jan-2005');
insert into student values('1ms02cs004','abhinav','cs','01-jan-2005');

insert into course values(101,'dbms','cs');
insert into course values(102,'cg','cs');
insert into course values(201,'mech drawing','me');
insert into course values(301,'mis','is');
insert into course values(103,'ssm','cs');

insert into enroll values('1ms02cs001',101,6,99);
insert into enroll values('1ms02cs001',102,5,99);
insert into enroll values('1ms02cs002',101,4,99);
insert into enroll values('1ms02cs004',101,1,99);

insert into text values(10101010,'Intro to dbms','pearson','xyz');
insert into text values(10101011,'Advanced dbms','pearson','xyz');
insert into text values(10101012,'Dbms for gurus','pearson','xyz');
insert into text values(10101021,'autosim','pearson','flq');
insert into text values(11111111,'bookname','tmh','aaa');
insert into text values(20101021,'basic mech drawing','aw','abc');
insert into text values(20101022,'vtu mech drawing','subhas','def');

insert into  booka values(101,1,10101010);
insert into  booka values(101,4,10101011);
insert into  booka values(101,6,10101012);
insert into  booka values(201,1,20101021);
insert into  booka values(301,1,10101010);
insert into  booka values(301,2,11111111);
commit;

insert into text values(30303033,'mis basics','wiley','misauth');
insert into booka values(301,3,30303033);


select booka.courseno,booka.bookisbn,booktitle from course,booka,text 
where  course.dept='cs' and booka.courseno in (select courseno from booka
group by courseno having count(*)>2)
and booka.courseno=course.courseno and text.bookisbn=booka.bookisbn
order by booktitle
;


/* not equal is != */


create view x as select course.courseno,course.dept,text.bookisbn,publisher
from course,booka,text where course.courseno=booka.courseno and booka.bookisbn=text.bookisbn;

select distinct dept from course
where not exists
(select * from x where publisher!='pearson' and x.dept=course.dept);

drop view x;


drop table booka;
drop table text;
drop table enroll;
drop table course;
drop table student;