Complete Guide and Detailed Examples of SQL Commands: From Basics to Advanced Queries

Structured Query Language (SQL) is one of those tools that, once mastered, becomes your best ally for interacting with any relational database. If you've ever wondered how to access, modify, delete, or analyze large volumes of data easily and efficiently, SQL is the answer you've been looking for. Learning to use SQL commands correctly makes the difference between being a basic user and becoming a true expert in data management and analysis.

In this article, I've compiled a comprehensive list of the main SQL commands, accompanied by clear explanations and practical examples, covering everything from the basics to more advanced queries. I'll also show you tricks and details that are often overlooked in more superficial articles, so you can get the most out of your databases, whether you're just starting out or looking to refine your skills. Get ready to dive into the world of SQL and discover how this language can transform the way you work with information.

What is SQL and why is it still essential?

SQL , or Structured Query Language, is a programming language specifically designed to manage and manipulate relational databases. Although it was developed in the 70s, it remains the standard solution for communicating with databases such as MySQL, PostgreSQL, SQL Server, MariaDB, and Oracle Database, among others.

The reason for its longevity is simple: SQL allows everything from basic tasks like querying data to advanced operations such as joining complex tables, creating structures, bulk updating records, and defining permissions . Furthermore, its syntax is simple enough for anyone to quickly become familiar with it, yet powerful enough to solve complex business problems.

Professionals across a wide range of fields—from developers, analysts, and database administrators to data scientists—rely on SQL to process, cleanse, and analyze critical data for their projects and organizations.

SQL command categories: DQL, DML, DDL, and DCL

Before we get to the examples, it's worth remembering that SQL commands can be grouped into different categories according to their function:

• DQL (Data Query Language): These are the sentences to query data, such as SELECT.
• DML (Data Manipulation Language): They manipulate records, with commands like INSERT, UPDATED y DELETE.
• DDL (Data Definition Language): They manage the structure of the database, for example, CREATE, ALTER, DROP y TRUNCATE.
• DCL (Data Control Language): They control permissions and access, using GRANT y REVOKE.

Each type of command serves an essential function in daily database management. It's important to know which command to use in each situation to avoid errors or even disasters in data management.

Main SQL commands: Fundamentals and practical examples

Mastering basic SQL commands opens the door to virtually any data management or analysis task. Below, I'll explain the most relevant ones, how they work, and provide usage examples to help you learn them.

SELECT: The king of queries

The SELECT command is the cornerstone of most SQL operations. It allows you to retrieve data stored in one or more tables.

General syntax:

SELECT column1, column2, ... FROM table_name;

To get all the columns in a table, simply use the asterisk:

SELECT * FROM employees;

If you want to filter specific data, you can add conditions:

SELECT name, age FROM employees WHERE age > 30;

Thanks to SELECT you can retrieve anything from a single value to complex datasets, apply aggregate functions, calculate derived columns, and prepare information for reports or visualizations.

WHERE: Filtering results precisely

The WHERE clause allows you to specify conditions to select only those rows that meet certain criteria. It is a fundamental filter for any query.

Basic syntax:

SELECT * FROM products WHERE price < 100;

You can combine conditions using logical operators such as AND , OR , and NOT , as well as comparison operators ( =, <, >, <=, >=, <> ).

For example, to recruit sales employees under 30 years old:

SELECT * FROM employees WHERE department = 'Sales' AND age < 30;

INSERT: Adding new records

With INSERT you can add new rows to a table. It's essential for keeping your databases updated with fresh information.

General syntax:

INSERT INTO products (name, price) VALUES ('Keyboard', 29.99);

If you want to add multiple records in a single query, you can also do so:

INSERT INTO products (name, price) VALUES ('Mouse', 15.50), ('Monitor', 120.00);

Remember that if you omit the column list, you must provide a value for each field in the table, respecting their order.

UPDATE: Modifying existing data

The UPDATE command allows you to change the value of one or more fields in records that meet a specific condition. It is useful for correcting errors or updating information.

Syntax:

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

In this example, all Marketing employees will see their salary increased by 5%. It's essential to use WHERE whenever you update , unless you really want to modify the entire table.

DELETE: Deleting data

To delete records, use DELETE along with a condition, thus avoiding accidentally deleting all the data from the table.

Example:

DELETE FROM products WHERE id_producto = 10;

If you omit the WHERE clause, all rows in the table will be deleted. Caution!

