DBMS 16 Slips
Q.16.bloodgrroup_donor
create table don
(
dno number(10) primary key,
dname varchar2(20),
city varchar2(20)
);
create table bl_donat
(
bid number(10) primary key,
bl_group varchar2(10) NOT NULL,
quantity number(10),
dt_collec date,
dno number(10) references don(dno)
);
insert into don values(11,'seema','pune');
insert into don values(12,'sanjiv','kamshet');
insert into don values(13,'sagar','mumbai');
insert into don values(14,'suhas','panshet');
insert into don values(15,'samir','nasik');
insert into bl_donat values(1,'A+',10,'2 jun 2013',11);
insert into bl_donat values(2,'AB+',15,'15 jun 2013',12);
insert into bl_donat values(3,'A+',20,'20 jul 2013',13);
insert into bl_donat values(4,'B+',14,'25 dec 2013',14);
insert into bl_donat values(5,'O+',25,'27 jun 2013',15);
1. select sum(bl_donat.quantity) from don,bl_donat
where don.dno=bl_donat.dno and
bl_donat.dt_collec='25 dec 2013';
SUM(BL_DONAT.QUANTITY)
----------------------
14
2.select count(quantity),dname from don,bl_donat group by dname;
COUNT(QUANTITY) DNAME
--------------- -------------------
5 suhas
5 samir
5 seema
5 sanjiv
5 sagar
3. select don.* from don,bl_donat
where don.dno=bl_donat.dno and
bl_donat.bl_group='A+';
DNO DNAME CITY
------- -------------------- -----------
11 seema pune
13 sagar mumbai
4. select dname from don,bl_donat
where don.dno=bl_donat.dno and
bl_donat .bid>2;
DNAME
---------------
sagar
suhas
samir
5. select don.*,bl_group from don,bl_donat
where don.dno=bl_donat.dno and
city like '%sh%';
DNO DNAME CITY BL_GROUP
--------- -------------------- -------------------- ----------
12 sanjiv kamshet AB+
14 suhas panshet B+
Tags:
Dbms