SQL Query: Complete Guide with Types, Syntax And Examples

0 min read

In the world of data, SQL (Structured Query Language) is the universal language used to interact with databases. Whether you're managing data in MySQL, PostgreSQL, SQL Server, or Oracle, knowing how to write SQL queries is an essential skill for developers, analysts, and database administrators alike.

SQL queries allow you to retrieve, insert, update, and delete data. They can be simple one-liners or complex nested statements performing sophisticated operations across multiple tables.

In this article, we’ll dive deep into SQL queries - what they are, why they matter, and how you can use them effectively across different scenarios. We will cover various types of SQL queries, provide syntax, and walk through practical examples.

What is an SQL Query?

An SQL query is a command written in Structured Query Language that allows you to interact with a relational database. SQL statements are used to perform operations like retrieving records, inserting new data, updating existing data, and deleting unwanted rows.

SQL statements follow a declarative approach, which means you tell the database what you want, not how to get it.

Types of SQL Queries (With Syntax and Examples)

1. SELECT Query (Data Retrieval)

Used to retrieve data from one or more tables.

Syntax:

Example:

2. INSERT Query (Add Data)

Used to add new records to a table.

Syntax:

Example:

3. UPDATE Query (Modify Data)

Used to modify existing data in a table.

Syntax:

Example:

4. DELETE Query (Remove Data)

Used to delete rows from a table.

Syntax:

Example:

5. CREATE TABLE Statement

Used to create a new table in the database.

Syntax:

Example:

6. ALTER TABLE Statement

Used to modify the structure of an existing table.

Syntax:

Example:

7. DROP TABLE Statement

Used to remove an existing table from the database.

Syntax:

Example:

8. Nested Queries (Subqueries)

A subquery is a query inside another query. It is often used to filter or compute values.

Syntax:

Example:

9. JOIN Queries (Combine Tables)

Used to retrieve related data from multiple tables.

a. INNER JOIN

Returns records with matching values in both tables.

Syntax:

Example:

b. LEFT JOIN

Returns all records from the left table and matching records from the right table.

c. RIGHT JOIN

Returns all records from the right table and matching records from the left table.

d. FULL OUTER JOIN

Returns records when there is a match in either table.

10. GROUP BY Query

Used to group rows that have the same values in specified columns.

Syntax:

Example:

11. HAVING Clause

Used to filter groups based on aggregate functions.

Syntax:

Example:

12. ORDER BY Clause

Used to sort query results.

Syntax:

Example:

13. DISTINCT Keyword

Used to remove duplicate records.

Syntax:

Example:

14. LIMIT Clause (TOP / FETCH FIRST)

Restricts the number of returned rows.

Syntax:

This returns the first five rows from the employees table.

15. UNION and UNION ALL

Combines the results of two queries.

Syntax:

Example:

Advanced SQL Queries & Features

1. EXISTS Clause

Checks the existence of rows in a subquery.

Example:

SELECT name FROM employees WHERE EXISTS (
  SELECT 1 FROM attendance WHERE attendance.employee_id = employees.id
);

2. CASE Statement

Implements conditional logic.

Example:

SELECT name, 
  CASE 
    WHEN salary > 50000 THEN 'High'
    ELSE 'Low'
  END AS salary_bracket
FROM employees;

3. COALESCE() Function

Returns the first non-null value in a list.

Example:

SELECT name, COALESCE(phone, 'Not Provided') AS contact FROM employees;

4. IN Clause

Matches a value against a list.

Example:

SELECT * FROM employees WHERE department IN ('HR', 'Finance');

5. BETWEEN Operator

Checks if a value is within a range.

Example:

SELECT * FROM employees WHERE salary BETWEEN 30000 AND 60000;

6. LIKE Operator

Used for pattern matching.

Example:

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

7. IS NULL / IS NOT NULL

Checks for NULL values.

Example:

SELECT * FROM employees WHERE email IS NULL;

8. RENAME TABLE / COLUMN

Changes table or column names.

Example:

RENAME TABLE old_table TO new_table;
ALTER TABLE employees RENAME COLUMN name TO full_name;

9. TRUNCATE TABLE

Quickly deletes all rows from a table.

Example:

TRUNCATE TABLE employees;

10. INDEXES

Speeds up data retrieval.

Example:

CREATE INDEX idx_name ON employees (name);

Expert-Level SQL Features

1. WITH Clause (Common Table Expressions - CTE)

Defines a temporary result set for use within a larger query.

Example:

WITH dept_count AS (
  SELECT dept_id, COUNT(*) AS total
  FROM employees
  GROUP BY dept_id
)
SELECT * FROM dept_count WHERE total > 5;

2. WINDOW FUNCTIONS

Performs calculations across rows related to the current row.

Example:

SELECT name, salary, 
  RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

3. MERGE / UPSERT

Merges data - update if exists, insert if not.

Example (PostgreSQL-style):

INSERT INTO employees (id, name, age) 
VALUES (1, 'Amit', 28)
ON CONFLICT (id) 
DO UPDATE SET name = EXCLUDED.name, age = EXCLUDED.age;

4. TRANSACTIONS (BEGIN, COMMIT, ROLLBACK)

Ensures data consistency and rollback on failure.

Example:

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

If something goes wrong:

ROLLBACK;

Applications of SQL Queries

SQL queries are used across a wide range of industries and roles. Here are some key applications:

• Business Intelligence & Reporting: Extracting and analyzing data to generate insights, dashboards, and reports.
• Software Development: Managing backend databases for apps, websites, and software systems.
• Data Analysis: Filtering, aggregating, and transforming data to find trends and patterns.
• Database Administration: Performing maintenance tasks like data backup, restoration, and table optimization.
• E-commerce: Managing product listings, orders, customer data, and transactions.
• Finance & Accounting: Automating reports, processing transactions, and reconciling records.
• Healthcare: Managing patient records, appointment systems, and medical histories.
• Telecommunications: Handling call records, billing systems, and user data.
• Education: Organizing student information systems, grades, and attendance records.
• CRM Systems: Segmenting customers, tracking interactions, and personalizing communications.

SQL remains an essential tool for anyone working with structured data, making its mastery highly valuable across domains.

Quiz Time!!!

Learn SQL with ease! Explore this course on Unstop

Suggested Reads: 

• Keywords in SQL Explained: Complete Guide with Examples for Beginners
• Difference Between Superkey And Candidate Key in SQL Explained
• Difference Between DELETE and TRUNCATE Commands In SQL
• Advantages And Disadvantages Of SQL Simplified (With Examples)