C# .NET面试系列十一：数据库SQL查询（附建表语句）

第1题

用一条 SQL 语句 查询出每门课都大于80 分的学生姓名

建表语句：

create table tableA
(
	name varchar(10),
	kecheng varchar(10),
	fenshu int(11)
) DEFAULT CHARSET = 'utf8';

插入数据

insert into tableA values ('张三', '语文', 81);
insert into tableA values ('张三', '数学', 75);
insert into tableA values ('李四', '语文', 76);
insert into tableA values ('李四', '数学', 90);
insert into tableA values ('王五', '语文', 81);
insert into tableA values ('王五', '数学', 100);
insert into tableA values ('王五', '英语', 90);

答案有2种：

-- 法1：使用了GROUP BY和HAVING子句，首先按姓名分组，然后使用HAVING子句过滤出分数最低的分数大于80分的学生姓名。这样也能保证查询出每门课都大于80分的学生姓名。
SELECT name
FROM tableA
GROUP BY name
HAVING MIN(fenshu) > 80;

-- 法2：使用了子查询，首先在子查询中找出所有分数不低于80分的学生姓名，然后在外层查询中找出不在这个子查询结果中的学生姓名。这样就能保证查询出每门课都大于80分的学生姓名。
SELECT DISTINCT NAME 
FROM
	tableA 
WHERE
	NAME NOT IN (
	SELECT DISTINCT NAME 
	FROM
		tableA 
WHERE
	fenshu <= 80)

第2题

一道SQL语句面试题，关于group by表内容：

如果要生成下列结果, 该如何写sql语句?

建表语句：

create table tableb (
	rq varchar(10),
	shengfu nchar(1)
) DEFAULT CHARSET = 'utf8';

插入数据：

insert into tableb values('2005-05-09', '胜');
insert into tableb values('2005-05-09', '胜');
insert into tableb values('2005-05-09', '负');
insert into tableb values('2005-05-09', '负');
insert into tableb values('2005-05-10', '胜');
insert into tableb values('2005-05-10', '负');
insert into tableb values('2005-05-10', '负')

答案：

SELECT
	rq,
	sum( CASE WHEN shengfu = '胜' THEN 1 ELSE 0 END ) '胜',
	sum( CASE WHEN shengfu = '负' THEN 1 ELSE 0 END ) '负' 
FROM
	tableb 
GROUP BY
	rq

-- 使用 CASE WHEN 来对每个胜负情况进行条件判断，然后将符合条件的结果进行计数。
-- 使用 SUM 对计数结果进行求和。
-- 使用 CONCAT 将胜负的计数结果连接成字符串，形成最终的结果。

第3题

一个叫 team 的表，里面只有一个字段name, 一共有4 条纪录，分别是a,b,c,d, 对应四个球队，现在四个球对进行比赛，用一条sql 语句显示所有可能的比赛组合

team表建表语句:

CREATE TABLE team (
	name varchar(20)
) DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO team VALUES ('a');  
INSERT INTO team VALUES ('b');  
INSERT INTO team VALUES ('c');  
INSERT INTO team VALUES ('d')

答案：

SELECT t1.name AS team1, t2.name AS team2
FROM team t1
JOIN team t2 ON t1.name < t2.name;

-- 这条 SQL 语句使用自联接将 team 表与自身连接，通过比较球队的名称确保每个组合只显示一次。t1.name < t2.name 的条件保证了不会重复显示同一场比赛。

结果如图所示：

第4题

怎么把这样一个表儿

查成这样一个结果

建表语句：

CREATE TABLE table1 (
	`year` int (11),
	`month` int (11),
	`amount` DECIMAL
) DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO table1 VALUES (1991, 1, 1.1);  
INSERT INTO table1 VALUES (1991, 2, 1.2); 
INSERT INTO table1 VALUES (1991, 3, 1.3); 
INSERT INTO table1 VALUES (1991, 4, 1.4); 
INSERT INTO table1 VALUES (1992, 1, 2.1);  
INSERT INTO table1 VALUES (1992, 2, 2.2); 
INSERT INTO table1 VALUES (1992, 3, 2.3); 
INSERT INTO table1 VALUES (1992, 4, 2.4);

答案：

SELECT
  year,
  MAX(CASE WHEN month = 1 THEN amount END) AS m1,
  MAX(CASE WHEN month = 2 THEN amount END) AS m2,
  MAX(CASE WHEN month = 3 THEN amount END) AS m3,
  MAX(CASE WHEN month = 4 THEN amount END) AS m4
