SQL
Select
1. output specific content of the table with conditions
select content
from table
where condition
#if the condition has specific content
#example:
Where exp = 'Y'
For example:
Big country
Link: https://leetcode.cn/problems/big-countries/
Problem Description:
A country is big if:
it has an area of at least three million (i.e., 3000000 km2), or
it has a population of at least twenty-five million (i.e., 25000000).
Write an SQL query to report the name, population, and area of the big countries.
Return the result table in any order.
# Write your MySQL query statement below
Select name, population, area
From World
Where area>=3000000 or population>=25000000;
2. two table joint search
2.1Customers Who Never Order
Link: https://leetcode.com/problems/customers-who-never-order/
Problem Description:
Table: Customers
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
id is the primary key column for this table.
Each row of this table indicates the ID and name of a customer.
Table: Orders
+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| customerId | int |
+-------------+------+
id is the primary key column for this table.
customerId is a foreign key of the ID from the Customers table.
Each row of this table indicates the ID of an order and the ID of the customer who ordered it.
Write an SQL query to report all customers who never order anything.
Return the result table in any order.
# Write your MySQL query statement below
select name as Customers
from Customers
# get the other customers
where id not in
# get thte customer id exists in the order table
(select customerId from Orders)
Select & Order
3. If
3.1 Calculate Special Bonus
Link: https://leetcode.com/problems/calculate-special-bonus/
Problem Description:
Table: Employees
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| employee_id | int |
| name | varchar |
| salary | int |
+-------------+---------+
employee_id is the primary key for this table.
Each row of this table indicates the employee ID, employee name, and salary.
Write an SQL query to calculate the bonus of each employee. The bonus of an employee is 100% of their salary if the ID of the employee is an odd number and the employee name does not start with the character 'M'. The bonus of an employee is 0 otherwise.
Return the result table ordered by employee_id.
# Write your MySQL query statement below
select employee_id,
#if(condition, exp1, exp2)
if(employee_id%2=0 or name like 'M%', 0, salary) as bonus
From Employees
#output order
order by employee_id
output order
order by
#Ascending
ORDER BY column1 ASC,
#descending
ORDER BY column1 DESC;
4. Update the table
update table.name column = condition
4.1 Swap Salary
Link: https://leetcode.cn/problems/swap-salary/
Problem Description:
Table: Salary
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| id | int |
| name | varchar |
| sex | ENUM |
| salary | int |
+-------------+----------+
id is the primary key for this table.
The sex column is ENUM value of type ('m', 'f').
The table contains information about an employee.
Write an SQL query to swap all 'f' and 'm' values (i.e., change all 'f' values to 'm' and vice versa) with a single update statement and no intermediate temporary tables.
Note that you must write a single update statement, do not write any select statement for this problem.
# Write your MySQL query statement below
update Salary set sex=if(sex = 'f', 'm', 'f')
5. Delete content
update table.name column = condition
5.1 Delete Duplicate Emails
Link: https://leetcode.com/problems/delete-duplicate-emails/
Problem Description:
Table: Person
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| email | varchar |
+-------------+---------+
id is the primary key column for this table.
Each row of this table contains an email. The emails will not contain uppercase letters.
Write an SQL query to delete all the duplicate emails, keeping only one unique email with the smallest id. Note that you are supposed to write a DELETE statement and not a SELECT one.
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.
# Please write a DELETE statement and DO NOT write a SELECT statement.
# Write your MySQL query statement below
# create two sub table
# delete the p1, so process the p1
Delete p1 From Person p1, Person p2
#traverse table
#when p1's email = p2, and p1's id >p2 so get the minimum id
where p1.email = p2.email and p1.id > p2.id
String
Method
1.CONCAT()
merge the multiple substring
2. LEFT(Str, length)
str is the string, the length is length of string it cutted from the left side
3.SUBSTRING(Str, begin,end)
if don’t fill end parameter, it will cut the substring from pointed element to the end of the string. the start element isn’t the index. it’s the ith element
4. UPPER(str), LOWER(str)
2.1 Fix Names in a Table
Link: https://leetcode.cn/problems/fix-names-in-a-table/
Problem Description:
Table: Users
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| user_id | int |
| name | varchar |
+----------------+---------+
user_id is the primary key for this table.
This table contains the ID and the name of the user. The name consists of only lowercase and uppercase characters.
Write an SQL query to fix the names so that only the first character is uppercase and the rest are lowercase.
Return the result table ordered by user_id.
# Write your MySQL query statement below
# choose the user id
Select user_id,
# left(name,1) get first element of strign and use Upper to make it uppercase
# substring(name,2)get the substring from second element to the end, and use Lower to lowercase
# use concat to combine them and output as name
Concat(Upper(Left(name,1)),Lower(Substring(name,2))) as name
From Users
# output order
Order by user_id
2.2 Group Sold Products By The Date
Link: https://leetcode.com/problems/group-sold-products-by-the-date/
Problem Description:
Table Activities:
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| sell_date | date |
| product | varchar |
+-------------+---------+
There is no primary key 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 an SQL query 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.
# Write your MySQL query statement below
# choose sell_date
Select sell_date,
# use distinct to make product is unique, and count them to get num_sold
Count(distinct product) as num_sold,
# use group_concat to get combine the product name
Group_Concat(distinct product) as products
From Activities
# fisrtly get output of group
# then order with sell_date
Group by sell_date
Order by sell_date
2.3 Patients With a Condition
Link: https://leetcode.cn/problems/patients-with-a-condition/
Problem Description:
Table: Patients
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| patient_id | int |
| patient_name | varchar |
| conditions | varchar |
+--------------+---------+
patient_id is the primary key for this table.
'conditions' contains 0 or more code separated by spaces.
This table contains information of the patients in the hospital.
Write an SQL query to report the patient_id, patient_name all conditions of patients who have Type I Diabetes. Type I Diabetes always starts with DIAB1 prefix
Return the result table in any order.
# Write your MySQL query statement below
# Select patient_id, patient_name,conditions
Select *
From Patients
#use like
# DIAB1 as first element or the not first(add space before DIAB1)
# Where conditions like 'DIAB1%' or conditions like '% DIAB1%'
# use REGEXP
# ^DIAB1 as DIAB1 appeara firstly
# \s is space
Where conditions REGEXP '^DIAB1|\\sDIAB1'