SQL (Structured Query Language) is a powerful language used to manage and manipulate databases. Knowing how to use SQL commands effectively is essential for data analysts, developers, and database administrators. This guide provides a comprehensive overview of the most important SQL commands, accompanied by practical examples.
Basic SQL Commands
1. SELECT
Retrieves data from a database.
sqlSELECT * FROM employees;
Example:
sqlSELECT first_name, last_name FROM employees;
This command selects all records from the “employees” table, retrieving only the first and last names of each employee.
2. FROM
Specifies the table to retrieve data from.
sqlSELECT product_name, price FROM products;
Example:
sqlSELECT department_name FROM departments;
This command retrieves the department names from the “departments” table.
3. WHERE
Filters rows based on a condition.
sqlSELECT first_name, last_name FROM employees WHERE department = ‘Sales’;
Example:
sqlSELECT product_name, price FROM products WHERE price > 100;
This command retrieves product names and prices from the “products” table where the price is greater than 100.
4. AS
Renames a column or table with an alias.
sqlSELECT first_name AS fname, last_name AS lname FROM employees;
Example:
sqlSELECT product_name AS pname, price AS cost FROM products;
This command renames the “product_name” column to “pname” and “price” column to “cost” in the result set.
5. IN
Checks if a value matches any value in a list.
sqlSELECT first_name, last_name FROM employees WHERE department IN (‘Sales’, ‘Marketing’);
Example:
sqlSELECT product_name FROM products WHERE category IN (‘Electronics’, ‘Furniture’);
This command retrieves product names from the “products” table where the category is either ‘Electronics’ or ‘Furniture’.
Transaction Control Commands
6. COMMIT
Saves all changes made during the current transaction.
sqlUPDATE employees SET salary = salary * 1.1 WHERE department = ‘Sales’; COMMIT;
Example:
sqlDELETE FROM employees WHERE department = ‘Sales’; COMMIT;
This command increases the salary of employees in the Sales department by 10% and saves the changes.
7. ROLLBACK
Undoes all changes made during the current transaction.
sqlDELETE FROM employees WHERE department = ‘Sales’; ROLLBACK;
Example:
sqlUPDATE employees SET salary = 50000 WHERE department = ‘HR’; ROLLBACK;
This command deletes records of employees in the Sales department and then undoes the deletion.
Data Manipulation Commands
8. UPDATE
Modifies existing data in a table.
sqlUPDATE employees SET salary = salary * 1.05 WHERE department = ‘IT’;
Example:
sqlUPDATE products SET price = price * 1.2 WHERE category = ‘Electronics’;
This command updates the salary of IT department employees by 5%.
9. INSERT INTO
Adds new rows to a table.
sqlINSERT INTO employees (first_name, last_name, department, salary) VALUES (‘John’, ‘Doe’, ‘HR’, 60000);
Example:
sqlINSERT INTO products (product_name, category, price) VALUES (‘Laptop’, ‘Electronics’, 800);
This command inserts a new employee record into the “employees” table.
Aggregation Commands
10. GROUP BY
Groups rows sharing a property for aggregate functions.
sqlSELECT department_name FROM departments;
Example:
sqlSELECT category, SUM(price) FROM products GROUP BY category;
This command counts the number of employees in each department.
11. HAVING
Filters groups based on aggregate properties.
sqlSELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000;
Example:
sqlSELECT category, COUNT(*) FROM products GROUP BY category HAVING COUNT(*) > 10;
This command finds departments with an average salary greater than 50,000.
12. SUM
Calculates the total of a numeric column.
sqlSELECT SUM(salary) FROM employees WHERE department = ‘IT’;
Example:
sqlSELECT SUM(price) FROM products WHERE category = ‘Electronics’;
This command calculates the total salary of IT department employees.
13. MIN
Finds the minimum value in a column.
sqlSELECT MIN(salary) FROM employees;
Example:
sqlSELECT MIN(price) FROM products;
This command finds the minimum salary in the “employees” table.
14. MAX
Finds the maximum value in a column.
sqlSELECT MAX(salary) FROM employees;
Example:
sqlSELECT MAX(price) FROM products;
This command finds the maximum salary in the “employees” table.
Join and Conditional Commands
15. JOIN
Combines rows from two or more tables based on related columns.
sqlSELECT employees.first_name, employees.last_name, departments.department_name FROM employees JOIN departments ON employees.department_id = departments.id;
Example:
sqlSELECT orders.order_id, customers.customer_name FROM orders JOIN customers ON orders.customer_id = customers.id;
This command joins the “employees” and “departments” tables based on the department_id.
16. AND
Combines two or more conditions in a query.
sqlSELECT first_name, last_name FROM employees WHERE department = ‘Sales’ AND salary > 50000;
Example:
sqlSELECT product_name, price FROM products WHERE category = ‘Electronics’ AND price > 500;
This command retrieves employees in the Sales department with a salary greater than 50,000.
17. OR
Combines two or more conditions in a query where any condition can be true.
sqlSELECT first_name, last_name FROM employees WHERE department = ‘Sales’ OR department = ‘Marketing’;
Example:
sqlSELECT product_name FROM products WHERE category = ‘Electronics’ OR category = ‘Furniture’;
This command retrieves employees in either the Sales or Marketing departments.
18. CASE
Provides conditional logic in a query.
sqlSELECT 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;
Example:
sqlSELECT product_name, CASE WHEN price > 1000 THEN ‘Expensive’ WHEN price BETWEEN 500 AND 1000 THEN ‘Moderate’ ELSE ‘Cheap’ END AS price_range FROM products;
This command categorizes employees’ salary levels as ‘High’, ‘Medium’, or ‘Low’.
19. IS NULL
Checks for null values.
sqlSELECT first_name, last_name FROM employees WHERE manager_id IS NULL;
Example:
sqlSELECT product_name FROM products WHERE price IS NULL;
This command retrieves employees who do not have a manager.
20. LIKE
Searches for a specified pattern in a column.
sqlSELECT first_name, last_name FROM employees WHERE first_name LIKE ‘J%’;
Example:
sqlSELECT product_name FROM products WHERE product_name LIKE ‘%Phone’;
This command retrieves employees whose first names start with ‘J’.
Data Definition Commands
21. ALTER TABLE
Modifies an existing table structure.
sqlALTER TABLE employees ADD birth_date DATE;
Example:
sqlALTER TABLE products ADD stock_quantity INT;
This command adds a new column “birth_date” to the “employees” table.
22. CREATE
Defines a new table, database, or other database object.
sqlCREATE TABLE departments ( id INT PRIMARY KEY, department_name VARCHAR(50) );
Example:
sqlCREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, order_date DATE );
This command creates a new “departments” table.
23. DELETE
Removes data from a table.
sqlDELETE FROM employees WHERE department = ‘Sales’;
Example:
sqlDELETE FROM products WHERE category = ‘Electronics’;
This command deletes all employees in the Sales department.
24. DROP
Deletes a database object like a table or index.
sqlDROP TABLE departments;
Example:
sqlDROP TABLE orders;
This command drops the “departments” table.
Sorting and Calculation Commands
25. ORDER BY
Sorts the result set of a query.
sqlSELECT first_name, last_name FROM employees ORDER BY last_name ASC;
Example:
sqlSELECT product_name, price FROM products ORDER BY price DESC;
This command sorts employees by last name in ascending order.
26. COUNT
Counts the number of rows in a table.
sqlSELECT COUNT(*) FROM employees WHERE department = ‘IT’;
Example:
sqlSELECT COUNT(*) FROM products WHERE category = ‘Electronics’;
This command counts the number of employees in the IT department.
27. AVG
Calculates the average value of a numeric column.
sqlSELECT AVG(salary) FROM employees WHERE department = ‘Sales’;
Example:
sqlSELECT AVG(price) FROM products WHERE category = ‘Furniture’;
This command calculates the average salary of employees in the Sales department.
28. LIMIT
Restricts the number of rows returned by a query.
sqlSELECT first_name, last_name FROM employees LIMIT 10;
Example:
sqlSELECT product_name, price FROM products LIMIT 5;
This command retrieves the first 10 employees from the “employees” table.
Advanced SQL Commands
29. SELECT DISTINCT
Selects distinct values from a column.
sqlSELECT DISTINCT department FROM employees;
Example:
sqlSELECT DISTINCT category FROM products;
This command retrieves unique department names from the “employees” table.
30. INNER JOIN
Joins two tables on a common column.
sqlSELECT employees.first_name, employees.last_name, departments.department_name FROM employees INNER JOIN departments ON employees.department_id = departments.id;
Example:
sqlSELECT orders.order_id, customers.customer_name FROM orders INNER JOIN customers ON orders.customer_id = customers.id;
This command performs an inner join between the “employees” and “departments” tables.
31. LEFT JOIN
Left join two tables.
sqlSELECT employees.first_name, employees.last_name, departments.department_name FROM employees LEFT JOIN departments ON employees.department_id = departments.id;
Example:
sqlSELECT orders.order_id, customers.customer_name FROM orders LEFT JOIN customers ON orders.customer_id = customers.id;
This command performs a left join between the “employees” and “departments” tables.
32. RIGHT JOIN
Right join two tables.
sqlSELECT employees.first_name, employees.last_name, departments.department_name FROM employees RIGHT JOIN departments ON employees.department_id = departments.id;
Example:
sqlSELECT orders.order_id, customers.customer_name FROM orders RIGHT JOIN customers ON orders.customer_id = customers.id;
This command performs a right join between the “employees” and “departments” tables.
33. FULL OUTER JOIN
Full join two tables.
sqlSELECT employees.first_name, employees.last_name, departments.department_name FROM employees FULL OUTER JOIN departments ON employees.department_id = departments.id;
Example:
sqlSELECT orders.order_id, customers.customer_name FROM orders FULL OUTER JOIN customers ON orders.customer_id = customers.id;
This command performs a full outer join between the “employees” and “departments” tables.
Grouping and Filtering Commands
34. GROUP BY
Group records by a column.
sqlSELECT department, COUNT(*) FROM employees GROUP BY department;
Example:
sqlSELECT category, COUNT(*) FROM products GROUP BY category;
This command groups employees by department and counts the number of employees in each department.
35. HAVING
Filter groups by a condition.
sqlSELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 10;
Example:
sqlSELECT category, COUNT(*) FROM products GROUP BY category HAVING COUNT(*) > 5;
This command filters groups to only include departments with more than 10 employees.
Conclusion: Essential SQL Commands: Examples and Usage
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.
