Summary of "Week 3 Open Session"

Main ideas & lessons (Week 3 – Intermediate/Advanced SQL)

Course structure

• Week 3 focuses mainly on:
  • SQL (intermediate/advanced)
  • JOINs
• Views and triggers are introduced in the next session/class, and are stated to be less important for exams than SQL itself.
• Coverage target today: sections 3.1 through ~3.3, plus medium/difficult practice questions.

SELECT clauses

DISTINCT

• Removes duplicate values from the result.
• Used in SELECT (e.g., SELECT DISTINCT building ...).
• Example idea: if “Watson” appears twice in the input rows, DISTINCT outputs “Watson” once.

ALL

• Default SQL behavior: duplicates are retained unless you use DISTINCT.
• SELECT ALL ... is often redundant because the default is effectively ALL.

Cartesian product / Cross join

• A Cartesian product produces all possible combinations of rows between two tables.
• Row count logic:
  • If table A has 4 rows and table B has 5 rows → result has 20 rows.
• Implementation note:
  • Using comma syntax in FROM: FROM A, B implies a cross join.
• No shared attributes required:
  • Cross join works even if tables share no common keys.
  • If filtering is needed, it’s done later using the WHERE clause.
• Example workflow emphasized:
  1. Cross join to generate all student–department pairs.
  2. Filter using WHERE student.dept_name = department.dept_name.
  3. Apply additional predicates (e.g., budget threshold) using AND.

Aliasing / rename

• Use table aliases, e.g. FROM student AS s, department AS d.
• Use attribute aliases in output, e.g. SELECT s.name AS student_name, d.budget AS department_budget.
• Prefixing is needed mainly to distinguish same-named attributes from multiple tables.

Logical operators and membership

AND

• All conditions must be true.

OR

• At least one condition must be true.

IN

• Checks whether a value is present in a list (set membership across multiple possible values).
• Example: WHERE semester IN ('fall','spring').

String pattern matching (e.g., LIKE)

• Wildcards:
  • _ (underscore): represents exactly one character
  • % (percent): represents zero or more characters
• Example pattern idea:
  • “Course ID has exactly 3 alphabets before a hyphen”
  • Express with something like LIKE '___%-' (three underscores for 3 letters, then a hyphen).

Ordering: ORDER BY

• Default ordering is ascending if not specified.
• Direction options: ASC / DESC.
• Multi-level sorting:
  • First by one column (e.g., department_name ASC)
  • Then within each group by another column (e.g., total_credits DESC)

Set operations

• Set-style keywords:
  • UNION: combines sets and removes duplicates
  • UNION ALL: keeps duplicates
  • INTERSECT: keeps only common elements
  • EXCEPT: set difference (A minus B)
• Alternative approach discussed:
  • Achieving similar results using boolean logic (IN, AND/OR) rather than explicit set operators.
• Special focus:
  • EXCEPT / set difference with range conditions
  • A “subtract the complement” trick for salary ranges was emphasized.

Aggregate functions

• Core aggregates:
  • SUM, COUNT, AVG, MIN, MAX
• AVG with GROUP BY and HAVING:
  • GROUP BY groups rows by a key (e.g., building).
  • HAVING filters groups after aggregation (e.g., average capacity > threshold).
  • Clause order emphasized: GROUP BY → HAVING → ORDER BY
• MIN / MAX:
  • Use MIN(field) / MAX(field) and optionally rename output columns.
• COUNT:
  • Counts rows occurrences by default (duplicates matter unless DISTINCT is used).
  • For “number of courses running in each building,” the lecture stressed counting rows as given by the schema, including multiple offerings across semesters/years.
  • COUNT(DISTINCT ...) can be used when duplicates must be ignored (tradeoffs discussed).
• SUM:
  • Use SUM(credits) with grouping by department, etc.

---

Nested subqueries & quantifiers (advanced SQL methods)

What nested subqueries are

• A “query within a query.”
• Can appear in:
  • SELECT
  • FROM
  • WHERE
