参考答案-数据库原理测试一

测试一参考答案

1.建表与插入数据

create database School;

use School;

create table Classes (
    cid int primary key,
    cname varchar(30),
    cteacher varchar(10)
);

create table Student (
    sid int primary key,
    sname varchar(10),
    ssex char(2),
    sage smallint,
    sclass int,
    foreign key (sclass)
        references Classes (cid)
);

insert into Classes values( 1,'2010级软件工程1班','张老师');
insert into Classes values( 2,'2010级软件工程2班','张老师');
insert into Classes values( 3,'2010级计算机科学与技术1班','王老师');
insert into Classes values( 4,'2010级计算机科学与技术2班','王老师');

insert Into Student(sid,sname,sage,ssex,sclass) values(1,'张三',19,'男',3);
insert Into Student(sid,sname,sage,ssex,sclass) values(2,'李四',20,'男',4);
insert Into Student(sid,sname,sage,ssex,sclass) values(3,'王五',18,'女',3);
insert Into Student(sid,sname,sage,ssex,sclass) values(4,'赵六',19,'女',2);
insert Into Student(sid,sname,sage,ssex,sclass) values(5,'钱七',18,'男',1);
insert Into Student(sid,sname,sage,ssex,sclass) values(6,'张八',19,'女',1);
insert Into Student(sid,sname,sage,ssex,sclass) values(7,'王九',21,'男',1);
insert Into Student(sid,sname,sage,ssex,sclass) values(8,'赵十',20,'女',4);

2.SQL查询

/* （1）查看School数据库有哪些表*/
show tables;

/*（2）查看Student表的结构*/
desc student;

/*（3）查询所有班级的信息*/
select * 
from classes;

/*（4）查询3号班级的学生学号、姓名和年龄*/
select sid,sname,sage
from student
where sid=3;

/*（5）查询2010级软件工程1班学生的学号，姓名和出生年*/
select sid,sname,year(now())-sage
from student,classes
where student.sclass=cid
  and cname = '2010级软件工程1班';

/*（6）查询每个班级的学生数，结果显示班级号、班级人数*/
select sclass 班级号,count(*) 班级人数
from student
group by sclass;

/*（7）查询每个班级的学生平均年龄，结果显示班级号、平均年龄*/
select sclass 班级号, avg(sage) 平均年龄
from student
group by sclass;

/*（8）查询每个教师所带学生数，结果显示教师名、带学生数*/
select cteacher 教师名,count(*) 带学生数
from student,classes
where student.sclass = classes.cid
group by cteacher;

/*（9）查询教师带学生人数大于2人的教师名和带学生数*/
select cteacher 教师名,count(*) 带学生数
from student,classes
where student.sclass = classes.cid
group by cteacher
having count(*)>2;

/*（10）查询每个班级的学生数，结果显示班级号和班级人数，按人数降序排序*/
select sclass 班级号,count(*) 班级人数
from student
group by sclass
order by 班级人数 desc;

3.数据更新