Write MySQL queries for the following questions (screenshots show correct result

Question

Write MySQL queries for the following questions (screenshots show correct results): The database is called "Rentals" You can use any name for a table example.

Using a subquery return all reservations that have more than the average number of people

Now add the the firstname and lastname to the previous query

Using a subquery return all guests with reservations

Create stored procedure that returns all personnel

Create view that displays all properties with reservations

Explanation / Answer

Hi,

Since you have not provided the table relationships diagram, I assume that the columns given in all the above given screenshots are all existing in one table.

Below are the queries that are framed with that assumption-

Ans 1 - Select reservationid,people from table1
where people > (select avg(people) from table1);

Ans 2 - Select guestlastname,guestfirstname,reservationid,people from table1
where people > (select avg(people) from table1);

Ans 3 - Select guestlastname,guestfirstname,reservationid,people from table1

Ans 4 - DELIMITER //
CREATE PROCEDURE personnel
(IN con CHAR(20))
BEGIN
SELECT PersonID,FirstName,LastName,JobTitle,Manager FROM table1;
END //
DELIMITER ;

Ans 5 - CREATE VIEW PR AS SELECT propertyname,reservationid,startdate,enddate FROM table1;

Regards,

Vinay Singh

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.