In this guide, we explain 150+ SQL commands in simple words, covering everything from basic queries to advanced functions for 2026. We cover almost every SQL command that exists in one single place, so you never have to go search for anything anywhere else. If you master these 150 commands, you will become an SQL expert.
150+ SQL Commands Index
SQL Commands for Data Query
SQL commands for data query are used to fetch information from our database. They allow us to look at specific data, sort it, and filter it. We use these SQL commands to turn raw data into meaningful insights for reports.
SELECT
This command retrieves data from one or more tables. It specifies which columns we want to see. We use it to view information without changing the database. It returns a result set with the requested data.
Example: SELECT first_name, last_name FROM customers
DISTINCT
This command removes duplicate rows from the results. It ensures every value shown is unique. We use it when we want to see a list of unique items only.
Example: SELECT DISTINCT country FROM customers
WHERE
This command filters records based on a specific condition. It only returns rows that meet the criteria. We use it to find specific data that matches our needs.
Example: SELECT * FROM orders WHERE status = 'shipped'
AND
This command combines multiple conditions in a WHERE clause. It requires all conditions to be true for a row to appear. We use it to make our search more specific.
Example: SELECT * FROM products WHERE price > 50 AND stock > 10
OR
This command combines conditions where at least one must be true. It expands our search results. We use it to find rows that match either of two criteria.
Example: SELECT * FROM users WHERE city = 'London' OR city = 'Paris'
NOT
This command reverses the result of a condition. It shows rows that do not match the criteria. We use it to exclude specific data from our results.
Example: SELECT * FROM items WHERE NOT category = 'Books'
BETWEEN
This command selects values within a given range. It works with numbers, dates, and text. We use it to filter results that fall between two specific values.
Example: SELECT * FROM sales WHERE sale_date BETWEEN '2025-01-01' AND '2025-12-31'
LIKE
This command searches for a specific pattern in a column. It uses wildcards like percent signs for flexibility. We use it when we only know part of the text we are looking for.
Example: SELECT * FROM employees WHERE name LIKE 'A%'
IN
This command allows us to specify multiple possible values in a WHERE clause. It is a shorthand for multiple OR conditions. We use it to filter for items in a specific list.
Example: SELECT * FROM orders WHERE region IN ('North', 'South', 'East')
IS NULL
This command checks for empty values in a column. It finds rows where data is missing. We use it to identify incomplete records.
Example: SELECT * FROM students WHERE phone_number IS NULL
IS NOT NULL
This command ensures a column contains actual data. It filters out any empty values. We use it when we only want to see complete records.
Example: SELECT * FROM orders WHERE customer_id IS NOT NULL
AS
This command gives a temporary name to a column or table. It makes output more readable. We use it to rename results for better understanding.
Example: SELECT first_name AS Name FROM staff
ORDER BY
This command sorts the result set in ascending or descending order. It arranges data based on one or more columns. We use it to organize our data for easier reading.
Example: SELECT * FROM products ORDER BY price DESC
LIMIT
This command restricts the number of rows returned. It is useful for large tables. We use it to see a small sample of the data.
Example: SELECT * FROM visitors LIMIT 10
OFFSET
This command skips a specific number of rows before returning the rest. It works with LIMIT for pagination. We use it to show pages of data one after another.
Example: SELECT * FROM posts LIMIT 5 OFFSET 10
GROUP BY
This command groups rows that have the same values. It is often used with aggregate functions. We use it to summarize data by categories.
Example: SELECT category, COUNT(*) FROM items GROUP BY category
HAVING
This command filters groups created by GROUP BY. It works like WHERE but for aggregated data. We use it to filter results based on a calculation.
Example: SELECT department, SUM(salary) FROM staff GROUP BY department HAVING SUM(salary) > 50000
WITH
This command creates a temporary result set known as a Common Table Expression. It makes complex queries easier to read. We use it to organize large queries into smaller parts.
Example: WITH HighPrices AS (SELECT * FROM products WHERE price > 100) SELECT * FROM HighPrices
SQL Commands for Table Management
SQL commands for table management allow us to define the structure of our data storage. We use them to create tables, change their design, or remove them entirely when no longer needed.
CREATE TABLE
This command creates a new table in the database. It defines column names and data types. We use it when we need a new place to store our data.
Example: CREATE TABLE users (id INT, username VARCHAR(50))
ALTER TABLE
This command adds, deletes, or modifies columns in an existing table. It changes the table structure. We use it when we need to update how our data looks.
Example: ALTER TABLE users ADD email VARCHAR(100)
DROP TABLE
This command deletes an entire table and its data. The action is permanent. We use it only when we are sure we no longer need the table.
Example: DROP TABLE old_records
TRUNCATE TABLE
This command removes all rows from a table but keeps the structure. It is faster than DELETE. We use it to clear all data quickly.
Example: TRUNCATE TABLE temp_data
RENAME TABLE
This command changes the name of an existing table. It does not affect the data inside. We use it to give a table a more descriptive name.
Example: RENAME TABLE clients TO customers
SQL Commands for Data Modification
These commands let us change the actual information stored in the database. We use them to add new records, update existing ones, or remove data we do not need.
INSERT INTO
This command adds new rows of data to a table. It specifies which columns to fill. We use it to add new information to our database.
Example: INSERT INTO customers (name, age) VALUES ('John', 30)
UPDATE
This command changes existing data in a table. It uses the WHERE clause to choose rows. We use it to fix errors or update old details.
Example: UPDATE employees SET salary = 60000 WHERE id = 5
DELETE
This command removes rows from a table. It also uses a WHERE clause to select data. We use it to erase records we no longer need.
Example: DELETE FROM orders WHERE order_date < '2020-01-01'
MERGE
This command performs insert, update, or delete actions in a single statement. It matches data from a source to a target. We use it to synchronize two tables.
Example: MERGE INTO target USING source ON target.id = source.id WHEN MATCHED THEN UPDATE SET target.value = source.value
REPLACE
This command works like INSERT but deletes the old row if a duplicate key exists. It ensures no duplicate primary keys. We use it to overwrite existing data safely.
Example: REPLACE INTO users (id, name) VALUES (1, 'Mike')
SQL Commands for Indexes
These commands help us speed up our database queries. By creating indexes, we can find data much faster without scanning every single row in the table.
CREATE INDEX
This command creates an index on a table column. It speeds up data retrieval. We use it to make searches run much faster.
Example: CREATE INDEX idx_email ON users (email)
DROP INDEX
This command removes an index from a table. It slows down searches but saves space. We use it when an index is no longer helpful.
Example: DROP INDEX idx_email ON users
CREATE UNIQUE INDEX
This command ensures that the indexed column contains only unique values. It prevents duplicate entries. We use it to enforce data integrity.
Example: CREATE UNIQUE INDEX idx_id ON products (product_id)
SQL Commands for Constraints
SQL commands for constraints enforce rules on our data to ensure accuracy. They prevent invalid data from being entered into our tables.
PRIMARY KEY
This command uniquely identifies each record in a table. It does not allow NULL values. We use it to ensure every row is distinct and identifiable.
Example: CREATE TABLE orders (order_id INT PRIMARY KEY, amount DECIMAL)
FOREIGN KEY
This command links two tables together. It ensures data in one table matches data in another. We use it to maintain relationships between data.
Example: CREATE TABLE orders (user_id INT, FOREIGN KEY (user_id) REFERENCES users(id))
UNIQUE
This command ensures all values in a column are different. It allows one NULL value but prevents duplicates. We use it to stop repeated data.
Example: CREATE TABLE members (email VARCHAR(100) UNIQUE)
CHECK
This command enforces a specific condition on a column. It ensures values meet a rule. We use it to validate data before it enters the database.
Example: CREATE TABLE people (age INT CHECK (age >= 18))
DEFAULT
This command sets a default value for a column. It is used if no value is specified during an insert. We use it to handle missing data automatically.
Example: CREATE TABLE logs (created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)
SQL Commands for Views
SQL commands for views allow us to save complex queries as virtual tables. This makes it easier to access frequently needed data without rewriting the same query every time.
CREATE VIEW
This command creates a virtual table based on the result of a query. It does not store data physically. We use it to save complex queries for future use.
Example: CREATE VIEW customer_view AS SELECT name, email FROM customers
DROP VIEW
This command removes a virtual table from the database. It does not affect the underlying data. We use it when we no longer need the saved query.
Example: DROP VIEW customer_view
SQL Commands for Joins
These commands allow us to combine data from two or more different tables. This is essential for analysing related data that is stored separately.
INNER JOIN
This command selects records that have matching values in both tables. It combines rows from two tables. We use it when we only want related data.
Example: SELECT orders.id, customers.name FROM orders INNER JOIN customers ON orders.customer_id = customers.id
LEFT JOIN
This command returns all records from the left table and matching records from the right. It shows NULL for non-matches on the right. We use it to see all data from the first table.
Example: SELECT students.name, courses.title FROM students LEFT JOIN courses ON students.course_id = courses.id
RIGHT JOIN
This command returns all records from the right table and matching records from the left. It shows NULL for non-matches on the left. We use it when the second table is the main focus.
Example: SELECT employees.name, departments.title FROM employees RIGHT JOIN departments ON employees.dept_id = departments.id
FULL JOIN
This command returns all records when there is a match in either left or right table. It combines everything from both. We use it to see a complete picture of all data.
Example: SELECT * FROM table1 FULL JOIN table2 ON table1.id = table2.id
CROSS JOIN
This command returns the Cartesian product of the two tables. It combines every row of the first table with every row of the second. We use it to generate all possible combinations.
Example: SELECT * FROM colors CROSS JOIN sizes
SELF JOIN
This command joins a table to itself. It treats the table as two different tables. We use it to compare rows within the same table.
Example: SELECT A.name AS Employee, B.name AS Manager FROM staff A JOIN staff B ON A.manager_id = B.id
SQL Commands for Set Operations
SQL commands for set operations allow us to combine the results of two or more SELECT statements. This is useful for comparing or merging lists of data.
UNION
This command combines the result sets of two or more SELECT statements. It removes duplicate rows between them. We use it to stack similar data from different tables.
Example: SELECT name FROM customers UNION SELECT name FROM suppliers
UNION ALL
This command combines result sets but keeps duplicate rows. It is faster than UNION. We use it when we want to keep every single record.
Example: SELECT city FROM users UNION ALL SELECT city FROM visitors
INTERSECT
This command returns the rows that exist in both result sets. It shows only the common data. We use it to find overlaps between two queries.
Example: SELECT email FROM list_a INTERSECT SELECT email FROM list_b
EXCEPT
This command returns rows from the first query that are not in the second query. It subtracts one set of data from another. We use it to find unique items in the first set.
Example: SELECT id FROM products EXCEPT SELECT id FROM archived_products
SQL Commands for Transactions
These commands ensure that a group of operations are treated as a single unit. We use them to maintain data integrity when making multiple changes.
BEGIN
This command starts a new transaction block. It ensures the following commands are treated as a single unit. We use it when we have a series of related updates.
Example: BEGIN TRANSACTION
COMMIT
This command saves all changes made in the current transaction. It makes the changes permanent. We use it when we are happy with the results of our work.
Example: COMMIT
ROLLBACK
This command undoes changes made in the current transaction. It restores the database to the state before the transaction began. We use it to cancel changes if an error occurs.
Example: ROLLBACK
SAVEPOINT
This command sets a marker inside a transaction. It allows us to roll back to that specific point. We use it to create safe spots during complex processes.
Example: SAVEPOINT my_savepoint
RELEASE SAVEPOINT
This command removes a savepoint within a transaction. It makes the savepoint no longer available. We use it to clean up markers we no longer need.
Example: RELEASE SAVEPOINT my_savepoint
SQL Commands for Aggregation
Aggregation perform calculations on multiple rows and returns a single result. We use them to summarise data and get statistical insights.
COUNT
This command returns the number of rows that match a specified criterion. It counts the items. We use it to know how many records exist.
Example: SELECT COUNT(*) FROM orders
SUM
This command calculates the total sum of a numeric column. It adds values together. We use it to get totals like sales figures.
Example: SELECT SUM(price) FROM cart_items
AVG
This command calculates the average value of a numeric column. It finds the middle value. We use it to analyze typical amounts.
Example: SELECT AVG(score) FROM reviews
MIN
This command returns the smallest value in a column. It finds the lowest number or earliest date. We use it to identify extremes.
Example: SELECT MIN(price) FROM products
MAX
This command returns the largest value in a column. It finds the highest number or latest date. We use it to identify top performance.
Example: SELECT MAX(salary) FROM employees
SQL Commands for Access Control
We use these commands to secure our data and decide who can see or change information.
GRANT
This command gives specific privileges to a user. It allows them to perform actions on the database. We use it to control who can see or change data.
Example: GRANT SELECT, INSERT ON customers TO user_jane
REVOKE
This command removes privileges from a user. It takes away their access rights. We use it when we need to restrict access.
Example: REVOKE INSERT ON customers FROM user_jane
DENY
This command specifically blocks a user from a privilege. It overrides other grants. We use it to ensure a user cannot perform an action.
Example: DENY DELETE ON sales TO trainee_user
SQL Commands for User Management
SQL commands for user management allow us to create and manage the accounts that can access the database. We use them to set up login credentials and control identities.
CREATE USER
This command creates a new database user account. It sets up the login credentials. We use it to allow new people to access the system.
Example: CREATE USER 'tom' IDENTIFIED BY 'password123'
DROP USER
This command deletes a user account. It removes their access completely. We use it when a user leaves the organization.
Example: DROP USER 'tom'
ALTER USER
This command modifies user account details. It can change passwords or names. We use it to maintain user accounts.
Example: ALTER USER 'tom' IDENTIFIED BY 'newpassword456'
SQL Commands for Schema and Database
SQL commands for schema and database management help us organize our overall database environment. We use them to create databases, switch between them, and inspect their structure.
CREATE DATABASE
This command creates a new database. It sets up a new container for data. We use it when we start a completely new project.
Example: CREATE DATABASE shop_data
DROP DATABASE
This command deletes an entire database permanently. It removes all tables and data inside. We use it with extreme caution.
Example: DROP DATABASE shop_data
USE
This command selects a specific database to work with. It sets the active context for queries. We use it to tell the system which database to use.
Example: USE shop_data
SHOW DATABASES
This command lists all databases on the server. It shows us what is available. We use it to see our options.
Example: SHOW DATABASES
SHOW TABLES
This command lists all tables in the current database. It shows the structure of the data. We use it to navigate the database.
Example: SHOW TABLES
DESCRIBE
This command shows the structure of a specific table. It lists columns, data types, and constraints. We use it to understand a table design.
Example: DESCRIBE customers
SHOW COLUMNS
This command displays information about the columns in a table. It is similar to DESCRIBE. We use it to see field details.
Example: SHOW COLUMNS FROM customers
SQL Commands for Data Types
SQL commands for data types define what kind of information can be stored in each column. We use them to ensure our data is stored in the correct format.
INT
This command defines a column for whole numbers. It stores integers without decimals. We use it for IDs or counts.
Example: CREATE TABLE items (id INT, quantity INT)
VARCHAR
This command defines a column for variable-length text. It stores letters and numbers up to a limit. We use it for names and addresses.
Example: CREATE TABLE users (username VARCHAR(50))
DATE
This command defines a column for date values. It stores year, month, and day. We use it for birthdays or events.
Example: CREATE TABLE events (event_date DATE)
TIMESTAMP
This command defines a column for date and time values. It tracks precise moments. We use it for logging when things happen.
Example: CREATE TABLE logs (created_at TIMESTAMP)
BOOLEAN
This command defines a column for true or false values. It stores binary states. We use it for switches or flags.
Example: CREATE TABLE settings (is_active BOOLEAN)
SQL Commands for Utility
SQL commands for utility help us analyze and optimize how our queries run. We use them to understand the performance of our database.
EXPLAIN
This command shows the execution plan for a query. It explains how the database will retrieve data. We use it to analyze and improve query performance.
Example: EXPLAIN SELECT * FROM orders
CALL
This command executes a stored procedure. It runs a predefined set of SQL commands. We use it to perform complex tasks easily.
Example: CALL update_monthly_stats()
SQL Commands for Window and Analytic Functions
Window functions perform calculations across a set of table rows related to the current row. They are essential for complex analytics and reporting.
ROW_NUMBER
This command assigns a unique sequential integer to rows within a partition. It numbers rows from 1 without gaps. We use it for ranking and pagination.
Example: SELECT ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM employees
RANK
This command calculates a rank for each row with gaps for ties. It assigns the same rank to equal values. We use it when ties should skip ranks.
Example: SELECT RANK() OVER (ORDER BY score DESC) AS rank FROM students
DENSE_RANK
This command calculates a rank for each row without gaps. It assigns consecutive ranks even with ties. We use it when ties should not skip numbers.
Example: SELECT DENSE_RANK() OVER (ORDER BY points DESC) AS rank FROM players
LAG
This command accesses data from a previous row in the result set. It lets us compare current row with past rows. We use it for calculating differences from previous periods.
Example: SELECT sales, LAG(sales) OVER (ORDER BY month) AS prev_month_sales FROM monthly_report
LEAD
This command accesses data from a following row in the result set. It lets us look ahead to future rows. We use it for comparing with upcoming periods.
Example: SELECT price, LEAD(price) OVER (ORDER BY date) AS next_price FROM stock_prices
NTILE
This command distributes rows into a specified number of roughly equal groups. It assigns a bucket number to each row. We use it for creating percentile-based groupings.
Example: SELECT NTILE(4) OVER (ORDER BY total_sales) AS quartile FROM sales_data
FIRST_VALUE
This command returns the first value in an ordered partition. It lets us access the first row’s value. We use it for comparisons against the earliest record.
Example: SELECT value, FIRST_VALUE(value) OVER (ORDER BY date) AS first_value FROM measurements
LAST_VALUE
This command returns the last value in an ordered partition. It lets us access the final row’s value. We use it for comparisons against the most recent record.
Example: SELECT amount, LAST_VALUE(amount) OVER (ORDER BY transaction_date) AS running_total FROM transactions
OVER
This clause defines the window frame for window functions. It specifies how rows are grouped and ordered. We use it to control the scope of window function calculations.
Example: SELECT department, salary, AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary FROM employees
PARTITION BY
This clause divides rows into partitions to which window functions are applied. It creates groups like GROUP BY but doesn’t collapse rows. We use it to calculate statistics within groups.
Example: SELECT category, amount, SUM(amount) OVER (PARTITION BY category) AS category_total FROM sales
SQL Commands for JSON Operations
JSON commands handle semi-structured data stored in JSON format. They are crucial for modern applications dealing with flexible data schemas.
JSON_EXTRACT
This command retrieves data from a JSON document using a path expression. It returns the matched values. We use it to pull specific values from JSON columns.
Example: SELECT JSON_EXTRACT(data, '$.name') AS name FROM users
JSON_VALUE
This command extracts a scalar value from a JSON string. It returns a single value like text or number. We use it when we need a simple value from JSON.
Example: SELECT JSON_VALUE(info, '$.age') AS age FROM customers
JSON_QUERY
This command extracts an object or array from a JSON string. It returns JSON fragments rather than scalar values. We use it to retrieve complex JSON structures.
Example: SELECT JSON_QUERY(details, '$.address') AS address FROM orders
JSON_TABLE
This command converts JSON data into relational rows and columns. It lets us query JSON like a regular table. We use it for reporting on JSON data.
Example: SELECT * FROM JSON_TABLE(json_data, '$.items[*]' COLUMNS (id INT PATH '$.id', name VARCHAR PATH '$.name')) AS jt
JSON_SET
This command inserts or updates values in a JSON document. It modifies existing values or adds new ones. We use it to update JSON data.
Example: UPDATE products SET attributes = JSON_SET(attributes, '$.price', 29.99) WHERE id = 1
JSON_ARRAY
This command creates a JSON array from a list of values. It constructs array structures. We use it to build JSON arrays from database values.
Example: SELECT JSON_ARRAY(name, email) AS contact_info FROM users
JSON_OBJECT
This command creates a JSON object from key-value pairs. It constructs object structures. We use it to build JSON objects from database columns.
Example: SELECT JSON_OBJECT('name', name, 'age', age) AS user_info FROM customers
SQL Commands for Date and Time
Date and time commands handle temporal data essential for almost every application. They help us work with dates, times, and intervals.
NOW
This command returns the current date and time. It provides the moment the query executes. We use it for timestamping records.
Example: INSERT INTO logs (message, created_at) VALUES ('User login', NOW())
CURRENT_DATE
This command returns the current date without time. It provides just the calendar date. We use it for date-based filtering.
Example: SELECT * FROM events WHERE event_date = CURRENT_DATE
CURRENT_TIMESTAMP
This command returns the current date and time. It is similar to NOW but follows SQL standards. We use it for standardized timestamp handling.
Example: UPDATE sessions SET last_activity = CURRENT_TIMESTAMP WHERE user_id = 5
DATE_ADD
This command adds a time interval to a date. It calculates future dates. We use it for finding dates in the future.
Example: SELECT DATE_ADD(order_date, INTERVAL 30 DAY) AS due_date FROM orders
DATE_SUB
This command subtracts a time interval from a date. It calculates past dates. We use it for finding dates in the past.
Example: SELECT DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) AS week_ago
DATEDIFF
This command calculates the difference between two dates. It returns the number of days between dates. We use it for date arithmetic.
Example: SELECT DATEDIFF(return_date, borrow_date) AS days_borrowed FROM library_loans
EXTRACT
This command extracts a specific part from a date or time. It returns year, month, day, etc. We use it for getting date components.
Example: SELECT EXTRACT(YEAR FROM birth_date) AS birth_year FROM people
DATE_TRUNC
This command truncates a date to the specified precision. It sets lower precision parts to default values. We use it for grouping by time periods.
Example: SELECT DATE_TRUNC('month', order_date) AS order_month, COUNT(*) FROM orders GROUP BY 1
SQL Commands for String Manipulation
String commands manipulate text data crucial for formatting and searching. They help us clean, format, and extract information from text.
CONCAT
This command joins two or more strings together. It combines text from multiple columns. We use it to build full names or addresses.
Example: SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers
SUBSTRING
This command extracts a portion of a string. It returns a specific length of text starting at a position. We use it to get parts of text values.
Example: SELECT SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS username FROM users
TRIM
This command removes leading and trailing characters from a string. It cleans up extra spaces. We use it for sanitizing input data.
Example: SELECT TRIM(product_name) AS clean_name FROM products
REPLACE
This command replaces all occurrences of a substring in a string. It substitutes text. We use it for correcting or updating text values.
Example: SELECT REPLACE(phone_number, '-', '') AS clean_phone FROM contacts
LOWER
This command converts a string to lowercase. It standardizes text case. We use it for case-insensitive comparisons.
Example: SELECT * FROM users WHERE LOWER(email) = '[email protected]'
UPPER
This command converts a string to uppercase. It standardizes text case. We use it for emphasizing text or case-insensitive comparisons.
Example: SELECT UPPER(product_code) AS code FROM inventory
LENGTH
This command returns the length of a string in characters. It measures text size. We use it for validation or formatting.
Example: SELECT username, LENGTH(username) AS name_length FROM accounts
POSITION
This command finds the position of a substring within a string. It returns the starting index. We use it for locating text within values.
Example: SELECT POSITION(' ' IN full_address) AS space_position FROM addresses
SQL Commands for Recursive Queries
Recursive commands handle hierarchical and tree-structured data. They are essential for organizational charts, categories, and threaded discussions.
WITH RECURSIVE
This command creates a recursive Common Table Expression (CTE). It queries hierarchical data by referencing itself. We use it for tree structures and parent-child relationships.
Example: WITH RECURSIVE category_tree AS (SELECT id, name, parent_id FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.name, c.parent_id FROM categories c JOIN category_tree ct ON c.parent_id = ct.id) SELECT * FROM category_tree
SQL Commands for Temporary Tables
Temporary table commands create session-specific tables for intermediate results. They are useful for complex queries and data processing.
CREATE TEMPORARY TABLE
This command creates a table that exists only for the current session. It stores temporary data. We use it for intermediate results in complex queries.
Example: CREATE TEMPORARY TABLE temp_results AS SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id
DROP TEMPORARY TABLE
This command removes a temporary table from the current session. It cleans up temporary data. We use it when we no longer need the temporary table.
Example: DROP TEMPORARY TABLE IF EXISTS temp_results
ON COMMIT DELETE ROWS
This option specifies that temporary table data should be deleted at transaction commit. It clears data after each transaction. We use it for transaction-specific temporary data.
Example: CREATE TEMPORARY TABLE temp_data (id INT, value VARCHAR(50)) ON COMMIT DELETE ROWS
ON COMMIT PRESERVE ROWS
This option specifies that temporary table data should be preserved at transaction commit. It keeps data across transactions in the session. We use it for session-specific temporary data.
Example: CREATE TEMPORARY TABLE session_cache (key VARCHAR(50), value TEXT) ON COMMIT PRESERVE ROWS
SQL Commands for Triggers
Trigger commands automatically execute code in response to database events. They help maintain data integrity and implement business logic.
CREATE TRIGGER
This command creates a new trigger that executes in response to database events. It associates code with table operations. We use it for automatic data validation or updates.
Example: CREATE TRIGGER update_inventory BEFORE INSERT ON order_items FOR EACH ROW UPDATE products SET stock = stock - NEW.quantity WHERE id = NEW.product_id
DROP TRIGGER
This command removes a trigger from a table. It deletes the automatic event handler. We use it when we no longer need the trigger.
Example: DROP TRIGGER IF EXISTS update_inventory
BEFORE
This timing specifies that the trigger executes before the triggering event. It lets us modify values before they are saved. We use it for data validation.
Example: CREATE TRIGGER validate_data BEFORE INSERT ON users FOR EACH ROW IF NEW.age < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age cannot be negative'
AFTER
This timing specifies that the trigger executes after the triggering event. It lets us perform actions after data changes. We use it for logging or cascading updates.
Example: CREATE TRIGGER log_changes AFTER UPDATE ON products FOR EACH ROW INSERT INTO audit_log (table_name, record_id, change_time, old_value, new_value) VALUES ('products', NEW.id, NOW(), OLD.price, NEW.price)
INSTEAD OF
This timing specifies that the trigger executes instead of the triggering event. It is typically used on views. We use it to make views updatable.
Example: CREATE TRIGGER instead_of_insert INSTEAD OF INSERT ON customer_view FOR EACH ROW INSERT INTO customers (name, email) VALUES (NEW.name, NEW.email)
SQL Commands for Procedures and Functions
Procedure and function commands encapsulate reusable logic in the database. They help us create modular, maintainable database code.
CREATE PROCEDURE
This command creates a stored procedure that can accept parameters and execute SQL statements. It encapsulates reusable logic. We use it for complex business logic.
Example: CREATE PROCEDURE GetCustomerOrders(IN customer_id INT) BEGIN SELECT * FROM orders WHERE customer_id = customer_id; END
CREATE FUNCTION
This command creates a user-defined function that returns a value. It encapsulates reusable calculations. We use it for data transformation and validation.
Example: CREATE FUNCTION CalculateTax(amount DECIMAL(10,2)) RETURNS DECIMAL(10,2) BEGIN RETURN amount * 0.08; END
RETURN
This command specifies the value that a function returns. It sends the result back to the caller. We use it to output function results.
Example: RETURN calculated_value
DECLARE
This command declares variables within stored procedures or functions. It creates storage for intermediate values. We use it for procedural logic.
Example: DECLARE total_amount DECIMAL(10,2) DEFAULT 0
BEGIN
This command starts a block of SQL statements in stored procedures or functions. It groups multiple statements. We use it to define procedural blocks.
Example: BEGIN -- SQL statements END
END
This command ends a block of SQL statements started with BEGIN. It completes the procedural block. We use it to close statement groups.
Example: END
IF
This command executes SQL statements conditionally based on a boolean expression. It creates branching logic. We use it for conditional processing.
Example: IF NEW.quantity < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Quantity cannot be negative'
WHILE
This command repeats SQL statements while a condition is true. It creates looping logic. We use it for iterative processing.
Example: WHILE counter < 10 DO SET counter = counter + 1; END WHILE
LOOP
This command creates an unconditional loop that repeats until explicitly exited. It provides another looping mechanism. We use it for custom iteration.
Example: label: LOOP SET counter = counter + 1; IF counter >= 10 THEN LEAVE label; END IF; END LOOP
SQL Commands for Backup and Restore
Backup and restore commands protect and recover our data. They are essential for disaster recovery and data migration.
BACKUP DATABASE
This command creates a full backup of a database. It copies the entire database for safekeeping. We use it for regular data protection.
Example: BACKUP DATABASE my_database TO DISK = 'C:\backups\my_database.bak'
RESTORE DATABASE
This command restores a database from a backup file. It recovers data from backups. We use it for disaster recovery or migration.
Example: RESTORE DATABASE my_database FROM DISK = 'C:\backups\my_database.bak'
DUMP
This command exports database data and structure to a text file. It creates a portable backup format. We use it for logical backups and transfers.
Example: mysqldump -u username -p database_name > backup_file.sql
LOAD DATA
This command imports data from a file into a database table. It loads data quickly from external sources. We use it for bulk data imports.
Example: LOAD DATA INFILE 'data.csv' INTO TABLE customers FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
SQL Commands for Performance Optimization
Performance commands help maintain and improve database efficiency. They are crucial for keeping queries fast and databases healthy.
ANALYZE
This command collects statistics about table contents for the query optimizer. It helps the database plan better queries. We use it after significant data changes.
Example: ANALYZE VERBOSE customers
VACUUM
This command reclaims storage occupied by dead tuples in PostgreSQL. It cleans up disk space. We use it to maintain database performance.
Example: VACUUM VERBOSE ANALYZE
OPTIMIZE TABLE
This command reorganizes table storage and can improve performance. It rebuilds tables to reduce fragmentation. We use it to optimize table storage.
Example: OPTIMIZE TABLE orders
REINDEX
This command rebuilds indexes on a table or database. It refreshes index structures for better performance. We use it when indexes become fragmented.
Example: REINDEX TABLE customers
SQL Commands for Locking and Concurrency
Locking commands control concurrent access to data. They help manage multiple users working with the same data simultaneously.
LOCK TABLE
This command explicitly locks a table in a specific mode. It controls access to table data. We use it for complex operations requiring exclusive access.
Example: LOCK TABLE orders IN EXCLUSIVE MODE
UNLOCK TABLE
This command releases table locks acquired by the current session. It frees up locked resources. We use it after finishing exclusive operations.
Example: UNLOCK TABLES
SELECT FOR UPDATE
This command locks selected rows to prevent concurrent modifications. It ensures data doesn’t change while working with it. We use it for consistent updates.
Example: SELECT * FROM products WHERE id = 1 FOR UPDATE
SET TRANSACTION
This command sets characteristics of the current transaction. It controls transaction isolation levels and access modes. We use it for fine-grained transaction control.
Example: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SQL Commands for Partitioning and Storage
Partitioning commands split large tables into smaller, more manageable pieces. They improve performance and manageability of big data.
CREATE PARTITION
This command creates a partition of a partitioned table. It defines a segment of the table’s data. We use it to implement partitioning strategies.
Example: CREATE TABLE orders_2023 PARTITION OF orders FOR VALUES FROM ('2023-01-01') TO ('2024-01-01')
ATTACH PARTITION
This command attaches an existing table as a partition to a partitioned table. It incorporates a table into the partition structure. We use it to add new partitions.
Example: ALTER TABLE orders ATTACH PARTITION orders_2024 FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')
DETACH PARTITION
This command detaches a partition from a partitioned table. It converts a partition into a standalone table. We use it to remove or archive old partitions.
Example: ALTER TABLE orders DETACH PARTITION orders_2022
SQL Commands for Security and Roles
Security commands manage access control and permissions. They help us protect data and manage database users effectively.
CREATE ROLE
This command creates a new database role. It defines a group or user entity. We use it to organize permissions and users.
Example: CREATE ROLE read_only
SET ROLE
This command changes the current session’s role. It switches the active security context. We use it to temporarily assume different permissions.
Example: SET ROLE admin
ALTER ROLE
This command modifies properties of an existing role. It changes role attributes like password or options. We use it to update role configurations.
Example: ALTER ROLE analyst WITH LOGIN PASSWORD 'newsecurepassword'
DROP ROLE
This command removes a role from the database. It deletes the role and its permissions. We use it to clean up unused roles.
Example: DROP ROLE IF EXISTS temp_access
SQL Commands for Security and Privileges (Administrative)
We covered basic users and roles, but here are the commands used for managing advanced security features and permissions.
SET PASSWORD
This command changes the password for a user account. It updates login credentials. We use it to enforce security policies when users need to change passwords.
Example: SET PASSWORD FOR 'user_name'@'localhost' = 'new_password'
GRANT PROXY
This command allows one user to act as another user. It sets up a proxy relationship between roles. We use it to manage role delegation or middle-tier connections.
Example: GRANT PROXY ON 'app_user' TO 'middleware_user'
REVOKE PROXY
This command removes the proxy privilege from a user. It stops a user from acting as another. We use it to revoke delegation access.
Example: REVOKE PROXY ON 'app_user' FROM 'middleware_user'
SQL Commands for Geospatial Data
Modern databases like PostgreSQL and MySQL support geographic data. These SQL commands handle map locations and spatial queries.
ST_DISTANCE
This command calculates the distance between two geometry points. It returns the physical distance. We use it for location-based features like “find nearest store”.
Example: SELECT id, ST_DISTANCE(location, ST_Point(1, 1)) AS distance FROM shops
ST_CONTAINS
This command checks if one geometry contains another. It returns true if one shape is fully inside another. We use it to find points within specific zones.
Example: SELECT * FROM zones WHERE ST_CONTAINS(zone_boundary, user_location)
ST_WITHIN
This command checks if a geometry is within another geometry. It is the reverse of CONTAINS. We use it to verify if a point lies inside a specific area.
Example: SELECT * FROM cities WHERE ST_WITHIN(city_center, country_boundary)
ST_INTERSECTS
This command checks if two geometries overlap or touch. It returns true if shapes share any space. We use it to find overlapping regions or boundaries.
Example: SELECT * FROM areas WHERE ST_INTERSECTS(area_a, area_b)
SQL Commands for Full-Text Search
Standard LIKE is slow for searching large text. These SQL commands provide specialized and fast text search capabilities.
MATCH … AGAINST
This command performs a full-text search in MySQL. It looks for natural language or boolean search strings. We use it for fast search engines within database content.
Example: SELECT * FROM articles WHERE MATCH(title, body) AGAINST('database tutorial' IN NATURAL LANGUAGE MODE)
TO_TSVECTOR
This command converts a document into a text search vector in PostgreSQL. It prepares text for searching. We use it to index text for fast full-text queries.
Example: SELECT to_tsvector('english', 'The quick brown fox jumps over the dog')
TO_TSQUERY
This command converts a search query string into a text search query. It prepares the search terms. We use it to match against a TSVECTOR.
Example: SELECT to_tsquery('english', 'fox & !dog')
TS_RANK
This command calculates the relevance of a document to a query. It ranks search results by quality. We use it to order search results from best to worst.
Example: SELECT title, ts_rank(text_search_vector, query) AS rank FROM articles, to_tsquery('cats & dogs') query WHERE text_search_vector @@ query ORDER BY rank DESC
SQL Commands for System and Administration
These commands manage the database server itself, rather than data inside tables.
SHOW VARIABLES
This command displays system variable values for the server. It shows server configuration. We use it to debug settings or check server limits.
Example: SHOW VARIABLES LIKE 'max_connections'
SET GLOBAL
This command sets a global system variable for the server. It changes configuration for all connections. We use it to tune server performance or behavior.
Example: SET GLOBAL max_allowed_packet = 16777216
SHOW STATUS
This command displays status counters for the server. It shows runtime statistics like connections or queries. We use it for monitoring server health.
Example: SHOW STATUS LIKE 'Threads_connected'
SHOW PROCESSLIST
This command lists all currently executing threads and queries. It shows what the server is doing right now. We use it to identify or kill long-running queries.
Example: SHOW PROCESSLIST
KILL
This command terminates a specific connection or query. It stops a running process. We use it to cancel a query that is hanging or causing problems.
Example: KILL 42
SQL Commands for Prepared Statements
These commands are used in programming interfaces to execute the same query efficiently with different parameters.
PREPARE
This command prepares a statement for execution. It creates a template with placeholders. We use it to improve performance or security by separating code from data.
Example: PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?'
EXECUTE
This command runs a prepared statement with specific parameters. It fills in the placeholders. We use it to run the query multiple times safely.
Example: EXECUTE stmt USING 1
DEALLOCATE PREPARE
This command releases a prepared statement. It cleans up the memory used. We use it when we are done with the statement.
Example: DEALLOCATE PREPARE stmt
SQL Commands for Export
These commands help export data or manage session settings.
DESCRIBE
This command (often shortened to DESC) shows the structure of a table. It lists columns, types, and keys. We use it to quickly inspect a table design.
Example: DESCRIBE customers
SHOW CREATE TABLE
This command shows the exact SQL statement needed to recreate a table. It outputs the CREATE TABLE statement with all constraints. We use it to copy table structures.
Example: SHOW CREATE TABLE orders
SQL Quiz Test 2026
Now that you have learned all major SQL Commands, it is a good time to test your knowledge. Try our free 100-question SQL Test 2026 and see how strong your SQL skills really become.
100 SQL MCQ with Answers (SQL Test 2026)
Resources and References:
- MySQL 9.5 Reference Manual The official guide for MySQL developers, covering syntax, functions, and optimization.
- PostgreSQL 18 Documentation Comprehensive documentation for PostgreSQL, including advanced features like JSON and recursive queries.
- SQL Server Transact-SQL Reference (T-SQL) Microsoft’s official reference for SQL Server, detailing all T-SQL commands and functions.
- Oracle Database SQL Language Reference The complete language reference for Oracle Database users.
- SQLite Documentation The official documentation for the lightweight, serverless SQLite database engine.
