Quiz 2 - Study Guide Flashcards
Terminology over chapters 3 & 4 to help you prepare for quiz 2 of COP 4555 - Database Management.
CREATE TABLE
A command to define a new table (relation) by specifying its columns, data types, and integrity constraints.
INSERT
A SQL statement to add new rows to a table.
DELETE
A SQL statement to remove rows from a table.
DROP TABLE
A command that removes an entire table and its data from the database.
ALTER TABLE
A command to modify an existing table’s structure (e.g., adding or dropping columns).
DDL (Data Definition Language)
The subset of SQL used to create, alter, and drop database objects (e.g., tables), and specify integrity constraints.
DML (Data Manipulation Language)
The subset of SQL used to query and modify data (SELECT, INSERT, UPDATE, DELETE).
Transaction Control
SQL statements (e.g., BEGIN, COMMIT, ROLLBACK) that manage transactions, ensuring data integrity across multiple operations.
Embedded SQL / Dynamic SQL
Mechanisms for placing SQL commands within a general-purpose programming language to execute queries and updates.
Authorization
Commands and mechanisms in SQL to grant or revoke user privileges on database objects.
Schema
The overall logical structure of a database, describing relations (tables), attributes (columns), and constraints.
Integrity Constraints
Rules that maintain data accuracy and consistency (e.g., PRIMARY KEY, FOREIGN KEY, NOT NULL).
Primary Key
A column (or set of columns) that uniquely identifies each row in a table.
Foreign Key
A column (or set of columns) in one table that references the primary key of another table, creating a relationship.
NOT NULL
A constraint disallowing NULL values in a column.
char(n)
A fixed-length character data type of length n.
varchar(n)
A variable-length character data type with a maximum length of n.
int / smallint
Integer data types, with ranges dependent on the machine. smallint is typically a smaller range than int.
numeric(p,d)
A fixed-point number with p total digits, d of which appear after the decimal point.
float(n)
A floating-point data type with at least n digits of precision.
Basic Query Structure (SELECT-FROM-WHERE)
The core SQL query format specifying desired columns (SELECT), source tables (FROM), and row conditions (WHERE).
SELECT *
A shorthand to select all columns from the specified table(s).
DISTINCT
A keyword that removes duplicate rows in the query result.
Logical Connectives (AND, OR, NOT)
Used in the WHERE clause to combine or negate conditions.
Cartesian Product
The result of listing every row of one table with every row of another, typically constrained by a matching condition.
Table Alias (AS)
A way to rename a table (or column) temporarily within a query for convenience (e.g., instructor AS T).
Self Join
A join in which a table is joined to itself using different aliases.
LIKE
A pattern-matching operator, often used with wildcards (%) and (_) to filter rows based on partial string matches.
ORDER BY
A clause used to sort query results by specified columns in ascending (ASC) or descending (DESC) order.
BETWEEN
An operator used in the WHERE clause to filter rows within an inclusive numeric or date range.
Set Operations (UNION, INTERSECT, EXCEPT)
Commands that combine the results of multiple subqueries: UNION merges two sets (removing duplicates by default), INTERSECT returns rows common to both sets, EXCEPT returns rows in one set but not in the other.
union all / intersect all / except all
Variants of set operations that preserve duplicate rows.
NULL
A marker that indicates missing or unknown data.
IS NULL / IS NOT NULL
Predicates that check whether a value is NULL or not NULL, respectively.
Three-Valued Logic
Logical evaluation in SQL that can yield TRUE, FALSE, or UNKNOWN (due to NULL).
Aggregate Functions (AVG, MIN, MAX, SUM, COUNT)
Functions that operate on a column’s collection of values to produce a single summarized result.
GROUP BY
A clause that groups rows that share the same values in specified columns, often used with aggregates.
HAVING
A clause (used with GROUP BY) that filters groups based on an aggregate condition.
Nested Subquery
A SELECT-FROM-WHERE statement placed inside another query (e.g., in the WHERE or FROM clause).
IN
Tests whether a value matches any value in a given list or the result of a subquery.
SOME
Compares a value to each value in a subquery and returns TRUE if the comparison is TRUE for at least one.
ALL
Compares a value to each value in a subquery and returns TRUE if the comparison is TRUE for every value.
EXISTS / NOT EXISTS
EXISTS returns TRUE if a subquery returns any rows; NOT EXISTS returns TRUE if a subquery returns none.
CASE Statement
A conditional expression providing IF-THEN-ELSE logic inside queries or updates.
UPDATE
A SQL statement to modify existing row values in a table.
Transaction
A sequence of one or more SQL operations that form a single logical unit of work, typically ending with COMMIT or ROLLBACK.
Join
A SQL operation that combines rows from two tables based on a related column between them (often matching rows with a specific condition).
Natural Join
A type of join that automatically matches columns between two tables with the same name and removes duplicate columns in the result.
Inner Join
A join that returns rows where there is at least one match in both tables.
Outer Join
A set of join operations (LEFT, RIGHT, FULL) that include rows from one or both tables even if they do not match in the other table.
Left Outer Join
An outer join that returns all rows from the left (first) table, plus matched rows from the right (second) table.
Right Outer Join
An outer join that returns all rows from the right (second) table, plus matched rows from the left (first) table.
Full Outer Join
An outer join that returns all rows from both tables, with matched rows merged into a single row.
USING Clause
A clause that specifies which columns should be used to match rows when performing joins.
ON Condition
A clause in a join specifying a custom condition for matching rows.
View
A virtual table derived from one or more real tables (or other views) through a SELECT query.
CREATE VIEW
A SQL statement used to define a new view based on a query expression.
Materialized View
A view whose results are physically stored (copied) for quick access.
View Expansion
The process of internally rewriting a query on a view by substituting the view’s defining query into the main query.
Updating a View
The action of performing INSERT, UPDATE, or DELETE on a view.
COMMIT
A SQL statement to end a transaction, making all the changes within the transaction permanent in the database.
ROLLBACK
A SQL statement to undo all changes made in the current transaction.
UNIQUE Constraint
Similar to PRIMARY KEY but allows NULL values. Ensures that values in specified column(s) are distinct across rows.
CHECK Constraint
A condition that each row in the table must satisfy.
Assertion
A database-wide constraint (using CREATE ASSERTION) that enforces a condition involving multiple tables.
Referential Integrity
Ensures that a foreign key value in one table corresponds to an existing primary key in another table.
FOREIGN KEY
A column (or set of columns) in one table whose values must match the primary key of another table.
ON DELETE CASCADE / ON UPDATE CASCADE
Options that automatically propagate deletions or updates from the referenced (parent) table to the referencing (child) table.
SQL Data Types (DATE, TIME, TIMESTAMP)
Built-in types in SQL to represent dates, times, and combined date+time values.
INTERVAL
A type representing a duration or period of time (e.g., days, hours).
Large Object (LOB) Types
Data types for storing large unstructured objects: BLOB: Binary Large Object, CLOB: Character Large Object.
User-Defined Type (UDT)
A custom type created with CREATE TYPE, allowing developers to define new domain-specific data types.
Domain
A user-defined data type created with CREATE DOMAIN that can include constraints such as NOT NULL or CHECK.
Index
A data structure (e.g., B+ tree, hash) that speeds up data retrieval on specific columns.
GRANT Statement
Used to give specific privileges (SELECT, INSERT, etc.) on a database object (table, view) to a user.
REVOKE Statement
Used to remove previously granted privileges from a user or role.
Roles
Named groups of privileges in SQL.
WITH GRANT OPTION
A clause in a GRANT statement allowing the recipient to further grant the same privileges to other users.
