SQL Commands for Beginners and Experts

SQL (Structured Query Language) is used to interact with databases. SQL commands can be categorized based on their function and purpose within the database management system. Below is a categorization of the SQL commands mentioned:

1. Data Querying and Retrieval

These commands are used to retrieve and filter data from tables.

SELECT: Retrieves data from a table.
SELECT DISTINCT: Retrieves unique records from a column.
WHERE: Filters records based on conditions.
ORDER BY: Sorts records based on one or more columns.
GROUP BY: Groups rows sharing a property and applies aggregate functions (like COUNT, AVG, etc.).
HAVING: Filters records after grouping.
JOIN: Combines data from two or more tables based on a related column.
UNION: Combines results of two SELECT queries and removes duplicates.
LIMIT / TOP: Limits the number of rows returned by a query.
IN: Filters data based on a list of values.
BETWEEN: Filters data within a specified range.
LIKE: Filters data using pattern matching (with wildcards).
EXISTS: Checks if a subquery returns any rows.
ANY / ALL: Compares a value against a set of values from a subquery.
RANK / DENSE_RANK / ROW_NUMBER: Ranking and row numbering functions.

2. Data Manipulation Language (DML)

These commands are used to modify data within tables.

INSERT INTO: Adds new records into a table.
UPDATE: Modifies existing records in a table.
DELETE: Removes records from a table.
TRUNCATE TABLE: Deletes all records in a table but does not remove the table structure.

3. Database Definition (DDL)

These commands define or alter the structure of database objects such as tables and views.

CREATE TABLE: Creates a new table in the database.
ALTER TABLE: Modifies an existing table (e.g., add/remove columns, change column types).
DROP TABLE: Removes a table from the database.
CREATE INDEX: Creates an index to optimize query performance.
DROP INDEX: Deletes an index from the database.
CREATE VIEW: Creates a virtual table (view) based on a query.
DROP VIEW: Deletes a view from the database.

4. Data Integrity and Constraints

These commands are used to ensure data accuracy, validity, and consistency.

PRIMARY KEY: Defines a primary key for uniquely identifying rows.
FOREIGN KEY: Defines a relationship between tables.
UNIQUE: Ensures that all values in a column are unique.
CHECK: Adds a condition to enforce data integrity.
IS NULL / IS NOT NULL: Checks for NULL values in a column.

5. Transactions Control Language (TCL)

These commands manage transactions, ensuring that multiple operations are executed as a single unit.

BEGIN TRANSACTION: Starts a new transaction.
COMMIT: Commits (saves) changes made during a transaction.
ROLLBACK: Reverts changes made during a transaction.
SAVEPOINT: Sets a point in a transaction to roll back to if needed.

6. User Access and Security

These commands manage user permissions and privileges.

GRANT: Assigns privileges to a user or role.
REVOKE: Removes privileges from a user or role.

7. Subqueries and Conditional Expressions

Commands used for creating subqueries or conditional logic within queries.

CASE: Conditional logic in a SELECT statement.
EXPLAIN: Shows the query execution plan for performance optimization.

8. Miscellaneous Operations

Commands for performing tasks that don’t fall into the other categories but still play essential roles in SQL operations.

IS NULL / IS NOT NULL: Checks for NULL values.
TRANSACTION: A command to manage a sequence of SQL queries.
VIEW: Creates and deletes views (virtual tables).
FOREIGN KEY: Establishes relationships between tables and ensures referential integrity.
INDEX: Used for indexing specific columns to improve query performance.

Summary of Categories:

Category	Commands
Data Querying	`SELECT`, `SELECT DISTINCT`, `WHERE`, `ORDER BY`, `GROUP BY`, `HAVING`, `JOIN`, `UNION`, `LIMIT`, `IN`, `BETWEEN`, `LIKE`, `EXISTS`, `ANY/ALL`, `RANK/DENSE_RANK/ROW_NUMBER`
Data Manipulation	`INSERT INTO`, `UPDATE`, `DELETE`, `TRUNCATE TABLE`
Database Definition	`CREATE TABLE`, `ALTER TABLE`, `DROP TABLE`, `CREATE INDEX`, `DROP INDEX`, `CREATE VIEW`, `DROP VIEW`
Data Integrity	`PRIMARY KEY`, `FOREIGN KEY`, `UNIQUE`, `CHECK`, `IS NULL`, `IS NOT NULL`
Transactions	`BEGIN TRANSACTION`, `COMMIT`, `ROLLBACK`, `SAVEPOINT`
User Access	`GRANT`, `REVOKE`
Subqueries & Conditionals	`CASE`, `EXPLAIN`
Miscellaneous	`VIEW`, `FOREIGN KEY`, `INDEX`

