SQL练习题

二、
1.在学生成绩表tblCourseScore中的列Score用来存放某学生学习某课程的考试成绩（0~100分，没有小数）, 用下面的哪种类型最节省空间？ C
A、int
B、smallint
C、tinyint
D、decimal(3,0)
2.定单表Orders的列OrderID的类型是小整型（smallint），根据业务的发展需要改为整型（integer），应该使用下面的哪条语句？ C
A、ALTER COLUMN OrderID integer FROM Orders
B、ALTER TABLE Orders ( OrderID integer )
C、ALTER TABLE Orders ALTER COLUMN OrderID integer
D、ALTER COLUMN Orders.OrderID integer
3.一个数据库中的用户定义数据类型能够用于同一个服务器上的另一个数据库中吗？
答：不可以。

4.你正在设计一个要存储数百万种不同产品的信息数据库，而且想以最少的空间存储产品信息。

每一个产品在products表中都有一行描述。

有时候，产品描述需要200个字符，但绝大多数产品描述只需要50个字符。

那么，你应该使用哪一种数据类型？
答：nvarchar(200)
5.有家企业要用表tblCustomerInfo来存储客户的信息。

客户的信息包括：代号（整型IDENTITY，从100001开始，每
次增加5），名称（最长40个汉字），电话（20个字符），传真（20个字符），备注（最长1000个汉字）。

电话号码和传真号码要用同一自定义类型type_TelphoneNum。

（1）请写出创建该表的SQL语句。

Create table tblCustomerInfo
( 代号 nvchar(80) not null ,
名称 varchar(80),
电话 type_TelphoneNum (20),
传真 type_TelphoneNum (20),
备注 nvarchar(1000) null
)
（2）后来因手机流行，需要在表tblCustomerInfo中再添加列“手机”，该列的类型也是type_TelphoneNum。

请写
出添加该列的SQL语句。

AITER table tblCustomerInfo add 手机 type_TelphoneNum
(11)
三、
1. 在学生管理系统中使用下面的学生信息表：
CREATE TABLE 学生信息表
( 学号 char(8) PRIMARY KEY NONCLUSTERED, 姓名 varchar(20) NOT NULL,
身份证号码 varchar(30) NOT NULL,
出生日期 datetime NULL,
系号 char(2) NOT NULL
)
学生的身份证号码是唯一的。

你想把每个学生的信息按照身份证号码的顺序物理地存放在数据库文件中。

下面的哪个约束可以完成改任务？A
A、UNIQUE CLUSTERED
B、UNIQUE NONCLUSTERED
C、PRIMARY KEY CLUSTERED
D、PRIMARY KEY NONCLUSTERED
2. 在登记学生成绩时要保证列Score的值在0到100之间，下面的方法中哪种最简单？D
A. 编写一个存储过程，管理插入和检查数值，不允许直接插入；
B. 生成用户自定义类型type_Score和规则，将规则与数据类型type_Score相关联，然后设置列Score的数据类型类型为type_Score；
C. 编写一个触发器来检查Score的值，如果不在0和100之间，则撤消插入；
D. 在Score列增加检查限制。

3. 在数据库中应该将哪种约束添加到Country字段中，以确保你的印度尼西亚子公司只与其他印度尼西亚公司进行贸易。

答： check约束。

4. 在实现问题4的约束（或规则）后，数据录入操作员抱怨他们不得不一遍又一遍地输入Indonesia这个单词，有什么办法可以解决它吗？
答：创建一个dafult
5. 假设你的业务改变了并且你也不在印度尼西亚工作。

你的子公司迁移到了马来群岛与其他几个东亚国家做生意。

现在，
在country字段中包括印度尼西亚在内的记录有四百五十万条。

如何加入新的国家而且保留包含印度尼西亚的行呢？
答：修改表来删除现有约束，然后添加新的约束。

在添加新约束时，使用WITHNOCHECK选项.
6 在学生管理系统中已经创建了学生信息表tblStudInfo和系信息表tblDeptInfo，而且这两个表都存放了不少数据。

创建表的语句如下：
CREATE TABLE tblDeptInfo
(
DeptID char(2) not null PRIMARY KEY,
DeptName varchar(20) not null,
Remarks varchar(255)
)
CREATE TABLE tblStudInfo
(
StudID char(8) not null PRIMARY KEY,
Name varchar(20) not null,
Birthday datetime null,
DeptID char(2) not null
)
学生信息表tblStudInfo的列DeptID，用来存放学生所在系的代号。

