MySQL Notes

Short Notes Found while Practice in MySQL

1. Find Even Numbers: (Cannot use % in SQL) : MOD

WHERE MOD(ID,2) = 0;

2. Subtraction Operation in SQL: COUNT, DISTINCT

 SELECT COUNT(CITY) - COUNT(DISTINCT CITY)

3. String Length: LENGTH

SELECT LENGTH(CITY)

4. Using AS Variable: AS

SELECT LENGTH(CITY) AS CITY_LENGTH

5. Return Only the Shortest CITY Name (1 Row Only): LIMIT

SELECT CITY , LENGTH(CITY) AS CITY_LENGTH
FROM STATION
ORDER BY CITY_LENGTH ASC,CITY ASC
LIMIT 1;

6. Return Only the Longest CITY Name (1 Row Only): LIMIT

SELECT CITY , LENGTH(CITY) AS CITY_LENGTH
FROM STATION
ORDER BY CITY_LENGTH DESC, CITY ASC
LIMIT 1;

7. Match Null Value: IS NULL or IS NOT NULL

SELECT name FROM Customer
WHERE referee_id != 2 OR referee_id IS NULL

8. Not equal operator : <>, !=

SELECT name FROM Customer
WHERE referee_id <> 2 ; // Id is not equal 2

9. Left Join

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders).

Leet Code Question: 1378. Replace Employee ID With The Unique Identifier

Write a solution to show the unique ID of each user, if a user does not have a unique ID replace just show null.

Select EmployeeUNI.unique_id , Employees.name
from Employees
LEFT JOIN EmployeeUNI
On Employees.id = EmployeeUNI.id;

Leet Code Question: 577. Employee Bonus

Write a solution to report the name and bonus amount of each employee with a bonus less than 1000. ( But if a employee dont have empId in Bonus Table then show Bonus = NULL)

select Employee.name , Bonus.bonus
from Employee
LEFT JOIN Bonus
on Employee.empId = Bonus.empId
where Bonus.bonus < 1000 or Bonus.bonus is null ; 
//( If dont found any empId in Bonus, SQL Automatically assign null to it)

Leet Code Question: 175. Combine Two Tables

Write a solution to report the first name, last name, city, and state of each person in the Person table. If the address of a personId is not present in the Address table, report null instead.

select 
    P.firstName as firstName,
    P.lastName as lastName,
    A.city as city,
    A.state as state
from Person P
LEFT JOIN Address A
On P.personId = A.personId;

10. Inner Join

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

JOIN Three Tables

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are records in the "Orders" table that do not have matches in "Customers", these orders will not be shown!

Leet Code Question: 1068. Product Sales Analysis I

Write a solution to report the product_name, year, and price for each sale_id in the Sales table

Select Product.product_name , Sales.year ,Sales.price
from Sales
INNER Join Product
On Sales.product_id = Product.product_id;

11. GROUP BY

The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

The following SQL statement lists the number of customers in each country, sorted high to low:

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

GROUP BY With JOIN Example: The following SQL statement lists the number of orders sent by each shipper:

SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
LEFT JOIN Shippers 
ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;

Given the CITY and COUNTRY tables, query the names of all the continents (COUNTRY.Continent) and their respective average city populations (CITY.Population) rounded down to the nearest integer.

SELECT COUNTRY.CONTINENT, FLOOR(AVG(CITY.POPULATION))
FROM CITY
INNER JOIN COUNTRY
ON CITY.COUNTRYCODE = COUNTRY.CODE
GROUP BY COUNTRY.CONTINENT;

GROUP BY multiple values

1693. Daily Leads and Partners: For each date_id and make_name, find the number of distinct lead_id's and distinct partner_id's.

SELECT 
    date_id, make_name, 
    COUNT(DISTINCT lead_id) AS unique_leads, 
    COUNT(DISTINCT partner_id) AS unique_partners 
FROM 
    DailySales 
