[MySQL] LeetCode 문제 연습 10

196. Delete Duplicate Emails (Easy)

• Table: Person
  +-------------+---------+
  | Column Name | Type |
  +-------------+---------+
  | id | int |
  | email | varchar |
  +-------------+---------+
  id is the primary key (column with unique values) for this table.
  Each row of this table contains an email. The emails will not contain uppercase letters.
  
  Write a solution to delete all duplicate emails, keeping only one unique email with the smallest id.
  
  For SQL users, please note that you are supposed to write a DELETE statement and not a SELECT one.
  
  For Pandas users, please note that you are supposed to modify Person in place.
  
  After running your script, the answer shown is the Person table. The driver will first compile and run your piece of code and then show the Person table. The final order of the Person table does not matter.
  
  

제출한 쿼리 :

DELETE p1
FROM Person p1
JOIN Person p2
ON p1.email = p2.email AND p1.id > p2.id;

---

176. Second Highest Salary (Medium)

• Table: Employee
  +-------------+------+
  | Column Name | Type |
  +-------------+------+
  | id | int |
  | salary | int |
  +-------------+------+
  id is the primary key (column with unique values) for this table.
  Each row of this table contains information about the salary of an employee.
  
  Write a solution to find the second highest salary from the Employee table. If there is no second highest salary, return null (return None in Pandas).
  
  

제출한 쿼리 :

SELECT (SELECT DISTINCT salary
        FROM Employee
        ORDER BY salary DESC
        LIMIT 1 OFFSET 1
       ) AS SecondHighestSalary;

---

1484. Group Sold Products By The Date (Easy)

• Table Activities:
  +-------------+---------+
  | Column Name | Type |
  +-------------+---------+
  | sell_date | date |
  | product | varchar |
  +-------------+---------+
  There is no primary key (column with unique values) for this table. It may contain duplicates.
  Each row of this table contains the product name and the date it was sold in a market.
  
  Write a solution to find for each date the number of different products sold and their names.
  
  The sold products names for each date should be sorted lexicographically.
  
  Return the result table ordered by sell_date.
  
  

제출한 쿼리 :

SELECT sell_date
       , COUNT(DISTINCT product) AS num_sold
       , GROUP_CONCAT(DISTINCT product ORDER BY product SEPARATOR ',') AS products
FROM Activities
GROUP BY sell_date
ORDER BY sell_date;

---

1327. List the Products Ordered in a Period (Easy)

• Table: Products
  +------------------+---------+
  | Column Name | Type |
  +------------------+---------+
  | product_id | int |
  | product_name | varchar |
  | product_category | varchar |
  +------------------+---------+
  product_id is the primary key (column with unique values) for this table.
  This table contains data about the company's products.
  
  Table: Orders
  +---------------+---------+
  | Column Name | Type |
  +---------------+---------+
  | product_id | int |
  | order_date | date |
  | unit | int |
  +---------------+---------+
  This table may have duplicate rows.
  product_id is a foreign key (reference column) to the Products table.
  unit is the number of products ordered in order_date.
  
  Write a solution to get the names of products that have at least 100 units ordered in February 2020 and their amount.
  
  Return the result table in any order.
  
  

제출한 쿼리 :

SELECT p.product_name
       , SUM(o.unit) AS unit
FROM Orders o
JOIN Products p ON o.product_id = p.product_id
WHERE o.order_date >= '2020-02-01' 
    AND o.order_date < '2020-03-01'
GROUP BY p.product_name
HAVING SUM(o.unit) >= 100;

---

1517. Find Users With Valid E-Mails (Easy)

• Table: Users
  +---------------+---------+
  | Column Name | Type |
  +---------------+---------+
  | user_id | int |
  | name | varchar |
  | mail | varchar |
  +---------------+---------+
  user_id is the primary key (column with unique values) for this table.
  This table contains information of the users signed up in a website. Some e-mails are invalid.
  
  Write a solution to find the users who have valid emails.
  
  A valid e-mail has a prefix name and a domain where:
  • The prefix name is a string that may contain letters (upper or lower case), digits, underscore '_', period '.', and/or dash '-'. The prefix name must start with a letter.
  • The domain is '@leetcode.com'

      Return the result table in any order.

제출한 쿼리 :

SELECT *
FROM Users
WHERE mail REGEXP '^[A-Za-z][A-Za-z0-9_\.\-]*@leetcode(\\?com)?\\.com$';

---