该列引用系信息表tblDeptInfo的列DeptID，但是在创建表时没有创建该约束。

请在不删除表的情况下，写出创建该约束的SQL语句。

答：use tblStudInfo
Go
Add unique DeptID
7. 某公司使用数据库进行内部管理：表tblEmployees存储雇员的代号（4位字符，唯一）、身份证号码（18个字符）、名字（最长20个字符）和工资等信息；表tblDepartments存储部门的部门号（2个字符，唯一）、部门名称（30个字符）等
信息；表tblWork每一行表示某雇员在某部门工作过及其开始工作时间和备注。

请写出创建这三个表的SQL语句，要保证：工资的值大于0，身份证号码唯一，并且当删除某雇员时该雇员在表tblWork的所有信息自动删除。

答：create table tblEmployees
( ID char(4),unique
IDCARD char(18),unique
Name nvchar(20),
Gongzi integer(10) check gongzi>0)
Create table tblDepartments
(bmID char(2) unique
Bmname nvarchar(30) )
Create table tblWork
(ID char(4)
Bmh char(8),
Time datetime
Beizhu nvarchar(500)
)
8. 定单录入系统有两个主要的表：Orders和Customers。

如果希望惟一地标识每个定单和客户，应该考虑哪种数据完整性组件？如何管理这两个表之间的关系？
答：参照完整性。

创建外键约束。

四、
1.您需要显示从2001年1月1日到2001年12月31日雇佣
的所有职员的姓名和雇佣日期。

职员信息表tblEmployees 包含列Name和列HireDate，下面哪些语句能完成该功能？D
A、SELECT Name, HireDate FROM tblEmployees
B、 SELECT Name, HireDate FROM tblEmployees
WHERE HireDate =‟2001-01-01‟ OR …2001-12-31‟
C、 SELECT Name, HireDate FROM tblEmployees
WHERE HireDate BETWEEN ‟2000-12-31‟ AND …2002-01-01‟
D、 SELECT Name, HireDate FROM tblEmployees
WHERE HireDate DATEPART(yy, HireDate) =2001
2.在SQL Server 2005创建了定单表，创建语句如下：
CREATE TABLE 定单表
( 定单代号 int IDENTITY(1,1) PRIMARY KEY, 客户代号 int not null,
雇员代号 int not null, --经手该笔业务的雇员的代号定单日期 datetime not null,
销售金额 money not null,
备注 varchar(200) null
)
你需要获得定单信息列表，包括雇员代号、销售金额和定单日期。

你想按日期从近到早的顺序显示，并且对于每一天的定单，按销售金额从大到小的顺序排序。

假设列“销售日期”的时间部分的值都是0，下面哪条语句能够准确地完成该任务？C
A. SELECT 雇员代号, 销售金额, 定单日期
FROM 定单表
ORDER BY销售金额, 定单日期 DESC
B. SELECT 雇员代号, 销售金额, 定单日期
FROM 定单表
ORDER BY 定单日期, 销售金额DESC
C. SELECT 雇员代号, 销售金额, 定单日期
FROM 定单表
ORDER BY 定单日期 DESC, 销售金额 DESC
D. SELECT 雇员代号, 销售金额, 定单日期
FROM 定单表
ORDER BY 销售金额 DESC, 定单日期 DES
3. 你是一位健康护理计划的数据库管理员。

使用下面语句创建physicians表：
CREATE TABLE dbo.physicians (
physician_no int IDENTITY (100, 2) NOT NULL ,
f_name varchar (25) NOT NULL ,
l_name varchar (25) NOT NULL ,
street varchar (50) NULL ,
city varchar (255) NULL ,
state varchar (255) NULL ,
postal_code varchar (7) NULL ,
co_pay money NOT NULL CONSTRAINT phys_co_pay
DEFAULT (10)
)
a) 如何检索在纽约州（NY）、华盛顿州（WA）、弗吉
尼亚州（VA）、或加利福尼亚州（CA）实习的医生信息？
Select *
From physicians
where state in ('NY', 'WA', 'VA', 'CA')
b) 在结果集中，如何产生一个没有重复州的列表？
Select语句中加 distinct 关键字。

