SQL Flashcards
(25 cards)
What does SQL stand for?
Structured Query Language
Units of time are divided into two groups in SQL, list the groups.
year, month
day, hour, minute, second
Distinguish between intra and inter relational constraints.
Intra - involve a single relation
Inter - involve multiple relations
Explain the four different reaction policies.
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
T or F. SQL is a declarative language.
True
Give a basic SQL query structure.
select attribute, attribute
from table , table
where conditon x and condition y
When combining two tables in sql what must be included as condition in the where clause?
That the foreign key holds true. E.g. that taking_course is equal to course_code
When tables are joined along attributes X with the same name what is the syntax for the select?
select C.attribute
from table C, table P
where condition x and C.X = P.X
Give an example of an aggregate SQL query.
count()
Differentiate between count(*), count(distinct) and count(all).
count(*) selects all rows
count(distinct) selects all unique rows
count(all) selects all rows that have not null values.
Is the following query valid?
select FirstName, Surname, max(Salary)
from EMPLOYEE
Query is invalid. Syntax does not allow aggregate functions and attribute expressions together in select clause.
What is the group by clause?
Allows applying aggregate operators to specific subsets of rows.
What is the rule for the group by clause syntax?
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.
What is the having clause?
Having clause added after group by clause to retain only subsets that satisfy a group predicate.
Rule for having clause syntax.
Only aggregate operators are allowed to appear in the having clause.
What does keyword all mean when added to the SQL keyword union?
Keep duplicates.
What is the short of =any?
in
What is the short of <>all?
not in
What does the exist operator return?
Returns true only if nested query does not produce a null result.
Explain insert, delete and update.
Insert: insert rows
Delete: delete rows
Update: Change attribute values of the rows of a table
What are the two categories of constraints?
Immediate
Deferred
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
(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.
What is the check clause?
The check clause can be used to express arbitrary constraints during schema definition.
What is assertion? Give sample code.
Assertion allows the definition of constraints outside of table definitions.
create assertion AssertionName
check (condition)
