Unknown Subquery bothering me

ramond

New Member
I have this subquery I needed to solve could never understand it anyone here understand it i like to know what I did wrongImagine that a rich client asks you for the name of the resort that has the most expensive accommodation, along with the description and cost per night of the most expensive accommodation. When we analyze this request, we can see two distinct steps that our query will need to retrieve this information: a. Retrieve the highest cost per night of all accommodations. b. Retrieve the resort and accommodation information that corresponds to that highest cost accommodation. This type of processing naturally fits with a query that contains an uncorrelated subquery. The uncorrelated subquery can retrieve the most expensive cost_per_night of all accommodations, and the outer query can use that value to list the name of the resort that has the most expensive accommodation, along with the requested accommodation information. Write this query, including only the necessary columns in the result set. Note that in this step, the subquery is used to retrieve a single value.\[code\]CREATE TABLE Resort (resort_id DECIMAL(12) PRIMARY KEY,resort_type_id DECIMAL(12) NOT NULL,name VARCHAR(128) NOT NULL);CREATE TABLE Resort_Type (resort_type_id DECIMAL(12) PRIMARY KEY,resort_type VARCHAR(128) NOT NULL);CREATE TABLE Accommodations (accommodations_id DECIMAL(12) PRIMARY KEY,resort_id DECIMAL(12) NOT NULL,description VARCHAR(255) NOT NULL,cost_per_night NUMERIC(7,2));ALTER TABLE ResortADD CONSTRAINT resort_type_fkFOREIGN KEY(resort_type_id)REFERENCES Resort_Type(resort_type_id);ALTER TABLE AccommodationsADD CONSTRAINT resort_id_fkFOREIGN KEY(resort_id)REFERENCES Resort(resort_id);INSERT INTO Resort_Type (resort_type_id, resort_type)VALUES (87, 'Ocean');INSERT INTO Resort_Type (resort_type_id, resort_type)VALUES (88, 'Lakeside');INSERT INTO Resort_Type (resort_type_id, resort_type)VALUES (89, 'Mountaintop');INSERT INTO Resort_Type (resort_type_id, resort_type)VALUES (90, 'Country');INSERT INTO Resort (resort_id, resort_type_id, name)VALUES (50, 87, 'Light of the Ocean')INSERT INTO Resort (resort_id, resort_type_id, name)VALUES (51, 88, 'Breathtaking Bahamas')INSERT INTO Resort (resort_id, resort_type_id, name)VALUES (52, 89, 'Mountainous Mexico')INSERT INTO Resort (resort_id, resort_type_id, name)VALUES (53, 90, 'Greater Lakes');INSERT INTO Accommodations (accommodations_id, resort_id, description, cost_per_night)VALUES (801, 50, 'Bungalow 1', 289.00);INSERT INTO Accommodations (accommodations_id, resort_id, description, cost_per_night)VALUES (802, 50, 'Bungalow 2', 289.00);INSERT INTO Accommodations (accommodations_id, resort_id, description, cost_per_night)VALUES (803, 50, 'Bungalow 3', 325.00);INSERT INTO Accommodations (accommodations_id, resort_id, description, cost_per_night)VALUES (804, 51, 'Suite 101', 199.00);INSERT INTO Accommodations (accommodations_id, resort_id, description, cost_per_night)VALUES (805, 51, 'Suite 102', 199.00);INSERT INTO Accommodations (accommodations_id, resort_id, description, cost_per_night)VALUES (806, 51, 'Suite 201', 250.00);INSERT INTO Accommodations (accommodations_id, resort_id, description, cost_per_night)VALUES (807, 51, 'Suite 202', 250.00);INSERT INTO Accommodations (accommodations_id, resort_id, description, cost_per_night)VALUES (808, 51, 'Room 10', 150.00);INSERT INTO Accommodations (accommodations_id, resort_id, description, cost_per_night)VALUES (809, 52, 'Room 20', NULL);INSERT INTO Accommodations (accommodations_id, resort_id, description, cost_per_night)VALUES (810, 53, 'Cabin A', 300.00);INSERT INTO Accommodations (accommodations_id, resort_id, description, cost_per_night)VALUES (811, 53, 'Cabin B', NULL);INSERT INTO Accommodations (accommodations_id, resort_id, description, cost_per_night)VALUES (812, 53, 'Cabin C', 350.00);\[/code\]
 
Top