Table of content:
SQL Query: Complete Guide with Types, Syntax And Examples
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!!!
QUIZZ SNIPPET IS HER QUIZZ SNIPPET IS HERE QUIZZ SNIPPET IS HERE
Learn SQL with ease! Explore this course on Unstop
Suggested Reads: