USA: +1 725-224-8795 
Sign in
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.
Returns all guests with reservations
Returns all distinct guests with reservations
Returns guesid, FirstName, Lastname, Reservationid, propertyname
Returns all guests without reservations
Properties without reservations
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 proc that returns all personnel and then execute it
Create view that displays all properties with reservations
Expert Answer
Assuming Below tables:
1. guests Table:
guestId, guestFirstName, guestLastName, city
2. reservations Table:
reservationId, startDate, endDate, propertyId
3. property table
propertyId, propertyName
Returns all guests with reservations:
select g.guestId, g.guestFirstName, g.guestLastName, g.city, r.reservationId, r.startDate, r.endDate
from Rentals.guests g, Rentals.reservations r
where
g.guestId = r.guestId
Returns all distinct guests with reservations:
select .guestId, g.guestFirstName, g.guestLastName, g.city
from Rentals.guests g
where g.guestId in (select distinct r.guestId from Rentals.reservations r)
Returns guesid, FirstName, Lastname, Reservationid, propertyname
select g.guestId, g.guestFirstName, g.guestLastName, g.city, r.reservationId, p.propertyName
from Rentals.guests g, Rentals.reservations r, Rentals.property p
where
g.guestId = r.guestId and
r.propertyId = p.propertyId
Returns all guests without reservations
select .guestId, g.guestFirstName, g.guestLastName
from Rentals.guests g
where g.guestId not in (select distinct r.guestId from Rentals.reservations r)
Properties without reservations
select p.propertyName
from Rentals.property p
where
p.propertyId not in (select distinct r.propertyId from Rentals.reservations r)
Hey, I am answering to your first 5 queries. I request you to please post other remaining sub parts in a seperate question, as due to time constraints, it is not possible to answer all of your subparts.