GROUP BY 
    date_id, make_name

11. HAVING

Note: The HAVING clause was introduced because the WHERE clause does not support aggregate functions. Also, GROUP BY must be used before the HAVING clause.
The SQL HAVING clause is used if we need to filter the result set based on aggregate functions such as MIN() and MAX(), SUM() and AVG(), and COUNT().

SELECT AggFunc(column), extra_columns
FROM table
GROUP BY target_column
HAVING condition

596. Classes More Than 5 Students: Write a solution to find all the classes that have at least five students.

SELECT class
FROM Courses
GROUP BY class
HAVING COUNT(student) >= 5;

1084. Sales Analysis III : Write a solution to report the products that were only sold in the first quarter of 2019. That is, between 2019-01-01 and 2019-03-31 inclusive.

select P.product_id as product_id, P.product_name as product_name
from Product P
INNER JOIN Sales S
on P.product_id = S.product_id
GROUP BY s.product_id
HAVING 
    MIN(s.sale_date) >= '2019-01-01' AND
    MAX(s.sale_date) <= '2019-03-31';

12. MySQL | Regular expressions (Regexp)

MySQL | Regular expressions (Regexp) - GeeksforGeeksGeeksforGeeks

Weather Observation Station 6: Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates

Match beginning of string (^): ^[aeiouAEIOU]

SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '^[aeiouAEIOU]'

Weather Observation Station 7: Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION. Your result cannot contain duplicates.

Match the end of a string ($): '[aeiouAEIOU]$'

SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '[aeiouAEIOU]$'

Weather Observation Station 9: Query the list of CITY names from STATION that do not start with vowels. Your result cannot contain duplicates.

[^abc]

Any character not listed between the square brackets

SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '^[^aeiouAEIOU]'

13. Useful Resources

197. Rising Temperature: Write a solution to find all dates' Id with higher temperatures compared to its previous dates (yesterday).

Select w1.id
from Weather w1, Weather w2
Where DATEDIFF(w1.recordDate , w2.recordDate) = 1 
AND w1.temperature > w2.temperature;

Draw A Triangle : P(R) represents a pattern drawn by Julia in R rows. The following pattern represents P(5):

* * * * * 
* * * * 
* * * 
* * 
*

Write a query to print the pattern P(20).

WITH RECURSIVE Pattern AS (
    -- Base case: start with 20 stars
    SELECT 20 AS row_num, REPEAT('* ', 20) AS stars
    UNION ALL
    -- Recursive case: create the next row with one less star
    SELECT row_num - 1, REPEAT('* ', row_num - 1)
    FROM Pattern
    WHERE row_num > 1 -- Stop when row_num reaches 1
)
-- Select the stars column to display the pattern
SELECT stars
FROM Pattern;

14. MySQL SUBSTR() Function

SELECT SUBSTR("SQL Tutorial", 5, 3) AS ExtractString;
// extract a substring from a string (start at position 5, extract 3 characters):

SELECT SUBSTR("SQL Tutorial", 5) AS ExtractString;
// extract a substring from a  (start at position 5, extract rest of all characters):

Question: Hacker Rank - Higher Than 75 Marks

Query the Name of any student in STUDENTS who scored higher than Marks. Order your output by the last three characters of each name. If two or more students both have names ending in the same last three characters (i.e.: Bobby, Robby, etc.), secondary sort them by ascending ID.

SELECT Name
FROM STUDENTS
WHERE Marks > 75
ORDER BY SUBSTR(Name, -3), ID;

15. CONCAT() , UPPER() , LOWER()

1667. Fix Names in a Table : Write a solution to fix the names so that only the first character is uppercase and the rest are lowercase.

SELECT user_id ,
    CONCAT(
        UPPER( SUBSTR(name , 1, 1)),
        LOWER( SUBSTR(name , 2))
    ) AS name 
FROM Users
ORDER BY Users.user_id ASC;

