数据库 6

数据库及SQL,数据库基本知识

数据库也是计算机类笔试面试中不可避免会遇到的考点,尤其是银行和部分传统软件类公司。这里根据整理的资料,对数据库的相关知识也做个总结吧。希望学过数据库但长时间不用的同学根据这些知识能够回忆和重拾,没学过的同学能掌握一些数据库的基础知识。

数据库也是计算机类笔试面试中不可避免会遇到的考点,尤其是银行和部分传统软件类公司。这里根据整理的资料,对数据库的相关知识也做个总结吧。希望学过数据库但长时间不用的同学根据这些知识能够回忆和重拾,没学过的同学能掌握一些数据库的基础知识。

(一)

第一节

第一节

 

 一、相关概念    

  1.
Data:数据,是数据库中存储的基本对象,是描述事物的符号记录。

     
 2.
Database:数据库,是长期储存在计算机内、有组织的、可共享的大量数据的集合

     
 3.
DBMS:数据库管理系统,是位于用户与操作系统之间的一层数据管理软件,用于科学地组织、存储和管理数据、高效地获取和维护数据。

     
 4.
DBS:数据库系统,指在计算机系统中引入数据库后的系统,一般由数据库、数据库管理系统、应用系统、数据库管理员(DBA)构成。

     
 5.
数据模型:是用来抽象、表示和处理现实世界中的数据和信息的工具,是对现实世界的模拟,是数据库系统的核心和基础;其组成元素有数据结构、数据操作和完整性约束

     
 6.
概念模型:也称信息模型,是按用户的观点来对数据和信息建模,主要用于数据库设计。

     
 7. 逻辑模型:是按计算机系统的观点对数据建模,用于DBMS实现。

     
 8.
物理模型:是对数据最底层的抽象,描述数据在系统内部的表示方式和存取方法,在磁盘或磁带上的存储方式和存取方法,是面向计算机系统的。

     
 9.
实体和属性:客观存在并可相互区别的事物称为实体。实体所具有的某一特性称为属性。

     
 10.E-R图:即实体-关系图,用于描述现实世界的事物及其相互关系,是数据库概念模型设计的主要工具。

     
 11.关系模式:从用户观点看,关系模式是由一组关系组成,每个关系的数据结构是一张规范化的二维表。

     
 12.型/值:型是对某一类数据的结构和属性的说明;值是型的一个具体赋值,是型的实例。

     
 13.数据库模式:是对数据库中全体数据的逻辑结构(数据项的名字、类型、取值范围等)和特征(数据之间的联系以及数据有关的安全性、完整性要求)的描述。

     
 14.数据库的三级系统结构:外模式、模式和内模式

     
 15.数据库内模式:又称为存储模式,是对数据库物理结构和存储方式的描述,是数据在数据库内部的表示方式。一个数据库只有一个内模式

     
 16.数据库外模式:又称为子模式或用户模式,它是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述,是数据库用户的数据视图。通常是模式的子集。一个数据库可有多个外模式

     
 17.数据库的二级映像:外模式/模式映像、模式/内模式映像。

     
一、相关概念

1.写出一条Sql语句:取出表A中第31到第40记录(SQLServer,
以自动增长的ID作为主键,  注意:ID可能不是连续的。)

select top 10 * from A where id not in (select top 30 id from A)
解2: select top 10 * from A where id > (selectmax(id) from (select
top 30 id from A )as A)

 二、重点知识点

  1. 数据库系统由数据库、数据库管理系统、应用系统和数据库管理员构成。

     
 2. 数据模型的组成要素是:数据结构、数据操作、完整性约束条件。

     
 3. 实体型之间的联系分为一对一、一对多和多对多三种类型。

     
 4. 常见的数据模型包括:关系、层次、网状、面向对象、对象关系映射等几种。

     
 5. 关系模型的完整性约束包括:实体完整性、参照完整性和用户定义完整性。

      6. 阐述数据库三级模式、二级映象的含义及作用。

     
  数据库三级模式反映的是数据的三个抽象层次: 模式是对数据库中全体数据的逻辑结构和特征的描述内模式又称为存储模式,是对数据库物理结构和存储方式的描述。外模式又称为子模式或用户模式,是对特定数据库用户相关的局部数据的逻辑结构和特征的描述

     
  数据库三级模式通过二级映象在 DBMS 内部实现这三个抽象层次的联系和转换。外模式面向应用程序, 通过外模式/模式映象与逻辑模式建立联系, 实现数据的逻辑独立性。 模式/内模式映象建立模式与内模式之间的一对一映射, 实现数据的物理独立性

       1.
Data:数据,是数据库中存储的基本对象,是描述事物的符号记录。
       2.
Database:数据库,是长期储存在计算机内、有组织的、可共享的大量数据的集合。
       3.
DBMS:数据库管理系统,是位于用户与操作系统之间的一层数据管理软件,用于科学地组织、存储和管理数据、高效地获取和维护数据。
       4.
DBS:数据库系统,指在计算机系统中引入数据库后的系统,一般由数据库、数据库管理系统、应用系统、数据库管理员(DBA)构成。
       5.
数据模型:是用来抽象、表示和处理现实世界中的数据和信息的工具,是对现实世界的模拟,是数据库系统的核心和基础;其组成元素有数据结构、数据操作和完整性约束。
       6.
概念模型:也称信息模型,是按用户的观点来对数据和信息建模,主要用于数据库设计。
       7.
逻辑模型:是按计算机系统的观点对数据建模,用于DBMS实现。
       8.
物理模型:是对数据最底层的抽象,描述数据在系统内部的表示方式和存取方法,在磁盘或磁带上的存储方式和存取方法,是面向计算机系统的。
       9.
实体和属性:客观存在并可相互区别的事物称为实体。实体所具有的某一特性称为属性。
     
 10.E-R图:即实体-关系图,用于描述现实世界的事物及其相互关系,是数据库概念模型设计的主要工具。
     
 11.关系模式:从用户观点看,关系模式是由一组关系组成,每个关系的数据结构是一张规范化的二维表。
     
 12.型/值:型是对某一类数据的结构和属性的说明;值是型的一个具体赋值,是型的实例。
     
 13.数据库模式:是对数据库中全体数据的逻辑结构(数据项的名字、类型、取值范围等)和特征(数据之间的联系以及数据有关的安全性、完整性要求)的描述。
     
 14.数据库的三级系统结构:外模式、模式和内模式。
     
 15.数据库内模式:又称为存储模式,是对数据库物理结构和存储方式的描述,是数据在数据库内部的表示方式。一个数据库只有一个内模式。
     
 16.数据库外模式:又称为子模式或用户模式,它是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述,是数据库用户的数据视图。通常是模式的子集。一个数据库可有多个外模式。
     
 17.数据库的二级映像:外模式/模式映像、模式/内模式映像。

 

第二节

     

2. 写sql语句实现下列查询
(表)t
a b(列)
1 2
1 3
1 4
2 1
2 2
3 1
4 1
5 3
5 2
查询结果要求
a b
1 2
2 1
3 1
4 1
5 2

一、相关概念

     
 1. 主键: 能够唯一地标识一个元组的属性或属性组称为关系的键或候选键。 若一个关系有多个候选键则可选其一作为主键(Primary key)。

     
 2. 外键:如果一个关系的一个或一组属性引用(参照)了另一个关系的主键,则称这个或这组属性为外码或外键(Foreign key)。

     
 3. 关系数据库: 依照关系模型建立的数据库称为关系数据库。 它是在某个应用领域的所有关系的集合

     
 4. 关系模式: 简单地说,关系模式就是对关系的型的定义, 包括关系的属性构成、各属性的数据类型、 属性间的依赖、 元组语义及完整性约束等。 关系是关系模式在某一时刻的状态或内容, 关系模型是型, 关系是值, 关系模型是静态的、 稳定的, 而关系是动态的、随时间不断变化的,因为关系操作在不断地更新着数据库中的数据

     
 5.
实体完整性:用于标识实体的唯一性。它要求基本关系必须要有一个能够标识元组唯一性的主键,主键不能为空,也不可取重复值。

     
 6. 参照完整性: 用于维护实体之间的引用关系。 它要求一个关系的外键要么为空, 要么取与被参照关系对应的主键值,即外键值必须是主键中已存在的值

     
 7. 用户定义的完整性:就是针对某一具体应用的数据必须满足的语义约束。包括非空、 唯一和布尔条件约束三种情况。

 二、重点知识点

 

二、重要知识点

     
1. 关系数据库语言分为关系代数、关系演算和结构化查询语言三大类。

     
2. 关系的5种基本操作是选择、投影、并、差、笛卡尔积

     
3.关系模式是对关系的描述,五元组形式化表示为:R(U,D,DOM,F),其中

     
      R —— 关系名

     
      U —— 组成该关系的属性名集合

     
      D —— 属性组 U 中属性所来自的域

     
      DOM —— 属性向域的映象集合

     
      F —— 属性间的数据依赖关系集合

     
4.笛卡尔乘积,选择和投影运算如下

数据库 1

     
 1. 数据库系统由数据库、数据库管理系统、应用系统数据库管理员构成。

3.一组通话记录(总共500万条):
ID 主叫号码 被叫号码 通话起始时间   通话结束时间           通话时长
1  98290000 0215466546656 2007-02-01 09:49:53.000 2007-02-01
09:50:16.00023
2  98290000 021546654666  2007-02-01 09:50:29.000 2007-02-0109:50:41.000
12
3  98290000 021546654666  2007-02-01 09:50:58.000 2007-02-0109:51:12.000
14
4  68290900 0755133329866 2007-02-01 10:04:31.000 2007-02-01
10:07:13.000162
5  78290000 0755255708638 2007-02-01 10:48:26.000 2007-02-01
10:49:23.00057
6  78290000 0755821119109 2007-02-01 10:49:39.000 2007-02-01
10:52:55.000196
7  78290000 035730928370  2007-02-01 11:30:45.000 2007-02-0111:31:58.000
73
8  78290000 0871138889904 2007-02-01 11:33:47.000 2007-02-01
11:35:00.00073
9  68290000 035730928379  2007-02-01 11:52:20.000 2007-02-0111:54:56.000
156
10 68290000 0298521811199 2007-02-01 12:44:45.000 2007-02-01
12:45:04.000 19
求其中同一个号码的两次通话之间间隔大于10秒的通话记录ID
例如:6,7,8,9,10条记录均符合。

第三节

     
 2. 数据模型的组成要素是:数据结构、数据操作、完整性约束条件

 

一、相关概念

     
 1. SQL:结构化查询语言的简称, 是关系数据库的标准语言。SQL 是一种通用的、 功能极强的关系数据库语言, 是对关系数据存取的标准接口, 也是不同数据库系统之间互操作的基础。集数据查询、数据操作、数据定义、和数据控制功能于一体。

     
 2. 数据定义:数据定义功能包括模式定义、表定义、视图和索引的定义。

     
 3. 嵌套查询:指将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 短语的条件中的查询。

     
 3. 实体型之间的联系分为一对一、一对多多对多三种类型。

 

二、重要知识点

     
 1. SQL 数据定义语句的操作对象有:模式、表、视图和索引。

     
 2. SQL 数据定义语句的命令动词是:CREATE、DROP 和 ALTER。

     
 3. RDBMS 中索引一般采用 B+树或 HASH 来实现

     
 4. 索引可以分为唯一索引、非唯一索引和聚簇索引三种类型。

数据库 2

  6.SQL 创建表语句的一般格式为

     
        CREATE TABLE <表名>

     
      
 ( <列名> <数据类型>[ <列级完整性约束> ]

     
      
 [,<列名> <数据类型>[ <列级完整性约束>] ] …

     
        [,<表级完整性约束> ] ) ;

其中<数据类型>可以是数据库系统支持的各种数据类型,包括长度和精度。 

   
列级完整性约束为针对单个列(本列)的完整性约束, 包括 PRIMARY KEY、 REFERENCES表名(列名)、UNIQUE、NOT NULL 等。 

   
表级完整性约束可以是基于表中多列的约束,包括 PRIMARY KEY ( 列名列表) 、FOREIGN KEY REFERENCES 表名(列名) 等。

     
 7. SQL 创建索引语句的一般格式为

     
        CREATE [UNIQUE] [CLUSTER] INDEX <索引名>

     
        ON <表名> (<列名列表> ) ;

其中UNIQUE:表示创建唯一索引,缺省为非唯一索引;

     
CLUSTER:表示创建聚簇索引,缺省为非聚簇索引;

     
<列名列表>:一个或逗号分隔的多个列名,每个列名后可跟 ASC 或 DESC,表示升/降序,缺省为升序。多列时则按为多级排序。 
  

   8. SQL 查询语句的一般格式为

     
      
 SELECT [ALL|DISTINCT] <算术表达式列表> FROM <表名或视图名列表>

     
        [ WHERE <条件表达式 1> ]

     
      
 [ GROUP BY <属性列表 1> [ HAVING <条件表达式 2 > ] ]

     
        [ ORDER BY <属性列表 2> [ ASC|DESC ] ] ;

其中

   
  ALL/DISTINCT: 缺省为 ALL, 即列出所有查询结果记录, 包括重复记录。 DISTINCT则对重复记录只列出一条

   
   算术表达式列表:一个或多个逗号分隔的算术表达式,表达式由常量(包括数字和字符串)、列名、函数和算术运算符构成。每个表达式后还可跟别名。也可用 *代表查询表中的所有列。

   
  <表名或视图名列表>: 一个或多个逗号分隔的表或视图名。 表或视图名后可跟别名。

   
  条件表达式 1:包含关系或逻辑运算符的表达式,代表查询条件。

   
  条件表达式 2:包含关系或逻辑运算符的表达式,代表分组条件。

   
  <属性列表 1>:一个或逗号分隔的多个列名。

   
  <属性列表 2>: 一个或逗号分隔的多个列名, 每个列名后可跟 ASC 或 DESC, 表示升/降序,缺省为升序。

     
 4. 常见的数据模型包括:关系、层次、网状、面向对象、对象关系映射等几种。

 

第四节

     
 5. 关系模型的完整性约束包括:实体完整性、参照完整性和用户定义完整性

(二)

一、相关概念和知识

     
 1.触发器是用户定义在基本表上的一类由事件驱动的特殊过程。由服务器自动激活, 能执行更为复杂的检查和操作,具有更精细和更强大的数据控制能力。使用 CREATE TRIGGER 命令建立触发器。

     
 2.计算机系统存在技术安全、管理安全和政策法律三类安全性问题。

     
 3. TCSEC/TDI 标准由安全策略、责任、保证和文档四个方面内容构成。

     
 4. 常用存取控制方法包括自主存取控制(DAC)和强制存取控制(MAC)两种。

     
 5. 自主存取控制(DAC)的 SQL 语句包括 GRANT 和 REVOKE 两个。 用户权限由数据对象和操作类型两部分构成。

数据库 3

     
 6. 常见SQL 自主权限控制命令和例子。

     
   1) 把对 Student 和 Course 表的全部权限授予所有用户。

     
        GRANT ALL PRIVILIGES ON TABLE Student,Course TO PUBLIC

     
   2) 把对 Student 表的查询权和姓名修改权授予用户 U4。

     
        GRANT SELECT,UPDATE(Sname) ON TABLE Student TO U4 ;

     
   3) 把对 SC 表的插入权限授予 U5 用户,并允许他传播该权限。

     
        GRANT INSERT ON TABLE SC TO U5 WITH GRANT OPTION ;

     
   4) 把用户 U5 对 SC 表的 INSERT
权限收回,同时收回被他传播出去的授权。

     
        REVOKE INSERT ON TABLE SC FROM U5 CASCADE ;

     
   5) 创建一个角色 R1,并使其对 Student