c) 在结果集中，如何产生一个列，包含co_pay值加每位
医生$5.00的服务费，并且给该列取一个别名Amt_Due?
答：在选择列表中使用计算列。

使用列别名’Amt_Due’=(co_pay+5)。

4. 学生成绩表包含列“学号”、列“课程代号”、列“成绩”、列“考试时间”等。

列“成绩”的值为NULL表示该学生缺考这门课。

请用SELECT语句查询学号为“98120001”的学生所有缺考的课程数。

Select count(*) as 课程数
From 成绩表
Where 学号=‘98120001’,成绩=‘ null’
6.雇员信息表包含列“雇员代号”、列“雇员姓名”、列“出生日期”、列“基本工资”、列“津贴”，总工资=基本工资+津贴，请用SELECT语句查询1972年以后出生的雇员的姓名、出生日期和总工资，按总工资递减的顺序显示。

Select 姓名，出生日期，基本工资+津贴 as 总工资
Form 雇员信息表
Where 出生日期 >="1972-1-1"
Order by 总工资 desc
7.下面哪些关键字能影响SELECT语句返回的结果的行数？（多选）A, B, D
A. TOP n
B. WHERE
C. ORDER BY
D. DISTINCT
8.某班级的学生成绩表包含列“学号”、列“课程代号”、列“成绩”、列“学期号”，请统计学期号为“第三学期”的每个学生所有科目的平均成绩。

答：select 学号，avg(成绩)
From 学生成绩表
Where 学期="第三学期"
Order by 学号
9.接上题，现在想评定该班级第二学年（包括第三学期和第四学期）的优秀学生奖学金，要求从该班级选出平均成绩排前5名的同学。

请用SELECT语句显示这些同学的学号和平均成绩。

（要考虑可能有多个同学并列第5名）
答：go top 5 with tie 学号，avg(成绩) as 平均成绩
From 学生成绩表
Where 学期="第三学期" or学期= "第四学期"
Group by 学号
Order by 成绩 desc
10.接上题，学校规定前三学年（第一学期到第六学期）总平均分在90分以上而且没有一次考试不及格（成绩>=60分）的同学可以参加学校最高级别的评奖。

请用SELECT语句显示该班级中满足评奖条件的候选人的学号和前三学年的总平均分。

答：select 学号，avg(成绩) as 三年平均成绩
From 学生成绩表
Where 学期="第一学期" or学期="第二学期" or学
期="第二学期" or 学期="第三学期"or学期="第四学期" or
学期="第五学期'or学期="第六学期"
Group by 学号
Having avg(成绩) >90 and min (成绩)>=60
Order by 学号
11.需要提供前１００个产品和销售倒数百分之五的产品的列表。

可以使用SELECT TOP n[PERCENT]语句解答每个问题吗？还有其他方法来解答这些问题吗？
答:可以用 select top 100 ........order by ..desc 或是 order by .. asc
12.你在SQL Server 2005数据库中创建了如下两个表：
CREATE TABLE 雇员表
( 雇员代号 int IDENTITY(10001,1) PRIMARY KEY
NONCLUSTERED,
雇员姓名 varchar(20) NOT NULL,
通信地址 varchar(200) NULL
)
CREATE TABLE 定单表
( 定单号 int IDENTITY(1,1) PRIMARY KEY, 雇员代号 int NOT NULL,
客户代号 int NOT NULL,
订购日期 datetime NOT NULL,
订购金额 money NOT NULL
)
你需要获得2003年9月1日每个雇员的最高的一笔销售金额，要求列出“雇员代号”、“雇员姓名”、“订购日期”、最高的“订购金额”。

