Whether you are a backend developer, a data analyst, or preparing for a technical interview, working with databases requires a strong grasp of SQL (Structured Query Language). Writing efficient queries is key to retrieving, inserting, and managing relational database records.

In this guide, we will walk through the most commonly used sql queries, explain the core concepts behind JOINs and aggregations, and provide standard examples you can run on MySQL, PostgreSQL, or SQL Server.

1. Retrieving and Filtering Data (SELECT & WHERE)

The most basic and frequently used command in SQL is the SELECT statement, which retrieves records from a table. We use the WHERE clause to filter the returned rows based on specific conditions, and ORDER BY to sort the results:

SELECT employee_id, first_name, salary 
FROM employees 
WHERE salary >= 50000 
ORDER BY salary DESC;

In this query, we select specific columns from the employees table, filter out anyone earning less than 50,000, and sort the employees from the highest salary to the lowest.

2. Combining Data from Multiple Tables (JOINs)

In relational databases, data is split across multiple tables. To combine rows from two or more tables based on a related column, we use JOIN operations. Understanding joins is one of the most commonly used sql queries skills.

INNER JOIN Example

An INNER JOIN returns rows when there is a match in both tables:

SELECT orders.order_id, customers.customer_name, orders.order_date
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

LEFT JOIN Example

A LEFT JOIN (or LEFT OUTER JOIN) returns all records from the left table, and the matched records from the right table. If there is no match, the result is NULL on the right side:

SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

Types of SQL JOINs Compared

3. Aggregating and Grouping Data (GROUP BY & HAVING)

Aggregating data allows you to perform calculations on multiple rows and return a single summary value. Common functions include COUNT(), SUM(), AVG(), MIN(), and MAX(). We group the results using GROUP BY, and filter groups using HAVING:

SELECT department_id, COUNT(employee_id) AS total_employees, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 60000;

Note: You cannot use aggregate functions inside the WHERE clause. To filter grouped calculations, you must use HAVING instead.

4. Modifying Existing Records (UPDATE & DELETE)

In your daily database operations, you will frequently update or remove records. These commands are powerful and should always be accompanied by a WHERE clause to prevent accidental mass changes:

// Safely updating a user email
UPDATE users 
SET email = 'newemail@example.com' 
WHERE user_id = 42;

// Safely deleting an inactive user
DELETE FROM users 
WHERE status = 'inactive' AND last_login < '2023-01-01';

Summary & Best Practices

Mastering these basic operations is crucial for working with any relational database management system (RDBMS). Always test your queries on a staging database before executing modifications on live production tables. For advanced query patterns, see our guide on Subqueries in SQL Queries. You can also explore the official MySQL SELECT Documentation to learn about more query execution optimization flags.