• Reasons given:
  • More concise expression
  • Sometimes improved performance/structure

Subqueries in WHERE: set tests

Set membership: IN / NOT IN

• Use IN / NOT IN to check whether something is in a generated set.
• Example concept:
  • Courses in Fall 2009 and Spring 2010 (intersection)
    • either via INTERSECT
    • or via IN (subquery) approach
• NOT IN supports “in A but not in B” logic.

Set cardinality comparisons: ANY / ALL

• ANY (at least one)
  • Condition is true if it holds for at least one value from the subquery result.
• ALL (every value)
  • Condition is true only if it holds for every value from the subquery result.
• Lecture example:
  • “salary > at least one biology salary” → ANY
  • “salary > all biology salaries” → ALL
• Emphasis about confusion to avoid:
  • When comparing against values from the same table, ensure correct correlated logic.
  • ANY/ALL simplifies comparisons against a subquery-generated list.

Existence tests: EXISTS / NOT EXISTS

• EXISTS
  • Returns true if subquery produces a non-empty result.
  • Returns false if subquery is empty.
• NOT EXISTS
  • Opposite logic: true when subquery produces no rows.
• Key example logic used:
  • Find courses taught in both Fall 2009 and Spring 2010 using EXISTS.
  • Find students who took all biology courses using NOT EXISTS:
    • outer query iterates students
    • inner query checks whether there exists a biology course the student has not taken
    • if such a course exists → the student fails the “took all” condition

Testing absence of duplicates: UNIQUE

• UNIQUE(subquery)
  • True if the subquery result has no duplicate tuples.
• Example concept:
  • “Courses offered at most once in 2009”
  • Uses UNIQUE to ensure no duplicate rows for the same course occurrence in context.

Subqueries in FROM (derived tables)

• Subquery as a temporary relation:
  • The lecture showed building an intermediate aggregated table inside FROM, e.g.:
    • FROM (SELECT department_name, AVG(salary) ... GROUP BY department_name) AS ...
  • Then the outer query filters it:
    • WHERE average_salary > threshold
• This was described as producing a temporary relation from the query.

WITH clause (CTE - temporary relation)

• Purpose:
  • Defines a temporary named relation available only within the query.
• Example concept:
  • Create a temp relation holding the maximum budget, then select departments whose budget equals that value.

---

Practice instruction segments (questions where method/approach was emphasized)

Query construction practice (medium)

Players name starts with S but does not end with N

• Approach: use EXCEPT
  • Set A: names matching “starts with S”
  • Set B: names matching “ends with N”
  • Result: A EXCEPT B

Users who ordered at least one product with quantity in

• Preferred set/membership strategy:
  • Use a subquery with IN
  • Inner query: select user_id from orders where quantity IN (...)
  • Outer query: select user names where user_id is in that set
• Alternative mentioned:
  • Use a join (natural join) plus filtering on quantity IN (...)
  • May need DISTINCT to handle duplicates.

Query construction practice (difficult)

State and sum(amount) of products ordered per state where ratings > 4

• Approach described:
  • Identify required tables:
    • address for state
    • payment for amount
    • orders for product_id
    • reviews for rating
  • Use joins and grouping:
    • SELECT state, SUM(amount) ...
    • WHERE product_id IN (SELECT product_id FROM reviews WHERE rating > 4)
    • GROUP BY state

---

Speakers / sources featured

• Instructor (“Ma’am”): primary speaker; teaches SQL concepts and runs examples.
• Course materials / slides: referenced during the session (no specific external author named).
• PGAdmin / database schemas: shown live during demonstrations (tool used by the instructor, not a separate source identity).

Category

Educational

---

Share this summary

Share on X/Twitter Share on Facebook Share on LinkedIn Share on Reddit Share on WhatsApp Share on Telegram

---

Is the summary off?

If you think the summary is inaccurate, you can reprocess it with the latest model.

Reprocess summary

Video