下面哪个语句能完成该功能？B
A.
SELECT a.雇员代号,a.雇员姓名,b.订购日期,b.订购金额
FROM 雇员表 AS a LEFT OUTER JOIN 定单表 AS b
ON a.雇员代号 = b.雇员代号
WHERE b.订购日期 = '09/01/2003'
AND 订购金额 IN (SELECT MAX(订购金额) FROM 定单表)
B.
SELECT a.雇员代号,a.雇员姓名,b.订购日期,MAX(订购金额) FROM 雇员表 AS a LEFT OUTER JOIN 定单表 AS b
ON a.雇员代号 = b.雇员代号 AND b.订购日期 =
'09/01/2003'
GROUP BY a.雇员代号,a.雇员姓名,b.订购日期
C.
SELECT a.雇员代号,a.雇员姓名,b.订购日期,MAX(订购金额) FROM 雇员表 AS a INNER JOIN 定单表 AS b
ON a.雇员代号 = b.雇员代号
WHERE b.订购日期 = '09/01/2003'
GROUP BY a.雇员代号,a.雇员姓名,b.订购日期,b.定单号D.
SELECT a.雇员代号,a.雇员姓名,b.订购日期,MAX(订购金额) FROM 雇员表 AS a INNER JOIN 定单表 AS b
ON a.雇员代号 = b.雇员代号
WHERE b.订购日期 = '09/01/2003'
AND 订购金额 IN (SELECT MAX(订购金额) FROM 定单表)
12.Duluth Mutual Life健康保健组织有一个用于跟踪医生和他们的病人的信息的数据库。

这个数据库有下列数据表。

表9- 1 医生数据表
列数据类型和约束
doc_id char(9), PRIMARY KEY Fname char(20)
Lname char(25)
Specialty char(25)
Phone char(10)
表9- 2 病人数据表
列数据类型和约束
pat_id char(9), PRIMARY KEY Fname char(20)
Lname char(25)
insurance_company char(25)
Phone char(10)
表9- 3 病历表
列数据类型和约束
datetime, PRIMARY KEY (composite) admission_
date
pat_id char(9), PRIMARY KEY (composite), FOREIGN KEY to patient.pat_id
doc_id char(9), FOREIGN KEY to doctor.doc_id Diagnosis varchar(150)
根据这个表结构，回答下列问题。

a)如何生成一个信息表，列出病人的姓名和在医院治疗的
日期？
答:用他们的共同列来建立连接，将几张表连接起来。

b)如何查询某个特定医生所诊治过的所有病人？
答：用他们共同的列将三张表连接起来，然后用where 子句查出特定的医生所诊治过的病人。

c)如何查询具有相同专业的医生？
答：使用SPECIALTY进行自连接
d)如何为医生和病人生成一个姓名和电话列表？
答：先在医生表里找出医生的姓名和电话列表，再从病人表中找出姓名和电话列表，然后用unin 语句把他们连接起来。

13. 学生信息系统中有两个表：“学生信息表”和“系信息表”。

在学生信息表中有列“学号”、列“姓名”、列“出生日期”、列“系号”，在系信息表中有列“系号”、列“系名”、列“系主任”。

列“系号”是学生信息表引用系信息表的外键。

请用SELECT语句列出所有学生的学号、姓名和所在的系名。

答：select s_no as 学号 ,S_no as 姓名，X_no as 系
名
FROM 学生信息表 AS a INNER JOIN 系信息表 as
b ON 学生信息表.S_no =系信息表.X_no
14. 接上题，请用SELECT语句统计并显示每个系的系号、系名以及每个系学生的人数。

要求：如果新创建的系还没有学生，就不要显示该系的信息。

答：select X_no as 系号 ,X_name as 系名 ,count (*) as 系学生人数
FROM 学生信息表 AS a INNER JOIN 系信息表 AS b ON 学生信息表.S_no =系信息表.X_no
GROUP BY X_no, X_name
15. 接上题，请用SELECT语句统计并显示每个系的系号、系名以及每个系学生的人数。

要求：如果新创建的系还没有学生，该系也要显示，并且该系的学生人数为0。

答：select X_no as 系号，X_name as 系名，count (*) as 系学生人数
From 学生信息表AS a RIGHT JOIN 系信息表AS b ON 学生信息表.S_no =系信息表.X_no
GROUP BY X_no,X_name
16. 接上题，教师信息表有列“教师代号”、列“教师姓名”、列“出生日期”、列“系号”，其中列“系号”是教师信息表引用系信息表的外键。

请用SELECT语句列出系号为…12‟的系的所有人员的姓名和出生日期（包括学生和教师）。

17. 学生信息系统中有两个表：“学生信息表”和“系信息
表”。

在学生信息表中有列“学号”、列“姓名”、列“出生日期”、列“系号”，在系信息表中有列“系号”、列“系名”、列“系主任”。

列“系号”是学生信息表引用系信息表的外键。

请用“相关子查询作为表达式”方法写出SELECT语句，来统计并显示每个系的系号、系名以及每个系学生的人数。

要求：如果新创建的系还没有学生，则显示该系的学生人数为0。

