Essential SQL Functions

Field Value Transformation with CASE WHEN

CASE WHEN provides conditional logic similar to if-then statements in programming languages.

Syntax variant 1:

CASE column_name 
  WHEN 'result1' THEN 'display1'
  WHEN 'result2' THEN 'display2'
END AS alias

Syntax variant 2:

CASE 
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
END AS alias

Handling NULL Values

Different database systems provide various functions for replacing NULL values with defaults.

Database	Function	Example
MySQL	IFNULL	IFNULL(column_name, 0)
Oracle	NVL	NVL(column_name, 0)
SQL Server/Sybase	ISNULL	ISNULL(column_name, 0)

Pagination Techniques

MySQL using LIMIT:

SELECT * FROM table_name LIMIT 10;
SELECT * FROM table_name LIMIT 5, 10;

Oracle using ROWNUM:

SELECT * FROM table_name WHERE ROWNUM <= 2;

SQL Server/Sybase using TOP:

SELECT TOP 2 * FROM table_name;
SELECT TOP 50 PERCENT * FROM table_name;

String Operations

Extracting substrings:

SELECT SUBSTRING(column_name, start_position, length) AS alias
FROM table_name;

Adding sequential row numbers to results:

SET @counter = 0;
SELECT @counter := @counter + 1 AS row_num, h.* 
FROM household h;

GROUP BY with HAVING

Filtering groups based on aggregate conditions:

SELECT user_id 
FROM user_records 
GROUP BY user_id 
HAVING AVG(user_age) < 22;

Removing Duplicate Records

Retain the record with the minimum ID for each duplicate group:

DELETE FROM products 
WHERE product_code IN (
  SELECT product_code FROM products 
  GROUP BY product_code HAVING COUNT(1) > 1
)
AND id NOT IN (
  SELECT MIN(id) FROM products 
  GROUP BY product_code HAVING COUNT(1) > 1
);

Date and Time Formatting

MySQL DATE_FORMAT function:

SELECT DATE_FORMAT(update_timestamp, '%Y-%m-%d %H:%i:%s') AS update_time
FROM user_account;

Formatting date strings:

SELECT DATE_FORMAT('2018-10-10 00:00:00', '%Y%m%d');

Result: 20181010

Determining day of week:

• DAYOFWEEK returns Sunday as 1, so subtract 1
• WEEKDAY returns Monday as 0, so add 1

SELECT DAYOFWEEK('2021-04-22') - 1 AS sunday_based,
       WEEKDAY('2021-04-20') + 1 AS monday_based;

UNION vs UNION ALL

UNION removes duplicate rows after combining result sets, while UNION ALL simply appends all rows without deduplication.

SELECT column FROM table1
UNION
SELECT column FROM table2;

SELECT column FROM table1
UNION ALL
SELECT column FROM table2;

JOIN Types Comparison

LEFT JOIN: Returns all records from the left table plus matching records from the right table. Left table values are always preserved.

RIGHT JOIN: Returns all records from the right table plus matching records from the left table. Right table values are always preserved.

INNER JOIN: Returns only records where both tables have matching values. Both sides must match.

Common SQL Operations

Identifying and Releasing Table Locks

MySQL:

1. Check for locked tables:

SHOW OPEN TABLES WHERE In_use > 0;

2. Find the SQL causing locks:

SELECT a.trx_id, 
       a.trx_mysql_thread_id, 
       a.trx_query
FROM INFORMATION_SCHEMA.INNODB_LOCKS b
JOIN INFORMATION_SCHEMA.innodb_trx a 
  ON b.lock_trx_id = a.trx_id;

3. Generate KILL statements:

SELECT CONCAT('KILL ', a.trx_mysql_thread_id, ';') 
FROM INFORMATION_SCHEMA.INNODB_LOCKS b
JOIN INFORMATION_SCHEMA.innodb_trx a 
  ON b.lock_trx_id = a.trx_id;

SQL Server:

SELECT request_session_id AS spid,
       OBJECT_NAME(resource_associated_entity_id) AS table_name
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT';

Release lock:

DECLARE @spid INT;
SET @spid = 57;
DECLARE @sql VARCHAR(1000);
SET @sql = 'KILL ' + CAST(@spid AS VARCHAR);
EXEC(@sql);

Finding Tables Containing a Specific Column

MySQL:

SELECT DISTINCT TABLE_NAME 
FROM information_schema.COLUMNS
WHERE COLUMN_NAME = 'ip'
  AND TABLE_SCHEMA = 'my_database'
  AND TABLE_NAME NOT LIKE 'vm%';

