Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

DBT > Revision - Lab qs > Flashcards

Revision - Lab qs Flashcards

1
Q

How do you create a table?

A 
CREATE TABLE t1(
c1 INT PRIMARY KEY, 
c2 INT,
FOREIGN KEY (c2) REFERENCES t2(c2)
);
Set c2 column as a foreign key
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

How do you alter a table by adding a constraint?

A

ALTER TABLE t ADD constraint;

Add a constraint

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

How do you add a PK definition to a table?

A 
CREATE TABLE t(
c1 INT, c2 INT, c3 VARCHAR,
PRIMARY KEY (c1,c2)
);
Set c1 and c2 as a primary key
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

How do you add a FK definition to a table?

A 
CREATE TABLE t1(
c1 INT PRIMARY KEY, 
c2 INT,
FOREIGN KEY (c2) REFERENCES t2(c2)
);
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

How do you alter a table by removing a constraint?

A

LTER TABLE t DROP constraint;

Drop a constraint

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What is the LIKE operator for?

A

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 %, _

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

From the DOG database, how do you choose the owners whose phone number starts with ‘141’?

OWNERID, PHONE attributes needed.

A

SELECT *
FROM owner
WHERE phone like ‘141%’ or phone IS NULL;

% means 0, 1 or multiple chars
LIKE operator compares chars

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

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.

A

SELECT DISTINCT BREEDNAME
FROM DOG
ORDER BY BREEDNAME ASC

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What is the syntax of ordering information in ascending or descending order?

A

SELECT c1, c2
FROM t
ORDER BY c1 ASC [DESC];

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

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.

A 
SELECT dog.kennelname
FROM dog
WHERE dog.name = dog.mothername
OR dog.name = dog.fathername
OR dog.mothername IS NULL
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

How do you check if there is a value or no value?

A

IS NULL

OR

IS NOT NULL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

QL 4: Show the breed of each dog sorted by the dog name in ascending order by associating two relations.

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Display the dogs (names), their owners (names) and the shows (showname) that the dogs have participated.

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

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).

A 
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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What are aliases?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Can aliases be used to copy a table, so that it can be combined with itself?

A

Yes this fusion of information from the same table.

Used in recursion eg. Employee AS Employee and Supervisor AS supervisor

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

SQL 7: Get the owner (name) whose dogs’ kennels have unspecified addresses (addresses
with NULL values).

A 
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’.

18
Q

How do you represent the concept of unspecified in a query?

A

By declaring ‘IS NULL’ to represent the concept of ‘unspecified’.

19
Q

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…

A 
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.

20
Q

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.

A

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.

21
Q

What does a subquery return?

A

A set of values, rather than a single so we need to use one of the set operators.

IN, EXITS, ALL/ANY

22
Q

What does the ‘IN’ operator do?

A

Checks if a value is in a set ie., (True/False)

23
Q

What does the ‘EXISTS’ operator do?

A

Checks if a set is empty or not (True/False)

24
Q

What does ALL/ANY do?

A

Checks if a relationship holds for every/one member of the set (true/false)

25
Q

What comparison operators are ALL and ANY used with?

A 
=
><
<>    
>=
<=

<> means not equal to

26
Q

When do you use EXISTS?

A

To check if there is at least 1 element in a set

Select
From
Where exists

27
Q

When do you use NOT EXISTS?

A

When you are checking if the set is empty.

SELECT
FROM
WHERE NOT EXISTS

28
Q

When do you use IN?

A

Use IN to see if a value is in a set of values

SELECT
FROM
WHERE
IN

29
Q

When do you use NOT IN?

A

To check if a given value is not in a set

SELECT
FROM
WHERE
NOT IN

30
Q

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.

A 
SELECT O.OWNERID, O.NAME, 
COUNT(DISTINCT (K.KENNELNAME))
FROM OWNER AS O, 
 DOG AS D, KENNEL AS K
WHERE 
D.OWNERID = O.OWNERID AND 
D.KENNELNAME = K.KENNELNAME
GROUP BY O.OWNERID
ORDER BY O.NAME
31
Q

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.

A

SELECT * FROM DOG
WHERE
KENNELNAME IN
(‘Novar kennel club’, ‘Canine kennel club’, ‘Island kennel club’)

32
Q

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

A 
SELECT O.NAME, D.NAME
FROM OWNER AS O, DOG AS D
WHERE O.OWNERID = D.OWNERID
AND D.DOGID NOT IN (
SELECT A.DOGID
FROM ATTENDANCE AS A)
ORDER BY O.NAME
33
Q

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.

A 
SELECT D1.NAME, O1.NAME
FROM DOG AS D1, OWNER AS O1
WHERE EXISTS (
SELECT * 
FROM ATTENDANCE AS A2
WHERE D1.DOGID = A2.DOGID
AND A2.PLACE = 1)
AND O1.OWNERID = D1.OWNERID
34
Q

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

