# B0124 - Tutorial Chapter 7

• Script Name B0124 - Tutorial Chapter 7
• Description Tutorial Chapter 7 - Seminar 2 - Relational Algebra
• Category Oracle 12c
• Contributor Soon Jing
• Created Saturday July 08, 2017
• Statement 1
``create table Lorry_Age (Lorry_No varchar(5),Age int, primary key(Lorry_No))``
Table created.
• Statement 2
``create table Drive (Driver_No varchar(5),Driver_Name varchar(50), Lorry_No varchar(5),foreign key(Lorry_No) references Lorry_Age(Lorry_No))``
Table created.
• Statement 3
``create table Recent_Route (Lorry_No varchar(5), Route_No varchar(5), foreign key(Lorry_No) references Lorry_Age (Lorry_No))``
Table created.
• Statement 4
``````insert all
into Lorry_Age values('L1',5)
into Lorry_Age values('L2',4)
into Lorry_Age values('L3',1)
into Lorry_Age values('L4',3)
into Lorry_Age values('L5',4)
into Lorry_Age values('L6',1)
into Lorry_Age values('L7',2)
select * from dual``````
7 row(s) inserted.
• Statement 5
``````insert all
into Drive values('D1','Allen','L2')
into Drive values('D4','Davis','L4')
into Drive values('D2','Brown','L2')
into Drive values('D5','Davis','L3')
into Drive values('D8','Davis','L4')
into Drive values('D9','Philips','L4')
into Drive values('D7','Davis','L6')
select * from dual``````
7 row(s) inserted.
• Statement 6
``````insert all
into Recent_Route values('L2','R1')
into Recent_Route values('L2','R2')
into Recent_Route values('L2','R3')
into Recent_Route values('L3','R4')
into Recent_Route values('L4','R5')
into Recent_Route values('L4','R6')
into Recent_Route values('L5','R7')
select * from dual``````
7 row(s) inserted.
• Statement 7
``select * from Drive where Driver_Name = 'Davis'``
DRIVER_NODRIVER_NAMELORRY_NO
D4DavisL4
D5DavisL3
D8DavisL4
D7DavisL6

4 rows selected.
• Statement 8
``select * from Drive where Lorry_No = 'L4' and Driver_Name = 'Davis'``
DRIVER_NODRIVER_NAMELORRY_NO
D4DavisL4
D8DavisL4

2 rows selected.
• Statement 9
``select * from Lorry_Age where Age < 4 and Lorry_No <> 'L7'``
LORRY_NOAGE
L31
L43
L61

3 rows selected.
• Statement 10
``select Lorry_No from Drive``
LORRY_NO
L2
L4
L2
L3
L4
L4
L6

7 rows selected.
• Statement 11
``select Lorry_No, Driver_Name from Drive``
LORRY_NODRIVER_NAME
L2Allen
L4Davis
L2Brown
L3Davis
L4Davis
L4Philips
L6Davis

7 rows selected.
• Statement 12
``select Drive.Lorry_No,Driver_No,Driver_Name,Age from Drive inner join Lorry_Age on Drive.Lorry_No=Lorry_Age.Lorry_No``
LORRY_NODRIVER_NODRIVER_NAMEAGE
L2D1Allen4
L4D4Davis3
L2D2Brown4
L3D5Davis1
L4D8Davis3
L4D9Philips3
L6D7Davis1

7 rows selected.
• Statement 13
``select Drive.Lorry_No,Driver_No,Driver_Name,Route_No from Drive inner join Recent_Route on Drive.Lorry_No=Recent_Route.Lorry_No``
LORRY_NODRIVER_NODRIVER_NAMEROUTE_NO
L2D1AllenR1
L2D2BrownR1
L2D1AllenR2
L2D2BrownR2
L2D1AllenR3
L2D2BrownR3
L3D5DavisR4
L4D4DavisR5
L4D8DavisR5
L4D9PhilipsR5
L4D4DavisR6
L4D8DavisR6
L4D9PhilipsR6

13 rows selected.
• Statement 14
``select distinct l.Lorry_No from Lorry_Age l,Drive d where Driver_Name ='Davis' and l.Lorry_No=d.Lorry_No``
LORRY_NO
L6
L4
L3

3 rows selected.
• Statement 15
``select d.Lorry_No as "Lorry_No Driven by D9",Age from Drive d,Lorry_Age l where l.Lorry_No=d.Lorry_No and Driver_No ='D9'``
Lorry_No Driven by D9AGE
L43
• Statement 16
``select d.Driver_Name,age,l.Lorry_No,r.Route_No from Drive d,Lorry_Age l,Recent_Route r where d.Lorry_No=l.Lorry_No and l.Lorry_No=r.Lorry_No and l.Age >1 and l.Age <5 ``
DRIVER_NAMEAGELORRY_NOROUTE_NO
Allen4L2R1
Brown4L2R1
Allen4L2R2
Brown4L2R2
Allen4L2R3
Brown4L2R3
Davis3L4R5
Davis3L4R5
Philips3L4R5
Davis3L4R6
Davis3L4R6
Philips3L4R6

12 rows selected.