本次练习都给出标准答案，建议还是自己先不看答案，手动练习一遍。虽然答案一看就明白，但是主要目的是培养大家的动手能力
一.创建一个学生档案表(表名为xsda)，其表结构如下：
列名
类型
长度
允许空
备注
Xh
Char
10
学号
Xm
8
√
姓名
csny
Datetime
出生年月
Gz
Decimal
6
工资
Zy
专业
CREATETABLE`xsda`(xhCHAR(10)NOTNULLUNIQUE,xmCHAR(8),csnyDATETIME,gzDECIMAL(6,1),//整数部分最多为6位，小数部分为1位zyCHAR(10))ENGINE=INNODBDEFAULTCHARSET=utf8;答案1.
INSERTINTO`xsda`(xh,xm)VALUES('2000jsj008','李平');答案2.
UPDATE`xsda`SETgz=gz*1.2WHEREcsny<'80-01-01';答案3.
SELECTzyAS'专业',COUNT(xh)AS'人数'FROMxsdaGROUPBYzy;答案4.
Selectkchas'课程',kccjas'成绩'from`xsda`,`xscj`wherexsda.xh=xscj.xhandxsda.xm=’李平’;答案5.
SELECTxsda.xh,xsda.xmfromxsda,xscjwherekch='Yy2'ANDxsda.xh=xscj.xh;
二、有一个[学生课程]数据库，数据库中包括三个表：
学生表:Student由学号(Sno)、姓名(Sname)、性别(Ssex)、年龄(Sage)、所在系(Sdept)五个属性组成，记为：Student(Sno,Sname,Ssex,Sage,Sdept)，Sno为关键字。
课程表:Course由课程号(Cno)、课程名(Cname)、先修课号(Cpno)、学分(Ccredit)四个属性组成，记为：Course(Cno,Cname,Cpno,Ccredit)Cno为关键字。
成绩表:SG由学号(Sno)、课程号(Cno)、成绩(Grade)三个属性组成，记为：SG(Sno,Cno,Grade)(SNO,CNO)为关键字。
基础题
用SQL语言实现下列功能：
1．建立学生表[Student]，其中学号属性不能为空，并且其值是唯一的。
2．查考试成绩有不及格的学生的学号。
3．将学号为05001学生的年龄改为22岁。
4．计算1号课程的学生平均成绩。
5.查计算机系姓赵的男同学的姓名(Sname)、性别(Ssex)、年龄(Sage)。
6．将一个新学生记录(学号：05020；姓名：丁莉；性别：女；年龄：17岁；所在系：计算机；)插入Student表中。
1．createtablestudent(Snochar(5)primarykey,Snamechar(20),Ssexchar(2),Sageint,Sdeptchar(15))engine=InnoDBdefaultcharset=utf8;2．selectdistinctsnofromsgwheregrade<603．updatestudentsetsage=22wheresno='05001'4．selectavg(grade)fromsgwherecno='1'5.selectsname，ssex，sagefromsdudentwheresdept=’计算机系’andsnamelike'赵%'andssex='男'6．insertintostudentvalues('05020','丁莉','女',17,'计算机系')升级题
ALTERTABLE`Student`ADDScomeDATETIME;3．查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。
SELECTSno,GradeFROM`SG`WHERECno='3'ORDERBYGradeDESC;4．查询学习1号课程的学生最高分数、平均成绩。
selectMAX(Grade),AVG(Grade)FROMSCWHERECno='1';5．查询与“李洋”在同一个系学习的学生。
SELECTSno,Sname,SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname='李洋')6．将计算机系全体学生的成绩置零。
UPDATESGSETGrade=0WHERESnoin(SELECTSnoFROMStudentWHERESdept='计算机系')7．删除学号为05019的学生记录。
DELETEFROMStudentWHERESno='05019'8．删除计算机系所有学生的成绩记录。
DELETEFROMSGWHERESnoin(SELECTSnoFROMStudentWHERESdept='计算机系')三、现有关系数据库如下：
数据库名：学生成绩数据库
学生表(班级编号，学号，姓名，性别，民族，身份证号，出生日期)
课程表(课程号，课程名，开课学期，学时)
成绩表(ID,学号，课程号，分数)
用SQL语言实现下列功能的sql语句代码：
1．查询学生信息表中的年龄(重复年龄只显示一次)
use教学成绩管理数据库selectdistinct年龄=datediff(year,出生日期,getdate())from学生信息表补充：DATEDIFF()函数返回两个日期之间的天数。SELECTDATEDIFF(day,'2008-12-29','2008-12-30')ASDiffDate;结果：
2．从学生信息表和教学成绩表中查询查询学生的学号、姓名、课程名和分数
use学生成绩数据库select成绩表.学号,姓名,课程名,分数from教学成绩表,学生信息表where成绩表.学号=学生表.学号AND成绩表.课程号=课程表.课程号3．从课程表中统计第二学期的总学时。
执行结果为：
课程好课程名开课学期学时
…………
sum
============
…
Select课程号,课程名,开课学期,学时From课程表Where开课学期=2computeavg(学时)4．编写一个存储过程，输入学号，从“教学成绩表视图”显示该学生的姓名、课程名、分数。
CREATEPROCEDURE成绩1@xhchar(6)asselect姓名,课程名称,分数from教学成绩表视图where学号=@xh5．创建一个触发器，当修改学生表中的姓名时，显示“学生姓名已被修改”。
CreateTrigger触发器7On学生表ForupdataAsPrint“学生姓名已被修改”Go升级题
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学生表asajoin成绩表asbona.学号=b.学号groupby班级编号,a.学号,a.姓名orderbyavg(分数)desc,班级编号asc
3．编写一个自定义函数，根据[学生表]中的[出生日期]列，计算年龄。
CREATEFUNCTIONdbo.计算年龄(@vardatedatetime,@Curdatedatetime)RETURNStinyintASBEGINreturndatediff(yyyy,@vardate,@Curdate)END4．创建一个视图[教学成绩表视图]显示学生的学号、姓名、课程名、分数。
CREATEVIEW[教学成绩表视图]ASSELECT学号,姓名,课程名,分数FROM学生表,成绩表WHERE学生表.学号=成绩表.学号
5．编写一个存储过程，输入学号，从[教学成绩表视图]显示该学生的姓名、课程名、分数。
CREATEPROCEDURE[成绩1]@xhchar(6)asselect姓名,课程名称,分数from教学成绩表视图where学号=@xh
6．把[学生表]、[成绩表]通过[学号]建立约束关系。
altertable学生表addconstraint约束1foreignkey(学号)references成绩表(学号)
7．创建一个触发器，当修改学生表中的姓名时，显示“学生姓名已被修改”。
CreateTrigger触发器7On学生表ForupdataAsPrint'学生姓名已被修改'Go8．在学生表中插入记录：
班级编号学号姓名性别民族身份证号出生日期
050201050201001王莉欣女汉530102197906252241979-06-25
050202050202001张晶男NULL01020319801224121NULL
把张晶的民族改为“汉”、出生日期改为“1980-12-24”
insert学生表values('050201','050201001','王莉欣','女','汉','53010219790625224','1979-06-25')insert学生表values('050202','050201001','张晶','男',null,'01020319801224121',null)update学生表set民族='汉',身份证号='01020319800226121',出生日期='1980-02-26'where姓名='张晶'四、用SQL语句创建表1和表2。
departments(Depid,Depname,Depnote)
employees(Empid,Empname,Birthdate,Depart,Salary,Position)
表1departments
字段名
数据类型
说明
Depid
Tinyint
部门编号（主键）
Depname
Char(12)
部门名称
Depnote
Varchar(100)
有关说明
表2employees
Empid
Char(6)
员工编号（主键）
Empname
Char(20)
员工姓名（非空）
Birthdate
Smalldatetime
出生日期
Depart
所在部门（外键）（非空）
Salary
Float
月薪
Position
Char(8)
职务
createtabledepartsment(DepidTinyintnotnullprimarykey,DepnameChar(12),DepnoteVarchar(100),)gocreatetableemployee(EmpidChar(6)notnullprimarykey,EmpnameChar(20)notnull,BirthdateSmalldatetimeDepartTinyintforeignkeyreferencesdepartments(Depdid),SalaryFloat,PositionChar(8))
2、向表1中添加如下数据
Denote
1
软件开发部
2
系统集成部
insertintodepartments(Depid,Depname)values('1','软件开发部')goinsertintodepartments(Depid,Depname)values('2','系统集成部')
3、向表2中添加如下数据
empid
empname
birthdate
depart
salary
A00001
王晓丽
1970/4/27
2400．00
A00004
马明
1962/3/14
4600．00
副经理
insertintoemployeevalues('A00001','王晓丽','1970/4/27',2,2400．00,'')goinsertintoemployeevalues('A00004','马明''1962/3/14',1,4600．00,'副经理')4、查询所有1970年以后出生的员工的信息。
Select*fromemployeewhereBirthdate>’1970-01-01’5、查询工资高于2000元的员工的信息。
Select*fromemployeewhereSalary>20006、查询系统集成部的所有员工的信息。
Selectemployee.*fromemployee,departmentswhereemployee.Depart=departments.DepdidandDepname=’系统集成部’7、统计软件开发部的人均工资
selectavg(salary)fromemployee,departmentswhereemployee.Depart=departments.DepdidandDepname=’软件开发部’8、查询所有员工中工资最高和最低的人。
select*fromemployeewheresaraly=(selectmax(saraly)fromemployee)goselect*fromemployeewheresaraly=(selectmin(saraly)fromemployee)9、统计软件开发部的人数。
selectcount(*)fromemployee,departmentswhereemployee.Depart=departments.DepdidandDepname=’软件开发部’10、将所有员工的工资上调10%。
Updateemployeesetgz=gz*1.211、将工资收入低于2500元的员工每人加薪200元。
Updateemployeesetgz=gz+200wheresaray<250012、对所有“岗位”一栏为空的记录，将其“岗位”改为“职员”。