					More programs at www.pranavthegreat.cjb.net


CREATE TABLE AUTHOR
(AUTHOR_ID INT,
NAME VARCHAR2(20) NOT NULL,
CITY VARCHAR2(20) DEFAULT 'BANGALORE',
COUNTRY VARCHAR2(20) DEFAULT 'INDIA',
CONSTRAINT TAB4PK1 PRIMARY KEY(AUTHOR_ID));

CREATE TABLE PUBLISHER
(PUBLISHER_ID INT,
NAME VARCHAR2(20) NOT NULL,
CITY VARCHAR2(20) DEFAULT 'BANGALORE',
COUNTRY VARCHAR(20) DEFAULT 'INDIA',
CONSTRAINT TAB4PK2 PRIMARY KEY(PUBLISHER_ID));

CREATE TABLE CATEGORY
(CATEGORY_ID INT,
DESCRIPTION VARCHAR2(30) DEFAULT 'COMPUTERS',
CONSTRAINT TAB4PK3 PRIMARY KEY (CATEGORY_ID));

CREATE TABLE CATALOG
(BOOK_ID INT,
TITLE VARCHAR2(20),
AUTHOR_ID INT,
PUBLISHER_ID INT,
CATEGORY_ID INT,
YEAR INT CHECK (YEAR>1600 AND YEAR<2006),
PRICE INT DEFAULT 150,
CONSTRAINT TAB4PK4 PRIMARY KEY(BOOK_ID),
CONSTRAINT TAB4FK1 FOREIGN KEY(AUTHOR_ID) REFERENCES AUTHOR(AUTHOR_ID),
CONSTRAINT TAB4FK2 FOREIGN KEY(PUBLISHER_ID) REFERENCES PUBLISHER(PUBLISHER_ID),
CONSTRAINT TAB4FK3 FOREIGN KEY(CATEGORY_ID) REFERENCES CATEGORY(CATEGORY_ID));

CREATE TABLE ORDER_DETAILS
(ORDER_NO INT,
BOOK_ID INT,
QTY INT DEFAULT 1,
CONSTRAINT TAB4PK5 PRIMARY KEY(ORDER_NO),
CONSTRAINT TAB4FK4 FOREIGN KEY(BOOK_ID) REFERENCES CATALOG(BOOK_ID));

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

INSERT INTO AUTHOR
VALUES(&AUTHOR_ID,'&NAME','&CITY','&COUNTRY');

INSERT INTO PUBLISHER
VALUES(&PUBLISHER_ID,'&NAME','&CITY','&COUNTRY');

INSERT INTO CATEGORY
VALUES(&CATEGORY_ID,'&DESCRIPTION');

INSERT INTO CATALOG
VALUES(&BOOK_ID,'&TITLE',&AUTHOR_ID,&PUBLISHER_ID,&CATEGORY_ID,&YEAR,&PRICE);

INSERT INTO ORDER_DETAILS
VALUES(&ORDER_NO,&BOOK_ID,&QTY);

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

SELECT NAME,AUTHOR_ID
FROM AUTHOR
WHERE AUTHOR_ID IN (SELECT AUTHOR_ID
FROM CATALOG
WHERE YEAR>2000 AND (SELECT AVG(PRICE) FROM CATALOG)<PRICE
GROUP BY AUTHOR_ID
HAVING COUNT(*)>=2);
*********************
VARIATION OF THE SAME
*********************
SELECT  A.AUTHOR_ID,A.NAME
FROM AUTHOR A,CATALOG C
WHERE A.AUTHOR_ID=C.AUTHOR_ID AND
(SELECT AVG(PRICE) FROM CATALOG)<PRICE AND C.YEAR>2000
GROUP BY A.AUTHOR_ID,A.NAME
HAVING COUNT(*)>=2;

SELECT A.NAME
FROM AUTHOR A,CATALOG C
WHERE A.AUTHOR_ID=C.AUTHOR_ID AND C.BOOK_ID IN
(SELECT BOOK_ID
FROM ORDER_DETAILS
GROUP BY BOOK_ID
HAVING SUM(QTY)>=ALL(SELECT SUM(QTY)
FROM ORDER_DETAILS
GROUP BY BOOK_ID));

UPDATE CATALOG
SET PRICE=PRICE*1.1
WHERE PUBLISHER_ID='&PUBLISHER';
=========================================================================

SELECT BOOK_ID,TITLE,A.AUTHOR_ID
FROM CATALOG C,AUTHOR A
WHERE A.AUTHOR_ID=C.AUTHOR_ID AND 
NAME LIKE'C%' OR NAME LIKE'J%' AND
TITLE LIKE'E%' OR TITLE LIKE'J%';

					More programs at www.pranavthegreat.cjb.net
