• The exercise questions are from: leetcode-cn.com/

Average salary: Department compared to company

Write a query for the following two tables to find out how the average salary of each department compares with the average salary of the company on each payday (high/low/same). Table: salary

+----+-------------+--------+------------+ | id | employee_id | amount | pay_date | | -- - | -- -- -- -- -- -- -- -- -- -- -- -- - | -- -- -- -- -- -- -- - | -- -- -- -- -- -- -- -- -- -- -- - | | | 1 | 9000 | | 2017-03-31 | 2 | 2 | 6000 | 2017-03-31 | | 10000 | 3 | 3 | The 2017-03-31 | | | 1 | 7000 | | 2017-02-28 | | 2 | 6000 | 2017-02-28 | | 8000 | | 2017-02-28 | 3 | 6 +----+-------------+--------+------------+Copy the code

The employee_id field is the foreign key of the Employee_id field in the Employee table.

+-------------+---------------+ | employee_id | department_id | |-------------|---------------| | 1 | 1 | | 2 | 2 | | 3 2 | | + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code

For the above sample data, the result is:

+-----------+---------------+-------------+
| pay_month | department_id | comparison  |
|-----------|---------------|-------------|
| 2017-03   | 1             | higher      |
| 2017-03   | 2             | lower       |
| 2017-02   | 1             | same        |
| 2017-02   | 2             | same        |
+-----------+---------------+-------------+
Copy the code

Explain that in March, the average company salary was (9000+6000+10000)/3 = 8333.33…

Since there is only one employee whose employee_id is ‘1’ in department ‘1’, the average salary of department ‘1’ is 9000 for that employee. Because 9000 > 8333.33, the comparison result is ‘higher’.

The average salary of the second department is the average salary of two employees whose employee_id is ‘2’ and ‘3’, which is (6000+10000)/2=8000. Since 8000 < 8333.33, the comparison result is ‘lower’.

Use the same formula to find the average salary in February and compare, the comparison result is ‘same’ because the average salary of department ‘1’ and department ‘2’ is the same as the average salary of the company, both 7000.

  • DATE_FORMAT()

    • Used to display date/time data in different formats.
    • DATE_FORMAT(date,format)
    • The date argument is a valid date. Format Specifies the date/time output format.

  • SQL1:

    • The Join function
    • select
a.pay_month pay_month, a.department_id department_id,
if(a.d_salary > b.c_salary, 'higher', if(a.d_salary = b.c_salary, 'same', 'lower')) comparison
from
(select
avg(t.amount) d_salary, t.department_id, date_format(t.pay_date, '%Y-%m') pay_month
from
(select
s.employee_id, s.amount, s.pay_date, e.department_id
from
salary s join employee e
on s.employee_id = e.employee_id) t
group by date_format(t.pay_date, '%Y-%m'), t.department_id) a
join
(select
avg(amount) c_salary, date_format(pay_date, '%Y-%m') pay_month
from
salary
group by date_format(pay_date, '%Y-%m')) b
on a.pay_month = b.pay_month
order by a.pay_month DESC, a.department_id
Copy the code

  • SQL2:

    • The window function
    • select
distinct date_format(t.pay_date, '%Y-%m') pay_month,
t.department_id department_id,
case
when t.d_salary > t.c_salary then 'higher'
when t.d_salary = t.c_salary then 'same'
else 'lower'
end comparison
from
(select
s.amount, s.pay_date, e.department_id,
avg(s.amount) over(partition by s.pay_date) c_salary,
avg(s.amount) over(partition by e.department_id, s.pay_date) d_salary
from
salary s join employee e
on s.employee_id = e.employee_id) t
order by t.pay_date DESC, t.department_id
Copy the code

Student geographic information report

An American university has students from Asia, Europe, and the Americas whose geographic information is stored in the student table below.

+--------+-----------+
| name   | continent |
|--------|-----------|
| Jack   | America   |
| Pascal | Europe    |
| Xi     | Asia      |
| Jane   | America   |
+--------+-----------+
Copy the code

Write a query to implement a PivotTable operation on the Continent column so that each student is listed alphabetically under the continent by name. Exports should be titled America, Asia and Europe, in that order.

For the sample input, its corresponding output is:

+---------+------+--------+
| America | Asia | Europe |
|---------|------|--------|
| Jack    | Xi   | Pascal |
| Jane    |      |        |
+---------+------+--------+
Copy the code

Advanced: If you can’t determine which continent has the most students, can you write a query to generate the student report?

Case [col_name] when [value1] THEN [result1]… ELSE [default] END CASE WHEN [expr] THEN [result1]… ELSE [default] END

  • Window functions:

  • SQL1:

    • You need to know the maximum value of a single category in advance, as T1
    • select
America, Asia, Europe
from
    (select
    name America,
    ROW_NUMBER() over(order by name) rn
    from
    student
    where continent = 'America') t1
    left join
    (select
    name Asia,
    ROW_NUMBER() over(order by name) rn
    from
    student
    where continent = 'Asia') t2
    on t1.rn = t2.rn
    left join
    (select
    name Europe,
    ROW_NUMBER() over(order by name) rn
    from
    student
    where continent = 'Europe') t3
    on t1.rn = t3.rn
Copy the code

  • SQL2:

    • Just know the categories
    • select
max(case continent when 'America' then name else NULL end) America,
max(case continent when 'Asia' then name else NULL end) Asia,
max(case continent when 'Europe' then name else NULL end) Europe
from
(select
*,
ROW_NUMBER() over(partition by continent order by name) rk
from
student) t
group by rk
Copy the code

The maximum number that occurs only once

MyNumbers table:

+ -- -- -- -- -- -- -- -- -- -- -- -- -- + | + -- -- -- -- -- - the Column Name | Type | + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- + | num | int | + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + this table without a primary key. May contain repeated digits. Each row in this table contains an integer.Copy the code

A single number is a number that appears only once in the MyNumbers table. Write an SQL query to report the largest single number. If no single number exists, the query reports NULL.

The following example shows the query result. Example 1:

Input: MyNumbers table: + -- -- -- -- -- + | num | + -- -- -- -- -- + | | 8 8 | | | | 3 | 3 | | 1 | | | 4 5 | | | | + -- -- -- -- -- 6 + output: + -- -- -- -- -- + | num | + -- -- -- -- -- + | | + -- -- -- -- -- 6 + explanation: a single number is 1, 4, 5 and 6. 6 is the largest single number. Return 6.Copy the code

Example 2:

Enter: MyNumbers table: + -- -- -- -- -- + | num | + -- -- -- -- -- + | | 8 8 | | | | 7 7 | | | | 3 | 3 | 3 | | + -- -- -- -- -- + output: + -- -- -- -- -- -- + | num | + -- -- -- -- -- -- + | null | + -- -- -- -- -- - + explanation: single digital input in the table does not exist, it returns null.Copy the code

Note: If there is no number that occurs only once, null is printed.

  • SQL1:

    • When null values are processed using aggregate functions, SUM/AVG/MAX/MIN all return null values
    • select
max(t.num) num
from
(select
num, count(num) cnt
from
MyNumbers
group by num
having cnt < 2
order by num DESC
limit 1
) t
Copy the code

  • SQL2:

    • If we run the SELECT statement, we’ll get null
    • select(
  select num
  from MyNumbers
  group by num
  having count(num) = 1
  ORDER BY num DESC
  LIMIT 1
) num
Copy the code

Interesting movie

A new cinema opened in a city, which attracted many people to see the film. The cinema pays special attention to the user experience and has a special LED display board for movie recommendations, which posts movie reviews and descriptions. As the information director of the cinema, you need to write an SQL query to find all movies that are described as non-boring and have an odd number of ids, ranked by rating.

For example, the following table cinema:

+---------+-----------+--------------+-----------+ | id | movie | description | rating | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- + | 1 | War | great 3 d 8.9 | | | | 2 Science | fiction 8.5 | | | | 3 Irish | boring 6.2 | | | | 4 Ice song | Fantacy 8.6 | | | | 5 House card | Interesting | | 9.1 +---------+-----------+--------------+-----------+Copy the code

For the above example, the correct output would be:

+---------+-----------+--------------+-----------+ | id | movie | description | rating | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- + | | 5 House card | Interesting 9.1 | | | | 1 War | great 3 d | | 8.9 +---------+-----------+--------------+-----------+Copy the code
  • mod(N,M):
    • Returns the remainder of N divided by M


  • MySQL > determine an odd number

    • mod(x, 2) = 1If the remainder is 1, it’s odd.
    • power(-1, x) = -1If it’s negative 1, it’s odd
    • x % 2 = 1If the remainder is 1, it’s odd.
    • x & 1 = 1If it’s 1, it’s odd
    • x regexp '[1, 3, 5, 7, 9]$' = 1If it’s 1, it’s odd
    • x>>1<<1 ! = xIf you move one to the right and you move one to the left, it’s odd

  • SQL:

    • select id, movie, description, rating from cinema where description ! = 'boring' and id%2 = 1 order by rating DESCCopy the code

In the seat

Mei, an information technology teacher in a middle school, has a seat chart, which she usually uses to store students’ names and seat ids corresponding to them. The id of the vertical column is continuously increasing, and Mei wants to change the seats of the two adjacent students.

Can you write an SQL Query for her to output the result that Mei wants?

Example:

+---------+---------+ | id | student | +---------+---------+ | 1 | Abbot | | 2 | Doris | | 3 | Emerson | | 4 | Green | |  5 | Jeames | +---------+---------+Copy the code

If the data input is from the above table, the output is as follows:

+---------+---------+ | id | student | +---------+---------+ | 1 | Doris | | 2 | Abbot | | 3 | Green | | 4 | Emerson | |  5 | Jeames | +---------+---------+Copy the code

Note: If the number of students is odd, there is no need to change the seat of the last student.

  • SQL1:

    • select (case when mod(id, 2) = 1 and cnt ! = id then id + 1 when mod(id, 2) = 1 and cnt = id then id else id - 1 end) id, student from seat, (select count(*) cnt from seat) t order by idCopy the code

  • SQL2:

    • The window function
    • select
if(mod(t.id, 2) = 1, t.lag, t.lead) student
from
(select
id, student,
LAG(student, 1, student) lag
LEAD(student, 1, student) lead
from
seat) t
Copy the code

  • Coalesce () :

    • Returns the first non-empty expression in a parameter (left to right)

  • SQL3:

    • Use (ID +1)^1-1 to calculate the seat ID of each student after the exchange
    • select
s1.id, coalesce(s2.student, s1.student) student
from
seat s1 left join seat s2
on ((s1.id + 1) ^ 1) - 1 = s2.id
order by s1.id
Copy the code

Change the gender

Given a salary table, as shown below, there are values m = male and f = female. Swap all f and m values (for example, change all f values to M and vice versa). Only one Update statement is required, and no intermediate temporary tables are required.

Note that you must only write one Update statement; do not write any Select statements. Such as:

+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
| 1  | A    | m   | 2500   |
| 2  | B    | f   | 1500   |
| 3  | C    | m   | 5500   |
| 4  | D    | f   | 500    |
+----+------+-----+--------+
Copy the code

After running the update statement you wrote, you will get the following table:

+----+------+-----+--------+ | id | name | sex | salary | +----+------+-----+--------+ | 1 | A | f | 2500 | | 2 | B | m | | 1500 | | 3 | C f | 5500 | | | D | 4 m | | + 500 - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- + explanation: (1 A) and (3 C) from the 'm' to 'f'. (2, B) and (4, D) change from 'f' to 'm'.Copy the code

  • SQL1:

    • update
salary
set
sex = case sex
when 'm' then 'f'
else 'm'
end
Copy the code

  • SQL2:

    • update
salary
set sex = if(sex = 'm', 'f', 'm')
Copy the code

  • SQL3:

    • update
salary
set sex = char(ascii('m') + ascii('f') - ascii(sex))
Copy the code

Customers who buy all the products

The Customer table:

+-------------+---------+ | Column Name | Type | +-------------+---------+ | customer_id | int | | product_key | int | + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + product_key is the foreign key Customer table.Copy the code

The Product table:

+-------------+---------+ | Column Name | Type | +-------------+---------+ | product_key | int | +-------------+---------+ product_key is the primary key of this table.Copy the code

Write an SQL query to query from the Customer table the IDS of the customers who have purchased all the products in the Product table. Example:

The Customer table:  +-------------+-------------+ | customer_id | product_key | +-------------+-------------+ | 1 | 5 | | 2 | 6 | | 3 | 5 | 6 | | 3 | | | | 6 + 1 -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + Product list: + -- -- -- -- -- -- -- -- -- -- -- -- -- + | product_key | + -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 5 6 | | + -- -- -- -- -- -- -- -- -- -- -- -- -- + Result table: + -- -- -- -- -- -- -- -- -- -- -- -- -- + | customer_id | + -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1 | | 3 | + -- -- -- -- -- -- -- -- -- -- -- -- -- + (5 and 6) for all products purchased customer id is 1 and 3.Copy the code

  • Because the number of products in the Product table is fixed, as long as the group by Customer_id is included, all products will be purchasedhaving count distinct product_key = product.product_key

  • SQL:

    • select
customer_id
from
Customer
group by customer_id
having count(distinct product_key) >= (select
                                      count(distinct product_key)
                                      from
                                      Product)
Copy the code

Actors and directors who have worked together at least three times

ActorDirector table:

+-------------+---------+ | Column Name | Type | +-------------+---------+ | actor_id | int | | director_id | int | | Timestamp | int | + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + timestamp is this table's primary key.Copy the code

Write an SQL query to get the ID pairs (actor_id, director_id) of actors and directors who have worked together at least three times:

ActorDirector table:  +-------------+-------------+-------------+ | actor_id | director_id | timestamp | + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 | 0 | | 1 | | 1 | | 1 | 1 | 2 | | 1 | 2 | 3 | | 1 | 2 | 4 | | | 2 2 1 5 | | | | | | 6 + 1 -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + Result table:  +-------------+-------------+ | actor_id | director_id | +-------------+-------------+ | 1 | 1 | + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + unique id is (1, 1), they just three times cooperation.Copy the code
  • SQL: 
    • select
actor_id, director_id
from
ActorDirector
group by actor_id, director_id
having count(*) >= 3
Copy the code

Product sales analysis series

Sales Table

+-------------+-------+ | Column Name | Type | +-------------+-------+ | sale_id | int | | product_id | int | | year | int | | quantity | int | | price | int | +-------------+-------+ (sale_id, Year is the primary key of the Sales table Sales and product_id is the foreign key associated with the Product table Product. Note: price means price per unitCopy the code

Table of Products

+--------------+---------+ | Column Name | Type | +--------------+---------+ | product_id | int | | product_name | Varchar | + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + product_id is the primary key table.Copy the code

1. Write an SQL query statement to obtain the product name product_name corresponding to all products in table Sales and all Sales years and prices of this product.

Example:

The Sales table:  +---------+------------+------+----------+-------+ | sale_id | product_id | year | quantity | price | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + | 1 | 100 | 2008 | | 5000 | 10 | 2 | | 100 | 2009 | | 5000 | | 7 200 | 2011 | | 9000 | 15 + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + Product list:  +------------+--------------+ | product_id | product_name | +------------+--------------+ | 100 | Nokia | | 200 | Apple | | 300 | Samsung | + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + Result table:  +--------------+-------+-------+ | product_name | year | price | +--------------+-------+-------+ | Nokia | 2008 | 5000  | | Nokia | 2009 | 5000 | | Apple | 2011 | 9000 | +--------------+-------+-------+Copy the code
  • SQL: 
    • select
p.product_name product_name, s.year year, s.price price
from
Sales s join Product p
on s.product_id = p.product_id
Copy the code

2, write a SQL query to count the total sales of each product by product ID product_id. The query result is in the following format:

The Sales table:  +---------+------------+------+----------+-------+ | sale_id | product_id | year | quantity | price | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + | 1 | 100 | 2008 | | 5000 | 10 | 2 | | 100 | 2009 | | 5000 | | 7 200 | 2011 | | 9000 | 15 + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + Product list:  +------------+--------------+ | product_id | product_name | +------------+--------------+ | 100 | Nokia | | 200 | Apple | | 300 | Samsung | + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + Result table:  +--------------+----------------+ | product_id | total_quantity | +--------------+----------------+ | 100 | 22 | | 200 15 | | + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - +Copy the code

  • SQL1:

    • select
product_id, sum(quantity) total_quantity
from
Sales
group by product_id
Copy the code

  • SQL2:

    • The window function
    • select
distinct product_id,
sum(quantity) over(partition by product_id) total_quantity
from
Sales
Copy the code