My solutions of SQL-1 Study Plan in MySQL

595. Big Countries | Easy

https://leetcode.com/problems/big-countries/

Select name, population, area
from world
where area >=3000000 or population>=25000000;

1757. Recyclable and Low Fat Products | Easy

https://leetcode.com/problems/recyclable-and-low-fat-products/

Select product_id
from Products
where low_fats=’Y’ and recyclable=’Y’;

584. Find Customer Referee | Easy

https://leetcode.com/problems/find-customer-referee/

Select name
from Customer
where referee_id <> 2 or referee_id IS NULL;

183. Customers Who Never Order | Easy

https://leetcode.com/problems/customers-who-never-order/

Select name as Customers
from customers c
left join orders o on c.id = o.customerid
where c.id not in (select customerid from orders);

1873. Calculate Special Bonus | Easy

https://leetcode.com/problems/calculate-special-bonus/

Select employee_id,
case when employee_id%2!=0 && SUBSTRING(name, 1, 1)<>’M’ then salary
else 0
end as bonus
from employees
order by employee_id;

627. Swap Salary | Easy

https://leetcode.com/problems/swap-salary/

Update salary
set sex=case sex when ‘m’ then ‘f’
else ‘m’
end;

196. Delete Duplicate Emails | Easy

https://leetcode.com/problems/delete-duplicate-emails/

Delete p1 from person p1, person p2
where p1.email=p2.email and p1.id>p2.id;

1667. Fix Names in a Table | Easy

https://leetcode.com/problems/fix-names-in-a-table/

Select user_id,
concat(upper(substring(name,1,1)),lower(substring(name,2))) as name
from users
order by user_id;

1484. Group Sold Products By The Date | Easy

https://leetcode.com/problems/group-sold-products-by-the-date/

Select sell_date,
count(distinct product) as num_sold,
group_concat(distinct product) as products
from Activities
group by sell_date
order by sell_date;

1527. Patients With a Condition | Easy

https://leetcode.com/problems/patients-with-a-condition/

Select *
from Patients
where conditions like ‘% DIAB1%’ or conditions like ‘DIAB1%’;

1965. Employees With Missing Information | Easy

https://leetcode.com/problems/employees-with-missing-information/

select e1.employee_id
from Employees e1
where e1.employee_id not in (select employee_id from salaries)

union

select s1.employee_id
from Salaries s1
where s1.employee_id not in (select employee_id from employees)

order by employee_id

1795. Rearrange Products Table | Easy

https://leetcode.com/problems/rearrange-products-table/

select product_id,
‘store1’ as store,
store1 as price
from products
where store1 is not null

union all

select product_id,
‘store2’ as store,
store2 as price
from products
where store2 is not null

union all

select product_id,
‘store3’ as store,
store3 as price
from products
where store3 is not null

608. Tree Node | Medium

https://leetcode.com/problems/tree-node/

Select id as ‘id’,
case when id = (select id from tree where p_id is null) then ‘Root’
when id in (Select p_id from tree) then ‘Inner’
else ‘Leaf’
end as type
from tree
order by 1;

176. Second Highest Salary | Medium

https://leetcode.com/problems/second-highest-salary/

Select if(max(salary) is null, null, max(salary)) as ‘SecondHighestSalary’
from Employee
where salary < (select max(salary) from employee);

175. Combine Two Tables | Easy

https://leetcode.com/problems/combine-two-tables/

Select firstName,
lastName,
city,
state
from Person
left join address on person.personId=Address.personId;

1581. Customer Who Visited but Did Not Make Any Transactions | Easy

https://leetcode.com/problems/customer-who-visited-but-did-not-make-any-transactions/

Select Visits.customer_id,
count(customer_id) as count_no_trans
from visits
left join Transactions on Visits.visit_id=Transactions.visit_id
where amount is null
group by customer_id;

1148. Article Views I | Easy

https://leetcode.com/problems/article-views-i/

