SQL Flashcards
1
Q
SQL FROM
A
- Specifies which tables you are using
2
Q
SQL WHERE
A
- Specifies which rows you are including (SELECTION)
3
Q
SQL SELECT
A
- Specifies which columns you are including (PROJECTION)
4
Q
SQL LIKE
A
- % stands for 0 or more arbitrary characters
- _ stands for a single arbitrary character
5
Q
SQL Comparisons Involving NULL
A
- Any comparison involving NULL using standard operators is always False
- Use “IS” to check for NULL
6
Q
SQL COUNT(*) vs COUNT(attribute)
A
- COUNT(*) counts the number of tuples in a result
- COUNT(attribute) counts the number of non-NULL values of an attribute in a result
7
Q
SQL DISTINCT
A
- Removes duplicates
8
Q
SQL GROUP BY
A
- Group together tuples with a common value
- Apply aggregate functions to the tuples in each subgroup
9
Q
SQL HAVING
A
- Allows us to apply a selection condition in the subgroups produced through the GROUP BY clause
10
Q
SQL Difference between WHERE and HAVING
A
- A WHERE clause is applied before grouping
- A HAVING clause is applied after grouping
11
Q
SQL ORDER BY
A
- Sorts the tuples in the result by one or more attributes
- Ascending by default, use DESC for descending
12
Q
SQL Aggregate Functions
A
- Perform a computation on a collection of values on an attribute
- Can be included in the SELECT clause
- Examples: MIN, MAX, AVG, SUM, COUNT
- Generally cannot mix aggregate functions with column names in the SELECT clause
13
Q
SQL Subqueries
A
- Allows us to use the result of one query in the evaluation of another query
14
Q
SQL Set Membership
A
- Subqueries can be used to test for set membership in conjunction with the IN and NOT IN operators
15
Q
SQL Set Operations
A
- UNION
- INTERSECTION
- EXCEPT (set difference)
16
Q
SQL Outer Joins
A
- FROM T1 LEFT OUTER JOIN T2 ON join condition
- FROM T1 RIGHT OUTER JOIN T2 ON join condition
- FROM T1 FULL OUTER JOIN T2 ON join condition
17
Q
SQL CREATE TABLE
A
- Creates a relation with the specified schema
- CREATE TABLE relation_name(attribute_name attribute_type, …);
18
Q
SQL DROP TABLE
A
- Removes an entire relation from a database
- A relation cannot be dropped if referred to by a foreign key
- DROP TABLE relation_namel
19
Q
SQL INSERT
A
- Adds a tuple to a relation
- INSERT INTO relation VALUES (val1, val2, …);
- INSERT INTO relation(attr1, attr2, …) VALUES (val1, val2,…);
20
Q
SQL DELETE
A
- Removes one or more tuples from a relation
- DELETE FROM table WHERE selection condition;
21
Q
SQL UPDATE
A
- Modifies one or more tuples in a relation
- UPDATE table SET list of assignments WHERE selection condition;