A 
ELECT DISTINCT(A1.DOGID), D1.NAME
FROM ATTENDANCE AS A1, DOG AS D1
WHERE A1.PLACE > ANY(
SELECT A2.PLACE
FROM ATTENDANCE AS A2)
AND D1.DOGID = A1.DOGID
35
Q

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

A 
ELECT DISTINCT(A1.DOGID), D1.NAME
FROM ATTENDANCE AS A1, DOG AS D1
WHERE A1.PLACE <= ALL(
SELECT A2.PLACE
FROM ATTENDANCE AS A2)
AND D1.DOGID = A1.DOGID
36
Q

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.

A

ELECT Dog.name AS NAME, (4-Attendance.place)*100 AS REWARD
FROM Dog, Attendance
WHERE Attendance.place <= 3
AND Dog.dogid = Attendance.dogid

e just declare at the select statement the function with the attribute we want to calculate. In this case it is
(4-attendance.place)*100. Then, we simply provide an alias, like ‘REWARD’ to get a representation of the
outcome of the function.

37
Q

SQL 8: How many different dogs have participated in shows? Experiment with the COUNT and
DISTINCT operators.

A

SQL 8: SELECT COUNT(DISTINCT(dogid))FROM Attendance

We retrieve all the different dogids from the Attendance table, since a dog might have attended to a show
more than once, and then we count them with the COUNT operator.

38
Q

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

A

SELECT MIN(place) AS MIN_PLACE
FROM Attendance, Dog
WHERE Dog.dogid = Attendance.dogid
AND Dog.name LIKE ‘%e%’

e collect all dogs whose name contains an ‘e’ from the attendance table and then take the minimum of
their corresponding place. This minimum is just over the places of those dogs that satisfy the above criterion

39
Q

SQL 10: Display how many dogs have participated in each show, order by the show name. Experiment
with the GROUP BY operator.

A 
SELECT Show.showname, COUNT(Dog.dogid)
FROM Show, Attendance, Dog
WHERE Show.showname = Attendance.showname AND
Attendance.dogid = Dog.dogid
GROUP BY Show.showname
ORDER BY Show.showname

e group all dogs that have attended the same show together, and then simply count them. In order to know
which dog was present at a show, we need to involve the attendance table in the query.

40
Q

SQL 11: Display how many dogs are owned by each owner, order by the owner name. Experiment with
the GROUP BY operator.

A

ELECT Owner.name, Owner.ownerid, COUNT() AS DOGS_OWNED
FROM Owner, Dog
WHERE Owner.ownerid = Dog.ownerid
GROUP BY Owner.ownerid
We group all dogs with the same owner and then simply count the dogs per owner. This means that for each
owner, there are dogs with different dogids, thus, we adopt the COUNT() operator.

41
Q

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 ame 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 �

A

QL 12:
Case 1:
SELECT Kennel.kennelname,COUNT(Dog.dogid) as Quantity
FROM Kennel, Dog
WHERE Dog.kennelname = Kennel.kennelname
GROUP BY Kennel.kennelname
ORDER BY Quantity DESC
LIMIT 1
We group all the dogs with the same kennel together. The grouping is based on the kennelname, which is a
primary key. Then, we count all the corresponding dogs for each kennel and sort the kennels according to the
number of dogs in decreasing order (from high to low). We just keep/show only the first kennel of the result,
which has the most dogs. This means that there exists ONLY one kennel having the most dogs! If there are
more than
Case 2: We first find the maximum number of dogs in the kennels. Then, check if a kennel has as many
dogs as the minimum number.
SELECT Kennel.kennelname AS KENNEL_NAME, COUNT(Dog.dogid) AS MOST_DOGS
FROM Kennel, Dog
WHERE Dog.kennelname = Kennel.kennelname
GROUP BY Kennel.kennelname
HAVING COUNT(Dog.dogid) = (
SELECT MAX(DOGS_PER_KENNEL.Quantity)
FROM (
SELECT COUNT(Dog.dogid) as Quantity
FROM Kennel, Dog
WHERE Dog.kennelname = Kennel.kennelname
GROUP BY Kennel.kennelname) AS DOGS_PER_KENNEL
)
In the inner subquery, we construct a relation called DOGS_PER_KENNEL with the attribute Quantity,
where we store the number of dogs per kennel. Then, in an outer sub-query, we get the maximum value of
the attribute Quantity from the relation DOGS_PER_KENNEL. In the most outer query, we calculate the
number of dogs per kennel and then check if this number is equal to the maximum value of the Quantity of
DOGS_PER_KENNEL.
Similar example solution and explanation is provided in the lecture �

42
Q

What does <> mean?

A

<> means not equal to

The following example returns all rows in the Production.ProductCategory table that do not have value in ProductCategoryID that is equal to the value 3 or the value 2.

SELECT ProductCategoryID, Name
FROM Production.ProductCategory
WHERE ProductCategoryID <> 3 AND ProductCategoryID <> 2;

DBT (8 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions