A schema is a named collection of database objects like tables, views, indexes, and stored procedures. It provides a way to logically group and organize database objects. You can create a schema using CREATE SCHEMA statement and manage object ownership and permissions.
A view is a virtual table based on the result of a SELECT statement. It doesn't store data physically but provides a way to simplify complex queries. Created using CREATE VIEW: CREATE VIEW high_salary_employees AS SELECT * FROM employees WHERE salary > 50000.
A PRIMARY KEY constraint uniquely identifies each record in a table. It can be defined during table creation: CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(100)), or added later: ALTER TABLE employees ADD PRIMARY KEY (id).
A FOREIGN KEY creates a relationship between two tables by referencing the primary key of another table. It ensures referential integrity. Example: CREATE TABLE orders (id INT, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(id)).
The CHECK constraint is used to limit the value range that can be placed in a column. Example: CREATE TABLE employees (age INT CHECK (age >= 18 AND age <= 65)), which ensures the age is between 18 and 65.
Table renaming can be done using ALTER TABLE. The exact syntax varies between database systems. For example, in MySQL: RENAME TABLE old_table TO new_table; in SQL Server: SP_RENAME 'old_table', 'new_table'.
A composite key is a primary key composed of multiple columns. It's used when a single column cannot uniquely identify a record. Example: CREATE TABLE order_items (order_id INT, product_id INT, PRIMARY KEY (order_id, product_id)).
A clustered index determines the physical order of data in a table. Each table can have only one clustered index. It sorts and stores the data rows in the table based on their key values, which affects the way data is physically stored.
Database normalization is the process of organizing data to reduce redundancy and improve data integrity. It involves creating tables, defining relationships, and applying constraints to minimize data duplication and potential anomalies.
Cascading actions define what happens to dependent records when a referenced record is updated or deleted. Options include CASCADE (propagate changes), SET NULL, SET DEFAULT, and NO ACTION.
Data types define the type of data a column can store, its size, and potential constraints. They ensure data integrity, optimize storage, and define how data can be processed and manipulated in the database.
A materialized view is a database object that contains the results of a query. Unlike regular views, it stores the query results physically. Syntax varies by database system, but generally involves CREATE MATERIALIZED VIEW with a SELECT statement.
Indexes are created using the CREATE INDEX statement. For example: CREATE INDEX idx_last_name ON employees(last_name). Indexes improve query performance by allowing faster data retrieval. They can be unique or non-unique and can be created on one or multiple columns.
The UNIQUE constraint ensures that all values in a column are different. Unlike PRIMARY KEY, a table can have multiple UNIQUE constraints. Example: CREATE TABLE users (id INT, email VARCHAR(100) UNIQUE).
The DEFAULT constraint provides a default value for a column when no value is specified. Example: CREATE TABLE products (id INT, price DECIMAL DEFAULT 0.00), which sets the default price to 0 if not explicitly provided.
Table inheritance allows creating a new table based on an existing table, inheriting its columns and characteristics. This is supported differently across database systems, with PostgreSQL providing direct support for table inheritance.
DDL (Data Definition Language) is a subset of SQL used to define and manage database structures. The main DDL commands are CREATE, ALTER, DROP, and TRUNCATE, which are used to create, modify, delete, and remove database objects like tables, indexes, and schemas.
The CREATE TABLE statement is used to create a new table in a database. It specifies the table name, column names, data types, and optional constraints. For example: CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(100), salary DECIMAL(10,2)).
ALTER TABLE is used to modify an existing table structure. Common operations include adding, modifying, or dropping columns, adding or removing constraints. For example: ALTER TABLE employees ADD COLUMN email VARCHAR(100), or ALTER TABLE employees DROP COLUMN phone_number.
DROP removes the entire table structure and data, while TRUNCATE removes all rows from a table but keeps the table structure intact. DROP is a DDL command that deletes the table, TRUNCATE quickly removes all data without logging individual row deletions.
Table partitioning divides large tables into smaller, more manageable pieces. The syntax varies by database system. It allows improving query performance and management of large datasets by splitting them into logical segments.
Table constraints are rules enforced on data columns to maintain data integrity. Common constraints include PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT. They define rules for data that can be inserted into a table.
DROP TABLE completely removes a table and all its data from the database. For example: DROP TABLE employees. This command permanently deletes the table structure and all associated data, and cannot be undone unless you have a backup.
Temporary tables are created using CREATE TEMPORARY TABLE or CREATE TEMP TABLE. They exist only for the duration of a session. Example: CREATE TEMPORARY TABLE temp_sales (product_id INT, total_sales DECIMAL).
Auto-increment columns automatically generate unique numeric values when a new record is inserted. Syntax varies by database: MySQL uses AUTO_INCREMENT, SQL Server uses IDENTITY, PostgreSQL uses SERIAL.
A sequence is a database object that generates a series of numeric values. Example: CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1. It can be used to generate unique identifier values for tables.
Computed columns are columns whose values are calculated dynamically from other columns in the table. Example: total_price DECIMAL GENERATED ALWAYS AS (quantity * unit_price) STORED.
