DTA (M) Database Theory & Applications
Lab 7 Tasks [ADVANCED]
Task 1: Advanced SQL SELECT Statements
Write the SQL Select statements involving the GROUP BY and HAVING operators.
SQL 1: Display the owners sorted by name who own more than eight dogs.
SQL 2: Display the kennels which breed more than three dogs order by their number of dogs.
SQL 3: Display the kennel which has the most dogs among all kennels; use the ALL operator.
SQL 4: Show the dogs which are the winners (PLACE = 1) to all the shows that have attended more
than once.
SQL 5: Show the average place (rank) for each owner (whose dog(s) have at least attended a
show), and the average rank overall. Experiment with the UNION operator and the AVG operator
including the ‘::numeric(10,2)’ (see Note 2).
Note 1: Link for PostgreSQL Aggregation Functions:
https://www.postgresqltutorial.com/postgresql-avg-function/
Note 2: Link for get a real number/value after applying an aggregation operator over an attribute:
https://www.postgresqltutorial.com/postgresql-avg-function/
Task 2: Modification SQL Statements
Write and execute the SQL statements involving CREATE, DROP, INSERT, DELETE, UPDATE
Statement 1: Create a table with the ID attribute as integer: Tobedeleted(ID) and then drop it:
CREATE TABLE Tobedeleted(ID integer);
DROP TABLE Tobedeleted;
Statement 2: Execute the following alternations over Tobedeleted(ID); create the table again 😊
CREATE TABLE Tobedeleted(ID integer);
ALTER TABLE Tobedeleted ADD COLUMN surname varchar(50);
ALTER TABLE Tobedeleted DROP COLUMN surname;
ALTER TABLE Tobedeleted RENAME ID TO NameID;
https://www.postgresqltutorial.com/postgresql-avg-function/
https://www.postgresqltutorial.com/postgresql-avg-function/
DTA (M) Database Theory & Applications
Statement 3: Execute the following PK constraint alternations over Tobedeleted table:
ALTER TABLE Tobedeleted ADD CONSTRAINT PK PRIMARY KEY (NameID);
ALTER TABLE Tobedeleted DROP CONSTRAINT PK
Statement 4: Execute the following SQL insertions:
INSERT INTO Owner(ownerid, name, phone)
VALUES (2015, ‘chris’, ‘01413307252’);
INSERT INTO Owner(ownerid, name, phone)
VALUES (2015, ‘nikos’, ‘01413307252’);
Statement 5: Execute the following SQL updates.
UPDATE Owner SET name=‘nikos’
WHERE ownerid = 2015;
UPDATE Owner SET ownerid = 2015
WHERE ownerid = 4563
Statement 6: Execute the following SQL deletion: delete the owner with id = 2015.
DELETE FROM Owner WHERE ownerid = 2015