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

测试一参考答案

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,sagefrom studentwhere sid=3;/*（5）查询2010级软件工程1班学生的学号，姓名和出生年*/select sid,sname,year(now())-sagefrom student,classeswhere student.sclass=cid  and cname = '2010级软件工程1班';/*（6）查询每个班级的学生数，结果显示班级号、班级人数*/select sclass 班级号,count(*) 班级人数from studentgroup by sclass;/*（7）查询每个班级的学生平均年龄，结果显示班级号、平均年龄*/select sclass 班级号, avg(sage) 平均年龄from studentgroup by sclass;/*（8）查询每个教师所带学生数，结果显示教师名、带学生数*/select cteacher 教师名,count(*) 带学生数from student,classeswhere student.sclass = classes.cidgroup by cteacher;/*（9）查询教师带学生人数大于2人的教师名和带学生数*/select cteacher 教师名,count(*) 带学生数from student,classeswhere student.sclass = classes.cidgroup by cteacherhaving count(*)>2;/*（10）查询每个班级的学生数，结果显示班级号和班级人数，按人数降序排序*/select sclass 班级号,count(*) 班级人数from studentgroup by sclassorder by 班级人数 desc;

3.数据更新