fork download
copy 
  1. -- ============================================================
  2. -- 1. CREATE TABLES
  3. -- ============================================================
  4.  
  5. CREATE TABLE hotel (
  6.     hotel_no INT PRIMARY KEY,
  7.     hotel_name VARCHAR(50),
  8.     city VARCHAR(50)
  9. );
  10.  
  11. CREATE TABLE room (
  12.     room_no INT,
  13.     hotel_no INT,
  14.     type VARCHAR(20),
  15.     price DECIMAL(10,2),
  16.     PRIMARY KEY (room_no, hotel_no),
  17.     FOREIGN KEY (hotel_no) REFERENCES hotel(hotel_no)
  18. );
  19.  
  20. CREATE TABLE guest (
  21.     guest_no INT PRIMARY KEY,
  22.     guest_name VARCHAR(50),
  23.     guest_address VARCHAR(100)
  24. );
  25.  
  26. CREATE TABLE booking (
  27.     hotel_no INT,
  28.     guest_no INT,
  29.     datefrom DATE,
  30.     dateto DATE,
  31.     room_no INT,
  32.     PRIMARY KEY (hotel_no, guest_no, room_no, datefrom),
  33.     FOREIGN KEY (hotel_no) REFERENCES hotel(hotel_no),
  34.     FOREIGN KEY (guest_no) REFERENCES guest(guest_no),
  35.     FOREIGN KEY (room_no, hotel_no) REFERENCES room(room_no, hotel_no)
  36. );
  37.  
  38. -- ============================================================
  39. -- 2. INSERT DUMMY DATA
  40. -- (Bangladeshi style names with some international cities as required)
  41. -- ============================================================
  42.  
  43. -- Hotels
  44. INSERT INTO hotel (hotel_no, hotel_name, city) VALUES
  45. (1, 'ABC Hotel', 'Dhaka'),
  46. (2, 'London Grand', 'London'),
  47. (3, 'CBC Hotel', 'Chittagong');
  48.  
  49. -- Rooms
  50. -- For ABC Hotel (hotel_no = 1)
  51. INSERT INTO room (room_no, hotel_no, type, price) VALUES
  52. (101, 1, 'single', 100.00),
  53. (102, 1, 'double', 200.00),
  54. (103, 1, 'family', 350.00),
  55. (104, 1, 'suite', 500.00);
  56.  
  57. -- For London Grand (hotel_no = 2)
  58. INSERT INTO room (room_no, hotel_no, type, price) VALUES
  59. (201, 2, 'single', 150.00),
  60. (202, 2, 'double', 250.00),
  61. (203, 2, 'family', 300.00),
  62. (204, 2, 'suite', 450.00);
  63.  
  64. -- For CBC Hotel (hotel_no = 3)
  65. INSERT INTO room (room_no, hotel_no, type, price) VALUES
  66. (301, 3, 'double', 350.00),
  67. (302, 3, 'family', 380.00),
  68. (303, 3, 'single', 220.00),
  69. (304, 3, 'double', 390.00);
  70.  
  71. -- Guests
  72. INSERT INTO guest (guest_no, guest_name, guest_address) VALUES
  73. (1, 'Rahim Uddin', 'Dhaka'),
  74. (2, 'Karim Ullah', 'Chittagong'),
  75. (3, 'Salman Khan', 'London'),
  76. (4, 'Mohammad Ali', 'Sylhet'),
  77. (5, 'Jamal Hossain', 'NY'),
  78. (6, 'Faruk Ahmed', 'NY'),
  79. (7, 'Nusrat Jahan', 'NY'),
  80. (8, 'Rafiq Islam', 'Khulna');
  81.  
  82. -- Bookings
  83. -- Bookings for ABC Hotel
  84. INSERT INTO booking (hotel_no, guest_no, datefrom, dateto, room_no) VALUES
  85. (1, 1, '2024-03-20', '2024-03-25', 101);
  86.  
  87. -- Bookings for London Grand
  88. INSERT INTO booking (hotel_no, guest_no, datefrom, dateto, room_no) VALUES
  89. (2, 2, '2024-03-10', '2024-03-15', 201),
  90. (2, 3, '2024-03-23', '2024-03-28', 202);
  91.  
  92. -- Bookings for CBC Hotel
  93. INSERT INTO booking (hotel_no, guest_no, datefrom, dateto, room_no) VALUES
  94. (3, 4, '2024-03-22', '2024-03-27', 301),
  95. (3, 6, '2024-03-23', '2024-03-28', 302),
  96. (3, 7, '2024-03-22', '2024-03-27', 304);
  97.  
  98. -- ============================================================
  99. -- 3. SQL QUERIES
  100. -- ============================================================
  101.  
  102. -- 1. List full details of all hotels in London.
  103. SELECT *
  104. FROM hotel
  105. WHERE city = 'London';
  106.  
  107. -- 2. List all double or family rooms with a price below $400 per night, in ascending order of price.
  108. SELECT *
  109. FROM room
  110. WHERE type IN ('double', 'family') 
  111.   AND price < 400
  112. ORDER BY price ASC;
  113.  
  114. -- 3. What are the min, max and avg price of the rooms.
  115. SELECT MIN(price) AS min_price,
  116.        MAX(price) AS max_price,
  117.        AVG(price) AS avg_price
  118. FROM room;
  119.  
  120. -- 4. List the rooms that are currently unoccupied by “ABC” hotel.
  121. -- (Assuming the current date is '2024-03-23')
  122. SELECT r.*
  123. FROM room r
  124. JOIN hotel h ON r.hotel_no = h.hotel_no
  125. LEFT JOIN booking b ON r.room_no = b.room_no
  126.     AND r.hotel_no = b.hotel_no
  127.     AND '2024-03-23' BETWEEN b.datefrom AND b.dateto
  128. WHERE h.hotel_name = 'ABC Hotel'
  129.   AND b.room_no IS NULL;
  130.  
  131. -- 5. List the guest names who lived in “NY” and books room in “CBC hotel” after 20/3/24.
  132. SELECT DISTINCT g.guest_name
  133. FROM guest g
  134. JOIN booking b ON g.guest_no = b.guest_no
  135. JOIN hotel h ON b.hotel_no = h.hotel_no
  136. WHERE g.guest_address = 'NY'
  137.   AND h.hotel_name = 'CBC Hotel'
  138.   AND b.datefrom > '2024-03-20';
  139.  
Success #stdin #stdout 0.01s 5280KB
comments (?)
stdin
copy 
Standard input is empty
stdout
copy 
2|London Grand|London
102|1|double|200
202|2|double|250
203|2|family|300
103|1|family|350
301|3|double|350
302|3|family|380
304|3|double|390
100|500|303.333333333333
102|1|double|200
103|1|family|350
104|1|suite|500
Faruk Ahmed
Nusrat Jahan
https://ideone.com/dZIZVs
language:
SQLite (sqlite 3.27.2)
created:
8 days ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!