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 microsystem', 'sanfrasncisco');
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
WRITE A PL/SQL PROCEDURE TO INSERT THE RECORDS IN THE BOOKS TABLE:
SQL> create or replace procedure book_insert(bno in number, bname
2 in varchar2, pubno in number, dt in date, cost in number) as
3 begin
4 insert into book10 values(bno, bname, pubno, dt, cost);
5 end book_insert;
6 /
Procedure created.
BEFORE PROCEDURE EXECUTION:
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
PROCEDURE EXECUTION:
SQL> begin
2 book_insert(&book_number, &book_name, &publisher_number,
3 &date, &cost);
4 end;
5 /
Enter value for book_number: 510
Enter value for book_name: 'C SHARP'
Enter value for publisher_number: 102
old 2: book_insert(&book_number, &book_name, &publisher_number,
new 2: book_insert(510, 'C SHARP', 102,
Enter value for date: '12-JUN-07'
Enter value for cost: 3244
old 3: &date, &cost);
new 3: '12-JUN-07', 3244);
PL/SQL procedure successfully completed.
AFTER PROCEDURE EXECUTION:
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
510 C SHARP 102 12-JUN-07 3244
6 rows selected.
Comments