DTA (M) Database Theory & Applications
Lab 6 Tasks
Task 1: SQL SELECT Statements ‘Dog’ Database
Format of SQL SELECT:
Task 1.1: Write the SQL Select statements (using the Query Tool in pgAgmin4)
SQL 1: Show the owners whose phone number starts with ‘141’ or without phone.
Experiment with 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 2: Show the breed of each dog sorted by the breed name in descending order. Use the
ORDER BY clause to sort the results set in ascending (ASC) or descending (DESC) order.
Visit the link:
https://www.w3schools.com/sql/sql_orderby.asp
SQL 3: Show the kennel name of a dog whose dog’s name is the same either to its mother or
its father, or the dog’s mother name is unspecified. An unspecified value for an attribute is
representing with NULL. Recall: we check if an attributed has a NULL value or not using:
IS NULL or IS NOT NULL, respectively.
Visit the follow link to experiment more with the NULL operator:
https://www.w3schools.com/sql/sql_null_values.asp
https://www.w3schools.com/sql/sql_like.asp
https://www.w3schools.com/sql/sql_orderby.asp
https://www.w3schools.com/sql/sql_null_values.asp
DTA (M) Database Theory & Applications
SQL SELECT over multiple tables:
Task 1.2: Write the SQL Select statements involving more than one table.
SQL 4: Show the breed of each dog sorted by the dog name in ascending order.
Note: You can join two tables via an equi-join operator over their FK and PK. Always use the
FK and the PK to associate tables together, depending on the context. For a join operators
reference, you can visit the link:
https://www.w3schools.com/sql/sql_join.asp
However, if you can find the information you are searching for in one relation, then, you do
not need to associate two relations The result will be the same!
SQL 5: Display the dogs (names), their owners (names) and the shows (showname) that the
dogs have participated.
SQL 6: Get the address of the kennels and the phone numbers of the owners who have
attended a show (with their dogs) and their dogs ranked first (i.e., place = 1).
Aliases in SQL SELECT, i.e., selection statements using the AS variable definition operator.
https://www.w3schools.com/sql/sql_join.asp
DTA (M) Database Theory & Applications
Task 1.3: Write the SQL Select statements:
SQL 7: Get the owner (name) whose dogs’ kennels have unspecified addresses (addresses
with NULL values).
SQL 8: Show the names of all dogs which have the same owner with the owner of the dog
with name ‘Laser’; do not include ‘Laser’ in the result list…
SQL 9: Show all pairs of dogs (names) with the same owner in lexicographical order, e.g., ‘A’
> ‘B’, ‘B’ > ‘C’. Do not display identical pairs.