SQL Problems

Made by Mike_Zhang

数据结构与算法题主题：

Recyclable and Low Fat Products

LeetCode Problem #1757

1
2
3
4
5
6
7
8
9
10
SELECT product_id
FROM Products
WHERE low_fats = 'Y';

INTERSECT

SELECT product_id
FROM Products
WHERE recyclable = 'Y';

Big Countries

LeetCode Problem #595

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- M2 Using UNION
SELECT name, population, area
FROM World
WHERE area >= 3000000;

UNION

SELECT name, population, area
FROM World
WHERE population >= 25000000;


-- M1 Using OR
-- SELECT name, population, area
-- FROM World
-- WHERE area >= 3000000 OR population >= 25000000;

Find Customer Referee

LeetCode Problem #584

1
2
3
SELECT name
FROM Customer
WHERE NVL(referee_id,0) != 2;

2022-10-25

Calculate Special Bonus

LeetCode Problem #1873

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- SELECT employee_id , salary as bouns
-- FROM Employees
-- WHERE MOD(employee_id,2)!=0 AND name NOT LIKE 'M%'

-- UNION

-- SELECT employee_id , 0 as bouns
-- FROM Employees
-- WHERE MOD(employee_id,2)=0 OR name LIKE 'M%'
-- ORDER BY employee_id;


select employee_id , salary as bonus 
from employees 
where MOD(employee_id,2)!=0 and name not like 'M%'

union

select employee_id , 0 as bonus
from employees
where MOD(employee_id,2)=0 or name like 'M%'
order by employee_id;

2022-10-25

Swap Salary

LeetCode Problem #627

1
2
UPDATE Salary 
SET sex = if(sex='f','m','f');

2022-10-26

Article Views I

LeetCode Problem #1148

1
2
3
4
5
# Write your MySQL query statement below
SELECT DISTINCT author_id AS id
FROM Views
WHERE author_id = viewer_id
ORDER BY author_id;

2022-10-27

Daily Leads and Partners

LeetCode Problem #1693

1
2
3
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;

2022-10-28

Find Followers Count

LeetCode Problem #1729

1
2
3
4
SELECT user_id, COUNT(*) AS followers_count
FROM Followers
GROUP BY user_id
ORDER BY user_id;

2022-10-29

Game Play Analysis I

LeetCode Problem #511

1
2
3
SELECT player_id, MIN(event_date) AS first_login
FROM Activity
GROUP BY player_id;

2022-10-30

Duplicate Emails

LeetCode Problem #182

1
2
3
4
SELECT email AS Email
FROM Person
GROUP BY email
HAVING COUNT(*)>1;

2022-10-31

Duplicate Emails

LeetCode Problem #182

1
2
3
4
SELECT email AS Email
FROM Person
GROUP BY email
HAVING COUNT(*)>1;

2022-10-31

Customer Who Visited but Did Not Make Any Transactions

LeetCode Problem #1581

1
2
3
4
SELECT customer_id, COUNT(v.visit_id) AS count_no_trans 
FROM Visits v NATURAL LEFT JOIN Transactions t
WHERE t.visit_id IS NULL
GROUP BY customer_id;

2022-11-01

Combine Two Tables

LeetCode Problem #175

1
2
3
SELECT firstName, lastName, city, state
FROM Person p LEFT JOIN Address a
ON p.personId = a.personId;

2022-11-02

Market Analysis I

LeetCode Problem #1158

1
2
3
4
SELECT u.user_id AS 'buyer_id', u.join_date AS 'join_date', count(o.order_id) AS 'orders_in_2019'
FROM Users u LEFT JOIN Orders o 
    ON u.user_id = o.buyer_id AND YEAR(order_date) = '2019'
GROUP BY u.user_id;

2022-11-03

Sales Person

LeetCode Problem #607

1
2
3
4
5
6
7
8
9
10
11
SELECT sp.name AS 'name'
FROM SalesPerson sp
WHERE sp.sales_id NOT IN (
    SELECT o.sales_id
    FROM Orders o
    WHERE o.com_id IN (
        SELECT c.com_id
        FROM Company c
        WHERE c.name = 'RED'
    )
);

2022-11-04

Customers Who Never Order

LeetCode Problem #183

1
2
3
4
5
6
SELECT name AS Customers
FROM Customers
WHERE id NOT IN (
    SELECT customerId
    FROM Orders
);

2022-11-05

Customer Placing the Largest Number of Orders

LeetCode Problem #586

1
2
3
4
5
6
7
8
SELECT customer_number
FROM Orders
GROUP BY customer_number
HAVING COUNT(*) >= ALL(
    SELECT COUNT(*)
    FROM Orders
    GROUP BY customer_number
);

2022-11-05

Top Travellers

LeetCode Problem #1407

1
2
3
4
5
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;

2022-11-06

Employees With Missing Information

LeetCode Problem #1965

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT employee_id
FROM Employees
WHERE employee_id NOT IN (
    SELECT employee_id
    FROM Salaries
)
UNION
SELECT employee_id
FROM Salaries
WHERE employee_id NOT IN (
    SELECT employee_id
    FROM Employees
)
ORDER BY employee_id;

2022-11-06

Employees Earning More Than Their Managers

LeetCode Problem #181

1
2
3
SELECT e.name AS Employee
FROM Employee e, Employee m
WHERE e.managerId = m.id AND e.salary > m.salary;

