sql

北邮上机实践考试数据库

样题一

 

一、创建数据库与数据表(40分)

1.请从考核服务器上下载Access数据库文件stud.mdb;

2.使用配置工具在本地机器上启动SQL Server服务,接着启动Management Studio;然后创建一个大小为1MB的数据库student,事务日志的大小为1MB。注意数据文件和事务日志文件都保存在D盘下。

3.把Access数据库文件stud.mdb导入到数据库student中;

4.设置学生表的主键学号、课程表的主键课程号;

5.请用SQL语句在student数据库中创建表“家庭”。表结构如下所示:

家庭表,结构为:学号(C4),姓名(C8),父姓名(C8),父单位(C10),母姓名(C8),母单位(C10);学号为主键。

CREATE TABLE FAMILY(

STUNO CHAR(4) ,

NAME CHAR(8) ,

FATHERNAME CHAR(8),

FATHERCOMPANY CHAR(10),

MOTHERNAME CHAR(8),

MOTHERCOMPANY CHAR(10),

Primary key (stuno));

6.建立学生表、课程表和选课表之间的参照关系。

请在ans.doc中记录你把access数据库导入到SQL Server中的操作过程。接着,打开企业管理器的“关系”对话框,把包含上述四个表的关系图粘贴到文件ans.doc中,最后把建立数据表“家庭”的SQL语句粘贴到ans.doc文件中。

二、SQL 语句操作(50分)

SQL的语法就类似英文语法一样,举例:

We are searthing(select)  girl, young_lady from Paris who(where)  (age between 18 and 25) and  height>160;

按英语语法去理解即可记住SQL语句的格式。

 

 

1.查询“教育系”学生的学号,姓名,性别,选修过的课程号,课程名,考试成绩,考试日期;

1、直接查多表的写法:

SELECT 学生.学号,学生.姓名,学生.性别,课程.课程号,课程.课程名,选课.考试成绩,选课.考试日期 from 学生,课程,选课 where 学生.学号=选课.学号 and 课程.课程号=选课.课程号 and 学生.系别=‘教育系’;

2、join表的写法:

SELECT 学生.学号,学生.姓名,学生.性别,课程.课程号,课程.课程名,选课.考试成绩,选课.考试日期 from 学生 join 选课 on 学生.学号=选课.学号  join 课程 on  and 课程.课程号=选课.课程号  where   学生.系别=‘教育系’;

2.计算出每位学生的选课门数和平均成绩;

SELECT COUT(课程号) as ‘选课门数’,avg(分数) as ‘平均成绩’from 学生,选课 where 学生.学号=选课.学号 group by 学生.学号;

3.检索出没有选修课程“数据库原理”和“数据结构”的学生的信息;

SELECT * FROM 学生 where  学号 not in (SELECT 学号 from 选课,课程  where 选课.课程号=课程.课程号 and 课程.课程名 in (‘数据库原理’,’数据结构’));

4.重新计算出每位学生的总收入(总收入=打工收入+奖金);

SELECT SUM(打工收入+奖金) from 打工 ,学生 where 学生.学号=打工.学号 group by 学生.学号;

5. 计算出所有单位的打工收入和奖励的总额;

SELECT sum(打工收入+奖励) from 打工;

6.建立视图course,显示出每个学生选课的学号、选课门数、平均成绩,最高成绩。

Create view course as select 学号,count(课程) as 选课门数 ,avg(成绩) as 平均成绩 ,max(成绩) as 最高成绩 from 选课 group by 学号;

请把你为完成上述功能使用SQL语句粘贴到ans.doc文档中。

三、登陆标识与安全性 (10分)

为系统添加一个登录标识:xsks,并且把该标识设定为student的dbo。

请把你的操作步骤记录到ans.doc文档中,并把设置成功的界面粘贴到文档ans.doc中。

样题二

 

一、创建数据库与数据表(40分)

1.请从考核服务器上下载Access数据库文件hospital.mdb;

2.使用配置工具在本地机器上启动SQL Server服务,接着启动Management Studio;然后创建一个大小为1MB的数据库hospital,事务日志的大小为1MB。注意数据文件和事务日志文件都保存在D盘下。

3.把Access数据库文件hospital.mdb导入到数据库student中;