表具有数据查询和更新权限。

     
        CREATE ROLE R1;

     
        GRANT SELECT,UPDATE ON TABLE Student TO R1;

     
   6) 对修改 Student 表结构的操作进行审计。

     
        AUDIT ALTER ON Student ;

 
    6. 阐述数据库三级模式、二级映象的含义及作用。
        数据库三级模式反映的是数据的三个抽象层次: 模式是对数据库中全体数据的逻辑结构和特征的描述。内模式又称为存储模式,是对数据库物理结构和存储方式的描述。外模式又称为子模式或用户模式,是对特定数据库用户相关的局部数据的逻辑结构和特征的描述。

 

数据库知识总结(2)范式

     
  数据库三级模式通过二级映象在 DBMS 内部实现这三个抽象层次的联系和转换。外模式面向应用程序, 通过外模式/模式映象与逻辑模式建立联系, 实现数据的逻辑独立性。 模式/内模式映象建立模式与内模式之间的一对一映射, 实现数据的物理独立性。

Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表

 一、相关概念和知识点

     
 1.数据依赖:反映一个关系内部属性与属性之间的约束关系,是现实世界属性间相互联系的抽象,属于数据内在的性质和语义的体现。

     
 2. 规范化理论:是用来设计良好的关系模式的基本理论。它通过分解关系模式来消除其中不合适的数据依赖,以解决插入异常、删除异常、更新异常和数据冗余问题。

     
 3. 函数依赖:简单地说,对于关系模式的两个属性子集X和Y,若X的任一取值能唯一确定Y的值,则称Y函数依赖于X,记作X→Y。

     
 4. 非平凡函数依赖:对于关系模式的两个属性子集X和Y,如果X→Y,但Y!⊆X,则称X→Y为非平凡函数依赖;如果X→Y,但Y⊆X,则称X→Y为非平凡函数依赖。

     
 5. 完全函数依赖:对于关系模式的两个属性子集X和Y,如果X→Y,并且对于X的任何一个真子集X’,都没有X’→Y,则称Y对X完全函数依赖。

     
 6. 范式:指符合某一种级别的关系模式的集合。在设计关系数据库时,根据满足依赖关系要求的不同定义为不同的范式。

     
 7. 规范化:指将一个低一级范式的关系模式,通过模式分解转换为若干个高一级范式的关系模式的集合的过程。

     
 8. 1NF:若关系模式的所有属性都是不可分的基本数据项,则该关系模式属于1NF。

     
 9. 2NF:1NF关系模式如果同时满足每一个非主属性完全函数依赖于码,则该关系模式属于2NF。

     
 10. 3NF:若关系模式的每一个非主属性既不部分依赖于码也不传递依赖于码,则该关系模式属于3NF。

     
 11. BCNF:若一个关系模式的每一个决定因素都包含码,则该关系模式属于BCNF。

     
 12. 数据库设计:是指对于一个给定的应用环境,构造优化的数据库逻辑模式和物理结构,并据此建立数据库及其应用系统,使之能够有效地存储和管理数据,满足各种用户的应用需求,包括信息管理要求和数据操作要求。

     
 13.
数据库设计的6个基本步骤:需求分析,概念结构设计,逻辑结构设计,物理结构设计,数据库实施,数据库运行和维护。

     
 14. 概念结构设计:指将需求分析得到的用户需求抽象为信息结构即概念模型的过程。也就是通过对用户需求进行综合、归纳与抽象,形成一个独立于具体DBMS的概念模型。

     
 15. 逻辑结构设计:将概念结构模型(基本E-R图)转换为某个DBMS产品所支持的数据模型相符合的逻辑结构,并对其进行优化。

     
 16. 物理结构设计:指为一个给定的逻辑数据模型选取一个最适合应用环境的物理结构的过程。包括设计数据库的存储结构与存取方法。

     
 17. 抽象:指对实际的人、物、事和概念进行人为处理,抽取所关心的共同特性,忽略非本质的细节,并把这些特性用各种概念精确地加以描述,这些概念组成了某种模型。     
 18. 数据库设计必须遵循结构设计和行为设计相结合的原则。     
 19. 数据字典主要包括数据项、数据结构、数据流、数据存储和处理过程五个部分。

     
 20. 三种常用抽象方法是分类、聚集和概括。

     
 21. 局部 E-R
图之间的冲突主要表现在属性冲突、命名冲突和结构冲突三个方面。     
 22. 数据库常用的存取方法包括索引方法、聚簇方法和 HASH方法三种。

     
 23. 确定数据存放位置和存储结构需要考虑的因素主要有: 存取时间、
存储空间利用率和维护代价等。

第二节

 

二、细说数据库三范式

  2.1 第一范式(1NF)无重复的列

       第一范式(1NF)中数据库表的每一列都是不可分割的基本数据项

     
 同一列中不能有多个值

     
 即实体中的某个属性不能有多个值或者不能有重复的属性

     
 简而言之,第一范式就是无重复的列。

     
 在任何一个关系数据库中,第一范式(1NF)是对关系模式的基本要求,不满足第一范式(1NF)的数据库就不是关系数据库

  

  2.2 第二范式(2NF)属性完全依赖于主键[消除部分子函数依赖]     

  满足第二范式(2NF)必须先满足第一范式(1NF)。   
 

  第二范式(2NF)要求数据库表中的每个实例或行必须可以被惟一地区分。     

  为实现区分通常需要为表加上一个列,以存储各个实例的惟一标识。 

  第二范式(2NF)要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性,如果存在,那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体,新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列,以存储各个实例的惟一标识。简而言之,第二范式就是属性完全依赖于主键。

  2.3 第三范式(3NF)属性不依赖于其它非主属性[消除传递依赖]

     
 满足第三范式(3NF)必须先满足第二范式(2NF)。

     
 简而言之,第三范式(3NF)要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

     
 例如,存在一个部门信息表,其中每个部门有部门编号(dept_id)、部门名称、部门简介等信息。那么在的员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表,则根据第三范式(3NF)也应该构建它,否则就会有大量的数据冗余。简而言之,第三范式就是属性不依赖于其它非主属性。

  

  2.4
具体实例剖析

  下面列举一个学校的学生系统的实例,以示几个范式的应用。

  在设计数据库表结构之前,我们先确定一下要设计的内容包括那些。学号、学生姓名、年龄、性别、课程、课程学分、系别、学科成绩,系办地址、系办电话等信息。为了简单我们暂时只考虑这些字段信息。我们对于这些信息,说关心的问题有如下几个方面。

     
 1)学生有那些基本信息 
     
 2)学生选了那些课,成绩是什么 
     
 3)每个课的学分是多少 
     
 4)学生属于那个系,系的基本信息是什么。

     
 首先第一范式(1NF):数据库表中的字段都是单一属性的,不可再分。这个单一属性由基本类型构成,包括整型、实数、字符型、逻辑型、日期型等。在当前的任何关系数据库管理系统(DBMS)中,不允许你把数据库表的一列再分成二列或多列,因此做出的都是符合第一范式的数据库。 

     
 我们再考虑第二范式,把所有这些信息放到一个表中(学号,学生姓名、年龄、性别、课程、课程学分、系别、学科成绩,系办地址、系办电话)下面存在如下的依赖关系。 
     
 1)(学号)→ (姓名, 年龄,性别,系别,系办地址、系办电话) 
     
 2) (课程名称) → (学分) 
     
 3)(学号,课程)→ (学科成绩)

  根据依赖关系我们可以把选课关系表SelectCourse改为如下三个表: 

     
 学生:Student(学号,姓名, 年龄,性别,系别,系办地址、系办电话); 
     
 课程:Course(课程名称, 学分); 
     
 选课关系:SelectCourse(学号, 课程名称, 成绩)。

     
 事实上,对照第二范式的要求,这就是满足第二范式的数据库表,若不满足第二范式,会产生如下问题

  (1)数据冗余: 同一门课程由n个学生选修,”学分”就重复n-1次;同一个学生选修了m门课程,姓名和年龄就重复了m-1次。

  (2)更新异常:1)若调整了某门课程的学分,数据表中所有行的”学分”值都要更新,否则会出现同一门课程学分不同的情况。 
     
         
 2)假设要开设一门新的课程,暂时还没有人选修。这样,由于还没有”学号”关键字,课程名称和学分也无法记录入数据库。

  (3)删除异常 : 假设一批学生已经完成课程的选修,这些选修记录就应该从数据库表中删除。但是,与此同时,课程名称和学分信息也被删除了。很显然,这也会导致插入异常。

  我们再考虑如何将其改成满足第三范式的数据库表,接着看上面的学生表Student(学号,姓名, 年龄,性别,系别,系办地址、系办电话),关键字为单一关键字”学号”,因为存在如下决定关系:

     
(学号)→ (姓名, 年龄,性别,系别,系办地址、系办电话) 
  但是还存在下面的决定关系 
     
 (学号) → (所在学院)→(学院地点, 学院电话) 
     
 
即存在非关键字段”学院地点”、”学院电话”对关键字段”学号”的传递函数依赖。 
     
 它也会存在数据冗余、更新异常、插入异常和删除异常的情况(这里就不具体分析了,参照第二范式中的分析)。根据第三范式把学生关系表分为如下两个表就可以满足第三范式了:

     
 学生:(学号, 姓名, 年龄, 性别,系别); 
     
 系别:(系别, 系办地址、系办电话)。

SQL语句总结

一、相关概念

问题:
1、查询“001”课程比“002”课程成绩高的所有学生的学号;
  select a.S# from (select s#,score from SC where C#=’001′)
a,(selects#,score
  from SC where C#=’002′) b
  where a.score>b.score and a.s#=b.s#;
2、查询平均成绩大于60分的同学的学号和平均成绩;
    select S#,avg(score)
    from sc
    group by S# having avg(score) >60;
3、查询所有同学的学号、姓名、选课数、总成绩;
  select Student.S#,Student.Sname,count(SC.C#),sum(score)
  from Student left Outer join SC on Student.S#=SC.S#
  group by Student.S#,Sname
4、查询姓“李”的老师的个数;
  select count(distinct(Tname))
  from Teacher
  where Tname like ‘李%’;
5、查询没学过“叶平”老师课的同学的学号、姓名;
    select Student.S#,Student.Sname
    from Student 
    where S# not in (select distinct( SC.S#) fromSC,Course,Teacher
where  SC.C#=Course.C# and Teacher.T#=Course.T#
andTeacher.Tname=’叶平’);
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
  select Student.S#,Student.Sname from Student,SC where
Student.S#=SC.S#and SC.C#=’001’and exists( Select * from SC as SC_2
where SC_2.S#=SC.S# andSC_2.C#=’002′);
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
  select S#,Sname
  from Student
  where S# in (select S# from SC ,Course ,Teacher where
SC.C#=Course.C#and Teacher.T#=Course.T# and Teacher.Tname=’叶平’
groupby S# having count(SC.C#)=(select count(C#) from Course,Teacher 
whereTeacher.T#=Course.T# and Tname=’叶平’));
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
  Select S#,Sname from (select Student.S#,Student.Sname,score
