CODEWITHSUNDEEP
mysqlsql
Jonathan Bishop
Jonathan Bishop

Reputation: 167

SQL query to find averages

I have created three tables such that

CREATE TABLE guest(

name varchar(100),
ranking int,
PRIMARY KEY (name)
);

CREATE TABLE room(

 roomname varchar(100),
 wallcolor varchar(100),
 rating int,
 PRIMARY KEY(roomnane)
 );

 CREATE TABLE reservation(

 name varchar(100),
 roomname varchar(100),
 day varchar(100),
 moveinday int,
 moveoutday int,
 PRIMARY KEY(roomname, day, start, finish),
  FOREIGN KEY(roomname) REFERENCES room(roomname),
  FOREIGN KEY(name) REFERENCES guest(name)
 );

I am trying to create two queries

  1. For each reserved room, list the average ranking of guests having reserved that room. I am having trouble listing the average for each room. Here's what I have tried

    select reservation.roomname, AVG(guest.ranking)
from reservation, room, guest
 where reservation.roomname == room.roomname AND reservation.name = guest.name

This does not provide the desired result. How could I fix my query?

  1. Query to verify that there are no conflicting reservations for rooms. (same room can't be reserved for multiple people over moveinday and moveoutday. I am not sure how to start for this

Upvotes: 1

Views: 59

Answers (3)

Ben
Ben

Reputation: 423

As for your second question, the basic should be such a join:

select room.roomname 
from room
join reservation r1 on room.roomname = r1.roomname 
join reservation r2 on room.roomname = r2.roomname

and then the where clause is the complicated part:

where (r1.moveinday < r2.moveinday and r1.moveoutday > r2.moveoutday)
or (r1.moveinday < r2.moveinday and r1.moveoutday > r2.moveinday)
or (r1.moveinday < r2.moveoutday and r1.moveoutday > r2.moveoutday)

The 1st case: r1 totally contains r2.

The 2nd case: r1 overlaps the move in day of r2

The 3rd case: r1 overlaps the move out day of r2

you might want to use <=, >=, depends how you consider this case. If you like to use <=, >= (instead of <. >), I recommend you add a unique ID to each reservation, as you will have to add to the where clause something such as r1.ID <> r2.ID

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270993

You should learn to use proper join syntax and table aliases.

select r.roomname, AVG(g.ranking)
from room r join
     guest g
     on r.name = g.name
group by r.roomname;

In addition, the room table is not actually needed.

Upvotes: 1

Nick
Nick

Reputation: 10163

Dont forget about GROUP BY:

SELECT reservation.roomname, AVG(guest.ranking)
FROM reservation, room, guest
WHERE reservation.roomname = room.roomname 
AND reservation.name = guest.name
GROUP BY reservation.roomname

Upvotes: 2

Related Questions