CREATE DATABASE CSTP
USE CSTP
/*1:建立学生表*/
CREATE TABLE STUDENT( SNO char(6) primary key, SNAME char(6), SEX char(2), AGE int,
CLASS char(4) );
insert into student values('980101','李华','男',19,'9801'); insert into student values('980102','张军','男',18,'9801'); insert into student values('980103','王红','女',19,'9801'); insert into student values('980301','黄华','女',17,'9803'); insert into student values('980302','大卫','男',16,'9803'); insert into student values('980303','赵峰','男',20,'9803'); insert into student values('980304','孙娟','女',21,'9803');
/*2:建立成绩表*/ create table SC( SNO char(6), CNO char(4),
primary key(SNO,CNO), GRADE int, );
insert into sc values('980101','8104',67); insert into sc values('980101','8105',86); insert into sc values('980102','8244',96); insert into sc values('980102','8245',76); insert into sc values('980103','8104',86); insert into sc values('980103','8105',56); insert into sc values('980301','8244',76); insert into sc values('980301','8245',96); insert into sc values('980302','8104',45); insert into sc values('980302','8105',85); insert into sc values('980303','8244',76); insert into sc values('980303','8245',79); insert into sc values('980304','8104',86); insert into sc values('980304','8105',95);
/*3:建立教师表*/ create table teacher(
TNO char(3) primary key,
1
TNAME char(6), SEX char(2), AGE int ,
PROF char(6), DEPT char(8) );
insert into teacher values('801','李新','男',38,'副教授','计算机系'); insert into teacher values('802','钱军','男',45,'教授','计算机系'); insert into teacher values('803','王立','女',35,'副教授','食品系'); insert into teacher values('804','李丹','女',22,'讲师','食品系');
/*4:建立课程表*/
create table COURSE (
CNO char(4) primary key, CNAME char(10), TNO char(3), );
insert into course values('8104','计算机导论','801'); insert into course values('8105','C语言','802');
insert into course values('8244','数据库系统','803'); insert into course values('8245','数据结构','804');
/*5:所有表内容*/ select * from student; select * from course; select * from teacher; select * from sc;
2
3
设有表4-1~表4-4的4个基本表（表结构与表内容是假设的），请先创建数据库及根据表内容创建表结构，并添加表记录，写出实现一下个体功能的SQL语句： ①查询选修课程“8105”且成绩在80到90之间的所有记录； select * from sc
where cno='8105' and grade between 80 and 90; 或者 select * from sc
where cno='8105' and grade>80 and grade<90;
②查询成绩为79、89或99的记录； select * from sc
where GRADE=79 or GRADE=89 or GRADE=99; 或者 select * from sc
where GRADE in(79,89,99);
③查询“9803”班的学生人数； select count(sno) as 学生人数 from student
where class='9803';
④查询至少有20名学生选修的并且课程号以8开头的课程的平均成绩； select avg(grade) as 平均成绩 from sc
where cno like '8%' group by cno
having count(cno) >= 20;
换成2名学生选修
4
⑤查询最低分大于80，最高分小于95的SNO与平均分； select sno,min(grade)as 最低分,max(grade)as 最高分 from sc
group by sno having min(grade)>80 and max(grade) < 95;
⑥查询“9803”班学生所选各课程的课程号及其平均成绩； select sno,avg(grade) as 平均成绩 from sc
where sno in
(select sno from student where class = '9803') group by sno;
⑦查询选修“8105”课程的成绩高于“980302”号同学成绩的所有同学的记录； select *
from student where sno in (select sno from sc
where cno='8105' and grade > (select grade from sc
where sno='980302' and cno='8105'));
⑧查询与学号为“980103”的同学同岁的所有学生的SNO,SNAME和AGE； 法1:select sno,sname,age from student
where age = (select age from student where sno='980103'); 法2:select sno,sname,age from student
where age in (select age from student where sno='980103');
5