sql语句（学生表、课程表、分数表、教师表）

您所在的位置：网站首页 › 教师好用的课程表有哪些 › sql语句（学生表、课程表、分数表、教师表）

sql语句（学生表、课程表、分数表、教师表）

2024-07-15 06:27| 来源: 网络整理| 查看: 265

一、创建表并初始化数据 1、表说明

student(学生表)、teacher(教师表)、course(课程表)、sc(分数表)

2、创建表

//学生表

create table student( sno varchar2(10) primary key, sname varchar2(20), sage number(2), ssex varchar2(5) );

//教师表

create table teacher( tno varchar2(10) primary key, tname varchar2(20) );

//课程表

create table course( cno varchar2(10), cname varchar2(20), tno varchar2(20), constraint pk_course primary key (cno,tno) );

//分数表

create table sc( sno varchar2(10), cno varchar2(10), score number(4,2), constraint pk_sc primary key (sno,cno) ); 3、插入初始化数据

/*初始化学生表的数据/

insert into student values ('s001','张三',23,'男'); insert into student values ('s002','李四',23,'男'); insert into student values ('s003','吴鹏',25,'男'); insert into student values ('s004','琴沁',20,'女'); insert into student values ('s005','王丽',20,'女'); insert into student values ('s006','李波',21,'男'); insert into student values ('s007','刘玉',21,'男'); insert into student values ('s008','萧蓉',21,'女'); insert into student values ('s009','陈萧晓',23,'女'); insert into student values ('s010','陈美',22,'女'); commit;

/初始化教师表*****/

insert into teacher values ('t001', '刘阳'); insert into teacher values ('t002', '谌燕'); insert into teacher values ('t003', '胡明星'); commit;

/初始化课程表*************/

insert into course values ('c001','J2SE','t002'); insert into course values ('c002','Java Web','t002'); insert into course values ('c003','SSH','t001'); insert into course values ('c004','Oracle','t001'); insert into course values ('c005','SQL SERVER 2005','t003'); insert into course values ('c006','C#','t003'); insert into course values ('c007','JavaScript','t002'); insert into course values ('c008','DIV+CSS','t001'); insert into course values ('c009','PHP','t003'); insert into course values ('c010','EJB3.0','t002'); commit;

/初始化成绩表********/

insert into sc values ('s001','c001',78.9); insert into sc values ('s002','c001',80.9); insert into sc values ('s003','c001',81.9); insert into sc values ('s004','c001',60.9); insert into sc values ('s001','c002',82.9); insert into sc values ('s002','c002',72.9); insert into sc values ('s003','c002',81.9); insert into sc values ('s001','c003','59'); commit; 二、SQL语句

1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；

select a.* from (select * from sc a where a.cno='c001') a, (select * from sc b where b.cno='c002') b where a.sno=b.sno and a.score > b.score; 或 select * from sc a where a.cno='c001' and exists(select * from sc b where b.cno='c002' and a.score>b.score and a.sno = b.sno)

2、查询平均成绩大于60 分的同学的学号和平均成绩；

select sno,avg(score) from sc group by sno having avg(score)>60;

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

select a.*,s.sname from (select sno,sum(score),count(cno) from sc group by sno) a , student s where a.sno=s.sno

4、查询姓“刘”的老师的个数；

select count(*) from teacher where tname like '刘%';

5、查询没学过“谌燕”老师课的同学的学号、姓名；

select a.sno,a.sname from student a where a.sno not in ( select distinct s.sno from sc s, ( select c.* from course c , (select tno from teacher t where tname='谌燕') t where c.tno=t.tno ) b where s.cno = b.cno ) 或 select * from student st where st.sno not in ( select distinct sno from sc s join course c on s.cno=c.cno join teacher t on c.tno=t.tno where tname='谌燕' )

6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；

select st.* from sc a join sc b on a.sno=b.sno join student st on st.sno=a.sno where a.cno='c001' and b.cno='c002' and st.sno=a.sno;

7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名；

select st.* from student st join sc s on st.sno=s.sno join course c on s.cno=c.cno join teacher t on c.tno=t.tno where t.tname='谌燕'

8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；

select * from student st join sc a on st.sno=a.sno join sc b on st.sno=b.sno where a.cno='c002' and b.cno='c001' and a.score < b.score

9、查询所有课程成绩小于60 分的同学的学号、姓名；

select st.*,s.score from student st join sc s on st.sno=s.sno join course c on s.cno=c.cno where s.score 1;

42、查询全部学生都选修的课程的课程号和课程名

select distinct(c.cno),c.cname from course c ,sc where sc.cno=c.cno 或 select cno,cname from course c where c.cno in (select cno from sc group by cno)

43、查询没学过“谌燕”老师讲授的任一门课程的学生姓名

select st.sname from student st where st.sno not in (select distinct sc.sno from sc,course c,teacher t where sc.cno=c.cno and c.tno=t.tno and t.tname='谌燕')

44、查询两门以上不及格课程的同学的学号及其平均成绩

select sno,avg(score)from sc where sno in ( select sno from sc where sc.score1 ) group by sno

45、检索“c004”课程分数小于60，按分数降序排列的同学学号

select sno from sc where cno='c004' and score

【本文地址】

sql语句（学生表、课程表、分数表、教师表）

sql语句（学生表、课程表、分数表、教师表）

今日新闻

推荐新闻