5. Advanced SQL Flashcards

(11 cards)

1
Q

GROUP BY:

A

Groups rows based on shared values in specified columns. Aggregates apply to each group.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Aggregate Functions:

A

(e.g., SUM, AVG, COUNT, MIN, MAX) compute a single value over a set of rows or a group. Using an aggregate without GROUP BY applies it to the entire result set (treated as one group).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

HAVING:

A

Filters groups based on a condition, typically involving aggregates. Applied after GROUP BY; cannot use WHERE to filter groups.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Order of Evaluation (approximate):

A

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What are Subqueries?

A

A SELECT statement nested within another SQL statement. Can be used in SELECT, FROM, WHERE, or HAVING clauses. Often evaluated first. Many joins can be rewritten as subqueries, but not vice versa.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What are singleton Subqueries?

A

Return at most one row and one column. Used with comparison operators (=, <, >, etc.) in WHERE or HAVING.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What are correlated Subqueries?

A

The inner query’s WHERE clause refers to a table in the outer query. Executed once for each row of the outer query.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

EXISTS:

A

Boolean operator taking a subquery; returns true if subquery returns any rows. Useful with correlated subqueries.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

SOME/ALL:

A

Used with comparison operators and non-singleton subqueries (must return single column).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Common Table Expressions (CTEs) (WITH):

A

Reusable named subqueries defined using the WITH clause within a single SELECT statement. Improve readability and can simplify complex queries.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Window Functions (Analytic Functions):

A

Apply aggregate-like computations over a subset (“window”) of rows related to the current row. They do not reduce the number of rows in the result. Use the OVER clause. PARTITION BY divides rows into partitions (similar to GROUP BY but output includes all rows). ORDER BY sorts rows within each partition.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly