SQL

Question 1

What does SQL stand for?

Answer 1

Structured Query Language

Question 2

Units of time are divided into two groups in SQL, list the groups.

Answer 2

year, month
day, hour, minute, second

Question 3

Distinguish between intra and inter relational constraints.

Answer 3

Intra - involve a single relation
Inter - involve multiple relations

Question 4

Explain the four different reaction policies.

Answer 4

cascade - propagate the change made in one table to linked tables.
set null - nullify the attributes in other tables
set default - assign the default value to referring attributes
no action - don’t allow change

Question 5

T or F. SQL is a declarative language.

Answer 5

True

Question 6

Give a basic SQL query structure.

Answer 6

select attribute, attribute
from table , table
where conditon x and condition y

Question 7

When combining two tables in sql what must be included as condition in the where clause?

Answer 7

That the foreign key holds true. E.g. that taking_course is equal to course_code

Question 8

When tables are joined along attributes X with the same name what is the syntax for the select?

Answer 8

select C.attribute
from table C, table P
where condition x and C.X = P.X

Question 9

Give an example of an aggregate SQL query.

Answer 9

count()

Question 10

Differentiate between count(*), count(distinct) and count(all).

Answer 10

count(*) selects all rows
count(distinct) selects all unique rows
count(all) selects all rows that have not null values.

Question 11

Is the following query valid?
select FirstName, Surname, max(Salary)
from EMPLOYEE

Answer 11

Query is invalid. Syntax does not allow aggregate functions and attribute expressions together in select clause.

Question 12

What is the group by clause?

Answer 12

Allows applying aggregate operators to specific subsets of rows.

Question 13

What is the rule for the group by clause syntax?

Answer 13

The attributes that appear in the target list must be a subset of the attributes used in the group by clause. I.e. all attributes that appear in select must appear in group by.

Question 14

What is the having clause?

Answer 14

Having clause added after group by clause to retain only subsets that satisfy a group predicate.

Question 15

Rule for having clause syntax.

Answer 15

Only aggregate operators are allowed to appear in the having clause.

Question 16

What does keyword all mean when added to the SQL keyword union?

Answer 16

Keep duplicates.

Question 17

What is the short of =any?

Answer 17

in

Question 18

What is the short of <>all?

Answer 18

not in

Question 19

What does the exist operator return?

Answer 19

Returns true only if nested query does not produce a null result.

Question 20

Explain insert, delete and update.

Answer 20

Insert: insert rows
Delete: delete rows
Update: Change attribute values of the rows of a table

Question 21

What are the two categories of constraints?

Answer 21

Immediate
Deferred

Question 22

Give examples of how restraints can be placed on (a) a single relation with a single tuple. (b) a single relation with multiple rows of the same relation. (c) a single relation with aggregation constraints. and (d) On multiple relations

Answer 22

(a) On a single tuple: Attribute constraints, multiple-attribute constraints
(b) On multiple rows of the same relation: Functional dependencies, Cardinality Constraints
(c) Aggregation Constants. E.g. The average salary of employees in department must be greater than 30
(d) On multiple relations: foreign keys.

Question 23

What is the check clause?

Answer 23

The check clause can be used to express arbitrary constraints during schema definition.

Question 24

What is assertion? Give sample code.

Answer 24

Assertion allows the definition of constraints outside of table definitions.
create assertion AssertionName
check (condition)

Question 25

What are views? What rule applies to multiple views?

Answer 25

Views are virtual tables whose contents depend on contents of other tables.

They cannot be mutually dependent.