– sql练习题
–学生表
create table student1
(
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 sc1
(
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 student1 (sid,sname,sage,ssex) values('101','zhangsan',23,1);
insert into student1 (sid,sname,sage,ssex) values('102','lisi',25,2);
insert into student1 (sid,sname,sage,ssex) values('103','wangwu',27,1);
insert into student1 (sid,sname,sage,ssex) values('104','zhaoliu',16,2);
insert into student1 (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 sc1 (scid,sid,cid,score) values(1,'101','001',65.5);
insert into sc1 (scid,sid,cid,score) values(2,'101','002',70.0);
insert into sc1 (scid,sid,cid,score) values(3,'101','003',35.5);
insert into sc1 (scid,sid,cid,score) values(4,'102','001',45.5);
insert into sc1 (scid,sid,cid,score) values(5,'102','002',27.5);
insert into sc1 (scid,sid,cid,score) values(6,'102','003',32.5);
insert into sc1(scid,sid,cid,score) values(7,'103','001',83.0);
insert into sc1 (scid,sid,cid,score) values(8,'103','002',64.5);
insert into sc1 (scid,sid,cid,score) values(9,'103','003',92.5);
insert into sc1 (scid,sid,cid,score) values(10,'104','001',17.5);
insert into sc1 (scid,sid,cid,score) values(11,'104','002',25.5);
insert into sc1 (scid,sid,cid,score) values(12,'104','003',97.5);
insert into sc1 (scid,sid,cid,score) values(13,'105','001',73.5);
insert into sc1 (scid,sid,cid,score) values(14,'105','002',29.5);
insert into sc1 (scid,sid,cid,score) values(15,'105','003',17.5);
– 查询平均成绩大于60分的同学的学号和平均成绩;
SELECT sid,avg(score)
from sc1
GROUP BY sid
having avg(score) > 60
– 查询"001"课程比"002"课程成绩高的所有学生的学号;
SELECT a.aa
from
(SELECT sid aa,cid,score
from sc1
GROUP BY sid,cid
having cid = '001') a join
(SELECT sid bb,cid,score
from sc1
GROUP BY sid,cid
having cid = '002') b
on a.aa= b.bb
where a.score > b.score
SELECT * from sc1
– 查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
SELECT cid,max(score),min(score)
from sc1
GROUP BY cid
– 查询所有课程成绩小于60分的同学的学号、姓名(这个方法不对,懒得改了)
select
– 查询所有同学的学号、姓名、选课数、总成绩;
SELECT sid,sname,count(*),sum(score)
from sc1 NATURAL join student1
GROUP BY sid