#面试题实战
USE train;
#1.查找学生表中所有重复的学生名
#建立表
DROP TABLE IF EXISTS student;
CREATE TABLE student(
学号 VARCHAR(255) PRIMARY KEY,
姓名 VARCHAR(255) NOT NULL
);
INSERT INTO student
VALUES ('0001', '猴子'),
('0002','马云'),
('0003', '王思聪'),
('0004', '王思聪'),
('0005', '猴子');
#查找
SELECT 姓名
FROM student
GROUP BY 姓名
HAVING COUNT(*) >= 2;
#2.现在要查找出所有学生的学号，姓名，课程和成绩。
#创建表
DROP TABLE IF EXISTS 学生成绩表;
CREATE TABLE 学生成绩表(
学号 VARCHAR(255) NOT NULL,
课程 VARCHAR(255) NOT NULL,
成绩 INT NOT NULL
);
INSERT INTO 学生成绩表
VALUES('1','语文', 90),
('1', '数学',65),
('2', '语文', 68),
('2', '数学', 96),
('3', '数学',55 );
DROP TABLE IF EXISTS 学生信息表;
CREATE TABLE 学生信息表(
学号 VARCHAR(255) NOT NULL,
姓名 VARCHAR(255) NOT NULL
);
INSERT INTO 学生信息表
VALUES('1','张三'),
('2', '李四'),
('3', '王五'),
('4', '赵六');
#查询
SELECT data1.学号, data1.姓名, 课程, 成绩
FROM 学生信息表 AS data1
LEFT JOIN 学生成绩表 AS data2
ON data1.学号 = data2.学号;
#3.请问不是近视眼的学生都有谁？
#建立表
DROP TABLE IF EXISTS 学生表;
CREATE TABLE 学生表(
学号 VARCHAR(255) PRIMARY KEY,
姓名 VARCHAR(255) NOT NULL
);
INSERT INTO 学生表
VALUES ('0001', '周周'),
('0002','李李'),
('0003', '王王'),
('0004', '张张'),
('0005', '猴子');
DROP TABLE IF EXISTS 近视学生表;
CREATE TABLE 近视学生表(
序号 VARCHAR(255) PRIMARY KEY,
学生学号 VARCHAR(255) NOT NULL
);
INSERT INTO 近视学生表
VALUES ('1', '0001'),
('2','0002'),
('3', '0003');
#查找
SELECT 姓名
FROM 学生表
WHERE 学号 NOT IN (
SELECT 学生学号
FROM 近视学生表
);
#4.查找当前所有雇员入职以来的薪水涨幅，给出雇员编号以及其对应的薪水涨幅，并按照薪水涨幅进行升序。
DROP TABLE IF EXISTS 雇员表;
CREATE TABLE 雇员表(
雇员编号 VARCHAR(255) PRIMARY KEY,
出生日期 DATE NOT NULL,
姓名 VARCHAR(255) NOT NULL,
性别 VARCHAR(255) NOT NULL,
雇佣日期 DATE NOT NULL
);
INSERT INTO 雇员表
VALUES ('10002', '1976-09-09', '小明', '男', '2001-08-02'),
('10005','1973-08-07',' 小红', '女', '2001-09-09'),
('10006', '1980-08-28', '小兰', '女', '2001-08-02');
DROP TABLE IF EXISTS 薪水表;
CREATE TABLE 薪水表(
雇员编号 VARCHAR(255) NOT NULL,
薪水 INT NOT NULL,
起始日期 DATE NOT NULL,
结束日期 DATE NOT NULL
);
INSERT INTO 薪水表
VALUES ('10002', 72527, '2001-08-02', '2003-01-01'),
('10002', 75432, '2003-01-01', '2004-01-01'),
('10005', 94692, '2001-09-09', '2003-01-01'),
('10006', 43311, '2001-08-02', '2004-01-01');
#查询
#1)在职雇员的当前薪水
(SELECT 雇员编号, 薪水 AS 当前薪水
FROM 薪水表
WHERE 结束日期 = '2004-01-01') AS temp1;
#2)在职雇员的初始薪水
SELECT data1.雇员编号, 薪水 AS 初始薪水
FROM 雇员表 AS data1 LEFT JOIN 薪水表 AS data2
ON data1.雇佣日期 = data2.起始日期 AND data1.雇员编号 = data2.雇员编号
WHERE data1.雇员编号 IN(
SELECT 雇员编号
FROM 薪水表
WHERE 结束日期 = '2004-01-01'
) AS temp2;
#3）涨薪情况
SELECT temp1.雇员编号, 当前薪水 - 初始薪水 AS 薪水涨幅
FROM
(SELECT 雇员编号, 薪水 AS 当前薪水
FROM 薪水表
WHERE 结束日期 = '2004-01-01') AS temp1 LEFT JOIN
(SELECT data1.雇员编号, 薪水 AS 初始薪水
FROM 雇员表 AS data1 LEFT JOIN 薪水表 AS data2
ON data1.雇佣日期 = data2.起始日期 AND data1.雇员编号 = data2.雇员编号
WHERE data1.雇员编号 IN(
SELECT 雇员编号
FROM 薪水表
WHERE 结束日期 = '2004-01-01'
) )AS temp2
ON temp1.雇员编号 = temp2.雇员编号
ORDER BY 薪水涨幅 ASC;
#5.请找出所有比前一天（昨天）营业额更高的数据。（前一天的意思，如果“当天”是1月，“昨天”（前一天）就是1号）
DROP TABLE IF EXISTS 日销;
CREATE TABLE 日销(
ID INT PRIMARY KEY,
日期 DATE NOT NULL,
营业额（万元） INT NOT NULL
);
INSERT INTO 日销
VALUES(1, '2019-01-01', 97),
(2, '2019-01-02', 87),
(3, '2019-01-03', 88),
(4, '2019-01-04', 98),
(5, '2019-01-05', 100),
(6, '2019-01-06', 80),
(7, '2019-01-07', 77),
(8, '2019-01-08', 92);
#查询
SELECT data1.ID,
data2.日期 AS 日期,
data2.营业额（万元） AS 营业额（万元）
FROM 日销 AS data1 LEFT JOIN 日销 AS data2
ON data1.日期 + 1 = data2.日期
WHERE data2.日期 IS NOT NULL AND data1.营业额（万元） < data2.营业额（万元）;
#5.小明想改变相邻俩学生的座位。
#建表
DROP TABLE IF EXISTS 学生表;
CREATE TABLE 学生表(
座位号 INT PRIMARY KEY,
姓名 VARCHAR(255) NOT NULL,
出生日期 DATE NOT NULL,
性别 VARCHAR(255) NOT NULL
);
INSERT INTO 学生表
VALUES(1, "猴子1", '1989-01-01', '男'),
(2, "猴子2", '1990-12-21', '女'),
(3, "马云", '1991-12-21', '男'),
(4, "王思聪", '1990-05-20', '男');
#查询
SELECT (
CASE
WHEN MOD(座位号, 2) = 1 THEN 座位号+1
WHEN MOD(座位号, 2) = 0 THEN 座位号-1
END
)AS 新座位号, 姓名
FROM 学生表;
#6. 筛选出2017年入学的“计算机”专业年龄最小的3位同学名单（姓名、年龄）
#建表
DROP TABLE IF EXISTS 学生表;
CREATE TABLE 学生表(
姓名 VARCHAR(255) PRIMARY KEY,
学号 VARCHAR(255) NOT NULL,
班级 VARCHAR(255) NOT NULL,
入学时间 DATE NOT NULL,
年龄 INT NOT NULL,
专业 VARCHAR(255) NOT NULL
);
INSERT INTO 学生表
VALUES('小赵', '0001', '1班', '2016-09-01', 19, '计算机'),
('小钱', '0002', '1班', '2017-09-01', 21, '计算机'),
('小孙', '0003', '2班', '2017-09-01', 19, '金融'),
('小李', '0004', '3班', '2017-09-01', 17, '计算机'),
('小周', '0005', '3班', '2017-09-01', 20, '计算机'),
('小吴', '0006', '3班', '2017-09-01', 18, '计算机');
DROP TABLE IF EXISTS 成绩表;
CREATE TABLE 成绩表(
学号 VARCHAR(255) NOT NULL,
课程号 VARCHAR(255) NOT NULL,
分数 INT NOT NULL
);
INSERT INTO 成绩表
VALUES ('0001', '01', 90),
('0002', '01', 70),
('0002', '02', 84),
('0003', '01', 90),
('0003', '03', 80),
('0004', '01', 90),
('0004', '02', 60),
('0005', '01', 85),
('0006', '02', 70);
#查询
#筛选出2017年入学的“计算机”专业年龄最小的3位同学名单（姓名、年龄）
SELECT 姓名, 年龄
FROM 学生表
WHERE YEAR(入学时间) = 2017 AND 专业 = '计算机'
ORDER BY 年龄
LIMIT 3;
#7. 统计每个班同学各科成绩平均分大于80分的人数和人数占比
#每个班级的人数
SELECT 班级, COUNT(*) AS 总人数
FROM 学生表
GROUP BY 班级;
#每个班级同学各科平均成绩大于80的人数
SELECT 班级, COUNT(*) AS 人数
FROM (
SELECT 班级, data2.学号, AVG(分数) AS 平均成绩
FROM 学生表 AS data1 RIGHT JOIN 成绩表 AS data2
ON data1.学号 = data2.学号
GROUP BY 班级, data2.学号
HAVING 平均成绩 > 80
) AS temp
GROUP BY 班级
#统计占比
SELECT temp1.班级, ROUND(人数/总人数, 2) AS 占比
FROM(
SELECT 班级, COUNT(*) AS 总人数
FROM 学生表
GROUP BY 班级
) AS temp1 LEFT JOIN (
SELECT 班级, COUNT(*) AS 人数
FROM (
SELECT 班级, data2.学号, AVG(分数) AS 平均成绩
FROM 学生表 AS data1 RIGHT JOIN 成绩表 AS data2
ON data1.学号 = data2.学号
GROUP BY 班级, data2.学号
HAVING 平均成绩 > 80
) AS temp
GROUP BY 班级
)AS temp2 ON temp1.班级 = temp2.班级;
#8.行列互换*****
#建表
DROP IF EXISTS cook;
CREATE TABLE cook(
年 INT NOT NULL,
月 INT,
值 FLOAT(1)
);
INSERT INTO cook
VALUES (2009, 1, 1.1),
(2009, 2, 1.2),
(2009, 3, 1.3),
(2009, 4, 1.4),
(2010, 1, 2.1),
(2010, 2, 2.2),
(2010, 3, 2.3),
(2010, 4, 2.4);
SELECT 年,
MAX(CASE 月 WHEN '1' THEN 值 ELSE 0 END) AS 'm1',
MAX(CASE 月 WHEN '2' THEN 值 ELSE 0 END) AS 'm2',
MAX(CASE 月 WHEN '3' THEN 值 ELSE 0 END) AS 'm3',
MAX(CASE 月 WHEN '4' THEN 值 ELSE 0 END) AS 'm4'
FROM cook
GROUP BY 年;
#9.查找所有至少连续出现3次的成绩。
DROP TABLE IF EXISTS 成绩表;
CREATE TABLE 成绩表(
学号 VARCHAR(255) PRIMARY KEY,
成绩 INT NOT NULL
);
INSERT INTO 成绩表
VALUES(0001, 89),
(0002, 76),
(0003, 76),
(0004, 84),
(0005, 84),
(0006, 84),
(0007, 76),
(0008, 91),
(0009, 88),
(00010, 86);
#查询
SELECT data1.成绩
FROM 成绩表 AS data1, 成绩表 AS data2, 成绩表 AS data3
WHERE data1.学号 + 1 = data2.学号 AND data2.学号 + 1 = data3.学号
AND data1.成绩 = data2.成绩 AND data2.成绩 = data3.成绩
#10.某日活跃用户（用户id）在后续的一周内的留存情况（计算次日留存用户数，3日留存用户数，7日留存用户数）
#(没有数据)
SELECT a.登陆时间,COUNT(DISTINCT a.用户id) AS 活跃用户数,
COUNT(DISTINCT WHEN 时间间隔=1 THEN 用户id ELSE NULL END) AS 次日留存数,
COUNT(DISTINCT WHEN 时间间隔=1 THEN 用户id ELSE NULL END) AS 次日留存数 / COUNT(DISTINCT a.用户id) AS 次日留存率,
COUNT(DISTINCT WHEN 时间间隔=3 THEN 用户id ELSE NULL END) AS 三日留存数,
COUNT(DISTINCT WHEN 时间间隔=3 THEN 用户id ELSE NULL END) AS 三日留存数 / COUNT(DISTINCT a.用户id) AS 三日留存率,
COUNT(DISTINCT WHEN 时间间隔=7 THEN 用户id ELSE NULL END) AS 七日留存数,
COUNT(DISTINCT WHEN 时间间隔=7 THEN 用户id ELSE NULL END) AS 七日留存数 / COUNT(DISTINCT a.用户id) AS 七日留存率
FROM
(SELECT *,TIMESTAMPDIFF(DAY,a.登陆时间,b.登陆时间) AS 时间间隔
FROM
(SELECT a.用户id,a.登陆时间,b.登陆时间
FROM 用户行为信息表 AS a
LEFT JOIN 用户行为信息表 AS b
ON a.用户id = b.用户id
WHERE a.应用名称= '相机') AS c
) AS d
GROUP BY a.登陆时间;
( 粤ICP备18085999号-1 | 粤公网安备 44051102000585号)
狗仔卡
发表于 2020-7-22 18:05:06
置顶卡
千斤顶
显身卡
楼主