NAME: Rutvi Shah

Roll NO:38
NAME: Rutvi shah
Roll No: 38
Assignment: RDMS

SET-6
 DOCTOR (DNO, DNAME, SPECIALIZATION, CLINIC_ADDR)
create table doctor (dno number (5) primary key, dname
varchar (20), SPECIALIZATION varchar (30), CLINIC_ADDR
varchar (30));
 MEDICINE (MNO, MNAME, TYPE, CONTENT, MANUFACTURER)
CREATE TABLE MEDICINE (MNO VARCHAR(20) PRIMARY
KEY,MNAME VARCHAR(200) NOT NULL,TYPE VARCHAR(40) NOT
NULL,CONTENT VARCHAR(500) NOT NULL,MANUFACTURER
VARCHAR(100) NOT NULL);
 DISEASE (DISEASE_NAME, SYMPTOM1, SYMPTOM2, SYMPTOM3)
CREATE TABLE DISEASE (DISEASE_NAME VARCHAR(100) NOT
NULL,SYMPTOM1 VARCHAR(40) NOT NULL,SYMPTOM2
VARCHAR(40) NOT NULL,SYMPTOM3 VARCHAR(40) NOT NULL);
 TREATMENT (TNO, DNO, DISEASE_NAME, MNO, DOSAGE,
AVG_CURE_TIME)
CREATE TABLE TREATMENT (TNO INT PRIMARY KEY,DNO INT
REFERENCES doctor(DNO) ,DISEASE_NAME VARCHAR(100)
REFERENCES disease(DISEASE_NAME),MNO VARCHAR(20)
REFERENCES medicine(MNO),DOSAGE int NOT
NULL,AVG_CUR_TIME int NOT NULL);

 INSERT INTO DOCTOR (DNO, DNAME, SPECIALIZATION,

CLINIC_ADDR) VALUES
 INSERT INTO MEDICINE (MNO, MNAME, TYPE, CONTENT,
MANUFACTURER) VALUES
 NAME: Rutvi Shah
Roll NO:38
 INSERT INTO DISEASE (DISEASE_NAME, SYMPTOM1,
SYMPTOM2, SYMPTOM3) VALUES
 INSERT INTO TREATMENT (TNO, DNO, DISEASE_NAME, MNO,
DOSAGE, AVG_CURE_TIME) VALUES

 Do as directed:

1. Display records of each table in ascending order.

 select * from doctor order by dname;

 select * from medicine order by mname;
 select * from disease order by symptom1;
 select * from treatment order by mno;
2. Count total number of doctors which has not given any treatment.
 select count(dno) from doctor where special = '';
COUNT (dno)
--------------------
2
3. Display all Chennai doctors who treat cancer.
 select dno, dname from doctor where specilization = 'cancer'
and clinic_addr = 'chennai';
DNO DNAME
---------- --------------------------------------------------
2 dr. prakash
4. Remove disease “polio” from disease table as well as treatment
table.
 delete from treatment where disease_name = 'blood cancer';
1 rows deleted.
 delete from disease where disease_name = 'blood cancer';
1 rows deleted.
5. Delete all those treatments related to liver of Dr. Shah.
 delete from treatment where dno = (select dno from doctor
where dname='dr. shah');
1 rows deleted.
 NAME: Rutvi Shah
Roll NO:38

6. Create index on dno, Disease name in the treatment table.

 create index treat on treatment (dno, disease_name);
Index created.
7. Display details of doctors who treat migraines.
 select d.dno, d.dname from doctor d, treatment t where d.dno
= t.dno and disease_name = 'depression';

DNO DNAME
---------- --------------------------------------------------
1 dr.heena
8. What is the maximum dosage of “penicillin” prescribe by the doctor
for the treatment of any disease?
 select max(dosage) from treatment where mno = (select mno
from medicine where mname = 'penicilline');
MAX(DOSAGE)
-----------
3
9. Display total number of disease treated by every doctor.
 select count(dno) from treatment;
COUNT(DNO)
----------
5
10.Which doctor have no treatment for “depression”?
 select * from doctor where dno not in (select dno from
treatment where disease_name='depression');

DNO DNAME
---------- --------------------------------------------------
SPECILIZATION
--------------------------------------------------
CLINIC_ADDR
--------------------------------------------------
1 dr.prakash
cancer
meghaninagar
 NAME: Rutvi Shah
