Complex queries Flashcards
(47 cards)
1
Q
- Describe the IN operator
A
- Used in
WHEREclause to determine if a value is in several listed values
2
Q
Describe the BETWEEN operator
A
- Alternative to using ≥ and ≤
- Note that this is inclusive of the min and max values
- note that The
BETWEENoperator can be used with any data types for which comparison operators, like < > =, are valid.
3
Q
- Does BETWEEN work on CHAR types?
A
The BETWEEN operator can be used with any data types for which comparison operators, like < > =, are valid. CHAR and VARCHAR are valid
4
Q
- Describe the LIKE operator
A
- Matches text against a pattern using two ‘wildcard’ characters:
%and_
5
Q
- What does the ‘%’ operator do?
A
-
%matches any number of characters
6
Q
- What does the
_operator do?
A
-
_matches exactly 1 character
7
Q
- Describe the DISTINCT clause
A
- Used to return only unique or distinct values
8
Q
- Describe the ORDER BY clause
A
- Returns results in ascending order
9
Q
- How would one return a series of strings in descending order using the ORDER BY clause?
A
- Can add
DESCkeyword if the reverse is desired
10
Q
- Describe some SQL string functions
A
CONCAT(s1, s2, …) which returns the string that results from concatenating the string arguments
- LOWER(s) which returns the lowercase_s_
- TRIM(s) which returns the string_s_without leading and trailing spaces
11
Q
- Describe some SQL numeric functions
A
- ABS(n) which returns the absolute value of_n
- POW(x, y) which returns x to the power of y
12
Q
- Describe some SQL date functions
A
- CURDATE(), CURTIME(), NOW() which returns the current date, time, or date and time in
'YYYY-MM-DD','HH:MM:SS', or'YYYY-MM-DD HH:MM:SS'format- DATE(expr) TIME(expr) which Extracts the date or time from a date or datetime expression_expr_
- DAY(d) MONTH(d) YEAR(d) which Returns the day, month, or year from date_d_
13
Q
- What is an aggregate function?
A
- Processes values from a set of rows and returns a summary value
14
Q
- Describe the GROUP BY clause
A
- Consists of the GROUP BY keyword and one or more columns
- Commonly used with aggregate functions
- Each simple or composite value of the columns becomes a group
- Then the aggregate function is completed, and returns one row for each group
15
Q
- Describe the HAVING clause
A
-
HAVINGis used withGROUP BYto filter group results
16
Q
- How do aggregate functions treat NULL values?
A
- Aggregate functions ignore null values
17
Q
- How do arithmetic functions treat NULL values?
A
- Arithmetic operations return null if any operand is null
18
Q
- Describe a
joinstatement
A
- Aggregating data from multiple tables is called ‘joining’
- A
joinis aSELECTstatement that combines data from two tables
- A
19
Q
- What are two tables referred to as?
A
- Left and Right
20
Q
- How do you reference identical column names from different tables?
A
- If columns from different tables contain the same name, they must be differentiated using a prefix
21
Q
- How do you add an alias to a column name?
A
- One can add an alias to the column name using the
ASkeyword
22
Q
- What is the difference between FULL JOIN and INNER JOIN?
A
-
INNER JOIN→ Selects only matching left and right table rows-
FULL JOIN→ Selects all left and right table rows regardless of match- Unmatched rows appear as `NULL
-
23
Q
- What is the difference between LEFT and RIGHT joins?
A
-
LEFT JOIN→ Selects all left table rows, but only matching right table rows-
RIGHT JOIN→ Selects all right table rows, but only matching left table rows
-
24
Q
- What is an OUTER join?
A
- *Any join that selects unmatched rows including left, right, and full joins
25
- Does MySQL support FULL JOIN?
- MySQL does not support `FULL JOIN`
26
- Describe the UNION keyword
- Though INNER and OUTER joins can be written without a JOIN clause, it is good practice to include the keyword JOIN
- `UNION` keyword combines results into 1 table
27
- Describe a subquery
aka `Nested queries and inner queries`
- Typically used in a `SELECT` statements `WHERE` clause
- Subqueries are placed in parenthesis
28
- Describe a correlated subquery
- When subqueries reference a column in the outer query, they are referred to as `correlated subquery`
29
- Describe the EXISTS operator
- Returns `TRUE` if at least one row in subquery exists, and `FALSE` if not
- `NOT EXISTS` is the opposite
30
- What is ‘flattening a subquery’?
- Replacing a subquery with a join is called *flattening* the subquery
31
- Describe the steps in flattening a subquery
1. Retain the outer query `SELECT, FROM, GROUP BY, HAVING, and ORDER BY` clauses.
2. Add `INNER JOIN` clauses for each **subquery** table.
3. Move comparisons between **subquery and outer query** columns to `ON` clauses.
4. Add a `WHERE` clause with the remaining expressions in the subquery and outer query `WHERE` clauses.
5. If necessary, remove duplicate rows with `SELECT DISTINCT`.
- *After this first pass, test the flattened query and adjust to achieve the correct result. Verify that the original and flattened queries are equivalent against a variety of data.*
32
- Describe the process for writing a complex query
1. Examine a table diagram or other database summary to understand the tables and relationships.
2. Identify the tables containing the necessary data to answer the question.
3. Determine which columns should appear in the result table.
4. Write a query that joins the tables using the table's primary and foreign keys.
5. Break the problem into simple queries, writing one part of the query at a time.
33
- What is a ‘view table’?
- View table → a table associated with a `SELECT` statement, AKA `view query
34
- What is a ‘base table’?
- The name of the table in a view query’s `FROM` clause
35
- Describe a ‘materialized view’
- the ability to store view table data
36
- Why is updating, inserting, or deleting values in a view table generally not a good idea?
- Whenever a base table changes, the corresponding view tables can also change, so materialized views must be refreshed
37
- What are ‘Codd’s operations’?
- A single operation must be available to do each of the following operations: **retrieve data, insert data, update data, or delete data**
38
- What are some of relational algebra’s symbols?
- Sigma, Pi, X, Bowtie
39
- What is the ‘relational algebra’ symbol for the Select expression? What does it do?
- Written as: `{sigma symbol}expression(table name)`
- Equivalent as `SELECT * FROM Table WHERE expression`.
40
- What is the ‘relational algebra symbol’ for the Project expression? What does it do?
- Written as `{Pi symbol} (column1, column2)(Table name) `
- Equivalent as `SELECT Column1, Column2, ... FROM Table
41
- What is the ‘relational algebra symbol’ for the Product expression? What does it do?
- combines two tables into one result
- The result contains all combinations of rows from both tables
- Equivalent to: `SELECT * FROM Table1 CROSS JOIN Table2`
- **NOTE**: If Table1 has n1 rows and Table2 has n2 rows, then the product has n1 × n2 rows
42
- What is the ‘relational algebra symbol’ for the Join expression? What does it do?
- Denoted with the bowtie symbol
- Equivalent to `SELECT * FROM Table1 INNER JOIN Table2 ON expression`
43
- What is the ‘relational algebra symbol’ for the Union expression? What does it do?
- combines all rows of two compatible tables into a single table excluding duplicate rows
- Equivalent to: `SELECT * FROM Table1 UNION SELECT * FROM Table2.`
44
- What is the ‘relational algebra symbol’ for the Intersect expression? What does it do?
- Returns only rows that appear in both tables
- Equivalent to: `SELECT * FROM Table1 INTERSECT SELECT * FROM Table2`
45
- What is the ‘relational algebra symbol’ for the Difference expression? What does it do?
- Removes from a table all rows that appear in a second table
- Equivalent to: `SELECT * FROM Table1 MINUS SELECT * FROM Table2`
- Will return the first table with any identical rows found in the second table removed
46
- What is the ‘relational algebra symbol’ for the Rename expression? What does it do?
- Specifies new table and column names
- where TableName is the new table name, and ColumnName1 (etc) are new column names
47
- What is the ‘relational algebra symbol’ for the Aggregate expression? What does it do?
- Applies an aggregate function (SUM(), MIN(), etc.)
- Equivalent to: `SELECT GroupColumn, Function(Column) FROM Table GROUP BY GroupColumn`
- **Note**: If GroupColumn is omitted, the operation is equivalent to `SELECT Function(Column) FROM Table` and computes a single aggregate value for all rows.
