Listen to this Post
SQL is a powerful tool for managing and querying databases, but some concepts can be tricky. Below, we break down advanced SQL topics with practical examples.
1. WHERE vs HAVING
- WHERE filters rows before grouping.
- HAVING filters after grouping (used with aggregate functions like
SUM()).
Example:
-- WHERE filters individual rows SELECT name, salary FROM employees WHERE salary > 50000; -- HAVING filters groups SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 60000;
2. WINDOW FUNCTIONS vs GROUP BY
Window functions (ROW_NUMBER(), SUM() OVER()) operate on related rows without collapsing them like GROUP BY.
Example:
-- ROW_NUMBER assigns a unique number to each row SELECT name, salary, ROW_NUMBER() OVER(ORDER BY salary DESC) AS rank FROM employees; -- SUM() OVER() calculates running totals SELECT date, revenue, SUM(revenue) OVER(ORDER BY date) AS running_total FROM sales;
3. CTEs vs Subqueries
Common Table Expressions (CTEs) improve readability and allow recursion.
Example:
-- CTE for readability WITH high_earners AS ( SELECT name, salary FROM employees WHERE salary > 100000 ) SELECT FROM high_earners; -- Recursive CTE (e.g., for hierarchical data) WITH RECURSIVE employee_hierarchy AS ( SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id FROM employees e JOIN employee_hierarchy eh ON e.manager_id = eh.id ) SELECT FROM employee_hierarchy;
4. Deadlocks in SQL
A deadlock occurs when two transactions block each other.
Prevention:
- Keep transactions short.
- Access tables in a fixed order.
- Implement retry logic.
5. Partitioned Tables
Partitioning improves performance by splitting large tables (e.g., by date).
Example (PostgreSQL):
CREATE TABLE sales (
sale_id SERIAL,
sale_date DATE,
amount NUMERIC
) PARTITION BY RANGE (sale_date);
-- Create partitions for different date ranges
CREATE TABLE sales_2023 PARTITION OF sales FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
6. Materialized Views
Materialized views store results physically for faster reads but require refreshes.
Example (PostgreSQL):
CREATE MATERIALIZED VIEW mv_high_sales AS SELECT product_id, SUM(amount) FROM sales GROUP BY product_id; -- Refresh when data changes REFRESH MATERIALIZED VIEW mv_high_sales;
7. Covering Indexes
A covering index includes all columns needed for a query, avoiding table lookups.
Example:
CREATE INDEX idx_employee_covering ON employees (department_id) INCLUDE (name, salary);
You Should Know:
SQL Performance Tuning Commands
-- Check query execution plan (PostgreSQL) EXPLAIN ANALYZE SELECT FROM employees WHERE salary > 50000; -- Find slow queries (MySQL) SELECT FROM mysql.slow_log; -- Rebuild indexes (SQL Server) ALTER INDEX ALL ON employees REBUILD;
Linux Commands for Database Admins
Monitor PostgreSQL logs tail -f /var/log/postgresql/postgresql-14-main.log Check disk I/O performance iotop -o Kill a runaway SQL query sudo kill -9 $(pgrep -f "long_running_query")
Windows Commands for SQL Debugging
Check SQL Server status Get-Service -Name "MSSQLSERVER" Find locked tables sp_who2
What Undercode Say:
SQL optimization is crucial for high-performance applications. Use partitioning for large datasets, CTEs for complex queries, and covering indexes to speed up reads. Avoid deadlocks with transaction best practices.
Expected Output:
-- Example of a well-optimized query WITH recent_orders AS ( SELECT customer_id, SUM(amount) AS total_spent FROM orders WHERE order_date > '2023-01-01' GROUP BY customer_id ) SELECT c.name, ro.total_spent FROM customers c JOIN recent_orders ro ON c.id = ro.customer_id WHERE ro.total_spent > 1000 ORDER BY ro.total_spent DESC;
Prediction:
As databases grow, automated query optimization and AI-driven indexing will become standard, reducing manual tuning efforts.
End of
References:
Reported By: Raul Junco – Hackers Feeds
Extra Hub: Undercode MoN
Basic Verification: Pass ✅