Each category serves a specific purpose, ensuring that you can perform all essential tasks from managing data, to defining table structures, ensuring data consistency, and optimising performance.

Here are some commonly used SQL commands:

1. SELECT – Retrieve Data from Database

SELECT column1, column2 FROM table_name;

Retrieves data from specified columns in the table.

Example:

SELECT first_name, last_name FROM employees;

2. INSERT INTO – Insert Data into Table

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Adds a new record to the table.

Example:

INSERT INTO employees (first_name, last_name, age)
VALUES ('John', 'Doe', 28);

3. UPDATE – Modify Existing Data

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Updates existing records based on a condition.

Example:

UPDATE employees
SET age = 30
WHERE first_name = 'John' AND last_name = 'Doe';

4. DELETE – Delete Data from Table

DELETE FROM table_name WHERE condition;

Deletes records based on the condition.

Example:

DELETE FROM employees
WHERE first_name = 'John' AND last_name = 'Doe';

5. CREATE TABLE – Create a New Table

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...
);

Creates a new table with specified columns and datatypes.

Example:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT
);

6. ALTER TABLE – Modify an Existing Table

ALTER TABLE table_name
ADD column_name datatype;

Adds a new column to an existing table.

Example:

ALTER TABLE employees
ADD email VARCHAR(100);

ALTER TABLE employees
DROP COLUMN email;

Drops a column from a table.

7. DROP TABLE – Delete a Table

DROP TABLE table_name;

Removes a table and all of its data from the database.

Example:

DROP TABLE employees;

8. CREATE INDEX – Create an Index to Speed Up Queries

CREATE INDEX index_name
ON table_name (column1, column2);

Creates an index for faster searches.

Example:

CREATE INDEX idx_employees_name
ON employees (first_name, last_name);

9. DROP INDEX – Delete an Index

DROP INDEX index_name;

Removes an index from the database.

Example:

DROP INDEX idx_employees_name;

10. SELECT DISTINCT – Get Unique Records

SELECT DISTINCT column1 FROM table_name;

Retrieves unique values from a column.

Example:

SELECT DISTINCT age FROM employees;

11. WHERE – Filter Records

SELECT column1, column2
FROM table_name
WHERE condition;

Filters records based on conditions.

Example:

SELECT first_name, last_name
FROM employees
WHERE age > 25;

12. ORDER BY – Sort Records

SELECT column1, column2
FROM table_name
ORDER BY column1 [ASC|DESC];

Sorts the result set based on one or more columns.

Example:

SELECT first_name, last_name, age
FROM employees
ORDER BY age DESC;

13. GROUP BY – Group Records

SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1;

Groups records based on a column and performs an aggregate function like COUNT, AVG, SUM, etc.

Example:

SELECT age, COUNT(*)
FROM employees
GROUP BY age;

14. HAVING – Filter Groups

SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1
HAVING COUNT(*) > 1;

Filters records after grouping.

Example:

SELECT age, COUNT(*)
FROM employees
GROUP BY age
HAVING COUNT(*) > 2;

15. JOIN – Combine Rows from Two or More Tables

SELECT table1.column1, table2.column2
FROM table1
JOIN table2
ON table1.common_column = table2.common_column;

Combines rows based on a related column.

Example:

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
JOIN departments
ON employees.department_id = departments.department_id;

16. UNION – Combine Results of Two SELECT Statements

SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;

Combines results from two SELECT queries, eliminating duplicates.

17. LIMIT / TOP – Limit the Number of Records Returned

SELECT column1, column2
FROM table_name
LIMIT 10;

Limits the result to the top 10 rows.

Example:

SELECT first_name, last_name FROM employees LIMIT 5;

18. IN – Filter Data Based on a List of Values

SELECT column1
FROM table_name
WHERE column1 IN (value1, value2, value3);

Filters records based on a list of values.

Example:

SELECT first_name, last_name
FROM employees
WHERE age IN (25, 30, 35);

These are the basic and most commonly used SQL commands for database operations.

