经典数据库笔试面试题
一. 创建一个学生档案表(表名为xsda)，其表结构如下：
|
列名 |
类型 |
长度 |
允许空 |
备注 |
|
Xh |
Char |
10 |
学号 |
|
|
Xm |
Char |
8 |
√ |
姓名 |
|
csny |
Datetime |
√ |
出生年月 |
|
|
Gz |
Decimal |
6 |
√ |
工资 |
|
Zy |
Char |
10 |
√ |
专业 |
Create Table xsda
(
xh Char (10)not null unique,
xm Char(8),
csny Datetime ,
gz Decimal(6,1),
zy Char (10)
)
1. 在xsda表中插入一个学生记录：（2000jsj008,李平）
insert intoxsda(xh,xm) values(‘2000jsj008’,’李平’)
2. 把xsda表中80-01-01前出生的人的工资增加20%
Updatexsda set gz=gz*1.2 where csny<’ 80-01-01’
3. 查询xsda表中不同专业的人数
Select zy ,count(xh) from xsda group by zy
4. 假如另外还有一个学生成绩表xscj(xh,kch,kccj), xh,kch,kccj分别指学号、课程号、成绩，要求查询姓名为李平的同学的各门课程的成绩。
Selectkch,kccj from xsda,xscj wherexsda.xh=xscj.xh and xm=’ 李平’
5. xsda、xscj表同上，查询选修了kch为Yy2的学生的xh和Xm
Selectxh,xm from xsda,xscj wherexsda.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,
CLASSNAME CHAR (__20__) NOT NULL
）
三、有一个[学生课程]数据库，数据库中包括三个表：
学生表: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．
createtable student
(Snochar(5) primary key,
Sname char(20),
Ssex char(2),
Sage int,
Sdept char(15)
)
2．
selectdistinct sno
from sg
where grade < 60
3．
updatestudent
set sage=22
where sno='05001'
4．
select avg(grade)
from sg
where cno='1'
5.
selectsname，ssex，sage
fromsdudent
wheresdept=’计算机系’and sname like '赵 %' and ssex ='女'
6．
insertinto student
values ('05020', '丁莉', '女', 17, '计算机系')
四、有一个[学生课程]数据库，数据库中包括三个表：
学生表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．向Student表增加“入学时间(Scome)”列，其数据类型为日期型。
3．查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。
4．查询学习1号课程的学生最高分数、平均成绩。
5．查询与“李洋”在同一个系学习的学生。
6．将计算机系全体学生的成绩置零。
7．删除学号为05019的学生记录。
8．删除计算机系所有学生的成绩记录。
1．
CREATETABLEStudent
(SnoCHAR(5) NOT NULL UNIQUE,
SnameCHAR(20),
SsexCHAR(2),
SageINT,
SdeptCHAR(15))
2．
ALTERTABLE Student ADD Scome DATETIME
3．
SELECTSno, Grade
FROM SG
WHERECno='3'
ORDERBY Grade DESC
4．
SELECTMAX(Grade), AVG(Grade)
FROM SC
WHERECno='1'
5．
SELECTSno, Sname, Sdept
FROMStudent
WHERESdept IN
(SELECT Sdept FROM Student
WHERE Sname='李洋')
6．
UPDATESG
SETGrade=0
WHERESno in
(SELECT Sno FROM Student
WHERESdept = '计算机系')
7．
DELETEFROM Student
WHERESno='05019'
8．
DELETEFROM SG
WHERESno in
(SELECT Sno FROM Student
WHERESdept = '计算机系')
五、有一个[学生课程]数据库，数据库中包括三个表：
学生表由学号、姓名、性别、年龄、所在系五个属性组成，
可记为：学生表(学号，姓名，性别，年龄，所在系) [学号]为关键字。
课程表由课程号、课程名、先修课号、学分四个属性组成，
可记为：课程表(课程号，课程名，先修课号，学分) 课程表为关键字。
学生选课表由学号、课程号、成绩三个属性组成，
可记为： 成绩表(学号，课程号，成绩) (学号、课程号)为关键字。
编写用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 20AND 23
4、计算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
compute avg(学时)
4．编写一个存储过程，输入学号，从“教学成绩表视图” 显示该学生的姓名、课程名、分数。
CREATE PROCEDURE 成绩1 @xh char (6)
as
select 姓名,课程名称,分数 from 教学成绩表视图
where 学号=@xh
5．创建一个触发器，当修改学生表中的姓名时，显示“学生姓名已被修改”。
Create Trigger 触发器7 On 学生表
For updata
As
Print “学生姓名已被修改”
Go
现有关系数据库如下：
数据库名：学生成绩数据库
学生表(班级编号，学号，姓名，性别，民族，身份证号，出生日期)
课程表(课程号，课程名)
成绩表(ID,学号，课号，分数)
用SQL语言实现下列功能的sql语句代码：
1．在[学生成绩数据库]的[学生表]中查询年龄为20岁或22岁的学生。
2．在[学生成绩数据库]中查询每个学生的班级编号、学号、姓名、平均分，结果按平均分降序排列，均分相同者按班级排列。
3．编写一个自定义函数，根据[学生表]中的[出生日期]列，计算年龄。
4．创建一个视图[教学成绩表视图]显示学生的学号、姓名、课程名、分数。
5．编写一个存储过程，输入学号，从[教学成绩表视图]显示该学生的姓名、课程名、分数。
6．把[学生表]、[成绩表]通过[学号]建立约束关系。
7．把[学生表]、[成绩表]通过[学号]建立约束关系。
8．在学生表中插入记录：
班级编号 学号 姓名 性别 民族 身份证号 出生日期
050201 050201001 王莉欣 女 汉 53010219790625224 1979-06-25
050202 050202001 张晶 男 NULL 01020319801224121 NULL
把张晶的民族改为“汉”、出生日期改为“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 ajoin成绩表 as b
on a.学号 = b.学号
group by 班级编号, a.学号,a.姓名
order by avg(分数) desc, 班级编号 asc
3．编写一个自定义函数，根据[学生表]中的[出生日期]列，计算年龄。
CREATE FUNCTION dbo.计算年龄(@vardatedatetime,@Curdate datetime)
RETURNS tinyint
AS
BEGIN
return datediff(yyyy, @vardate, @Curdate)
END
4．创建一个视图“教学成绩表视图” 显示学生的学号、姓名、课程名、分数。
CREATE VIEW [教学成绩表视图]
AS
SELECT 学号, 姓名, 课程名, 分数
FROM 学生表, 成绩表
WHERE 学生表.学号=成绩表.学号
5．编写一个存储过程，输入学号，从“教学成绩表视图” 显示该学生的姓名、课程名、分数。
CREATE PROCEDURE [成绩1] @xh char (6)
as
select 姓名,课程名称,分数 from 教学成绩表视图
where 学号=@xh
6．把学生表、成绩表通过“学号”建立约束关系。
alter table 学生表
add constraint 约束1 foreign key (学号) references 成绩表 (学号)
7．创建一个触发器，当修改学生表中的姓名时，显示“学生姓名已被修改”。
Create Trigger 触发器7 On 学生表
For updata
As
Print '学生姓名已被修改'
Go
8．
insert 学生表 values
('050201','050201001','王莉欣','女','汉','53010219790625224',
'1979-06-25')
insert 学生表 values
('050201','050201001','张晶', '男', null,null, null)
update 学生表
set 民族='汉',
身份证号='01020319800226121',
出生日期='1980-02-26'
where 姓名='张晶'
1、 用SQL语句创建表1和表2。
departments(Depid,Depname,Depnote)
employees(Empid,Empname,Birthdate,Depart,Salary,Position)
表1 departments
|
字段名 |
数据类型 |
说明 |
|
Depid |
Tinyint |
部门编号（主键） |
|
Depname |
Char(12) |
部门名称 |
|
Depnote |
Varchar(100) |
有关说明 |
表2 employees
|
字段名 |
数据类型 |
说明 |
|
Empid |
Char(6) |
员工编号（主键） |
|
Empname |
Char(20) |
员工姓名（非空） |
|
Birthdate |
Smalldatetime |
出生日期 |
|
Depart |
Tinyint |
所在部门（外键）（非空） |
|
Salary |
Float |
月薪 |
|
Position |
Char(8) |
职务 |
2、 向表1中添加如下数据
|
Depid |
Depname |
Denote |
|
1 |
软件开发部 |
|
|
2 |
系统集成部 |
3、 向表2中添加如下数据
|
empid |
empname |
birthdate |
depart |
salary |
Position |
|
A00001 |
王晓丽 |
1970/4/27 |
2 |
2400．00 |
|
|
A00004 |
马明 |
1962/3/14 |
1 |
4600．00 |
副经理 |
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)
表1 departments
|
字段名 |
数据类型 |
说明 |
|
Depid |
Tinyint |
部门编号（主键） |
|
Depname |
Char(12) |
部门名称 |
|
Depnote |
Varchar(100) |
有关说明 |
表2 employees
|
字段名 |
数据类型 |
说明 |
|
Empid |
Char(6) |
员工编号（主键） |
|
Empname |
Char(20) |
员工姓名（非空） |
|
Birthdate |
Smalldatetime |
出生日期 |
|
Depart |
Tinyint |
所在部门（外键）（非空） |
|
Salary |
Float |
月薪 |
|
Position |
Char(8) |
职务 |
createtable departsment
(
Depid Tinyint not null primary key,
Depname Char(12),
Depnote Varchar(100),
)
go
createtable employee
(
Empid Char(6) not null primary key,
Empname Char(20) not null,
Birthdate Smalldatetime
Depart Tinyint foreign key referencesdepartments(Depdid),
Salary Float,
Position Char(8)
)
2、 向表1中添加如下数据
|
Depid |
Depname |
Denote |
|
1 |
软件开发部 |
|
|
2 |
系统集成部 |
insertinto departments(Depid,Depname) values('1','软件开发部')
go
insertinto departments(Depid,Depname) values('2','系统集成部')
3、 向表2中添加如下数据
|
empid |
empname |
birthdate |
depart |
salary |
Position |
|
A00001 |
王晓丽 |
1970/4/27 |
2 |
2400．00 |
|
|
A00004 |
马明 |
1962/3/14 |
1 |
4600．00 |
副经理 |
insert into employeevalues('A00001','王晓丽','1970/4/27',2,2400．00,'')
go
insert into employeevalues('A00004','马明' '1962/3/14',1,4600．00,'副经理')
4、 查询所有1970年以后出生的员工的信息。
Select * from employee where Birthdate>’1970-01-01’
5、 查询工资高于2000元的员工的信息。
Select * from employee where Salary>2000
6、 查询系统集成部的所有员工的信息。
Selectemployee.* fromemployee,departments where employee.Depart=departments. Depdid and Depname=’系统集成部’
7、 统计软件开发部的人均工资
select avg(salary)from employee, departments whereemployee. Depart=departments. Depdid andDepname=’ 软件开发部’
8、 查询所有员工中工资最高和最低的人。
Select * fromemployee where saraly=(select max(saraly) from employee)
go
Select * fromemployee where saraly=(select min(saraly) from employee)
9、 统计软件开发部的人数。
select count(*)from employee, departments whereemployee. Depart=departments. Depdid andDepname=’ 软件开发部’
10、 将所有员工的工资上调10%。
Update employee set gz=gz*1.2
11、 将工资收入低于2500元的员工每人加薪200元。
Update employee set gz=gz+200 where saray<2500
12、 对所有“岗位”一栏为空的记录，将其“岗位”改为“职员”。
Update employee set Position=’职员’ where Position is null
13、 删除年龄大于50岁的员工的信息。
Delete from employeewhere year(getdate())-year(birthdate)>50
14、删除所有1970年以前出生的员工的信息。
Delete from employee where birthdate<’1970-01-01’