SQL Server:

SELECT table_name 
FROM user_tab_columns 
WHERE COLUMN_NAME = 'field_name';

Retrieving Table Structure with Comments

SELECT t.table_name,
       t.COLUMN_NAME,
       t.DATA_TYPE || '(' || t.DATA_LENGTH || ')',
       c.COMMENTS
FROM User_Tab_Cols t
JOIN User_Col_Comments c
  ON t.table_name = c.table_name
 AND t.column_name = c.column_name;

Bulk Insert from Existing Data

INSERT INTO employees(name, age, gender)
SELECT name, age, gender FROM employees;

Date Range Queries

Today:

SELECT * FROM records 
WHERE TO_DAYS(event_date) = TO_DAYS(NOW());

Yesterday:

SELECT * FROM records 
WHERE TO_DAYS(NOW()) - TO_DAYS(event_date) = 1;

Current week:

SELECT * FROM records 
WHERE YEARWEEK(DATE_FORMAT(event_date, '%Y-%m-%d')) = YEARWEEK(NOW());

Current month:

SELECT * FROM records 
WHERE DATE_FORMAT(event_date, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m');

Previous month:

SELECT * FROM records 
WHERE DATE_FORMAT(event_date, '%Y%m') = 
      DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y%m');

Current year:

SELECT * FROM records 
WHERE YEAR(event_date) = YEAR(NOW());

Previous year:

SELECT * FROM records 
WHERE YEAR(event_date) = YEAR(DATE_SUB(NOW(), INTERVAL 1 YEAR));

Current quarter:

SELECT * FROM invoices 
WHERE QUARTER(create_date) = QUARTER(NOW());

Last quarter:

SELECT * FROM invoices 
WHERE QUARTER(create_date) = QUARTER(DATE_SUB(NOW(), INTERVAL 1 QUARTER));

Last 6 months:

SELECT * FROM records 
WHERE event_date BETWEEN DATE_SUB(NOW(), INTERVAL 6 MONTH) AND NOW();

Geospatial Distance Calculations

Calculate distance between two coordinates in meters:

SELECT ST_DISTANCE_SPHERE(
  POINT('114.43107891381024', '30.52764363752110'),
  POINT('114.42638694658900', '30.54681469735225')
) AS distance_meters;

IP Address Range Queries

Extract network prefix:

SELECT SUBSTRING_INDEX(ip_address, '.', 3) 
FROM ip_ranges;

Find IPs within a range:

SELECT * FROM ip_ranges
WHERE INET_ATON(ip_address) 
      BETWEEN INET_ATON('192.168.21.0') 
      AND INET_ATON('192.168.21.255');

Data base Metadata Queries

MySQL

SHOW DATABASES;

SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'database_name' 
  AND table_type = 'base table';

SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_schema = 'database_name' 
  AND table_name = 'table_name';

SQL Server

SELECT * FROM sysdatabases;

SELECT * FROM sysobjects WHERE xtype = 'U';

SELECT name 
FROM syscolumns 
WHERE id = OBJECT_ID('table_name');

SELECT sc.name, st.name 
FROM syscolumns sc
JOIN systypes st ON sc.xtype = st.xtype
WHERE sc.id IN (
  SELECT id FROM sysobjects 
  WHERE xtype = 'U' AND name = 'table_name'
);

Oracle

SELECT * FROM v$tablespace;

SELECT * FROM user_tables;

SELECT column_name 
FROM user_tab_columns 
WHERE table_name = 'TABLE_NAME';

SELECT column_name, data_type 
FROM user_tab_columns 
WHERE table_name = 'TABLE_NAME';

Business Scenario Examples

Student Performance Analysis

Consider a table class_records with columns: student_id, student_name, class_name, exam_score.

Find highest score per class:

SELECT class_name, 
       MAX(exam_score) AS highest_score
FROM class_records 
GROUP BY class_name;

Find top 3 scores per class (approach 1):

SELECT *
FROM (
  SELECT student_name, exam_score, class_name,
         (SELECT COUNT(*) + 1 
          FROM class_records c 
          WHERE c.exam_score > r.exam_score 
            AND c.class_name = r.class_name) AS ranking
  FROM class_records r
) ranked
WHERE ranking <= 3
ORDER BY class_name, ranking;

Find top 3 scores per class (approach 2):

SELECT a.*
FROM class_records a
WHERE 4 > (
  SELECT COUNT(*) + 1 
  FROM class_records 
  WHERE class_name = a.class_name 
    AND exam_score > a.exam_score
)
ORDER BY a.class_name, a.exam_score DESC;

Find second highest score per class:

SELECT class_name, MAX(exam_score) AS second_highest
FROM class_records
WHERE exam_score NOT IN (
  SELECT MAX(exam_score) 
  FROM class_records 
  GROUP BY class_name
)
GROUP BY class_name;

Rank students by total score:

SELECT student_id, student_name,
       SUM(exam_score) AS total_score
FROM class_records
GROUP BY student_id, student_name
ORDER BY total_score DESC;

Find students with all scores above 80:

SELECT student_id
FROM class_records
GROUP BY student_id
HAVING MIN(exam_score) > 80;

Ranking with Ties

Standard ranking (no gaps when ties exist):

SELECT student_id, student_name, exam_score,
       (SELECT COUNT(*) + 1 
        FROM class_records 
        WHERE exam_score > r.exam_score) AS rank
FROM class_records r
ORDER BY rank;

Ranking within class groups:

SELECT class_name, student_name, exam_score,
       (SELECT COUNT(*) + 1 
        FROM class_records c 
        WHERE c.exam_score > r.exam_score 
          AND c.class_name = r.class_name) AS rank
FROM class_records r
ORDER BY class_name, rank;

Dense ranking (gaps allowed after ties):

SELECT student_id, student_name, exam_score,
       CASE 
         WHEN @prev_score = exam_score THEN @cur_rank
         WHEN @prev_score := exam_score THEN @cur_rank := @cur_rank + 1
       END AS rank
FROM class_records,
     (SELECT @cur_rank := 0, @prev_score := NULL) vars
ORDER BY exam_score;

Organizational Hierarchy Queries

Table structure for a hierarchical organization:

CREATE TABLE organizational_units (
  unit_id INT PRIMARY KEY,
  parent_unit_id INT,
  unit_name VARCHAR(50)
);

INSERT INTO organizational_units VALUES
(1, NULL, 'Headquarters'),
(2, 1, 'Human Resources'),
(3, 1, 'Finance'),
(4, 1, 'Marketing'),
(5, 2, 'Recruitment'),
(6, 2, 'Training'),
(7, 5, 'Recruitment Team A'),
(8, 5, 'Recruitment Team B'),
(9, 6, 'Training Team A'),
(10, 6, 'Training Team B');

Find all descendants of a unit (MySQL 5.x):

SELECT u.unit_id, u.unit_name, u.parent_unit_id
FROM organizational_units u,
     (SELECT @target := 5) init
WHERE FIND_IN_SET(parent_unit_id, @target) > 0
  AND @target := CONCAT(@target, ',', unit_id)
UNION
SELECT unit_id, unit_name, parent_unit_id
FROM organizational_units
WHERE unit_id = 5
ORDER BY unit_id;

Find all descendants (MySQL 8.0+):

WITH RECURSIVE unit_hierarchy AS (
  SELECT unit_id, unit_name, parent_unit_id
  FROM organizational_units
  WHERE unit_id = 5
  UNION ALL
  SELECT d.unit_id, d.unit_name, d.parent_unit_id
  FROM organizational_units d
  JOIN unit_hierarchy h ON d.parent_unit_id = h.unit_id
)
SELECT * FROM unit_hierarchy;

Find all ancestors of a unit (MySQL 5.x):

SELECT h2.unit_id, h2.unit_name, h2.parent_unit_id
FROM (
  SELECT @current := unit_id,
         (SELECT parent_unit_id 
          FROM organizational_units 
          WHERE unit_id = @current) AS parent,
         @level := @level + 1 AS depth
  FROM organizational_units,
       (SELECT @current := 7, @level := 0) vars
  WHERE @current <> 0
) current_path
JOIN organizational_units h2 ON current_path.unit_id = h2.unit_id
ORDER BY depth DESC;

Find all ancestors (MySQL 8.0+):

WITH RECURSIVE ancestor_chain AS (
  SELECT unit_id, unit_name, parent_unit_id
  FROM organizational_units
  WHERE unit_id = 7
  UNION ALL
  SELECT d.unit_id, d.unit_name, d.parent_unit_id
  FROM organizational_units d
  JOIN ancestor_chain a ON d.unit_id = a.parent_unit_id
)
SELECT * FROM ancestor_chain;