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.