Lecture on More SQL in Database Systems

These flashcards cover key concepts related to SQL as outlined in the lecture on more complex SQL retrieval, including Nested Queries, Aggregate Functions, and the use of NULL.

More Complex SQL Retrieval Queries

Additional features that allow users to specify more complex data retrievals from databases.

NULL

Represents an unknown value, an unavailable or withheld value, or a not applicable attribute.

Three-Valued Logic

A logic system in SQL that includes TRUE, FALSE, and UNKNOWN as possible truth values.

IS NULL and IS NOT NULL

SQL queries that check whether an attribute value is NULL.

Nested Queries

Queries that are embedded within other queries, allowing for complex filtering based on subqueries.

IN Clause

A comparison set operator used to check if a value exists within a set of values.

Correlated Nested Queries

A nested query where the inner query references attributes from the outer query.

EXISTS Function

SQL function used to check if the result of a nested query is empty or not.

JOIN

An operation that combines rows from two or more tables based on a related column.

Aggregate Functions

Functions such as COUNT, SUM, MAX, MIN, and AVG used to summarize information from multiple tuples.

GROUP BY Clause

Used to group rows that have the same values in specified columns into summary rows.

HAVING Clause

Provides a condition for filtering groups created by the GROUP BY clause.

CREATE VIEW

SQL command to define a virtual table derived from other tables.

ALTER Command

SQL command used to modify an existing database structure.

DROP Command

SQL command used to remove schema elements such as tables.