💾 SQL Bytes — Bite-Sized SQL Tips & Tricks! 🚀

Welcome to SQL Bytes, your daily dose of SQL magic! 🌟 Whether you’re here for quick tips or diving into advanced concepts, this page evolves daily with fresh content to keep your SQL skills sharp. 💡

🚀 Explore. Learn. Level Up! 📊

From beginner-friendly bites to pro-level insights, there’s always something new to discover. Bookmark this page 📑 and check back regularly for updates that make your queries shine! ✨

⚡ Quick. Simple. Powerful. 🌟

---

CASE WHEN Vs IF

CASE WHEN

The CASE expression is more versatile and can be used in any SQL dialect, including MySQL, PostgreSQL, SQL Server, and Oracle.

SELECT 
    order_id, 
    order_amount,
    CASE
        WHEN order_amount > 100 THEN 'High'
        WHEN order_amount BETWEEN 50 AND 100 THEN 'Medium'
        ELSE 'Low'
    END AS order_category
FROM orders;

IF

IF is more commonly used in MySQL and is simpler to write but less flexible than CASE. It’s generally used when you have a single condition to evaluate.

SELECT 
    order_id, 
    order_amount,
    IF(order_amount > 100, 'High', 'Low') AS order_category
FROM orders;

---

Round Vs Floor Vs Ceil

Round Function

The ROUND() function rounds a number to a specified number of decimal places. It can round to the nearest integer or to a specific number of decimal places. If no decimal places are specified, it rounds to the nearest whole number.

SELECT ROUND(123.456) AS RoundedValue; -- Result: 123
SELECT ROUND(123.456, 1) AS RoundedValue; -- Result: 123.5
SELECT ROUND(123.456, 2) AS RoundedValue; -- Result: 123.46
SELECT ROUND(123.456, -1) AS RoundedValue; -- Result: 120
SELECT ROUND(-123.456) AS RoundedValue; -- Result: -123

Floor Function

The FLOOR() function rounds a number down to the nearest integer. It always rounds towards negative infinity, regardless of the fractional part.

SELECT FLOOR(123.456) AS FlooredValue; -- Result: 123
SELECT FLOOR(-123.456) AS FlooredValue; -- Result: -124
SELECT FLOOR(3.9) AS FlooredValue; -- Result: 3
SELECT FLOOR(-3.9) AS FlooredValue; -- Result: -4

The CEIL() (or CEILING()) function rounds a number up to the nearest integer. It always rounds towards positive infinity, regardless of the fractional part.

SELECT CEIL(123.456) AS CeilingValue; -- Result: 124 
SELECT CEIL(-123.456) AS CeilingValue; -- Result: -123 
SELECT CEIL(3.1) AS CeilingValue; -- Result: 4 
SELECT CEIL(-3.1) AS CeilingValue; -- Result: -3

---

Joins

Inner Join

An INNER JOIN combines rows from two tables based on matching values. The result includes only rows with matching values in both tables.

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Cross Join

The CROSS JOIN produces a Cartesian product of two tables. Each row from the first table is combined with each row from the second table.

SELECT column_list
FROM table1
CROSS JOIN table2;

---

Common Table Expressions (CTEs)

A CTE is a named temporary result set that can be referenced within a query. It improves readability and simplifies complex queries by breaking them into manageable parts.

WITH CTE_Example AS (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
)
SELECT * FROM CTE_Example;

---

Handling NULL Values

Filtering NULL Values

To remove rows where a specific column contains NULL values, use the IS NOT NULL condition.

SELECT *
FROM employees
WHERE some_column IS NOT NULL;

---

Sub queries with IN and =

IN vs = in Subqueries

Use IN when the sub query returns multiple values and = when it returns a single value.

-- Correct usage of `in`
SELECT *
FROM athletes
WHERE athlete_id IN (
  SELECT athlete_id FROM teams WHERE team_count > 1
);

-- Incorrect usage of =
SELECT *
FROM athletes
WHERE athlete_id = (
  SELECT athlete_id FROM teams WHERE team_count > 1
);

---

RANK vs DENSE_RANK vs ROW_NUMBER

Ranking Functions

RANK() assigns a rank to each row, leaving gaps for ties. DENSE_RANK() avoids gaps, and ROW_NUMBER() provides a unique sequential number.

SELECT
  first_name, last_name, salary,
  RANK() OVER (ORDER BY salary DESC) AS rank, -- Result: 1,2,2,4,4,6
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank, -- Result: 1,2,2,3,3,4
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_number -- Result: 1,2,3,4,5,6
FROM employees;

---

RANK() OVER (PARTITION BY)

The RANK() function in SQL is used to assign a rank to each row based on the order specified by the ORDER BY clause. When combined with the PARTITION BY clause, it allows us to rank rows within specific categories (partitions), rather than across the entire dataset. This is useful when you want to rank items within each category.

RANK() OVER (PARTITION BY column_name ORDER BY column_name [ASC|DESC])

• PARTITION BY column_name: Defines the categories to divide the data into (i.e., each unique value of the column will have its own ranking group).

• ORDER BY column_name: Specifies the order in which the rows are ranked within each partition.

This below example helps us in ranking employees by Salary in different departments in descening order…

SELECT employee_id, department_id, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;

---

IFNULL vs NULLIF

NULLIF Example

The NULLIF() function returns NULL if the two arguments are equal; otherwise, it returns the first argument. It is often used to avoid division by zero.

SELECT NULLIF(115, 0) AS result; -- 115
SELECT NULLIF(0, 0) AS result; -- NULL

IFNULL Example

The IFNULL() function returns the first argument if it is not NULL; otherwise, it returns the second argument.

SELECT IFNULL(NULL, 0) AS result; -- 0
SELECT IFNULL(6.96, 0) AS result; -- 6.96

---

DATEDIFF

The DATEDIFF() function calculates the difference in days between two dates.

SELECT *
FROM activities
WHERE DATEDIFF('2019-07-27', activity_date) < 30
  AND activity_date <= '2019-07-27';

DATE_FORMAT

The DATE_FORMAT function in SQL is used to format date and time values according to a specified format. It allows you to extract or display date components (e.g., year, month, day) in a specific arrangement.

It is commonly used in SQL queries when you need to:

1. Format a date for readability.

2. Extract specific parts of a date for filtering, grouping, or calculations.

DATE_FORMAT(date, format)

• date: The column or value containing a date or datetime.

• format: A string specifying the desired output format using format specifiers.

Common Format Specifiers

Here are some useful format specifiers for DATE_FORMAT:

• %Y: Year (4 digits, e.g., 2025)

• %y: Year (2 digits, e.g., 25)

• %m: Month (2 digits, e.g., 01 for January)

• %c: Month (numeric, no leading zero, e.g., 1 for January)

• %d: Day of the month (2 digits, e.g., 08)

• %e: Day of the month (no leading zero, e.g., 8)

• %H: Hour (24-hour format, e.g., 14)

• %i: Minutes (2 digits, e.g., 30)

• %s: Seconds (2 digits, e.g., 45)

SELECT DATE_FORMAT('2025-01-24', '%d-%b-%Y') AS formatted_date; -- 24-Jan-2025

SELECT DATE_FORMAT(order_date, '%Y-%m') AS year_month --2024-07,2024-08...
FROM orders;

SELECT *
FROM sales
WHERE DATE_FORMAT(sale_date, '%Y-%m') = '2024-07'; --retrives all sales from July 2024

---

Aliases in Sub queries

Aliases provide a temporary name for tables or sub queries, making SQL queries more readable.

SELECT *
FROM (SELECT * FROM MyTable) AS SubQueryAlias;

Example

select count(*) as duplicate_companies
FROM (
  SELECT company_id, count(*) as duplicate_companies
  FROM job_listings
  group by company_id,title,description
  having count(*) > 1
) as duplicates

COUNT() in WHERE Clause?

SELECT customer_id
FROM Customer
GROUP BY customer_id
HAVING COUNT(DISTINCT product_key) = (  --- Right way of using count with having
  SELECT COUNT(DISTINCT product_key) FROM Product
);

---

String Handling in SQL

Manipulating strings is a common task in SQL. Here, we’ll explore how to perform various string operations like changing case, extracting substrings, concatenating strings, and more.

Convert Text to Uppercase (UPPER)

SELECT UPPER('hello world') AS uppercase_result;
-- Output: HELLO WORLD

Convert Text to Lowercase (LOWER)

SELECT LOWER('HELLO WORLD') AS lowercase_result;
-- Output: hello world

Capitalize the First Letter (INITCAP)

SELECT INITCAP('hello world') AS initcap_result;
-- Output: Hello World

Extract Substrings (SUBSTRING)

SELECT SUBSTRING(string, start_position, length); --Syntax

SELECT SUBSTRING('hello world', 1, 5) AS result; --Example
-- Output: hello

string: The input string. start_position: The position (1-based index) to start extracting. length: The number of characters to extract.

Tip: If you only specify the starting position, the substring will include all characters from that point onward:

SELECT SUBSTRING('hello world', 7) AS result;
-- Output: world

Concatenate Strings (CONCAT)

The CONCAT function combines two or more strings into one.

SELECT CONCAT(string1, string2, ..., stringN); -- Syntax

SELECT CONCAT('Hello', ' ', 'World!') AS result; -- Example
-- Output: Hello World!

Common String Handling Functions

Function	Description	Example	Output
UPPER	Converts to uppercase	UPPER('hello')	HELLO
LOWER	Converts to lowercase	LOWER('HELLO')	hello
SUBSTRING	Extracts a portion of a string	SUBSTRING('hello', 2, 3)	ell
CONCAT	Combines multiple strings	CONCAT('Hello', ' ', 'World')	Hello World
TRIM	Removes leading/trailing spaces	TRIM(' hello ')	hello
LENGTH	Returns the length of a string	LENGTH('hello')	5
REPLACE	Replaces part of a string	REPLACE('hello world', 'world', 'SQL')	hello SQL

Understanding GROUP_CONCAT in SQL

The GROUP_CONCAT function in SQL is used to concatenate values from a group into a single string. This is particularly useful when aggregating data from multiple rows into a single row.

Syntax:

`GROUP_CONCAT(expression [ORDER BY expression] [SEPARATOR 'separator'])`

• expression: The column or calculation whose values will be concatenated.

• ORDER BY: Optional. Specifies the order of concatenated values.

• SEPARATOR: Optional. Specifies a custom separator (default is a comma ,).

An example:

group_concat(distinct(product) order by product separator ',')
--- Basketball,Headphone,T-Shirt (All these 3 from same date gets displayed in a single row instead of three)

---