SQL Flashcards
Why SQL?
High-level language that specifies “what” rather than “how” avoiding data manipulation details; lets DBMS optimize and execute “best way”
What is the Basic SQL Query?
SELECT [Distinct] target-list FROM relation-list WHERE qualification
What does FROM specify?
A list of relation names possibly with a range-variable
What does the SELECT specify?
The list of attributes of relations in relation-list
What does the WHERE specify?
The qualification or comparisons (Attribute op const/attribute) combined using AND, OR, NOT
What is DISTINCT and where is it placed?
Optional keyword indicating that the answer should not contain duplicates; its placed between SELECT and the target-list
What is the conceptual evaluation strategy of the basic SQL query?
- Compute the cross-product of the relation-list
- Discard resulting tuples if they fail qualifications
- Delete attributes not in the target-list
- If distinct is specified, eliminate duplicate rows
What is a Range variable and when are they Variables needed?
Ex: From Relations R;
needed only if the same relation appears twice in the FROM caluse
What is LIKE keyword used for and where is it used?
Used for string matching in the WHERE clause.
Ex: WHERE s.name LIKE ‘B-%B’
In conjunction with LIKE, what does _ mean
Any one character
In conjunction with LIKE, what does % mean
0 or more characters
What are two ways to name fields in a result?
Using AS and =
Ex: SELECT age1=S.age-5, 2*S.age AS age2
Does SELECT-FROM-WHERE use bag or set semantics? Why?
Bag because it is easier to avoid eliminating duplicates.
Does union, intersection, and difference use bag or set semantics? Why?
Set because it’s already sorted so it’s easy to eliminate duplicates.
How do you force the result to be a set?
SELECT DISTINCT
How do you force the result to be a bag?
ALL Keyword (Ex: UNION ALL)
What is a nested Query?
A feature where WHERE, FROM, or HAVING clauses can contain an SQL query?
What are the 3 query clauses that can use a nested query?
WHERE, FROM, HAVING
How do you find tuples that are not in a query?
Use NOT IN
What are SQL Join Expressions?
NATURAL JOIN, CROSS JOIN, OUTER JOIN can be used in place of relations in a FROM clause
What is the SQL Theta Join Semantics?
R JOIN S on
Ex: Sailors S JOIN Reserves R ON S.sid=R.sid;
What are the optional OUTER JOIN keywords?
1-NATURAL in front of OUTER
2- Optional ON after JOIN
3- LEFT, RIGHT, or FULL before OUTER
What are the 6 SQL aggregate operations?
1- COUNT(*) 2- COUNT ([DISTINCT] A) 3- SUM([DISTINCT] A) 4- AVG[DISTINCT ] A) 5- MAX (A) 6- MIN (A) where A is a single column
Where are the aggregate operators placed?
In the SELECT clause (which can be in the where clause for nested query)
What use grouping?
To apply aggregate operators on groups instead of individual tuples.
What follows the GROUP BY clause?
A grouping-list?
What follows the HAVING clause?
A group-qualification
When using grouping, what may be in the SELECT target-list?
(i) attribute names that is a subset of the grouping list and (ii) terms with aggregate operations like MIN(S.age)
- each answer tuple corresponds to a group, and these attributes must have a single value per group
If any aggregation is used, what are the 2 restrictions on each element of the SELECT list?
1- elements must be aggregated, or
2- an attribute on the group by list
What may be in the HAVING clause?
Relations or attributes in the FROM clause, that are either
1- grouping attribute
2- aggregated
What is the conceptual evaluation for queries with grouping?
- Compute cross-product of relation-list
- Discard tuples that fail qualification
- Delete unnecessary fields
- Partition remaining tuples into groups by the values of the attributes int he grouping list (group by)
- Apply group qualifications to eliminate some groups. Expressions must have a single value per group. One answer tuple is generated per qualifying group.
What’s the difference between EXISTS and IN?
EXISTS evaluates to True or False while IN is used to compare against a subquery column
Can aggregate operations be nested?
No, the below example is wrong:
Ex: Where.Sage = (SELECT MIN(AVG(S2.age)) FROM Sailors S2)…
When are NULL values used?
When field values are unknown or inapplicable
What is a constraint?
A relationship among data elements that the DBMS is required for enforce
ex: key, foreign key
What is a trigger?
An event, condition, or action that is more flexible and general than a constraint
What are 5 kinds of constraints?
1- Keys
2- Foreign key (referential integrity)
3- Value-based (attributes contained to a value)
4- Tuple-based (relations among components)
5- assertions: any SQL boolearn expression
What are 2 possible violations to foreign keys?
1- insert/update on referencing relation
2- delete/update on referenced relation
What are 4 possible actions on foreign key violations?
1- reject
2- cascade
3- set NULL
4- set value
What are attributed-based checks and how are they implemented?
- constraints on the value of a particular attribute
Add CHECK to declaration of attribute;
EX: beer CHAR(2) (beer IN (SELECT name FROM beers))
Ex: price REAL CHECK (price
When are attribute checks done?
When attribute value is inserted or updated?
What is a tuple-based check?
A check added as a relation-scheme element. The Check may refer to any attribute of the relation
Ex: CHECK (bar = ‘Joes’ OR price
What are assertions and how are they created?
Database-scheme elements like relation or views
CREATE ASSERTION
CHECK
where condition may refer to any relation or attribute in a database scheme
When are assertion checks?
On every modification to every relation of the database (drawback), but a clever system could do better
