DTA (M) Database Theory & Applications
Lab 7 Tasks
Task 1: Advanced SQL SELECT Statements ‘Dog’ Database
Nested SQL SELECT operators:
Write and execute the following SQL Select statements (using the Query Tool in pgAdmin4):
SQL 1: Owners would like to contact the kennels of their dogs. For each owner, show the number of
kennels they need to contact, ordered by owner name.
SQL 2: Show all dog’s information (SELECT *) whose kennel is either ‘Novar kennel club’ or ‘Canine
kennel club’ or ’Island kennel club’ using the IN operator. Strings/characters in SQL are case-sensitive.
SQL 3: Show the dogs’ names (and their owners’ names) that have not yet participated in a show using
the NOT IN operator ordered by owner’s name.
SQL 4: Show the dogs’ names (and their owners’ names) that have participated in a show at least once
and ranked first (i.e., having the smallest ranking/place value = 1). You could use the EXISTS operator.
DTA (M) Database Theory & Applications
SQL 5: Find the DogIDs and dog names of the dogs which have been ranked less than someone else.
Experiment with the ANY operator, where ANY operator evaluates to true if any (at least one) of the
subquery values meet the condition in the WHERE clause. Visit the link:
https://www.w3schools.com/sql/sql_any_all.asp
SQL 6: Find the DogIDs and dog names of those dogs who have been winners (relative ranking) in at
least one show. Experiment with the ALL operator, where ALL operator evaluates to true if all of the
subquery values meet the condition in the WHERE clause. Visit the link:
https://www.w3schools.com/sql/sql_any_all.asp
SQL 7: For each dog, which has attended to a show and got a ranking (place) 1, 2, or 3, give a reward in
£ calculated as £100*(4-place). E.g., if the dog has ranked in place 2, then the reward is £100*(4-2) =
£200, if dog is ranked in place 1, the reward is £100*(4-1) = £300, and if a dog is ranked in place 3, the
reward is £100*(4-3) = £100.
SQL 8: How many different dogs have participated in shows? Experiment with the COUNT and
DISTINCT operators.
SQL 9: Which is the minimum place value of the dogs that have participated in shows and their dog
names contain the character ‘e’? Experiment with the aggregation function MIN and the LIKE
operator for comparing characters. Specifically, the LIKE operator is used in a WHERE clause to
search for a specified pattern in an attribute. There are two wildcards often used with the LIKE
operator:
% : The percent sign represents zero, one, or multiple characters.
_ : The underscore represents a single character.
Visit the link: https://www.w3schools.com/sql/sql_like.asp
SQL 10: Display how many dogs have participated in each show, order by the show name. Experiment
with the GROUP BY operator.
SQL 11: Display how many dogs are owned by each owner, order by the owner name. Experiment with
the GROUP BY operator.
SQL 12: Display the kennel (kennel name), which has the most dogs. Here, there are two cases.
Case 1: We assume that there exists only one kennel that has the most dogs. This is the easiest
case, since we can use the LIMIT clause in PostgreSQL to control the number of tuples retrieved.
That is, LIMIT 1 means that no more than 1 row is to be fetched (note: LIMIT ALL is the
https://www.w3schools.com/sql/sql_any_all.asp
https://www.w3schools.com/sql/sql_any_all.asp
https://www.w3schools.com/sql/sql_like.asp
DTA (M) Database Theory & Applications
same as omitting the LIMIT. Experiment with the LIMIT operator to provide a solution for Case
1; visit: http://www.postgresql.org/docs/8.2/static/queries-limit.html
Case 2: There is no assumption here, that is, it might be the case that more than one kennel has
the most dogs. Experiment now with nested aggregation queries 😊
http://www.postgresql.org/docs/8.2/static/queries-limit.html