金三银四面试准备SQL题(附答案)

第一节前言

本文总结了我们在开发中比较常用的一些查询SQL，有简单的分组统计、开发中常用的删除重复数据、实用的行转列且字段合并等等。这些查询SQL语句非常适合准备换工作的同学用来练习巩固自己的SQL知识。当然，这些查询SQL语句也是我们开发中经常会用到的，建议收藏方便查阅。

第二节推荐SQL在线编辑工具

如果只是练习SQL，不需要在本地搭建环境安装数据库，SQL Fiddle提供了在线编辑SQL功能，如下图：

SQL Fiddle

左边窗口写DDL and DML statements，右边窗口写select statements。

第三节 SQL题集

说明：以下SQL主要针对MySQL数据库。

题目1

NO1.1 问题描述

用一条SQL语句查出商品A、B、C目前还剩多少？

NO1.2 数据准备

CREATE TABLE t_book_store (
    vc_name varchar(20),
    n_num int
);

INSERT INTO t_book_store (vc_name, n_num)
VALUES ('A', '100');

INSERT INTO t_book_store (vc_name, n_num)
VALUES ('B', '120');

INSERT INTO t_book_store (vc_name, n_num)
VALUES ('C', '50');

CREATE TABLE t_book_sell (
    vc_name varchar(20),
    n_sellnum int
);

INSERT INTO t_book_sell (vc_name, n_sellnum)
VALUES ('A', '10');

INSERT INTO t_book_sell (vc_name, n_sellnum)
VALUES ('A', '20');

INSERT INTO t_book_sell (vc_name, n_sellnum)
VALUES ('B', '10');

INSERT INTO t_book_sell (vc_name, n_sellnum)
VALUES ('B', '20');

INSERT INTO t_book_sell (vc_name, n_sellnum)
VALUES ('B', '30');

NO1.3 参考答案

SELECT 
    t.vc_name,
    t.n_num,
    COALESCE(t2.sum_sell_num, 0) sum_sell_num,
    (t.n_num - COALESCE(t2.sum_sell_num, 0)) AS num
FROM
    t_book_store t
        LEFT JOIN
    (SELECT 
        vc_name, SUM(n_sellnum) sum_sell_num
    FROM
        t_book_sell
    GROUP BY vc_name) t2 ON t.vc_name = t2.vc_name

NO1.4 补充说明

在本题案例中，产品C是没有销售记录的，所以我们用库存表去左关联销量表时，需要处理C产品销量为null的情况。这里我们用到了 COALESE（field,0）函数，这个函数会在fiele 为null时将值修改为后面指定的值0，当然还有 ifnull 和 is_null 函数有类似功能。

题目2

NO2.1 问题描述

查询有多个员工的工资不低于2000的部门编号(一个部门的员工大于2000的人数有两个或两个以上就查询出来)

NO2.2 数据准备

CREATE TABLE t_payroll_sheet (
	vc_dep_id varchar(20),
	vc_name varchar(20),
	n_num int
);

INSERT INTO t_payroll_sheet
VALUES ('1', 'aa', 2000);

INSERT INTO t_payroll_sheet
VALUES ('2', 'bb', 1200);

INSERT INTO t_payroll_sheet
VALUES ('1', 'cc', 2100);

INSERT INTO t_payroll_sheet
VALUES ('2', 'dd', 1800);

INSERT INTO t_payroll_sheet
VALUES ('1', 'ee', 2100);

INSERT INTO t_payroll_sheet
VALUES ('3', 'ff', 8000);

INSERT INTO t_payroll_sheet
VALUES ('2', 'gg', 2200);

INSERT INTO t_payroll_sheet
VALUES ('3', 'hh', 4500);

NO2.3 参考答案

SELECT 
    vc_dep_id, COUNT(1) AS count_num
FROM
    t_payroll_sheet
WHERE
    n_num > 2000
GROUP BY vc_dep_id
HAVING count_num > 1

NO2.4 补充说明

