SQL Practice Questions and Lecture Review

Vocabulary flashcards covering core SQL commands, database relationships, ACID properties, subqueries, and scalar/aggregate functions based on the transcript.

Primary Key

A column or set of columns that uniquely identify each row of a table; must be unique and not null.

Foreign Key

A column (or columns) that references a column (most often the primary key) of another table to establish a relationship between records in separate tables.

Referential Integrity

Ensures that the foreign key in any referencing table must always refer to a valid row in the referenced table, keeping relationships synchronized during updates and deletes.

Inner Join

A join that returns only the rows that actually match based on the join predicate.

Outer Join

A join that returns rows that match as well as rows that are not common between both tables.

Composite Key

A combination of two or more columns in a table that can be used to uniquely identify each row in the table.

One-to-One (1:1) Relationship

A relationship between two tables where both tables should be associated with each other based on only one matching row.

DROP TABLE

An SQL statement that removes one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables.

DELETE Command

An SQL command used to remove rows from a table, requiring COMMIT or ROLLBACK to make permanent or undo, and firing DELETE triggers.

Entity-Relationship Diagram (ERD)

A data modeling technique that creates an illustration of an information system's entities and the relationships between those entities.

NOT NULL Constraint

An SQL constraint indicating that a column cannot store a NULL value.

HAVING Clause

An SQL clause that specifies a search condition for a group or an aggregate function used in a SELECT statement.

Normalization

The process of organizing data in a database by creating tables and establishing relationships to eliminate redundancy and inconsistent dependency.

UNION Operator

An SQL operator that returns only distinct rows that appear in either result set.

UNION ALL Operator

An SQL operator that returns all rows from combined queries without eliminating duplicate selected rows.

INTERSECT Operator

An SQL operator that returns only those rows returned by both queries.

Subquery (Nested Query)

A query within another SQL query embedded within the WHERE clause to return data used as a condition in the main query.

Orphaned Record

A record that references a key which no longer exists in the foreign table.

SELECT INTO

An SQL statement that selects data from one table and inserts it into a new table.

Cross Join

A join without a WHERE or ON clause that produces the Cartesian product of the tables involved in the join.

Cascade Delete

A foreign key setting where deleting a record in the parent table automatically deletes corresponding records in the child table.

ORDER BY

An SQL keyword used to sort the result-set by one or more columns in ascending order by default or descending order with DESC.

View

A virtual table in SQL based on the result-set of an SQL statement, containing fields from one or more real tables.

Index

A structure created in a table to allow the database application to find data fast without reading the whole table.

Scalar Function

An SQL function that operates on each individual row and returns a single value for that row.

Aggregate Function

An SQL function that operates on a set of rows to return aggregate calculated data.

Implicit Cursor

A context area memory space automatically created by Oracle whenever an SQL statement is executed without an explicit cursor.

Explicit Cursor

A programmer-defined cursor in PL/SQL created on a SELECT statement returning more than one row for control over the context area.

Common Table Expression (CTE)

A temporary named result set derived from a single SELECT, INSERT, DELETE, or UPDATE statement, defined using the WITH keyword.

DDL (Data Definition Language)

The SQL component used to define and modify database schema structure (includes CREATE, ALTER, DROP, TRUNCATE).

DML (Data Manipulation Language)

The SQL component used to add, change, and remove data in tables (includes INSERT, UPDATE, DELETE).

DQL (Data Query Language)

The SQL component used to retrieve and query data from the database (associated with SELECT).

DCL (Data Control Language)

The SQL component used to control access and privileges on database objects (includes GRANT, REVOKE).

TCL (Transaction Control Language)

The SQL component used to manage database transactions (includes COMMIT, ROLLBACK, SAVEPOINT).

Stored Procedure

Precompiled SQL code providing advanced database functionality and security for users without direct task access.

LIKE Command

An SQL command used to match string patterns in data retrieval queries.

JDBC

Java Database Connectivity, providing a connection between front-end Java applications and back-end databases.

Degree of a Relationship

The number of entity types participating in a relationship.

Binary Relationship

A relationship involving two entity types.

Ternary Relationship

A relationship involving three entity types.

Cardinality Ratio Constraint

A constraint specifying the maximum number of relationship instances that an entity can participate in (1:N, N:1, 1:1, M:N).

Participation Constraint

A constraint specifying the minimum number of relationship instances that each entity can participate in.

Total Participation

A constraint where every entity in the entity set must participate in at least one relationship instance.

Partial Participation

A constraint where not every entity is required to participate in the relationship.

Atomicity

An ACID property treating a transaction as a single, indivisible unit where either all operations succeed or none do.

Consistency (ACID)

An ACID property ensuring the database transitions from one valid state to another, adhering to all rules, constraints, and triggers.

Isolation

An ACID property guaranteeing that concurrent transactions do not interfere with each other, appearing as if executed sequentially.

Durability

An ACID property guaranteeing that once a transaction is committed, its changes are permanent despite system failures.

% Wildcard

An SQL wildcard character representing zero or more characters in a LIKE pattern.

_ Wildcard

An SQL wildcard character representing exactly one character in a LIKE pattern.

COUNT(*)

An aggregate function that returns the number of rows in the group or result set being counted.

AVG()

An aggregate function that returns the average value of a numeric expression or column.

MIN()

An aggregate function that returns the minimum value in the selected set.

MAX()

An aggregate function that returns the maximum value in the selected set.

SUM()

An aggregate function that returns the sum of selected numeric values.

UPPER()

A text scalar function that converts text to uppercase.

LOWER()

A text scalar function that converts text to lowercase.

LENGTH()

A text scalar function that returns the length of a text value.

TRIM()

A text scalar function that removes surrounding whitespace from a string.

SUBSTRING()

A text scalar function that extracts a portion of a string using a starting position and length.

CONCAT()

A scalar function that combines strings or columns into one string.

REPLACE()

A scalar function that replaces a specified substring with a replacement value.

NOW()

A date/time scalar function that returns the current date and time.

CURRENT_DATE

A date/time scalar function that returns the current date.

AGE(timestamp1, timestamp2)

A date/time scalar function that returns the difference between two timestamps.

EXTRACT(field FROM timestamp)

A date/time scalar function that extracts a specified date/time field from a timestamp.