Roll NO:38
3 dr.heena
depression
gandhinagar

DNO DNAME
---------- --------------------------------------------------
SPECILIZATION
--------------------------------------------------
CLINIC_ADDR
--------------------------------------------------
11.Create a view which contains the treatment and doctors details.
Make sure that no body is allowed to modify any detail in the view.
 View created
12.Write a PL/SQL block to print the following report (Symptoms wise
print total number of medicine given)

 DECLARE
CURSOR symptom_cursor IS
SELECT MNAME, SYMPTOM1, SYMPTOM2, SYMPTOM3, COUNT(*) AS total

FROM MEDICINE
INNER JOIN TREATMENT ON MEDICINE.MNO = TREATMENT.MNO
INNER JOIN DISEASE ON TREATMENT.DISEASE_NAME = DISEASE.DISEASE_NAME
GROUP BY MNAME, SYMPTOM1, SYMPTOM2, SYMPTOM3;
BEGIN

 DBMS_OUTPUT.PUT_LINE('Medicine Symptom1 Symptom2 Symptom3

Total');
FOR symptom_rec IN symptom_cursor LOOP

DBMS_OUTPUT.PUT_LINE(symptom_rec.MNAME || ' ' ||

symptom_rec.SYMPTOM1 || ' ' || symptom_rec.SYMPTOM2 || ' ' ||
symptom_rec.SYMPTOM3 || ' ' || symptom_rec.total);

END LOOP;
END;
 NAME: Rutvi Shah
Roll NO:38
/

13)Write a trigger which does not allow to insert or update treatment table if
AVG_CURE_TIME is less than 1.
 CREATE OR REPLACE TRIGGER prevent_low_cure_time
BEFORE INSERT OR UPDATE ON TREATMENT
FOR EACH ROW
BEGIN
IF: NEW.AVG_CURE_TIME < 1 THEN
RAISE_APPLICATION_ERROR(-20001, 'AVG_CURE_TIME must
be at least 1');
END IF;
END;
/
 NAME: Rutvi Shah
Roll NO:38
SET-7

 CUSTOMER (cno, cust_name, cust_phone, location,gender)

create table customer (cno number (5) primary key,cust_phone
number(10),location varchar(20),gender varchar(7),cus_name
varchar(20));
 ITEM (itemno, itemname, color, weight, expire_date, price,
shop_name)
create table item(itemno number(5) primary key,itemname
varchar(20),color varchar(7),weight number(10)
check(weight>0),expire_date date,price number(5)
check(price>0) not null,shop_name varchar(20));
 CUST_ITEM (cno, itemno, quantity_purchased,
date_purchase)
create table cust_item (cno number(5),foreign key(cno)
references customer(cno),itemno number(5),foreign
key(itemno) references item(itemno),
quantity_purchases number(5),date_purchases date);

 insert into
customer(cno,cust_name,cust_phone,location,gender) values
 insert into
item(itemno,itemname,color,weight,expire_date,price,shop)
values
 insert into
cust_item(cno,itemno,quantity_purchased,date_purchase)
values
Do as directed:

1) delete the item whose price is more than 50000.

delete from item where price>=50000;
 NAME: Rutvi Shah
Roll NO:38

2) Find the names of the customer who is located in same location as

that of other customer.
 select cust_name from customer where location in('ahd','rjt','surat')

delete item from color where

delete whose item color is black,white,brown
red,blue,green

3)display the name of items which is black ,white,brown.

 select * from item where color in ('black', 'white', 'brown');
ITEMNO ITEMNAME COLOR
---------- -------------------------------------------------- ----------
WEIGHT EXPIRE_DATE PRICE
---------- --------- ----------
SHOP_NAME
--------------------------------------------------
82 Biscuit black
1 30-june-22 50
zudio

20 jacket brown
14 22-oct-22 7000
dmart

ITEMNO ITEMNAME COLOR

---------- -------------------------------------------------- ----------
WEIGHT EXPIRE_DATE PRICE
---------- --------- ----------
SHOP_NAME
------------------------------------------------
4) display the name of the items whose name lies between 'p' and 'e';
 select * from item where itemname between 'p' and 's';
ITEMNO ITEMNAME COLOR
---------- -------------------------------------------------- ----------
WEIGHT EXPIRE_DA PRICE
---------- --------- ----------
 NAME: Rutvi Shah
