Consider
the following entities and their relationship.
Bus(bus_no,
capacity, source, destination)
Driver(driver_no,
driver_name, license_no, addr, age, salary)
Relationship
between Bus and Driver is many-to-many with descriptive attribute
date_of_duty_allotted and shift.
Constraints:
primary key, foreign key,primary key for third table (bus_no,
driver_no,date_of_duty_allotted),
driver_name
should not be null,shift can be morning or evening.
Create
a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write
a function which will return name of driver having maximum salary.
SQL>
Set serveroutput on
SQL>
Create or replace function fun(dn in varchar) return varchar as dnm varchar(10);
2
Begin
3
Select (dname) into dnm from
driver where salary=(select max(salary) from driver);
4 If
sql %found then
5
Return(dnm);
6 Else
7
Return null;
8 End
if;
9 End;
10 /
Function
created.
SQL>begin
2dbms_output.put_line('name-'||fun('dname'));
3end;
4/
name-omkar
PL/SQL
procedure successfully completed.
2)Write
a cursor which will display date wise bus and their driver details.
SQL>
Set serveroutput on
SQL>
Declare
2
Cursor c1 is select
capacity,source,destination,dname,licenceno,age,salary from bus, driver,
busdriver where bus.busno=busdriver.busno and driver.dno=busdriver.dno;
3 C
c1%rowtype;
4
Begin
5 Open
c1;
6 Loop
7
Fetch c1 into c;
8 Exit
when c1%notfound;
9
Dbms_output.put_line(c.capacity||' '||c.source||' '||c.destination||'
'||c.dname||' '||c.licenceno||' '||
c.age||' '||c.salary);
10 End
loop;
11
Close c1;
12 End;
13 /
14
delhi daund ram 6464 25 4969
27
daund delhi sam 5454 30 5535
20
daund pune shasi 4452 21 5545
15
nasik mumbai omkar 5554 35 6000
30
hadapsar katraj shubham 5448 26 4444
PL/SQL
procedure successfully completed.
