Database Management Systems (DBMS) Lab Program 4

Database Management Systems (DBMS) Lab Program



Question 4

CREATE TABLE author (authorid NUMBER(10),
name VARCHAR(30) NOT NULL,
city VARCHAR(30),
country VARCHAR(30),
PRIMARY KEY(authorid)
);


CREATE TABLE publisher (
publisherid NUMBER(10),
name VARCHAR(30) NOT NULL,
city VARCHAR(30),
country VARCHAR(30),
PRIMARY KEY(publisherid)
);

CREATE TABLE category (
catagoryid NUMBER(10),
description VARCHAR(50),
PRIMARY KEY(catagoryid)
);

CREATE TABLE catalog(
bookid NUMBER(10),
title VARCHAR(30) NOT NULL,
authorid NUMBER(30),
publisherid NUMBER(30),
price NUMBER(10),
year NUMBER(4),
catagoryid NUMBER(10),
PRIMARY KEY(bookid),
CONSTRAINT ca1 FOREIGN KEY (authorid) REFERENCES author(authorid),
CONSTRAINT ca2 FOREIGN KEY (publisherid) REFERENCES publisher(publisherid),
CONSTRAINT ca3 FOREIGN KEY (catagoryid) REFERENCES category (catagoryid)
);

CREATE TABLE ord_details (
orderno NUMBER(10),
bookid NUMBER(10),
qty NUMBER(10) NOT NULL,
PRIMARY KEY(orderno,bookid),
CHECK(qty>0),
CONSTRAINT or1 FOREIGN KEY (bookid) REFERENCES catalog(bookid)
);


INSERT INTO author VALUES (1,'Adarsha','Bangalore','India');
INSERT INTO author VALUES (2,'Ashish','Bangalore','India');
INSERT INTO author VALUES (3,'Abhishek','Bangalore','India');
INSERT INTO author VALUES (4,'Aashray','Bangalore','India');
INSERT INTO author VALUES (5,'Anirudh','Bangalore','India');

INSERT INTO publisher VALUES(1,'abc','Bangalore','India');
INSERT INTO publisher VALUES(2,'bcd','Bangalore','India');
INSERT INTO publisher VALUES(3,'def','Bangalore','India');
INSERT INTO publisher VALUES(4,'ghi','Bangalore','India');
INSERT INTO publisher VALUES(5,'klm','Bangalore','India');


INSERT INTO category VALUES(1,'1st cata');
INSERT INTO category VALUES(2,'2nd cata');
INSERT INTO category VALUES(3,'3rd cata');
INSERT INTO category VALUES(4,'4th cata');
INSERT INTO category VALUES(5,'5th cata');

INSERT INTO catalog VALUES
(1,'Book1',1,1,495,2010,1);
INSERT INTO catalog VALUES
(2,'Book2',2,2,399,2005,2);
INSERT INTO catalog VALUES
(3,'Book3',3,3,395,2009,3);
INSERT INTO catalog VALUES
(4,'Book4',4,4,785,2008,4);
INSERT INTO catalog VALUES
(5,'Book5',5,5,625,2010,5);
INSERT INTO catalog VALUES
(6,'Book6',5,5,625,2010,5);
INSERT INTO catalog VALUES
(7,'Book7',5,4,425,1990,5);
INSERT INTO catalog VALUES
(8,'Book8',3,5,525,2010,5);
INSERT INTO catalog VALUES
(9,'Book9',2,4,325,1999,5);
INSERT INTO catalog VALUES
(10,'Book10',5,2,225,2000,5);

INSERT INTO ord_details VALUES(1,2,5);
INSERT INTO ord_details VALUES(2,2,4);
INSERT INTO ord_details VALUES(3,1,6);
INSERT INTO ord_details VALUES(3,2,2);
INSERT INTO ord_details VALUES(4,2,3);
INSERT INTO ord_details VALUES(5,3,4);
INSERT INTO ord_details VALUES(5,2,4);

commit;


DESC author;
DESC publisher;
DESC category;
DESC catalog;
DESC ord_details;


SELECT * FROM author;
SELECT * FROM publisher;
SELECT * FROM category;
SELECT * FROM catalog;
SELECT * FROM ord_details;

----------------------------------------------------------------------------------------------------------
3.

select * from author a
where a.authorid IN
(
select authorid
from catalog
group by authorid
having count(authorid)>1)

AND authorid IN (
select authorid
from catalog c
where year>2000 AND price>(select avg(price) from catalog)
group by authorid
)
----------------------------------------------------------------------------------------------------------
4.

select a.*

from
(
select bookid as b,sum(qty) as s
from ord_details
group by bookid
) X,
(select max(s) as s
from (select bookid as b,sum(qty) as s
from ord_details
group by bookid)) Y,author a
WHERE X.s=Y.s and a.authorid=X.b;
----------------------------------------------------------------------------------------------------------
OR
----------------------------------------------------------------------------------------------------------
select a.*

from
(
(select o.bookid as b,sum(o.qty),c.price,sum(o.qty)*c.price as s
from ord_details o,catalog c
where o.bookid=c.bookid
group by o.bookid,c.price)
) X,
(select max(s) as s
from ((select o.bookid as b,sum(o.qty),c.price,sum(o.qty)*c.price as s
from ord_details o,catalog c
where o.bookid=c.bookid
group by o.bookid,c.price))) Y,author a
WHERE X.s=Y.s and a.authorid=X.b;

----------------------------------------------------------------------------------------------------------
5.

UPDATE (
select c.*
from catalog c,publisher p
where c.publisherid=p.publisherid and p.name='ghi')
SET price=1.1*price;