See Our team
Wondering how we keep quality?
Got unsolved questions? Ask Questions
GATE
GMAT
CBSE
NCERT
Career
Interview
Railway
UPSC
NID
NIFT-UG
NIFT-PG
PHP
AJAX
JavaScript
Node Js
Shell Script
Research
DATABASE MANAGEMENT SYSTEMS LABORATORY [10CSL57] VTU prg-4
4. The following tables are maintained by a book dealer.
AUTHOR (author-id:int, name:string, city:string, country:string)
PUBLISHER (publisher-id:int, name:string, city:string, country:string)
CATALOG (book-id:int, title:string, author-id:int, publisher-id:int, category-id:int, year:int, price:int)
CATEGORY (category-id:int, description:string)
ORDER-DETAILS (order-no:int, book-id:int, quantity:int)
i. Create the above tables by properly specifying the primary keys and the foreign keys.
ii. Enter at least five tuples for each relation.
iii. Give the details of the authors who have 2 or more books in the catalog and the price of the books is greater than the average price of the books in the catalog and the year of publication is after 2000.
iv. Find the author of the book which has maximum sales.
v. Demonstrate how you increase the price of books published by a specific publisher by 10%.
vi. Generate suitable reports.
vii. Create suitable front end for querying and displaying the results.
4. The following tables are maintained by a book dealer.
AUTHOR (author-id:int, name:string, city:string, country:string)
PUBLISHER (publisher-id:int, name:string, city:string, country:string)
CATALOG (book-id:int, title:string, author-id:int, publisher-id:int, category-id:int, year:int, price:int)
CATEGORY (category-id:int, description:string)
ORDER-DETAILS (order-no:int, book-id:int, quantity:int)
i. Create the above tables by properly specifying the primary keys and the foreign keys.
CREATE TABLE AAUTHOR
( AUTHORID INTEGER PRIMARY KEY,
NAME VARCHAR(30) NOT NULL,
CITY VARCHAR(30) NOT NULL,
COUNTRY VARCHAR(30) NOT NULL);
CREATE TABLE PPUBLISHER
(PUBLISHERID INTEGER PRIMARY KEY,
NAME VARCHAR(30) NOT NULL,
CITY VARCHAR(30) NOT NULL,
COUNTRY VARCHAR(30) NOT NULL);
CREATE TABLE BOOKCATEGORY
(CATEGORYID INTEGER PRIMARY KEY,
DESCRIPTION VARCHAR(30) NOT NULL);
CREATE TABLE CCATALOG
(BOOKID INTEGER PRIMARY KEY,
TITLE VARCHAR(30) NOT NULL,
AUTHORID INTEGER NOT NULL,
PUBLISHERID INTEGER NOT NULL,
CATEGORYID INTEGER NOT NULL,
YEAROFPUBLISH INTEGER NOT NULL,
PRICE INTEGER NOT NULL,
FOREIGN KEY (AUTHORID) REFERENCES AAUTHOR(AUTHORID),
FOREIGN KEY (PUBLISHERID) REFERENCES PPUBLISHER(PUBLISHERID),
FOREIGN KEY (CATEGORYID) REFERENCES BOOKCATEGORY(CATEGORYID));
CREATE TABLE OORDERDETAILS
(ORDERNO INTEGER PRIMARY KEY,
BOOKID INTEGER NOT NULL,
QUANTITY INTEGER NOT NULL,
FOREIGN KEY (BOOKID) REFERENCES CCATALOG(BOOKID));
ii. Enter at least five tuples for each relation.
INSERT INTO AAUTHOR VALUES (1,'NAVATHE','ARLINGTON','USA');
INSERT INTO AAUTHOR VALUES (2,'RAGHU RAMAKRISHNAN','CALIFORNIA','USA');
INSERT INTO AAUTHOR VALUES (3,'DHAMDHERE','MUMBAI','INDIA');
INSERT INTO AAUTHOR VALUES (4,'BJARNE','NEW JERSY','USA');
INSERT INTO AAUTHOR VALUES (5,'TANENBAUM','AMSTERDAM','NETHERLAND');
INSERT INTO PPUBLISHER VALUES (1,'JOHN WILEY','NEW YORK','USA');
INSERT INTO PPUBLISHER VALUES (2,'PEARSON','BANGALORE','INDIA');
INSERT INTO PPUBLISHER VALUES (3,'O REILLY','NEW JERSY','USA');
INSERT INTO PPUBLISHER VALUES (4,'TMH','CALCUTTA','INDIA');
INSERT INTO PPUBLISHER VALUES (5,'JOHN WILEY','NEW DELHI','INDIA');
INSERT INTO BOOKCATEGORY VALUES (1,'DATABASE MANAGEMENT');
INSERT INTO BOOKCATEGORY VALUES (2,'OPERATING SYSTEMS');
INSERT INTO BOOKCATEGORY VALUES (3,'C++');
INSERT INTO BOOKCATEGORY VALUES (4,'COMPUTER NETWORKS');
INSERT INTO BOOKCATEGORY VALUES (5,'C');
INSERT INTO CCATALOG VALUES (1,'FUNDAMENTALS OF DBMS',1,2,1,2004,500);
INSERT INTO CCATALOG VALUES (2,'PRINCIPLES OF DBMS',2,1,1,2004,400);
INSERT INTO CCATALOG VALUES (3,'OPERATING SYSTEMS',3,4,2,2004,200);
INSERT INTO CCATALOG VALUES (4,'C++ BIBLE',4,5,3,2003,500);
INSERT INTO CCATALOG VALUES (5,'COMPUTER NETWORKS',5,3,4,2002,250);
INSERT INTO CCATALOG VALUES (6,'FUNDAMENTALS OF C',1,2,5,2004,700);
INSERT INTO CCATALOG VALUES (7,'OPERATING SYSTEMS 2',3,2,2,2001,600);
INSERT INTO OORDERDETAILS VALUES (1,1,1);
INSERT INTO OORDERDETAILS VALUES (2,2,1);
INSERT INTO OORDERDETAILS VALUES (3,3,1);
INSERT INTO OORDERDETAILS VALUES (4,4,1);
INSERT INTO OORDERDETAILS VALUES (5,5,1);
INSERT INTO OORDERDETAILS VALUES (6,6,7);
INSERT INTO OORDERDETAILS VALUES (7,7,9);
iii. Give the details of the authors who have 2 or more books in the catalog and the price of the books is greater than the average price of the books in the catalog and the year of publication is after 2000.
SELECT *
FROM AAUTHOR A
WHERE EXISTS
(SELECT A1.AUTHORID,COUNT(A1.AUTHORID)
FROM AAUTHOR A1,CCATALOG C
WHERE A1.AUTHORID=C.AUTHORID AND
A.AUTHORID=A1.AUTHORID AND
C.YEAROFPUBLISH > 2000 AND
C.PRICE > (SELECT AVG(PRICE)
FROM CCATALOG)
GROUP BY A1.AUTHORID
HAVING COUNT(A1.AUTHORID) >=2);
iv. Find the author of the book which has maximum sales.
SELECT DISTINCT A.NAME
FROM AAUTHOR A, CCATALOG C, OORDERDETAILS ODM
WHERE A.AUTHORID=C.AUTHORID AND ODM.BOOKID=C.BOOKID AND
EXISTS
(SELECT OD.BOOKID,SUM(OD.QUANTITY)
FROM OORDERDETAILS OD
WHERE OD.BOOKID=ODM.BOOKID
GROUP BY BOOKID
HAVING SUM(OD.QUANTITY)>= ALL
(SELECT SUM(QUANTITY)
FROM OORDERDETAILS
GROUP BY BOOKID));
v. Demonstrate how you increase the price of books published by a specific publisher by 10%.
UPDATE CCATALOG
SET PRICE = (1.1) * PRICE
WHERE AUTHORID = (SELECT AUTHORID
FROM AAUTHOR
WHERE NAME = 'NAVATHE');
vi. Generate suitable reports.
vii. Create suitable front end for querying and displaying the results
Editors
Checkout our YouTube channel for video tutorials and learn more about Eduladder.
New updates
Similar Questions
Notes
Management And Entrepreneurship Unit-1DATABASE MANAGEMENT SYSTEMS LABORATORY [10CSL57] VTU prg-4DATABASE MANAGEMENT SYSTEMS LABORATORY [10CSL57] VTU prg-3ADVANCED DBMS 10CS751Intelligent Management Of Electrical Systems in IndustriDATABASE MANAGEMENT SYSTEMS LABORATORY [10CSL57] VTU prg-5TeradataDATABASE MANAGEMENT SYSTEMS LABORATORY [10CSL57] VTU prg-206is65 information system and management vtu notes unit-1DATABASE MANAGEMENT SYSTEMS LABORATORY [10CSL57] VTU prg-1