Roll NO:38
SHOP_NAME
--------------------------------------------------
55 purse pink
4 12-MAR-21 4000
pantaloons

5)find the item less weight?

 select * from item where weight = (select min(weight) from item);
ITEMNO ITEMNAME COLOR
---------- -------------------------------------------------- ----------
WEIGHT EXPIRE_DA PRICE
---------- --------- ----------
SHOP_NAME
--------------------------------------------------
82 Biscuit black
1 30-june-22 50
zudio

20 jacket brown
14 22-oct-22 7000
dmart

ITEMNO ITEMNAME COLOR

---------- -------------------------------------------------- ----------
WEIGHT EXPIRE_DA PRICE
---------- --------- ----------
SHOP_NAME
--------------------------------------------------
55 purse pink
4 12-MAR-21 4000
pantaloons

6)add one month more to those items whose item no=4.

 update item set expire_date = add_months(expire_date, 1) where

itemno=40;
1 rows updated.
 NAME: Rutvi Shah
Roll NO:38

7)Count total number of items which is going to expire in next month.

 select count(itemno) from item where to_char(expire_date, 'MM') =
to_char(sysdate, 'MM');
COUNT(ITEMNO)
-------------
2
8)list customer whose phone no start with '99'.

 select * from customer where cus_phone like '99%';

CNO CUS_NAME CUS_PHONE
---------- ------------------------------ ----------
LOCATION GENDER
-------------------------------------------------- ------
3 ajay 9465799565
gandhinagar male
;

CNO CUST_PHONE LOCATION GENDER CUS_NAME

---------- ---------- -------------------- ------- ----------
47 9988236655 surat female aayushi

9)display total value of (qty*price) for all items.

 select c.cno, c.itemno, (c.quantity_purchased * i.price) as value from

cust_item c, item i where c.itemno = i.itemno;
CNO ITEMNO VALUE
---------- ---------- ----------
1 11 20000
2 82 25000
3 22 70000
4 55 30000
5 44 44000

10)list customer details who has purchased maximum number of

items.
 NAME: Rutvi Shah
Roll NO:38
 color red prize>1000 and price<5000

11)Display total price item wise.

 select itemno, itemname, price from item;
ITEMNO ITEMNAME PRICE
---------- -------------------------------------------------- ----------
11 tea 5000
22 coffee 5000
33 bag 10000
44 jacket 3000
55 purse 4000

12) List name of items, customer details and qty purchased.

 select itemno, itemname, price from item;
select c.*, i.itemname, t.quantity_purchased from customer c, item i,
cust_item t where c.cno = t.cno and i.itemno = t.itemno;
CNO CUS_NAME CUS_PHONE
---------- ------------------------------ ----------
LOCATION GENDER
-------------------------------------------------- ------
ITEMNAME QUANTITY_PURCHASED
-------------------------------------------------- ------------------
1 harshali 9586086757
meghaninagar female
tea 4

2 nisha 9586095100
meghaninagar female
coffee 5

CNO CUS_NAME CUS_PHONE

---------- ------------------------------ ----------
LOCATION GENDER
-------------------------------------------------- ------
ITEMNAME QUANTITY_PURCHASED
 NAME: Rutvi Shah
Roll NO:38
-------------------------------------------------- ------------------

3 akansha 9979779955
gandhinagar female
bag 7

4 nikunj 4567891230
bapunagar male

CNO CUS_NAME CUS_PHONE

---------- ------------------------------ ----------
LOCATION GENDER
-------------------------------------------------- ------
ITEMNAME QUANTITY_PURCHASED
-------------------------------------------------- ------------------
jacket 10

5 janki 1234567890
bapunagar female
purse
 NAME: Rutvi Shah
Roll NO:38
SET-8
 SCREEN (SCREEN_ID, LOCATION, SEATING_CAP)
create table customer(cno number(5) primary
key,cust_phone number(10),location varchar(20),gender
varchar(7),cus_name varchar(20));
 MOVIE (MOVIE_ID, MOVIE_NAME, DATE_OF_RELEASE)
