12.27

最新推荐文章于 2024-12-27 19:28:47 发布

孙茂森_43986103

最新推荐文章于 2024-12-27 19:28:47 发布

阅读量170

点赞数

CC 4.0 BY-SA版权

分类专栏： Java基础学习代码 Java代码文章标签：查询

本文链接：https://blog.youkuaiyun.com/weixin_43986103/article/details/85296420

代码同时被 3 个专栏收录

12 篇文章

订阅专栏

Java基础学习

8 篇文章

订阅专栏

Java代码

8 篇文章

订阅专栏

本文详细介绍了一套完整的SQL数据库操作流程，包括学生、教师、课程及成绩等表的创建，以及数据插入。同时，提供了多种复杂查询示例，如查询平均成绩大于60分的学生信息，对比不同课程的成绩，找出各科成绩的高低界限，以及查找成绩低于60分的学生详情。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

create table student
(
sid varchar(10),
sname varchar(20),
sage int,
ssex int,
primary key(sid)
);
--drop table student;

--课程表
create table course
(
cid varchar(10),
cname varchar(20),
tid int,
primary key(cid)
);
--drop table course;

--成绩表
create table sc
(
scid int ,
sid varchar(10),
cid varchar(10),
score numeric(4,1),
primary key(scid)
);
--drop table sc;

--教师表
create table teacher
(
tid varchar(10),
tname varchar(20),
primary key(tid)
);

insert into student (sid,sname,sage,ssex) values('101','zhangsan',23,1);
insert into student (sid,sname,sage,ssex) values('102','lisi',25,2);
insert into student (sid,sname,sage,ssex) values('103','wangwu',27,1);
insert into student (sid,sname,sage,ssex) values('104','zhaoliu',16,2);
insert into student (sid,sname,sage,ssex) values('105','maba',19,1);

--select sid,sname,sage,ssex from student;

insert into teacher (tid,tname) values('1001','李四');
insert into teacher (tid,tname) values('1002','王五');
insert into teacher (tid,tname) values('1003','赵六');
insert into teacher (tid,tname) values('1004','李红');

--select tid,tname from teacher;

insert into course (cid,cname,tid) values('001','语文','1001');
insert into course (cid,cname,tid) values('002','数学','1002');
insert into course (cid,cname,tid) values('003','外语','1003');

--select cid,cname,tid from course;


insert into sc (scid,sid,cid,score) values(1,'101','001',65.5);
insert into sc (scid,sid,cid,score) values(2,'101','002',70.0);
insert into sc (scid,sid,cid,score) values(3,'101','003',35.5);
insert into sc (scid,sid,cid,score) values(4,'102','001',45.5);
insert into sc (scid,sid,cid,score) values(5,'102','002',27.5);
insert into sc (scid,sid,cid,score) values(6,'102','003',32.5);
insert into sc (scid,sid,cid,score) values(7,'103','001',83.0);
insert into sc (scid,sid,cid,score) values(8,'103','002',64.5);
insert into sc (scid,sid,cid,score) values(9,'103','003',92.5);
insert into sc (scid,sid,cid,score) values(10,'104','001',17.5);
insert into sc (scid,sid,cid,score) values(11,'104','002',25.5);
insert into sc (scid,sid,cid,score) values(12,'104','003',97.5);
insert into sc (scid,sid,cid,score) values(13,'105','001',73.5);
insert into sc (scid,sid,cid,score) values(14,'105','002',29.5);
insert into sc (scid,sid,cid,score) values(15,'105','003',17.5);

--查询平均成绩大于60分的同学的学号和平均成绩；
SELECT sid,avg(score)
FROM  sc 

GROUP BY sid
HAVING avg(score)>60
--查询"001"课程比"002"课程成绩高的所有学生的学号；


--查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select cid,max(score),min(score)
from sc
GROUP BY cid

--查询所有课程成绩小于60分的同学的学号、姓名
SELECT sc.sid,sname
from sc join student
on sc.sid = student.sid
where score < 60
GROUP BY sid

--查询所有同学的学号、姓名、选课数、总成绩；

SELECT sc.sid,sname,count(cid),sum(score)
from sc natural join student
GROUP BY sc.sid