gistfile1.sql

/* A1 1. */
SELECT HotelNo, COUNT(*) AS SingleRoomsCount
FROM room
WHERE Type = "single"
GROUP BY HotelNo
;

/* A1 2. With combined double and family rooms > 3 */
SELECT HotelNo, COUNT(*) AS FamilyAndDoubleRoomsCount
FROM room
WHERE Type = "family" OR Type = "double"
GROUP BY HotelNo
HAVING FamilyAndDoubleRoomsCount > 3
ORDER BY FamilyAndDoubleRoomsCount ASC
;

/* A1 2. With family rooms > 3 and/or double rooms > 3 */
SELECT HotelNo, SUM(RoomsCount) AS TotalFamilyOrDoubleRooms
FROM (
	SELECT HotelNo, Type, COUNT(*) AS RoomsCount
	FROM room
	GROUP BY HotelNo, Type
	HAVING (Type = "family" AND RoomsCount > 3) OR (Type = "double" AND RoomsCount > 3)
) AS FamilyOrDoubleRoomCount
GROUP BY HotelNo
ORDER BY TotalFamilyOrDoubleRooms ASC
;

/* A1 3. */
SELECT customer.Name, customer.Surname, customer.HouseNumber, customer.StreetName, customer.City, customer.Postcode, customer.Country, hotel.Name, hotel.Location
FROM booking, customer, hotel
WHERE booking.CustNo = customer.CustNo AND booking.HotelNo = hotel.HotelNo
;

/* A1 4. */
SELECT customer.Name, customer.Surname, customer.HouseNumber, customer.StreetName, customer.City, customer.Postcode, customer.Country, booking.Nights * room.Rate AS TotalCost
FROM booking, customer, room
WHERE booking.CustNo = 3 AND booking.CustNo = customer.CustNo AND booking.HotelNo = room.HotelNo AND booking.RoomNo = room.RoomNo
;