Many database management systems offer built-in SQL statements for profiling queries. For example, in MySQL, you can use the EXPLAIN statement to see how the database optimizer plans to execute a query. In PostgreSQL, you can use EXPLAIN ANALYZE for even more detailed information

-- Explain a slow query to see its execution plan
EXPLAIN SELECT * FROM your_table WHERE condition;

-- Analyze a slow query in more detail
EXPLAIN ANALYZE SELECT * FROM your_table WHERE condition;

Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.

SELECT
	product_id,
	product_name,
	list_price,
	RANK () OVER ( 
		ORDER BY list_price DESC
	) price_rank 
FROM
	production.products;

Recursive queries traverse hierarchical or graph-like structures.

WITH RECURSIVE employee_hierarchy AS (
    SELECT employee_id, manager_id FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id
    FROM employees e
    JOIN employee_hierarchy h ON e.manager_id = h.employee_id
)
SELECT * FROM employee_hierarchy;

This query retrieves the names of all tables and their corresponding columns in the current SQL Server database. It joins sys.tables (which stores information about all user-defined tables) with sys.columns (which stores information about table columns) using the object_id. This helps in understanding the structure of the database schema.

SELECT 
    T.name AS TableName, 
    C.name AS ColumnName
FROM sys.tables T
JOIN sys.columns C 
    ON T.object_id = C.object_id;

This query fetches the top 10 longest-running queries based on total elapsed time from the SQL Server dynamic management views (DMVs). It provides details such as the SQL handle, total execution time, execution count, and the actual query text.

SELECT TOP 10
    qs.sql_handle,
    qs.total_elapsed_time,
    qs.execution_count,
    SUBSTRING(qt.text, qs.statement_start_offset / 2 + 1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
            ELSE qs.statement_end_offset END) - qs.statement_start_offset) / 2 + 1
    ) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.total_elapsed_time DESC;

• The sys.dm_exec_query_stats DMV stores performance statistics of cached query plans.
• The sys.dm_exec_sql_text function retrieves the SQL text associated with a specific query plan.
• The query orders results in descending order by total elapsed time, helping identify slow-performing queries for optimization.

ROLLUP and CUBE generate hierarchical or multidimensional aggregated summaries.

SELECT product_id, region, SUM(sales)
FROM sales_data
GROUP BY ROLLUP (product_id, region);

The ROW_NUMBER() is a window function that assigns a sequential integer to each row within the partition of a result set. The row number starts with 1 for the first row in each partition.

SELECT 
   ROW_NUMBER() OVER (
	ORDER BY first_name
   ) row_num,
   first_name, 
   last_name, 
   city
FROM 
   sales.customers;

STRING_SPLIT (string, separator)

FROM

SELECT *
FROM employees;

JOIN (and ON)

SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

WHERE

SELECT name, salary
FROM employees
WHERE salary > 50000;

GROUP BY

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;

ROLLUP, CUBE, GROUPING SETS

// ROLLUP: Adds subtotal rows.
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY ROLLUP(department_id, job_id);

// CUBE: Adds subtotal rows for all combinations.
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY CUBE(department_id, job_id);

// GROUPING SETS: Allows custom grouping combinations.
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS ((department_id), (job_id), ());

HAVING

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 60000;

OVER (e.g., Window Functions)

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

SELECT DISTINCT

SELECT DISTINCT job_id
FROM employees;

UNION, INTERSECT, EXCEPT

// UNION
SELECT name FROM employees
UNION
SELECT name FROM contractors;

// INTERSECT
SELECT name FROM employees
INTERSECT
SELECT name FROM contractors;

// EXCEPT
SELECT name FROM employees
EXCEPT
SELECT name FROM contractors;

ORDER BY

SELECT name, salary
FROM employees
ORDER BY salary DESC;

OFFSET, FETCH FIRST/NEXT ROWS ONLY

SELECT name, salary
FROM employees
ORDER BY salary DESC
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY;

