Querying Relational Databases with SQL

Question 1

Add a tuple in the Student table/ insert rows

Answer 1

INSERT
INTO Student (mn, name, email, age)
VALUES (‘s1253477’, ‘Jenny’, ‘jenny@sms.ed.ac.uk’, 23)
• The above statement adds a tuple in the Student table.
• We could omit the list of column names and simply list
the values in the appropriate order, but it is good
practice to include column names.

Question 2

Deleting rows

Answer 2

DELETE
FROM Student
WHERE name = ‘Alan’

Question 3

Updating rows

Answer 3

We can update the column values in an
existing row using the UPDATE command
UPDATE Student
SET name = ‘Alan’
WHERE mn = ‘s1428571’

Question 4

What questions can we ask to the database?

Answer 4

Which students are older than 19?
 What are the names of all students taking the
Medical Informatics course?
 What is the average age of all students born in
Europe who are taking the Medical Informatics
course but not the Advanced Databases course?

Question 5

How to query returns all students older than

19.

Answer 5

• The following query returns all students older than
19.
SELECT *
FROM Student
WHERE age > 19
• The * means that the table returned has the same
schema as Students.

Question 6

What is SQL query syntax?

Answer 6

SELECT [DISTINCT] field-list
FROM table-list
[ WHERE qualification ]
• Anything in [square brackets] is optional.
• SELECT: the columns to be retained in the result
• FROM: the tables from which to take the data
• WHERE: conditions that should hold for the records
to be picked out

Question 7

What can we use instead of *

Answer 7

Instead of using *, we can explicitly specify the
list of fields to be returned. These could be in
a different order than in the original table
SELECT mn, name, email, age
FROM Student
WHERE age > 19

Question 8

How can we specify which tables the fields are from

Answer 8

We can specify which tables the fields are
from.
• This is particularly useful when the FROM clause includes several tables. 
SELECT Student.mn, Student.name,
Student.email, Student.age
FROM Student
WHERE Student.age > 19
Locally abbreviating names= Student S

Question 9

How to select only a subset of the

fields of each selected tuple

Answer 9

SELECT S.name
FROM Student S
WHERE S.age > 19
• In this case, the table returned has a different
schema to that in Student

Question 10

How not to specify a condition through

the WHERE-part of the query

Answer 10

SELECT age

FROM Student

Question 11

How to remove any duplicates from

the returned records

Answer 11

By using DISTINCT, we remove any duplicates from
the returned records.
SELECT DISTINCT age
FROM Student

Question 12

How to query returns the email
addresses of all students taking Medical
Informatics

Answer 12

SELECT S.email
FROM Student S, Takes T, Course C
WHERE S.mn = T.mn
AND T.cid = C.cid
AND C.title = ‘Medical Informatics’

Question 13

How to query evaluation

Answer 13

1. Take all rows from the tables.
2. Keep only the row combinations that satisfy the
   qualification conditions.
3. Return the specified columns.

Question 14

The three set-operation constructs

that extend the basic form of a query:

Answer 14

UNION: A or B
 INTERSECT: A and B
 EXCEPT: A but not B

Question 15

Example of union

Answer 15

Find the email addresses of all students taking

Medical Informatics or Advanced Databases

Question 16

Example of intersect

Answer 16

Find the email addresses of all students taking

Medical Informatics and Advanced Databases.

Question 17

Example of except

Answer 17

Find the email addresses of all students taking

Medical Informatics but not Advanced Databases

Question 18

What are nested queries?

Answer 18

• Queries that have other queries embedded within
them.
• The idea is to use the result of one query to build
another one.
• The following query returns the names of all
students that have a mark higher than 70 in any
course.

Question 19

Example of nested queries

Answer 19

The following query returns the names of all
students that have a mark higher than 70 in any
course.
SELECT DISTINCT S.name
FROM Student S
WHERE S.mn IN ( SELECT T.mn
FROM Takes T
WHERE T.mark > 70 )

Question 20

What are aggregate operators in SQL?

Answer 20

SQL also allows us to compute aggregate values

rather than simply retrieve data.

Question 21

What are the 5 aggregate operations available?

Answer 21

COUNT([DISTINCT] field-name): The number of (unique)
values in a particular field
 SUM([DISTINCT] field-name): The total of all (unique)
values in a particular field
 AVG([DISTINCT] field-name): The mean of all (unique)
values in a particular field
 MAX(field-name): The maximum value in a particular field
 MIN(field-name): The minimum value in a particular field