Select distinct author_id as ‘id’
from views
where author_id=viewer_id
order by author_id;

197. Rising Temperature | Easy

https://leetcode.com/problems/rising-temperature/

Select
weather.id as ‘Id’
from
weather
join
weather w on DATEDIFF(weather.recordDate, w.recordDate) = 1
and weather.Temperature > w.Temperature;

607. Sales Person | Easy

https://leetcode.com/problems/sales-person/

Select s.name
from salesperson s
where s.sales_id not in (select o.sales_id from orders o left join
company c on o.com_id = c.com_id where c.name=’RED’)

1141. User Activity for the Past 30 Days I | Easy

https://leetcode.com/problems/user-activity-for-the-past-30-days-i/

Select activity_date as ‘day’,
count(distinct user_id) as ‘active_users’
from Activity
where activity_date between ‘2019–06–28’ and ‘2019–07–27’
group by activity_date;

1693. Daily Leads and Partners | Easy

https://leetcode.com/problems/daily-leads-and-partners/

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;

1729. Find Followers Count | Easy

https://leetcode.com/problems/find-followers-count/

Select user_id,
count(follower_id) as “followers_count”
from Followers
group by user_id
order by user_id;

586. Customer Placing the Largest Number of Orders | Easy

https://leetcode.com/problems/customer-placing-the-largest-number-of-orders/

Select customer_number
from orders
group by customer_number
order by count(*) desc
limit 1;

511. Game Play Analysis I | Easy

https://leetcode.com/problems/game-play-analysis-i/

Select player_id,
min(event_date) as first_login
from activity
group by player_id;

1890. The Latest Login in 2020 | Easy

https://leetcode.com/problems/the-latest-login-in-2020/

Select
user_id,
MAX(time_stamp) AS last_stamp
from
Logins
where
year(time_stamp) = 2020
group by
user_id;

1741. Find Total Time Spent by Each Employee | Easy

https://leetcode.com/problems/find-total-time-spent-by-each-employee/

Select event_day as ‘day’,
emp_id,
sum(out_time-in_time) as ‘total_time’
from Employees
group by
event_day,
emp_id;

1393. Capital Gain/Loss | Medium

https://leetcode.com/problems/capital-gainloss/

Select stock_name,
sum(case when operation=’sell’ then price end)
- sum(case when operation=’buy’ then price end) as capital_gain_loss
from stocks
group by stock_name;

1407. Top Travellers | Easy

https://leetcode.com/problems/top-travellers/

Select name,
sum(if(distance is null, 0, distance)) as travelled_distance
from users
left join rides on users.id = rides.user_id
group by users.id
order by travelled_distance desc, name asc;

1158. Market Analysis I | Medium

https://leetcode.com/problems/market-analysis-i/

Select u.user_id as buyer_id,
u.join_date,
count(o.order_id) as ‘orders_in_2019’
from users u
left join Orders o
on o.buyer_id=u.user_id
and YEAR(order_date)=’2019'
group by u.user_id;

182. Duplicate Emails | Easy

https://leetcode.com/problems/duplicate-emails/

Select email
from Person
group by email
having count(email)>1;

1050. Actors and Directors Who Cooperated At Least Three Times | Easy

https://leetcode.com/problems/actors-and-directors-who-cooperated-at-least-three-times/

Select actor_id,
director_id
from ActorDirector
group by actor_id,
director_id
having count(timestamp) >=3;

1587. Bank Account Summary II | Easy

https://leetcode.com/problems/bank-account-summary-ii/

Select users.name,
sum(Transactions.amount) as “balance”
from Users
left join Transactions on Users.account=Transactions.account
group by Users.name
having sum(Transactions.amount)>10000;

1084. Sales Analysis III | Easy

https://leetcode.com/problems/sales-analysis-iii/

Select p.product_id,
p.product_name
from product p
inner join sales s on p.product_id = s.product_id
group by s.product_id
having min(sale_date)>=’2019–01–01'
and max(sale_date)<=’2019–03–31';