ORDER BY: Organizing results

The ORDER BY clause is used to sort the results of a query according to one or more columns. By default, the order will be ascending, but you can specify ASC or DESC as needed.

SELECT * FROM employees ORDER BY salary DESC;

You can combine multiple columns in the sort:

SELECT * FROM products ORDER BY category ASC, price DESC;

GROUP BY and aggregate functions: SUM, AVG, MIN, MAX, COUNT

GROUP BY allows you to group results according to one or more columns, which is very useful in conjunction with functions such as SUM() (sum), AVG() (average), MIN (minimum), MAX (maximum), and COUNT (count).

For example, to know how many products exist in each category:

SELECT category, COUNT(*) FROM products GROUP BY category;

Or to calculate the total bill for each customer:

SELECT customer_id, SUM(amount) FROM sales GROUP BY customer_id;

You can filter the resulting groups using the HAVING clause :

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

LIMIT and OFFSET: Limiting the number of results

When your tables have thousands of records, LIMIT is essential to show only a portion of the results, improving performance and readability.

SELECT * FROM products LIMIT 10;

With OFFSET you can skip a number of rows, useful for pagination:

SELECT * FROM products LIMIT 10 OFFSET 20;

BETWEEN, IN and LIKE: Advanced conditions in WHERE

These keywords expand the filtering possibilities:

• BETWEEN: Select values ​​within a range.

  SELECT * FROM employees WHERE salary BETWEEN 1000 AND 3000;

• IN: Search for multiple values.

  SELECT * FROM products WHERE category IN ('Computers', 'Electronics');

• LIKE: Look for patterns in text.

  SELECT * FROM customers WHERE name LIKE 'A%';

Wildcard characters such as % (any number of characters) and _ (a single character) can also be used to further customize LIKE searches.

JOIN: Crossing information between tables

One of the great strengths of SQL is the ability to combine data from different tables using JOINs . The most common ones are:

• INNER JOIN: Returns matching rows in both tables.
• LEFT JOIN: Returns all rows from the left table and any matches from the right (padding with NULL if there is no match).
• RIGHT JOIN: Returns all rows from the right table and matching rows from the left.
• FULL OUTER JOIN: Returns all rows from both tables (padded with NULL where there is no match).

INNER JOIN example:

SELECT orders.id, customers.name FROM orders INNER JOIN customers ON orders.customer_id = customers.id;

LEFT JOIN:

SELECT employees.name, sales.amount FROM employees LEFT JOIN sales ON employees.id = sales.employee_id;

ALTER, CREATE and DROP: Modifying the database structure

In addition to manipulating data, SQL allows you to define and modify the structure of tables and other database objects:

• CREATE: To create new objects (tables, views, indexes, users…).

  CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(100), price DECIMAL(8,2));

• ALTER: To modify the structure of an existing object.

  ALTER TABLE products ADD description VARCHAR(255);

  ALTER TABLE products ALTER COLUMN price SET NOT NULL;

• DROP: To delete objects from the database.

  DROP TABLE products;

  DROP USER example_user CASCADE;

Views (VIEW): Reusable queries and advanced filtering

Views are virtual objects that represent a saved query. They function like a table, but they don't store data; they only point to information in other tables. They are useful for simplifying complex queries, centralizing filters, and facilitating access to specific information.

CREATE VIEW active_employees AS SELECT * FROM employees WHERE active = 1;

You can query the view just like a table:

SELECT * FROM active_employees;

Views are ideal for managing permissions and displaying only relevant data to certain users.

Aggregate functions and calculations on columns

SQL allows you to work with aggregate functions and arithmetic operators to perform calculations on data:

• SUM(): Total sum of a column.
• AVG(): Arithmetic average.
• MIN() and MAX(): Minimum and maximum.
• COUNT(): Number of records.

For example, if you want the number of employees per department:

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

Or the highest salary by department:

SELECT department, MAX(salary) FROM employees GROUP BY department;

You can even create calculated columns directly in the query:

SELECT name, salary * 0.95 AS net_salary FROM employees;

Using aliases (AS) for tables and columns

In queries, you can assign temporary names to columns and tables using the AS command . This makes it easier to read results and write complex queries.

For example:

SELECT e.name AS employee, d.name AS department FROM employees AS e JOIN departments AS d ON e.department_id = d.id;

This way, the result will display columns with custom names. Using aliases is especially useful when performing JOINs or when columns have long or nondescript names.

