作业2

use master
go

if exists(select * from sysdatabases where name = 'Student')
	drop database Student

--创建学生数据库
create database Student

go

use Student

go


--创建学生信息表
create table stuInfo
(
	stuNo varchar(6) not null primary key,
	stuName varchar(10) not null,
	stuSex varchar(2) not null,
	stuAge int not null,
	stuSeat int not null identity(1, 1),
	strAddress varchar(255) default('地址不详')
)

go

--创建学生成绩表
create table stuMarks
(
	ExamNo varchar(7) not null primary key,
	stuNo varchar(6) not null references stuInfo(stuNo),
	writtenExam int null,
	LabExam int null
)

go

--往学生信息表内插入测试数据
insert into stuInfo(stuNo, stuName, stuSex, stuAge, strAddress)
select 's25301', '张秋丽', '男', 18, '北京海淀' union
select 's25303', '李斯文', '女', 22, '河阳洛阳' union
select 's25302', '李文才', '男', 85, '地址不详' union
select 's25304', '欧阳俊雄', '男', 28, '新疆' union
select 's25318', '梅超风', '女', 23, '地址不详'

go

--往学生成绩表内插入测试数据
insert into stuMarks(ExamNo, stuNo, writtenExam, LabExam)
select 's271811', 's25303', 93, 59 union
select 's271813', 's25302', 63, 91 union
select 's271816', 's25301', 90, 83 union
select &#