1、建表
CREATE TABLE customer(
mid char(5) primary key,
nam varchar(20),
birth date,
sex char(6)
);
CREATE TABLE [dbo].[user](
uid char(6) primary key,
zip char(8),
address varchar(255),
name varchar(20)
);
CREATE TABLE order_basic(
oid char(15) primary key,
odate date,
uid char(6),
memo varchar(255)
);
CREATE TABLE order_details(
oid char(15) ,
pid char(9),
quantity int,
primary key (oid,pid)
);
CREATE TABLE product(
pid char(9) primary key,
pname varchar(255),
price float(10,1)
);
2、插入数据
INSERT INTO customer values(1,’张三’,getdate(),0);
INSERT INTO customer values(2,’李四’,NULL,0); -- 后面查询题有一个要求查生日为null的数据；
Insert into product values(1,’商品一’,10); -- 每个字段都赋值
Insert into product set pid=2; -- 只插入产品id，其他字段为null
Insert into order_details(oid,pid,quantity) values(239,198,99); -- 直接指定每个要赋值的字段，然后根据格式写入值；
3、建视图
CREATE VIEW order_detail AS SELECT ob.oid,ob.uid,u.name,p.pname,p.price,od.quantity from
Order_basic ob join [user] u on ob.uid=u.uid join order_details od on ob.oid=od.oid
Join product p on od.pid=p.pid ;
4、查询
(1)
SELECT * FROM customer where sex=0 and birth is NULL;
(2)
SELECT oid,count(pid) from order_details group by oid;
(3)
SELECT * FROM product where price <= (select avg(price) from product );
(4)
select * from product where pid in (
select pu.pid from
(select distinct od.pid,ob.uid from order_details od ,order_basic ob,[user] u,product p
where p.pid=od.pid and od.oid=ob.oid and ob.uid=u.uid ) as pu
group by pu.pid having COUNT(pu.uid)=(select count(uid) from [user])
) ;
(5)
SELECT pid,pname from product where pid not in (SELECT DISTINCT pid from order_details);
**********************************************************************************************
样题一
一、创建数据库与数据表(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
