The database development course recently arranged some sql topics, which ended up bald
The title and mysql statement are as follows
- 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
- 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 )
- 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 ) )
- 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)
- 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
- 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
- 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
- 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
- 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 )
- 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
- 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
- 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
- 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
- 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"
- 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
- 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
- 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
- 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
- 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
- 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