DBMS Practical Slips

12.Book_Publisher

create table book11
(
b_no number(5)primary key,
b_name varchar(20),
price number(10)check(price>0)
);

create table publisher12
(
pno number(5)primary key,
pname varchar2(20),
city varchar(20)
);

create table book_publisher13
(
b_no number(5)references book11(b_no),
pno number(5)references publisher12(pno),
quantity number(5)
);

insert into book11 values(101,'c programm',250);
insert into book11 values(102,'c++',100);
insert into book11 values(103,'e commerce',150);
insert into book11 values(104,'dbms',200);

insert into publisher12 values(1,'bpv','pune');
insert into publisher12 values(2,'bpv','mumbai');
insert into publisher12 values(3,'universal','pune');
insert into publisher12 values(4,'bpv','delhi');

insert into book_publisher13 values(101,1,2);
insert into book_publisher13 values(102,2,5);
insert into book_publisher13 values(103,3,1);
insert into book_publisher13 values(104,4,4);

1. select COUNT(book_publisher13.quantity) from book11,publisher12,book_publisher13
where book11.b_no=book_publisher13.b_no and
publisher12.pno=book_publisher13.pno;

COUNT(book_publisher13.QUANTITY)
-------------------------------------------------------------
                                                                               4

2. select pname from publisher12
where publisher12.city='pune';

PNAME
--------------------
bpv
universal

3. select pname from  book11,publisher12,book_publisher13
where book11.b_no=book_publisher13.b_no and
publisher12.pno=book_publisher13.pno and
book11.b_no>2;

PNAME
--------------------
bpv
bpv
universal
bpv


4.



5. select book11.* from book11,publisher12,book_publisher13
where book11.b_no=book_publisher13.b_no and
publisher12.pno=book_publisher13.pno order by pname;


  B_NO  B_NAME           PRICE
----------  -------------------- ----------
       101   c programm        250
       104   dbms                   200
       102   c++                     100
       103   e commerce        150