SQL练习题 spark sql例题

转载

落笔成诗 2023-06-20 10:05:27

文章标签 SQL练习题 spark 升序建表多表查询 文章分类 Spark 大数据

Sql经典50题

建表
插入数据
题目

1. 简单查询
2. 汇总分析
3. 复杂查询
4. 多表查询

Sql经典50题

SQL练习题 spark sql例题_SQL练习题 spark

建表

SQL练习题 spark sql例题_建表_02

学生表

CREATE TABLE student (
s_id VARCHAR(30),
s_name VARCHAR(30) NOT NULL,
s_birth date NOT NULL,
s_sex VARCHAR(10) NOT NULL,
PRIMARY KEY(s_id)
);

成绩表

CREATE TABLE score (
s_id VARCHAR(30),
c_id VARCHAR(30),
s_score VARCHAR(3),
PRIMARY KEY(s_id, c_id)
);

课程表

CREATE TABLE course (
c_id VARCHAR(30),
c_name VARCHAR(30) NOT NULL,
t_id VARCHAR(30) NOT NULL,
PRIMARY KEY(c_id)
);

教师表

CREATE TABLE teacher (
t_id VARCHAR(30),
t_name VARCHAR(30) NOT NULL,
PRIMARY KEY(t_id)
);

插入数据

学生表

insert into student(s_id,s_name,s_birth,s_sex) 
values('0001' , '猴子' , '1989-01-01' , '男');

insert into student(s_id,s_name,s_birth,s_sex) 
values('0002' , '猴子' , '1990-12-21' , '女');

insert into student(s_id,s_name,s_birth,s_sex) 
values('0003' , '马云' , '1991-12-21' , '男');

insert into student(s_id,s_name,s_birth,s_sex) 
values('0004' , '王思聪' , '1990-05-20' , '男');

成绩表

insert into score(s_id,c_id,s_score) 
values('0001' , '0001' , 80);

insert into score(s_id,c_id,s_score) 
values('0001' , '0002' , 90);

insert into score(s_id,c_id,s_score) 
values('0001' , '0003' , 99);

insert into score(s_id,c_id,s_score) 
values('0002' , '0002' , 60);

insert into score(s_id,c_id,s_score) 
values('0002' , '0003' , 80);

insert into score(s_id,c_id,s_score) 
values('0003' , '0001' , 80);

insert into score(s_id,c_id,s_score) 
values('0003' , '0002' , 80);

insert into score(s_id,c_id,s_score) 
values('0003' , '0003' , 80);

课程表

insert into course(c_id,c_name,t_id)
values('0001' , '语文' , '0002');

insert into course(c_id,c_name,t_id)
values('0002' , '数学' , '0001');

insert into course(c_id,c_name,t_id)
values('0003' , '英语' , '0003');

教师表

-- 教师表：添加数据
insert into teacher(t_id,t_name) 
values('0001' , '孟扎扎');

insert into teacher(t_id,t_name) 
values('0002' , '马化腾');

-- 这里的t_name是空值（null）
insert into teacher(t_id,t_name) 
values('0003' , null);

-- 这里的t_name是空字符串（''）
insert into teacher(t_id,t_name) 
values('0004' , '');

题目

1. 简单查询

-- 查询姓“猴”的学生名单
SELECT * from student where s_name LIKE '猴%';

-- 查询姓名中最后一个字是“猴”的学生名单
SELECT * FROM student where s_name LIKE '%猴';

-- 查询姓名中带“猴”字的学生名单
SELECT * FROM student where s_name LIKE '%猴%';

-- 查询姓“孟”老师的个数
SELECT COUNT(t_id) from teacher WHERE t_name LIKE '孟%';

2. 汇总分析

汇总分析

-- 查询课程编号为“0002”的总成绩
SELECT SUM(s_score) FROM score WHERE c_id = '0002';

-- 查询选了课程的学生数
SELECT COUNT(DISTINCT(s_id)) AS 学生人数 FROM score;

