Database Management System 1 - SQL Essentials

Vocabulary flashcards covering key SQL concepts, commands, command sub-categories (DDL, DML, DQL, TCL, DCL), engine mechanisms, and constraints based on lecture material.

Data Definition Language (DDL)

A category of SQL queries used to define and modify the structure of the database and its objects, including commands like CREATE, ALTER, DROP, TRUNCATE, and RENAME.

Data Manipulation Language (DML)

A category of SQL queries used to manipulate data stored in the database, including commands like INSERT, UPDATE, and DELETE.

Data Query Language (DQL)

A category of SQL queries centered on the SELECT command, used to retrieve data from database tables.

Transaction Control Language (TCL)

A category of SQL commands used to manage database transactions and ensure data integrity, including COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION.

Data Control Language (DCL)

A category of SQL queries used to manage access privileges and security permissions for database objects, including GRANT and REVOKE.

SQL Parser

The database engine component that breaks down a query into pieces to verify its syntax and structure, throwing an error if a typo or logic issue exists.

Query Optimizer

The database engine component that calculates and determines the most efficient path or strategy to retrieve or process query results.

Execution Plan

A step-by-step guide produced after query optimization that specifies how the database engine will carry out an operations request.

SQL Joins

A feature in SQL used to combine records from multiple tables based on related columns between them.

Aggregations

SQL functions used to calculate summary results across multiple rows of data, such as totals or averages using functions like COUNT(*) or AVG().

Transaction

An undoable bundle of database operations executed as a single group, ensuring all changes succeed together or fail together.

PRIMARY KEY Constraint

A database constraint combining NOT NULL and UNIQUE attributes to uniquely identify each individual row in a table.

FOREIGN KEY Constraint

A database constraint that prevents actions that would break relationships or links established between tables.

NOT NULL Constraint

A constraint ensuring that a designated column cannot store a NULL value.

UNIQUE Constraint

A constraint ensuring that all values stored within a specific column are distinct from one another.

CHECK Constraint

A constraint ensuring that all values in a column satisfy a defined logical condition.

DEFAULT Constraint

A constraint that assigns a predefined value to a column when no value is explicitly supplied.

CREATE INDEX

A command used to create lookup structures on table columns to significantly speed up data retrieval.

MySQL View

A virtual table containing rows and columns derived from one or more underlying tables, created using the CREATE VIEW statement.

TRUNCATE

A DDL command that deletes all rows from a table while leaving its schema structure intact.

EXPLAIN Command

A utility command used to display execution plans for queries, aiding in query and index performance optimization.

AUTO_INCREMENT

A MySQL field attribute that automatically generates sequential unique numeric values whenever a new row is added.

FLUSH PRIVILEGES

A command used to clear and reload the database server internal memory cache regarding user privileges and grants.