FROM table1
GROUP BY year;
-- 这条 SQL 语句使用了条件聚合函数 MAX(CASE WHEN ...) 来将每个月份的数据转置成表的列。每个 MAX(CASE WHEN ...) 子句对应一个月份，当月份匹配时，将 amount 的值取出。

第5题

一张部门表，一张员工表

部门表建表语句：

create table dpt
(
	id int(11),
	department varchar(12) 
)DEFAULT CHARSET=utf8;

插入数据：

insert into dpt values(1, '设计');
insert into dpt values(2, '市场');
insert into dpt values(3, '售后')

员工表建表语句：

create table emy
(
	id int(11),
	dptID int(11),
	`name` varchar(12)
) DEFAULT CHARSET=utf8;

插入数据

insert into emy values(1, 1, '张三');
insert into emy values(2, 1, '李四');
insert into emy values(3, 2, '王五');
insert into emy values(4, 3, '彭六');
insert into emy values(5, 4, '陈七')

第6题

删除除了自动编号不同, 其他都相同的学生冗余信息

学生表建表语句：

create table tablestudent
(
	id int(11),
	studentno varchar(10),
	studentname varchar(10),
	classno varchar(10),
	classname varchar (11),
	score int (11)
) DEFAULT CHARSET = 'utf8';

插入数据:

insert into tablestudent values (1, '2005001', '张三', '0001', '数学',69);
insert into tablestudent values (2, '2005002', '李四', '0001', '数学',69);
insert into tablestudent values (3, '2005001', '张三', '0001', '数学',69)

如图所示：

答案：

DELETE ts1
FROM tablestudent ts1
JOIN tablestudent ts2 ON ts1.studentno = ts2.studentno
                      AND ts1.studentname = ts2.studentname
                      AND ts1.classno = ts2.classno
                      AND ts1.classname = ts2.classname
                      AND ts1.score = ts2.score
                      AND ts1.id < ts2.id;
-- 这条 SQL 语句使用了自联接（self-join），将表自身与自身连接，通过比较各个字段来找到除了自动编号不同以外其他字段都相同的冗余信息。ts1.id < ts2.id 保证了只删除自动编号较小的冗余信息。

第7题

关于年度、季度SQL统计查询的面试题

A表 - 销售数据表，其中adddate是销售时间，company是客户公司，money是销售金额，sales是销售员

B表 - 产品销售表，aid对应A表中的id，pid对应是C表中产品表。

C表 - 产品类型表

1、销售数据表建表语句：

CREATE TABLE `a` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `adddate` datetime NOT NULL,
  `company` varchar(10) DEFAULT NULL,
  `money` decimal(7,2) DEFAULT NULL,
  `sales` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

插入数据：

insert  into `a`(`id`,`adddate`,`company`,`money`,`sales`) 
values 
(1, '2011-01-02 00:00:00', 'A', '10000.00', '张三'),
(2, '2011-07-03 00:00:00', 'B', '50000.00', '王五'),
(3, '2011-09-03 00:00:00', 'A', '20000.00', '赵六'),
(4, '2012-07-03 00:00:00', 'A', '10000.00', '李四'),
(5, '2013-10-03 00:00:00', 'B', '5000.00', '麻子'),
(6, '2013-07-04 00:00:00', 'B', '6000.00', '小柒');

2、产品销售表建表语句:

DROP TABLE IF EXISTS `b`;
CREATE TABLE `b` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `product` varchar(20) DEFAULT NULL,
  `pid` int(11) DEFAULT NULL,
  `price` decimal(7,2) DEFAULT NULL,
  `number` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

插入数据：

insert  into `b`(`id`,`product`,`pid`,`price`,`number`) 
values 
(1, 'AC1100', 1, '10000.00', 2),
(2, 'AC1200', 1, '20000.00', 3),
(3, 'SSL1100', 2, '10000.00', 1),
(4, 'WOC1100', 3, '10000.00', 1);

3、产品类型表建表语句:

CREATE TABLE `c` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

插入数据：

insert  into `c`(`id`,`type`) 
values
(1, 'AC'),
(2, 'SSL'),
(3, 'WOC');

问题1：请使用SQL语句统计2011年至2013年三年中每个季度的销售金额?

SELECT
  YEAR(adddate) AS year,
  QUARTER(adddate) AS quarter,
  SUM(money) AS total_sales
FROM
  a
WHERE
  YEAR(adddate) BETWEEN 2011 AND 2013
GROUP BY
  YEAR(adddate),
  QUARTER(adddate)
ORDER BY
  YEAR(adddate),
  QUARTER(adddate);

问题2：请使用SQL语句按如下格式统计2013年销售金额前5位的销售员

