/*
* 复杂sql练习
* 2022-04-01
*author:Rabin
*address:shanghai
*题目来源:https://zhuanlan.zhihu.com/p/38354000,作者:猴子数据分析
使用环境:sqlserver
*
*学生表:student(学号,学生姓名,出生年月,性别)
成绩表:score(学号,课程号,成绩)
课程表:course(课程号,课程名称,教师号)
教师表:teacher(教师号,教师姓名)
*/
-- 一、创建数据库和表
-- 1、学生表
CREATE table student (
sid int,--学号
sname varchar(20),--姓名
sbirth varchar(20), --出生日期
sgender varchar(20)--性别
);
INSERT into student values (0001,'猴子','1989-01-01','男'),(0002,'猴子','1990-12-21','女'),(0003,'马云','1991-12-21','男'),(0004,'王思聪','1990-05-20','男');
SELECT * from student ;
-- 2.创建学生成绩表
create table score (
sid int ,--学号
scoure int,--课程号
sscore int, --成绩
primary key (sid,scoure)
);
INSERT into score values (0001,0001,80),(0001,0002,90),(0001,0003,99),(0002,0002,60),(0002,0003,80),(0003,0001,80),(0003,0002,80),(0003,0003,80);
SELECT * from score ;
-- 创建课程表
create table course (
scoure int ,--课程号
cname varchar(20),--课程名称
cteacher int
);
INSERT into course values (001,'语文',0002),(0002,'数学',0001),(0003,'英语',0003);
SELECT * from course ;
--创建教师表
create table teacher(
cteacher int,--教师号
tname varchar (20)
);
INSERT into teacher values (0001,'孟扎扎'),(0002,'马化腾'),(0003,null),(0004,'');
SELECT * from teacher ;
-- 1、查询姓“猴”的学生名单
SELECT * from student s where s.sname like '猴%';
-- 1.1查询姓名中最后一个字是猴的学生名单
SELECT * from student s where s.sname like '%猴';
-- 1.2查询姓名中带猴的学生名单
SELECT * from student s where s.sname like '%猴%';
-- 1.3、查询姓“孟”老师的个数
SELECT count(*) from student s where s.sname like '孟%';
--2、查询课程编号为“0002”的总成绩
SELECT sum(s.sscore) from course c join score s on c.scoure =s.scoure where c.scoure =002 ;
-- 2.1、查询选了课程的学生人数
SELECT count( DISCONNECT s.sid) from score s;
-- 3、查询各科创建最高和最低分数
SELECT scoure , max(sscore),min(sscore) from score group by scoure ;
-- 3.1、查询每门课程被选修学生数
SELECT * from course ;
SELECT s.scoure,count(*) from score s join course c on s.scoure =c.scoure group by s.scoure ;
-- 3.2、查询男生,女生人数
SELECT sgender , count(*) from student group by sgender ;
-- 4、查询平均成绩大于70分学生的学号和平均成绩
SELECT s.sid ,AVG(s.sscore) from score s group by s.sid HAVING AVG(s.sscore)>70 ;
-- 4.1、查询至少选修两门课程的学生学好
SELECT s.sid,count(s.scoure)from score s group by s.sid HAVING count(s.scoure)>=2;
-- 4.2、查询同名同性学生名单并统计同名人数
SELECT count(*),s2.sname FROM student s2 group by s2.sname HAVING count(*)>1;
-- 4.3、查询不及格的课程并按课程号从大到小排列
SELECT s.scoure,s.sscore from score s where s.sscore <60 ORDER BY s.scoure
-- 4.4、查询每门课程的平均成绩,结果按平均成绩升序排序,平均成绩相同时,按课程号降序排列
SELECT s2.scoure,AVG(s2.sscore) from score s2 group by s2.scoure ORDER by avg(s2.sscore),s2.scoure DESC ;
S
(001)初级大数据开发_基础SQL
最新推荐文章于 2024-05-04 20:26:08 发布
![](https://img-home.csdnimg.cn/images/20240711042549.png)