SQL: Advanced Queries, Assertions, Triggers and Views

Flashcards for reviewing advanced SQL concepts, including nested queries, joins, assertions, triggers, and views.

Nested Queries

Complete select-from-where blocks within WHERE clause of another query.

Outer Query

The main query in a nested query structure.

IN operator

Compares a value with a set of values; evaluates to TRUE if the value is in the set.

= ANY (or = SOME) operator

Returns TRUE if the value is equal to some value in the set.

Correlated nested query

Evaluated once for each tuple in the outer query.

EXISTS function

Checks whether the result of a correlated nested query is empty or not.

Joined table

A table resulting from a join operation in the FROM clause of a query.

NATURAL JOIN

Implicit EQUIJOIN condition for each pair of attributes with the same name from relations R and S.

Inner join

Tuple is included in the result only if a matching tuple exists in the other relation (default type of join).

LEFT OUTER JOIN

Every tuple in the left table must appear in the result; if no matching tuple, padded with NULL values for the right table's attributes.

RIGHT OUTER JOIN

Every tuple in the right table must appear in the result; if no matching tuple, padded with NULL values for the left table's attributes.

FULL OUTER JOIN

Combines the results of both left and right outer joins.

Aggregate functions

Used to summarize information from multiple tuples into a single-tuple summary (COUNT, SUM, MAX, MIN, AVG).

GROUP BY clause

Specifies grouping attributes to partition a relation into subsets of tuples.

HAVING clause

Provides a condition on the summary information in a GROUP BY query.

CREATE TRIGGER

Specifies automatic actions that the database system will perform when certain events and conditions occur.

ALTER Command

Used to modify table structure using actions such as adding or dropping a column (attribute), Changing a column definition, Adding or dropping table constraints.

Query modification

Modify view query into a query on underlying base tables.

UNIQUE(Q) function

Returns TRUE if there are no duplicate tuples in the result of query Q.

CREATE ASSERTION

Specifies additional types of constraints outside the scope of built-in relational model constraints.

CREATE TRIGGER

Specifies automatic actions that the database system will perform when certain events and conditions occur.

DROP command

Used to drop named schema elements, such as tables, domains, or constraints.

ALTER Command

Used to modify table structure using actions such as adding or dropping a column (attribute), Changing a column definition, Adding or dropping table constraints.

CREATE VIEW command

Creates a virtual table derived from other tables.

View materialization

Physically create a temporary view table when the view is first queried.

Clause WITH CHECK OPTION

Must be added at the end of the view definition if a view is to be updated.

In-line view

Defined in the FROM clause of an SQL query.

WHERE clause

Specifies a search condition for a group or an aggregate.

AVG function

Returns the average value of a numeric column.

COUNT function

Returns the number of rows that matches a specified criterion.

MAX function

Returns the largest value of the selected column.

MIN function

Returns the smallest value of the selected column.

SUM function

Returns the sum of all values in a numeric column.

ORDER BY Clause

Used to sort the result-set in ascending or descending order.

JOIN Clause

Combines rows from two or more tables, based on a related column between them.

GROUP BY

Used with aggregate functions to group the result-set by one or more columns.

Define common table expressions

Allows to name a sub-query block, which can then be referenced in several places in the main SQL query.