create table item(itemno number(5) primary key,itemname
varchar(20),color varchar(7),weight number(10)
check(weight>0),expire_date date,price number(5)
check(price>0) not null,shop_name varchar(20));
 CURRENT (SCREEN_ID, MOVIE_ID, DATE_OF_ARRIVAL,
DATE_OF_CLOSURE)
create table cust_item(cno number(5),foreign key(cno)
references customer(cno),itemno number(5),foreign
key(itemno) references item(itemno),
quantity_purchases number (5), date_purchases date);

 INSERT INTO scr1 VALUES ('s1', 'sf',122);

 INSERT INTO movies VALUES (1, 'anaconda, 2019-02-20");
 INSERT INTO current1 VALUES ('s1,2,2004-08-25, 2004-09-
10);

Do as directed:

1. Get the name of movie which has run the longest in the
multiplex so far.
 select moviel. movieid, moviel. mane from moviel, current1 where
moviel. movie id-current1.movieid and (DOC-DOA) in (select max (DOC-
DOA) from current1);
movieid mane
-----------------------------
5 idiot
 NAME: Rutvi Shah
Roll NO:38
2. Get the average duration of a movie on screen number
‘S4’.
 select avg (DOC-DOA) as average duration from current where currentl.
scri

average duration
85.0000
3. Get the details of movie that closed on date 24-
november-2004.
 select from moviel, current where current1.movieid-moviel. movieid
and OC-2004-09-10";

movieid mane DOR scrid movieid DOA DOC

-------------------------------------------------------------------------------
2 TPH 2019-04-24 s1 2 2004-08-25 2004-
09-10
4. Movie ‘Star Wars III ‘was released in the 7th week of
2005. Find out the date of its release considering
that a movie releases only on Friday.
 select {fn dayname (DOR)) as 'star war release date from moviel where
mname anaconda";

star war release date

Wednesday
5. Get the full outer join of the relations screen and
current.
select from scri as s,current as c where movieid DOA
s.scrid-c.scrid;
scrid location seating cap scrid movieid DOA DOC
------------------------------------------------------------------------
S1 sf 122 s1 2 2004-08-25
2004-09-10
S2 ff 121 s2 1 2022-09-10
2022-06-25
 NAME: Rutvi Shah
Roll NO:38

S3 tf 120 s3 3 2022-06-25
2022-07-10
S4 ff 200 s4 4 2020-01-25
2020-02-10
S5 sf 234 s5 5 2019-02-07
2019-03-10

6. Write a PL/SQL function which will count total

number of day’s horror movie last longer.
 select count(date_of_arrivel-date_of_release) from current, screen,
movie where mname='agnipath';

COUNT(DATE_OF_ARRIVEL-DATE_OF_RELEASE)
-------------------------------------------------------------------
30
7. Write a PL/SQL procedure that will display movie
which is going to release today.
 select (date_of_closure - date_of_arrival) into day from cur where
movie_id = (select movie_id from movie where movie_name = 'horror');
return day;
end;
/

declare
c number;
begin
c:= da();
dbms_output.put_line('days: ' || c);
end;
/

8. Write a trigger which will not allow to insert/update

in current table if Date_of_arrival is less than
date_of_closure.
create or replace procedure rele
 NAME: Rutvi Shah
Roll NO:38
2 (c in out sys_refcursor)
3 is begin
4 open c for
5 select movie_name from movie where to_char
(date_of_release, 'DD') = to_char (sysdate, 'DD');
6 ends;
7/
 NAME: Rutvi Shah
Roll NO:38
SET-9
APPLICANT (aid, aname, addr, abirth_dt)
 create table applicant (aid varchar2(5) primary key, aname
varchar2(20), addr varchar2(20), abirth_dt date ;

ENTRANCE_TEST (etid, etname, max_score, cut_score)

 create table entrance_test (etid number (10) primary key, etname
varchar2(20), max_score number (10), cut_score number (10));

ETEST_CENTRE (etcid, location, incharge, capacity)

 table etest_centre (etcid number (10) primary key, location
varchar2(15), incharge varchar2(10), capacity number (10));

ETEST_DETAILS (aid, etid, etcid, etest_dt, score)

create table etest_details (aid varchar2(5) references applicant(aid),
etid number (10) references entrance_test(etid), etcid number (10)
references etest_centre(etcid), etest_dt date
check(to_char(etest_dt,’d’) <>1), score number (10), primary key(aid,
etid, etcid));

 DO as directed:

1. Modify the APPLICANT table so that every applicant id has an ‘A’

before its value. E.g. if value is ‘1123’, it should become ‘A1123’.
Display test center details where no tests were conducted. Display
details about applicants who have the same score as that of
Ajaykumar in ‘ORACLE FUNDAMENTALS’.
 select applicant.aid, applicant. aname from applicant,
entrance_test, etest_details where applicant.aid =
etest_details.aid and score = (select score from etest_details
ed, entrance_test et, applicant a where et. etname = 'maths'
and et. etid = ed. etid and a. aname = 'ajaykumar' and a.aid =
ed.aid) and entrance_test. etname = 'ORCL' and entrance_test.
etid = etest_details. Etid;
 NAME: Rutvi Shah
Roll NO:38
AID ANAME
---- --------------------
A1 anjali
2. Display details of applicants who appeared for all tests.
 select aid from etest_details group by aid having count(aid) =
(select count(*) from (select etid from etest_details group by
etid));
AID
---------
A1
3. Display those tests where no applicant has failed.
 select * from entrance_test where etid not in (select ed.etid
from etest_details ed,entrance_test et where ed.etid=et.etid
and score<cut_score);
ETID ETNAME MAX_SCORE CUT_SCORE
---------- -------------------- ---------- ----------------------------
2 java 200 100
4. Display details of entrance test centers which had full attendance
between 1st Oct 15 and 15th Oct 16.
 select etcid from etest_center where (etcid, capacity) in
(select etcid, count (*) from etest_details where etest_dt > '1-
sep-2010' and etest_dt < '30-dec-2010' group by etcid);
no rows selected
5. Display details of the applicants who scored more than the cut
score in the tests they appeared in.
 select ed.aid, cut_score, score from etest_details ed,
entrance_test et, applicant a where ed. etid = et. etid and
a.aid = ed.aid and score > cut_score;
AID CUT_SCORE SCORE
----- ---------- ----------------------
A1 35 90
A1 100 150
6. Display average and maximum score test wise of tests conducted at
Mumbai.
 select max(ed.score) "MAX", avg(ed.score) "AVG" from
etest_details ed, etest_center et where et.location = 'mumbai'
and et.etcid=ed.etcid group by ed.etid;
 NAME: Rutvi Shah
Roll NO:38
MAX AVG
---------- ---------------
90 60
7. Display the number of applicants who have appeared for each test,
test center wise.
 select ed.etid, ed.etcid, count(aid) from etest_details ed group
by ed.etid, ed.etcid order by etid;
ETID ETCID COUNT(AID)
---------- ---------- ----------
1 11 2
2 22 1

8. Display details about test centers where no tests have been

conducted.
 select * from etest_center where etcid not in (select etcid
from etest_details);
no rows selected.
9. For tests, which have been conducted between 2-3-17 and 23-4-17,
show details of the tests as well as the test centre.
 select ed.aid, et. etname, et.max_score, et.cut_score, ec etcid,
ec location, ec incharge, ec capacity from etest_details ed,
entrance_test et, etest_center ec where ed. etest_dt > '1-jan-
20' and ed. etest_dt < '30-dec-2022' and et. etid = ed. etid and
ec etcid = ed. etcid;

AID ETNAME MAX_SCORE CUT_SCORE ETCID LOCATION

----- -------------------- ---------- ---------- ---------- --------------------
INCHARGE CAPACITY
-------------------- ----------
A1 ORCL 100 35 11 chennai
janvi 100

A1 java 200 100 22 bapunagar

riya 200

A2 ORCL 100 35 11 meghaninagar

 NAME: Rutvi Shah
Roll NO:38
janvi 100
10.How many applicants appeared in the ‘ORACLE FUNDAMENTALS’
test at Chennai in the month of February?
 select count (*) "NO" from etest_details ed, etest_center ec,
entrance_test et where to_char (etest_dt, 'mon’) ='dec' and
ec. location = 'chennai' and ec. etcid=ed. etcid and et. etid =
ed.etid and etname = 'ORCL' group by ec.etcid;
NO
----------------
1

11.Display details about applicants who appeared for tests in the same
month as the month in which they were born.
 select aid, aname from applicant where aid in (select a.aid
from applicant a, etest_details ed where to_char (a.abirth_dt,
'mon') = to_char (ed.etest_dt, 'mon'));
AID ANAME
---- --------------------
A1 jayesh
A3 rajvi
12.Display the details about APPLICANTS who have scored the highest
in each test, test centre wise.
 select ed.etid, ed.etcid, max(score) from etest_details ed
group by ed.etid, ed.etcid order by etid;
ETID ETCID MAX(SCORE)
---------- ---------- -----------------------
1 11 90
2 22 150
13.Design a read only view, which has details about applicants and the
tests that he has appeared for.
 create or replace view view1 as select a.aid, aname, et.etid,
et.etname from applicant a, etest_details ed, entrance_test et
where a.aid = ed.aid and et.etid=ed.etid with read only;
View created.

select * from view1;

 NAME: Rutvi Shah
Roll NO:38
AID ANAME ETID ETNAME
---- -------------------- ---------- --------------------
A1 Rutvi 1 ORCL
A1 Rutvi 2 java
A2 kavya 1 ORCL
14.Write a procedure which will print maximum score centre wise.
create or replace procedure et
2 (c in out sys_refcursor)
3 is begin
4 open c for
5 select * from entrance_test;
6 end;
7/

Procedure created.

variable etd refcursor;

execute et(etd);
PL/SQL procedure successfully completed.
print etd;

ETID ETNAME MAX_SCORE CUT_SCORE

---------- -------------------- ---------- ----------
1 ORCL 100 35
2 java 200 100
15. Write a procedure which will print details of entrance test.
Centre name: _____ candidate id: ____ date: ___ score: _____
15. Write a trigger which do not allow insertion / updation /
deletion of Enterance test details on Sunday.
 NAME: Rutvi Shah
Roll NO:38
SET-10
CATEGORY (CAT_CODE, CATDESC)
 (CAT_CODE number (4), CATDESC varchar2(30));
ROUTEMASTER (ROUTENO, ORIGIN, DESTINATION, FARE, DISTANCE,
CAPACITY, DAY, CAT_CODE).
 (ROUTENO number (4), ORIGIN varchar2(20), DESTINATION
varchar2(30), FARE varchar2(30), DISTANCE number (4),
CAPACITY number (4), DAY date, CAT_CODE number (4));
TICKETHEADER (TICKETNO, DATEOFISSUE, DATEOFTRAVEL, BOAR DPLACE,
ROUTENO)
 (TICKETNO number (6), DATEOFISSUE date, DATEOFTRAVEL
date, BOAR varchar2(30), DPLACE varchar2(30), ROUTENO
number (4));
TICKET DETAILS (TICKETNO, NAME, SEX, AGE, FARE)
create table TICKET_DETAILS
 (TICKETNO number (6), NAME varchar2(30), SEX char (2), AGE
number (3), FARE number (3));

ADD THE FOLLOWING CONSTRAINTS:

1. DELUXE, SUPERDELUXE, SUPERFAST AND NORMAL ARE THE
CATDESC.
 alter table category add constraint Cat Desc
check(c_desc="delux" or C_des "superde lux" or
C_desc="superfast" or C_desc="noamal");

2. ORIGIN AND DESTINATION CANNOT BE SAME,

 alter table ROUTEMASTER add constraint
OriginDestCheck CHECK (origin! =destination);
3. CAPACITY SHOULD BE>0 AND <=60.
 alter table ROUTEMASTER add constraint
OriginDestCheck CHECK (origin!=destination);
 NAME: Rutvi Shah
Roll NO:38
Do as directed:

1. Display the total number of people traveled on each ticket

group by ticket no 23.
 select count(name) from ticketdetail where ticketno-23
order by ticketno;
count(name)
2. Give the total collection of fare for each route.
 select routeno, (fare capacity) from routemaster group by
routeno;

routeno (fare capacity)

101 25370
102 1500
103 800
104 42500
3. Give the number of months between issue date and travel
date of each ticket issued.
 select routeno, (fare*capacity) "collection" from routemaster;
ROUTENO collection
---------- ----------------------
11 30000
22 16000
33 1200
44 800.
4. Count number of person boarding from the same place and same
route.
5. select (to_char(dateoftravel, 'MM') - to_char(dateofissue, 'MM'))
"difference" from ticketheader;
difference
----------------
0
1
 NAME: Rutvi Shah
Roll NO:38
0
5
0

5.Display count of person who has traveled in each category.

 select count(c.catdesc) from category c, routemaster r where
c.cat_code=r.cat_code group by c.cat_code;

COUNT(C.CATDESC)
-------------------------------
1
1
1
1
6. Write a trigger which allow to insert or update the bus capacity only
greater than zero and less than 60.
7. Write a Procedure which will print tour details, a driver is going to take it.
(Pass routeno as parameter)