Advanced SQL Techniques and Permissions

If you've already mastered basic SQL, it's time to level up. This article explores advanced SQL tools and how to manage permissions securely.

---

1. Window Functions

Window functions allow you to perform calculations across a set of rows related to the current row.

Example: ROW_NUMBER()

SELECT name, department,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

This ranks employees within their departments based on salary.

Other Common Window Functions:

• RANK()
• DENSE_RANK()
• LEAD() / LAG()
• NTILE(n)

2. Common Table Expressions (CTEs)

CTEs are temporary result sets used within an SQL query. Useful for readability and recursion.

Basic Syntax:

WITH dept_sales AS (
  SELECT department_id, SUM(sales) AS total_sales
  FROM orders
  GROUP BY department_id
)
SELECT * FROM dept_sales;

Recursive CTE Example:

WITH RECURSIVE counter AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 5
)
SELECT * FROM counter;

3. Pivoting Data

Pivoting transforms rows into columns, making summary data easier to read.

Example using CASE:

SELECT
  department,
  SUM(CASE WHEN gender = 'Male' THEN 1 ELSE 0 END) AS male_count,
  SUM(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS female_count
FROM employees
GROUP BY department;

4. Performance Optimization Tips

• Use proper indexing (especially on WHERE/JOIN fields).
• Avoid SELECT *
• Break complex queries into smaller CTEs or subqueries.
• Analyze query plans with EXPLAIN or ANALYZE.

---

5. SQL Security and Permissions

Controlling access in SQL is critical for security, especially in multi-user databases.

GRANT Statement

Use GRANT to give users privileges:

GRANT SELECT, INSERT ON employees TO 'user123';

REVOKE Statement

Use REVOKE to take away privileges:

REVOKE INSERT ON employees FROM 'user123';

Role-Based Permissions

Assign roles to group users and simplify access control.

CREATE ROLE hr_team;
GRANT SELECT, UPDATE ON employees TO hr_team;
GRANT hr_team TO 'user1';

Best Practices

• Use the principle of least privilege.
• Audit user actions where possible.
• Regularly review and clean up old roles or users.

Also Read:

• SQL Joins and Subqueries
• SQL Constraints, Indexes and Views
• SQL Clauses and Operators

FAQs

• Q: Are window functions supported by all databases?
  A: Most modern RDBMS like PostgreSQL, MySQL 8+, SQL Server, and Oracle support them.
• Q: Is GRANT/REVOKE permanent?
  A: Yes, until explicitly changed or the user is removed.
• Q: Can CTEs be nested?
  A: Yes, and they improve query readability.

Explore more SQL tutorials on Sabbir93s Blog.