Scribd
Upload
18 views5 pages

Advanced SQL Techniques and Concepts

The document provides advanced SQL notes covering key topics such as joins, subqueries, window functions, common table expressions (CTEs), indexing, optimization, transactions, stored procedures, triggers, and advanced SQL topics like views and JSON functions. It includes examples for clarity and emphasizes best practices like avoiding SELECT *, using specific columns, and employing parameterized queries to prevent SQL injection. Additionally, it discusses the importance of ACID properties in transactions and various isolation levels.

Uploaded by

foodonline506
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
18 views5 pages

Advanced SQL Techniques and Concepts

The document provides advanced SQL notes covering key topics such as joins, subqueries, window functions, common table expressions (CTEs), indexing, optimization, transactions, stored procedures, triggers, and advanced SQL topics like views and JSON functions. It includes examples for clarity and emphasizes best practices like avoiding SELECT *, using specific columns, and employing parameterized queries to prevent SQL injection. Additionally, it discusses the importance of ACID properties in transactions and various isolation levels.

Uploaded by

foodonline506
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Advanced SQL Notes

1. Joins and Subqueries


- INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN.
- Self Join used for hierarchical data.
- Subquery inside WHERE, FROM, or SELECT.
Example:
SELECT name FROM Employee WHERE salary > (SELECT AVG(salary) FROM Employee);
Correlated Subquery: Runs once per outer row.
SELECT [Link] FROM Employee e1 WHERE salary > (SELECT AVG(salary) FROM Employee
e2 WHERE [Link]=[Link]);
2. Window Functions
- Perform calculations across rows without GROUP BY.
SELECT name, salary, RANK() OVER(ORDER BY salary DESC) AS rank FROM Employee;
- Functions: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE().
- PARTITION BY divides results into groups.

3. CTE (Common Table Expressions)


- Improves readability of complex queries.
WITH HighEarners AS (SELECT * FROM Employee WHERE salary > 80000) SELECT * FROM
HighEarners;
- Recursive CTE for hierarchical data.
4. Indexing and Optimization
- Clustered Index: sorts table physically.
- Non-clustered Index: logical pointer structure.
- Composite Index: multiple columns.
- Avoid SELECT *; use specific columns.
- Use EXPLAIN / EXPLAIN ANALYZE to check query plan.
- Normalize up to 3NF or BCNF for minimal redundancy.
5. Transactions and ACID
- Atomicity, Consistency, Isolation, Durability.
- COMMIT / ROLLBACK to manage transactions.
- Isolation Levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ,
SERIALIZABLE.
- Deadlock detection and prevention (ordering locks).

6. Stored Procedures & Triggers


- Stored Procedure: reusable SQL logic.
CREATE PROCEDURE GetHighEarners AS SELECT * FROM Employee WHERE salary > 90000;
- Trigger: executes automatically on events (INSERT/UPDATE/DELETE).
7. Advanced Topics
- Views: virtual tables for abstraction.
- Materialized Views: physical data copies for performance.
- Partitioning large tables by range/hash/list.
- SQL Injection → use parameterized queries.
- JSON functions → JSON_EXTRACT(), JSON_OBJECT().
- Analytical SQL → PIVOT, UNPIVOT, GROUPING SETS.

End of SQL Notes

You might also like