Advanced SQL Topics and Query Design

Seventy question-and-answer flashcards covering joins, subqueries, derived tables, set operations, CASE logic, views, triggers, routines, temporal extensions, performance guidelines, and recent SQL enhancements.

Why do we construct multiple-table queries in an RDBMS?

To leverage relationships between tables and combine related data into one result set.

What relationship links rows across tables by matching values in common columns?

A primary-key–foreign-key relationship.

Which clause of a SELECT statement normally specifies join conditions between tables?

The WHERE clause (or ON/USING inside the FROM clause).

What is the most frequently used relational operation in SQL?

The join operation.

List three common join types defined in the SQL standard.

INNER JOIN, OUTER JOIN (LEFT/RIGHT/FULL), and CROSS JOIN.

What rows does an INNER JOIN return?

Only rows that have matching values in both tables.

How does an OUTER JOIN differ from an INNER JOIN?

An OUTER JOIN also returns unmatched rows from one (LEFT/RIGHT) or both (FULL) tables, filling missing columns with NULLs.

Name the three outer-join variants supported by the ANSI syntax.

LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.

What is a Cartesian join and when does it occur unintentionally?

A join that returns every possible row combination of the participating tables; it happens when no join condition is supplied.

Which keyword explicitly requests a Cartesian product?

CROSS JOIN.

Define an equi-join.

A join that links tables by testing equality between common column values.

What advantage does the USING clause offer over ON in an equi-join?

It shortens syntax when the join columns share the same name in both tables.

Why is SQL called a set-oriented language with respect to joins?

Because it processes entire sets of rows at once rather than one row at a time.

What distinguishes a NATURAL JOIN from an equi-join?

A natural join removes duplicate common columns from the result.

When two tables share a column name, why must that column be qualified in a query?

To resolve ambiguity and tell the DBMS which table’s column is being referenced.

In an OUTER JOIN result, what appears in columns from the table that lacks a match?

NULL values.

Why must you test outer joins involving more than two tables carefully?

Different DBMSs may interpret complex outer-join syntax differently, producing unexpected results.

How many ON or WHERE conditions are needed when joining three tables A, B, and C?

Two conditions (A-B and B-C).

What is a self-join?

A query that joins a table to itself to compare rows within the same table.

Give one business situation that commonly requires a self-join.

Representing an employee-to-manager (supervises) relationship stored in one Employee table.

Why do self-joins use table aliases?

To distinguish the two logical copies of the same table in the query.

When would you use a subquery instead of a join?

When you need to filter results based on values derived from another SELECT but don’t need to display columns from that secondary table.

Which operator lets a subquery return multiple values to the outer query?

IN (or NOT IN).

What do EXISTS and NOT EXISTS test in SQL?

Whether the subquery returns any rows (non-empty set) or none (empty set).

Define a correlated subquery.

A subquery that references a column from the outer query and is executed once for each outer-query row.

What is a derived table?

A temporary result set produced by a subquery in the FROM clause that can be treated like a table in the outer query.

Why are derived tables useful when using aggregates such as AVG or MAX?

They let you compute the aggregate once and reference its value elsewhere in the outer query’s WHERE or SELECT clauses.

What requirement must all SELECTs in a UNION satisfy?

Each SELECT must return the same number of columns with compatible data types.

How does the CAST function assist when combining queries with UNION?

It converts column data types so that the result sets are compatible.

What do the INTERSECT and MINUS operators do?

INTERSECT returns rows common to both result sets; MINUS returns rows in the first set that are not in the second.

Which SQL keyword supplies IF-THEN-ELSE logic inside a query?

CASE.

Name the four forms of the CASE expression in SQL.

Expression-based CASE, predicate-based CASE, NULLIF, and COALESCE.

List two major advantages of using views.

They simplify complex queries for users and can hide sensitive columns to enhance security.

How does a dynamic view differ from a materialized view?

A dynamic view is recreated at query time and stores no data; a materialized view stores data physically and is refreshed periodically.

What does WITH CHECK OPTION guarantee when updating through a view?

