Most Important SQL Commands

September 16, 2024

Web Stories

Structured Query Language (SQL) is the backbone of database management and is crucial for anyone working with databases. Whether you’re a data analyst, developer, or database administrator, understanding the most important SQL commands is essential. Here’s a detailed guide on the key SQL commands, along with sample code and examples to illustrate their usage.

Basic SQL Commands

1. SELECT

Retrieves data from a database.

sql

SELECT column1, column2 FROM table_name;

Example:

sql

SELECT first_name, last_name FROM employees;

2. FROM

Specifies the table to retrieve data from.

sql

SELECT column1, column2 FROM table_name;

Example:

sql

SELECT product_name, price FROM products;

3. WHERE

Filters rows based on a condition.

sql

SELECT column1, column2 FROM table_name WHERE condition;

Example:

sql

SELECT first_name, last_name FROM employees WHERE department = ‘Sales’;

4. AS

Renames a column or table with an alias.

sql

Copy code

SELECT column1 AS alias_name FROM table_name;

Example:

sql

SELECT first_name AS fname, last_name AS lname FROM employees;

5. IN

Checks if a value matches any value in a list.

sql

SELECT column1, column2 FROM table_name WHERE column IN (value1, value2, …);

Example:

sql

SELECT first_name, last_name FROM employees WHERE department IN (‘Sales’, ‘Marketing’);

Transaction Control Commands

6. COMMIT

Saves all changes made during the current transaction.

sql

COMMIT;

Example:

sql

UPDATE employees SET salary = salary * 1.1 WHERE department = ‘Sales’; COMMIT;

7. ROLLBACK

Undoes all changes made during the current transaction.

sql

ROLLBACK;

Example:

sql

DELETE FROM employees WHERE department = ‘Sales’; ROLLBACK;

Data Manipulation Commands

8. UPDATE

Modifies existing data in a table.

sql

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

Example:

sql

UPDATE employees SET salary = salary * 1.05 WHERE department = ‘IT’;

9. INSERT INTO

Adds new rows to a table.

sql

INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …);

Example:

sql

INSERT INTO employees (first_name, last_name, department, salary) VALUES (‘John’, ‘Doe’, ‘HR’, 60000);

Aggregation Commands

10. GROUP BY

Groups rows sharing a property for aggregate functions.

sql

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

Example:

sql

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

11. HAVING

Filters groups based on aggregate properties.

sql

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

Example:

sql

SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000;

12. SUM

Calculates the total of a numeric column.

sql

SELECT SUM(column) FROM table_name WHERE condition;

Example:

sql

SELECT SUM(salary) FROM employees WHERE department = ‘IT’;

13. MIN

Finds the minimum value in a column.

sql

SELECT MIN(column) FROM table_name;

Example:

sql

SELECT MIN(salary) FROM employees;

14. MAX

Finds the maximum value in a column.

sql

SELECT MAX(column) FROM table_name;

Example:

sql

SELECT MAX(salary) FROM employees;

Join and Conditional Commands

15. JOIN

Combines rows from two or more tables based on related columns.

sql

SELECT columns FROM table1 JOIN table2 ON table1.column = table2.column;

Example:

sql

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

16. AND

Combines two or more conditions in a query.

sql

SELECT column1, column2 FROM table_name WHERE condition1 AND condition2;

Example:

sql

SELECT first_name, last_name FROM employees WHERE department = ‘Sales’ AND salary > 50000;

17. OR

Combines two or more conditions in a query where any condition can be true.

sql

SELECT column1, column2 FROM table_name WHERE condition1 OR condition2;

Example:

sql

SELECT first_name, last_name FROM employees WHERE department = ‘Sales’ OR department = ‘Marketing’;

18. CASE

Provides conditional logic in a query.

sql

SELECT column1, CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE result END FROM table_name;

Example:

sql

SELECT first_name, last_name, CASE WHEN salary > 70000 THEN ‘High’ WHEN salary BETWEEN 50000 AND 70000 THEN ‘Medium’ ELSE ‘Low’ END AS salary_level FROM employees;

19. IS NULL

Checks for null values.

sql

SELECT column1, column2 FROM table_name WHERE column IS NULL;

Example:

sql

SELECT first_name, last_name FROM employees WHERE manager_id IS NULL;

20. LIKE

Searches for a specified pattern in a column.

sql

SELECT column1, column2 FROM table_name WHERE column LIKE pattern;

Example:

sql

Copy code

SELECT first_name, last_name FROM employees WHERE first_name LIKE ‘J%’;

Data Definition Commands

21. ALTER TABLE

Modifies an existing table structure.

sql

ALTER TABLE table_name ADD column_name datatype;

Example:

sql

ALTER TABLE employees ADD birth_date DATE;

22. CREATE

Defines a new table, database, or other database object.

sql

CREATE TABLE table_name ( column1 datatype, column2 datatype, … );

Example:

sql

CREATE TABLE departments ( id INT PRIMARY KEY, department_name VARCHAR(50) );

23. DELETE

Removes data from a table.

sql

DELETE FROM table_name WHERE condition;

Example:

sql

DELETE FROM employees WHERE department = ‘Sales’;

24. DROP

Deletes a database object like a table or index.

sql

DROP TABLE table_name;

Example:

sql

DROP TABLE departments;

Sorting and Calculation Commands

25. ORDER BY

Sorts the result set of a query.

sql

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

Example:

sql

SELECT first_name, last_name FROM employees ORDER BY last_name ASC;

26. COUNT

Counts the number of rows in a table.

sql

SELECT COUNT(column) FROM table_name WHERE condition;

Example:

sql

SELECT COUNT(*) FROM employees WHERE department = ‘IT’;

27. AVG

Calculates the average value of a numeric column.

sql

SELECT AVG(column) FROM table_name WHERE condition;

Example:

sql

SELECT AVG(salary) FROM employees WHERE department = ‘Sales’;

28. LIMIT

Restricts the number of rows returned by a query.

sql

SELECT column1, column2 FROM table_name LIMIT number;

Example:

sql

SELECT first_name, last_name FROM employees LIMIT 10;

Conclusion: Most Important SQL Commands

Mastering these SQL commands is crucial for anyone working with databases. Whether you’re retrieving data, updating records, or managing database structures, these commands provide the tools needed to interact effectively with your database. Practice using these commands to become proficient in SQL and enhance your data management skills.