18. 接上题，使用派生表方法写出SELECT语句，来统计并
显示每个系的系号、系名以及每个系学生的人数。

要求：如果新创建的系还没有学生，则不显示该系；
19.接上题，请显示学生人数最多的系的系号、名称和学生
人数。

20. 实验方案
Duluth Mutual Life健康保健组织有一个用于跟踪医生和他们的病人的信息的数据库。

这个数据库有下列数据表。

●医生数据表
表错误！文档中没有指定样式的文字。

-1
列数据类型和约束
doc_id char(9), PRIMARY KEY Fname char(20)
Lname char(25)
Specialty char(25)
Phone char(10)
●病人数据表
表错误！文档中没有指定样式的文字。

-2
列数据类型和约束
pat_id char(9), PRIMARY KEY Fname char(20)
Lname char(25)
Insurance_company char(25)
Phone char(10)
●病历表
表错误！文档中没有指定样式的文字。

-3
列数据类型和约束Admission_date datetime, PRIMARY KEY
(composite)
pat_id char(9), PRIMARY KEY
(composite),
FOREIGN KEY to
patient.pat_id
doc_id char(9), FOREIGN KEY to
doctor.doc_id
Diagnosis varchar(150)
根据这个表结构，回答下列问题。

a)如何用一个查询语句，查询出数据库中最初入院治
疗的病历？
答：使用函数"MIN"来确定最早入院治疗的日期。

用WHERE子句把min查询的结果同每个病例的入院治疗日期进行比较。

b)想要知道入院治疗的总数，按病人姓名排序，如何
做到这一点？你的方法有哪些优点和缺点？
答：利用
六、
1.你在SQL Server 2005数据库中创建了定单表：
CREATE TABLE 定单表
( 定单号 int IDENTITY(100001,1) PRIMARY KEY, 雇员代号 int NOT NULL,
客户代号 int NOT NULL,
订购日期 datetime NOT NULL,
订购金额 money NOT NULL
)
由于业务量很大，定单表已经存放了大量的数据。

你想删除3年以前的订购信息，下面哪个语句能够完成改任务？
A. DELETE FROM 定单表 WHERE 定单日期 < DATEADD( YY, -3, GETDATE() )
B. DELETE FROM 定单表 WHERE 定单日期 < DATEADD( YY, 3, GETDATE() )
C. DELETE FROM 定单表 WHERE 定单日期 < GETDATE() - 3
D. DELETE FROM 定单表 WHERE 定单日期 < GETDATE() + 3
2. 学生信息系统中有两个表：“学生信息表”和“系信息表”。

在学生信息表中有列“学号”、列“姓名”、列“出生日期”、列“系号”、列“学生备注”，在系信息表中有列“系号”、
列“系名”、列“系主任”、列“系简介”。

列“系号”是学生信息表引用系信息表的外键。

假设成立了一个新的系，“系号”为“18”，“系名”为“生命科学系”，“系主任”和“系简介”为空值。

请用INSERT语句添加该系到系信息表，要求写出两种形式的INSERT语句。

3. 接上题，有一个新学生报到，该学生的学号为“03120001”，姓名为“张三”，出生日期为“1982-01-01”，所在
的系名为“计算机科学系”，没有“学生备注”。

请用INSERT语句添加该学生到学生信息表。

提示：使用局部变量…@系号‟。

4. 接上题，请用DELETE语句删除所在系的系名为“计算机科学系”的所有学生的纪录。

5. 你是一个关注健康计划的数据库管理员。

使用下面的语句来创建physicians表：
CREATE TABLE dbo.physicians (
physician_no int IDENTITY (100, 2) NOT NULL
,f_name varchar (25) NOT NULL
,l_name varchar (25) NOT NULL
,street varchar (50) NULL
,city varchar (255) NULL
,state varchar (255) NULL
,postal_code varchar (7) NULL
,co_pay money NOT NULL CONSTRAINT phys_co_pay DEFAULT (10)
)
GO
a) 必须提供最少多少个列值才能把一个新的行添加到表
中？
答：至少两个列的数据，其他的值为空。

b) 已增加了参加活动的医师的服务报酬。

你如何为所有医
生的co_pay字段值增加12%？
答：update physicians set co-copy=(co-copy+co-copy*0.12)
c) 你如何从 physicians表中删除所有行？
答：使用delect 语句。