Database Management Systems (DBMS) Lab Program 3

Database Management Systems (DBMS) Lab Program



Question 3
 

CREATE TABLE student (
regno VARCHAR(10) CONSTRAINT ss1 PRIMARY KEY,
name VARCHAR(10) CONSTRAINT ss2 NOT NULL,
major VARCHAR(10) CONSTRAINT ss3 NOT NULL,
bdate DATE
);

CREATE TABLE course (
courseno NUMBER(3) CONSTRAINT co1 PRIMARY KEY,
cname VARCHAR(10) CONSTRAINT co2 NOT NULL,
dept VARCHAR(10) CONSTRAINT co3 NOT NULL
);

CREATE TABLE text (
bookISBN NUMBER(10) CONSTRAINT t1 PRIMARY KEY,
book_title VARCHAR(50) CONSTRAINT t2 NOT NULL,
publisher VARCHAR(50),
author VARCHAR(50)
);

CREATE TABLE enroll (
regno VARCHAR(10) CONSTRAINT e11 REFERENCES student(regno) ON DELETE CASCADE,
courseno NUMBER(3) CONSTRAINT e12 REFERENCES course(courseno) ON DELETE CASCADE,
sem NUMBER(1) CONSTRAINT e13 NOT NULL,
marks NUMBER(5,2) CONSTRAINT e14 NOT NULL,
CONSTRAINT e15 PRIMARY KEY (regno,courseno)
);

CREATE TABLE book_adoption (
courseno NUMBER(3) CONSTRAINT b1 REFERENCES course(courseno) ON DELETE CASCADE,
sem NUMBER(1),
bookISBN NUMBER(10) CONSTRAINT b2 NOT NULL REFERENCES text(bookISBN) ON DELETE CASCADE,
PRIMARY KEY(courseno,sem)
);

DESC student;
DESC course;
DESC text
DESC enroll;
DESC book_adoption;

commit;


INSERT INTO student VALUES('ox03','Adarsha','CS','21-JUN-1990');
INSERT INTO student VALUES('ox07','Ashish','EC','18-MAR-1990');
INSERT INTO student VALUES('ox12','Bhavesh','IS','20-JUN-1990');
INSERT INTO student VALUES('ox02','Abhishek','CS','20-JUN-1989');
INSERT INTO student VALUES('ox01','Abhay','CV','2-JUN-1990');


INSERT INTO course VALUES(1,'Comp Sci','CS');
INSERT INTO course VALUES(2,'Electro','EC');
INSERT INTO course VALUES(3,'Info Sci','IS');
INSERT INTO course VALUES(4,'Civil','CV');
INSERT INTO course VALUES(5,'Biotech','BT');
INSERT INTO course VALUES(6,'abc','CS');

  

INSERT INTO text VALUES(1234,'Oper sys','Thiva','Tenanbom');
INSERT INTO text VALUES(4567,'FAFL','SIVA','Farajay');
INSERT INTO text VALUES(7890,'Genes','TGMC','Yan koa');
INSERT INTO text VALUES(1597,'Structures','IIT','Ramesh');
INSERT INTO text VALUES(3579,'VHDL','Oxford','Ashish');

INSERT INTO enroll VALUES('ox03',1,5,701);
INSERT INTO enroll VALUES('ox03',3,4,700);
INSERT INTO enroll VALUES('ox07',5,5,699);
INSERT INTO enroll VALUES('ox12',2,4,698);
INSERT INTO enroll VALUES('ox02',1,4,702);
INSERT INTO enroll VALUES('ox02',2,5,701);
INSERT INTO enroll VALUES('ox01',4,5,700);

INSERT INTO book_adoption VALUES(1,5,1234);
INSERT INTO book_adoption VALUES(2,4,3579);
INSERT INTO book_adoption VALUES(3,4,1234);
INSERT INTO book_adoption VALUES(4,5,1597);
INSERT INTO book_adoption VALUES(1,4,4567);
INSERT INTO book_adoption VALUES(2,5,4567);
INSERT INTO book_adoption VALUES(5,5,7890);
INSERT INTO book_adoption VALUES(6,5,4567);
INSERT INTO book_adoption VALUES(6,4,7410);
INSERT INTO book_adoption VALUES(6,6,1234);
INSERT INTO book_adoption VALUES(1,4,7410);

SELECT * FROM student;
SELECT * FROM course;
SELECT * FROM text;
SELECT * FROM enroll;
SELECT * FROM book_adoption;

commit;

------------------------------------------------------------------------------------------------------------
3.
INSERT INTO text VALUES(7410,'Computer Networks','Head First','Gimp');
INSERT INTO book_adoption VALUES(3,5,7410);
SELECT * FROM text;
SELECT * FROM book_adoption;
------------------------------------------------------------------------------------------------------------
4.

select distinct c.courseno,t.bookISBN,t.book_title
from course c,text t,book_adoption a
where c.courseno=a.courseno and a.bookISBN=t.bookISBN and c.dept LIKE 'CS' and c.courseno IN

(select x.courseno
from book_adoption x
group by x.courseno
having count(x.courseno)>2)


order by book_title;

SELECT * FROM course;
SELECT * FROM text;
SELECT * FROM book_adoption;

commit;
------------------------------------------------------------------------------------------------------------
5.

select DISTINCT c.dept
from course c,book_adoption a,text t
where c.courseno=a.courseno and t.bookISBN=a.bookISBN
and c.courseno NOT IN
(
select x.courseno
from book_adoption x
where x.bookISBN NOT IN (
select y.bookISBN
from text y
where y.publisher LIKE 'abc'));