Topic source

The database development course recently arranged some sql topics, which ended up bald

The title and mysql statement are as follows

First question

1. Write an sql statement to query the employees whose cumulative working hours exceed 1000, and the result returns the employee job number eno.

select
  eno
from
  works
group by
  eno
having
  sum(hours) > 1000

1. Write an sql statement to query the employees without registered family members, and the result returns the employee's job number eno.

select
  eno
from
  employees
where
  not exists (
    select
      *
    from
      relations
    where
      employees.eno = relations.eno
  )

Second question

1. Write an sql statement to find the sailors who have booked all ships, and the result returns the sailor's name sname.

select
  sname
from
  sailors
where
  not exists (
    select
      *
    from
      boats
    where
      not exists (
        select
          *
        from
          reserves
        where
          sailors.sid = reserves.sid
          and boats.bid = reserves.bid
      )
  )

Third way

1. Write an sql statement to query the matches of each group, and the result returns the group number group_id and field times of each group match_num.

SELECT
  group_id,
  COUNT(group_id) AS match_num
FROM
  matches
GROUP BY
  (group_id)

1. Write an sql statement to query the game with the largest difference. If the difference is the same, select match_ For a match with a smaller ID, the result returns the match number match_id and differential sub (greater than 0).

SELECT
  match_id,
  abs(first_score - second_score) as sub
FROM
  matches
ORDER BY
  abs(first_score - second_score) DESC
LIMIT
  1

Question 4

1. Write an sql query to find the day with the largest number of orders and the number of orders on that day. If the number of orders is the same, select the day with the smallest date, and the result returns the date order_date and order quantity num.

select
  order_date,
  count(order_id) as num
from
  orders
group by
  order_date
order by
  count(*) desc
limit
  1

1. Write an sql statement to find the users who have bought their favorite products, and the result returns the user number user_id.

select
  users.user_id
from
  users,
  orders,
  items
where
  users.user_id = orders.buyer_id
  and orders.item_id = items.item_id
  and users.favorite_brand = items.item_brand

Question 5

1. Write an sql statement to calculate the total pass rate of friend application accept_rate, expressed with 2 decimal places. The calculation formula of passing rate is the number of friends' applications accepted divided by the total number of applications (there may be duplication between application and reception, which is regarded as one time at this time).

select
  round(
    ifnull(
      (
        select
          count(*)
        from
          (
            select
              distinct requester_id,
              accepter_id
            from
              accepted_requests
          ) as b
      ) /(
        select
          count(*)
        from
          (
            select
              distinct sender_id,
              send_to_id
            from
              friend_requests
          ) as a
      ),
      0
    ),
    2
  ) as accept_rate

1. Write an sql statement to query the users who have issued applications but all applications have not passed, and the result returns the user number user_id.

select
  distinct sender_id as user_id
from
  friend_requests
where
  sender_id not in(
    select
      requester_id
    from
      accepted_requests
  )

Question 6

1. Write an sql statement to query all authors who have browsed their own articles, and the result returns the user number id, which is arranged in ascending order of id.

select
  distinct author_id id
from
  views
where
  author_id = viewer_id
order by
  id

1. Write an sql statement to find out who has read at least two articles in a day, and the result returns the user number viewer_id, with viewer_id in ascending order.

select
  distinct viewer_id
from
  views
group by
  view_date,
  viewer_id
having
  count(distinct article_id) >= 2
ORDER BY
  viewer_id

1. Write an sql statement to find out the users who read the most articles. When the number of reading articles is the same, select view_ For users with smaller ID, the result returns the user number viewer_id and number of articles read_ num.

select
  viewer_id,
  count(article_id) as article_num
from
  views
group by
  viewer_id
order by
  count(*) desc
limit
  1

Question 7

1. Write an sql statement to query the last login day of each user, and the result returns the user number user_id and login date, in user_id in ascending order.

select
  user_id,
  login_date as date
from
  logins a
where
  not exists(
    select
      1
    from
      logins
    where
      user_id = a.user_id
      and login_date > a.login_date
  )
order by
  user_id asc

Question 8

1. Write an sql statement to find the average salary of the Technology department, and the result returns the Department name department and the average salary avg_salary.

select
  departments.department_name as department,
  avg(employees.salary) as avg_salary
from
  departments,
  employees
where
  employees.department_id = departments.department_id
  and department_name = "Technology"

1. Write an sql statement to find the difference between the maximum wage and the minimum wage of each department. The result returns the Department name department and the difference sub.

select
  department_name as department,(max(salary) - min(salary)) as sub
from
  employees,
  departments
where
  departments.department_id = employees.department_id
group by
  employees.department_id

Question 9

1. Find the movie titles with the highest average score in February 2020. If there is the same, return the movie name with smaller dictionary order. Result field: title

select
  title
from
  movie_rating r
  left join movies m on r.movie_id = m.movie_id
where
  date_format(created_at, '%Y-%m') = '2020-02'
group by
  r.movie_id
order by
  avg(rating) desc,
  title
limit
  1

1. Ask for the highest score, lowest score and average score of each film. The result field contains movie_id, title, avg_rating, max_rating, min_rating.

select
  movies.movie_id,
  movies.title,
  avg(movie_rating.rating) as avg_rating,
  max(movie_rating.rating) as max_rating,
  min(movie_rating.rating) as min_rating
from
  movie_rating,
  movies
where
  movie_rating.movie_id = movies.movie_id
group by
  movie_rating.movie_id

1. Find the movies seen by the user with user ID 1 and the scores given. The result field contains user_id，name，title，rating.

select
  users.user_id,
  users.name,
  movies.title,
  movie_rating.rating
from
  users,
  movies,
  movie_rating
where
  users.user_id = movie_rating.user_id
  and users.user_id = 1
  and movies.movie_id = movie_rating.movie_id

Question 10

1. Query the invoice number, user ID and user name corresponding to the invoice with the largest amount. Result field: invoice_id，price, user_id，customer_name

select
  i1.invoice_id,
  i1.price,
  i1.user_id,
  customers.customer_name
from
  customers,
  invoices as i1,(
    select
      max(price) as maxprice
    from
      invoices
  ) as i2
where
  i1.price = i2.maxprice
  and i1.user_id = customers.customer_id

1. Ask for the ID and name of the customer who has the contact and the contact name. Result field customer_id，customer_name，contact_name

select
  customers.customer_id,
  customers.customer_name,
  contacts.contact_name
from
  customers,
  contacts
where
  customers.customer_id = user_id