,(selectscore from SC SC_2 where SC_2.S#=Student.S# and
SC_2.C#=’002′) score2
  from Student,SC where Student.S#=SC.S# and C#=’001′) S_2 where
score2<score;
9、查询所有课程成绩小于60分的同学的学号、姓名;
  select S#,Sname
  from Student
  where S# not in (select Student.S# from Student,SC where
S.S#=SC.S# andscore>60);
10、查询没有学全所有课的同学的学号、姓名;
    select Student.S#,Student.Sname
    from Student,SC
    where Student.S#=SC.S# group by Student.S#,Student.Sname having
count(C#) <(select count(C#) from Course);
11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;
    select S#,Sname from Student,SC where Student.S#=SC.S# andC# in
select C# from SC where S#=’1001′;
12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名;
    select distinct SC.S#,Sname
    from Student,SC
    where Student.S#=SC.S# and C# in (select C# from SC
whereS#=’001′);
13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;
    update SC set score=(select avg(SC_2.score)
    from SC SC_2
    where SC_2.C#=SC.C# ) from Course,Teacher whereCourse.C#=SC.C#
and Course.T#=Teacher.T# and Teacher.Tname=’叶平’);
14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名;
    select S# from SC where C# in (select C# from SC
whereS#=’1002′)
    group by S# having count(*)=(select count(*) from SC
whereS#=’1002′);
15、删除学习“叶平”老师课的SC表记录;
    Delect SC
    from course ,Teacher 
    where Course.C#=SC.C# and Course.T#= Teacher.T# and
Tname=’叶平’;
16、向SC表中插入一些记录,这些记录要求符合以下条件:没有上过编号“003”课程的同学学号、2、
    号课的平均成绩;
    Insert SC select S#,’002′,(Select avg(score)
    from SC where C#=’002′) from Student where S# not in (SelectS#
from SC where C#=’002′);
17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩,按如下形式显示:
学生ID,,数据库,企业管理,英语,有效课程数,有效平均分
    SELECT S# as 学生ID
        ,(SELECT score FROM SC WHERESC.S#=t.S# AND C#=’004′) AS
数据库
        ,(SELECT score FROM SC WHERESC.S#=t.S# AND C#=’001′) AS
企业管理
        ,(SELECT score FROM SC WHERE SC.S#=t.S#AND C#=’006′) AS
英语
        ,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩
    FROM SC AS t
    GROUP BY S#
    ORDER BY avg(t.score) 
18、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
    SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分
    FROM SC L ,SC AS R
    WHERE L.C# = R.C# and
        L.score = (SELECT MAX(IL.score)
                     FROM SC AS IL,Student AS IM
                     WHERE L.C# = IL.C# and IM.S#=IL.S#
                     GROUP BY IL.C#)
        AND
        R.Score = (SELECT MIN(IR.score)
                     FROM SC AS IR
                     WHERE R.C# = IR.C#
                 GROUP BY IR.C#
                   );
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
    SELECT t.C# AS
课程号,max(course.Cname)AS课程名,isnull(AVG(score),0) AS 平均成绩
        ,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0
END)/COUNT(*) AS 及格百分数
    FROM SC T,Course
    where t.C#=course.C#
    GROUP BY t.C#
    ORDER BY 100 * SUM(CASE WHEN  isnull(score,0)>=60THEN 1 ELSE 0
END)/COUNT(*) DESC
20、查询如下课程平均成绩和及格率的百分数(用”1行”显示):
企业管理(001),马克思(002),OO&UML (003),数据库(004)
    SELECT SUM(CASE WHEN C# =’001′ THEN score ELSE 0END)/SUM(CASE C#
WHEN ‘001’ THEN 1 ELSE 0 END) AS 企业管理平均分
        ,100 * SUM(CASE WHEN C# = ‘001’ ANDscore >= 60 THEN 1 ELSE
0 END)/SUM(CASE WHEN C# = ‘001’ THEN 1 ELSE 0 END)AS
企业管理及格百分数
        ,SUM(CASE WHEN C# = ‘002’ THEN scoreELSE 0 END)/SUM(CASE C#
WHEN ‘002’ THEN 1 ELSE 0 END) AS 马克思平均分
        ,100 * SUM(CASE WHEN C# = ‘002’ ANDscore >= 60 THEN 1 ELSE
0 END)/SUM(CASE WHEN C# = ‘002’ THEN 1 ELSE 0 END)AS 马克思及格百分数
        ,SUM(CASE WHEN C# = ‘003’ THEN scoreELSE 0 END)/SUM(CASE C#
WHEN ‘003’ THEN 1 ELSE 0 END) AS UML平均分
        ,100 * SUM(CASE WHEN C# = ‘003’ ANDscore >= 60 THEN 1 ELSE
0 END)/SUM(CASE WHEN C# = ‘003’ THEN 1 ELSE 0 END)AS UML及格百分数
        ,SUM(CASE WHEN C# = ‘004’ THEN scoreELSE 0 END)/SUM(CASE C#
WHEN ‘004’ THEN 1 ELSE 0 END) AS 数据库平均分
        ,100 * SUM(CASE WHEN C# = ‘004’ ANDscore >= 60 THEN 1 ELSE
0 END)/SUM(CASE WHEN C# = ‘004’ THEN 1 ELSE 0 END)AS 数据库及格百分数
  FROM SC

SQL语句中常用关键词及其解释如下:

     
 1. 主键: 能够唯一地标识一个元组的属性或属性组称为关系的键或候选键。 若一个关系有多个候选键则可选其一作为主键(Primary key)。

 

1)SELECT

将资料从数据库中的表格内选出,两个关键字:从 (FROM) 数据库中的表格内选出
(SELECT)。语法为
SELECT
“栏位名” FROM “表格名”。

     
 2. 外键:如果一个关系的一个或一组属性引用(参照)了另一个关系的主键,则称这个或这组属性为外码或外键(Foreign key)。

21、查询不同老师所教不同课程平均分从高到低显示
  SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS
课程ID,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均成绩
    FROM SC AS T,Course AS C ,Teacher AS Z
    where T.C#=C.C# and C.T#=Z.T#
  GROUP BY C.C#
  ORDER BY AVG(Score) DESC
22、查询如下课程成绩第 3 名到第 6
名的学生成绩单:企业管理(001),马克思(002),UML
(003),数据库(004)
    [学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩
    SELECT  DISTINCT top 3
      SC.S# As 学生学号,
        Student.Sname AS 学生姓名 ,
      T1.score AS 企业管理,
      T2.score AS 马克思,
      T3.score AS UML,
      T4.score AS 数据库,
      ISNULL(T1.score,0) + ISNULL(T2.score,0) +ISNULL(T3.score,0) +
ISNULL(T4.score,0) as 总分
      FROM Student,SC  LEFT JOIN SC AS T1
                     ON SC.S# = T1.S# AND T1.C# = ‘001’
            LEFT JOIN SCAS T2
                     ON SC.S# = T2.S# AND T2.C# = ‘002’
            LEFT JOIN SCAS T3
                     ON SC.S# = T3.S# AND T3.C# = ‘003’
            LEFT JOIN SCAS T4
                     ON SC.S# = T4.S# AND T4.C# = ‘004’
      WHERE student.S#=SC.S# and
      ISNULL(T1.score,0) + ISNULL(T2.score,0) +ISNULL(T3.score,0) +
ISNULL(T4.score,0)
      NOT IN
      (SELECT
            DISTINCT
            TOP 15 WITHTIES
           ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0)
+ISNULL(T4.score,0)
      FROM sc
            LEFT JOIN scAS T1
                     ON sc.S# = T1.S# AND T1.C# = ‘k1’
            LEFT JOIN scAS T2
                     ON sc.S# = T2.S# AND T2.C# = ‘k2’
            LEFT JOIN scAS T3
                     ON sc.S# = T3.S# AND T3.C# = ‘k3’
            LEFT JOIN scAS T4
                     ON sc.S# = T4.S# AND T4.C# = ‘k4’
      ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0)+
ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC);

2)DISTINCT

在上述 SELECT 关键词后加上一个 DISTINCT
就可以去除选择出来的栏位中的重复,从而完成求得这个表格/栏位内有哪些不同的值的功能。语法为
SELECT
DISTINCT “栏位名” FROM “表格名”。

     
 3. 关系数据库: 依照关系模型建立的数据库称为关系数据库。 它是在某个应用领域的所有关系的集合。

 

3)WHERE

这个关键词可以帮助我们选择性地抓资料,而不是全取出来。语法为
SELECT
“栏位名” FROM “表格名” WHERE “条件” 

     
 4. 关系模式: 简单地说,关系模式就是对关系的型的定义, 包括关系的属性构成、各属性的数据类型、 属性间的依赖、 元组语义及完整性约束等。 关系是关系模式在某一时刻的状态或内容, 关系模型是型, 关系是值, 关系模型是静态的、 稳定的, 而关系是动态的、随时间不断变化的,因为关系操作在不断地更新着数据库中的数据。

23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[
<60]
    SELECT SC.C# as 课程ID, Cname as 课程名称
        ,SUM(CASE WHEN score BETWEEN 85 AND100 THEN 1 ELSE 0 END) AS
[100 – 85]
        ,SUM(CASE WHEN score BETWEEN 70 AND85 THEN 1 ELSE 0 END) AS [85

4)AND OR

上例中的 WHERE
指令可以被用来由表格中有条件地选取资料。这个条件可能是简单的
(像上一页的例子),也可能是复杂的。复杂条件是由二或多个简单条件透过 AND
或是 OR 的连接而成。语法为:
SELECT
“栏位名”  FROM “表格名”  WHERE “简单条件”  {[AND|OR]
“简单条件”}+

     
 5. . 实体完整性:用于标识实体的唯一性。它要求基本关系必须要有一个能够标识元组唯一性的主键,主键不能为空,也不可取重复值

  • 70]
            ,SUM(CASE WHEN score BETWEEN 60 AND70 THEN 1 ELSE 0 END) AS [70
  • 60]
            ,SUM(CASE WHEN score < 60 THEN 1ELSE 0 END) AS [60 -]
        FROM SC,Course
        where SC.C#=Course.C#
        GROUP BY SC.C#,Cname;  

5)IN

在 SQL 中,在两个情况下会用到 IN  这个指令;这一页将介绍其中之一:与
WHERE
有关的那一个情况。在这个用法下,我们事先已知道至少一个我们需要的值,而我们将这些知道的值都放入
IN  这个子句。语法为:
SELECT
“栏位名”  FROM “表格名”  WHERE “栏位名” IN (‘值一’, ‘值二’, …)
 

     
 6. 参照完整性: 用于维护实体之间的引用关系。 它要求一个关系的外键要么为空, 要么取与被参照关系对应的主键值,即外键值必须是主键中已存在的值

24、查询学生平均成绩及其名次
      SELECT 1+(SELECT COUNT( distinct 平均成绩)
             FROM (SELECT S#,AVG(score) AS 平均成绩
                     FROM SC
                 GROUP BY S#
                 ) AS T1
            WHERE 平均成绩 > T2.平均成绩) as 名次,
      S# as 学生学号,平均成绩
    FROM (SELECT S#,AVG(score) 平均成绩
            FROM SC
        GROUP BY S#
        ) AS T2
    ORDER BY 平均成绩 desc;
25、查询各科成绩前三名的记录:(不考虑成绩并列情况)
      SELECT t1.S# as 学生ID,t1.C#as 课程ID,Score as 分数
      FROM SC t1
      WHERE score IN (SELECT TOP 3 score
             FROM SC
             WHERE t1.C#= C#
            ORDER BYscore DESC
             )
      ORDER BY t1.C#;
26、查询每门课程被选修的学生数
  select c#,count(S#) from sc group by C#;
27、查询出只选修了一门课程的全部学生的学号和姓名
  select SC.S#,Student.Sname,count(C#) AS 选课数
  from SC ,Student
  where SC.S#=Student.S# group by SC.S# ,Student.Sname having
count(C#)=1;
28、查询男生、女生人数
    Select count(Ssex) as 男生人数 fromStudent group by Ssex having
Ssex=’男’;
    Select count(Ssex) as 女生人数 fromStudent group by Ssex having
Ssex=’女’;
29、查询姓“张”的学生名单
    SELECT Sname FROM Student WHERE Sname like ‘张%’;
30、查询同名同性学生名单,并统计同名人数
  select Sname,count(*) from Student group by Sname
having count(*)>1;;
31、1981年出生的学生名单(注:Student表中Sage列的类型是datetime)
    select Sname,  CONVERT(char (11),DATEPART(year,Sage))as age
    from student
    where  CONVERT(char(11),DATEPART(year,Sage))=’1981′;
32、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列
    Select C#,Avg(score) from SC group by C# order byAvg(score),C#
DESC ;
33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
    select Sname,SC.S# ,avg(score)
    from Student,SC
    where Student.S#=SC.S# group by SC.S#,Snamehaving   
avg(score)>85;
34、查询课程名称为“数据库”,且分数低于60的学生姓名和分数
    Select Sname,isnull(score,0)
    from Student,SC,Course
    where SC.S#=Student.S# and SC.C#=Course.C#
and Course.Cname=’数据库’and score <60;
35、查询所有学生的选课情况;
    SELECT SC.S#,SC.C#,Sname,Cname
    FROM SC,Student,Course
    where SC.S#=Student.S# and SC.C#=Course.C# ;
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;
    SELECT  distinctstudent.S#,student.Sname,SC.C#,SC.score
    FROM student,Sc
    WHERE SC.score>=70 AND SC.S#=student.S#;
37、查询不及格的课程,并按课程号从大到小排列
    select c# from sc where scor e <60 order by C# ;
38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;
    select SC.S#,Student.Sname from SC,Student whereSC.S#=Student.S#
and Score>80 and C#=’003′;
39、求选了课程的学生人数
    select count(*) from sc;
40、查询选修“叶平”老师所授课程的学生中,成绩最高的学生姓名及其成绩
    select Student.Sname,score
    from Student,SC,Course C,Teacher
    where Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T#and
Teacher.Tname=’叶平’ and SC.score=(selectmax(score)from SC where
C#=C.C# );
41、查询各个课程及相应的选修人数
    select count(*) from sc group by C#;
42、查询不同课程成绩相同的学生的学号、课程号、学生成绩
  select distinct  A.S#,B.score from SC A  ,SC B whereA.Score=B.Score
and A.C# <>B.C# ;
43、查询每门功成绩最好的前两名
    SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
      FROM SC t1
      WHERE score IN (SELECT TOP 2 score
             FROM SC
             WHERE t1.C#= C#
            ORDER BYscore DESC
             )
      ORDER BY t1.C#;
44、统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,查询结果按人数降序排列,若人数相同,按课程号升序排列 
    select  C# as 课程号,count(*) as 人数
    from  sc 
    group  by  C#
    order  by  count(*) desc,c# 
45、检索至少选修两门课程的学生学号
    select  S# 
    from  sc 
    group  by  s#
    having  count(*)  >  =  2
46、查询全部学生都选修的课程的课程号和课程名
    select  C#,Cname 
    from  Course 
    where  C#  in  (select  c# from  sc group  by  c#) 
47、查询没学过“叶平”老师讲授的任一门课程的学生姓名
    select Sname from Student where S# not in (select S#
fromCourse,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C#
and Tname=’叶平’);
48、查询两门以上不及格课程的同学的学号及其平均成绩
    select S#,avg(isnull(score,0)) from SC where S# in (selectS# from
SC where score <60 group by S# having count(*)>2)group by S#;
49、检索“004”课程分数小于60,按分数降序排列的同学学号
    select S# from SC where C#=’004’and score <60 order byscore
desc;
50、删除“002”同学的“001”课程的成绩
delete from Sc where S#=’001’and C#=’001′;

6)BETWEEN

IN 这个指令可以让我们依照一或数个不连续
(discrete)的值的限制之内抓出资料库中的值,而 BETWEEN
则是让我们可以运用一个范围 (range)
 内抓出资料库中的值,语法为:
SELECT
“栏位名”  FROM “表格名” WHERE “栏位名” BETWEEN ‘值一’ AND
‘值二’ 

     
 7. 用户定义的完整性:就是针对某一具体应用的数据必须满足的语义约束。包括非空、 唯一和布尔条件约束三种情况。

 

7)LIKE

LIKE 是另一个在 WHERE  子句中会用到的指令。基本上, LIKE
 能让我们依据一个模式(pattern) 来找出我们要的资料。语法为:
SELECT
“栏位名”  FROM “表格名”  WHERE “栏位名” LIKE {模式} 

      二、重要知识点

(三)

8)ORDER BY

我们经常需要能够将抓出的资料做一个有系统的显示。这可能是由小往大
(ascending)  或是由大往小(descending)。在这种情况下,我们就可以运用
ORDER BY 这个指令来达到我们的目的。语法为:
SELECT
“栏位名”  FROM “表格名 [WHERE “条件”] ORDER BY “栏位名” [ASC,
DESC] 

      1. 关系数据库语言分为关系代数、关系演算和结构化查询语言三大类。

 

9)函数

函数允许我们能够对这些数字的型态存在的行或者列做运算,包括 AVG
(平均)、COUNT (计数)、MAX (最大值)、MIN (最小值)、SUM
(总合)。语法为:
SELECT
“函数名”(“栏位名”) FROM “表格名”  

      2. 关系的 5 种基本操作是选择、投影、并、差、笛卡尔积。

问题描述:
本题用到下面三个关系表:
CARD     借书卡。   CNO 卡号,NAME  姓名,CLASS 班级
BOOKS    图书。     BNO书号,BNAME 书名,AUTHOR 作者,PRICE
单价,QUANTITY 库存册数
BORROW   借书记录。 CNO 借书卡号,BNO 书号,RDATE 还书日期
备注:限定每人每种书只能借一本;库存册数随借书、还书而改变。
要求实现如下15个处理:
  1.
写出建立BORROW表的SQL语句,要求定义主码完整性约束和引用完整性约束。
  2. 找出借书超过5本的读者,输出借书卡号及所借图书册数。
  3. 查询借阅了”水浒”一书的读者,输出姓名及班级。
  4. 查询过期未还图书,输出借阅者(卡号)、书号及还书日期。
  5. 查询书名包括”网络”关键词的图书,输出书号、书名、作者。
  6. 查询现有图书中价格最高的图书,输出书名及作者。
  7.
查询当前借了”计算方法”但没有借”计算方法习题集”的读者,输出其借书卡号,并按卡号降序排序输出。
  8. 将”C01″班同学所借图书的还期都延长一周。
  9. 从BOOKS表中删除当前无人借阅的图书记录。
  10.如果经常按书名查询图书信息,请建立合适的索引。
 
11.在BORROW表上建立一个触发器,完成如下功能:如果读者借阅的书名是”数据库技术及应用”,就将该读者的借阅记录保存在BORROW_SAVE表中(注ORROW_SAVE表结构同BORROW表)。
 
12.建立一个视图,显示”力01″班学生的借书信息(只要求显示姓名和书名)。
 
13.查询当前同时借有”计算方法”和”组合数学”两本书的读者,输出其借书卡号,并按卡号升序排序输出。
  14.假定在建BOOKS表时没有定义主码,写出为BOOKS表追加定义主码的语句。
  15.对CARD表做如下修改:
    a. 将NAME最大列宽增加到10个字符(假定原为6个字符)。
    b. 为该表增加1列NAME(系名),可变长,最大20个字符。

10)COUNT

这个关键词能够帮我我们统计有多少笔资料被选出来,语法为:
SELECT
COUNT(“栏位名”) FROM “表格名”

     
3.关系模式是对关系的描述,五元组形式化表示为:R(U,D,DOM,F),其中

 

11)GROUP BY

GROUP BY
语句用于结合合计函数,根据一个或多个列对结果集进行分组。语法为:
SELECT
“栏位1”, SUM(“栏位2”)  FROM “表格名”  GROUP BY “栏位1” 

            R —— 关系名

  1. 写出建立BORROW表的SQL语句,要求定义主码完整性约束和引用完整性约束
    –实现代码:
    CREATE TABLE BORROW(
        CNO int FOREIGN KEY REFERENCES CARD(CNO),
        BNO int FOREIGN KEY REFERENCES BOOKS(BNO),
        RDATE datetime,
        PRIMARY KEY(CNO,BNO))

     

  2. 找出借书超过5本的读者,输出借书卡号及所借图书册数
    –实现代码:
    SELECT CNO,借图书册数=COUNT(*)
    FROM BORROW
    GROUP BY CNO
    HAVING COUNT(*)>5

     

  3. 查询借阅了”水浒”一书的读者,输出姓名及班级
    –实现代码:
    SELECT * FROM CARD c
    WHERE EXISTS(
        SELECT * FROM BORROW a,BOOKS b
        WHERE a.BNO=b.BNO
            AND b.BNAME=N’水浒’
            AND a.CNO=c.CNO)

     

  4. 查询过期未还图书,输出借阅者(卡号)、书号及还书日期
    –实现代码:
    SELECT * FROM BORROW
    WHERE RDATE<GETDATE()

     

  5. 查询书名包括”网络”关键词的图书,输出书号、书名、作者
    –实现代码:
    SELECT BNO,BNAME,AUTHOR FROM BOOKS
    WHERE BNAME LIKE N’%网络%’

     

  6. 查询现有图书中价格最高的图书,输出书名及作者
    –实现代码:
    SELECT BNO,BNAME,AUTHOR FROM BOOKS
    WHERE PRICE=(
        SELECT MAX(PRICE) FROM BOOKS)

     