where 针对每一条记录筛选，而 having 对分组后的结果集进行筛选。

问题3

NO3.1 问题描述

删除重复数据（id没有重复）

NO3.2 数据准备

CREATE TABLE t_records (
	vc_id int,
	vc_name varchar(20),
	vc_age varchar(20)
);

INSERT INTO t_records
VALUES (1, 'a', '20');

INSERT INTO t_records
VALUES (2, 'a', '20');

INSERT INTO t_records
VALUES (3, 'b', '25');

INSERT INTO t_records
VALUES (4, 'c', '27');

INSERT INTO t_records
VALUES (5, 'd', '24');

INSERT INTO t_records
VALUES (6, 'd', '24');

INSERT INTO t_records
VALUES (7, 'd', '24');

INSERT INTO t_records
VALUES (8, 'e', '28');

NO3.3 易错SQL

DELETE FROM t_records 
WHERE
    vc_id NOT IN (SELECT 
        vc_id
    FROM
        t_records
    GROUP BY vc_name , vc_age
    HAVING MAX(vc_id));

提示错误信息

You can't specify target table 't_records' for update in FROM clause

在同一语句中，不能先select出同一表中的某些值，再update这个表。也就是说将select出的结果再通过中间表select一遍，这样就规避了错误。注意，这个问题只出现于MySQL , Oracle不会出现此问题。

NO3.4 参考答案

DELETE FROM t_records 
WHERE
    vc_id NOT IN (SELECT 
        t.vc_id
    FROM
        (SELECT 
            MAX(vc_id)
        FROM
            t_records
        GROUP BY vc_name , vc_age
        HAVING MAX(vc_id)) t);

NO3.5 补充说明

如果id也重复，变通找到不同的字段，oracle中有现成的一个字段：oracle的行号 rowid，所以可以很快得到删除语句。如果其他数据库没有这种字段，则可以自己新建一个临时行号字段：rowid，此时删除也可以继续进行了。

题目4

NO4.1 问题描述

找出忘记填写性别的员工

NO.4.2 数据准备

CREATE TABLE t_emp (
	vc_id varchar(20),
	vc_name varchar(20)
);

CREATE TABLE t_sex (
	vc_id varchar(20),
	vc_sex varchar(20)
);

INSERT INTO t_emp
VALUES ('1', 'a');

INSERT INTO t_emp
VALUES ('2', 'b');

INSERT INTO t_emp
VALUES ('3', 'c');

INSERT INTO t_emp
VALUES ('4', 'd');

INSERT INTO t_sex
VALUES ('1', '男');

INSERT INTO t_sex
VALUES ('4', '女');

INSERT INTO t_sex
VALUES ('5', '男');

NO4.3 参考答案

用not in

SELECT 
    *
FROM
    t_emp
WHERE
    vc_id NOT IN (SELECT 
            vc_id
        FROM
            t_sex)

用not exists

SELECT 
    *
FROM
    t_emp t1
WHERE
    NOT EXISTS( SELECT 
            *
        FROM
            t_sex t2
        WHERE
            t1.vc_id = t2.vc_id)

用left join

SELECT 
    t1.*
FROM
    t_emp t1
        LEFT JOIN
    t_sex t2 ON t1.vc_id = t2.vc_id
WHERE
    t2.vc_sex IS NULL

题目5

NO5.1 问题描述

算出部门2中得到奖金最多的员工姓名及奖金

NO5.2 数据准备

CREATE TABLE t_emp (
	emp_no int,
	e_name VARCHAR(10),
	job VARCHAR(10),
	mgr int,
	hiredate DATE,
	sal int,
	comm int,
	deptno int
);

INSERT INTO t_emp
VALUES (1, '员工1', '员工', 3, NULL
	, 2000, 500, 1);

INSERT INTO t_emp
VALUES (2, '员工2', '员工', 3, NULL
	, 2500, 500, 1);

