数据库学习试题

数据表创建

--–.学生表

--Student(s_id,s_name,s_brith,s_sex) –学生编号,学生姓名, 出生年月,学生性别

CREATE TABLE dbo.Student (

s_id int IDENTITY(1,1) NOT NULL,

s_name nvarchar(20) NOT NULL ,

s_brith datetime NOT NULL ,

s_sex bit NOT NULL DEFAULT 1,

PRIMARY KEY(s_id)

);

 

--–.课程表

--Course(c_id,c_name,t_id) ––课程编号, 课程名称, 教师编号

create table dbo.Course(

c_id int IDENTITY(1,1) NOT NULL,

c_name nvarchar(20) not null ,

t_id int NOT NULL,

PRIMARY KEY(c_id)

);

 

/*

–.教师表

Teacher(t_id,t_name) –教师编号,教师姓名

*/

CREATE TABLE dbo.Teacher(

t_id int IDENTITY(1,1) NOT NULL,

t_name nvarchar(20) NOT NULL ,

PRIMARY KEY(t_id)

);

 

/*

–.成绩表

Score(s_id,c_id,s_score) –学生编号,课程编号,分数

*/

Create table dbo.Score(

s_id int NOT NULL,

c_id int NOT null ,

s_score INT ,

primary key( s_id , c_id )

);

 

 

--插入学生表测试数据

SET IDENTITY_INSERT Student ON

insert into Student values(1 , '赵雷' , '1990-01-01' , 1);

insert into Student values(2 , '钱电' , '1990-12-21' , 1);

insert into Student values(3 , '孙风' , '1990-05-20' , 1);

insert into Student values(4 , '李云' , '1990-08-06' , 1);

insert into Student values(5 , '周梅' , '1991-12-01' , 0);

insert into Student values(6 , '吴兰' , '1992-03-01' , 0);

insert into Student values(7 , '郑竹' , '1989-07-01' , 0);

insert into Student values(8 , '王菊' , '1990-01-20' , 0);

SET IDENTITY_INSERT Student OFF

--课程表测试数据

SET IDENTITY_INSERT Course ON

insert into Course(c_id,c_name,t_id) values(1 , '语文' , '02');

insert into Course(c_id,c_name,t_id) values(2 , '数学' , '01');

insert into Course(c_id,c_name,t_id) values(3 , '英语' , '03');

SET IDENTITY_INSERT Course OFF

--教师表测试数据

SET IDENTITY_INSERT Teacher ON

insert into Teacher(t_id,t_name) values(1 , '张三');

insert into Teacher(t_id,t_name) values(2 , '李四');

insert into Teacher(t_id,t_name) values(3 , '王五');

SET IDENTITY_INSERT Teacher OFF

--成绩表测试数据

insert into Score(s_id,c_id,s_score) values(1, 1, 80);

insert into Score(s_id,c_id,s_score) values(1, 2, 90);

insert into Score(s_id,c_id,s_score) values(1, 3, 99);

insert into Score(s_id,c_id,s_score) values(2, 1, 70);

insert into Score(s_id,c_id,s_score) values(2, 2, 60);

insert into Score(s_id,c_id,s_score) values(2, 3, 80);

insert into Score(s_id,c_id,s_score) values(3, 1, 80);

insert into Score(s_id,c_id,s_score) values(3, 2, 80);

insert into Score(s_id,c_id,s_score) values(3, 3, 80);

insert into Score(s_id,c_id,s_score) values(4, 1, 50);

insert into Score(s_id,c_id,s_score) values(4, 2, 30);

insert into Score(s_id,c_id,s_score) values(4, 3, 20);

insert into Score(s_id,c_id,s_score) values(5, 1, 76);

insert into Score(s_id,c_id,s_score) values(5, 2, 87);

insert into Score(s_id,c_id,s_score) values(6, 1, 31);

insert into Score(s_id,c_id,s_score) values(6, 3, 34);

insert into Score(s_id,c_id,s_score) values(7, 2, 89);

insert into Score(s_id,c_id,s_score) values(7, 3, 98);