Recently, I have done 32 questions in the Easy part, which can be summarized as follows:

Reading, doing questions and doing complete projects are three things; Typing the code according to the book and writing the complete code by yourself are two different things Of course, the foundation should be laid step by step, but it really needs more practice

1. Mandatory available seats: query continuous values in a table (self join query)

After self connection, use abs(id1-id2)=1 to filter 'continuous', and don't forget to add distinct (there will be many duplicate lines after self connection).

SELECT distinct a.seat_id
FROM cinema a JOIN cinema b
ON abs(a.seat_id-b.seat_id) = 1
AND a.free = TRUE AND b.free = TRUE
ORDER BY a.seat_id;

2. Sales Person: salesperson, company and order multi table connection query (it is not necessary to join all tables...)

SELECT name
FROM SalesPerson
WHERE sales_id not in
(
    SELECT o.sales_id
    FROM orders o
    JOIN company c
    ON o.com_id = c.com_id
    WHERE c.name = 'red'
)

 3. Project Employees II: use of Having (follow group by to specify filter conditions for a group of rows or aggregates.)  

(with having, you don't have to write the aggregate function after select if you don't want that column of aggregate results)

SELECT project_id
FROM project
GROUP BY project_id
HAVING count(employee_id) >= all
(
    SELECT count(employee_id)
    FROM project
    GROUP BY project_id
)

4. Sales Analysis II: product, sale table, filter the buyer ID who bought a but not b (nested statement not in, describing another condition):

SELECT distinct buyer_id
FROM product p JOIN sales s
ON (p.product_id = s.product_id)
WHERE p.product_name = 'S8'
AND buyer_id not in
(SELECT buyer_id
FROM product p JOIN sales s
ON (p.product_id = s.product_id)
WHERE p.product_name = 'iPhone')

 5. Variant of sales analysis III: 4: use not in to filter another condition (Note: add distinct; use these two filter conditions to express "only")

SELECT distinct p.product_id, p.product_name
FROM product p JOIN sales s
ON (p.product_id = s.product_id)
WHERE (s.sale_date between '2019-01-01' and '2019-03-31') AND p.product_id not in
(SELECT product_id FROM sales WHERE sale_date > '2019-03-31' OR sale_date < '2019-01-01')

6. Game Play Analysis II: filter another field corresponding to the minimum value of a field (expressed by < = all)

SELECT player_id, device_id 
from Activity a1
WHERE event_date <= ALL
(SELECT event_date 
 FROM Activity a2 
 WHERE a1.player_id = a2.player_id )

7. Students and Examinations: if you want to get a general table in a unified format, you can directly connect it all; Use case when to fill the missing records with 0, sum(case when...1...else 0...) To count; The aggregate function conditions of groupby can also be placed in case when.

select a.student_id,a.student_name,b.subject_name
,sum(case when a.student_id=c.student_id and b.subject_name = c.subject_name then 1 else 0 end ) AS attended_exams
from students a ,subjects b , examinations c
group by a.student_id,a.student_name,b.subject_name
ORDER BY a.student_id, a.student_name

 8. Immediate Food Delivery I: calculation problem of a table: filter the proportion of rows in a table that meet certain conditions: (group by is not necessary after count, and from is not necessary after select)

SELECT round(
(SELECT count(delivery_id) FROM delivery WHERE order_date = customer_pref_delivery_date)*100/
(SELECT count(*) FROM delivery),2) AS immediate_percentage

9. Average Selling Price: the calculation problem of two tables: (the conditions of JOIN can be multiple, and it can not be a=b, any condition can be; the aggregate function corresponding to group by can also be more than avg or sum, and the operation of aggregate function can be carried out)

 

select p.product_id, ROUND(sum(price*units)/sum(units),2) as average_price 
from Prices p join UnitsSold u
ON (p.product_id = u.product_id AND (u.purchase_date between p.start_date and p.end_date))
group by p.product_id

10. Ads Performance: calculation problem of a table: like 7, sum(case when...1...0) is also used to count (if statement here is also OK); Use coalesce to fill null with 0

SELECT ad_id, 
COALESCE(round(SUM(IF(action = 'Clicked', 1, 0))/(sum(IF(action = 'Viewed', 1, 0))+sum(IF(action = 'Clicked', 1, 0)))*100,2),0) AS ctr FROM Ads
GROUP BY ad_id
ORDER BY ctr DESC, ad_id ASC;

11. Group Sold Products By The Date: use of group concat (because there is a duplicate record, distinct should be added to both aggregate functions)

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

12. Customer Order Frequency: with...as... Put the child form alone, and the code will be clearer later. Conversion problem: first filter out the customerid and month of monthly spending > = 100 and put them in the sub table, and then filter the month, which only includes the months from June to July.

WITH cte1 as (
SELECT customer_id, month(order_date) as month, sum(quantity*price) as spending
FROM Orders INNER JOIN Product USING(product_id)
group by month, customer_id
having spending >= 100)

SELECT customer_id, name
FROM cte1 INNER JOIN Customers c USING(customer_id)
WHERE month = 6 or month = 7
group by customer_id
HAVING count(month) = 2

13. Find Users With Valid E-Mails: regexp regular expression: note that the escape of mysql should use double back slash!

SELECT user_id, name, mail
FROM users
WHERE mail regexp '^[a-zA-Z][A-Za-z0-9_\\.\\-]*(@leetcode\\.com)$'