XAMPP PL/SQL INSERT, UPDATE, DELETE & SELECT INTO [ SET 3]

 

In this tutorial, we are going to learn how to use SQL in PL/SQL. SQL is the actual component that takes care of fetching and updating of data in the database whereas PL/SQL is the component that processes these data. Further, in this article, we will also discuss how to combine the SQL within the PL/SQL block.

YOU MIGHT LIKE:

https://www.shoutcoders.com/xamppsqlset2/

https://www.shoutcoders.com/sqlexampleset1/

EXAMPLE 3:

1> Create the following tables:

i ) Hotel (H_no, Name, Address)

create table Hotel (H_no char(4), Name varchar(20), address varchar(20))

OUTPUT:
Table created.

ii) Room (R_no, Rtype, H_no, Price)

create table Room (R_no char(4), Rtype char(6), H_no char(4), Price int(6));

OUTPUT:
Table Created.

iii) Book (H_no, G_no, R_no, Dt_from, Dt_to )

create table Book (H_no char(4), G_no char(4), R_no char(6), dtfrom date, dtto date)

OUTPUT:
Table Created.

iv) Guest (G_no, G_name, G_address)

create table guest (G_no char(4), G_name varchar(20), g_address varchar(20))

OUTPUT:
Table Created.

2> INSERT VALUES IN THE TABLES

i> HOTEL

insert into Hotel values ('h001', 'Taj', 'Mumbai');
insert into Hotel values ('h002', 'Nihauka', 'Kolkata');
insert into Hotel values ('h003', 'Oberoi', 'Delhi');
insert into Hotel values ('h004', 'Nondini', 'Bangaluru');
insert into Hotel values ('h005', 'Kohinoor', 'Mumbai');

OUTPUT:
5 rows inserted.

ii> ROOM

insert into Room values ('r001','AC','h002',1700);
insert into Room values ('r011','AC','h001',2500);
insert into Room values ('r021','AC','h003',1500);
insert into Room values ('r012','AC','h001',2000);
insert into Room values ('r031','NONAC','h005',900);
insert into Room values ('r032','NONAC','h005',850);

OUTPUT
6 row(s) inserted.

iii> BOOK

insert into Book values ('h001', 'g001','r011','2017/02/02', '2017/07/01');
insert into Book values ('h001', 'g003','r012','2017/01/06', '2017/03/02');
insert into Book values ('h002', 'g004','r001','2017/11/11', '2018/01/01');
insert into Book values ('h005', 'g006','r031','2017/03/31', '2017/04/01');
insert into Book values ('h003', 'g007','r021','2017/05/12', '2018/1/02');

OUTPUT
5 row(s) inserted.

iv> GUEST

insert into Guest values ('g001', 'Spandan', 'Burdwan');
insert into Guest values ('g002', 'Saidul', 'Burdwan');
insert into Guest values ('g003', 'Mousumi', 'Durgapur');
insert into Guest values ('g004', 'Tapas', 'Asansol');
insert into Guest values ('g005', 'Deep', 'Kolkata');
insert into Guest values ('g006', 'Souvik', 'Burdwan');
insert into Guest values ('g007', 'Arka', 'Kolkata');

OUTPUT
7 row(s) inserted.

3> i) Display Book Table.

select * from Book;

OUTPUT:
H_no	G_no	R_no	Dtfrom	Dtto 	
h001 	g001 	r011 	2017-02-02 	2017-07-01
h001 	g003 	r012 	2017-01-06 	2017-03-02
h002 	g004 	r001 	2017-11-11 	2018-01-01
h005 	g006 	r031 	2017-03-31 	2017-04-01
h003 	g007 	r021 	2017-05-12 	2018-01-02

ii) Display Guest Table.

select * from Guest;

OUTPUT:
G_no	G_name	G_address 	
g001 	Spandan Burdwan
g002 	Saidul 	Burdwan
g003 	Mousumi Durgapur
g004 	Tapas 	Asansol
g005 	Deep 	Kolkata
g006 	Souvik 	Burdwan
g007 	Arka 	Kolkata

iii) Display Hotel table.

select * from Hotel;

OUTPUT:
H_no	Name	Address 	
h001 	Taj 	 Mumbai
h002 	Nihauka  Kolkata
h003 	Oberoi 	 Delhi
h004 	Nondini  Bangaluru
h005 	Kohinoor Mumbai

iv) Display Room Table.

select * from Room;

OUTPUT:
R_no	Rtype	H_no	Price 	
r001 	AC 	h002 	1700
r011 	AC 	h001 	2500
r021 	AC 	h003 	1500
r012 	AC 	h001 	2000
r031 	NONAC 	h005 	900
r032 	NONAC 	h005 	850

4>
i> Find the name of all guest who are staying in hotel either in kolkata or mumbai.

select G_name from Hotel natural join Guest natural join Book where Address in ('Kolkata', 'Mumbai') 

OUTPUT:
G_name 	
Spandan
Mousumi
Tapas
Souvik

#SAMPLE ANS
select G_no, H_no, G_name from Guest natural join Book natural join Hotel;
OUTPUT:
G_no	H_no	G_name 	
g001 	h001 	Spandan
g003 	h001 	Mousumi
g004 	h002 	Tapas
g006 	h005 	Souvik

# SAMPLE ANS
select G_no, H_no, G_name from Guest natural join Book
OUTPUT:
G_no	H_no	G_name 	
g001 	h001 	Spandan
g003 	h001 	Mousumi
g004 	h002 	Tapas
g006 	h005 	Souvik
g007 	h003 	Arka

FOR NATURAL JOIN: https://www.c-sharpcorner.com/UploadFile/c63ec5/natural-join-in-php3/

ii> Find total number of guest in hotel taj.

select count(G_no) from Book natural join Hotel where Name='Taj';

OUTPUT
count(G_no) 	
2

iii> List the number of Booked rooms in each hotel.