数据库考试操作题和答案

admin · 发表于 2015-4-28 16:52:13

一、设有一个订货管理数据库，其中有仓库表、职工表、订购单表、供货商表如下所示：
仓库表（仓库号，城市，面积）
职工表（仓库号，职工号，工资）
订购单表（职工号，供应商号，订购单号，订购日期）
供应商表（供应商号，供应商名，城市）
请用SQL语句完成如下操作（1-12每题3分，13题4分，共40分）。：
1、从职工关系中检索所有工资值，要求结果中没有重复值。
2、检索工资多于1230元的职工号和他们所在的城市。
3、检索工作在面积大于400的仓库的职工号以及这些职工工作所在的城市。
4、检索出哪些城市至少有一个仓库的职工工资为1250元
5、检索所有职工的工资都多于1210元的仓库的信息。
6、先按仓库号升序排序，再按工资降序排序并输出全部职工信息。
7、求北京和上海的仓库职工的工资总和。
8、求在wh2仓库工作的职工的最高工资值。
9、求至少有两个职工的每个仓库的平均工资。
10、检索出不在北京的全部供应商信息。
11、列出每个职工经手的具有最高总金额的订购单信息。
12、检索哪些仓库中还没有职工的仓库的信息。
13、检索有职工的工资大于或等于wh1仓库中任何一名职工工资的仓库号

二、现有关系数据库如下：
数据库名：教师数据库
教师表(编号 char(6)，姓名，性别，民族，职称，身份证号)
课程表(课号 char(6)，名称)
任课表(ID，教师编号,课号，课时数)
用SQL语言实现下列功能的sql语句代码（每题5分，共40分）：
1、创建上述三表的建库、建表代码；
要求使用：主键(教师表.编号，课程表.课号)、外键(任课表.教师编号，任课表.课号)、默认(民族)、非空(民族，姓名)、唯一(身份证号)、检查(性别、课时数),自动编号(ID)
2、将下列课程信息添加到课程表的代码
   ① 课号    课程名称
      100001 SQL Server数据库
      100002 数据结构
      100003 VB程序设计
   ② 修改  课号为100003的课程名称：Visual Basic程序设计
   ③删除  课号为100003的课程信息
3、写出创建[任课表视图](教师编号，姓名，课号，课程名称，课时数)的代码；
4、写出创建[某门课任课教师]内嵌表值函数以及检索的代码；
检索：所有代'SQL Server数据库'这门课程的老师姓名；
5、写出创建[统计课时数]：输出最大课时数、最低课时数、平均课时的存储过程以及执行代码。
6、写出创建：计算某教师代课总课时，并将值返回的存储过程以及执行代码。
执行：计算“郭老师”的总课时。
7、检索有一门或一门以上课程课时数大于90的所有教师的信息，包括编号、姓名。
8、建一个规则，并将其绑定到教师表的职称列上，规定取值为（'教授','副教授','讲师', '助教'）之一。

三、设有一个人才信息管理数据库，其中有三张表。
人才基本情况表RCDA（编号，姓名，性别，出生日期，工资现状）
人才专业特长表ZYTC(编号，专业，专业年限，职称，英语水平)
      人才成就成果表CJDA（编号，成果名称，成果类别，成果出处）
  请使用SQL语言完成如下操作（每题4分，共40分）：
1、查询RCDA表中的所有信息；
2、查询编号、姓名、性别、出生日期、工资现状，并按编号的字段值降序排列。
3、查询编号、姓名、性别、出生日期、工资现状，要求查询出工资现状超过5000，或少于3000元的记录，且查询结果按工资现状字段值升序排列。
4、查询RCDA表中所有字段的信息，要求选择所有男同志的记录。
5、在RCDA表中查询所有记录的姓名和工资现状的情况；
6、在RCDA表中查询所有男性的姓名、性别和工资现状的情况，并按工资现状降序排序；
7、在RCDA表中查询年龄在20-30岁间的职工的姓名，出生日期；
8、在RCDA表中求所有人的工资总和，并将列标题改为“工资总和”；
9、在RCDA表中查询男职工和女职工的工资总和；
10、在RCDA表中查询所有人的平均工资；