4.设置病人表的主键“病人号”、医生表的主键“医生号”;

5.请用SQL语句在企业管理器中创建数据表“医生附加信息”,表结构如下所示:

医生附加信息,结构为:医生号(C4),毕业学校(C20),毕业年度(C6),业余爱好(C30);设置医生号为主键。

CREATE TABLE DOCTOR_EXTENSION(

DOCNO CHAR(4),

GRADUATE_SCHOOL CHAR(20),

GRADUATE_YEAR CHAR(6),

Favourate char(30),

Primary key (docno));

6.建立医生表、病人表和诊断信息表之间的参照关系。

请在ans.doc中记录你把access数据库导入到SQL Server中的操作过程。接着,打开企业管理器的“关系”对话框,把包含上述四个表的关系图粘贴到文件ans.doc中,最后把建立数据表“医生附加信息”的SQL语句粘贴到ans.doc文件中。

二、SQL 语句操作(50分)

1.查询病人“王晓萍”的病人号、姓名、籍贯、诊断医生的编号、姓名、诊断建议、诊断费用、诊断日期(提示:连接查询);

SELECT 病人.病人号,病人.姓名,病人.籍贯,医生.编号,医生.姓名,诊断.建议,诊断.费用,诊断.日期 from 病人 join 诊断 on 病人.病人号=诊断.病人号 join 医生 on 诊断.医生号=医生的编号 where 病人.姓名=’王晓萍’;

2.根据工资和奖金,计算出每位医生的总收入存到实际收入中。(总收入=工资+奖金);

UPDATE 医生 set 总收入=sum(工资+奖金) ;

3.查询“部门收入”,能够计算出每个部门的工资和奖金的平均额;

SELECT avg(工资),avg(奖金) from 部门收入 group by 部门;

4. 利用SQL语句向医生附加信息表中添加一条新记录,信息内容为:B004,北京大学医学部,2006,踢球、唱歌;

INSERT INTO DOCTOR_EXTENSION VALUES (‘B004’,’北京大学医学部’,’2006’,’踢球、唱歌’);

5. 对每个诊断病人次数在3次以上的医生奖金增加1000元;

UPDATE 医生 SET 奖金=奖金+1000 where 编号 in (select 医生号 from 诊断 group by 医生号 having count(诊断)>=3);

6.为医生表创建依据医生姓名的唯一性索引doctor。

CREATE UNIQUE INDEX DOCTOR ON DOCTOR (NAME);

    请把你为完成上述功能使用SQL语句粘贴到ans.doc文档中。

三、登陆标识与安全性 (10分)

为系统添加一个登录标识:xsks,并且把该标识设定为hospital的dbo。

请把你的操作步骤记录到ans.doc文档中,并把设置成功的界面粘贴到文档ans.doc中。


样题三

一、创建数据库与数据表(30分)

1.请从考核服务器上下载Access数据库文件stud.mdb;

2.使用配置工具在本地机器上启动SQL Server服务,接着启动Management Studio;然后创建一个大小为1MB的数据库student,事务日志的大小为1MB。注意数据文件和事务日志文件都保存在D盘下。

3.把Access数据库文件stud.mdb导入到数据库student中;

4.设置学生表的主键学号、课程表的主键课程号;

5.删除学生表中的字段“奖励”,并增加字段“年龄”;

6.建立学生表、课程表和选课表之间的参照关系。

请在ans.doc中记录你把access数据库导入到SQL Server中的操作过程。接着,打开企业管理器的“关系”对话框,把包含上述四个表的关系图粘贴到文件ans.doc中,最后把修改学生表结构的SQL语句粘贴到ans.doc文件中。

二、SQL 语句操作(50分)

1.查询选修课程“计算机应用基础”的学生的学号,姓名,性别,考试成绩,考试日期;

SELECT 学生.学号,学生.姓名,学生.性别,选课.考试成绩,选课.考试日期 from 学生 join 选课 on 学生.学号=选课.学号 join 课程 on 选课.课程号=课程.课程号 where 课程.课程名=’计算机应用基础’;

2.计算出每门课程的选课人数和平均成绩;

SELECT 课程号,count(学号) as ‘选课人数’,avg(成绩) as ‘平均成绩’ from 选课 group by 课程号;