That any insert or update performed through the view still satisfies the view’s defining WHERE condition.

Define a trigger in SQL.

A stored block of procedural code that the DBMS executes automatically in response to a specified event on a table or database.

What are the three parts of a typical trigger definition?

Event, condition (optional WHEN or FOR EACH ROW), and action.

How do routines (procedures/functions) differ from triggers?

Routines must be called explicitly; triggers fire automatically upon their defined event.

Give one administrative use of a DDL trigger.

Preventing accidental DROP or ALTER TABLE commands on production objects.

Why can cascaded triggers be dangerous?

They may cause unintended side effects or infinite loops if triggers fire other triggers repeatedly.

What does SQL/PSM stand for?

SQL Persistent Stored Modules.

In SQL terminology, what is a routine?

A stored procedure or function defined in the database.

How does a function differ from a procedure in SQL/PSM?

A function returns a single value and has only input parameters, while a procedure can have input, output, and input/output parameters.

List two advantages of SQL-invoked routines over embedding logic in each application.

Server-side routines are sharable by many programs and can be optimized centrally for performance.

Name two vendor-specific procedural SQL dialects.

Oracle PL/SQL and Microsoft Transact-SQL.

What business goal did the ProductLineSale PL/SQL procedure accomplish for Pine Valley Furniture?

It computed and stored sale prices for products by applying percentage discounts based on standard price.

What is the ‘impedance mismatch’ between SQL and many host languages?

SQL processes sets of rows at once, whereas many host languages process one record at a time.

What is the data dictionary in an RDBMS?

A collection of system tables that store metadata about database objects, users, and privileges.

Which Oracle data dictionary view shows all tables in the database?

DBA_TABLES.

Which SQL Server catalog view lists all database objects and their types?

sys.objects.

What major temporal feature did SQL:2011 add?

Support for application-time period tables and system-versioned tables to manage time-variant data.

What columns typically define an application-time period table?

A start date/time and end date/time that bound the validity period of each row’s attributes.

Name two new temporal predicates introduced in SQL:2011.

OVERLAPS and CONTAINS (others include PRECEDES, SUCCEEDS, etc.).

What does a system-versioned table automatically maintain?

Historical rows reflecting every insert, update, and delete, enabling auditing over time.

What is a bi-temporal table?

A table that combines application-time periods and system-versioning to track both business validity and transaction history.

Which two DML operations can the MERGE command perform in one statement?

INSERT and UPDATE (optionally DELETE in some DBMSs).

Which SQL:2016 feature improves time-series pattern detection?

Row Pattern Recognition using the MATCH_RECOGNIZE clause.

What do Polymorphic Table Functions (PTFs) allow you to do?

Write functions that return tables whose row types are determined at runtime, enabling flexible custom processing.

Why should SELECT * generally be avoided in production queries?

It retrieves unnecessary columns, increasing I/O and network traffic and reducing performance.

Why is keeping optimizer statistics up to date important?

Out-of-date statistics can cause the query optimizer to choose inefficient execution plans.

How does using compatible data types for columns and literals help query performance?

It avoids run-time data type conversions that slow processing.

What does the TOP operator (or FETCH FIRST) accomplish?

Returns only the first N rows or a percentage of rows from an ordered result set.

What is the first step in constructing a complex SQL query according to recommended practice?

Identify the desired result and list the required attributes (columns).

Name one efficiency strategy for writing faster SQL queries.

Minimize the number of subqueries or avoid correlated subqueries when possible.

How can temporary tables improve performance for a sequence of related queries?

They store intermediate results once so subsequent queries don’t need to recompute or rescan large base tables.

How do triggers differ from constraints in enforcing integrity?

Constraints are declarative and generally faster but less flexible; triggers allow complex logic but incur more overhead.

Which SQL:2008 addition lets you perform linear regressions and moving averages inside the database?

OLAP/analytical functions.

Why is JSON support in SQL:2016 significant?

It enables relational databases to store, retrieve, and manipulate semi-structured JSON data directly.