Revision - Lab qs Flashcards
(42 cards)
How do you create a table?
CREATE TABLE t1( c1 INT PRIMARY KEY, c2 INT, FOREIGN KEY (c2) REFERENCES t2(c2) ); Set c2 column as a foreign key
How do you alter a table by adding a constraint?
ALTER TABLE t ADD constraint;
Add a constraint
How do you add a PK definition to a table?
CREATE TABLE t( c1 INT, c2 INT, c3 VARCHAR, PRIMARY KEY (c1,c2) ); Set c1 and c2 as a primary key
How do you add a FK definition to a table?
CREATE TABLE t1( c1 INT PRIMARY KEY, c2 INT, FOREIGN KEY (c2) REFERENCES t2(c2) );
How do you alter a table by removing a constraint?
LTER TABLE t DROP constraint;
Drop a constraint
What is the LIKE operator for?
Like operator compares characters
%percent sign repesents 0, 1 or multiple characters
_ underscore represents a single character.
SELECT c1, c2 FROM t1
WHERE c1 [NOT] LIKE pattern;
Query rows using pattern matching %, _
From the DOG database, how do you choose the owners whose phone number starts with ‘141’?
OWNERID, PHONE attributes needed.
SELECT *
FROM owner
WHERE phone like ‘141%’ or phone IS NULL;
% means 0, 1 or multiple chars
LIKE operator compares chars
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.
BreedName, DogID needed.
SELECT DISTINCT BREEDNAME
FROM DOG
ORDER BY BREEDNAME ASC
What is the syntax of ordering information in ascending or descending order?
SELECT c1, c2
FROM t
ORDER BY c1 ASC [DESC];
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.
SELECT dog.kennelname FROM dog WHERE dog.name = dog.mothername OR dog.name = dog.fathername OR dog.mothername IS NULL
How do you check if there is a value or no value?
IS NULL
OR
IS NOT NULL
QL 4: Show the breed of each dog sorted by the dog name in ascending order by associating two relations.
SELECT Dog.name as Dog_Name, Breed.breedname as Breed_Name
FROM Dog, Breed
WHERE Dog.breedname = Breed.breedname
ORDER BY Dog_Name
or a single relation
SELECT Dog.name as Dog_Name, Dog.breedname
FROM Dog
ORDER BY Dog_Name
Display the dogs (names), their owners (names) and the shows (showname) that the dogs have participated.
SELECT Attendance.showname AS Show, Owner.name AS Owner, Dog.name AS Dog
FROM Attendance, Dog, Owner
WHERE Attendance.dogid = Dog.dogid
AND Owner.ownerid = Dog.ownerid
QL 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).
SELECT Owner.phone AS TEL, Kennel.address AS address FROM Attendance, Dog, Owner, Kennel WHERE Attendance.dogid = Dog.dogid AND Owner.ownerid = Dog.ownerid AND Dog.kennelname = Kennel.kennelname AND Attendance.place = 1
What are aliases?
Aliases rename columns or tables to:
Make names more meaningful
Make names shorter & easier to typle
Resolve ambiguous names
Selfjoins
There are two forms of aliases:
Column aliases:
SELECT column AS newName
Table aliases:
SELECT…FROM table AS newName
Can aliases be used to copy a table, so that it can be combined with itself?
Yes this fusion of information from the same table.
Used in recursion eg. Employee AS Employee and Supervisor AS supervisor
SQL 7: Get the owner (name) whose dogs’ kennels have unspecified addresses (addresses
with NULL values).
SELECT distinct(O.name) AS Owner FROM Owner O, Dog D, Kennel K WHERE O.ownerid= D.ownerid AND D.kennelname= K.kennelname AND K.address IS NULL
Note: The idea is simply declaring ‘IS NULL’ to represent the concept of ‘unspecified’.
How do you represent the concept of unspecified in a query?
By declaring ‘IS NULL’ to represent the concept of ‘unspecified’.
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…
SELECT A.name FROM dog A, dog B WHERE B.ownerid= A.ownerid AND B.name = 'Laser' AND B.name <> A.name
SELECT D.NAME, O.OWNERID FROM DOG AS D, OWNER AS O WHERE D.OWNERID = O.OWNERID AND D.NAME <> 'Laser' AND O.OWNERID = (SELECT O.OWNERID FROM DOG AS D, OWNER AS O WHERE D.OWNERID = O.OWNERID AND D.NAME = 'Laser');
Note 1: The idea is to get the names of the dogs with the same owner, such that for these dogs, there exists at least one dog with name Laser.
Note 2: Both approaches are correct! As I’ve mentioned in the talk, the inherent complexity of a SQL SELECT statement depends on our way of thinking. SQL is giving us the opportunity to explore different versions of SELECT statements resulting into the same outcome. Now, the DB system knows how to optimally execute the abovementioned queries, and this cannot be enforced or dictated by SQL SELECT statements issued by end-users (only from those being inside the DB system.
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.
SELECT A.name, B.name
FROM dog A, dog B
WHERE B.ownerid = A.ownerid
AND B.name < A.name
Note: The idea is to get the two names A and B of the pair (A, B) of dogs, which have the same owner. But, we display only once the names of each pair, i.e., either (A,B) or (B,A). We achieve that by simply stating for each pair (A,B) that the dog name A has to be smaller (lexicographically) than the dog name B.
What does a subquery return?
A set of values, rather than a single so we need to use one of the set operators.
IN, EXITS, ALL/ANY
What does the ‘IN’ operator do?
Checks if a value is in a set ie., (True/False)
What does the ‘EXISTS’ operator do?
Checks if a set is empty or not (True/False)
What does ALL/ANY do?
Checks if a relationship holds for every/one member of the set (true/false)
