SQL错题集(二)

 1.查询出生日期大于所有女同学出生日期的男同学的姓名及系别

students表：

sno	sname	class	ssex	bday	bplace	IDNum	sdept	phone
1311104	李嘉欣	13英语1	女	1995-05-28	山西太原	330204199405281056	人文学院	15900002211
1311105	苏有明	13英语1	男	1994-04-16	内蒙古包头	330204199504162036	人文学院	15900002222
1711101	赵薇	17物流1	女	1999-02-11	安徽合肥	330203199902110925	经管学院	15900001177
1711102	陆毅	17物流1	女	1999-02-17	上海	330203199902170017	经管学院	15900001188

select sname,sdept from students
where bday>(select max(bday) from students where ssex="女")

注：日期在前的被认定为小的

2.找出选修课程成绩最差的选课记录

choices表：

错误代码：

select * from choices
group by no
having sorce=min(sorce)

错误原因：分组时用的聚合函数统计的是分组后每一组的数据结果

正确代码：

select * from choices
where sorce=(select min(sorce) from choices)

3.查询所有选修编号1001的课程的学生的姓名

students表：

choices表：

select sname from students
where sid in
(select sid from choices where cid=1001)

易错点：where sid in 不是 where sid =，后面可能会返回多个结果，所以得用in

4.查询了选修所有课程的学生姓名

students表：

choices表：

select sname from students
where not exists
(
    select * from courses where not exists
    (
        select * from choices where sid=students.sid
        and cid=courses.cid
    )
    
)

5.删除记录（一）_牛客题霸_牛客网 (nowcoder.com)

delete from exam_record
where score<60 and 
timestampdiff(minute,start_time,submit_time)<5

时间差：

• TIMESTAMPDIFF(interval, time_start, time_end)可计算time_start-time_end的时间差，单位以指定的interval为准，常用可选：
  • SECOND 秒
  • MINUTE 分钟（返回秒数差除以60的整数部分）
  • HOUR 小时（返回秒数差除以3600的整数部分）
  • DAY 天数（返回秒数差除以3600*24的整数部分）
  • MONTH 月数
  • YEAR 年数

6.插入记录（二）_牛客题霸_牛客网 (nowcoder.com)

INSERT INTO exam_record_before_2021(uid, exam_id, start_time, submit_time, score)
SELECT uid, exam_id, start_time, submit_time, score
FROM exam_record
WHERE YEAR(submit_time) < '2021';

插入记录的方式汇总：

• 普通插入（全字段）：

INSERT INTO table_name VALUES (value1, value2, ...)

• 普通插入（限定字段）：

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)

• 多条一次性插入：

INSERT INTO table_name (column1, column2, ...) VALUES (value1_1, value1_2, ...), (value2_1, value2_2, ...), ...

• 从另一个表导入(全字段)：

INSERT INTO table_name SELECT * FROM table_name2 [WHERE key=value]

• 从另一个表导入(限定字段字段)：

INSERT INTO table_name SELECT (column1, column2, ...) FROM table_name2 [WHERE key=value]

题解：此题应该用最后一种插入方式

7.筛选限定昵称成就值活跃日期的用户_牛客题霸_牛客网 (nowcoder.com)

SELECT
    uid,
    nick_name,
    achievement
FROM user_info
WHERE nick_name LIKE '牛客%号'
    AND achievement BETWEEN 1200 AND 2500
    AND uid IN (
        SELECT uid
        FROM (
            SELECT uid, start_time AS act_time
            FROM exam_record
            UNION
            SELECT uid, submit_time AS act_time
            FROM practice_record
        ) temp
        GROUP BY uid
        HAVING DATE_FORMAT(MAX(act_time), '%Y%m') = 202109
    )

题解：

• 先从表exam_record中筛选出月份是2021年9月提交的用户ID。
• 再从表practice_record中筛选出月份是2021年9月提交的用户ID

 知识点：where、date_format

where date_format(submit_time, '%Y%m') = '202109'

再从user_info表中筛选出成就值在1200到2500之间，uid在上述两个任意一个中，且nick_name能匹配牛客在首，号在结尾的情况。

 知识点：like、where、in

where nick_name like '牛客%' and nick_name like '%号'

8.获取当前薪水第二多的员工的emp_no以及其对应的薪水salary_牛客题霸_牛客网

SELECT
	t1.emp_no AS emp_no,
	salary,
	last_name,
	first_name 
FROM
	employees t1
	JOIN salaries t2 ON t1.emp_no = t2.emp_no 
WHERE
	t2.to_date = '9999-01-01' 
	AND salary = (
SELECT
	MAX( salary ) 
FROM
	salaries 
WHERE
	salary < ( SELECT MAX( salary ) FROM salaries WHERE to_date = '9999-01-01' ) 
	AND to_date = '9999-01-01' 
	)

难点：这题要求不准用order by，所以只能先查出原表最高工资，再查出除了原表最高工资以外的最高工资(第二高工资)

9.查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth_牛客题霸

select (
select salary from salaries
where emp_no = '10001'
order by to_date desc limit 1
)-(
select salary from salaries
where emp_no = '10001'
order by from_date asc limit 1
)
growth ;

注：这题如果只考虑薪资不下降得话很简单，但是如果考虑下降就必须得用最后一天的工资减第一天的工资

10. 将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分

CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

考察： CONCAT 函数可以将多个字符串连接成一个字符串，但分隔符需要指定多次，如要达到 “a:b:c” 的效果就需要指定两次 ":"

SELECT CONCAT(last_name, ' ', first_name) as Name
FROM employees

11.于表actor批量插入如下数据,如果数据已经存在，请忽略，不使用replace操作

actor_id	first_name	last_name	last_update
3	ED	CHASE	2006-02-15 12:34:33

用ignore关键字：insert ignore into

insert ignore into actor
values(3,'ED','CHASE','2006-02-15 12:34:33')

12.查找在职员工自入职以来的薪水涨幅情况_牛客题霸_牛客网 (nowcoder.com)

select s1.emp_no,(s1.salary-s2.salary) as growth
from (select emp_no,salary from salaries where to_date='9999-01-01') as s1
inner join (select e.emp_no ,s.salary from employees e left join salaries s on e.emp_no=s.emp_no and e.hire_date=s.from_date ) as s2
on s1.emp_no = s2.emp_no
order by growth

解：先查找入职工资表，再查找现在工资表，最后把两个salary相减

13.获取员工其当前的薪水比其manager当前薪水还高的相关信息_牛客题霸_牛客网

一表二用：

select de.emp_no,dm.emp_no as manager_no,
s1.salary as emp_salary,s2.salary as manager_salary
from dept_emp de,dept_manager dm,salaries s1,salaries s2
where de.dept_no=dm.dept_no
and de.emp_no=s1.emp_no
and dm.emp_no=s2.emp_no
and s1.salary>s2.salary
and s2.to_date='9999-01-01'
and s1.to_date='9999-01-01';