16. IFNULL, ROUND, SUM, BETWEEN, AND

Prices table:
+------------+------------+------------+--------+
| product_id | start_date | end_date   | price  |
+------------+------------+------------+--------+
| 1          | 2019-02-17 | 2019-02-28 | 5      |
| 1          | 2019-03-01 | 2019-03-22 | 20     |
| 2          | 2019-02-01 | 2019-02-20 | 15     |
| 2          | 2019-02-21 | 2019-03-31 | 30     |
+------------+------------+------------+--------+
UnitsSold table:
+------------+---------------+-------+
| product_id | purchase_date | units |
+------------+---------------+-------+
| 1          | 2019-02-25    | 100   |
| 1          | 2019-03-01    | 15    |
| 2          | 2019-02-10    | 200   |
| 2          | 2019-03-22    | 30    |
+------------+---------------+-------+
Output: 
+------------+---------------+
| product_id | average_price |
+------------+---------------+
| 1          | 6.96          |
| 2          | 16.96         |
+------------+---------------+
Explanation: 
Average selling price = Total Price of Product / Number of products sold.
Average selling price for product 1 = ((100 * 5) + (15 * 20)) / 115 = 6.96
Average selling price for product 2 = ((200 * 15) + (30 * 30)) / 230 = 16.96

1251. Average Selling Price : Write a solution to find the average selling price for each product. average_price should be rounded to 2 decimal places.

SELECT p.product_id,
IFNULL(ROUND(SUM(us.units * p.price) / SUM(us.units), 2),0) AS average_price
FROM Prices p
LEFT JOIN UnitsSold us
ON us.product_id = p.product_id
AND us.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY p.product_id;

Round the number to 2 decimal places:

SELECT ROUND(235.415, 2) AS RoundValue;

Return the specified value IF the expression is NULL, otherwise return the expression: if Value == NULL return W3Schools.com else returns Value

SELECT IFNULL(Value, "W3Schools.com");

SELECT IFNULL(ROUND(SUM(us.units * p.price) / SUM(us.units), 2),0)

Selects all products with a price between 10 and 20:

SELECT * FROM Products 
WHERE Price BETWEEN 10 AND 20;

17. FLOOR() , CEILING()

Return the largest integer value that is equal to or less than 25.75: (Return 25)

SELECT FLOOR(25.75) AS FloorValue;

Return the smallest integer value that is greater than or equal to a number: (Return 26)

SELECT CEILING(25.75) AS CeilValue;

18. SUBQUERY:

1978. Employees Whose Manager Left the Company: Find the IDs of the employees whose salary is strictly less than $30000 and whose manager left the company. When a manager leaves the company, their information is deleted from the Employees table, but the reports still have their manager_id set to the manager that left.

SELECT employee_id
FROM Employees
Where salary < 30000 
AND manager_id NOT IN (
    select employee_id
    from Employees
);

Find employees who earn more than the average salary of their respective departments.

SELECT name
FROM employees e1
WHERE salary > (SELECT AVG(salary)
                FROM employees e2
                WHERE e1.department_id = e2.department_id);

List the names of customers who have placed more than 5 orders.

ELECT customer_name
FROM customers c
WHERE (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) > 5;

Percentage Between Two Table

Table: Users

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| user_name   | varchar |
+-------------+---------+

Table: Register

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| contest_id  | int     |
| user_id     | int     |
+-------------+---------+

Write a solution to find the percentage of the users registered in each contest rounded to two decimals. Return the result table ordered by percentage in descending order. In case of a tie, order it by contest_id in ascending order.

SELECT contest_id,
ROUND(COUNT(distinct user_id)*100 / 
    (SELECT COUNT(user_id)
     FROM Users) ,2) as percentage
FROM Register
GROUP BY contest_id
ORDER BY percentage desc,contest_id asc;

19.SQL Condition: CASE or IF

Question : The Report

Question Link : The Report

