华美海润的笔试题(数据库)

/*
	建立数据库test1
	建立数据表Teacher，Student，Grade
	插入各表的数据
*/
create database test1;
create table Teacher
(
	TeacherId varchar(10) primary key,
	Name varchar(20),
	Email varchar(20),
)
insert into Teacher values('001','龙**','LMS@nwsuaf.edu.cn');
insert into Teacher values('002','李**','LJL@nwsuaf.edu.cn');
insert into Teacher values('003','王**','WSZ@nwsuaf.edu.cn');
insert into Teacher values('004','何**','HWJ@nwsuaf.edu.cn');
insert into Teacher values('005','毛*','MR@nwsuaf.edu.cn');
insert into Teacher values('006','胡**','HSJ@nwsuaf.edu.cn');
insert into Teacher values('007','鱼*','YX@nwsuaf.edu.cn');
insert into Teacher values('008','张*','ZY@nwsuaf.edu.cn');
insert into Teacher values('009','李**','LSQ@nwsuaf.edu.cn');
insert into Teacher values('0010','樊**','FSM@nwsuaf.edu.cn');
insert into Teacher values('0011','任**','RGX@nwsuaf.edu.cn');
insert into Teacher values('0012','冯*','FY@nwsuaf.edu.cn');
insert into Teacher values('0013','刘**','LQZ@nwsuaf.edu.cn');
insert into Teacher values('0014','李**','LHL@nwsuaf.edu.cn');
insert into Teacher values('0015','韩*','HH@nwsuaf.edu.cn');
insert into Teacher values('0016','来**','LZY@nwsuaf.edu.cn');
insert into Teacher values('0017','耿*','GN@nwsuaf.edu.cn');
insert into Teacher values('0018','蔚**','WJC@nwsuaf.edu.cn');
insert into Teacher values('0019','杨**','YLB@nwsuaf.edu.cn');
insert into Teacher values('0020','杨**','YHJ@nwsuaf.edu.cn');
create table Student
(
	StudentId varchar(10) primary key,
	Name varchar(20),
	Phone varchar(20),
	Advisor varchar(10)
)
insert into Student values('2009012901','吴**','13484615244','刘德华');
insert into Student values('2009012902','高*','13482690978','黎明');
insert into Student values('2009012903','曹**','15297902453','张学勇');
insert into Student values('2009012904','刘**','13779380296','李德禄');
insert into Student values('2009012905','严**','18900660173','高露洁');
insert into Student values('2009012906','唐**','13912395310','安丽佳');
insert into Student values('2009012907','侯**','18700249734','安七炫');
insert into Student values('2009012908','朱鑫','13496258724','孙楠');
insert into Student values('2009012909','卢**','13267884932','蔡依林');
insert into Student values('2009012910','陈*','13392469749','周杰伦');
insert into Student values('2009012911','朱*','13378934259','阿杜');
insert into Student values('2009012912','猛**','13489249253','F4');
insert into Student values('2009012913','李*','13797424392','小F4');
insert into Student values('2009012914','李**','13892834926','孙燕姿');
insert into Student values('2009012915','李**','13923942353','林志玲');
insert into Student values('2009012916','朱*','13209782342','陈惠玲');
insert into Student values('2009012917','熊**','13597235442','陈小春');
insert into Student values('2009012918','袁*','13892742492','无解');
insert into Student values('2009012919','王*','13492753249','王宝强');
insert into Student values('2009012920','陈**','15924392753','阿宝');
create table Grade
(
	StudentId varchar(10),
	TeacherId varchar(10),
	CourseId varchar(10),
	Score real,
	primary key(StudentId,TeacherId),
	foreign key(StudentId) REFERENCES Student(StudentId),
	foreign key(TeacherId) REFERENCES Teacher(TeacherId),	
)
insert into Grade values('2009012901','001','101',99.0);
insert into Grade values('2009012902','002','102',89.0);
insert into Grade values('2009012903','003','103',59.0);
insert into Grade values('2009012904','004','104',49.0);
insert into Grade values('2009012905','005','105',60.0);
insert into Grade values('2009012906','006','106',100.0);
insert into Grade values('2009012908','006','120',86.0);
insert into Grade values('2009012907','007','107',77.0);
insert into Grade values('2009012908','008','108',85.0);
insert into Grade values('2009012909','009','109',80.0);
insert into Grade values('2009012910','0010','110',72.0);
insert into Grade values('2009012911','0011','111',69.0);
insert into Grade values('2009012912','0012','112',69.0);
insert into Grade values('2009012913','0013','113',99.0);
insert into Grade values('2009012914','0014','114',91.0);
insert into Grade values('2009012915','0015','115',85.0);
insert into Grade values('2009012916','0016','116',83.0);
insert into Grade values('2009012908','0010','111',66.0);
insert into Grade values('2009012917','0017','117',93.0);
insert into Grade values('2009012918','0018','118',72.0);
insert into Grade values('2009012919','0019','119',64.0);
insert into Grade values('2009012920','0020','120',16.0);
insert into Grade values('2009012908','0020','120',96.0);
/*
	查询成绩在80分以上的学生记录，显示教师姓名，学生姓名，选修课和成绩
*/
select Teacher.Name,Student.Name,CourseId,Score 
from Grade,Student,Teacher
where Score>80 
and Student.StudentId=Grade.StudentId 
and Teacher.TeacherId=Grade.TeacherId;
/*
	查询Grade表中的数据，显示成绩最低的前5条记录
*/
select top 5 *
from Grade
order by Score asc
/*
	查询Grade表中的数据，显示成绩最高的前5条记录
*/
select top 5 *
from Grade
order by Score desc
/*
	查询学生朱鑫的所有课程的成绩
*/
select Score
from Grade,Student
where Student.Name='朱鑫' and Student.StudentId=Grade.StudentId;