INSERT INTO t_emp
VALUES (3, '员工3', '经理', 5, NULL
	, 4000, 1000, 1);

INSERT INTO t_emp
VALUES (4, '员工4', '财务', 5, NULL
	, 3500, 500, 1);

INSERT INTO t_emp
VALUES (5, '员工5', '主任', NULL, NULL
	, 5000, 2000, 1);

INSERT INTO t_emp
VALUES (6, '员工6', '员工', 8, NULL
	, 2100, 500, 2);

INSERT INTO t_emp
VALUES (7, '员工7', '员工', 8, NULL
	, 2300, 500, 2);

INSERT INTO t_emp
VALUES (8, '员工8', '经理', 10, NULL
	, 5500, 1500, 2);

INSERT INTO t_emp
VALUES (9, '员工9', '财务', 10, NULL
	, 3000, 500, 2);

INSERT INTO t_emp
VALUES (10, '员工10', '主任', NULL, NULL
	, 6000, 2500, 2);

NO5.3 易错SQL写法

SELECT 
    e_name, MAX(comm) max_comm
FROM
    t_emp
WHERE
    deptno = 2;

该SQL由直观的题目要求得到，其实这是一条有语法错误的SQL,因为用到了聚合函数max，所以需要group by。e_name是取的第一条记录的员工姓名，而不是奖金最多的员工姓名，所以是错的。

NO5.4 参考答案

SELECT 
    e_name, comm
FROM
    t_emp
WHERE
    comm = (SELECT 
            MAX(comm) AS max_comm
        FROM
            t_emp
        WHERE
            deptno = 2);

题目6

NO6.1 问题描述

行转列查询

NO6.2 数据准备

CREATE TABLE stu_score (
	grade_id varchar(10) DEFAULT NULL,
	subject_name varchar(10) DEFAULT NULL,
	max_score int(10) DEFAULT NULL
);

INSERT INTO `stu_score` (`grade_id`, `subject_name`, `max_score`)
VALUES ('1', '语文', 98);

INSERT INTO `stu_score` (`grade_id`, `subject_name`, `max_score`)
VALUES ('2', '数学', 95);

INSERT INTO `stu_score` (`grade_id`, `subject_name`, `max_score`)
VALUES ('2', '政治', 87);

INSERT INTO `stu_score` (`grade_id`, `subject_name`, `max_score`)
VALUES ('5', '语文', 97);

INSERT INTO `stu_score` (`grade_id`, `subject_name`, `max_score`)
VALUES ('5', '数学', 100);

INSERT INTO `stu_score` (`grade_id`, `subject_name`, `max_score`)
VALUES ('5', '政治', 92);

NO6.3 参考答案

SELECT CASE grade_id
		WHEN 1 THEN '一年级'
		WHEN 2 THEN '二年级'
		WHEN 5 THEN '五年级'
	END AS '年级', MAX(IFNULL(CASE subject_name
		WHEN '语文' THEN max_score
	END, 0)) AS '语文'
	, MAX(IFNULL(CASE subject_name
		WHEN '数学' THEN max_score
	END, 0)) AS '数学'
	, MAX(IFNULL(CASE subject_name
		WHEN '政治' THEN max_score
	END, 0)) AS '政治'
FROM stu_score
GROUP BY grade_id;

行转列-结果

题目7

NO7.1 问题描述

行转列字段合并

NO7.2 数据准备

CREATE TABLE t_class (
    n_class varchar(20),
    n_time varchar(20),
    n_stu_name varchar(20)
);

INSERT INTO t_class (n_class, n_time,n_stu_name)
VALUES ('101', '2019-01-01 12:12:12','张三');

INSERT INTO t_class (n_class, n_time,n_stu_name)
VALUES ('101', '2019-01-01 11:12:12','李四');

INSERT INTO t_class (n_class, n_time,n_stu_name)
VALUES ('101', '2019-01-01 14:12:12','王五');

INSERT INTO t_class (n_class, n_time,n_stu_name)
VALUES ('104', '2019-01-01 15:12:12','李白');

