					More programs at www.pranavthegreat.cjb.net


CREATE TABLE STUDENT
(REGNO VARCHAR2(10),
NAME VARCHAR2(30) NOT NULL,
MAJOR VARCHAR2(20),
BDATE DATE,
CONSTRAINT TAB3PK1 PRIMARY KEY(REGNO));

CREATE TABLE COURSE
(COURSE_NO INT,
CNAME VARCHAR2(20) NOT NULL,
DEPT VARCHAR2(20),
CONSTRAINT TAB3PK2 PRIMARY KEY(COURSE_NO));

CREATE TABLE ENROLL
(REGNO VARCHAR2(10),
COURSE_NO INT NOT NULL,
SEM INT CHECK (SEM>0 AND SEM<9),
MARKS INT,
CONSTRAINT TAB3PK3 PRIMARY KEY(REGNO),
CONSTRAINT TAB3FK1 FOREIGN KEY(REGNO) REFERENCES STUDENT(REGNO),
CONSTRAINT TAB3FK2 FOREIGN KEY(COURSE_NO) REFERENCES COURSE(COURSE_NO));

CREATE TABLE TEXT
(ISBN INT,
TITLE VARCHAR2(30) NOT NULL,
PUBLISHER VARCHAR2(25) NOT NULL,
AUTHOR VARCHAR2(30) DEFAULT 'Pranav',
CONSTRAINT TAB3PK4 PRIMARY KEY(ISBN));

CREATE TABLE BOOK_ADP
(COURSE_NO INT,
REGNO VARCHAR2(10),
ISBN INT,
CONSTRAINT TAB3PK5 PRIMARY KEY(REGNO),
CONSTRAINT TAB3FK3 FOREIGN KEY(COURSE_NO) REFERENCES COURSE(COURSE_NO),
CONSTRAINT TAB3FK5 FOREIGN KEY(ISBN) REFERENCES TEXT(ISBN));

======================================================================
======================================================================

INSERT INTO STUDENT
VALUES('&REGNO','&NAME','&MAJOR','&BDATE');

INSERT INTO COURSE
VALUES(&COURSE_NO,'&CNAME','&DEPT');

INSERT INTO ENROLL
VALUES('&REGNO',&COURSE_NO,&SEM,&MARKS);

INSERT INTO TEXT
VALUES('&ISBN','&TITLE','&PUBLISHER','&AUTHOR');

SELECT * FROM COURSE;
SELECT * FROM TEXT;
INSERT INTO BOOK_ADP
VALUES(&COURSE_NO,'&REGNO',&ISBN);

======================================================================
======================================================================

INSERT INTO TEXT
VALUES('44444','FUNDAMENTALS OF ELECTRONICS','S CHAND','BALAGURUSWAMY');
INSERT INTO BOOK_ADP
VALUES(3,6,'44444');

SELECT A.COURSE_NO,B.ISBN,C.TITLE
FROM COURSE A,BOOK_ADP B, TEXT C
WHERE A.COURSE_NO=B.COURSE_NO AND
B.ISBN=C.ISBN AND A.DEPT='MECHANICAL'
AND A.COURSE_NO IN(
SELECT COURSE_NO
FROM BOOK_ADP
GROUP BY COURSE_NO
HAVING COUNT(COURSE_NO)>=2)
GROUP BY DEPT
ORDER BY C.TITLE;

SELECT C.DEPT
FROM COURSE C,BOOK_ADP B,TEXT T
WHERE C.COURSE_NO=B.COURSE_NO AND T.ISBN=B.ISBN AND PUBLISHER='S CHAND';
			alternative of above
SELECT DEPT
FROM COURSE C
WHERE ((SELECT B.ISBN
FROM BOOK_ADP B
WHERE B.COURSE_NO=C.COURSE_NO)
CONTAINS (SELECT T.ISBN
FROM TEXT T
WHERE PUBLISHER='&PUBLISHER'));
			alternative of above
SELECT DEPT
FROM COURSE C
WHERE NOT EXISTS
((SELECT T.ISBN
FROM TEXT T
WHERE PUBLISHER='&PUBLISHER')
EXCEPT
(SELECT B.ISBN
FROM BOOK_ADP B
WHERE B.COURSE_NO=C.COURSE_NO));

================================================================================================EXTRA QUERIES===================================


					More programs at www.pranavthegreat.cjb.net