12)HAVING

该关键词可以帮助我们对函数产生的值来设定条件。语法为:
SELECT
“栏位1”, SUM(“栏位2”)  FROM “表格名”  GROUP BY “栏位1”  HAVING
(函数条件)  

            U —— 组成该关系的属性名集合

7.
查询当前借了”计算方法”但没有借”计算方法习题集”的读者,输出其借书卡号,并按卡号降序排序输出
–实现代码:
SELECT a.CNO
FROM BORROW a,BOOKS b
WHERE a.BNO=b.BNO AND b.BNAME=N’计算方法’
    AND NOT EXISTS(
        SELECT * FROM BORROW aa,BOOKS bb
        WHERE aa.BNO=bb.BNO
            ANDbb.BNAME=N’计算方法习题集’
            ANDaa.CNO=a.CNO)
ORDER BY a.CNO DESC

13)ALIAS

我们可以通过ALIAS为列名称和表名称指定别名,语法为:
SELECT
“表格别名”.”栏位1″ “栏位别名”  FROM “表格名” “表格别名”  

  • *

            D —— 属性组 U 中属性所来自的域

 

问题:

1、查询“001”课程比“002”课程成绩高的所有学生的学号;
select
a.S#
from
(select s#,score from SC where C#=’001′) a,
(select
s#,score from SC where C#=’002′) b
where
a.score>b.score and a.s#=b.s#;

2、查询平均成绩大于60分的同学的学号和平均成绩;
select
S#,avg(score)
from
sc
group
by S# having avg(score) >60;

3、查询所有同学的学号、姓名、选课数、总成绩;
select
Student.S#,Student.Sname,count(SC.C#),sum(score)
from
Student left Outer join SC on Student.S#=SC.S#
group
by Student.S#,Sname

4、查询姓“李”的老师的个数;
select
count(distinct(Tname))
from
Teacher
where
Tname like ‘李%’;

5、查询没学过“叶平”老师课的同学的学号、姓名;
select
Student.S#,Student.Sname
from
Student
where
S# not in (select distinct( SC.S#) from SC,Course,Teacher where
SC.C#=Course.C# and Teacher.T#=Course.T# and
Teacher.Tname=’叶平’);

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
select
Student.S#,Student.Sname
from
Student,SC

where
Student.S#=SC.S# and SC.C#=’001′and exists( Select * from SC as
SC_2 where SC_2.S#=SC.S# and SC_2.C#=’002′);

 

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
select
S#,Sname
from
Student
where
S# in
(select
S#
from
SC ,Course ,Teacher
where
SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=’叶平’
group by S# having count(SC.C#)=(select count(C#) from Course,Teacher
where Teacher.T#=Course.T# and Tname=’叶平’));

8、查询所有课程成绩小于60分的同学的学号、姓名;
select
S#,Sname
from
Student
where
S# not in (select Student.S# from Student,SC where S.S#=SC.S# and
score>60);

9、查询没有学全所有课的同学的学号、姓名;
select
Student.S#,Student.Sname
from
Student,SC
where
Student.S#=SC.S#
group
by Student.S#,Student.Sname having count(C#) <(select count(C#)
from Course);

10、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;
select
S#,Sname
from
Student,SC
where
Student.S#=SC.S# and C# in (select C# from SC where
S#=’1001’);

11、删除学习“叶平”老师课的SC表记录;
Delect
SC
from
course ,Teacher
where
Course.C#=SC.C# and Course.T#= Teacher.T# and Tname=’叶平’;

