Introduction to SQL – Vocabulary Flashcards

These vocabulary flashcards summarize key SQL terms, commands, data types, operators, and constraints covered in the lecture notes.

SQL (Structured Query Language)

A portable, easy-to-learn language used to define, manage, and query relational databases.

Data Definition Language (DDL)

SQL component used to create, alter, or remove database structures such as schemas, tables, and indexes.

Data Manipulation Language (DML)

SQL component used to retrieve and modify data with statements like SELECT, INSERT, UPDATE, and DELETE.

SQL Identifier

A name for database objects (table, column, view) that starts with a letter, contains letters/digits/underscores, has no spaces, and is ≤128 characters.

Character Data Type (CHAR / VARCHAR)

Stores a sequence of characters; defined as CHARACTER VARYING.

Bit Data Type (BIT)

Holds a fixed-length binary string; defined as BIT VARYING.

Exact Numeric Data Type

Stores numbers in decimal notation using NUMERIC or DECIMAL (precision, scale), or integers with INTEGER/INT and SMALLINT.

Approximate Numeric Data Type

Represents real numbers without exact precision using FLOAT, REAL, or DOUBLE PRECISION.

Datetime Data Types

DATE (year-month-day), TIME (hour-minute-second), and TIMESTAMP (date & time) with optional precision and time zone.

Interval Data Type

Represents a period of time; defined with INTERVAL and specified start/end fields such as YEAR TO MONTH.

Schema

A named collection of related database objects such as tables, views, and domains.

CREATE SCHEMA

DDL command: CREATE SCHEMA [name | AUTHORIZATION creator]; creates a new schema.

DROP SCHEMA

DDL command that removes a schema; RESTRICT fails if not empty, CASCADE drops all contained objects.

CREATE TABLE

DDL command to define a new table with columns, constraints, keys, and referential actions.

ALTER TABLE

DDL command to add or drop columns or constraints, or change defaults in an existing table.

DROP TABLE

DDL command that deletes a table; RESTRICT prevents drop if dependent objects exist, CASCADE removes dependents.

PRIMARY KEY

A column or set of columns that uniquely identifies each row in a table.

UNIQUE Constraint

Ensures all values in the specified column(s) are distinct but allows one NULL unless NOT NULL is also specified.

FOREIGN KEY

A column or set of columns in one table that refers to the PRIMARY KEY of another table.

ON DELETE / ON UPDATE

Referential actions (CASCADE, SET NULL, SET DEFAULT, NO ACTION) executed when parent rows are deleted or updated.

CASCADE (Referential Action)

Automatically propagates delete or update operations from the parent table to child rows.

RESTRICT

Prevents a DROP or DELETE/UPDATE if dependent objects or rows exist.

SET NULL

Referential action that sets child foreign-key values to NULL when the parent row is removed or updated.

SET DEFAULT

Referential action that assigns a predefined default value to child rows when the parent row changes.

CHECK Constraint

Validates column or table data against a Boolean condition during insert or update operations.

CREATE INDEX

DDL command: CREATE [UNIQUE] INDEX name ON table (column [ASC|DESC] …); speeds up data retrieval.

DROP INDEX

DDL command that removes an existing index from a table.

CREATE VIEW

DDL command: CREATE VIEW name AS subselect … ; stores a virtual table based on a query.

DROP VIEW

DDL command that removes a view; RESTRICT fails if dependents exist, CASCADE removes them.

Literal

A constant used in SQL. Non-numeric literals are in single quotes; numeric literals are not.

Comparison Operator

=,

Logical Operator

AND, OR, NOT; combine or negate conditions in search predicates.

BETWEEN

[NOT] BETWEEN value1 AND value2; tests whether a value falls within an inclusive range.

IN

[NOT] IN (value1, value2, …); tests membership of a value in a specified list.

LIKE

[NOT] LIKE pattern; performs pattern matching using % (any string) and _ (single character).

IS NULL

Tests whether a column value is NULL; IS NOT NULL tests the opposite.

SELECT Statement

Retrieves data: SELECT [DISTINCT|ALL] columns FROM table WHERE … GROUP BY … HAVING … ORDER BY …

DISTINCT

Keyword in SELECT that removes duplicate rows from the result set.

GROUP BY

Clause that groups rows sharing common values so aggregate functions can be applied to each group.

HAVING

Filters groups produced by GROUP BY based on a condition, often involving aggregates.

ORDER BY

Specifies the ordering of the query result set by one or more columns.

Aggregate Function

Operates on a set of rows: COUNT, SUM, AVG, MIN, MAX; often used with GROUP BY.

INSERT Statement

Adds new rows: INSERT INTO table [(columns)] VALUES (data_values).

UPDATE Statement

Modifies existing rows: UPDATE table SET column = value [, …] WHERE condition.

DELETE Statement

Removes rows: DELETE FROM table WHERE condition; omitting WHERE deletes all rows.

Index (Database)

A separate data structure that improves the speed of row retrieval at the cost of extra storage and maintenance.

ASC / DESC

Keywords that specify ascending or descending order when creating indexes or using ORDER BY.