Diego is working as an intern at a vacation rental company. His task is to manage and analyze rental and booking data. The database admin has already created two tables, Rentals and Bookings. Diego needs to perform several operations on these tables based on specific conditions.Table Name: RentalsTable Name: BookingsSample Input Records:Tasks for DiegoInsert Records: Insert at least 5 records into the Rentals and Bookings tables with the values mentioned.Update Prices: Update the PricePerNight of rentals where PricePerNight is greater than $100Delete Bookings: Remove bookings from the Bookings table where the BookingDate is before '2024-01-01' and the RentalID is not equal to 101, 102, 103.Select Rentals: Retrieve all rentals located at 'Beach' where the PricePerNight is less than $150.
Question
Diego is working as an intern at a vacation rental company. His task is to manage and analyze rental and booking data. The database admin has already created two tables, Rentals and Bookings. Diego needs to perform several operations on these tables based on specific conditions.Table Name: RentalsTable Name: BookingsSample Input Records:Tasks for DiegoInsert Records: Insert at least 5 records into the Rentals and Bookings tables with the values mentioned.Update Prices: Update the PricePerNight of rentals where PricePerNight is greater than 150.
Solution
Sure, here are the SQL commands for each of the tasks:
- Insert Records:
INSERT INTO Rentals VALUES (1, 'Beach', 120, '2022-01-01', '2022-01-07');
INSERT INTO Rentals VALUES (2, 'Mountain', 200, '2022-02-01', '2022-02-07');
INSERT INTO Rentals VALUES (3, 'City', 80, '2022-03-01', '2022-03-07');
INSERT INTO Rentals VALUES (4, 'Country', 150, '2022-04-01', '2022-04-07');
INSERT INTO Rentals VALUES (5, 'Island', 220, '2022-05-01', '2022-05-07');
INSERT INTO Bookings VALUES (1, 1, '2022-01-01', '2022-01-07');
INSERT INTO Bookings VALUES (2, 2, '2022-02-01', '2022-02-07');
INSERT INTO Bookings VALUES (3, 3, '2022-03-01', '2022-03-07');
INSERT INTO Bookings VALUES (4, 4, '2022-04-01', '2022-04-07');
INSERT INTO Bookings VALUES (5, 5, '2022-05-01', '2022-05-07');
- Update Prices:
UPDATE Rentals
SET PricePerNight = NEW_VALUE
WHERE PricePerNight > 100;
Replace NEW_VALUE with the new price you want to set.
- Delete Bookings:
DELETE FROM Bookings
WHERE BookingDate < '2024-01-01' AND RentalID NOT IN (101, 102, 103);
- Select Rentals:
SELECT * FROM Rentals
WHERE Location = 'Beach' AND PricePerNight < 150;
Please replace the sample values with the actual data you have.
Similar Questions
Charles is developing a travel booking system to manage customers, bookings, and payments. The system needs to handle three main types of data: CUSTOMERS, BOOKINGS, and PAYMENTS. To perform this the user needs to create the following tables with the mentioned constraints:
Update Prices: Update the PricePerNight of rentals where PricePerNight is greater than $100Delete Bookings: Remove bookings from the Bookings table where the BookingDate is before '2024-01-01' and the RentalID is not equal to 101, 102, 103.Select Rentals: Retrieve all rentals located at 'Beach' where the PricePerNight is less than $150.
The following tables form part of a database held in a relational DBMS:Hotel (hotelNo, hotelName, city)Room (roomNo, hotelNo, type, price)Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)Guest (guestNo, guestName, guestAddress)where Hotel contains hotel details and hotelNo is the primary key;Room contains room details for each hotel and (roomNo, hoteINo) forms the primary key;Booking contains details of bookings and (hoteINo, guestNo, dateFrom) forms the primary key;Guest contains guest details and guestNo is the primary key.
Table 1.0: List Price, Sale Price and Days to Sell ApartmentsBlue Apartments Red ApartmentsList Price Sale Price Days to Sell List Price Sale Price Days to Sell1 495.0 475.0 130 217.0 217.0 1822 379.0 350.0 71 148.0 135.5 3383 529.0 519.0 85 186.5 179.0 1224 552.5 534.5 95 239.0 230.0 1505 334.9 334.9 119 279.0 267.5 1696 550.0 505.0 92 215.0 214.0 587 169.9 165.0 197 279.0 259.0 1108 210.0 210.0 56 179.9 176.5 1309 975.0 945.0 73 149.9 144.9 14910 314.0 314.0 126 235.0 230.0 11411 315.0 305.0 88 199.8 179.0 12012 885.0 800.0 282 210.0 192.0 6113 975.0 975.0 100 226.0 195.0 14614 329.0 445.0 56 149.9 212.0 13715 365.0 305.0 49 160.0 146.5 28116 332.0 330.0 48 322.0 160.0 6317 520.0 312.0 88 187.5 292.5 4818 425.0 495.0 161 247.0 179.0 5219 675.0 405.0 14920 409.0 669.0 142
i. Create a database called orderproc_db for an Order processing company usingMySQL.ii. Replace the data types in the tables given below with MySQL Compatible format.iii. Using the orderproc_db database, create the following tables.CUSTOMER (custno: INT, cname: STRING, city: STRING)ORDE (orderno: INT, odate: DATE, custno: INT, ord_amt: INT)ITEM (itemno: INT, unit_price: INT)ORDE_ITEM (orderno: INT, itemno: INT, qty: INT)WAREHOUSE (warehouseno: INT, city: STRING)SHIPMENT (orderno: INT, warehouseno: INT, ship_date: DATE)iv. Identify the primary key and define primary key constrains for the table.v. Identify the foreign keys and enforce referential integrity for the tables.vi. Describe the structures of the tables you have created above.vii. Insert the records to the tables. Records are available as text files with the respectivetable names in a folder called “Tables” on your desktop. You need to properly formatthese records before inserting into the tables.viii. List all customers from Batticaloa.ix. Propose an alternative method to execute the above question (viii).x. Select all customers who are from Batticaloa or Jaffna.xi. How many customers are there in Colombo?xii. List the customer’s name ending with the letters “na”.xiii. List all the order amount above 50000.xiv. List all customers name who made order above 50000.xv. List all customers name who have at least one warehouse in their city.xvi. How many different cities are there in the customer table.xvii. How many warehouses are there in Colombo?.xviii. How do you check all order dates are less than or equal to shipping dates?xix. List all the order number, order date, customer number and order amount where all theshipment made from Colombo warehouses.
Upgrade your grade with Knowee
Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.