string manipulation or slicing
https://school.programmers.co.kr/learn/courses/30/lessons/131529
if u wanna lets say get first 2 alphabets for a given string, cannot use floor() or truncate(). We need to use
LEFT('Hello World', 5) -- 'Hello'
LEFT('MySQL', 2) -- 'My'
SUBSTRING('Hello World', 1, 5) -- 'Hello' (start at position 1, take 5 chars)
SUBSTRING('Hello World', 7, 5) -- 'World' (start at position 7, take 5 chars)
SUBSTRING('Hello World', 7) -- 'World' (from position 7 to end)
RIGHT('Hello World', 5) -- 'World'
RIGHT('MySQL', 2) -- 'QL'for substring(), it is 1-indexed so u need to say (1,2) or else if u just say (2) it takes from position 2 until the end.
group by
cast
u need case cuz if like 1/10 it will give 0. But we want decimal as answer so the numerator has to be decimal via 'cast as decimal'
CAST(sum(clicks) AS DECIMAL) / sum(impressions)self join
You have an employees table with columns: employee_id, name, salary, department_id, manager_id (references another employee).
Write a query to find all employees who earn more than their manager
for this kinda case we need to self join the table to get the employee and manager salary side by side to filter via where
SELECT e.name as employee_name, e.salary as employee_salary,
m.name as manager_name, m.salary as manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;
employee_nameemployee_salarymanager_namemanager_salary
Frank95000David90000left join
users table with columns: user_id, name, email
orders table with columns: order_id, user_id, product, price
How would you write a query to find all users who have placed at least one order? What if you wanted to also show users who haven't placed any orders yet?
SELECT user_id, name, email
FROM users
WHERE user_id IN (SELECT DISTINCT user_id FROM orders);
SELECT u.user_id, u.name, u.email, COUNT(o.order_id) as order_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.name, u.email;esp for part 2, if we just use join, it only returns users who have placed at least 1 order. Users without orders are excluded.
But with left join
SELECT u.user_id, u.name, u.email
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id;Result: Returns ALL users from the left table (users), even if they have no matching orders. Users without orders will have NULL values in the order columns.
Example:
- User A has 2 orders → included
- User B has 1 order → included
- User C has 0 orders → included (with NULL for order data) ✓
datetime
if u wanna check if start time is within the month of october. Theres 2 main ways - using string literals like '2022-10-01' or using MONTH() function
SELECT *
FROM your_table
WHERE date_column >= '2022-10-01'
AND date_column < '2022-11-01';
or
SELECT *
FROM your_table
WHERE MONTH(date_column) = 10
AND YEAR(date_column) = 2022;another case of datetime is:
Columns: sale_id, product, amount, sale_date
Write a query to find the total sales for each product, but only for sales made in the last 30 days. Order by total sales descending.
in mysql, there is date_sub, which subtracts time interval from a date
DATE_SUB is a MySQL-specific function that subtracts a time interval from a date.
-- Subtract 30 days from today
DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
-- Subtract 1 year
DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR)
-- Subtract 6 months
DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH)
-- Subtract 2 hours
DATE_SUB(NOW(), INTERVAL 2 HOUR)so for this query, if sale date has a value that is greater than today's date-30 days, then its recent
SELECT product, SUM(amount) as total_sales
FROM sales
WHERE sale_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) -- MySQL
GROUP BY product
ORDER BY total_sales DESC;to get last 30 days
WHERE date >= CURRENT_DATE - INTERVAL 30 DAYformat date to get yy-mm format
DATE_FORMAT(date, '%Y-%m')where
Is keyword is only used to check for null/not null values. For other values, we should use equal (=) operator
also use single quotes ' for string values, not double quotes.
-- Correct ✓
WHERE a.rarity = 'legend'
WHERE a.rarity != 'common'
WHERE a.rarity IS NULL
WHERE a.rarity IS NOT NULL
-- Incorrect ✗
WHERE a.rarity IS 'legend' -- Error!
WHERE a.rarity IS NOT 'legend' -- Error!case when then
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE default_result
ENDwindow function
unlike group by that collapses rows, window function performs calc across rows while keeping ALL rows
syntax:
FUNCTION() OVER (
PARTITION BY column -- Like GROUP BY (optional)
ORDER BY column -- For rankings/running totals (optional)
)partition by means do this calculation separately for each group, but keep all rows
for example
-- GROUP BY (collapses rows)
SELECT department, AVG(salary)
FROM employees
GROUP BY department;
Result: 3 rows (one per department)
-- Window function (keeps all rows)
SELECT name, department, salary,
AVG(salary) OVER (PARTITION BY department) as dept_avg
FROM employees;
Result: 10 rows (all employees, each with their dept average)ROW_NUMBER() assigns unqiue numbers
SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num
FROM employees;Result:
name | salary | row_num
--------|--------|--------
Alice | 100000 | 1
Bob | 95000 | 2
Charlie | 95000 | 3 ← Still gets 3, even if tiedRANK() skips numbers after ties while giving integer rank values
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) as rank
FROM employees;Result:
name | salary | rank
--------|--------|-----
Alice | 100000 | 1
Bob | 95000 | 2
Charlie | 95000 | 2 ← Tied, both get 2
David | 90000 | 4 ← Skips 3!for example, we wanna find second most expensive product
-- ❌ Your attempt
select product_id, rank() over (order by price desc) as rank
from products
where rank=2
-- Problem 1: WHERE can't see aliases from SELECT
-- Problem 2: No PARTITION BY category
-- Problem 3: This would rank ALL products together
-- Example output (if it worked):
-- All categories mixed together:
-- Electronics: $500 (rank 1)
-- Books: $450 (rank 2) ← Wrong! This isn't second in Books category
-- Clothing: $400 (rank 3)❌ Can't use WHERE with window function alias - rank doesn't exist yet when WHERE runs
❌ No PARTITION BY category - you're ranking ALL products together, not per category
❌ Need to handle ties - RANK() can skip numbers (1, 2, 2, 4)
in the inner subquery, we still need to choose product id and name and etc so that we can select them in the main query
correct:
SELECT product_id, name, price, category
FROM (
SELECT product_id, name, price, category,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) as rank
FROM products
) ranked
WHERE rank = 2;
or subquery via
SELECT p1.product_id, p1.name, p1.price, p1.category
FROM products p1
WHERE (
SELECT COUNT(DISTINCT p2.price)
FROM products p2
WHERE p2.category = p1.category
AND p2.price > p1.price
) = 1;DENSE_RANK() - no gaps in ranks
SELECT name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
FROM employees;Result:
name | salary | dense_rank
--------|--------|------------
Alice | 100000 | 1
Bob | 95000 | 2
Charlie | 95000 | 2 ← Tied, both get 2
David | 90000 | 3 ← No gap!creating index
to speed up read queries, for example this
SELECT text FROM searchedphrases WHERE text LIKE 'input_text%' ORDER BY count DESCwe wanna create composite index including both the text and count columns, with the text column listed first. the ON keyword specifies the table to which the new index belongs.
CREATE INDEX idx_searchedphrases_text_count ON searchedphrases (text, count DESC);