Limitations of this projects + Some example tests

[shubhamp-sf]

If you've just arrived and don't want to spend too much time setting up and testing this project for your specific use case, take a look at the following example cases where it didn't work as expected.

Question	SQL Query
What is the id of the client with the username jdoe?	SELECT client.id FROM client WHERE client.username = 'OOV';
How many clients have made a reservation with a rating of 5?	SELECT COUNT(*) FROM client;
What is the name of the client who made the most reservations?	SELECT client.name FROM client;
How many clients have made a reservation at more than 1 hotel?	SELECT COUNT(*) FROM client;
How many reservations were made by clients whose mobile number contains the digits 555?	SELECT COUNT(*) FROM reservation;
What is the highest-rated hotel in the database?	SELECT * FROM hotel;
What is the lowest-rated hotel in the database?	SELECT * FROM hotel;
How many clients are over the age of 30?	SELECT COUNT(*) FROM client WHERE client.age = 'the';
What is the username of the oldest client in the database?	SELECT client.username FROM client;
What is the username of the youngest client in the database?	SELECT client.username FROM client;
How many reservations were made by clients under the age of 25?	SELECT COUNT(*) FROM reservation INNER JOIN client ON reservation.clientId = client.id WHERE client.age = OOV;

Few more with relations:

Question	SQL Query
What are the name of all the hotel where the client with id is 123 made reservation?	SELECT hotel.name FROM hotel WHERE hotel.id = ‘123’;
How many client have the same name as their username?	SELECT COUNT(*) FROM client WHERE client.name = OOV AND client.username = OOV;
What is the username of the client with the most reservation?	SELECT client.username FROM client;
What is the name of the hotel with the most reservation?	SELECT hotel.name FROM hotel;
How many client have made reservation on the same date as another client?	SELECT COUNT() FROM client; SELECT COUNT() FROM client;
What is the total number of reservation made at hotel with id is 777?	SELECT COUNT(*) FROM reservation WHERE reservation.id = ‘777’;
What are the names of all the hotel where the client with id is 999 made reservation with a rating of 4 or higher?	SELECT * FROM hotel WHERE hotel.id = ‘999’;
What is the age distribution of client who made reservation at hotel with id is 333?	SELECT client.age FROM client WHERE client.id = ‘333’;
What is the name of the client who made the highest-rated reservation?	SELECT client.name FROM client;
How many client have a username that contains the word “hotel”?	SELECT COUNT(*) FROM client WHERE client.username = OOV;
What is the id of the client with the highest-rated reservation at hotel with id is 222?	SELECT client.id FROM client WHERE MAX(client.id) = ‘222’;
How many reservation were made by client whose usernames contain the letter “a”?	SELECT COUNT(*) FROM reservation;
What is the average rating for reservation made by client over the age of 40?	SELECT AVG(reservation.rating) FROM reservation INNER JOIN client ON reservation.clientId = client.id WHERE client.age = ‘the’;

Credits: @harshadk-sourcefuse for testing these out.

[shubhamp-sf]

Following database schema was provided

Table	Column	Type	Equivalences	Primary Key
hotel	id	int		Yes
hotel	name	string		No
reservation	id	int		Yes
reservation	rating	int		No
reservation	clientId	int		No
reservation	hotelId	int		No
reservation	dateA	date	check_in	No
reservation	dateD	date	check_out	No
client	id	int		Yes
client	name	string		No
client	username	string		No
client	address	string		No
client	telephone	string		No
client	age	int		No

Foreign keys:

Table	Column	Foreign Table	Foreign Column
reservation	hotelId	hotel	id
reservation	clientId	client	id