SQL Glossary Terms

Primary Key

Constraints

Unique identifier for a table's rows.

CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50));

Foreign Key

Constraints

Column linking to another table's primary key.

CREATE TABLE orders (id INT, user_id INT, FOREIGN KEY (user_id) REFERENCES users(id));

Index

Indexes

Structure to speed up query performance.

CREATE INDEX idx_name ON users(name);

Unique Constraint

Constraints

Ensures column values are unique.

CREATE TABLE users (email VARCHAR(50) UNIQUE);

Table

Schema

Structure storing data in rows and columns.

CREATE TABLE products (id INT, name VARCHAR(50));

Column

Schema

Field storing a specific data type in a table.

ALTER TABLE users ADD COLUMN email VARCHAR(50);

Row

Schema

Single record in a table.

INSERT INTO users (name, age) VALUES ('Alice', 30);

Join

Joins

Combines data from multiple tables.

SELECT users.name, orders.amount FROM users INNER JOIN orders ON users.id = orders.user_id;

Subquery

Queries

Nested query within another query.

SELECT name FROM users WHERE id IN (SELECT user_id FROM orders);

Aggregate

Aggregates

Function summarizing data, e.g., COUNT, SUM.

SELECT COUNT(*) FROM orders;

Schema

Schema

Database structure defining tables and relationships.

CREATE SCHEMA sales;

View

Schema

Virtual table based on a query.

CREATE VIEW active_users AS SELECT * FROM users WHERE active = true;

Trigger

Automation

Procedure executed on table events.

CREATE TRIGGER update_timestamp AFTER UPDATE ON users FOR EACH ROW UPDATE users SET updated_at = NOW();

Transaction

Transactions

Set of operations executed as a unit.

BEGIN; INSERT INTO orders (amount) VALUES (100); COMMIT;

Alias

Queries

Temporary name for a column or table.

SELECT name AS full_name FROM users;