SELECT
  sales,
  SUM(money) AS total_sales
FROM
  a
WHERE
  YEAR(adddate) = 2013
GROUP BY
  sales
ORDER BY
  total_sales DESC
LIMIT 5;

问题3：请用SQL语句统计出哪个产品线的销售金额最多?

SELECT c.type, SUM(a.money) AS total_sales
FROM a
JOIN b ON a.id = b.pid
JOIN c ON b.pid = c.id
GROUP BY c.type
ORDER BY total_sales DESC
LIMIT 1;

第8题

数据库的表设计如下：

部门表：部门编号，部门名称，地址；

员工表：员工编号，员工名字，职务，管理编号，入职日期，薪资，奖金，部门编号；

部门表建表语句：

CREATE TABLE dept(
	deptno INT PRIMARY KEY,
	dname VARCHAR(20) NOT NULL,
	loc VARCHAR(20) NOT NULL
) DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEWTORK');  
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');  
INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO');  
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON');

如图所示：

员工表建表语句：

CREATE TABLE emp(
empno INT PRIMARY KEY,
ename VARCHAR(20) NOT NULL,    
job VARCHAR(20) CHECK (job IN('CLERK','SALESMAN','MANAGER','ANALYST')),
mgp INT,  
hiredate DATE ,
sal DECIMAL(10,2),
comm DECIMAL(10,2),  
deptno INT,  
CONSTRAINT pk_we FOREIGN KEY (deptno) REFERENCES dept (deptno)  
)DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO emp VALUES
(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 1640, Null, 20),
(7499, 'ALLEN', 'SALESMAN',7698, '1981-02-20', 11400, 300, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 5200, 500, 30),
(7566, 'JOENS', 'MANAGER', 7839, '1981-04-02', 7015, Null, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 5200, 1400, 30),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 5900, Null, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2470, Null, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3040, Null, 20)

如图所示：

问题如下：

1、列出至少有一个雇员的所有部门

SELECT DISTINCT d.deptno, d.dname, d.loc
FROM dept d
JOIN emp e ON d.deptno = e.deptno;

2、列出薪金比"SMITH"多的所有雇员

SELECT *
FROM emp
WHERE sal > (SELECT sal FROM emp WHERE ename = 'SMITH');

3、列出入职日期早于其直接上级的所有雇员

SELECT e1.*
FROM emp e1
INNER JOIN emp e2 ON e1.mgp = e2.empno
WHERE e1.hiredate < e2.hiredate;

4、找员工姓名和直接上级的名字

SELECT e1.ename AS emp_name, e2.ename AS manager_name
FROM emp e1
LEFT JOIN emp e2 ON e1.mgp = e2.empno;

5、显示部门名称和人数

SELECT d.dname AS department_name, COUNT(e.empno) AS num_of_employees
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno
GROUP BY d.dname;

6、显示每个部门的最高工资的员工

SELECT e1.*
FROM emp e1
INNER JOIN (
    SELECT deptno, MAX(sal) AS max_sal
    FROM emp
    GROUP BY deptno
) e2 ON e1.deptno = e2.deptno AND e1.sal = e2.max_sal;

7、显示每个部门的工资前2名的员工

SELECT *
FROM (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS row_num
    FROM emp
) ranked
WHERE row_num <= 2;


-- 这段代码使用了 SQL 中的窗口函数 ROW_NUMBER()，这个函数主要用于给查询结果的行加上一个序号，这个序号根据指定的排序规则来确定。让我来解释一下这句代码的具体含义：
-- ROW_NUMBER(): 这是窗口函数的调用，它会为每一行结果分配一个唯一的行号。
-- OVER (PARTITION BY deptno ORDER BY sal DESC): 这部分是窗口函数的 OVER 子句，指定了窗口函数的分区和排序方式。具体来说：
-- PARTITION BY deptno: 意味着窗口函数会根据 deptno 列的值进行分区，也就是说，每个不同的 deptno 值会形成一个分区。
-- ORDER BY sal DESC: 指定了在每个分区内按照 sal 列的值降序排序，也就是工资高的排在前面。
-- AS row_num: 这部分是将窗口函数的结果命名为 row_num，作为一个新的列名。
-- 因此，整个语句的作用是为每个部门内的员工按照工资从高到低进行排序，并为每个排名结果分配一个行号。

SELECT e1.*
FROM emp e1
WHERE (
    SELECT COUNT(DISTINCT e2.sal)
    FROM emp e2
    WHERE e2.deptno = e1.deptno AND e2.sal >= e1.sal
) <= 2;

本系列文章题目摘自网络，答案重新梳理