use master
--删除数据库
if exists (select * from sysdatabases where name='订货管理')
DROP database 订货管理
GO
--创建数据库
CREATE DATABASE 订货管理
GO
--切换数据库
USE 订货管理
Go
--1/创建仓库表（仓库号，城市，面积）
create table 仓库表
(
仓库号 char(4) primary key,
城市 varchar(4) not null,
面积 int
)
go
--2/创建供应商表（供应商号，供应商名，城市）
create table 供应商表
(
供应商号 char(2) primary key,
供应商名 char(2),
城市 varchar(10)
)
Go
--3/创建职工表（仓库号，职工号，工资）
create table 职工表
(
仓库号 char(4) not null references 仓库表(仓库号),
职工号 char(2) primary key,
工资 int
)
Go
--4/创建订购单表（职工号，供应商号，订购单号，订购日期）
create table 订购单表
(
职工号 char(2) not null references 职工表(职工号),
供应商号 char(2) references 供应商表(供应商号),
订购单号 char(2) primary key,
订购日期 datetime
)
Go
--1、从职工关系中检索所有工资值，要求结果中没有重复值。
select 职工号,工资 from 职工表
--2、检索工资多于1230元的职工号和他们所在的城市。
select 城市,职工号 from 仓库表
inner join 职工表 on 仓库表.仓库号=职工表.仓库号
where 工资>1230
--3、检索工作在面积大于400的仓库的职工号以及这些职工工作所在的城市。
select 职工号,城市 from 职工表
inner join 仓库表 on 职工表.仓库号=仓库表.仓库号
where 面积>400
--4、检索出哪些城市至少有一个仓库的职工工资为1250元
select 城市 from 仓库表 where 仓库号 in
(select 仓库号 from 职工表 where 工资=1250)
--5、检索所有职工的工资都多于1210元的仓库的信息。
select * from 仓库表 where 仓库号 in
(select 仓库号 from 职工表
except
select 仓库号 from 职工表 where 工资<=1210)
--6、先按仓库号升序排序，再按工资降序排序并输出全部职工信息。
select * from 职工表 order by 仓库号,工资
--7、求北京和上海的仓库职工的工资总和。
select sum(工资) from 职工表 where 仓库号 in
(select 仓库号 from 仓库表 where 城市='北京' or 城市='上海')
--8、求在wh2仓库工作的职工的最高工资值。
select MAX(工资) from 职工表 where 仓库号='wh2'
--9、求至少有两个职工的每个仓库的平均工资。
select 仓库号,AVG(工资) as 职工平均工资 from 职工表
group by 仓库号
having COUNT(职工号)>=2
--10、检索出不在北京的全部供应商信息。
select * from 供应商表 where 城市<>'北京'
--11、列出每个职工经手的具有最高总金额的订购单信息。
--12、检索哪些仓库中还没有职工的仓库的信息。
select * from 仓库表 where 仓库号 in
(select 仓库号 from 仓库表
Except
select 仓库号 from 职工表)
--13、检索有职工的工资大于或等于wh1仓库中任何一名职工工资的仓库号
select distinct 仓库号 from 职工表
where 工资 >= (select min(工资) from 职工表 where 仓库号='wh1')

复制代码

