SQL Subqueries and Python For Loops Review

0.0(0)
Studied by 0 people
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
Card Sorting

1/39

flashcard set

Earn XP

Description and Tags

A comprehensive vocabulary review of SQL subquery concepts, roadmap levels, scoping rules, comparison operators (IN, ANY, ALL, EXISTS), interview patterns, and Python loop fundamentals.

40 Terms

1
New cards

Subquery Execution Order

The standard two-step execution process in SQL where the inner query executes first and subsequently returns its output to the outer query.

2
New cards

Subquery Column Compatibility Rule

The golden rule specifying that the column returned by a subquery must be compatible in data type and structure with the column being compared in the outer query.

3
New cards

Level 1 – Foundations

The introductory stage of the subquery learning roadmap covering what a subquery is, why it is needed, and how SQL executes nested queries.

4
New cards

Level 2 – Basic Subqueries

The learning level covering subqueries used with the WHEREWHERE clause and operators such as ININ, NOT INNOT\ IN, ==, >>, and <<.

5
New cards

Level 3 – Aggregate Subqueries

The learning level focused on using aggregate functions like AVG()AVG(), MAX()MAX(), MIN()MIN(), SUM()SUM(), and COUNT()COUNT() inside nested subqueries.

6
New cards

Level 4 – Multi-Row and Multi-Column Subqueries

The learning level that focuses on handling multi-row and multi-column results using operators like ININ, ANYANY, ALLALL, and multiple-column comparisons.

7
New cards

Level 5 – Correlated Subqueries

Nested queries where the inner query references and depends directly on columns from the outer query for each processed row.

8
New cards

Level 6 – Subqueries in Different SQL Clauses

The learning level covering the syntax and use cases for embedding subqueries across different SQL clauses: SELECTSELECT, FROMFROM, WHEREWHERE, and HAVINGHAVING.

9
New cards

Level 7 – Advanced Interview Problems

Complex scenario-based subquery problems including ranking, finding second-highest salaries, department comparisons, duplicates, and above-average employees.

10
New cards

Level 8 – Expert-Level Challenges

The highest subquery level addressing nested correlated subqueries, EXISTSEXISTS, NOT EXISTSNOT\ EXISTS, performance optimization, and tricky interview case studies.

11
New cards

SQL EXISTS Operator

A logical operator that checks whether a subquery returns at least one row, evaluating to TRUE if data exists and FALSE otherwise.

12
New cards

SQL NOT EXISTS Operator

A logical operator that checks whether a subquery returns zero rows, evaluating to TRUE when no matching records are found.

13
New cards

Inward Visibility

The subquery scoping rule that permits an inner nested query to look outward to reference tables and column aliases defined in the main query.

14
New cards

Outward Visibility

The subquery scoping restriction that blocks the outer main query from looking inward to access tables or aliases defined strictly inside the subquery.

15
New cards

Inner Query Alias Destruction

The SQL scoping behavior where any table alias defined inside a subquery is destroyed once the subquery's closing parenthesis is reached.

16
New cards

SELECT List Independence in EXISTS

The design characteristic of EXISTSEXISTS where it completely ignores whatever columns are listed in the subquery's SELECTSELECT clause (such as SELECT 1SELECT\ 1).

17
New cards

SQL IN Operator

A membership comparison operator that checks whether a given outer value equals at least one value in a specified list or single-column subquery result.

18
New cards

SQL = ANY Operator

A comparison construct meaning 'equals at least one value', functioning identically to the ININ operator.

19
New cards

SQL > ANY Operator

A comparison operator that evaluates to TRUE if the tested value is greater than at least one value returned by the subquery (meaning higher than the minimum).

20
New cards

SQL < ANY Operator

A comparison operator that evaluates to TRUE if the tested value is less than at least one value returned by the subquery.

21
New cards

SQL > ALL Operator

A comparison operator that evaluates to TRUE only if the tested value is strictly greater than every value returned by the subquery (meaning higher than the maximum).

22
New cards

SQL < ALL Operator

A comparison operator that evaluates to TRUE only if the tested value is strictly less than every value returned by the subquery.

