Are you tired of wasting time looking up SQL commands every time you need to perform a common task? Whether you’re a developer, data analyst, or working with databases in any capacity, mastering the most frequently used SQL commands can save you hours of work. In this post, we’ll cover the essential SQL commands you’ll need on a daily basis, along with practical examples and tips to make your database management tasks faster and easier.
CREATE – Creating a New Table or Database Object
The CREATE statement is used to define a new table, view, index, or other database objects. It is crucial when setting up the schema of a database.
Syntax:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
…
);Example:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
hire_date DATE
);INSERT – Adding Data to a Table
The INSERT statement is used to add new rows of data to a table.
Syntax:
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);Example:
INSERT INTO employees (first_name, last_name, hire_date)
VALUES ('John', 'Doe', '2024-01-01');UPDATE – Modifying Existing Data
The UPDATE statement is used to modify existing records in a table. It’s useful when you need to change the value of specific columns.
Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;Example:
UPDATE employees
SET salary = 60000
WHERE employee_id = 101;DELETE – Removing Data from a Table
The DELETE statement removes rows from a table based on a specified condition. Be cautious when using DELETE since it permanently removes data.
Syntax:
DELETE FROM table_name
WHERE condition;Example:
DELETE FROM employees
WHERE employee_id = 101;SELECT – Retrieving Data
The SELECT statement is the most commonly used SQL command. It allows you to retrieve data from a database table. It’s essential for querying and viewing the contents of a table.
Syntax:
SELECT column1, column2 FROM table_name;Example:
SELECT first_name, last_name, email FROM customers;FROM – Specifying the Source Table
The FROM clause is used to specify the table from which to retrieve or manipulate data. It is used with SELECT, UPDATE, DELETE, and JOIN.
Syntax:
SELECT column1, column2 FROM table_name;Example:
SELECT * FROM employees;WHERE – Filtering Data
The WHERE clause filters records based on a condition. It helps you narrow down your query results by specifying the rows you want to work with.
Syntax:
SELECT column1, column2 FROM table_name WHERE condition;Example:
SELECT * FROM employees WHERE department_id = 2;HAVING – Filtering Groups of Data
The HAVING clause is used to filter the result of a GROUP BY operation. It’s similar to WHERE, but it operates on grouped data.
Syntax:
SELECT column1, COUNT(*) FROM table_name GROUP BY column1 HAVING COUNT(*) > 5;Example:
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;DISTINCT – Eliminating Duplicates
The DISTINCT keyword is used to eliminate duplicate records from the result set. It ensures that only unique rows are returned.
Syntax:
SELECT DISTINCT column1, column2 FROM table_name;Examples:
SELECT DISTINCT department_id FROM employees;ORDER BY – Sorting the Results
The ORDER BY clause is used to sort the result set in either ascending or descending order. By default it sort the result in ascending order.
Syntax: To sort result in ascending order
SELECT column1, column2 FROM table_name ORDER BY column_name ASC;Note: By default result will be sorted in asceding order so you can remove Asc keyword from the query
Syntax: To sort result in descending order
SELECT column1, column2 FROM table_name ORDER BY column_name DESC;Example:
SELECT first_name, last_name FROM employees ORDER BY last_name DESC;LIMIT – Limit the Query result rows
In MySQL, PostgreSQL, and SQLite, LIMIT is used to restrict the number of rows returned by a query. It’s used at the end of the query to define how many rows you want to retrieve.
Syntax:
SELECT column1, column2
FROM table_name
LIMIT number_of_rows;or
SELECT column1, column2
FROM table_name
LIMIT number_of_rows OFFSET offset_value;number_of_rowsspecifies how many rows to return.OFFSET(optional) specifies how many rows to skip before starting to return the results.
Example:
-- Get the first 5 employees
SELECT first_name, last_name
FROM employees
LIMIT 5;-- Get 5 employees, skipping the first 10
SELECT first_name, last_name
FROM employees
LIMIT 5 OFFSET 10;When to Use: Use LIMIT when working with MySQL, PostgreSQL, SQLite, or other databases that follow similar syntax. It’s simple, easy to use, and commonly used in most SQL operations that require restricting result sets.
TOP – SQL Server
In SQL Server (and Sybase), TOP is used to limit the number of rows returned by a query. It is placed immediately after the SELECT keyword and specifies the number of rows to return.
Syntax:
SELECT TOP(number_of_rows) column1, column2
FROM table_name;or
SELECT TOP(number_of_rows) PERCENT column1, column2
FROM table_name;You can also use PERCENT to return a percentage of the result set.
Example:
-- Get the first 5 employees
SELECT TOP 5 first_name, last_name
FROM employees;-- Get the top 10% of employees
SELECT TOP 10 PERCENT first_name, last_name
FROM employees;When to Use: Use TOP when working with SQL Server. It’s particularly useful when you need to retrieve the first n rows, or even a percentage of rows, from a result set. If you want to get, for example, the top 10% of sales or the top 5 records by a certain condition, TOP works well.
Difference Between LIMIT and TOP
| Feature | LIMIT | TOP |
| Supported DBMS | MySQL, PostgreSQL, SQLite, MariaDB | SQL Server, Sybase |
| Syntax Position | Placed at the end of the SELECT query | Placed immediately after SELECT |
| Pagination | Can use LIMIT with OFFSET for pagination | No direct equivalent of OFFSET |
| PERCENT | Not supported in standard syntax | PERCENT is supported for returning a percentage of rows |
| Flexibility | Works with OFFSET for advanced paging | Offers advanced functionality for ordering and ranking rows |
Pagination is a technique used to divide large sets of data into smaller, more manageable chunks, or “pages,” that can be displayed separately.
IN – Specifying Multiple Values
The IN operator is used in the WHERE clause to specify multiple possible values for a column.
Syntax:
SELECT column1 FROM table_name WHERE column1 IN (value1, value2, ...);Example:
SELECT * FROM employees WHERE department_id IN (1, 2, 3);
BETWEEN – Filtering Within a Range
The BETWEEN operator is used to filter the result set based on a range of values. It can be used with numeric, date, or text values to select records that fall within a specified range. It is inclusive of the boundary values, meaning it includes both the lower and upper limits of the range.
Syntax:
SELECT column1, column2
FROM table_name
WHERE column_name BETWEEN value1 AND value2;Example – Numeric Range:
SELECT product_name, price
FROM products
WHERE price BETWEEN 50 AND 100;Example – Date Range:
SELECT order_id, order_date
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';Example – Text Range:
SELECT employee_name
FROM employees
WHERE employee_name BETWEEN 'A' AND 'M';JOINS
To Better understand consider below two tables.
| employee_id | employee_name | department_id |
| 1 | Alice | 1 |
| 2 | Bob | 2 |
| 3 | Charlie | 1 |
| 4 | David | NULL |
| 5 | Eve | 3 |
| department_id | department_name |
| 1 | HR |
| 2 | IT |
| 3 | Marketing |
| 4 | Sales |
INNER JOIN – Retrieving Matching Records
The INNER JOIN returns records that have matching values in both tables involved in the join. If a row in one table does not have a matching row in the other table, it is excluded from the result set
Syntax:
SELECT column1, column2
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;Example:
SELECT employees.employee_name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;Result:
| employee_name | department_name |
| Alice | HR |
| Bob | IT |
| Charlie | HR |
| Eve | Marketing |
Explanation: Only employees who are assigned to a department (i.e., have a matching department_id in both tables) are returned.
LEFT JOIN (or LEFT OUTER JOIN) – Retrieving All Records from Left Table
The LEFT JOIN returns all records from the left table (the table listed before the JOIN), and the matched records from the right table. If there’s no match, NULL values are returned for columns from the right table.
Syntax:
SELECT column1, column2
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
Example:
SELECT employees.employee_name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id;| employee_name | department_name |
| Alice | HR |
| Bob | IT |
| Charlie | HR |
| David | NULL |
| Eve | Marketing |
Explanation: The employee “David” does not have a department (department_id is NULL), so the department_name is NULL for him. All employees are returned, even if they don’t belong to a department.
RIGHT JOIN (or RIGHT OUTER JOIN) – Retrieving All Records from Right Table
The RIGHT JOIN is similar to LEFT JOIN, but it returns all records from the right table (the table listed after the JOIN), and the matched records from the left table. If there’s no match, NULL values are returned for columns from the left table.
Syntax:
SELECT column1, column2
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;Example:
SELECT employees.employee_name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id;| employee_name | department_name |
| Alice | HR |
| Bob | IT |
| Charlie | HR |
| Eve | Marketing |
| NULL | Sales |
Explanation: The department “Sales” does not have any employees associated with it, so employee_name is NULL for that department. All departments are returned, even if they don’t have employees.
FULL JOIN (or FULL OUTER JOIN) – Retrieving All Records from Both Tables
The FULL JOIN returns all records when there is a match in either left or right table. It returns NULL for missing matches on either side.
Syntax:
SELECT column1, column2
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;Example:
SELECT employees.employee_name, departments.department_name
FROM employees
FULL JOIN departments
ON employees.department_id = departments.department_id;| employee_name | department_name |
| Alice | HR |
| Bob | IT |
| Charlie | HR |
| David | NULL |
| Eve | Marketing |
| NULL | Sales |
Explanation: The result includes all employees and all departments, with NULL for non-matching records. “David” does not have a department, so his department is NULL. The “Sales” department has no employees, so the employee name is NULL.
CROSS JOIN – Combining All Records from Both Tables
The CROSS JOIN returns the Cartesian product of both tables, i.e., every row from the left table is combined with every row from the right table. This means if the left table has 3 rows and the right table has 4 rows, the result will have 12 rows.
Syntax:
SELECT column1, column2
FROM table1
CROSS JOIN table2;Example:
SELECT employees.employee_name, departments.department_name
FROM employees
CROSS JOIN departments;| employee_name | department_name |
| Alice | HR |
| Alice | IT |
| Alice | Marketing |
| Alice | Sales |
| Bob | HR |
| Bob | IT |
| Bob | Marketing |
| Bob | Sales |
| Charlie | HR |
| Charlie | IT |
| Charlie | Marketing |
| Charlie | Sales |
| David | HR |
| David | IT |
| David | Marketing |
| David | Sales |
| Eve | HR |
| Eve | IT |
| Eve | Marketing |
| Eve | Sales |
Explanation: Every employee is paired with every department. There are 5 employees and 4 departments, so the result contains 5 × 4 = 20 rows.
SELF JOIN – Joining a Table with Itself
A SELF JOIN is used to join a table with itself. This can be useful for hierarchical data where a row in the table has a relationship with another row in the same table (e.g., an employee’s manager).
Syntax:
SELECT A.column1, B.column2
FROM table A, table B
WHERE A.column_name = B.column_name;Example:
let’s assume an employees table that also has a manager_id field to represent the manager of each employee.
We’ll modify the employees table as follows:
| employee_id | employee_name | department_id | manager_id |
| 1 | Alice | 1 | NULL |
| 2 | Bob | 2 | 1 |
| 3 | Charlie | 1 | 1 |
| 4 | David | 3 | 2 |
| 5 | Eve | 2 | 2 |
Example:
SELECT E1.employee_name AS Employee, E2.employee_name AS Manager
FROM employees E1
JOIN employees E2
ON E1.manager_id = E2.employee_id;| Employee | Manager |
| Bob | Alice |
| Charlie | Alice |
| David | Bob |
| Eve | Bob |
Explanation: The SELF JOIN links employees to their managers. Employee “Bob” reports to Alice, and so on. We join the employees table with itself, using aliases E1 for employees and E2 for managers.
CASE in SQL – Conditional Logic in Queries
The CASE statement in SQL is used to implement conditional logic in a query, allowing you to create different results based on conditions. It functions like an IF-THEN-ELSE statement in programming. You can use CASE in SELECT, UPDATE, DELETE, and other SQL statements to handle different conditions within your queries.
There are two main forms of the CASE statement in SQL: Simple CASE and Searched CASE.
Simple CASE Expression
This version compares an expression to multiple values and returns the result for the first match.
Syntax:
SELECT column1,
CASE column2
WHEN value1 THEN result1
WHEN value2 THEN result2
ELSE default_result
END AS result_column
FROM table_name;Example:
SELECT student_name,
grade_level,
CASE grade_level
WHEN 'A' THEN 'Excellent'
WHEN 'B' THEN 'Good'
WHEN 'C' THEN 'Average'
ELSE 'Needs Improvement'
END AS grade_category
FROM students;Searched CASE Expression
This version evaluates multiple conditions and returns the result for the first true condition.
Syntax:
SELECT column1,
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE default_result
END AS result_column
FROM table_name;Example:
Using CASE in a SELECT query to assign a salary grade based on the value of a salary column.
SELECT employee_id,
salary,
CASE
WHEN salary >= 100000 THEN 'High'
WHEN salary >= 50000 THEN 'Medium'
ELSE 'Low'
END AS salary_grade
FROM employees;You can also use CASE within an UPDATE statement to modify data conditionally.
UPDATE employees
SET salary = CASE
WHEN job_title = 'Manager' THEN salary * 1.10
WHEN job_title = 'Developer' THEN salary * 1.05
ELSE salary * 1.02
END
WHERE department = 'Sales';SQL is a powerful skill for developers, analysts, and anyone working with data, and the more you practice, the more confident you’ll become in crafting effective queries. So, start experimenting with these commands and continue exploring the vast capabilities that SQL has to offer. Happy querying!
If you have any questions or need further clarification on any of the topics covered, feel free to leave a comment or reach out. Stay tuned for more tutorials and tips to sharpen your SQL skills!
Leave a Reply