分组

-- 查询各科成绩最高和最低的分
SELECT c_id, MAX(s_score) AS 最高分, MIN(s_score) AS 最低分 FROM score GROUP BY c_id;

-- 查询每门课程被选修的学生数
SELECT c_id, COUNT(s_id) AS 学生数 FROM score GROUP BY c_id;

-- 查询男生，女生人数
SELECT s_sex, COUNT(*) AS 人数 FROM student GROUP BY s_sex;

分组结果的条件

-- 查询平均成绩大于60分学生的学号和平均成绩
SELECT s_id, AVG(s_score) from score GROUP BY s_id HAVING AVG(s_score) > 60;

-- 查询至少选修两门课程的学生学号
SELECT s_id FROM score GROUP BY s_id HAVING COUNT(c_id) >= 2;

-- 查询同名同姓的学生名单并统计同名人数
SELECT s_name, COUNT(*) AS 人数 FROM student GROUP BY s_name HAVING COUNT(*) >= 2; 

-- 查询不及格的课程并按课程号从大到小排列
SELECT c_id FROM score WHERE s_score < 60 ORDER BY c_id DESC;

-- 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
SELECT c_id, AVG(s_score) AS 平均成绩 FROM score GROUP BY c_id ORDER BY AVG(s_score) ASC, c_id DESC;

-- 检索课程编号为“0004”且分数小于60的学生学号，结果按按分数降序排列
SELECT s_id, s_score FROM score WHERE c_id = 0004 AND s_score < 60 ORDER BY s_score DESC;

-- 统计每门课程的学生选修人数(超过2人的课程才统计)
要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序
SELECT c_id, COUNT(s_id) AS 选修人数 FROM score GROUP BY c_id HAVING COUNT(*) >= 2 ORDER BY COUNT(*) DESC, c_id ASC;

-- 查询两门以上不及格课程的同学的学号及其平均成绩
SELECT s_id, AVG(s_score) AS 平均成绩 FROM score WHERE s_score < 60 GROUP BY s_id HAVING COUNT(c_id) > 2;

汇总分析

-- 查询学生的总成绩并进行排名
SELECT s_id, SUM(s_score) AS 总成绩 FROM score GROUP BY s_id ORDER BY SUM(s_score) DESC;

-- 查询平均成绩大于60分的学生的学号和平均成绩
SELECT s_id, AVG(s_score) AS 平均成绩 FROM score GROUP BY s_id HAVING AVG(s_score) > 60;

3. 复杂查询

-- 查询所有课程成绩小于60分学生的学号、姓名
SELECT s_id, s_name FROM student WHERE s_id IN (SELECT s_id FROM score WHERE s_score < 60);

-- 查询没有学全所有课的学生的学号、姓名
SELECT s_id, s_name FROM student WHERE s_id IN (
SELECT s_id FROM score GROUP BY s_id HAVING COUNT(c_id) IN (
SELECT COUNT(c_id) FROM course));

-- 查询出只选修了两门课程的全部学生的学号和姓名
SELECT s_id, s_name FROM student WHERE s_id IN (SELECT s_id FROM score GROUP BY s_id HAVING COUNT(c_id) = 2);

-- 1990年出生的学生名单
SELECT s_name FROM student WHERE YEAR(s_birth) = 1990;

-- 查询各科成绩前两名的记录
(SELECT c_id, s_score FROM score WHERE s_id = '0001' GROUP BY c_id ORDER BY s_score DESC LIMIT 2)
UNION ALL
(SELECT c_id, s_score FROM score WHERE s_id = '0002' GROUP BY c_id ORDER BY s_score DESC LIMIT 2)
UNION ALL
(SELECT c_id, s_score FROM score WHERE s_id = '0003' GROUP BY c_id ORDER BY s_score DESC LIMIT 2);

-- 查询各学生的年龄（精确到月份）
SELECT s_id, TIMESTAMPDIFF(MONTH, s_birth, now()) / 12 FROM student;