← Back to Home

SQL Keywords & Usage Cheatsheet

17 Sep 2024

1. Data Query Language (DQL)

Keyword Usage
SELECT Retrieves data from a table.
FROM Specifies the table to query.
WHERE Filters records based on conditions.
GROUP BY Groups rows that have the same values.
HAVING Filters grouped data.
ORDER BY Sorts the results in ascending (ASC) or descending (DESC) order.
LIMIT Restricts the number of rows returned.
OFFSET Skips a specific number of rows.
DISTINCT Returns unique records.

Example

SELECT DISTINCT category, COUNT(*) 
FROM products 
WHERE price > 100 
GROUP BY category 
HAVING COUNT(*) > 5 
ORDER BY COUNT(*) DESC 
LIMIT 10 OFFSET 5;

2. Data Definition Language (DDL)

Keyword Usage
CREATE Creates a new database, table, index, or view.
DROP Deletes a database, table, or view.
ALTER Modifies an existing table (add/drop columns).
TRUNCATE Removes all records from a table but keeps structure.

Example

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100) UNIQUE
);
ALTER TABLE users ADD COLUMN age INT;
DROP TABLE users;

3. Data Manipulation Language (DML)

Keyword Usage
INSERT Adds new records to a table.
UPDATE Modifies existing records.
DELETE Removes records from a table.
MERGE Merges records from two tables.

Example

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
UPDATE users SET age = 30 WHERE name = 'Alice';
DELETE FROM users WHERE age < 18;

4. Data Control Language (DCL)

Keyword Usage
GRANT Gives privileges to a user.
REVOKE Removes privileges from a user.

Example

GRANT SELECT, INSERT ON users TO user1;
REVOKE INSERT ON users FROM user1;

5. Transaction Control Language (TCL)

Keyword Usage
COMMIT Saves the current transaction.
ROLLBACK Undoes the last transaction.
SAVEPOINT Creates a checkpoint in a transaction.

Example

BEGIN;
UPDATE users SET age = 25 WHERE name = 'Alice';
SAVEPOINT before_age_update;
ROLLBACK TO before_age_update;
COMMIT;

6. User Management & Permissions

Keyword Usage
CREATE USER Creates a new database user.
ALTER USER Modifies an existing user.
DROP USER Deletes a user from the database.
GRANT Assigns privileges to a user.
REVOKE Removes privileges from a user.

Example

CREATE USER admin WITH PASSWORD 'securepass';
ALTER USER admin WITH SUPERUSER;
DROP USER admin;
GRANT ALL PRIVILEGES ON DATABASE mydb TO admin;
REVOKE INSERT ON users FROM admin;

7. Joins & Subqueries

Type Usage
INNER JOIN Returns matching records between two tables.
LEFT JOIN Returns all records from the left table, plus matches.
RIGHT JOIN Returns all records from the right table, plus matches.
FULL JOIN Returns all records from both tables.
CROSS JOIN Returns the Cartesian product of both tables.
SELF JOIN Joins a table with itself.
SUBQUERY A query inside another query.

Example

SELECT orders.id, customers.name 
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;

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

SELECT A.name AS Employee, B.name AS Manager 
FROM employees A 
INNER JOIN employees B ON A.manager_id = B.id;

Related Posts