NO7.3 参考答案

SELECT 
    n_class, GROUP_CONCAT(n_stu_name) AS stuName
FROM
    t_class
GROUP BY n_class;

行转列字段合并

题目8

NO8.1 问题描述

max函数去重，取一条记录，只有ID不同。

NO8.2 数据准备

CREATE TABLE t_records (
    vc_id int,
    vc_name varchar(20),
    vc_age varchar(20)
);

INSERT INTO t_records
VALUES (1, 'a', '20');

INSERT INTO t_records
VALUES (2, 'a', '20');

INSERT INTO t_records
VALUES (3, 'b', '25');

INSERT INTO t_records
VALUES (4, 'c', '27');

INSERT INTO t_records
VALUES (5, 'd', '24');

INSERT INTO t_records
VALUES (6, 'd', '24');

INSERT INTO t_records
VALUES (7, 'd', '24');

INSERT INTO t_records
VALUES (8, 'e', '28');

NO8.3 参考答案

SELECT 
    MAX(vc_id) vc_id, vc_name, vc_age
FROM
    t_records
GROUP BY vc_name , vc_age;

max函数去重

题目9

NO9.1 问题描述

max函数去重，取一条记录。

NO9.2 数据准备

CREATE TABLE t_user_bank (
    vc_id int,
    vc_user_id varchar(20),
    vc_name varchar(20),
    vc_bank varchar(20)
);

INSERT INTO t_user_bank
VALUES (1, 'A001','AC', '浦发银行');

INSERT INTO t_user_bank
VALUES (2, 'A001','AC', '农业银行');

INSERT INTO t_user_bank
VALUES (3, 'A002','Alan Chen', '邮政');

INSERT INTO t_user_bank
VALUES (4, 'A002','Alan Chen', '招商银行');

INSERT INTO t_user_bank
VALUES (5, 'A003','Kyra', '建设银行');

NO9.3 参考答案

一个用户有多个银行账号，但我们只想取用户的一条银行账号。

SELECT 
    MAX(vc_id) vc_id,
    vc_user_id,
    MAX(vc_name) vc_name,
    MAX(vc_bank) vc_bank
FROM
    t_user_bank
GROUP BY vc_user_id;

max函数去重，取一条记录

题目10

NO10.1 问题描述

统计历史累计数据，每天的数据需要进行累加。例如：

5⽉1号 = 5⽉1号的数据

5⽉2号 = 5⽉1号+5⽉2号

5⽉3号 =5⽉1号+5⽉2号+5⽉3号

…

如果当天没有新增数据则跳过统计

NO10.2 数据准备

CREATE TABLE t_login_log (
    id INT,
	user_id INT,
    login_time DATETIME
);

INSERT INTO t_login_log
VALUES (1, '1001','2023-01-01 12:12:23');

INSERT INTO t_login_log
VALUES (2, '1001','2023-01-01 14:12:23');

INSERT INTO t_login_log
VALUES (3, '1002','2023-01-01 10:12:23');

INSERT INTO t_login_log
VALUES (4, '1001','2023-01-02 11:12:23');

INSERT INTO t_login_log
VALUES (5, '1002','2023-01-02 09:12:23');

INSERT INTO t_login_log
VALUES (6, '1001','2023-01-04 15:12:23');

INSERT INTO t_login_log
VALUES (7, '1002','2023-01-04 17:12:23');

NO10.3 参考答案

SELECT 
    c.tart_dat AS item, COUNT(c.tart_dat) AS total_count
FROM
    (SELECT 
        l_tab.tart_dat, r_tab.login_time
    FROM
        (SELECT 
        DATE_FORMAT(t.login_time, '%Y-%m-%d') AS tart_dat
    FROM
        t_login_log t
    GROUP BY tart_dat) l_tab
    INNER JOIN (SELECT 
        DATE_FORMAT(login_time, '%Y-%m-%d') AS login_time
    FROM
        t_login_log) r_tab ON l_tab.tart_dat >= r_tab.login_time) c