3.计算出每个院系中所有学生的总收入(即分院系求出打工收入的和);

SELECT 院系,sum(收入) from 学生 group by 院系;

4.列出同时选修了“计算机基础”和“大学外语”两门课程的同学的信息。

SELECT 学生.* from 学生 join 选课 on学生.学号=选课.学号 join 课程 on 选课.课程号=课程.课程号 where 课程 in (select 课程号 from 课程 where 课程名 in (’计算机基础’,’大学外语’) group by 学号 having count(课程号)>=2);

此思路不对!不能这么写!如果某同学重修了计算机基础,有两条计算机基础的选课记录,而没有选修大学外语,却会进入查询结果。

(注意:此题不能用简单的 课程=(或者in)计算机 and 课程in(或=)外语做条件,一条结果都查不出来的。)

李明辉(渣渣辉)提供的另一个思路:

SELECT 学生.* from 学生,选课,课程 where  学生.学号=选课.学号 and 选课.课程号=课程.课程号 and

学号 in ( select 学号 from 课程,选课 where 课程。课程号=选课。课程号 and 课程名='计算机基础') and

学号 in (select 学号 from 课程,选课 where 课程。课程号=选课。课程号 and 课程名='大学外语');

5.对于每位所有课程平均成绩在80分以上的学生奖励1500元;

UPDATE 学生  set 奖励=奖励+1500(还是直接写成奖励=1500?) where 学号 in (select 学号 from 选课 group by 学号 having avg(成绩)>=80);

6.创建一个存储过程xsgl,该过程包含一个参数tname。执行此存储过程,能够输出该教师讲授过的所有课程,以及选修这些课程的学生的学号,考试成绩。

CREATE PROC xsgl

@tname char(10)

As

Begin

Select 选课.学号,选课.成绩,课程.课程名 from 选课 join 课程 on 选课.课程号=课程.课程号 where 课程.教师= @tname;

end

    请把你为完成上述功能使用SQL语句粘贴到ans.doc文档中。

三、登陆标识与安全性 (10分)

为系统添加一个登录标识:jsjks,并且把该标识设定为student的dbo。

请把你的操作步骤记录到ans.doc文档中,并把设置成功的界面粘贴到文档ans.doc中。

四、创建ODBC数据源 (10分)

在ODBC中为你的数据库创建ODBC数据源,数据源名称为“sjks”,请把操作过程中的关键操作界面粘贴到ans文档中。

 

样题四:

一、创建数据库与数据表(40分)

1.下载文件(略);

Create table 家庭(学号 char(4) primary key, 姓名 char(8), 父姓名 char(8),父单位 char(10), 母姓名 char(8),母单位 char(10))

6.打开步骤4建立的关系图,把学生基本表中的主键【学号】拖动到【选课表】的字段“学号”上,把课程表中的主键【课程号】拖动到【选课表】的字段【课程号】上。当线路连接成功后,表示关系建立成功。

把关系图屏幕拷贝到文档ans.doc中。

最后保存关系图。

 

 

二、SQL 查询语言(50分)

不知为何样题四下面本来就提供了这些SQL~~~却没有题干。大概是北邮老师放错了吧。

1.

Select 学生基本表.学号,姓名,性别,课程表.课程号,课程名,考试成绩,

考试日期  from 学生基本表,课程表,选课表

Where 学生基本表.学号=选课表.学号 and 课程表.课程号=选课表.课程号 and 系别=’教育系’

2.Select 学号,count(*) as 选课门数,avg(考试成绩)

from 选课表 group by 学号order by 学号

3.Select * from 学生基本表

where 学号 not in

(select 学号 from 选课表,课程表 where 课程表.课程号=选课表.课程号 and 课程名=’数据库原理’)

and not in

(select 学号 from 选课表,课程表 where 课程表.课程号=选课表.课程号 and 课程名=’数据结构’);

4.Update 学生基本表 set 总收入=打工收入+奖金

5. select sum(打工收入) as 总打工,sum(奖励) as 总奖励

6.Create view course as select 学号,count(*) as 选课门数,avg(考试成绩) as 平均成绩),max(考试成绩) as 最高成绩 from 选课表 group by 学号 order by 学号

原文地址:https://www.cnblogs.com/lvzuwen/p/10818754.html