You Asked
create table hotel_reservation with following fields.
Booking id,
booking start date
booking end date
room type,
room rent
write PL/SQL block to accept start and end booking date and display all hotel reservation between that date.
*** In this question i am stuck in : ***
create table hotel_reservation(booking_id number(20),booking_start_date date,booking_end_date date,room_type varchar2(50),room_rent number(20));
insert into hotel_reservation values(1, TO_DATE('10-05-2022', 'dd-mm-yyyy'), TO_DATE('12-05-2022', 'dd-mm-yyyy'), 'Double', 12000);
insert into hotel_reservation values(2, TO_DATE('21-07-2022', 'dd-mm-yyyy'), TO_DATE('25-07-2022', 'dd-mm-yyyy'), 'Single', 5000);
insert into hotel_reservation values(3, TO_DATE('01-02-2022', 'dd-mm-yyyy'), TO_DATE('02-02-2022', 'dd-mm-yyyy'), 'Luxury', 30000);
insert into hotel_reservation values(4, TO_DATE('30-06-2022', 'dd-mm-yyyy'), TO_DATE('01-07-2022', 'dd-mm-yyyy'), 'Double', 10000);
insert into hotel_reservation values(5, TO_DATE('15-10-2022', 'dd-mm-yyyy'), TO_DATE('15-10-2022', 'dd-mm-yyyy'), 'Quad', 11000);
select * from hotel_reservation;
DECLARE
book_id hotel_reservation.booking_id%type;
book_sdate hotel_reservation.booking_start_date%type := TO_DATE('10-05-2022', 'dd-mm-yyyy');
book_edate hotel_reservation.booking_end_date%type := TO_DATE('15-10-2022', 'dd-mm-yyyy');
r_type hotel_reservation.room_type%type;
r_rent hotel_reservation.room_rent%type;
BEGIN
Select booking_id,booking_start_date,booking_end_date,room_type,room_rent INTO book_id, book_sdate, book_edate,r_type,r_rent FROM hotel_reservation WHERE booking_start_date = book_sdate and booking_end_date = book_edate;
dbms_output.put_line('hotel_reservation ' || book_id || ' ' || book_sdate || ' ' || book_edate || ' ' || r_type || ' ' || r_rent);
END;
*** in the begin block of pl/sql ***
with LiveSQL Test Case:
and Connor said...
Well done on what you've done so far. Just needs a few changes and you're good to go
SQL> create table hotel_reservation(booking_id number(20),booking_start_date date,booking_end_date date,room_type varchar2(50),room_rent number(20));
Table created.
SQL>
SQL> insert into hotel_reservation values(1, TO_DATE('10-05-2022', 'dd-mm-yyyy'), TO_DATE('12-05-2022', 'dd-mm-yyyy'), 'Double', 12000);
1 row created.
SQL> insert into hotel_reservation values(2, TO_DATE('21-07-2022', 'dd-mm-yyyy'), TO_DATE('25-07-2022', 'dd-mm-yyyy'), 'Single', 5000);
1 row created.
SQL> insert into hotel_reservation values(3, TO_DATE('01-02-2022', 'dd-mm-yyyy'), TO_DATE('02-02-2022', 'dd-mm-yyyy'), 'Luxury', 30000);
1 row created.
SQL> insert into hotel_reservation values(4, TO_DATE('30-06-2022', 'dd-mm-yyyy'), TO_DATE('01-07-2022', 'dd-mm-yyyy'), 'Double', 10000);
1 row created.
SQL> insert into hotel_reservation values(5, TO_DATE('15-10-2022', 'dd-mm-yyyy'), TO_DATE('15-10-2022', 'dd-mm-yyyy'), 'Quad', 11000);
1 row created.
SQL>
SQL>
SQL> set serverout on
SQL> DECLARE
2 l_book_sdate hotel_reservation.booking_start_date%type := TO_DATE('10-05-2022', 'dd-mm-yyyy');
3 l_book_edate hotel_reservation.booking_end_date%type := TO_DATE('15-10-2022', 'dd-mm-yyyy');
4
5 BEGIN
6 for i in (
7 select booking_id,booking_start_date,booking_end_date,room_type,room_rent
8 from hotel_reservation
9 where booking_start_date >= l_book_sdate
10 and booking_end_date <= l_book_edate )
11 loop
12 dbms_output.put_line(
13 'hotel_reservation ' ||
14 i.booking_id || ' ' ||
15 i.booking_start_date || ' ' ||
16 i.booking_end_date || ' ' ||
17 i.room_type || ' ' ||
18 i.room_rent);
19 end loop;
20 END;
21 /
hotel_reservation 1 10-MAY-22 12-MAY-22 Double 12000
hotel_reservation 2 21-JUL-22 25-JUL-22 Single 5000
hotel_reservation 4 30-JUN-22 01-JUL-22 Double 10000
hotel_reservation 5 15-OCT-22 15-OCT-22 Quad 11000
PL/SQL procedure successfully completed.
SQL>
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment