πŸ’Ύ 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. 🌟

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;

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)