Here are additional SQL commands and concepts that you may find useful for working with databases:

19. BETWEEN – Filter Data Within a Range

SELECT column1
FROM table_name
WHERE column1 BETWEEN value1 AND value2;

Filters data within a specified range.

Example:

SELECT first_name, last_name, age
FROM employees
WHERE age BETWEEN 25 AND 35;

20. LIKE – Pattern Matching

SELECT column1
FROM table_name
WHERE column1 LIKE 'pattern';

Filters data based on a pattern.

Example:

SELECT first_name, last_name
FROM employees
WHERE first_name LIKE 'J%';

The % wildcard matches any sequence of characters.
The _ wildcard matches a single character.

21. IS NULL – Check for NULL Values

SELECT column1
FROM table_name
WHERE column1 IS NULL;

Filters rows where the column value is NULL.

Example:

SELECT first_name, last_name
FROM employees
WHERE email IS NULL;

22. IS NOT NULL – Check for Non-NULL Values

SELECT column1
FROM table_name
WHERE column1 IS NOT NULL;

Filters rows where the column value is not NULL.

Example:

SELECT first_name, last_name
FROM employees
WHERE email IS NOT NULL;

23. EXISTS – Check if a Subquery Returns Any Rows

SELECT column1
FROM table_name
WHERE EXISTS (SELECT * FROM another_table WHERE condition);

Returns TRUE if the subquery returns any rows.

Example:

SELECT first_name, last_name
FROM employees
WHERE EXISTS (SELECT * FROM departments WHERE departments.department_id = employees.department_id);

24. ANY / ALL – Compare Value with Any or All Values in a Subquery

SELECT column1
FROM table_name
WHERE column1 > ANY (SELECT column1 FROM table_name WHERE condition);

ANY: Returns TRUE if the comparison is true for any row in the subquery.
ALL: Returns TRUE if the comparison is true for all rows in the subquery.

Example:

SELECT first_name, last_name
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department = 'Sales');

25. CASE – Conditional Expressions

SELECT column1,
  CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE result3
  END AS new_column_name
FROM table_name;

Returns values based on conditions.

Example:

SELECT first_name, last_name, 
  CASE
    WHEN age < 30 THEN 'Young'
    WHEN age BETWEEN 30 AND 50 THEN 'Middle-aged'
    ELSE 'Senior'
  END AS age_group
FROM employees;

26. TRANSACTION – Begin, Commit, and Rollback a Transaction

BEGIN TRANSACTION;

-- SQL queries

COMMIT;  -- Save changes

Ensures that a series of queries are treated as a single unit of work.

Example:

BEGIN TRANSACTION;

UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales';
UPDATE employees SET salary = salary * 1.05 WHERE department = 'Marketing';

COMMIT;  -- Saves the changes

ROLLBACK;  -- Rolls back to the previous state if an error occurs

27. SAVEPOINT – Set a Point in a Transaction to Roll Back to

SAVEPOINT savepoint_name;

-- SQL queries

ROLLBACK TO SAVEPOINT savepoint_name; -- Rollback to the savepoint if needed

28. REVOKE – Revoke Privileges from a User

REVOKE privilege_type ON object FROM user;

Removes previously granted privileges.

Example:

REVOKE SELECT, INSERT ON employees FROM user1;

29. GRANT – Grant Privileges to a User

GRANT privilege_type ON object TO user;

Assigns privileges to a user.

Example:

GRANT SELECT, INSERT ON employees TO user1;

30. FOREIGN KEY – Create a Foreign Key Constraint

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    FOREIGN KEY (column_name) REFERENCES another_table(column_name)
);

Defines a foreign key relationship between two tables to maintain referential integrity.

Example:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

31. UNIQUE – Ensure Unique Values in a Column

CREATE TABLE table_name (
    column1 datatype UNIQUE
);

Ensures that all values in a column are unique.

Example:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

32. CHECK – Add a Condition for Data Integrity

CREATE TABLE table_name (
    column1 datatype,
    CHECK (condition)
);

Ensures data integrity by specifying a condition that must be met for every row.

Example:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    age INT,
    CHECK (age >= 18)
);

33. ALTER COLUMN – Modify Column Data Type or Constraints

ALTER TABLE table_name
ALTER COLUMN column_name SET DATA TYPE new_datatype;

Changes a column’s data type or constraints.

Example:

ALTER TABLE employees
ALTER COLUMN age SET DATA TYPE BIGINT;

34. TRUNCATE TABLE – Delete All Data from a Table (Quickly)

TRUNCATE TABLE table_name;

Removes all records from a table but does not remove the table structure. It’s faster than DELETE.

Example:

TRUNCATE TABLE employees;

35. INDEX – Optimize Query Performance

CREATE INDEX index_name
ON table_name (column1, column2);

Creates an index for faster querying on a specific column or set of columns.

Example:

CREATE INDEX idx_employees_name
ON employees (first_name, last_name);

36. VIEW – Create a Virtual Table

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

Creates a virtual table based on the result of a query.

Example:

CREATE VIEW employee_view AS
SELECT first_name, last_name, age
FROM employees
WHERE age > 30;

37. DROP VIEW – Delete a View

DROP VIEW view_name;

Deletes a view from the database.

Example:

DROP VIEW employee_view;

38. EXPLAIN – Display Execution Plan of a Query

EXPLAIN SELECT column1 FROM table_name WHERE condition;

Provides the execution plan for a query, which helps optimize query performance.

Example:

EXPLAIN SELECT * FROM employees WHERE age > 30;

39. RANK / DENSE_RANK / ROW_NUMBER – Ranking Functions

SELECT column1, RANK() OVER (ORDER BY column2 DESC) AS rank
FROM table_name;

Used for ranking data based on a specific column.

Example:

SELECT first_name, salary, RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

40. WINDOW FUNCTIONS – Perform Calculations Over a Set of Rows

SELECT column1, SUM(column2) OVER (PARTITION BY column1) AS total
FROM table_name;

Performs calculations like sums, averages, etc., over a specified window of rows.

Example:

SELECT department, salary, SUM(salary) OVER (PARTITION BY department) AS department_salary
FROM employees;

These additional SQL commands and concepts should help you manage databases more effectively, perform complex queries, and maintain data integrity.

1. Data Querying and Retrieval

2. Data Manipulation Language (DML)

3. Database Definition (DDL)

4. Data Integrity and Constraints

5. Transactions Control Language (TCL)

6. User Access and Security

7. Subqueries and Conditional Expressions

8. Miscellaneous Operations

Summary of Categories:

1. SELECT – Retrieve Data from Database

2. INSERT INTO – Insert Data into Table

3. UPDATE – Modify Existing Data

4. DELETE – Delete Data from Table

5. CREATE TABLE – Create a New Table

6. ALTER TABLE – Modify an Existing Table

7. DROP TABLE – Delete a Table

8. CREATE INDEX – Create an Index to Speed Up Queries

9. DROP INDEX – Delete an Index

10. SELECT DISTINCT – Get Unique Records

11. WHERE – Filter Records

12. ORDER BY – Sort Records

13. GROUP BY – Group Records

14. HAVING – Filter Groups

15. JOIN – Combine Rows from Two or More Tables

16. UNION – Combine Results of Two SELECT Statements

17. LIMIT / TOP – Limit the Number of Records Returned

18. IN – Filter Data Based on a List of Values

19. BETWEEN – Filter Data Within a Range

20. LIKE – Pattern Matching

21. IS NULL – Check for NULL Values

22. IS NOT NULL – Check for Non-NULL Values

23. EXISTS – Check if a Subquery Returns Any Rows

24. ANY / ALL – Compare Value with Any or All Values in a Subquery

25. CASE – Conditional Expressions

26. TRANSACTION – Begin, Commit, and Rollback a Transaction

27. SAVEPOINT – Set a Point in a Transaction to Roll Back to

28. REVOKE – Revoke Privileges from a User

29. GRANT – Grant Privileges to a User

30. FOREIGN KEY – Create a Foreign Key Constraint

31. UNIQUE – Ensure Unique Values in a Column

32. CHECK – Add a Condition for Data Integrity

33. ALTER COLUMN – Modify Column Data Type or Constraints

34. TRUNCATE TABLE – Delete All Data from a Table (Quickly)

35. INDEX – Optimize Query Performance

36. VIEW – Create a Virtual Table

37. DROP VIEW – Delete a View

38. EXPLAIN – Display Execution Plan of a Query

39. RANK / DENSE_RANK / ROW_NUMBER – Ranking Functions

40. WINDOW FUNCTIONS – Perform Calculations Over a Set of Rows

Leave a Reply Cancel reply