题目来源

1

q1

select top (3) author_name from authors as a
right join books as b on book_name
group by a.author_name
order by b.sold_copies;

正确答案：

SELECT authors.author_name, SUM(books.sold_copies) AS sold_sum
FROM authors
JOIN books
ON books.book_name = authors.book_name
GROUP BY authors.author_name
ORDER BY sold_sum DESC
LIMIT 3;

2

Q2

SELECT COUNT(*) FROM
  (SELECT user_id, COUNT(event_date_time) AS image_per_user
  FROM event_log
  GROUP BY user_id) AS image_per_user
WHERE image_per_user < 2000 AND image_per_user > 1000;

3

Q3

select e.department_name 
from employees as e join salaries as s on employee_id
group by e.department_name
having avg(s.salary)<500;

正确答案：

SELECT department_name, AVG(salaries.salary) AS avg_salaries
FROM employees
JOIN salaries
ON employees.employee_id = salaries.employee_id
GROUP BY department_name
HAVING AVG(salaries.salary) < 500;

4.Delete duplicate data from table only first data remains constant.

image.png

DELETE M1 
From managers M1, managers M2 
Where M2.Name = M1.Name AND M1.Id>M2.Id; 

5.Find the Name of Employees.

Finding the name of Employees where First Name, Second Name and Last Name is given in table. Some Name is missing such as First Name, Second Name and may be Last Name. Here we will use COALESCE() function which will return first Non Null values.

image.png

select id, coalesce(fname,sname,lname) from employees;

6.Find the Employees who hired in the Last n months.

Finding the Employees who have been hire in the last n months. Here we get desire output by using TIMESTAMPDIFF() mysql function.

image.png

select fname,lname, timestampdiff(hiredate) as month from employees;

正确答案：

Select *, TIMESTAMPDIFF (month, Hiredate, current_date()) as DiffMonth 
From employees
Where TIMESTAMPDIFF (month, Hiredate, current_date()) 
Between 1 and 5 Order by Hiredate desc; 

6.Find the Employees who hired in the Last n days.

Finding the Employees who have been hire in the last n days. Here we get desire output by using DATEDIFF() mysql function.

image.png

select *, datediff(day,hiredate,current_date()) as diffday from employees;

正确答案：

Select *, DATEDIFF (current_date(), Hiredate) as DiffDay 
From employees
Where DATEDIFF (current_date(), Hiredate) between 1 and 100 order by Hiredate desc; 

7.找出每月最后一天的sales

image.png

Rank () over (partition by Month(date) order by Day(date) desc) 然后选 rank = 1

参考

8. 查找字符串'10,A,B' 中逗号','出现的次数cnt。

select char_length('A,10,B')- char_length(replace('10,A,B',',',''));