Advanced SQL Query Examples: Solve Real-World Cases

To become self-sufficient in SQL, you need to face real-world challenges. Below are expanded examples taken from practical cases that will help solidify your knowledge.

Query all employees in a department and their total sales

SELECT e.id, e.name, SUM(v.amount) AS total_sales FROM employees e JOIN sales v ON e.id = v.employee_id WHERE e.department = 'Sales' GROUP BY e.id, e.name;

This example shows how to join tables, apply aggregate functions, and filter results.

Calculation of the average and maximum salary by department

SELECT department, AVG(salary) AS average_salary, MAX(salary) AS maximum_salary FROM employees GROUP BY department;

This way, you can analyze salary distribution and identify inequalities or areas with greater incentives.

Count products by price range using CASE

SELECT CASE WHEN price < 50 THEN 'Cheap' WHEN price < 150 THEN 'Medium' ELSE 'Expensive' END AS price_range, COUNT(*) AS quantity FROM products GROUP BY price_range;

Using CASE allows you to create custom categories within the query.

Example query to find duplicate records

SELECT name, COUNT(*) FROM customers GROUP BY name HAVING COUNT(*) > 1;

This helps you keep your database clean and free of redundant data.

Identify the nth highest salary (subqueries)

SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP N salary FROM employees ORDER BY salary DESC ) ORDER BY salary ASC;

Perfect for answering questions in technical interviews or producing advanced reports.

Delete old records automatically

DELETE FROM logs WHERE date < NOW() - INTERVAL 6 MONTH;

Ideal for production databases where you need to control table sizes.

Useful tips and tricks in SQL

Mastering SQL isn't just about knowing how to write basic queries. Here are some tips to help you get the most out of the language:

• Always use WHERE in UPDATE and DELETE to prevent accidental mass modifications or deletions.
• Put indexes on the most consulted columns to speed up searches and joins.
• Prefer explicit JOINs to WHERE to join tables: facilitates maintenance and readability.
• Use aggregate functions and GROUP BY for automatic reporting and analysis without relying on external spreadsheets.
• Test on a test database before making major changes.
• Control permissions with GRANT and REVOKE to ensure the security of your data.
• Take advantage of the views (VIEW) to simplify access to complex information and centralize business logic.
• Don't overuse SELECT *, as you may bring in more data than necessary and slow down the system.
• Comment on complex queries to facilitate future maintenance.

Frequently asked questions about SQL commands in interviews and real-life cases

Mastering SQL is essential for excelling in technical interviews and solving everyday problems. Here are some typical questions and answers to help you prepare:

• What is an INNER JOIN?
  This is the default join in SQL; it returns rows that match both tables. Example:

  SELECT * FROM A INNER JOIN B ON A.id = B.a_id;

• What is LEFT JOIN used for?
  Returns all rows from the left table and any matches from the right table. Example:

  SELECT * FROM employees LEFT JOIN sales ON employees.id = sales.employee_id;

• What is the difference between DROP and DELETE?
  DELETE deletes rows from a table, while DROP completely removes the object (table, view, user) from the database.
• Can you rollback after an ALTER?
  No, because ALTER is DDL and many systems commit automatically. It's important to make sure before using it.
• What are pseudo-columns?
  They are virtual columns generated by the system, such as ROWID, USER or CURRVAL in Oracle.
• How to create a view?

  CREATE VIEW view_employees AS SELECT name, salary FROM employees;

• How to grant permissions?

  GRANT SELECT ON inventory TO ana, rita;

• How to delete a user along with their data?

  DROP USER user CASCADE;

Regular expressions and special operators in SQL

A lesser-known but very powerful part of SQL is the ability to search for patterns using regular expressions and advanced operators. For example:

• %: Represents any string of any length (used in LIKE).
• _ : Represents a single character (also in LIKE).
• | : Alternation, to look for one thing or another.
• *: Repetition of a character zero or more times.

Advanced Management: Roles, Permissions, and Security

Managing a database involves more than just querying and modifying data. It's crucial to control who can access, modify, or delete information using commands like GRANT , REVOKE , or advanced ROLE management.

CREATE ROLE manager;

GRANT SELECT, INSERT, UPDATE ON employees TO manager;

GRANT manager TO juan, maria;

This allows you to centralize security and simplify permissions management in large environments. For more information on permissions management, see our article on automation and administration.