Skip to Main Content
  • Questions
  • PL/SQL Question , how to write a query to accept start and end booking date and display all hotel reservation between that date.

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Hiren.

Asked: November 30, 2022 - 7:09 am UTC

Last updated: December 05, 2022 - 5:39 am UTC

Version: Oracle Live SQL

Viewed 1000+ times

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

Comments

Thank You

Hiren Meghnani, December 01, 2022 - 7:28 am UTC

Thank You - AskTOM team to solve my question. Totally appreciated for your response.
Connor McDonald
December 05, 2022 - 5:39 am UTC

Glad we could help

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library