You are given two tables: Students and Grades. Students contains three columns ID, Name and Marks.

Grades contains the following data:

Ketty gives Eve a task to generate a report containing three columns: Name, Grade and Mark. Ketty doesn't want the NAMES of those students who received a grade lower than 8. The report must be in descending order by grade -- i.e. higher grades are entered first. If there is more than one student with the same grade (8-10) assigned to them, order those particular students by their name alphabetically. Finally, if the grade is lower than 8, use "NULL" as their name and list them by their grades in descending order. If there is more than one student with the same grade (1-7) assigned to them, order those particular students by their marks in ascending order.

Write a query to help Eve.

Sample Input

Sample Output

Maria 10 99
Jane 9 81
Julia 9 88 
Scarlet 8 78
NULL 7 63
NULL 7 68

Note

Print "NULL" as the name if the grade is less than 8.

Case Approach:

select
  case
    when g.grade < 8 then null
    else s.name
  end,
  g.grade,
  s.marks
from students as s
join grades as g
on s.marks >= g.min_mark and s.marks <= g.max_mark
order by g.grade desc, s.name asc

IF Approach:

SELECT 
    IF(GRADES.GRADE>=8, 
            STUDENTS.NAME,
            NULL),
    GRADES.GRADE, 
    STUDENTS.MARKS
FROM GRADES, STUDENTS
WHERE STUDENTS.MARKS BETWEEN GRADES.MIN_MARK AND GRADES.MAX_MARK
ORDER BY GRADES.GRADE DESC, STUDENTS.NAME;

Case Use case : 1179. Reformat Department Table

Table: Department

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| revenue     | int     |
| month       | varchar |
+-------------+---------+
In SQL,(id, month) is the primary key of this table.
The table has information about the revenue of each department per month.
The month has values in ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"].

Reformat the table such that there is a department id column and a revenue column for each month.

Example 1:

Input: 
Department table:
+------+---------+-------+
| id   | revenue | month |
+------+---------+-------+
| 1    | 8000    | Jan   |
| 2    | 9000    | Jan   |
| 3    | 10000   | Feb   |
| 1    | 7000    | Feb   |
| 1    | 6000    | Mar   |
+------+---------+-------+
Output: 
+------+-------------+-------------+-------------+-----+-------------+
| id   | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
+------+-------------+-------------+-------------+-----+-------------+
| 1    | 8000        | 7000        | 6000        | ... | null        |
| 2    | 9000        | null        | null        | ... | null        |
| 3    | null        | 10000       | null        | ... | null        |
+------+-------------+-------------+-------------+-----+-------------+
Explanation: The revenue from Apr to Dec is null.
Note that the result table has 13 columns (1 for the department id + 12 for the months).

select id, 
	sum(case when month = 'jan' then revenue else null end) as Jan_Revenue,
	sum(case when month = 'feb' then revenue else null end) as Feb_Revenue,
	sum(case when month = 'mar' then revenue else null end) as Mar_Revenue,
	sum(case when month = 'apr' then revenue else null end) as Apr_Revenue,
	sum(case when month = 'may' then revenue else null end) as May_Revenue,
	sum(case when month = 'jun' then revenue else null end) as Jun_Revenue,
	sum(case when month = 'jul' then revenue else null end) as Jul_Revenue,
	sum(case when month = 'aug' then revenue else null end) as Aug_Revenue,
	sum(case when month = 'sep' then revenue else null end) as Sep_Revenue,
	sum(case when month = 'oct' then revenue else null end) as Oct_Revenue,
	sum(case when month = 'nov' then revenue else null end) as Nov_Revenue,
	sum(case when month = 'dec' then revenue else null end) as Dec_Revenue
from department
group by id
order by id

20.SQL Alternative instead of Using Join (From, Where)

SQL Query using JOIN

To retrieve a list of employees along with their respective department names, you can use an INNER JOIN:

SELECT employees.employee_id,departments.department_name
FROM employees
INNER JOIN departments 
ON employees.department_id = departments.department_id;

SQL Query using WHERE and FROM

The same result can be achieved using the WHERE clause to specify the join condition:

SELECT employees.employee_id,departments.department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

21. IFNULL(NULL, 0) && Order By on total distance

1407. Top Travellers: Return the result table ordered by travelled_distance in descending order, if two or more users traveled the same distance, order them by their name in ascending order.

Input: 
Users table:
+------+-----------+
| id   | name      |
+------+-----------+
| 1    | Alice     |
| 2    | Bob       |
| 3    | Alex      |
| 4    | Donald    |
| 7    | Lee       |
| 13   | Jonathan  |
| 19   | Elvis     |
+------+-----------+
Rides table:
+------+----------+----------+
| id   | user_id  | distance |
+------+----------+----------+
| 1    | 1        | 120      |
| 2    | 2        | 317      |
| 3    | 3        | 222      |
| 4    | 7        | 100      |
| 5    | 13       | 312      |
| 6    | 19       | 50       |
| 7    | 7        | 120      |
| 8    | 19       | 400      |
| 9    | 7        | 230      |
+------+----------+----------+
Output: 
+----------+--------------------+
| name     | travelled_distance |
+----------+--------------------+
| Elvis    | 450                |
| Lee      | 450                |
| Bob      | 317                |
| Jonathan | 312                |
| Alex     | 222                |
| Alice    | 120                |
| Donald   | 0                  | - > IF NULL USE CASE
+----------+--------------------+

Select U.name, IFNULL(sum(R.distance),0) as travelled_distance
From Users U
LEFT JOIN Rides R
ON U.id = R.user_id
GROUP BY R.user_id
ORDER BY travelled_distance DESC, U.name ASC;

22. UNION & UINON ALL

The UNION operator is used to combine the result-set of two or more SELECT statements.

SELECT City 
FROM Customers
UNION
SELECT City 
FROM Suppliers
ORDER BY City;

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL

1795. Rearrange Products Table: Write a solution to rearrange the Products table so that each row has (product_id, store, price). If a product is not available in a store, do not include a row with that product_id and store combination in the result table.

Input: 
Products table:
+------------+--------+--------+--------+
| product_id | store1 | store2 | store3 |
+------------+--------+--------+--------+
| 0          | 95     | 100    | 105    |
| 1          | 70     | null   | 80     |
+------------+--------+--------+--------+
Output: 
+------------+--------+-------+
| product_id | store  | price |
+------------+--------+-------+
| 0          | store1 | 95    |
| 0          | store2 | 100   |
| 0          | store3 | 105   |
| 1          | store1 | 70    |
| 1          | store3 | 80    |
+------------+--------+-------+

SELECT product_id, 'store1' as store, store1 AS price 
FROM products
WHERE store1 IS NOT NULL
UNION
SELECT product_id, 'store2' as store, store2 AS price 
FROM products
WHERE store2 IS NOT NULL
UNION
SELECT product_id, 'store3' as store, store3 AS price 
FROM products
WHERE store3 IS NOT NULL

23. FULL OUTER JOIN & COALESCE()

The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.

The COALESCE() function returns the first non-null value in a list.

1965. Employees With Missing Information : Write a solution to report the IDs of all the employees with missing information. The information of an employee is missing if:

The employee's name is missing, or
The employee's salary is missing.

Return the result table ordered by employee_id in ascending order.

SELECT 
    COALESCE(E.employee_id, S.employee_id) as employee_id
FROM 
    Employees E
FULL OUTER JOIN 
    Salaries S
ON 
    E.employee_id = S.employee_id
WHERE 
    E.employee_id IS NULL 
    OR S.employee_id IS NULL
ORDER BY 
    employee_id;

PreviousDatabase : SQL & NO-SQL NextCommon SQL Questions

Last updated 1 year ago