--1、
create database 教师数据库
go
use 教师数据库
go
create table 教师表
(
编号 char(6) primary key,
姓名 nchar(4) not null,
性别 nchar(1) check([性别] in ('男', '女')),
民族 nchar(8) default '汉族' not null,
职称 nchar(12),
身份证号 char(18) unique )
go
create table 课程表
(课号 char(6) primary key,
名称 char(40) not null
)
go
create table 任课表
(
ID int IDENTITY(1, 1),
教师编号 char(6) references 教师表(编号),
课号 char(6) references 课程表(课号),
课时数 integer check([课时数] between 0 and 200)
)
go
--2、
insert 课程表 values('100001', 'SQL Server数据库')
insert 课程表 values('100002', '数据结构')
insert 课程表 values('100003', 'VB程序设计')
update 课程表 set 名称='Visual Basic程序设计' where 课号='100003'
delete 课程表 where 课号='100003'
--3、
create view [任课表视图] as
select 教师编号,姓名,任课表.课号,名称,课时数 from 教师表
inner join 任课表 on 教师表.编号=任课表.教师编号
inner join 课程表 on 课程表.课号=任课表.课号
--4、
create function [某门课任课教师](@课程名 varchar(15))
returns table as
return (select 名称,课时数,教师姓名=姓名 from 任课表视图 where 名称=@课程名)
go
select * from [某门课任课教师]('SQL Server数据库')
--5、
create procedure [统计课时数]
as
select 最大课时数=max(课时数) ,最小课时数=min(课时数),平均课时数=avg(课时数)
from 任课表
go
execute [统计课时数]
--6、
create procedure [统计课时]
@教师名 nchar(16)
as
declare @总课时 int
select @总课时=sum(课时数) from 任课表视图
where 姓名=@教师名
go
execute [统计课时] '郭老师'
--7、
select 编号, 姓名 from 教师表
where 编号 in (select distinct 教师编号 from 任课表 where 课时数>=90)
8、
create rule zhicheng_rule
as @zhicheng in ('教授','副教授','讲师', '助教')
go
sp_bindrule zhicheng_rule, '教师表.职称'

复制代码

use master
--删除数据库
if exists (select * from sysdatabases where name='人才信息管理')
DROP database 人才信息管理
GO
--创建数据库
CREATE DATABASE 人才信息管理
GO
--切换数据库
USE 人才信息管理
Go
--人才基本情况表RCDA（编号，姓名，性别，出生日期，工资现状）
create table RCDA
(
编号 char(4) primary key,
姓名 varchar(10) not null,
性别 varchar(2) not null,
出生日期 datetime,
工资现状 int
)
go
--人才专业特长表ZYTC(编号，专业，专业年限，职称，英语水平)
create table ZYTC
(
编号 char(4) primary key,
姓名 varchar(10) not null,
性别 varchar(2) not null,
出生日期 datetime,
工资现状 int
)
go
--人才成就成果表CJDA（编号，成果名称，成果类别，成果出处）
create table CJDA
(
编号 char(4) primary key,
成果名称 varchar(10) not null,
成果类别 varchar(10) not null,
成果出处 varchar(10) not null
)
go
--1.查询RCDA表中的所有信息；
Select * from RCDA
--2. 查询编号、姓名、性别、出生日期、工资现状，并按编号的字段值降序排列。
Select 编号,姓名,性别,出生日期,工资现状 from RCDA
order by 编号 desc
--3.查询编号、姓名、性别、出生日期、工资现状，要求查询出工资现状超过5000，或少于3000元的记录，且查询结果按工资现状字段值升序排列。
Select 编号,姓名,性别,出生日期,工资现状 from RCDA
Where 工资现状>5000 or 工资现状<3000 order by 工资现状
--4.查询RCDA表中所有字段的信息，要求选择所有男同志的记录。
Select * from RCDA where 性别='男'
--5.在RCDA表中查询所有记录的姓名和工资现状的情况；
Select 姓名,工资现状 from RCDA
--6.在RCDA表中查询所有男性的姓名、性别和工资现状的情况，并按工资现状降序排序；
Select 姓名,性别,工资现状 from RCDA order by 工资现状 desc
--7. 在RCDA表中查询年龄在20-30岁间的职工的姓名，出生日期；
select 姓名,出生日期,datediff(yy,出生日期,getdate()) as 年龄 from RCDA
where datediff(yy,出生日期,getdate()) between 20 and 30
--8. 在RCDA表中求所有人的工资总和，并将列标题改为“工资总和”；
Select sum(工资现状) as 工资总和 from RCDA
--9. 在RCDA表中查询男职工和女职工的工资总和；
Select 性别,sum(工资现状) as 工资总和 from RCDA group by 性别
--10. 在RCDA表中查询所有人的平均工资；
Select avg(工资现状) from RCDA

复制代码

北城织温22度 · 发表于 2015-4-28 19:12:25

有了这个，不在担心不会做了

Bad-man · 发表于 2015-4-29 16:47:56

嗯二师兄说的对啊斑竹大牛数据库完全不懂的说啊.....

		自动登录	找回密码
密码			注册[Register]

[资料] 数据库考试操作题和答案

相关帖子

浏览过的版块