12、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
SELECT
L.C# 课程ID,L.score 最高分,R.score 最低分
FROM
SC L ,SC R
WHERE
L.C# = R.C#
and
L.score
= (SELECT MAX(IL.score)
FROM
SC IL,Student IM
WHERE
IL.C# = L.C# and IM.S#=IL.S#
GROUP
BY IL.C#)
and
R.Score
= (SELECT MIN(IR.score)
FROM
SC IR
WHERE
IR.C# = R.C#
GROUP
BY IR.C# );

13、查询学生平均成绩及其名次
SELECT
1+(SELECT COUNT( distinct 平均成绩)
FROM
(SELECT S#,AVG(score) 平均成绩
FROM
SC
GROUP
BY S# ) T1
WHERE
平均成绩 > T2.平均成绩) 名次, S# 学生学号,平均成绩
FROM
(SELECT S#,AVG(score) 平均成绩 FROM SC GROUP BY S# ) T2
ORDER
BY 平均成绩 desc;

14、查询各科成绩前三名的记录:(不考虑成绩并列情况)
SELECT
t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
FROM
SC t1
WHERE
score IN (SELECT TOP 3 score
FROM
SC
WHERE
t1.C#= C#
ORDER
BY score DESC)
ORDER
BY t1.C#;

15、查询每门功成绩最好的前两名
SELECT
t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
FROM
SC t1
WHERE
score IN (SELECT TOP 2 score
FROM
SC
WHERE
t1.C#= C#
ORDER
BY score DESC )

ORDER
BY t1.C#;

            DOM —— 属性向域的映象集合

  1. 将”C01″班同学所借图书的还期都延长一周
    –实现代码:
    UPDATE b SET RDATE=DATEADD(Day,7,b.RDATE)
    FROM CARD a,BORROW b
    WHERE a.CNO=b.CNO
        AND a.CLASS=N’C01′

     

  2. 从BOOKS表中删除当前无人借阅的图书记录
    –实现代码:
    DELETE A FROM BOOKS a
    WHERE NOT EXISTS(
        SELECT * FROM BORROW
        WHERE BNO=a.BNO)

     

  3. 如果经常按书名查询图书信息,请建立合适的索引
    –实现代码:
    CREATE CLUSTERED INDEX IDX_BOOKS_BNAME ON BOOKS(BNAME)

     

            F —— 属性间的数据依赖关系集合

11.
在BORROW表上建立一个触发器,完成如下功能:如果读者借阅的书名是”数据库技术及应用”,就将该读者的借阅记录保存在BORROW_SAVE表中(注ORROW_SAVE表结构同BORROW表)
–实现代码:
CREATE TRIGGER TR_SAVE ON BORROW
FOR INSERT,UPDATE
AS
IF @@ROWCOUNT>0
INSERT BORROW_SAVE SELECT i.*
FROM INSERTED i,BOOKS b
WHERE i.BNO=b.BNO
    AND b.BNAME=N’数据库技术及应用’

       4.笛卡尔乘积,选择和投影运算如下

 

数据库 4

  1. 建立一个视图,显示”力01″班学生的借书信息(只要求显示姓名和书名)
    –实现代码:
    CREATE VIEW V_VIEW
    AS
    SELECT a.NAME,b.BNAME
    FROM BORROW ab,CARD a,BOOKS b
    WHERE ab.CNO=a.CNO
        AND ab.BNO=b.BNO
        AND a.CLASS=N’力01′ 

第三节

13.
查询当前同时借有”计算方法”和”组合数学”两本书的读者,输出其借书卡号,并按卡号升序排序输出
–实现代码:
SELECT a.CNO
FROM BORROW a,BOOKS b
WHERE a.BNO=b.BNO
    AND b.BNAME IN(N’计算方法’,N’组合数学’)
GROUP BY a.CNO
HAVING COUNT(*)=2
ORDER BY a.CNO DESC

一、相关概念

 

     
 1. SQL:结构化查询语言的简称, 是关系数据库的标准语言。SQL 是一种通用的、 功能极强的关系数据库语言, 是对关系数据存取的标准接口, 也是不同数据库系统之间互操作的基础。集数据查询、数据操作、数据定义、和数据控制功能于一体。

  1. 假定在建BOOKS表时没有定义主码,写出为BOOKS表追加定义主码的语句
    –实现代码:
    ALTER TABLE BOOKS ADD PRIMARY KEY(BNO) 

     
 2. 数据定义:数据定义功能包括模式定义、表定义、视图和索引的定义。

15.1 将NAME最大列宽增加到10个字符(假定原为6个字符)
–实现代码:
ALTER TABLE CARD ALTER COLUMN NAME varchar(10)

     
 3. 嵌套查询:指将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 短语的条件中的查询。

 

 
    二、重要知识点

15.2 为该表增加1列NAME(系名),可变长,最大20个字符
–实现代码:
ALTER TABLE CARD ADD 系名 varchar(20)

       1. SQL
数据定义语句的操作对象有:模式、表、视图和索引。
       2. SQL 数据定义语句的命令动词是:CREATE、DROP 和 ALTER。
       3. RDBMS 中索引一般采用 B+树或 HASH 来实现。
       4. 索引可以分为唯一索引、非唯一索引和聚簇索引三种类型。

 

数据库 5

 

     
 6.SQL 创建表语句的一般格式为

 

            
 CREATE TABLE <表名>

(四)

            
 ( <列名> <数据类型>[ <列级完整性约束> ]

 

            
 [,<列名> <数据类型>[ <列级完整性约束>] ] …

问题描述:
为管理岗位业务培训信息,建立3个表:
S (S#,SN,SD,SA)   S#,SN,SD,SA
分别代表学号、学员姓名、所属单位、学员年龄
C (C#,CN )       C#,CN       分别代表课程编号、课程名称
SC ( S#,C#,G )    S#,C#,G    
分别代表学号、所选修的课程编号、学习成绩

            
 [,<表级完整性约束> ] ) ;

 

其中<数据类型>可以是数据库系统支持的各种数据类型,包括长度和精度。 

要求实现如下5个处理:
  1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名
  2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位
  3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位
  4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位
  5. 查询选修了课程的学员人数
  6. 查询选修课程超过5门的学员学号和所属单位

   
列级完整性约束
为针对单个列(本列)的完整性约束, 包括 PRIMARY KEY、 REFERENCES表名(列名)、UNIQUE、NOT NULL 等。 

 

   
表级完整性约束
可以是基于表中多列的约束,包括 PRIMARY KEY ( 列名列表) 、FOREIGN KEY REFERENCES 表名(列名) 等。

  1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名
    –实现代码:
    SELECT SN,SD FROM S
    WHERE [S#] IN(
        SELECT [S#] FROM C,SC
        WHERE C.[C#]=SC.[C#]
            AND CN=N’税收基础’)

     

  2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位
    –实现代码:
    SELECT S.SN,S.SD FROM S,SC
    WHERE S.[S#]=SC.[S#]
        AND SC.[C#]=’C2′

     

  3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位
    –实现代码:
    SELECT SN,SD FROM S
    WHERE [S#] NOT IN(
        SELECT [S#] FROM SC
        WHERE [C#]=’C5′)

     

  4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位
    –实现代码:
    SELECT SN,SD FROM S
    WHERE [S#] IN(
        SELECT [S#] FROM SC
            RIGHT JOIN C ON SC.[C#]=C.[C#]
        GROUP BY [S#]
        HAVING COUNT(*)=COUNT(DISTINCT [S#]))

     

  5. 查询选修了课程的学员人数
    –实现代码:
    SELECT 学员人数=COUNT(DISTINCT [S#]) FROM SC

     

  6. 查询选修课程超过5门的学员学号和所属单位
    –实现代码:
    SELECT SN,SD FROM S
    WHERE [S#] IN(
        SELECT [S#] FROM SC
        GROUP BY [S#]
        HAVING COUNT(DISTINCT [C#])>5)

     

 
     7. SQL 创建索引语句的一般格式为

(五)

     
      
 CREATE [UNIQUE] [CLUSTER] INDEX <索引名>

 

     
      
 ON <表名> (<列名列表> ) ;

一 SQLSERVER管理部分

其中UNIQUE:表示创建唯一索引,缺省为非唯一索引;

 

      CLUSTER:表示创建聚簇索引,缺省为非聚簇索引;

1
请讲出身份验证模式与登录帐号的关系及如何用各种帐号进行登录,并画出示意图
2 请讲出登录帐号、数据库用户及数据库角色之间的关系,并画出示意图
3
请讲出数据库用户、数据库角色与数据库对象之间的关系,并画出直接对用户授权与间接对用户授权(系统权限与对象权限)的方法
4
请讲出服务器角色、数据库角色、标准角色与应用程序角色的区别与验证其权限的方法
5 请讲出数据库还原模型对数据库的影响
6 有一个执行关键任务的数据库,请设计一个数据库备份策略
7 请使用文件与文件组恢复的方式恢复数据库
8 请使用事务日志恢复数据库到一个时间点
9 请设计作业进行周期性的备份数据库
10 如何监控数据库的阻塞,并实现数据库的死锁测试
11 如何监控数据库的活动,并能使用索引优化向导生成索引
12 理解数据库框图的作用并可以设计表与表之间的关系

      <列名列表>:一个或逗号分隔的多个列名,每个列名后可跟 ASC 或 DESC,表示升/降序,缺省为升序。多列时则按为多级排序。

 

     
  8. SQL 查询语句的一般格式为

1 有订单表,需要实现它的编号,格式如下:200211030001……200222039999等

     
      
 SELECT [ALL|DISTINCT] <算术表达式列表> FROM <表名或视图名列表>

 

     
      
 [ WHERE <条件表达式 1> ]

2
有表T1,T2,现有一事务,在向表T1添加数据时,同时也必须向T2也添加数据,如何实现该事务

     
      
 [ GROUP BY <属性列表 1> [ HAVING <条件表达式 2 > ] ]

 

     
      
 [ ORDER BY <属性列表 2> [ ASC|DESC ] ] ;

3
如何向T1中的编号字段(codevarchar(20))添加一万条记录,不充许重复,规则如下:编号的数据必须从小写的a-z之间取值

其中

 

   
  ALL/DISTINCT: 缺省为 ALL, 即列出所有查询结果记录, 包括重复记录。 DISTINCT则对重复记录只列出一条。

4 如何删除表中的重复数据,请使用游标与分组的办法

       算术表达式列表:一个或多个逗号分隔的算术表达式,表达式由常量(包括数字和字符串)、列名、函数和算术运算符构成。每个表达式后还可跟别名。也可用 *代表查询表中的所有列。

 

   
  <表名或视图名列表>: 一个或多个逗号分隔的表或视图名。 表或视图名后可跟别名。

5 如何求表中相邻的两条记录的某字段的值之差

   
  条件表达式 1:包含关系或逻辑运算符的表达式,代表查询条件。

 

   
  条件表达式 **2**:包含关系或逻辑运算符的表达式,代表分组条件。

6 如何统计数据库中所有用户表的数据,显示格式如下:

   
  <属性列表 1>:一个或逗号分隔的多个列名。

 

   
  <属性列表 2>: 一个或逗号分隔的多个列名, 每个列名后可跟 ASC 或 DESC, 表示升/降序,缺省为升序。

表名      记录数

 
   
关于SQL语句的知识这里先作如上简略介绍,具体写法下次将专门拿出一篇来叙述。

 

第四节

  sales      23

一、相关概念和知识

 

     
 1.触发器是用户定义在基本表上的一类由事件驱动的特殊过程。由服务器自动激活, 能执行更为复杂的检查和操作,具有更精细和更强大的数据控制能力。使用 CREATE TRIGGER 命令建立触发器。

7 如何删除数据库中的所有用户表(表与表之间有外键关系)

     
 2.计算机系统存在技术安全、管理安全和政策法律三类安全性问题。

 

     
 3. TCSEC/TDI 标准由安全策略、责任、保证和文档四个方面内容构成。

8 表A editor_id lb2_id
123 000
123 003
123 003
456 007
456 006
表B lb2_id lb2_name
000 a
003 b
006 c
007 d
显示 a 共1条 (表A内lb2_id为000的条数)
b 共2条(表A内lb2_id为003的条数)

     
 4. 常用存取控制方法包括自主存取控制(DAC)和强制存取控制(MAC)两种。

 

     
 5. 自主存取控制(DAC)的 SQL 语句包括 GRANT 和 REVOKE 两个。 用户权限由数据对象和操作类型两部分构成。

9
人员情况表(employee):里面有一字段文化程度(wh):包括四种情况(本科以上,大专,高中,初中以下),现在我要根据年龄字段查询统计出:表中文化程度为本科以上,大专,高中,初中以下,各有多少人,占总人数多少。

数据库 6

 

       6. 常见SQL 自主权限控制命令和例子。
         1) 把对 Student 和 Course 表的全部权限授予所有用户。
            
 GRANT ALL PRIVILIGES ON TABLE Student,Course TO PUBLIC ;
       
 2) 把对 Student 表的查询权和姓名修改权授予用户 U4。
            
 GRANT SELECT,UPDATE(Sname) ON TABLE Student TO U4 ;
       
 3) 把对 SC 表的插入权限授予 U5 用户,并允许他传播该权限。
            
 GRANT INSERT ON TABLE SC TO U5 WITH GRANT OPTION ;
       
 4) 把用户 U5 对 SC 表的 INSERT
权限收回,同时收回被他传播出去的授权。
            
 REVOKE INSERT ON TABLE SC FROM U5 CASCADE ;
       
 5) 创建一个角色 R1,并使其对 Student
表具有数据查询和更新权限。
            
 CREATE ROLE R1;
            
 GRANT SELECT,UPDATE ON TABLE Student TO R1;
       
 6) 对修改 Student 表结构的操作进行审计。
            
 AUDIT ALTER ON Student ;

SELECT wh AS 学历,age as 年龄, Count(*) AS 人数,

=====================================

 

数据库知识总结(2)范式

 一、相关概念和知识点

       1.数据依赖:反映一个关系内部属性与属性之间的约束关系,是现实世界属性间相互联系的抽象,属于数据内在的性质和语义的体现。
       2. 规范化理论:是用来设计良好的关系模式的基本理论。它通过分解关系模式来消除其中不合适的数据依赖,以解决插入异常、删除异常、更新异常和数据冗余问题。
       3. 函数依赖:简单地说,对于关系模式的两个属性子集X和Y,若X的任一取值能唯一确定Y的值,则称Y函数依赖于X,记作X→Y。
       4. 非平凡函数依赖:对于关系模式的两个属性子集X和Y,如果X→Y,但Y!⊆X,则称X→Y为非平凡函数依赖;如果X→Y,但Y⊆X,则称X→Y为非平凡函数依赖。
       5. 完全函数依赖:对于关系模式的两个属性子集X和Y,如果X→Y,并且对于X的任何一个真子集X’,都没有X’→Y,则称Y对X完全函数依赖。
       6. 范式:指符合某一种级别的关系模式的集合。在设计关系数据库时,根据满足依赖关系要求的不同定义为不同的范式。
       7. 规范化:指将一个低一级范式的关系模式,通过模式分解转换为若干个高一级范式的关系模式的集合的过程。
       8. 1NF:若关系模式的所有属性都是不可分的基本数据项,则该关系模式属于1NF。
       9. 2NF:1NF关系模式如果同时满足每一个非主属性完全函数依赖于码,则该关系模式属于2NF。
       10. 3NF:若关系模式的每一个非主属性既不部分依赖于码也不传递依赖于码,则该关系模式属于3NF。
       11. BCNF:若一个关系模式的每一个决定因素都包含码,则该关系模式属于BCNF。
       12. 数据库设计:是指对于一个给定的应用环境,构造优化的数据库逻辑模式和物理结构,并据此建立数据库及其应用系统,使之能够有效地存储和管理数据,满足各种用户的应用需求,包括信息管理要求和数据操作要求。
       13. 数据库设计的6个基本步骤:需求分析,概念结构设计,逻辑结构设计,物理结构设计,数据库实施,数据库运行和维护。
       14. 概念结构设计:指将需求分析得到的用户需求抽象为信息结构即概念模型的过程。也就是通过对用户需求进行综合、归纳与抽象,形成一个独立于具体DBMS的概念模型。
       15. 逻辑结构设计:将概念结构模型(基本E-R图)转换为某个DBMS产品所支持的数据模型相符合的逻辑结构,并对其进行优化。
       16. 物理结构设计:指为一个给定的逻辑数据模型选取一个最适合应用环境的物理结构的过程。包括设计数据库的存储结构与存取方法。
       17. 抽象:指对实际的人、物、事和概念进行人为处理,抽取所关心的共同特性,忽略非本质的细节,并把这些特性用各种概念精确地加以描述,这些概念组成了某种模型。

     
 18. 数据库设计必须遵循结构设计和行为设计相结合的原则。

     
 19. 数据字典主要包括数据项、数据结构、数据流、数据存储和处理过程五个部分。
       20. 三种常用抽象方法是分类、聚集和概括。
       21. 局部 E-R 图之间的冲突主要表现在属性冲突、命名冲突和结构冲突三个方面。

       22.
数据库常用的存取方法包括索引方法、聚簇方法和
HASH方法三种。
       23. 确定数据存放位置和存储结构需要考虑的因素主要有: 存取时间、
存储空间利用率和维护代价等。

二、细说数据库三范式

2.1 第一范式(1NF)无重复的列

       第一范式(1NF)中数据库表的每一列都是不可分割的基本数据项
       同一列中不能有多个值
     
 即实体中的某个属性不能有多个值或者不能有重复的属性。
     
 简而言之,第一范式就是无重复的列。

     
 在任何一个关系数据库中,第一范式(1NF)是对关系模式的基本要求,不满足第一范式(1NF)的数据库就不是关系数据库。

2.2 第二范式(2NF)属性完全依赖于主键[消除部分子函数依赖]

     
满足第二范式(2NF)必须先满足第一范式(1NF)。

     
第二范式(2NF)要求数据库表中的每个实例或行必须可以被惟一地区分。

     
为实现区分通常需要为表加上一个列,以存储各个实例的惟一标识。 
     
第二范式(2NF)要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性,如果存在,那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体,新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列,以存储各个实例的惟一标识。简而言之,第二范式就是属性完全依赖于主键。
2.3 第三范式(3NF)属性不依赖于其它非主属性[消除传递依赖]

     
满足第三范式(3NF)必须先满足第二范式(2NF)。

     
简而言之,第三范式(3NF)要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

     
例如,存在一个部门信息表,其中每个部门有部门编号(dept_id)、部门名称、部门简介等信息。那么在的员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表,则根据第三范式(3NF)也应该构建它,否则就会有大量的数据冗余。简而言之,第三范式就是属性不依赖于其它非主属性。

2.4
具体实例剖析

     
下面列举一个学校的学生系统的实例,以示几个范式的应用。

     
 在设计数据库表结构之前,我们先确定一下要设计的内容包括那些。学号、学生姓名、年龄、性别、课程、课程学分、系别、学科成绩,系办地址、系办电话等信息。为了简单我们暂时只考虑这些字段信息。我们对于这些信息,说关心的问题有如下几个方面。

     
 1)学生有那些基本信息 
       2)学生选了那些课,成绩是什么 
       3)每个课的学分是多少 
       4)学生属于那个系,系的基本信息是什么。

       首先第一范式(1NF):数据库表中的字段都是单一属性的,不可再分。这个单一属性由基本类型构成,包括整型、实数、字符型、逻辑型、日期型等。在当前的任何关系数据库管理系统(DBMS)中,不允许你把数据库表的一列再分成二列或多列,因此做出的都是符合第一范式的数据库。 

       我们再考虑第二范式,把所有这些信息放到一个表中(学号,学生姓名、年龄、性别、课程、课程学分、系别、学科成绩,系办地址、系办电话)下面存在如下的依赖关系。 
       1)(学号)→ (姓名, 年龄,性别,系别,系办地址、系办电话) 
       2) (课程名称) → (学分) 
       3)(学号,课程)→ (学科成绩)

根据依赖关系我们可以把选课关系表SelectCourse改为如下三个表: 

     
 学生:Student(学号,姓名, 年龄,性别,系别,系办地址、系办电话); 
       课程:Course(课程名称, 学分); 
       选课关系:SelectCourse(学号, 课程名称, 成绩)。

     
 事实上,对照第二范式的要求,这就是满足第二范式的数据库表,若不满足第二范式,会产生如下问题 
数据冗余: 同一门课程由n个学生选修,”学分”就重复n-1次;同一个学生选修了m门课程,姓名和年龄就重复了m-1次。

更新异常: 1)若调整了某门课程的学分,数据表中所有行的”学分”值都要更新,否则会出现同一门课程学分不同的情况。 
               
 2)假设要开设一门新的课程,暂时还没有人选修。这样,由于还没有”学号”关键字,课程名称和学分也无法记录入数据库。

删除异常 : 假设一批学生已经完成课程的选修,这些选修记录就应该从数据库表中删除。但是,与此同时,课程名称和学分信息也被删除了。很显然,这也会导致插入异常。

       我们再考虑如何将其改成满足第三范式的数据库表,接着看上面的学生表Student(学号,姓名, 年龄,性别,系别,系办地址、系办电话),关键字为单一关键字”学号”,因为存在如下决定关系:

     
(学号)→ (姓名, 年龄,性别,系别,系办地址、系办电话) 
但是还存在下面的决定关系 
       (学号) → (所在学院)→(学院地点, 学院电话) 
       
即存在非关键字段”学院地点”、”学院电话”对关键字段”学号”的传递函数依赖。 
     
 它也会存在数据冗余、更新异常、插入异常和删除异常的情况(这里就不具体分析了,参照第二范式中的分析)。根据第三范式把学生关系表分为如下两个表就可以满足第三范式了:

     
 学生:(学号, 姓名, 年龄, 性别,系别); 
       系别:(系别, 系办地址、系办电话)。

SQL语句总结

SQL语句中常用关键词及其解释如下:

      Count(*) * 100 /(SELECT Count(*) FROMemployee) AS 百分比

1)SELECT

将资料从数据库中的表格内选出,两个关键字:从
(FROM) 数据库中的表格内选出 (SELECT)。语法为
SELECT “栏位名” FROM “表格名”。

 

2)DISTINCT

在上述
SELECT 关键词后加上一个 DISTINCT
就可以去除选择出来的栏位中的重复,从而完成求得这个表格/栏位内有哪些不同的值的功能。语法为
SELECT DISTINCT “栏位名” FROM
“表格名”。

FROM employee GROUP BY wh,age

3)WHERE

这个关键词可以帮助我们选择性地抓资料,而不是全取出来。语法为
SELECT “栏位名” FROM “表格名”
WHERE “条件” 

 

4)AND OR

上例中的
WHERE 指令可以被用来由表格中有条件地选取资料。这个条件可能是简单的
(像上一页的例子),也可能是复杂的。复杂条件是由二或多个简单条件透过 AND
或是 OR 的连接而成。语法为:
SELECT “栏位名”  FROM “表格名”
 WHERE “简单条件”  {[AND|OR] “简单条件”}+

学历      年龄   人数      百分比

5)IN


SQL 中,在两个情况下会用到 IN  这个指令;这一页将介绍其中之一:与 WHERE
有关的那一个情况。在这个用法下,我们事先已知道至少一个我们需要的值,而我们将这些知道的值都放入
IN  这个子句。语法为:
SELECT “栏位名”  FROM “表格名”
 WHERE “栏位名” IN (‘值一’, ‘值二’, …)  

 

6)BETWEEN

IN
这个指令可以让我们依照一或数个不连续
(discrete)的值的限制之内抓出资料库中的值,而 BETWEEN
则是让我们可以运用一个范围 (range)  内抓出资料库中的值,语法为:
SELECT “栏位名”  FROM “表格名”
WHERE “栏位名” BETWEEN ‘值一’ AND ‘值二’ 

本科以上  20     34          14

7)LIKE

LIKE
是另一个在 WHERE  子句中会用到的指令。基本上, LIKE
 能让我们依据一个模式(pattern) 来找出我们要的资料。语法为:
SELECT “栏位名”  FROM “表格名”
 WHERE “栏位名” LIKE {模式} 

 

8)ORDER BY

我们经常需要能够将抓出的资料做一个有系统的显示。这可能是由小往大
(ascending)  或是由大往小(descending)。在这种情况下,我们就可以运用
ORDER BY 这个指令来达到我们的目的。语法为:
SELECT “栏位名”  FROM “表格名
[WHERE “条件”] ORDER BY “栏位名” [ASC, DESC] 

大专      20     33          13

9)函数

函数允许我们能够对这些数字的型态存在的行或者列做运算,包括
AVG (平均)、COUNT (计数)、MAX (最大值)、MIN (最小值)、SUM
(总合)。语法为:
SELECT “函数名”(“栏位名”) FROM
“表格名”  

 

10)COUNT

这个关键词能够帮我我们统计有多少笔资料被选出来,语法为:
SELECT COUNT(“栏位名”) FROM
“表格名”

高中      20     33          13

11)GROUP BY

GROUP
BY 语句用于结合合计函数,根据一个或多个列对结果集进行分组。语法为:
SELECT “栏位1”, SUM(“栏位2”)  FROM
“表格名”  GROUP BY “栏位1” 

 

12)HAVING

该关键词可以帮助我们对函数产生的值来设定条件。语法为:
SELECT “栏位1”, SUM(“栏位2”)  FROM
“表格名”  GROUP BY “栏位1”  HAVING (函数条件)  

初中以下  20     100        40

13)ALIAS

我们可以通过ALIAS为列名称和表名称指定别名,语法为:
SELECT “表格别名”.”栏位1″
“栏位别名”  FROM “表格名” “表格别名”  

下面为一个例子,通过它我们应该能很好地掌握以上关键词的使用方法。

Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表

问题:
1、查询“001”课程比“002”课程成绩高的所有学生的学号;
select a.S#
from (select s#,score from SC where C#=’001′) a,
(select s#,score from SC where C#=’002′) b
where a.score>b.score and a.s#=b.s#;

2、查询平均成绩大于60分的同学的学号和平均成绩;
select S#,avg(score)
from sc
group by S# having avg(score) >60;

3、查询所有同学的学号、姓名、选课数、总成绩;
select Student.S#,Student.Sname,count(SC.C#),sum(score)
from Student left Outer join SC on Student.S#=SC.S#
group by Student.S#,Sname

4、查询姓“李”的老师的个数;
select count(distinct(Tname))
from Teacher
where Tname like ‘李%’;

5、查询没学过“叶平”老师课的同学的学号、姓名;
select Student.S#,Student.Sname
from Student
where S# not in (select distinct( SC.S#) from SC,Course,Teacher where
SC.C#=Course.C# and Teacher.T#=Course.T# and
Teacher.Tname=’叶平’);

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
select Student.S#,Student.Sname
from Student,SC

where
Student.S#=SC.S# and SC.C#=’001′and exists( Select * from SC as
SC_2 where SC_2.S#=SC.S# and SC_2.C#=’002′);

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
select S#,Sname
from Student
where S# in
(select S#
from SC ,Course ,Teacher
where SC.C#=Course.C# and Teacher.T#=Course.T# and
Teacher.Tname=’叶平’ group by S# having count(SC.C#)=(select
count(C#) from Course,Teacher where Teacher.T#=Course.T# and
Tname=’叶平’));

8、查询所有课程成绩小于60分的同学的学号、姓名;
select S#,Sname
from Student
where S# not in (select Student.S# from Student,SC where S.S#=SC.S#
and score>60);