GROUP BY c.tart_dat
ORDER BY c.tart_dat

数据累计

第四节系列专题

系列一：case when系列专题

NO1.1 数据准备

CREATE TABLE t_user (
    id INT,
    ch_name VARCHAR(20),
    en_name VARCHAR(20),
    sex INT,
    phone VARCHAR(20),
    age int
);

INSERT INTO t_user
VALUES (1, '张三', 'AC',0,'1331781016x',17);
INSERT INTO t_user
VALUES (2, '', 'AlanChen',1,'1341781016x',9);
INSERT INTO t_user
VALUES (3, '李四', 'Kyra',2,'1301781016x',28);
INSERT INTO t_user
VALUES (4, '王五', 'Tom',1,'1332981014x',62);

NO1.2 数据统计

SELECT 
    COUNT(*) '总用户数',
    COUNT(CASE
        WHEN sex = 0 THEN sex
    END) AS '未知',
    COUNT(CASE
        WHEN sex = 1 THEN sex
    END) AS '男',
    COUNT(CASE
        WHEN sex = 2 THEN sex
    END) AS '女'
FROM
    t_user;

case when数据统计

NO1.3 数据转换

select
	id,
	ch_name,
	en_name,
	sex,
	case sex
		when 0 then '未知'
		when 1 then '男'
		when 2 then '女'
	end as sex_name
from t_user;

数据转换

NO1.4 数据转换用like

select
	id,
	ch_name,
	en_name,
    phone,
	case 
		when phone like '133%' then '电信'
		when phone like '134%' then '移动'
		when phone like '130%' then '联通'
	end as phone_channel
from t_user;

数据转换用like

NO1.4 数据转换用比较运算

select
	id,
	ch_name,
	en_name,
    age,
	case 
		when age<18 then '未成年人'
		when age>=18 and age < 60 then '青年'
        when age>=60 then '老年'
		else '其他'
	end as age_type
from t_user;

数据转换用比较运算

NO1.4 选取字段

SELECT 
    id,
    CASE
        WHEN ch_name IS NOT NULL AND ch_name != '' THEN ch_name
        ELSE en_name
    END AS user_name
FROM
    t_user;

选取字段

系列2：查询统计系列专题

NO2.1 数据准备

-- 学生表
CREATE TABLE student (
    sid varchar(10),
    sName varchar(20),
    sAge varchar(200),
    sSex varchar(10) DEFAULT NULL,
    PRIMARY KEY (sid)
);

-- 课程表
CREATE TABLE course (
    cid varchar(10) NOT NULL,
    cName varchar(10) DEFAULT NULL,
    tid int(20) DEFAULT NULL,
    PRIMARY KEY (cid)
);

-- 成绩表
CREATE TABLE sc (
    sid varchar(10) DEFAULT NULL,
    cid varchar(10) DEFAULT NULL,
    score int(10) DEFAULT NULL
);

-- 教师表
CREATE TABLE teacher (
    tid int(10) DEFAULT NULL,
    tName varchar(10) DEFAULT NULL
);

INSERT INTO teacher (tid, tName)
VALUES (1, '李老师'),
    (2, '何以琛'),
    (3, '叶平');

INSERT INTO student (sid, sName, sAge, sSex)
VALUES ('1001', '张三丰', '1980-10-12 23:12:36', '男'),
    ('1002', '张无极', '1995-10-12 23:12:36', '男'),
    ('1003', '李奎', '1992-10-12 23:12:36', '女'),
    ('1004', '李元宝', '1980-10-12 23:12:36', '女'),
    ('1005', '李世明', '1981-10-12 23:12:36', '男'),
    ('1006', '赵六', '1986-10-12 23:12:36', '男'),
    ('1007', '田七', '1981-10-12 23:12:36', '女');

