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

Understand the difference: Round Vs Floor

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
Floor Function

The FLOOR() function rounds a number down to the nearest integer. It always rounds towards negative infinity.

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

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 Function

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';

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;

COUNT() in WHERE Clause?

Aggregate functions like COUNT() cannot be directly used in the WHERE clause. Instead, use the HAVING clause after grouping.

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