9、查询没有学全所有课的同学的学号、姓名;
select Student.S#,Student.Sname
from Student,SC
where Student.S#=SC.S#
group by Student.S#,Student.Sname having count(C#) <(select
count(C#) from Course);

10、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;
select S#,Sname
from Student,SC
where Student.S#=SC.S# and C# in (select C# from SC where
S#=’1001’);

11、删除学习“叶平”老师课的SC表记录;
Delect SC
from course ,Teacher
where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname=’叶平’;

12、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
SELECT L.C# 课程ID,L.score 最高分,R.score 最低分
FROM SC L ,SC R
WHERE L.C# = R.C#
and
L.score = (SELECT MAX(IL.score)
FROM SC IL,Student IM
WHERE IL.C# = L.C# and IM.S#=IL.S#
GROUP BY IL.C#)
and
R.Score = (SELECT MIN(IR.score)
FROM SC IR
WHERE IR.C# = R.C#
GROUP BY IR.C# );

13、查询学生平均成绩及其名次
SELECT 1+(SELECT COUNT( distinct 平均成绩)
FROM (SELECT S#,AVG(score) 平均成绩
FROM SC
GROUP BY S# ) T1
WHERE 平均成绩 > T2.平均成绩) 名次, S# 学生学号,平均成绩
FROM (SELECT S#,AVG(score) 平均成绩 FROM SC GROUP BY S# ) T2
ORDER BY 平均成绩 desc;

14、查询各科成绩前三名的记录:(不考虑成绩并列情况)
SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
FROM SC t1
WHERE score IN (SELECT TOP 3 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC)
ORDER BY t1.C#;

15、查询每门功成绩最好的前两名
SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
FROM SC t1
WHERE score IN (SELECT TOP 2 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC )

ORDER
BY t1.C#;

=

 

本科以上  21      50         20

 

10 现在有三个表student:(FID 学生号,FName  姓名),

 

subject:(FSubID  课程号,FSubName 课程名), 

 

Score(FScoreId  成绩记录号,FSubID   课程号,FStdID    学生号,FScore   
成绩)

 

怎么能实现这个表:

 

姓名  英语  数学 语文  历史

 

张萨  78    67   89    76

 

王强  89    67   84    96 

 

SELECT a.FName AS 姓名,

 

      英语 = SUM(CASEb.FSubName WHEN ‘英语’ THEN c.FScore END),

 

      数学 = SUM(CASEb.FSubName WHEN ‘数学’ THEN c.FScore END),

 

      语文 = SUM(CASEb.FSubName WHEN ‘语文’ THEN c.FScore END),

 

      历史 = SUM(CASEb.FSubName WHEN ‘历史’ THEN c.FScore END)

 

FROM Student a, Subject b, Score c

 

WHERE a.FID = c.FStdId AND b.FSubID = c.FsubID GROUP BY a.FName

 

11 原始表的数据如下:

 

PID PTime    PNo

 

111111    2003-01-2804:30:09     

 

111111    2003-01-28 18:30:00

 

222222    2003-01-2804:31:09     

 

333333    2003-01-2804:32:09     

 

111111    2003-02-0903:35:25     

 

222222    2003-02-0903:36:25     

 

333333    2003-02-0903:37:25     

 

查询生成表

 

PDate       111111        222222   333333      ……

 

2003-01-28   04:30:09    04:31:09      04:32:09   ……

 

2003-01-28   18:30:00

 

2003-02-09   03:35:25    03:36:25     03:37:25    ……

 

12  表一(AAA)

 

商品名称mc  商品总量sl

 

  A        100

 

  B        120

 

表二(BBB)

 

商品名称mc  出库数量sl

 

  A        10

 

  A        20

 

  B        10

 

  B        20

 

  B        30

 

用一条SQL语句算出商品A,B目前还剩多少?

 

 

declare @AAA table (商品名称  varchar(10), 商品总量  int)

 

insert into @AAA values(‘A’,100)

 

insert into @AAA values(‘B’,120)

 

declare @BBB table (商品名称 varchar(10), 出库数量 int)

 

insert into @BBB values(‘A’, 10)

 

insert into @BBB values(‘A’, 20)

 

insert into @BBB values(‘B’, 10)

 

insert into @BBB values(‘B’, 20)

 

insert into @BBB values(‘B’, 30)

 

select TA.商品名称,A-B AS 剩余数量 FROM

 

(select 商品名称,sum(商品总量) AS A

 

from @AAA

 

group by 商品名称)TA,

 

(select 商品名称,sum(出库数量) AS B

 

from @BBB

 

group by 商品名称)TB

 

where TA.商品名称=TB.商品名称

 

 

select 商品名称,sum(商品总量)剩余数量 from (select * from @aaa union
all select 商品名称,-出库数量 from @bbb) a group by 商品名称

 

13 优化这句SQL语句

 

UPDATE tblExlTempYear

 

SET tblExlTempYear.GDQC = tblExlTempMonth.GDQC

 

FROM tblExlTempYear,tblExlTempMonth

 

where tblExlTempMonth.GDXM=tblExlTempYear.GDXM
andtblExlTempMonth.TXDZ=tblExlTempYear.TXDZ

 

(1)、加索引:

 

tblExlTempYear(GDXM,TXDZ)

 

tblExlTempMonth (GDXM,TXDZ)

 

(2)、删除无用数据

 

(3)、转移过时数据

 

(4)、加服务器内存,升级服务器

 

(5)、升级网络系统

 

UPDATE tblExlTempYear

 

SET tblExlTempYear.GDQC = tblExlTempMonth.GDQC

 

FROM tblExlTempYear (index indexY),tblExlTempMonth (index indexM)

 

where tblExlTempMonth.GDXM=tblExlTempYear.GDXM
andtblExlTempMonth.TXDZ=tblExlTempYear.TXDZ

 

14 品种    日期    数量

 

P0001  2002-1-10 10

 

P0001  2002-1-10 11

 

P0001  2002-1-10 50

 

P0001  2002-1-12  9

 

P0001  2002-1-12  8

 

P0001  2002-1-12  7

 

P0002  2002-10-10 5

 

P0002  2002-10-10 7

 

P0002  2002-10-12 0.5

 

P0003  2002-10-10 5

 

P0003  2002-10-12 7

 

P0003  2002-10-12 9

 

结果要先按照品种汇总,再按照日期汇总,结果如下:

 

P0001  2002-1-10   71

 

P0001    2002-1-12 24

 

P0002    2002-10-10 12

 

P0002    2002-10-12 0.5

 

P0003    2002-10-10 5

 

P0003    2002-10-12 16

 

SQL SERVER能做出这样的汇总吗…

 

15 在分組查循中with{cube|rollup}的區別是什么?

 

如:

 

  use pangu

 

  select firm_id,p_id,sum(o_price_quantity)as sum_values

 

  from orders

 

  group by firm_id,p_id

 

  with cube

 

  與

 

  use pangu

 

  select firm_id,p_id,sum(o_price_quantity)as sum_values

 

  from orders

 

  group by firm_id,p_id

 

  with rollup

 

  的區別是什么?

 

CUBE 和 ROLLUP 之间的区别在于:

 

CUBE 生成的结果集显示了所选列中值的所有组合的聚合。

 

ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。

 

例如,简单表 Inventory 中包含:

 

Item               Color               Quantity                 

 

 

Table               Blue               124                       

 

Table               Red                 223                       

 

Chair               Blue               101                       

 

Chair               Red                 210 

 

下列查询将生成小计报表:

 

SELECT CASE WHEN (GROUPING(Item) = 1) THEN ‘ALL’

 

           ELSE ISNULL(Item, ‘UNKNOWN’)

 

      END AS Item,

 

      CASE WHEN (GROUPING(Color) = 1) THEN’ALL’

 

           ELSE ISNULL(Color, ‘UNKNOWN’)

 

      END AS Color,

 

      SUM(Quantity) AS QtySum

 

FROM Inventory

 

GROUP BY Item, Color WITH ROLLUP

 

Item               Color               QtySum                   

 

 

Chair               Blue               101.00                   

 

Chair               Red                 210.00                   

 

Chair               ALL                 311.00                   

 

Table               Blue               124.00                   

 

Table               Red                 223.00                   

 

Table               ALL                 347.00                   

 

ALL                 ALL                 658.00                   

 

(7 row(s) affected)

 

如果查询中的 ROLLUP 关键字更改为 CUBE,那么 CUBE
结果集与上述结果相同,只是在结果集的末尾还会返回下列两行:

 

ALL                 Blue               225.00                   

 

ALL                 Red                 433.00                   

 

CUBE 操作为 Item 和Color 中值的可能组合生成行。例如,CUBE 不仅报告与Item
值 Chair 相组合的 Color 值的所有可能组合(Red、Blue 和 Red
+Blue),而且报告与 Color 值 Red 相组合的 Item
值的所有可能组合(Chair、Table 和 Chair + Table)。对于 GROUP BY
子句中右边的列中的每个值,ROLLUP
操作并不报告左边一列(或左边各列)中值的所有可能组合。例如,ROLLUP
并不对每个 Color 值报告 Item 值的所有可能组合。ROLLUP
操作的结果集具有类似于 COMPUTE BY 所返回结果集的功能;然而,

 

ROLLUP 具有下列优点: ROLLUP 返回单个结果集;COMPUTE BY
返回多个结果集,而多个结果集会增加应用程序代码的复杂性。ROLLUP可以在服务器游标中使用;COMPUTE
BY 不可以。有时,查询优化器为 ROLLUP 生成的执行计划比为 COMPUTE BY
生成的更为高效。

 

16 假如我有两个表
表1(电话号码,是否存在)
表2(电话号码,是否拨打)
想查找表1中的电话号码是否在表2中存在,如果存在就更新表1中的是否存在字段为1。
    UPDATE 表1 SET 是否存在=1
WHERE EXISTS(SELECT * FROM 表2 WHERE 表2.电话号码 = 表1.电话号码)

 

17 用存储过程调用外部程序.
不过要做成com控件
用sp_OACreate存储过程)
DECLARE @object int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
EXEC @hr = sp_OACreate ‘SQLDMO.SQLServer’, @object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

 

(六)

 

1、在MS SQL Server中,用来显示数据库信息的系统存储过程是( )
A sp_ dbhelp
B sp_ db
C sp_ help
D sp_ helpdb

 

2、SQL语言中,删除一个表的命令是( )
A DELETE
B DROP
C CLEAR
D REMORE

 

3、关系数据库中,主键是(__)
A、为标识表中唯一的实体
B、创建唯一的索引,允许空值
C、只允许以表中第一字段建立
D、允许有多个主键的

 

4、在Transact-SQL语法中,SELECT语句的完整语法较复杂,但至少包括的部分(1___),使用关键字(2___)可以把重复行屏
蔽,将多个查询结果返回一个结果集合的运算符是(3___),如果在SELECT语句中使用聚合函数时,一定在后面使用(4___)。
    ⑴ A、SELECT,INTO             B、SELECT,FROM
      C、SELECT,GROUP           D、仅SELECT
  ⑵ A、DISTINCT                         B、UNION
        C、ALL                                 C、TOP
  ⑶ A、JOIN                               B、UNION
        C、INTO                             C、LIKE
  ⑷ A、GROUPBY                     B、COMPUTE BY
        C、HAVING                         D、COMPUTE

 

5、语句DBCC SHRINKDATABASE (Sample, 25)中的25表示的意思是
A、25M
B、剩余占整个空间的25%
C、已用空间占整个空间的25%
D、以上都不对

 

6、你是一个保险公司的数据库开发人员,公司的保单信息存储在SQL Server
2000数据库中,你使用以下脚本建立了一个名为Policy的表:
CREATE TABLE Policy
(
PolicyNumber int NOT NULL DEFAULT (0),
InsuredLastName char (30) NOT NULL,
InsuredFirstName char (20) NOT NULL,
InsuredBirthDate datetime NOT NULL,
PolicyDate datetime NOT NULL,
FaceAmount money NOT NULL,
CONSTRAINT PK_Policy PRIMARY KEY (PolicyNumber)
)
每次公司销售出一份保单,Policy表中就增加一条记录,并赋予其一个新的保单号,你将怎么做?

 

a.建立一个INSTEAD OF
INSERT触发器来产生一个新的保单号,并将这个保单号插入数据表中。
b.建立一个INSTEAD OF
UPDATE触发器来产生一个新的保单号,并将这个保单号插入数据表中。
c.建立一个AFTER
UPDATE触发器来产生一个新的保单号,并将这个保单号插入数据表中。
d.用AFTER
UPDATE触发器替代DEFAULT约束条件产生一个新的保单号,并将这个保单号插入数据表中。

 

7、在SQL语言中,如果要建立一个工资表包含职工号,姓名,职称。工资等字段。若要保证工资字段的取值不低于800元,最合适的实现方法是:
A。在创建工资表时为”工资“字段建立缺省
B。在创建工资表时为”工资“字段建立检查约束
C。在工资表建立一个触发器
D。为工资表数据输入编写一个程序进行控制

 

8、Select 语句中用来连接字符串的符号是______.
A. “+” B. “&” C.“||” D.“|”

 

9、你是一个出版公司的数据库开发人员,对特定的书名的每天的销售情况建立了如下的存储过程:
CREATE PROCEDURE get_sales_for_title
title varchar(80), @ytd_sales int OUTPUT
AS
SELECT @ytd_sales = ytd_sales
FROM titles
WHERE title = @title
IF @@ROWCOUNT = 0
RETURN(-1)
ELSE
RETURN(0)
另外建立了一个脚本执行这个存储过程,如果执行成功,将返回对应于书名的每天的销售情况的报表,如果执行失败,将返回“NoSales
Found”,怎样建立这个脚本?

 

A. DECLARE @retval int
DECLARE @ytd int
EXEC get_sales_for_title ‘Net Etiquette’, @ytd
IF @retval < 0
PRINT ‘No sales found’
ELSE
PRINT ‘Year to date sales: ’ + STR (@ytd)
GO

 

B. DECLARE @retval int
DECLARE @ytd int
EXEC get_sales_for_title ‘Net Etiquette’, @ytd OUTPUT
IF @retval < 0
PRINT ‘No sales found’
ELSE
PRINT ‘Year to date sales: ’ + STR (@ytd)
GO

 

C. DECLARE @retval int
DECLARE @ytd int
EXEC get_sales_for_title ‘Net Etiquette’,@retval OUTPUT
IF @retval < 0
PRINT ‘No sales found’
ELSE
PRINT ‘Year to date sales: ’ + STR (@ytd)
GO

 

D. DECLARE @retval int
DECLARE @ytd int
EXEC @retval = get_sales_for_title ‘Net Etiquette’, @ytd OUTPUT
IF @retval < 0
PRINT ‘No sales found’
ELSE
PRINT ‘Year to date sales: ’ + STR (@ytd)
GO

 

10、You are a database developer for a container manufacturing
company.The containers produced by your company are a number of
different sizes andshapes. The tables that store the container
information are shown in the Size,Container, and Shape Tables exhibit:
Size
SizeID
SizeName
Height
Container
ContainerID
ShapeID
SizeID
Shape
ShapeID
ShapeName
Measurements

 

A sample of the data stored in the tables is shown below:
Size Table
SizeID       SizeName        Height
1           Small          40
2           Medium          60
3           Large          80
4           Jumbo          100
Shape Table
ShapeID  ShapeName  Measurement
1       Triangle        10
2       Triangle        20
3       Triangle        30
4       Square          20
5       Square          30
6       Square          40
7       Circle          15
8       Circle          25
9       Circle          35
Periodically, the dimensions of the containers change. Frequently, the
databaseusers require the volume of a container. The volume of a
container iscalculated based on information in the shape and size
tables.
You need to hide the details of the calculation so that the volume can
beeasily accessed in a SELECT query with the rest of the container
information.What should you do?
A.    Create a user-defined function that requires ContainerIDas an
argument and returns the volume of the container.
B.    Create a stored procedure that requires ContainerID as anargument
and returns the volume of the container.
C.    Add a column named volume to the container table. Create atrigger
that calculates and stores volume in this column when a new containeris
inserted into the table.
D.    Add a computed column to the container table thatcalculates the
volume of the container.

 

填空题(1空1分共20分)
1、
如果设计的表不符合第二范式,可能会导致_______,________,_______。
2、
SQL是由_______语言,________语言,_______语言组成。
3、 SQL
Server在两个安全级上验证用户,分别是______________,_____________________。
4、
自定义函数由___________函数,_______________函数,___________________函数组成。
5、
备份策略的三种类型是__________备份,_______________备份,___________________备份组成。
6、
启动一个显式事务的语句为__________,提交事务的语句为__________,回滚事务的语句为__________
7、
表的每一行在表中是惟一的实体属于__________完整性,使列的输入有效属于__________完整性,两个表的主关键字和外关键字的数据应该对应一致属于__________完整性。
简答题(共20分)
1、 在帮助中[ ,…n ] 意思是什么?(4分)
2、 请简述一下第二范式(4分)
3、 现有1销售表,它们结构如下:(4分)
idint                     (标识号)
codnochar(7)             (商品编码)
codname varchar(30)        (商品名称)
specvarchar(20)                       (商品规格)
pricenumeric(10,2)           (价格)
sellnumint                         (销售数量)
deptnochar(3)                   (售出分店编码)
selldatedatetime                   (销售时间)
要求:写出查询销售时间段在2002-2-15日到2002-4-29之间,分店编码是01的所有记录。
4、写一个存储过程,要求传入一个表名,返回该表的记录数(假设传入的表在数据库中都存在)(4分)
5、请简述UPDATE 触发器如何工作原理。(4分)
简答题:(共40分)
1、(5分)使用一条SQL语句找到重复的值及重复的次数:有一数据表ZD_ks,其中有字段BM,MC,。。。,请查询出在ZD_ks中BM有重复的值及重复的次数,没有的不要列出。如下表:
BM DUPCOUNT
001 3
002 2

 

2、描述(5分)
表1 student 学生信息表
ID    int      学生编号
Name  varchar  学生姓名
Sex    bit      性别(男0女1)
Class int      班级编号

 

表2 schedule 课程信息表
ID    int      课程编号
Name  varchar  课程名称

 

表3 Grade  成绩信息表
ID    int      自动编号
UID    int      学生编号
SID    int      课程编号
Num    int      考试成绩

 

(a)求各班的总人数(1分)
(b)求1班女生和男生的平均成绩(2分)
(c)各班”数据结构”(课程名称)不及格的人数(2分)

 

3、问题描述:(30分)
本题用到下面三个关系表:
CARD    借书卡。  CNO 卡号,NAME 姓名,CLASS 班级
BOOKS    图书。    BNO 书号,BNAME 书名,AUTHOR 作者,PRICE单价,QUANTITY
库存册数
BORROW  借书记录。 CNO 借书卡号,BNO书号,RDATE 还书日期
备注:限定每人每种书只能借一本;库存册数随借书、还书而改变。
要求实现如下15个处理:
  1.找出借书超过5本的读者,输出借书卡号及所借图书册数。(2分)
2.查询借阅了”水浒”一书的读者,输出姓名及班级。(3分)
3.查询过期未还图书,输出借阅者(卡号)、书号及还书日期。(3分)
4.查询书名包括”网络”关键词的图书,输出书号、书名、作者。(2分)
5.查询现有图书中价格最高的图书,输出书名及作者。(2分)
6.查询当前借了”计算方法”但没有借”计算方法习题集”的读者,输出其借书卡号,并按卡号降序排序输出。(4分)
7.将”C01″班同学所借图书的还期都延长一周。(2分)
8.从BOOKS表中删除当前无人借阅的图书记录。(2分)
9.在BORROW表上建立一个触发器,完成如下功能:如果读者借阅的书名是”数据库技术及应用”,就将该读者的借阅记录保存在BORROW_SAVE表中(注ORROW_SAVE表结构同BORROW表)。(4分)
10.建立一个视图,显示”力01″班学生的借书信息(只要求显示姓名和书名)。(3分)
11.查询当前同时借有”计算方法”和”组合数学”两本书的读者,输出其借书卡号,并按卡号升序排序输出。(3分)

 

 

 

(七)

 

1、问题描述:
已知关系模式:
S (SNO,SNAME)                     学生关系。SNO 为学号,SNAME 为姓名
C (CNO,CNAME,CTEACHER)  课程关系。CNO 为课程号,CNAME 为课程名,CTEACHER
为任课教师
SC(SNO,CNO,SCGRADE)        选课关系。SCGRADE 为成绩
要求实现如下5个处理:
  1. 找出没有选修过“李明”老师讲授课程的所有学生姓名
  2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩
  3. 列出既学过“1”号课程,又学过“2”号课程的所有学生姓名
  4. 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号
  5.
列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩

 

2、问题描述:
为管理岗位业务培训信息,建立3个表:
S (S#,SN,SD,SA)  S#,SN,SD,SA
分别代表学号、学员姓名、所属单位、学员年龄
C (C#,CN )       C#,CN      分别代表课程编号、课程名称
SC ( S#,C#,G )    S#,C#,G   
分别代表学号、所选修的课程编号、学习成绩

 

要求实现如下5个处理:
  1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名
  2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位
  3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位
  4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位
  5. 查询选修了课程的学员人数
  6. 查询选修课程超过5门的学员学号和所属单位

 

 

 

(八)

 

Student(S#,Sname,Sage,Ssex) 学生表   
Course(C#,Cname,T#) 课程表   
SC(S#,C#,score) 成绩表   
Teacher(T#,Tname) 教师表   
问题:   
1、查询“001”课程比“002”课程成绩高的所有学生的学号;   
  select a.S# from (select s#,score from SC where C#=’001′)
a,(selects#,score   
  from SC where C#=’002′) b   
  where a.score>b.score and a.s#=b.s#;   
2、查询平均成绩大于60分的同学的学号和平均成绩;   
    select S#,avg(score)   
    from sc   
    group by S# having avg(score) >60;   
3、查询所有同学的学号、姓名、选课数、总成绩;   
  selectStudent.S#,Student.Sname,count(SC.C#),sum(score)   
  from Student left Outer join SC on Student.S#=SC.S#   
  group by Student.S#,Sname   
4、查询姓“李”的老师的个数;   
  select count(distinct(Tname))   
  from Teacher   
  where Tname like ‘李%’;   
5、查询没学过“叶平”老师课的同学的学号、姓名;   
    select Student.S#,Student.Sname   
    from Student    
    where S# not in (select distinct( SC.S#) fromSC,Course,Teacher
where  SC.C#=Course.C# and Teacher.T#=Course.T#
andTeacher.Tname=’叶平’);   
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;   
  select Student.S#,Student.Sname from Student,SC where
Student.S#=SC.S#and SC.C#=’001’and exists( Select * from SC as SC_2
where SC_2.S#=SC.S# andSC_2.C#=’002′);   
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;   
  select S#,Sname   
  from Student   
  where S# in (select S# from SC ,Course ,Teacher where
SC.C#=Course.C#and Teacher.T#=Course.T# and Teacher.Tname=’叶平’
groupby S# having count(SC.C#)=(select count(C#) from Course,Teacher 
whereTeacher.T#=Course.T# and Tname=’叶平’));   
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;   
  Select S#,Sname from (select Student.S#,Student.Sname,score
,(selectscore from SC SC_2 where SC_2.S#=Student.S# and
SC_2.C#=’002′)score2   
  from Student,SC where Student.S#=SC.S# and C#=’001′) S_2 where
score2<score;   
9、查询所有课程成绩小于60分的同学的学号、姓名;   
  select S#,Sname   
  from Student   
  where S# not in (select Student.S# from Student,SC where
S.S#=SC.S# andscore>60);   
10、查询没有学全所有课的同学的学号、姓名;   
    select Student.S#,Student.Sname   
    from Student,SC   
    where Student.S#=SC.S# group by Student.S#,Student.Sname having
count(C#) <(select count(C#) fromCourse);   
11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;   
    select S#,Sname from Student,SC where Student.S#=SC.S# andC# in
select C# from SC where S#=’1001′;   
12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名;   
    select distinct SC.S#,Sname   
    from Student,SC   
    where Student.S#=SC.S# and C# in (select C# from SC
whereS#=’001′);   
13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;   
    update SC set score=(selectavg(SC_2.score)   
    from SC SC_2   
    where SC_2.C#=SC.C# ) from Course,Teacher whereCourse.C#=SC.C#
and Course.T#=Teacher.T# and Teacher.Tname=’叶平’);   
14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名;   
    select S# from SC where C# in (select C# from SC
whereS#=’1002′)   
    group by S# having count(*)=(select count(*) from SC
whereS#=’1002′);   
15、删除学习“叶平”老师课的SC表记录;   
数据库,    Delect SC   
    from course ,Teacher    
    where Course.C#=SC.C# and Course.T#= Teacher.T# and
Tname=’叶平’;   
16、向SC表中插入一些记录,这些记录要求符合以下条件:没有上过编号“003”课程的同学学号、2、   
    号课的平均成绩;   
    Insert SC select S#,’002′,(Selectavg(score)   
    from SC where C#=’002′) from Student where S# not in (SelectS#
from SC where C#=’002′);   
17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩,按如下形式显示:
学生ID,,数据库,企业管理,英语,有效课程数,有效平均分   
    SELECT S# as 学生ID   
        ,(SELECT score FROM SC WHERESC.S#=t.S# AND C#=’004′) AS
数据库   
        ,(SELECT score FROM SC WHERESC.S#=t.S# AND C#=’001′) AS
企业管理   
        ,(SELECT score FROM SC WHERESC.S#=t.S# AND C#=’006′) AS
英语   
        ,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩   
    FROM SC AS t   
    GROUP BY S#   
    ORDER BY avg(t.score)    
18、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分   
    SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分   
    FROM SC L ,SC AS R   
    WHERE L.C# = R.C# and   
        L.score = (SELECTMAX(IL.score)   
                     FROM SC AS IL,Student AS IM   
                     WHERE L.C# = IL.C# and IM.S#=IL.S#   
                     GROUP BY IL.C#)   
        AND   
        R.Score = (SELECTMIN(IR.score)   
                     FROM SC AS IR   
                     WHERE R.C# = IR.C#   
                 GROUP BY IR.C#   
                   );   
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序   
    SELECT t.C# AS
课程号,max(course.Cname)AS课程名,isnull(AVG(score),0) AS 平均成绩   
        ,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0
END)/COUNT(*) AS 及格百分数   
    FROM SC T,Course   
    where t.C#=course.C#   
    GROUP BY t.C#   
    ORDER BY 100 * SUM(CASE WHEN  isnull(score,0)>=60THEN 1 ELSE 0
END)/COUNT(*) DESC   
20、查询如下课程平均成绩和及格率的百分数(用”1行”显示):
企业管理(001),马克思(002),OO&UML (003),数据库(004)   
    SELECT SUM(CASE WHEN C# =’001′ THEN score ELSE 0END)/SUM(CASE C#
WHEN ‘001’ THEN 1 ELSE 0 END) AS 企业管理平均分   
        ,100 * SUM(CASE WHEN C# = ‘001’ ANDscore >= 60 THEN 1 ELSE
0 END)/SUM(CASE WHEN C# = ‘001’ THEN 1 ELSE 0 END)AS
企业管理及格百分数   
        ,SUM(CASE WHEN C# = ‘002’ THEN scoreELSE 0 END)/SUM(CASE C#
WHEN ‘002’ THEN 1 ELSE 0 END) AS 马克思平均分   
        ,100 * SUM(CASE WHEN C# = ‘002’ ANDscore >= 60 THEN 1 ELSE
0 END)/SUM(CASE WHEN C# = ‘002’ THEN 1 ELSE 0 END)AS
马克思及格百分数   
        ,SUM(CASE WHEN C# = ‘003’ THEN scoreELSE 0 END)/SUM(CASE C#
WHEN ‘003’ THEN 1 ELSE 0 END) AS UML平均分   
        ,100 * SUM(CASE WHEN C# = ‘003’ ANDscore >= 60 THEN 1 ELSE
0 END)/SUM(CASE WHEN C# = ‘003’ THEN 1 ELSE 0 END)AS UML及格百分数   
        ,SUM(CASE WHEN C# = ‘004’ THEN scoreELSE 0 END)/SUM(CASE C#
WHEN ‘004’ THEN 1 ELSE 0 END) AS 数据库平均分   
        ,100 * SUM(CASE WHEN C# = ‘004’ ANDscore >= 60 THEN 1 ELSE
0 END)/SUM(CASE WHEN C# = ‘004’ THEN 1 ELSE 0 END)AS
数据库及格百分数   
  FROM SC   
21、查询不同老师所教不同课程平均分从高到低显示   
  SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS
课程ID,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均成绩   
    FROM SC AS T,Course AS C ,Teacher AS Z   
    where T.C#=C.C# and C.T#=Z.T#   
  GROUP BY C.C#   
  ORDER BY AVG(Score) DESC   
22、查询如下课程成绩第 3 名到第 6
名的学生成绩单:企业管理(001),马克思(002),UML
(003),数据库(004)   
    [学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩   
    SELECT  DISTINCT top 3   
      SC.S# As 学生学号,   
        Student.Sname AS 学生姓名 ,   
      T1.score AS 企业管理,   
      T2.score AS 马克思,   
      T3.score AS UML,   
      T4.score AS 数据库,   
      ISNULL(T1.score,0) + ISNULL(T2.score,0) +ISNULL(T3.score,0) +
ISNULL(T4.score,0) as 总分   
      FROM Student,SC  LEFT JOIN SC AST1   
                     ON SC.S# = T1.S# AND T1.C# = ‘001’   
            LEFT JOIN SCAS T2   
                     ON SC.S# = T2.S# AND T2.C# = ‘002’   
            LEFT JOIN SCAS T3   
                     ON SC.S# = T3.S# AND T3.C# = ‘003’   
            LEFT JOIN SCAS T4   
                     ON SC.S# = T4.S# AND T4.C# = ‘004’   
      WHERE student.S#=SC.S# and   
      ISNULL(T1.score,0) + ISNULL(T2.score,0) +ISNULL(T3.score,0) +
ISNULL(T4.score,0)   
      NOT IN   
      (SELECT   
           DISTINCT   
            TOP 15 WITHTIES   
           ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0)

  • ISNULL(T4.score,0)   
          FROM sc   
                LEFT JOIN scAS T1   
                         ON sc.S# = T1.S# AND T1.C# = ‘k1’   
                LEFT JOIN scAS T2   
                         ON sc.S# = T2.S# AND T2.C# = ‘k2’   
                LEFT JOIN scAS T3   
                         ON sc.S# = T3.S# AND T3.C# = ‘k3’   
                LEFT JOIN scAS T4   
                         ON sc.S# = T4.S# AND T4.C# = ‘k4’   
          ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0)+
    ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC);   
    23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[<60]   
        SELECT SC.C# as 课程ID, Cname as 课程名称   
            ,SUM(CASE WHEN score BETWEEN 85 AND100 THEN 1 ELSE 0 END) AS
    [100 – 85]   
            ,SUM(CASE WHEN score BETWEEN 70 AND85 THEN 1 ELSE 0 END) AS [85
  • 70]   
            ,SUM(CASE WHEN score BETWEEN 60 AND70 THEN 1 ELSE 0 END) AS [70
  • 60]   
            ,SUM(CASE WHEN score < 60 THEN 1ELSE 0 END) AS [60 -]   
        FROM SC,Course   
        where SC.C#=Course.C#   
        GROUP BY SC.C#,Cname;   
    24、查询学生平均成绩及其名次   
          SELECT 1+(SELECT COUNT( distinct 平均成绩)   
                 FROM (SELECT S#,AVG(score) AS 平均成绩   
                         FROM SC   
                     GROUP BY S#   
                     ) AS T1   
                WHERE 平均成绩 > T2.平均成绩) as 名次,   
          S# as 学生学号,平均成绩   
        FROM (SELECT S#,AVG(score) 平均成绩   
                FROMSC   
            GROUP BY S#   
            ) AS T2   
        ORDER BY 平均成绩desc;   
    25、查询各科成绩前三名的记录:(不考虑成绩并列情况)   
          SELECT t1.S# as 学生ID,t1.C#as 课程ID,Score as 分数   
          FROM SC t1   
          WHERE score IN (SELECT TOP 3score   
                 FROM SC   
                 WHERE t1.C#= C#   
                ORDER BYscore DESC   
                 )   
          ORDER BY t1.C#;   
    26、查询每门课程被选修的学生数   
      select c#,count(S#) from sc group by C#;   
    27、查询出只选修了一门课程的全部学生的学号和姓名   
      select SC.S#,Student.Sname,count(C#) AS 选课数   
      from SC ,Student   
      where SC.S#=Student.S# group by SC.S# ,Student.Sname
    havingcount(C#)=1;   
    28、查询男生、女生人数   
        Select count(Ssex) as 男生人数 fromStudent group by Ssex having
    Ssex=’男’;   
        Select count(Ssex) as 女生人数 fromStudent group by Ssex having
    Ssex=’女’;   
    29、查询姓“张”的学生名单   
        SELECT Sname FROM Student WHERE Sname like ‘张%’;   
    30、查询同名同性学生名单,并统计同名人数   
      select Sname,count(*) from Student group by Sname
    having count(*)>1;;   
    31、1981年出生的学生名单(注:Student表中Sage列的类型是datetime)   
        select Sname,  CONVERT(char (11),DATEPART(year,Sage))as age   
        from student   
        where CONVERT(char(11),DATEPART(year,Sage))=’1981′;   
    32、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列   
        Select C#,Avg(score) from SC group by C# order byAvg(score),C#
    DESC ;   
    33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩   
        select Sname,SC.S# ,avg(score)   
        from Student,SC   
        where Student.S#=SC.S# group by SC.S#,Snamehaving   
    avg(score)>85;   
    34、查询课程名称为“数据库”,且分数低于60的学生姓名和分数   
        Select Sname,isnull(score,0)   
        from Student,SC,Course   
        where SC.S#=Student.S# and SC.C#=Course.C#
    and Course.Cname=’数据库’and score <60;   
    35、查询所有学生的选课情况;   
        SELECT SC.S#,SC.C#,Sname,Cname   
        FROM SC,Student,Course   
        where SC.S#=Student.S# and SC.C#=Course.C#;   
    36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;   
        SELECT  distinctstudent.S#,student.Sname,SC.C#,SC.score   
        FROM student,Sc   
        WHERE SC.score>=70 ANDSC.S#=student.S#;   
    37、查询不及格的课程,并按课程号从大到小排列   
        select c# from sc where scor e <60 order by C#;   
    38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;   
        select SC.S#,Student.Sname from SC,Student whereSC.S#=Student.S#
    and Score>80 and C#=’003′;   
    39、求选了课程的学生人数   
        select count(*) from sc;   
    40、查询选修“叶平”老师所授课程的学生中,成绩最高的学生姓名及其成绩   
        select Student.Sname,score   
        from Student,SC,Course C,Teacher   
        where Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T#and
    Teacher.Tname=’叶平’ and SC.score=(selectmax(score)from SC where
    C#=C.C# );   
    41、查询各个课程及相应的选修人数   
        select count(*) from sc group by C#;   
    42、查询不同课程成绩相同的学生的学号、课程号、学生成绩   
      select distinct  A.S#,B.score from SC A  ,SC B whereA.Score=B.Score
    and A.C# <>B.C# ;   
    43、查询每门功成绩最好的前两名   
        SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数   
          FROM SC t1   
          WHERE score IN (SELECT TOP 2score   
                 FROM SC   
                 WHERE t1.C#= C#   
                ORDER BYscore DESC   
                 )   
          ORDER BY t1.C#;   
    44、统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,查询结果按人数降序排列,若人数相同,按课程号升序排列    
        select  C# as 课程号,count(*) as 人数   
        from  sc    
        group  by  C#   
        order  by  count(*)desc,c#    
    45、检索至少选修两门课程的学生学号   
        select  S#    
        from  sc    
        group  by  s#   
        having  count(*)  >  = 2   
    46、查询全部学生都选修的课程的课程号和课程名   
        select  C#,Cname    
        from  Course    
        where  C#  in  (select  c# from  sc group  by  c#)    
    47、查询没学过“叶平”老师讲授的任一门课程的学生姓名   
        select Sname from Student where S# not in (select S#
    fromCourse,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C#
    and Tname=’叶平’);   
    48、查询两门以上不及格课程的同学的学号及其平均成绩   
        select S#,avg(isnull(score,0)) from SC where S# in (selectS# from
    SC where score <60 group by S# having count(*)>2)group
    byS#;   
    49、检索“004”课程分数小于60,按分数降序排列的同学学号   
        select S# from SC where C#=’004’and score <60 order byscore
    desc;   
    50、删除“002”同学的“001”课程的成绩   
    delete from Sc where S#=’001’and C#=’001′;   
    问题描述:  
    本题用到下面三个关系表:  
    CARD     借书卡。   CNO 卡号,NAME  姓名,CLASS 班级  
    BOOKS    图书。     BNO书号,BNAME 书名,AUTHOR 作者,PRICE
    单价,QUANTITY 库存册数   
    BORROW   借书记录。 CNO 借书卡号,BNO 书号,RDATE 还书日期  
    备注:限定每人每种书只能借一本;库存册数随借书、还书而改变。  
    要求实现如下15个处理:  
      1.
    写出建立BORROW表的SQL语句,要求定义主码完整性约束和引用完整性约束。  
      2. 找出借书超过5本的读者,输出借书卡号及所借图书册数。  
      3. 查询借阅了”水浒”一书的读者,输出姓名及班级。  
      4. 查询过期未还图书,输出借阅者(卡号)、书号及还书日期。  
      5. 查询书名包括”网络”关键词的图书,输出书号、书名、作者。  
      6. 查询现有图书中价格最高的图书,输出书名及作者。  
      7.
    查询当前借了”计算方法”但没有借”计算方法习题集”的读者,输出其借书卡号,并按卡号降序排序输出。  
      8. 将”C01″班同学所借图书的还期都延长一周。  
      9. 从BOOKS表中删除当前无人借阅的图书记录。  
      10.如果经常按书名查询图书信息,请建立合适的索引。  
     
    11.在BORROW表上建立一个触发器,完成如下功能:如果读者借阅的书名是”数据库技术及应用”,就将该读者的借阅记录保存在BORROW_SAVE表中(注ORROW_SAVE表结构同BORROW表)。  
     
    12.建立一个视图,显示”力01″班学生的借书信息(只要求显示姓名和书名)。  
     
    13.查询当前同时借有”计算方法”和”组合数学”两本书的读者,输出其借书卡号,并按卡号升序排序输出。  
     
    14.假定在建BOOKS表时没有定义主码,写出为BOOKS表追加定义主码的语句。  
      15.对CARD表做如下修改:  
        a. 将NAME最大列宽增加到10个字符(假定原为6个字符)。  
        b. 为该表增加1列NAME(系名),可变长,最大20个字符。  
  1. 写出建立BORROW表的SQL语句,要求定义主码完整性约束和引用完整性约束  
    –实现代码:  
    CREATE TABLE BORROW(  
        CNO int FOREIGN KEY REFERENCES CARD(CNO),  
        BNO int FOREIGN KEY REFERENCES BOOKS(BNO),  
        RDATE datetime,  
        PRIMARY KEY(CNO,BNO))   
  2. 找出借书超过5本的读者,输出借书卡号及所借图书册数  
    –实现代码:  
    SELECT CNO,借图书册数=COUNT(*)  
    FROM BORROW  
    GROUP BY CNO  
    HAVING COUNT(*)>5 
  3. 查询借阅了”水浒”一书的读者,输出姓名及班级  
    –实现代码:  
    SELECT * FROM CARD c  
    WHERE EXISTS(  
        SELECT * FROM BORROW a,BOOKS b   
        WHERE a.BNO=b.BNO  
            AND b.BNAME=N’水浒’ 
            AND a.CNO=c.CNO)   
  4. 查询过期未还图书,输出借阅者(卡号)、书号及还书日期  
    –实现代码:  
    SELECT * FROM BORROW   
    WHERE RDATE<GETDATE()   
  5. 查询书名包括”网络”关键词的图书,输出书号、书名、作者  
    –实现代码:  
    SELECT BNO,BNAME,AUTHOR FROM BOOKS  
    WHERE BNAME LIKE N’%网络%’   
  6. 查询现有图书中价格最高的图书,输出书名及作者  
    –实现代码:  
    SELECT BNO,BNAME,AUTHOR FROM BOOKS  
    WHERE PRICE=(  
        SELECT MAX(PRICE) FROM BOOKS)   
    7.
    查询当前借了”计算方法”但没有借”计算方法习题集”的读者,输出其借书卡号,并按卡号降序排序输出  
    –实现代码:  
    SELECT a.CNO  
    FROM BORROW a,BOOKS b  
    WHERE a.BNO=b.BNO AND b.BNAME=N’计算方法’ 
        AND NOT EXISTS(  
            SELECT * FROM BORROW aa,BOOKSbb  
            WHERE aa.BNO=bb.BNO  
                ANDbb.BNAME=N’计算方法习题集’ 
                ANDaa.CNO=a.CNO)  
    ORDER BY a.CNO DESC   
  7. 将”C01″班同学所借图书的还期都延长一周  
    –实现代码:  
    UPDATE b SET RDATE=DATEADD(Day,7,b.RDATE)  
    FROM CARD a,BORROW b  
    WHERE a.CNO=b.CNO  
        AND a.CLASS=N’C01′   
  8. 从BOOKS表中删除当前无人借阅的图书记录  
    –实现代码:  
    DELETE A FROM BOOKS a  
    WHERE NOT EXISTS(  
        SELECT * FROM BORROW  
        WHERE BNO=a.BNO)   
  9. 如果经常按书名查询图书信息,请建立合适的索引  
    –实现代码:  
    CREATE CLUSTERED INDEX IDX_BOOKS_BNAME ON BOOKS(BNAME)  
    11.
    在BORROW表上建立一个触发器,完成如下功能:如果读者借阅的书名是”数据库技术及应用”,就将该读者的借阅记录保存在BORROW_SAVE表中(注ORROW_SAVE表结构同BORROW表)  
    –实现代码:  
    CREATE TRIGGER TR_SAVE ON BORROW  
    FOR INSERT,UPDATE  
    AS  
    IF @@ROWCOUNT>0 
    INSERT BORROW_SAVE SELECT i.*  
    FROM INSERTED i,BOOKS b  
    WHERE i.BNO=b.BNO  
        AND b.BNAME=N’数据库技术及应用’   
  10. 建立一个视图,显示”力01″班学生的借书信息(只要求显示姓名和书名)  
    –实现代码:  
    CREATE VIEW V_VIEW  
    AS  
    SELECT a.NAME,b.BNAME  
    FROM BORROW ab,CARD a,BOOKS b  
    WHERE ab.CNO=a.CNO  
        AND ab.BNO=b.BNO  
        AND a.CLASS=N’力01′ 
    13.
    查询当前同时借有”计算方法”和”组合数学”两本书的读者,输出其借书卡号,并按卡号升序排序输出  
    –实现代码:  
    SELECT a.CNO  
    FROM BORROW a,BOOKS b  
    WHERE a.BNO=b.BNO  
        AND b.BNAME IN(N’计算方法’,N’组合数学’)  
    GROUP BY a.CNO  
    HAVING COUNT(*)=2 
    ORDER BY a.CNO DESC   
  11. 假定在建BOOKS表时没有定义主码,写出为BOOKS表追加定义主码的语句  
    –实现代码:  
    ALTER TABLE BOOKS ADD PRIMARY KEY(BNO)   
    15.1 将NAME最大列宽增加到10个字符(假定原为6个字符)  
    –实现代码:  
    ALTER TABLE CARD ALTER COLUMN NAME varchar(10)   
    15.2 为该表增加1列NAME(系名),可变长,最大20个字符  
    –实现代码:  
    ALTER TABLE CARD ADD 系名 varchar(20)  
    问题描述:  
    为管理岗位业务培训信息,建立3个表:  
    S (S#,SN,SD,SA)   S#,SN,SD,SA
    分别代表学号、学员姓名、所属单位、学员年龄  
    C (C#,CN )       C#,CN       分别代表课程编号、课程名称  
    SC ( S#,C#,G )    S#,C#,G    
    分别代表学号、所选修的课程编号、学习成绩  
    要求实现如下5个处理:  
      1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名  
      2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位  
      3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位  
      4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位  
      5. 查询选修了课程的学员人数  
      6. 查询选修课程超过5门的学员学号和所属单位  
  12. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名   
    –实现代码:  
    SELECT SN,SD FROM S  
    WHERE [S#] IN(  
        SELECT [S#] FROM C,SC  
        WHERE C.[C#]=SC.[C#]  
            AND CN=N’税收基础’)  
  13. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位  
    –实现代码:  
    SELECT S.SN,S.SD FROM S,SC  
    WHERE S.[S#]=SC.[S#]  
        AND SC.[C#]=’C2′ 
  14. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位  
    –实现代码:  
    SELECT SN,SD FROM S  
    WHERE [S#] NOT IN(  
        SELECT [S#] FROM SC   
        WHERE [C#]=’C5′)  
  15. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位  
    –实现代码:  
    SELECT SN,SD FROM S  
    WHERE [S#] IN(  
        SELECT [S#] FROM SC   
            RIGHT JOIN C ONSC.[C#]=C.[C#]  
        GROUP BY [S#]  
        HAVING COUNT(*)=COUNT(DISTINCT [S#]))  
  16. 查询选修了课程的学员人数  
    –实现代码:  
    SELECT 学员人数=COUNT(DISTINCT [S#]) FROM SC  
  17. 查询选修课程超过5门的学员学号和所属单位  
    –实现代码:  
    SELECT SN,SD FROM S  
    WHERE [S#] IN(  
        SELECT [S#] FROM SC   
        GROUP BY [S#]  
        HAVING COUNT(DISTINCT [C#])>5)  
    if not object_id(‘cj’)is null   
       drop table cj  
    go  
    create table cj(stuName nvarchar(10),KCM
    nvarchar(10),cjnumeric(5,2))   
    insert into cj select ‘张三’,’语文’,98   
    union select ‘李四’,’语文’,89   
    union select ‘王五’,’语文’,67   
    union select ‘周攻’,’语文’,56   
    union select ‘张三’,’数学’,89 
    union select ‘李四’,’数学’,78   
    union select ‘王五’,’数学’,90   
    union select ‘周攻’,’数学’,87   
    方法一:  
    select stuname from  
        (select stuName,kcm,(select count(*) from cj
    wherestuname!=a.stuname and kcm=a.kcm and cj>a.cj) cnt from cj a)
    x  
        group by stuname having max(cnt)<=1 
    go  
    方法二:  
    SELECT stuname FROM cj1 a    
    where cj IN(SELECT TOP 2 cj FROM cj1 WHERE kcm=a.kcm ORDER BY cj
    desc)  
    GROUP BY stuname HAVING(count(1)>1)  
    方法三:  
    select distinct stuname from cj a  
        where not exists(select kcm from cj b wherea.stuname=stuname  
                   and (select count(*) from cj where kcm=b.kcm and
    stuname!=a.stuname andcj>b.cj)>1)