数据库练习与答案解析.doc

一. 创建一个学生档案表(表名为xsda)，其表结构如下：列名类型长度允许空备注XhChar10学号XmChar8某csnyDatetime出生年月GzDecimal6工资ZyChar10专业Create Table xsda(xhChar(10) not null unique,xmChar(8),csnyDatetime,gz Decimal(6,1),zy Char(10)1. 在xsda表中插入一个学生记录：（2000jsj008,李平）insert into xsda(xh,xm) values(2000jsj008,李平)2. 把xsda表中80-01-01前出生的人的工资增加20%Update xsda set gz=gz*1.2 where csny< 80-01-013. 查询xsda表中不同专业的人数Select zy ,count(xh) from xsda group by zy4. 假如另外还有一个学生成绩表xscj(xh,kch,kccj), xh,kch,kccj分别指学号、课程号、成绩，要求查询某为李平的同学的各门课程的成绩。Select kch,kccj from xsda,xscj where xsda.xh=xscj.xh and xm= 李平5. xsda、xscj表同上，查询选修了kch为Yy2的学生的xh和XmSelect xh,xm from xsda,xscj where xsda.xh=xscj.xh and kch=Yy2二、使用SQL语句创建一个班级表CLASS，属性如下：CLASSNO，DEPARTNO，CLASSNAME；类型均为字符型；长度分别为8、2、20且均不允许为空。CREATE _table_ CLASS(CLASSNO _char_(8) NOT NULL,DEPARTNO CHAR (2) NOT NULL,CLASSNAMECHAR (_20_) NOT NULL）三、有一个学生课程数据库，数据库中包括三个表：学生表:Student由学号(Sno)、某(Sname)、性别(Ssex)、年龄(Sage)、所在系(Sdept)五个属性组成，记为：Student(Sno,Sname,Ssex,Sage,Sdept)，Sno 为关键字。课程表:Course由课程号(o)、课程名(ame)、先修课号(Cpno)、学分(Ccredit)四个属性组成，记为： Course(o,ame,Cpno,Ccredit) o为关键字。 成绩表:SG由学号(Sno)、课程号(o)、成绩(Grade)三个属性组成，记为： SG(Sno,o,Grade) (SNO, O) 为关键字。用SQL语言实现下列功能：1建立学生表Student，其中学号属性不能为空，并且其值是唯一的。2查考试成绩有不及格的学生的学号。3将学号为05001学生的年龄改为22岁。4计算1号课程的学生平均成绩。5. 查计算机系姓赵的男同学的某(Sname)、性别(Ssex)、年龄(Sage)。6将一个新学生记录(学号：05020；某：丁莉；性别：女；年龄：17岁；所在系：计算机；)插入Student表中。1create table student (Sno char(5) primary key,Sname char(20),Ssex char(2),Sage int,Sdept char(15)2select distinct sno from sgwhere grade <603 update student set sage=22 where sno='05001'4select avg(grade) from sgwhere o='1'5.select sname，ssex，sage from sdudent where sdept=计算机系and sname like '赵 %' and ssex ='女' 6 insert into student values ('05020', '丁莉', '女', 17, '计算机系')四、有一个学生课程数据库，数据库中包括三个表：学生表Student由学号(Sno)、某(Sname)、性别(Ssex)、年龄(Sage)、所在系(Sdept)五个属性组成，记为： Student(Sno,Sname,Ssex,Sage,Sdept) ，Sno 为关键字。课程表Course由课程号(o)、课程名(ame)、先修课号(Cpno)、学分(Ccredit)四个属性组成，记为：Course(o,ame,Cpno,Ccredit) o为关键字。 成绩表SG由学号(Sno)、课程号(o)、成绩(Grade)三个属性组成，记为： SG(Sno,o,Grade) (SNO, O)为关键字。用SQL语言实现下列功能：1建立学生表Student，其中学号属性不能为空，并且其值是唯一的。2向Student表增加“入学时间(Se)”列，其数据类型为日期型。3查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。4查询学习1号课程的学生最高分数、平均成绩。5查询与“李洋”在同一个系学习的学生。6将计算机系全体学生的成绩置零。7删除学号为05019的学生记录。8删除计算机系所有学生的成绩记录。 1CREATETABLE Student (Sno CHAR(5) NOT NULL UNIQUE,Sname CHAR(20),Ssex CHAR(2),Sage INT,Sdept CHAR(15)2ALTER TABLE Student ADD Se DATETIME3 SELECT Sno, GradeFROM SGWHERE o='3' ORDER BY Grade DESC4 SELECT MAX(Grade), AVG(Grade)FROM SC WHERE o='1'5 SELECT Sno, Sname, SdeptFROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname='李洋')6UPDATE SG SET Grade=0 WHERE Sno in( SELECT Sno FROM Student WHERE Sdept = '计算机系')7DELETE FROM StudentWHERE Sno='05019'8 DELETE FROM SG WHERE Sno in( SELECT Sno FROM Student WHERE Sdept = '计算机系')五、有一个学生课程数据库，数据库中包括三个表：学生表由学号、某、性别、年龄、所在系五个属性组成， 可记为：学生表(学号，某，性别，年龄，所在系) 学号为关键字。课程表由课程号、课程名、先修课号、学分四个属性组成， 可记为：课程表(课程号，课程名，先修课号，学分) 课程表为关键字。学生选课表由学号、课程号、成绩三个属性组成， 可记为： 成绩表(学号，课程号，成绩) (学号、课程号)为关键字。编写用SQL语言实现下列功能的sql语句代码：1、建立一个学生表，其中学号属性不能为空，并且其值是唯一的。2、查询全体学生的详细记录。3、查询年龄在20至23岁之间的学生的某、系别、和年龄。4、计算1号课程的学生平均成绩。5、将计算机科学系全体学生的成绩置零。1、建立一个学生表。CREATE TABLE 学生表(学号 CHAR(5) NOT NULL UNIQUE, 某CHAR(20), 性别 CHAR(2), 年龄 INT, 所在系 CHAR(15)2、查询全体学生的详细记录SELECT * FROM 学生表3、查询年龄在20至23岁之间的学生的某、系别、和年龄SELECT 某, 所在系, 年龄FROM 学生表WHERE 年龄 BETWEEN 20 AND 234、计算1号课程的学生平均成绩SELECT AVG(成绩) FROM 成绩表WHERE 课程号='1'5、将计算机科学系全体学生的成绩置零UPDATE 成绩表 SET 成绩=0 WHERE 学号 in( SELECT 学号 FROM 学生表 WHERE Sdept ='计算机科学系')现有关系数据库如下： 数据库名：学生成绩数据库学生表(班级编号，学号，某，性别，民族，某号，出生日期) 课程表(课程号，课程名，开课学期，学时) 成绩表(ID,学号，课程号，分数) 用SQL语言实现下列功能的sql语句代码：1查询学生信息表中的年龄(重复年龄只显示一次)2从学生信息表和教学成绩表中查询查询学生的学号、某、课程名和分数3从课程表中统计第二学期的总学时。执行结果为：课程好 课程名 开课学期 学时 sum = 4编写一个存储过程，输入学号，从“教学成绩表视图” 显示该学生的某、课程名、分数。5创建一个触发器，当修改学生表中的某时，显示“学生某已被修改”。1查询学生信息表中的年龄(重复年龄只显示一次)use 教学成绩管理数据库select distinct 年龄=datediff(year,出生日期,getdate() from 学生信息表2从学生信息表和教学成绩表中查询查询学生的学号、某、课程名和分数use 学生成绩数据库select 成绩表.学号,某,课程名,分数from 教学成绩表,学生信息表where成绩表.学号 = 学生表.学号 AND 成绩表.课程号=课程表.课程号3. 从课程表中统计第二学期的总学时。Select课程号, 课程名, 开课学期, 学时From 课程表Where开课学期=2 pute avg(学时)4编写一个存储过程，输入学号，从“教学成绩表视图” 显示该学生的某、课程名、分数。CREATE PROCEDURE 成绩1 xh char (6)asselect 某,课程名称,分数 from 教学成绩表视图where 学号=xh5创建一个触发器，当修改学生表中的某时，显示“学生某已被修改”。Create Trigger 触发器7 On 学生表For updataAsPrint “学生某已被修改”Go现有关系数据库如下：数据库名：学生成绩数据库学生表(班级编号，学号，某，性别，民族，某号，出生日期) 课程表(课程号，课程名) 成绩表(ID,学号，课号，分数) 用SQL语言实现下列功能的sql语句代码：1在学生成绩数据库的学生表中查询年龄为20岁或22岁的学生。2在学生成绩数据库中查询每个学生的班级编号、学号、某、平均分，结果按平均分降序排列，均分相同者按班级排列。3编写一个自定义函数，根据学生表中的出生日期列，计算年龄。4创建一个视图教学成绩表视图显示学生的学号、某、课程名、分数。5编写一个存储过程，输入学号，从教学成绩表视图显示该学生的某、课程名、分数。6把学生表、成绩表通过学号建立约束关系。7把学生表、成绩表通过学号建立约束关系。8在学生表中插入记录：班级编号 学号 某 性别 民族 某号 出生日期050201050201001王莉欣 女汉 530102197906252241979-06-25050202050202001X晶 男NULL01020319801224121NULL把X晶的民族改为“汉”、出生日期改为“1980-12-24”1在学生成绩数据库的学生表中查询年龄为20岁或22岁的学生use 教学成绩管理数据库select 某, 性别, 年龄=datediff(year,出生日期,getdate() from 学生表where (datediff(year,出生日期,getdate()=20) or (datediff(year,出生日期,getdate()=22)2在学生成绩数据库中查询每个学生的班级编号、学号、某、平均分，结果按平均分降序排列，均分相同者按班级排列use 教学成绩管理数据库select 班级编号,a.学号,a.某, avg(分数) 平均分from 学生表 as a join成绩表 as b on a.学号 = b.学号group by 班级编号, a.学号,a.某order by avg(分数) desc, 班级编号 asc3编写一个自定义函数，根据学生表中的出生日期列，计算年龄。CREATE FUNCTIONdbo.计算年龄(vardate datetime,Curdate datetime) RETURNS tinyint AS BEGIN return datediff(yyyy, vardate, Curdate)END4创建一个视图“教学成绩表视图” 显示学生的学号、某、课程名、分数。CREATE VIEW 教学成绩表视图ASSELECT 学号, 某, 课程名, 分数FROM 学生表, 成绩表WHERE 学生表.学号=成绩表.学号5编写一个存储过程，输入学号，从“教学成绩表视图” 显示该学生的某、课程名、分数。CREATE PROCEDURE 成绩1 xh char (6)asselect 某,课程名称,分数 from 教学成绩表视图where 学号=xh6把学生表、成绩表通过“学号”建立约束关系。alter table 学生表add constraint 约束1 foreign key (学号) references 成绩表 (学号)7创建一个触发器，当修改学生表中的某时，显示“学生某已被修改”。Create Trigger 触发器7 On 学生表For updataAsPrint '学生某已被修改'Go8insert 学生表 values('050201','050201001','王莉欣','女','汉','53010219790625224','1979-06-25')insert 学生表 values ('050201','050201001', 'X晶', '男', null, null, null)update 学生表 set 民族='汉', 某号='01020319800226121', 出生日期='1980-02-26'where 某='X晶'1、 用SQL语句创建表1和表2。departments(Depid,Depname,Depnote)employees(Empid,Empname,Birthdate,Depart,Salary,Position)表1departments字段名数据类型说明DepidTinyint部门编号（主键）DepnameChar(12)部门名称DepnoteVarchar(100)有关说明表2 employees字段名数据类型说明EmpidChar(6)员工编号（主键）EmpnameChar(20)员工某（非空）BirthdateSmalldatetime出生日期DepartTinyint所在部门（外键）（非空）SalaryFloat月薪PositionChar(8)职务2、 向表1中添加如下数据DepidDepnameDenote1软件开发部2系统集成部3、 向表2中添加如下数据empidempnamebirthdatedepartsalaryPositionA00001王晓丽1970/4/272240000A00004马明1962/3/141460000副经理4、 查询所有1970年以后出生的员工的信息。5、 查询工资高于2000元的员工的信息。6、 查询系统集成部的所有员工的信息。7、 统计软件开发部的人均工资。8、 查询所有员工中工资最高和最低的人。9、 统计软件开发部的人数。10、 将所有员工的工资上调10%。11、 将工资收入低于2500元的员工每人加薪200元。12、 对所有“岗位”一栏为空的记录，将其“岗位”改为“职员”。13、 删除年龄大于50岁的员工的信息。14、删除所有1970年以前出生的员工的信息。1、 用SQL语句创建表1和表2。departments(Depid,Depname,Depnote)employees(Empid,Empname,Birthdate,Depart,Salary,Position)表1departments字段名数据类型说明DepidTinyint部门编号（主键）DepnameChar(12)部门名称DepnoteVarchar(100)有关说明表2 employees字段名数据类型说明EmpidChar(6)员工编号（主键）EmpnameChar(20)员工某（非空）BirthdateSmalldatetime出生日期DepartTinyint所在部门（外键）（非空）SalaryFloat月薪PositionChar(8)职务create table departsment(DepidTinyint not null primary key,DepnameChar(12),DepnoteVarchar(100),)gocreate table employee(EmpidChar(6) not null primary key,EmpnameChar(20) not null,BirthdateSmalldatetimeDepartTinyint foreign key references departments(Depdid),SalaryFloat,PositionChar(8)2、 向表1中添加如下数据DepidDepnameDenote1软件开发部2系统集成部insert into departments(Depid,Depname) values('1','软件开发部')goinsert into departments(Depid,Depname) values('2','系统集成部')3、 向表2中添加如下数据empidempnamebirthdatedepartsalaryPositionA00001王晓丽1970/4/272240000A00004马明1962/3/141460000副经理insert into employee values('A00001','王晓丽','1970/4/27',2,240000,'')goinsert into employee values('A00004','马明''1962/3/14',1,460000,'副经理')4、 查询所有1970年以后出生的员工的信息。Select * from employee where Birthdate>1970-01-015、 查询工资高于2000元的员工的信息。Select * from employee where Salary>20006、 查询系统集成部的所有员工的信息。Select employee.* from employee,departments where employee. Depart=departments. Depdid and Depname= 系统集成部7、 统计软件开发部的人均工资select avg(salary) from employee, departments where employee. Depart=departments. Depdid and Depname= 软件开发部8、 查询所有员工中工资最高和最低的人。Select * from employee where saraly=(select max(saraly) from employee)goSelect * from employee where saraly=(select min(saraly) from employee)9、 统计软件开发部的人数。select count(*) from employee, departments where employee. Depart=departments. Depdid and Depname= 软件开发部10、 将所有员工的工资上调10%。Update employee set gz=gz*1.211、 将工资收入低于2500元的员工每人加薪200元。Update employee set gz=gz+200 where saray<250012、 对所有“岗位”一栏为空的记录，将其“岗位”改为“职员”。Update employee set Position= 职员 where Position is null13、 删除年龄大于50岁的员工的信息。Delete from employee where year(getdate()-year(birthdate)>5014、删除所有1970年以前出生的员工的信息。 Delete from employee where birthdate<1970-01-019 / 9