2022-11-07

Delete Duplicate Emails

LeetCode Problem #196

1
2
3
4
5
6
7
8
9
DELETE FROM Person
WHERE id NOT IN 
    (SELECT * FROM(
        SELECT MIN(id)
        FROM Person
        GROUP BY email
    )AS temp);
-- need to have a outer SELECT to create a new table, 
-- otherwise it wll delete from itself, error

2022-11-08

Rising Temperature

LeetCode Problem #197

1
2
3
4
5
6
7
8
# Write your MySQL query statement below
SELECT w.id
FROM Weather w
WHERE w.temperature > (
    SELECT e.temperature
    FROM Weather e
    WHERE DATEDIFF(w.recordDate, e.recordDate) = 1
    );
1
2
3
4
5
6
7
8
SELECT
    weather.id AS 'Id'
FROM
    weather
        JOIN
    weather w ON DATEDIFF(weather.recordDate, w.recordDate) = 1
        AND weather.Temperature > w.Temperature
;

2022-11-09

Game Play Analysis II

LeetCode Problem #512

1
2
3
4
5
6
7
SELECT player_id, device_id
FROM Activity
WHERE (player_id,event_date) IN (
    SELECT player_id, MIN(event_date)
    FROM Activity
    GROUP BY player_id
);

2022-11-11

Employee Bonus

LeetCode Problem #577

1
2
3
4
SELECT name, bonus
FROM Employee e LEFT JOIN Bonus b
ON e.empId = b.empId
WHERE bonus iS NULL OR bonus < 1000;

2022-11-12

Classes More Than 5 Students

LeetCode Problem #596

1
2
3
4
SELECT class
FROM Courses
GROUP BY class
HAVING COUNT(class)>=5;

2022-11-12

Consecutive Available Seats

LeetCode Problem #603

1
2
3
4
5
SELECT DISTINCT a.seat_id
FROM Cinema a JOIN Cinema b
ON (a.seat_id - b.seat_id = 1 OR b.seat_id - a.seat_id = 1)
    AND (a.free = true AND b.free = true)
ORDER BY a.seat_id;

2022-11-14

Triangle Judgement

LeetCode Problem #610

IF

1
2
SELECT *,IF(x+y>z AND x+z>y AND y+z>x, 'Yes', 'No') AS 'triangle'
FROM triangle;

CASE

1
2
3
4
5
6
7
SELECT *, (CASE 
             WHEN x + y > z AND 
                  x + z > y AND 
                  y + z > x THEN 'Yes'
             ELSE 'No'
           END) AS 'triangle'
FROM triangle;

UNION

1
2
3
4
5
6
7
8
-- WA...
SELECT x,y,z,'No' AS 'triangle'
FROM Triangle
WHERE x+y<=z OR x+z<=y OR y+z<=x
UNION
SELECT x,y,z,'Yes' AS 'triangle'
FROM Triangle
WHERE x+y>z AND x+z>y AND y+z>x;

2022-11-15

Not Boring Movies

LeetCode Problem #620

1
2
3
4
5
SELECT *
FROM Cinema
WHERE (id % 2) = 1
        AND description != 'boring'
ORDER BY rating DESC;

2022-11-16

Biggest Single Number

LeetCode Problem #619

1
2
3
4
5
6
SELECT MAX(num) as num
FROM (
    SELECT num
    FROM MyNumbers m
    GROUP BY num
    HAVING COUNT(*) = 1) AS t; -- Every derived table must have its own alias

2022-11-17

Shortest Distance in a Line

LeetCode Problem #613

1
2
3
SELECT MIN(ABS(A.x-B.X)) AS shortest
FROM Point A LEFT JOIN Point B 
    ON A.x < B.x;

2022-11-18

Sales Analysis I

LeetCode Problem #1082

1
2
3
4
5
6
7
8
9
10
11
WITH T AS (
    SELECT seller_id as id, SUM(price) AS s
    FROM Sales
    GROUP BY seller_id
)

SELECT id AS seller_id
FROM T
WHERE s = (
    SELECT MAX(s) FROM T
);

OR

1
2
3
4
5
6
7
8
9
10
11
SELECT id AS seller_id
FROM (
    SELECT seller_id as id, SUM(price) AS s
    FROM Sales
    GROUP BY seller_id
) AS T
WHERE s >= ALL(
    SELECT SUM(price) AS s
    FROM Sales
    GROUP BY seller_id
);

2022-11-19

Sales Analysis II

LeetCode Problem #1083

1
2
3
4
5
SELECT b.buyer_id
FROM Product AS a JOIN Sales AS b
ON a.product_id = b.product_id 
GROUP BY b.buyer_id 
HAVING SUM(a.product_name = 'S8') > 0 and SUM(a.product_name = 'iPhone') = 0;

2022-11-20

Last updated on 2022-11-25

References

Database - LeetCode

写在最后

SQL, Database相关的知识会继续学习，继续更新.
最后，希望大家一起交流，分享，指出问题，谢谢！

原创文章，转载请标明出处
Made by Mike_Zhang




感谢你的支持
Data Structure and Algorithm > Problem Set
#Java #SQL
SQL Problems
https://ultrafish.io/post/sql-problems/
Author
Mike_Zhang
Posted on
November 25, 2022
Licensed under