INSERT INTO sc (sid, cid, score)
VALUES ('1001', '001', 80),
    ('1001', '002', 60),
    ('1001', '003', 75),
    ('1002', '001', 85),
    ('1002', '002', 70),
    ('1003', '004', 100),
    ('1003', '001', 90),
    ('1003', '002', 55),
    ('1004', '002', 65),
    ('1004', '003', 60);

INSERT INTO course (cid, cName, did)
VALUES ('001', '企业管理', 3),
    ('002', '马克思', 3),
    ('003', 'UML', 2),
    ('004', '数据库', 1),
    ('005', '英语', 1);

NO2.2 查询“001”课程比“002”课程成绩高的所有学生的学号

SELECT 
    t1.*, t2.*
FROM
    sc t1,
    sc t2
WHERE
    t1.sid = t2.sid AND t1.cid = '001'
        AND t2.cid = '002'
        AND t1.score > t2.score;

NO2.3 查询平均成绩大于60分的同学的学号和平均成绩

方式一，自己算平均数

SELECT 
    sid, SUM(score) / COUNT(1) AS average
FROM
    sc
GROUP BY sid
HAVING average > 60;

方式二，用平均数函数

SELECT 
    sid, AVG(score) AS average
FROM
    sc
GROUP BY sid
HAVING average > 60;

NO2.4 查询所有同学的学号、姓名、选课数、总成绩

SELECT 
    t2.sid, t2.sName, t1.score_count, t1.score_sum
FROM
    (SELECT 
        sid, COUNT(1) AS score_count, SUM(score) AS score_sum
    FROM
        sc
    GROUP BY sid) t1,
    student t2
WHERE
    t1.sid = t2.sid;

NO2.5 查询姓“李”的老师的个数

SELECT 
    COUNT(1)
FROM
    teacher
WHERE
    tName LIKE '李%';

NO2.6 查询没学过“叶平”老师课的同学的学号、姓名

方式一：两表关联+ group by

select sid, sName
from student
where sid not in (
	select sid
	from (
		select sid
		from sc
		where cid in (
			select t1.cid
			from course t1, teacher t2
			where t1.tid = t2.tid
				and t2.tName = '叶平'
		)
		group by sid
	) t
);

方式二：三表关联+ distinct

SELECT 
    t.sid, t.sName
FROM
    student t
WHERE
    t.sid NOT IN (SELECT DISTINCT
            sid
        FROM
            sc t1,
            course t2,
            teacher t3
        WHERE
            t1.cid = t2.cid AND t2.tid = t3.tid
                AND t3.tName = '叶平')

NO2.7 查询学过“001”并且也学过编号“002”课程的同学的学号、姓名

易错SQL写法

SELECT 
    t1.sid, t1.sName
FROM
    student t1,
    (SELECT DISTINCT
        sid AS sid
    FROM
        sc
    WHERE
        cid IN ('001' , '002')) t2
WHERE
    t1.sid = t2.sid;

in是“或”的意思，这个查的是：学过“001” 或者学过编号“002”课程的同学的学号、姓名

参考答案

SELECT 
    t3.sid, t3.sName
FROM
    sc t1,
    sc t2,
    student t3
WHERE
    t1.sid = t2.sid AND t2.sid = t3.sid
        AND t1.cid = '001'
        AND t2.cid = '002'

NO2.8 查询所有课程成绩小于80分的同学的学号、姓名

SELECT 
    t1.sid, t1.sName
FROM
    student t1,
    (SELECT DISTINCT
        sid AS sid
    FROM
        sc
    WHERE
        sid NOT IN (SELECT DISTINCT
                sid AS sid
            FROM
                sc
            WHERE
                score >= 80)) t2
WHERE
    t1.sid = t2.sid;

展开阅读全文

页面更新：2024-04-27

标签：行号学号字段语句函数题目姓名员工答案同学数据金三银

1 2 3 4 5

金三银四 面试准备SQL题(附答案)

第一节 前言

第二节 推荐SQL在线编辑工具