23
New cards

Scalar Subquery

A subquery used in a comparison expression (e.g., salary>(SELECT avg(salary) FROM employees)salary > (SELECT\ avg(salary)\ FROM\ employees)) that returns exactly one row and one column value.

24
New cards

Subquery inside SELECT Clause

A subquery placed directly within the outer query's SELECTSELECT statement to compute and return a newly calculated scalar column alongside existing attributes.

25
New cards

Correlated Department Average Pattern

A query pattern that filters employees by checking e.salary>(SELECT AVG(x.salary) FROM employees x WHERE e.department=x.department)e.salary > (SELECT\ AVG(x.salary)\ FROM\ employees\ x\ WHERE\ e.department = x.department).

26
New cards

Second-Highest Salary Subquery Pattern

An interview query technique using MAX(salary)MAX(salary) filtered by a subquery: WHERE salary<(SELECT MAX(salary) FROM employees)WHERE\ salary < (SELECT\ MAX(salary)\ FROM\ employees).

27
New cards

Third-Highest Salary Subquery Pattern

A technique using two nested MAX(salary)MAX(salary) subqueries with the << operator to successively filter out the first and second highest salaries.

28
New cards

Fourth-Highest Salary Subquery Pattern

A four-level nested subquery structure using sequential <(SELECT MAX(salary)...)< (SELECT\ MAX(salary)...) conditions to isolate the fourth highest salary value.

29
New cards

Second-Highest Salary in Each Department Pattern

A query grouping by departmentdepartment that finds MAX(e.salary)MAX(e.salary) where e.salary<(SELECT MAX(x.salary) FROM employees x WHERE x.department=e.department)e.salary < (SELECT\ MAX(x.salary)\ FROM\ employees\ x\ WHERE\ x.department = e.department).

30
New cards

ANY Minimum Threshold Behavior

The evaluation rule where beating at least one value from a list (such as Sales salaries: 5000050000, 6500065000, 5800058000) requires only beating the lowest number (5000050000).

31
New cards

ALL Maximum Threshold Behavior

The evaluation rule where beating all values from a department list (such as Sales salaries: 5000050000, 6500065000, 5800058000) requires beating the highest number (6500065000).

32
New cards

Unassigned Employees Query Pattern

A subquery pattern using WHERE NOT EXISTS (SELECT 1 FROM employee_project ep WHERE ep.employee_id=e.employee_id)WHERE\ NOT\ EXISTS\ (SELECT\ 1\ FROM\ employee\_project\ ep\ WHERE\ ep.employee\_id = e.employee\_id) to find workers with no projects.

33
New cards

Non-Ordering Customers Query Pattern

A subquery pattern using WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id=c.customer_id)WHERE\ NOT\ EXISTS\ (SELECT\ 1\ FROM\ orders\ o\ WHERE\ o.customer\_id = c.customer\_id) to find customers who placed zero orders.

34
New cards

Ordering Customers Query Pattern

A subquery pattern using WHERE customer_id IN (SELECT customer_id FROM Orders)WHERE\ customer\_id\ IN\ (SELECT\ customer\_id\ FROM\ Orders) or a correlated EXISTSEXISTS clause to find customers with orders.

35
New cards

Compound Boundary Subquery Pattern

A query filtering records against two separate subqueries simultaneously, such as exceeding the company average salary while staying below the maximum IT salary.

36
New cards

range() Function in Python

A built-in Python function used within for loops to generate arithmetic sequences of numbers for controlling iteration cycles.

37
New cards

enumerate() Function in Python

A built-in Python function used in for loops that yields pairs containing both the loop index and the corresponding element value from an iterable.

38
New cards

zip() Function in Python

A built-in Python function used to combine and iterate across multiple lists or sequences concurrently inside a loop.

39
New cards

Python Loop Control Statements

The keywords breakbreak (which immediately exits a loop) and continuecontinue (which skips the remainder of the current iteration and jumps to the next).

40
New cards

Python for-else Clause

A specialized Python loop construct where the elseelse block executes after the for loop completes normal iteration without encountering a breakbreak.