TABLE CREATION:
Design a book database with the following tables:
publisher10(pubid, name, city)
sql> create table publisher10(pubid number primary key, name varchar2(30) not null, city varchar2(20) not null);
Table created.
book10(id, title, publid, year, price)
sql> create table book10(id number primary key, title varchar2(40) not null, pubid number not null, year date not
null, price number(9,2));
Table created.
CONSTRAINTS ADDED TO TABLES:
sql> alter table book10 add constraint pub_fk foreign key(pubid) references publisher10(pubid);
Table altered.
TABLE DESCRIPTIONS:
sql> desc publisher10;
Name Null? Type
-------------- ---------------- ----------------------------
PUBID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(30)
CITY NOT NULL VARCHAR2(20)
sql> desc book10;
Name Null? Type
--------------- ---------------------- ----------------------------
ID NOT NULL NUMBER
TITLE NOT NULL VARCHAR2(40)
PUBID NOT NULL NUMBER
YEAR NOT NULL DATE
PRICE NUMBER(9,2)
INSERTING VALUES INTO PUBLISHER10 TABLE:
sql> insert into publisher10 values(100, 'microsoft', 'washington');
1 row created.
sql> insert into publisher10 values(101, 'sun', 'sun microsystem');
1 row created.
sql> insert into publisher10 values(102, 'oracle', 'britan');
1 row created.
sql> insert into publisher10 values(103, 'tatamcgraw hill', 'delhi');
1 row created.
sql> insert into publisher10 values(104, 'samba', 'chennai');
1 row created.
INSERTING VALUES INTO BOOK10 TABLE:
sql> insert into book10 values(500, 'java', 100,'20-JUN-2000',505);
1 row created.
sql> insert into book10 values(501, 'programming in c', 101,'10-AUG-2005',2350);
1 row created.
sql> insert into book10 values(504, 'dotnet', 104,'20-JAN-2005',35);
1 row created.
sql> insert into book10 values(502, 'perl', 102,'15-DEC-2002',450);
1 row created.
sql> insert into book10 values(506, 'cobol', 102,'12-SEP-2007',1525);
1 row created.
DISPLAYING TABLE CONTENTS:
sql> select * from publisher10;
PUBID NAME CITY
--------- ------------------- --------------------
100 microsoft washington
101 sun sun microsystem
102 oracle britan
103 tatamcgraw hill delhi
104 samba chennai
sql> select * from book10;
ID TITLE PUBID YEAR PRICE
------ ------------------------------ ----------- -------------- ----------
500 java 100 20-JUN-00 505
501 programming in c 101 10-AUG-05 2350
504 dotnet 104 20-JAN-05 35
502 perl 102 15-DEC-02 450
506 cobol 102 12-SEP-07 1525
DML QURIES:
a. Get the titles and publisher names of all the books that are priced above 500.
sql> select title, name from book10 join publisher10 using (pubid)where price>500;
TITLE NAME
--------------------------- ------------------------------
java microsoft
programming in c sun
cobol oracle
b. Get the title and price of all books published after the year 2006 priced above 400.
sql> select title, price from book10 where price>400 and extract(year from year)>2006;
TITLE PRICE
----------- ----------
cobol 1525
c. Get the no. of books published each year.
sql> select count(id), extract(year from year) as year_of_release2 from book10 group by extract(year from year);
COUNT(ID) YEAR_OF_RELEASE2
-------------- -----------------------------
2 2005
1 2007
1 2000
1 2002
d. Get the title and price of all books whose price is less than the average price of all books.
sql> select title, price from book10 where price<(select avg(price) from book10);
TITLE PRICE
---------------- ----------
java 505
dotnet 35
perl 450
WRITE A PL/SQL CURSOR TO DISPLAY THE DETAILS OF THE BOOKS WITH THE HIGHEST
PRICE.
SQL> set serveroutput on
SQL> declare
2 cursor book_cur is
3 select *
4 from book10
5 where price>=
6 (select max(price) from book10);
7 begin
8 for cr in book_cur
9 loop
10 dbms_output.put_line('book id:'||cr.id||','||'title:'
11 ||cr.title||','||'pubid:'||cr.pubid||','||'year'||cr.year||
12 ','||'price'||cr.price);
13 end loop;
14 end;
15 /
book id:501,title:programming in c,pubid:101,year10-AUG-05,price2350
PL/SQL procedure successfully completed.
Comments