LIMIT, TOP

// LIMIT
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

// TOP
SELECT TOP 5 name, salary
FROM employees
ORDER BY salary DESC;

This query retrieves general information about all databases on your SQL Server instance. It provides details such as the database name, ID, creation date, and state description. You can use this table to monitor and manage databases, including checking their status, creation time, and availability.

SELECT name, database_id, create_date, state_desc
FROM sys.databases;

-- GET_EMPLOYEE_LIST 'E','first_name','DESC',0,20
CREATE OR ALTER   PROCEDURE [dbo].[GET_EMPLOYEE_LIST]
@SEARCH_TEXT AS VARCHAR(50)='',
@SORT_COLUMN_NAME AS VARCHAR(50)='',
@SORT_COLUMN_DIRECTION AS VARCHAR(50)='',
@START_INDEX AS INT=0,
@PAGE_SIZE AS INT=10
AS
BEGIN
 DECLARE @QUERY AS VARCHAR(MAX)='',@ORDER_QUERY AS VARCHAR(MAX)='',@CONDITIONS AS VARCHAR(MAX)='',
 @PAGINATION AS VARCHAR(MAX)=''

 SET @QUERY='SELECT * FROM Employees '

 -- SEARCH OPERATION
 IF(ISNULL(@SEARCH_TEXT,'')<>'')
 BEGIN
  IF(ISDATE(@SEARCH_TEXT)=1) SET @CONDITIONS=' WHERE CAST(date_of_birth AS DATE)=CAST('+@SEARCH_TEXT+'AS DATE)'
  ELSE IF(ISNUMERIC(@SEARCH_TEXT)=1)
  BEGIN
   SET @CONDITIONS=' WHERE salary='+@SEARCH_TEXT+' OR phone_number= CAST('+@SEARCH_TEXT+'AS VARCHAR(50))'
  END
  ELSE
  BEGIN
   SET @CONDITIONS='
   WHERE
   first_name LIKE ''%'+@SEARCH_TEXT+'%''
   OR first_name +'' ''+last_name LIKE ''%'+@SEARCH_TEXT+'%''
   OR last_name LIKE ''%'+@SEARCH_TEXT+'%''
   OR email LIKE ''%'+@SEARCH_TEXT+'%''
   OR gender LIKE ''%'+@SEARCH_TEXT+'%''
   OR department LIKE ''%'+@SEARCH_TEXT+'%''
   OR phone_number LIKE ''%'+@SEARCH_TEXT+'%''
  '
  END
 END

 -- SORT OPERATION
 IF(ISNULL(@SORT_COLUMN_NAME,'')<>'' AND ISNULL(@SORT_COLUMN_DIRECTION,'')<>'')
 BEGIN
  SET @ORDER_QUERY=' ORDER BY '+@SORT_COLUMN_NAME+' '+@SORT_COLUMN_DIRECTION
 END
 ELSE SET @ORDER_QUERY=' ORDER BY ID ASC'

 -- PAGINATION OPERATION
 IF(@PAGE_SIZE>0)
 BEGIN
  SET @PAGINATION=' OFFSET '+(CAST(@START_INDEX AS varchar(10)))+' ROWS
  FETCH NEXT '+(CAST(@PAGE_SIZE AS varchar(10)))+' ROWS ONLY'
 END

 IF(@CONDITIONS<>'') SET @QUERY+=@CONDITIONS
 IF(@ORDER_QUERY<>'') SET @QUERY+=@ORDER_QUERY
 IF(@PAGINATION<>'') SET @QUERY+=@PAGINATION

 PRINT(@QUERY)
 EXEC(@QUERY)
END

Stored procedures are precompiled SQL scripts that can be executed with parameters to automate tasks.

CREATE PROCEDURE GetCustomerOrders (IN customer_id INT)
BEGIN
    SELECT * FROM orders WHERE customer_id = customer_id;
END;