Complex queries

Question 1

• Describe the IN operator

Answer 1

• Used in WHERE clause to determine if a value is in several listed values

Question 2

Describe the BETWEEN operator

Answer 2

• Alternative to using ≥ and ≤
  
  • Note that this is inclusive of the min and max values
  • note that The BETWEEN operator can be used with any data types for which comparison operators, like < > =, are valid.

Question 3

• Does BETWEEN work on CHAR types?

Answer 3

The BETWEEN operator can be used with any data types for which comparison operators, like < > =, are valid. CHAR and VARCHAR are valid

Question 4

• Describe the LIKE operator

Answer 4

• Matches text against a pattern using two ‘wildcard’ characters: % and _

Question 5

• What does the ‘%’ operator do?

Answer 5

• % matches any number of characters

Question 6

• What does the _ operator do?

Answer 6

• _ matches exactly 1 character

Question 7

• Describe the DISTINCT clause

Answer 7

• Used to return only unique or distinct values

Question 8

• Describe the ORDER BY clause

Answer 8

• Returns results in ascending order

Question 9

• How would one return a series of strings in descending order using the ORDER BY clause?

Answer 9

• Can add DESC keyword if the reverse is desired

Question 10

• Describe some SQL string functions

Answer 10

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

Question 11

• Describe some SQL numeric functions

Answer 11

• ABS(n) which returns the absolute value of_n
  
  • POW(x, y) which returns x to the power of y

Question 12

• Describe some SQL date functions

Answer 12

• 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_

Question 13

• What is an aggregate function?

Answer 13

• Processes values from a set of rows and returns a summary value

Question 14

• Describe the GROUP BY clause

Answer 14

• 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

Question 15

• Describe the HAVING clause

Answer 15

• HAVING is used with GROUP BY to filter group results

Question 16

• How do aggregate functions treat NULL values?

Answer 16

• Aggregate functions ignore null values

Question 17

• How do arithmetic functions treat NULL values?

Answer 17

• Arithmetic operations return null if any operand is null

Question 18

• Describe a join statement

Answer 18

• Aggregating data from multiple tables is called ‘joining’
  
  • A join is a SELECT statement that combines data from two tables

Question 19

• What are two tables referred to as?

Answer 19

• Left and Right

Question 20

• How do you reference identical column names from different tables?

Answer 20

• If columns from different tables contain the same name, they must be differentiated using a prefix

Question 21

• How do you add an alias to a column name?

Answer 21

• One can add an alias to the column name using the AS keyword

Question 22

• What is the difference between FULL JOIN and INNER JOIN?

Answer 22

• 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

Question 23

• What is the difference between LEFT and RIGHT joins?

Answer 23

• 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

Question 24

• What is an OUTER join?

Answer 24

• *Any join that selects unmatched rows including left, right, and full joins

Question 25

- Does MySQL support FULL JOIN?

Answer 25

- MySQL does not support `FULL JOIN`

Question 26

- Describe the UNION keyword

Answer 26

- 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

Question 27

- Describe a subquery

Answer 27

aka `Nested queries and inner queries` - Typically used in a `SELECT` statements `WHERE` clause - Subqueries are placed in parenthesis

Question 28

- Describe a correlated subquery

Answer 28

- When subqueries reference a column in the outer query, they are referred to as `correlated subquery`

Question 29

- Describe the EXISTS operator

Answer 29

- Returns `TRUE` if at least one row in subquery exists, and `FALSE` if not - `NOT EXISTS` is the opposite

Question 30

- What is ‘flattening a subquery’?

Answer 30

- Replacing a subquery with a join is called *flattening* the subquery

Question 31

- Describe the steps in flattening a subquery

Answer 31

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.*

Question 32

- Describe the process for writing a complex query

Answer 32

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.

Question 33

- What is a ‘view table’?

Answer 33

- View table → a table associated with a `SELECT` statement, AKA `view query

Question 34

- What is a ‘base table’?

Answer 34

- The name of the table in a view query’s `FROM` clause

Question 35

- Describe a ‘materialized view’

Answer 35

- the ability to store view table data

Question 36

- Why is updating, inserting, or deleting values in a view table generally not a good idea?

Answer 36

- Whenever a base table changes, the corresponding view tables can also change, so materialized views must be refreshed

Question 37

- What are ‘Codd’s operations’?

Answer 37

- A single operation must be available to do each of the following operations: **retrieve data, insert data, update data, or delete data**

Question 38

- What are some of relational algebra’s symbols?

Answer 38

- Sigma, Pi, X, Bowtie

Question 39

- What is the ‘relational algebra’ symbol for the Select expression? What does it do?

Answer 39

- Written as: `{sigma symbol}expression(table name)` - Equivalent as `SELECT * FROM Table WHERE expression`.

Question 40

- What is the ‘relational algebra symbol’ for the Project expression? What does it do?

Answer 40

- Written as `{Pi symbol} (column1, column2)(Table name) ` - Equivalent as `SELECT Column1, Column2, ... FROM Table

Question 41

- What is the ‘relational algebra symbol’ for the Product expression? What does it do?

Answer 41

- 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

Question 42

- What is the ‘relational algebra symbol’ for the Join expression? What does it do?

Answer 42

- Denoted with the bowtie symbol - Equivalent to `SELECT * FROM Table1 INNER JOIN Table2 ON expression`

Question 43

- What is the ‘relational algebra symbol’ for the Union expression? What does it do?

Answer 43

- combines all rows of two compatible tables into a single table excluding duplicate rows - Equivalent to: `SELECT * FROM Table1 UNION SELECT * FROM Table2.`

Question 44

- What is the ‘relational algebra symbol’ for the Intersect expression? What does it do?

Answer 44

- Returns only rows that appear in both tables - Equivalent to: `SELECT * FROM Table1 INTERSECT SELECT * FROM Table2`

Question 45

- What is the ‘relational algebra symbol’ for the Difference expression? What does it do?

Answer 45

- 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

Question 46

- What is the ‘relational algebra symbol’ for the Rename expression? What does it do?

Answer 46

- Specifies new table and column names - where TableName is the new table name, and ColumnName1 (etc) are new column names

Question 47

- What is the ‘relational algebra symbol’ for the Aggregate expression? What does it do?

Answer 47

- 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.