Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Data Management Foundations, D426 > Complex queries > Flashcards

Complex queries Flashcards

1
Q
  • Describe the IN operator
A
  • Used in WHERE clause to determine if a value is in several listed values
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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 BETWEEN operator can be used with any data types for which comparison operators, like < > =, are valid.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q
  • Describe the LIKE operator
A
  • Matches text against a pattern using two ‘wildcard’ characters: % and _
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q
  • What does the ‘%’ operator do?
A
  • % matches any number of characters
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q
  • What does the _ operator do?
A
  • _ matches exactly 1 character
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q
  • Describe the DISTINCT clause
A
  • Used to return only unique or distinct values
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q
  • Describe the ORDER BY clause
A
  • Returns results in ascending order
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q
  • How would one return a series of strings in descending order using the ORDER BY clause?
A
  • Can add DESC keyword if the reverse is desired
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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_
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q
  • What is an aggregate function?
A
  • Processes values from a set of rows and returns a summary value
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q
  • Describe the HAVING clause
A
  • HAVING is used with GROUP BY to filter group results
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q
  • How do aggregate functions treat NULL values?
A
  • Aggregate functions ignore null values
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q
  • How do arithmetic functions treat NULL values?
A
  • Arithmetic operations return null if any operand is null
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q
  • Describe a join statement
A
  • Aggregating data from multiple tables is called ‘joining’
    • A join is a SELECT statement that combines data from two tables
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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 AS keyword
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
Q
  • Does MySQL support FULL JOIN?
A
  • MySQL does not support FULL JOIN
26
Q
  • Describe the UNION keyword
A
  • 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
Q
  • Describe a subquery
A

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

28
Q
  • Describe a correlated subquery
A
  • When subqueries reference a column in the outer query, they are referred to as correlated subquery
29
Q
  • Describe the EXISTS operator
A
  • Returns TRUE if at least one row in subquery exists, and FALSE if not
    • NOT EXISTS is the opposite
30
Q
  • What is ‘flattening a subquery’?
A
  • Replacing a subquery with a join is called flattening the subquery
31
Q
  • Describe the steps in flattening a subquery
A
  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
Q
  • Describe the process for writing a complex query
A
  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
Q
  • What is a ‘view table’?
A
  • View table → a table associated with a SELECT statement, AKA `view query
34
Q
  • What is a ‘base table’?
A
  • The name of the table in a view query’s FROM clause
35
Q
  • Describe a ‘materialized view’
A
  • the ability to store view table data
36
Q
  • Why is updating, inserting, or deleting values in a view table generally not a good idea?
A
  • Whenever a base table changes, the corresponding view tables can also change, so materialized views must be refreshed
37
Q
  • What are ‘Codd’s operations’?
A
  • A single operation must be available to do each of the following operations:retrieve data, insert data, update data, or delete data
38
Q
  • What are some of relational algebra’s symbols?
A
  • Sigma, Pi, X, Bowtie
39
Q
  • What is the ‘relational algebra’ symbol for the Select expression? What does it do?
A
  • Written as: {sigma symbol}expression(table name)
    • Equivalent as SELECT * FROM Table WHERE expression.
40
Q
  • What is the ‘relational algebra symbol’ for the Project expression? What does it do?
A
  • Written as {Pi symbol} (column1, column2)(Table name)
    • Equivalent as `SELECT Column1, Column2, … FROM Table
41
Q
  • What is the ‘relational algebra symbol’ for the Product expression? What does it do?
A
  • 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 n1rows and Table2 has n2rows, then the product has n1× n2rows
42
Q
  • What is the ‘relational algebra symbol’ for the Join expression? What does it do?
A
  • Denoted with the bowtie symbol
    • Equivalent to SELECT * FROM Table1 INNER JOIN Table2 ON expression
43
Q
  • What is the ‘relational algebra symbol’ for the Union expression? What does it do?
A
  • combines all rows of two compatible tables into a single table excluding duplicate rows
    • Equivalent to: SELECT * FROM Table1 UNION SELECT * FROM Table2.
44
Q
  • What is the ‘relational algebra symbol’ for the Intersect expression? What does it do?
A
  • Returns only rows that appear in both tables
    • Equivalent to: SELECT * FROM Table1 INTERSECT SELECT * FROM Table2
45
Q
  • What is the ‘relational algebra symbol’ for the Difference expression? What does it do?
A
  • 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
Q
  • What is the ‘relational algebra symbol’ for the Rename expression? What does it do?
A
  • Specifies new table and column names
    • where TableName is the new table name, and ColumnName1 (etc) are new column names
47
Q
  • What is the ‘relational algebra symbol’ for the Aggregate expression? What does it do?
A
  • 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 toSELECT Function(Column) FROM Tableand computes a single aggregate value for all rows.

Data Management Foundations, D426 (5 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions