实验一

实验名称：SQL语句的应用（6课时）

一、实验目的

通过使用SQL SERVER企业管理器建立表。模式为人事表、客户表、销售表、销售明细表、产品表。理解数据库模式的概念，理解主键约束、外键约束、UNIQUE约束和CHECK约束。通过SQL SERVER企业管理器建立表间的约束。将得到的表生成脚本，保存。

二、实验环境

是MS SQL SERVER 2000的中文客户端。

三、实验示例

create table employee1(

emp_no char(5) not null,

constraint emp_nochk check(emp_no like '[E-F][0-9][0-9][0-9][0-9]'),

emp_name char(10) not null,

emp_sex char(1) not null,

constraint emp_sexchk check(emp_sex in ('m','f')),

emp_phone char (13) not null,

constraint emp_phonechk check(emp_phone like '([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),

emp_add varchar(60) not null,

emp_salary char(5) not null,

constraint emp_salarychk check (emp_salary between 60000 and 80000)

)

go

四、实验内容与步骤

1、建立五张表，每张表至少需要20条记录。

（1）/*员工人事表employee */

（1）为每张表建立主键约束。

（2）通过拖放操作加入外键。

（3）在表employee加入CHECK约束：输入的员工编号必须以E开头的5位数编号，性别只能为M/F。

（4）为销售主表sales中的编号字段建立UNIQUE约束。

3、通过快捷菜单得到脚本。

五、实验报告

实验二

实验名称：使用SELECT、UPDATE/INSERT/DELETE语句。（2课时）

一、实验目的

理解SELECT语句的操作和基本使用方法，熟练使用UPDATE/INSERT/DELETE语句进行表操作。

二、实验环境

三、实验示例

1、查找出职称为经理和职称为职员的女员工的姓名、职称、性别。

select emp_name,title,sex

from employee

where title='经理'or title='职员'and sex='F'

2、选取销售数量最多的前5条订单订单号、数量。

select top 5 with ties order_no,sup_id,qty

from sale_item

order by qty desc

3、计算出sale_item表中每一笔销售数据的销售金额，并按照销售金额的大小排序。

select prod_id,qty,unit_price,(qty*unit_price)tot_amt

from sale_item

order by tot_amt desc

四、实验内容与步骤

1、查找所有经理的姓名、职称、薪水。

   select emp_name,title,salary from employee where title='经理';  

2、查找出姓“王”并且姓名的最后一个字为“功”的员工。

   select * from employee where emp_name like '王_功'   

3、添加一条记录至employee表（用INSERT…..INTO）。

  insert into employee values('E1014','a14','m','办公','经理','2012-01-20','1999-12-23',60000,'北京');

4、将每个员工的薪水上调3%。

    update employee set salary =salary*(1+0.03);

5、查找住在上海或北京的女员工，并显示其姓名、所属部门、职称、住址。

   select emp_name,dept,title,addr from  employee where sex='f' and (addr='上海' or addr='北京');

6、在表sales中挑出销售金额大于等于10000元订单。

   select * from sales where tot_amt>=10000;

7、选取订单金额最高的前10%的订单数据。

    select top 10 percent  * from sales order by  tot_amt desc;

8、查找出职称为经理或职称为职员的女员工的信息。

   select * from employee where title='经理' or title='职员' and sex='f';

9、删除sales表中作废的订单（其号码为I000000004），其订货明细数据也一并删除。

   delete from sales where invoice_no='I000000004';

    sales 表和sale_item 要建立关联，删除原则设为“层叠”

10、计算出一共销售了几种产品。

    select  count(distinct prod_id ) from sale_item  ;

11、显示sale_item表中每种个别产品的订购销售金额总和，并且依据销售金额由大到小排列来显示出每一种产品的排行榜。

   select  prod_id,sum(qty*unit_price) as 销售金额from sale_item group by prod_id order by sum(qty*unit_price) desc ;

12、计算每一产品每月的销售金额总和，并将结果按销售（月份，产品编号）排序。

    select  month(order_date) as 月份,prod_id,sum(qty*unit_price) as 销售金额from sale_item group by  prod_id ,month(order_date);

五、实验报告

实验三

实验名称：表连接JOIN（2课时）

一、实验目的

理解JOIN语句的操作和基本使用方法，掌握内连接、外连接、自身连接的概念和使用。

二、实验环境

三、实验示例

1、检索product 表和sale_item表中数量大于2的相同产品的产品编号、产品名称、数量、单价。

select a.prod_id,a.qty,a.unit_price,b.prod_name

from sale_item as a inner join product as b   /*如果改成left join/right join 试分析结果*/

on (a.prod_id=b.pro_id) and a.qty>2

order by a.prod_id

2、查找出employee表中住址相同的员工的姓名、性别、职称、薪水、住址。

select a.emp_name,a.sex,a.title,a.salary,a.addr,b.emp_name,b.sex,b.title,b.salary,b.addr

from employee as a inner join employee as b

on (a.emp_no!=b.emp_no) and (a.emp_name>b.emp_name) and (a.addr=b.addr)

四、实验内容与步骤

1、查找出employee表中部门相同且住址相同的女员工的姓名、性别、职称、薪水、住址。

  select a.emp_name,a.sex,a.title,a.dept,a.salary,a.addr,b.emp_name,b.sex,b.title,b.dept,b.salary,b.addr

from employee as a  join employee as b

on (a.emp_no!=b.emp_no) and (a.emp_name>b.emp_name) and (a.dept=b.dept) and (a.addr=b.addr) and a.sex = 'm'  and b.sex = 'm';  

2、检索product 表和sale_item表中相同产品的产品编号、产品名称、数量、单价。

   select a.prod_id,a.prod_name,b.qty,b.unit_price from product as a join sale_item as b on a.prod_id=b.prod_id order by prod_id ;

3、检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。

select a.prod_id,a.prod_name,b.qty,b.unit_price from product as a join sale_item as b on a.prod_id=b.prod_id and b.unit_price>2400 ;

4、分别使用左向外连接、右向外连接、完整外部连接检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。并分析比较检索的结果。  

select a.prod_id,a.prod_name,b.qty,b.unit_price from product as a left join sale_item as b on a.prod_id=b.prod_id and b.unit_price>2400 ;

   inner join   left join  right join  full join 

五、实验报告

实验四

实验名称：SELECT的嵌套使用，实现复杂查询（2课时）

一、实验目的

掌握SELECT语句的嵌套使用，实现表的复杂查询，进一步理解SELECT语句的高级使用方法。

二、实验环境

三、实验示例

1、由employee表中查找出薪水最高的员工信息。

select *

from employee

where salary=

       (select max(salary )

        from employee )

2、由sales表中查找出订单金额大于“E0107业务员在2012-1-2这天所接每一张订单的金额”的所有订单。

select * from sales where tot_amt >all

       (select tot_amt 

        from sales 

        where sale_id='E0107' and order_date='2012-01-02')

order by tot_amt  

3、利用begin...end语句实现数据查询，查询出employee表中所有女职工的平均工资和

　住址在＂上海市＂的所有女职工的平均工资

begin

select avg(salary)

from employee

where sex like 'f'

union

select avg(salary)

from employee

where sex like 'f' and addr like '上海'

end

四、实验内容与步骤

1、由sales表中查找出销售金额最高的订单。

select * from sales

where tot_amt=

       (select max(tot_amt )

        from sales )

2、由sales表中查找出订单金额大于“E0107业务员在2012-1-2这天所接任一张订单的金额”的所有订单，并显示承接这些订单的业务员和该条订单的金额。

 select sale_id,tot_amt from sales where tot_amt >all

       (select tot_amt 

        from sales 

        where sale_id='E0107' and order_date='2012-01-02')

order by tot_amt  

3、找出公司女业务员所接的订单。

 select *

from sales

where sale_id in

       (select emp_no 

        from employee where sex='f')

select * from sales a join employee b on a.sale_id=b.emp_no and b.sex='f' order by sale_id

select a.order_no,a.cust_id,a.sale_id,a.tot_amt from sales a,employee b where a.sale_id=b.emp_no and b.sex='f'

4、找出公司中姓名相同的员工，并且依据员工编号排序相识这些员工信息。

select * from employee where emp_name in

(select emp_name  from employee  group by emp_name having count(emp_name)>1

)order by emp_name,emp_no;

select * from employee as a where  exists  

(select *  from employee b where a.emp_no!=b.emp_no and a.emp_name=b.emp_name )

order by emp_name,emp_no

5、找出目前业绩未超过200000元的员工。

   select * from employee where emp_no in 

(select sale_id from sales where tot_amt<50000)

6、计算公司内各个部门的工资支出总和。

  select dept,sum(salary) from employee group by dept

7、计算每一产品销售数量总和与平均销售单价。

    select  prod_id,sum(qty),avg(unit_price)from sale_item group by prod_id

五、实验报告

实验五

实验名称：视图的建立与使用（2课时）

一、实验目的

掌握视图的定义与工作原理。

二、实验环境

三、实验示例

1、

create view emp_view as

select emp_no,emp_name,salary

from employee

where sex='f' 

2、

create view sale_item_view as

select sales.order_no,sales.order_date,prod_id,qty

from sales,sale_item

where sales.order_no=sale_item.order_no

/*注意：插入数据时要考虑数据列是否有默认值或允许为空，否则INSERT失败。*/

insert into sale_item_view(order_no,order_date)

values(10010,'1996/12/20')

insert into sale_item_view(prod_id,qty)

values('p0010',8)

四、实验内容与步骤

1、创建一个视图，该视图只含上海客户信息，即客户号、客户姓名、住址。

2、对视图添加一条记录数据。（注意：分别查看customer表和该视图的结果。）

3、删除视图中所有姓“王”的客户数据。

4、通过视图修改表内某一客户的姓名。

5、有两个基本表employee和sales，创建一个视图，该视图包含相同业务员的编号、姓名、订单号、销售总金额。

6、将上述视图中订单号为10001的记录的销售总金额改为60000。

7、给上述视图添加一条记录数据。

8、删除上述视图。

五、实验报告

实验六

实验名称：存储过程建立与调用（含带参存储过程的建立与调用）（2课时）

一、实验目的

理解存储过程的概念、建立和调用方法。

二、实验环境

三、实验示例

1、模糊查询

create procedure  sp_empname @E_name varchar(10) as

select a.emp_name,a.dept,b.tot_amt

from employee a inner join sales b

on a.emp_no=b.sale_id 

where a.emp_name like @E_name

go

exec sp_empname '陈%'

2、利用存储过程计算出’E0014’业务员的销售总金额。

create procedure  sp_saletot @E_no char(5),@p_tot int output as

select @p_tot=sum(tot_amt)

from sales

where sale_id=@E_no 

go

declare @tot_amt int

exec sp_saletot E0014, @tot_amt output

select @tot_amt

四、实验内容与步骤

1、利用存储过程，给employee表添加一条业务部门员工的信息。

2、利用存储过程从employee、sales、customer表的连接中返回所有业务员的姓名、客户姓名、销售金额。

3、利用存储过程查找“”的员工编号、订单编号、销售金额。

4、利用存储过程查找姓“李”并且职称为“职员”的员工的员工编号、订单编号、销售金额。

5、利用存储过程计算出订单编号为10003的订单的销售金额。

五、实验报告

实验七

实验名称：触发器的建立与使用（2课时）

一、实验目的

理解触发器的概念、定义方法和触发条件。

二、实验环境

采用Client/Server模式，学生为客户端，是MS SQL SERVER 2000的中文客户端。登录用户名是：学号；密码为：****** 。用户名和密码以任课老师给出为准。

三、实验示例

(一)After类型触发器

1.创建AFTER-INSERT触发器：在sale_item表上创建dml触发器inssale_item_trg,当向sale_item插入一条记录时，--检查prod_id 是否在product中，order是否在sales表中，只要一项不存在，则给出错误提示，并撤销插入操作。

USE company

GO

CREATE TRIGGER inssale_item_trg

ON sale_item

FOR INSERT

AS    /* 这触发器定义要执行的操作*/

IF EXISTS(SELECT * FROM sale_item

WHERE (sale_item.prod_id NOT IN(SELECT prod_id FROM product)

  OR sale_item.order_no NOT IN(SELECT order_no FROM sales)))

BEGIN

  RAISERROR('插入的数据违背了数据的一致性',16,1) /* 错误提示*/

  ROLLBACK TRANSACTION    /* 事务回滚*/

END;

GO

*/

insert into sale_item(order_no,prod_id) values('24','21')

2.创建AFTER-DELETE触发器：在表sales上创建触发器delsales_trg 当从sales表中删除一条记录时，检查sale_item中是否存在被删记录的order_no,如果存在，则从sale_item表中删除该记录。

/*

USE company

GO

CREATE TRIGGER delsales_trg

ON sales

FOR DELETE

AS    /* 为触发器定义要执行的操作*/

DELETE sale_item WHERE sale_item.order_no IN

(SELECT order_no FROM deleted)

GO

*/

delete from sales where order_no='24'

写一个允许用户一次只删除一条记录的触发器。

/*

create trigger tr_emp

on employee for delete as

declare @row_cnt int

select @row_cnt=count(*) from deleted

if @row_cnt>1

  begin 

   print '此删除操作可能回删除多条人事表数据!!!'

   rollback transaction

  end

*/

delete from employee

where sex='m'          /*结果：所影响的行数为*/

3 创建AFTER-UPDATE触发器，在product表上创建触发器updproduct_trg,当对表product的记录进行修改时，触发器将给出修改时间信息。

USE company

GO

CREATE TRIGGER updproduct_trg

ON product

FOR UPDATE

AS    /* 为触发器定义要执行的操作 */

PRINT  '修改的时间为:'+CONVERT(char, getdate(),110)  /* 显示修改的时间信息 */

GO

update product set prod_name='sql' where prod_id='20'

（二） Instead of类型触发器

1替代触发器能够使无法更新的视图支持更新功能。基于多表的视图必须使用Instead of触发器来支持基表数据的增、删、改操作。

建立视图：

Create View View_product_saleitem as

SELECT  dbo.product.prod_id, dbo.product.prod_name, dbo.sale_item.order_no, dbo.sale_item.qty, dbo.sale_item.unit_price, dbo.sale_item.order_date

FROM       dbo.product INNER JOIN

            dbo.sale_item ON dbo.product.prod_id = dbo.sale_item.prod_id

USE company

GO

CREATE TRIGGER InsViewprod_saleitem_Trg

ON View_prod_saleitem

INSTEAD OF INSERT

AS

BEGIN   /* 定义触发器的行为*/

DECLARE @prod_id char(5),@prod_name char(20)  /* 定义局部变量*/

DECLARE @order_no int,@qty int,@unit_price numeric(7,2) ,@order_date datetime

/* 从逻辑表inserted中选择相应的数据存放到变量中*/

SELECT @prod_id=prod_id,@prod_name=prod_name,@order_no=order_no,@qty=qty,@unit_price=unit_price,@order_date=order_date

FROM inserted

/* 将相应变量值插入到两个基表中*/

INSERT INTO product

(prod_id,prod_name)

VALUES(@prod_id,@prod_name)

INSERT INTO sale_item

(order_no,qty,unit_price,order_date)

VALUES (@order_no,@qty,@unit_price,@order_date)

END;

GO

*/

insert into View_prod_saleitem values('26','javaee',50,100,23.5,'2012-12-25')

实验八 事务及加锁

脏读，丢失更新，不可重复读，幻像度

1．脏读：指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。

2．丢失更新:当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。每个事务都不知道其它事务的存在。最后的更新将重写由其它事务所做的更新，这将导致数据丢失。

3．不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

4．幻象读：指当事务不是执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

1.3    锁的分类

按数据库引擎使用的资源锁模式。

从程序员的角度，分为悲观锁和乐观锁

乐观锁：完全依靠数据库来管理锁的工作。

悲观锁：程序员自己管理数据或对象上的锁处理。

从粒度来分：

表类型：锁定整个表

行类型：锁定某个行

文件类型：锁定某个数据库文件

数据库类型：锁定整个数据库

页类型：锁定8K为单位的数据库页

事务4大特性性：

1   原子性(Atomicity):事务是一个完整的操作。

2   一致性（Consistency）：当事务完成时，数据必须处于一致状态。

3   隔离性(Isolation):对数据进行修改的所有并发事务是彼此隔离的。

4   持久性(Durability):事务完成后，它对于系统的影响是永久性的。

一、程序中的事务的类型

1）显式事务

  显示事务是手工配置的事务。用保留字标识显式事务的开始和结束。保留字包括 BEGIN TRANSACTION、COMMIT TRANSACTION、ROLLBACK TRANSACTION和SAVE TRANSACTION，其中TRANSACTION可以简写为TRAN。

  要开始显式事务，首先输入关键词BEGIN TRAN。

  要结束显示事务，使用COMMIT TRAN。

  如果要取消事务，使用ROLL BACK TRAN命令，ROLLBACK TRAN将完全取消事务，或将事务恢复到初始状态。

  2）隐式事务

   有时候看起来没有使用事务，但它们可能隐藏在幕后，这种事务叫做隐式事务。

   要使用这种模式，必须使用Set Implicit_Transactions On语句来设置连接。SQLServer的任何数据修改语句，都是隐式事务。下列的任何一条语句都可以作为一个隐式事务的开始：ALTER TABLE、CREATE、 DELETE、DROP、FETCH、GRANT、INSERT、OPEN、REVOKE、SELECT、TRUNCATE TABLE、UPDATE。

   要结束隐式事务，必须使用COMMIT TRANSACTION或ROLLBACK TRANSACTION语句。之后，任何指令的命令都将会开始另一个事务。

二、实验准备

设计一个用于实验的简单表：

设table1(A,B,C)   

  A         B         C   

  a1       b1       c1   

  a2       b2       c2   

  a3       b3       c3   

事务实验

  select * from table1

begin tran

update table1  set a='ab'

select * from table1

if not exists(select * from table1 where b='b5')

   begin 

     print '找不到该记录，回滚事务'

     rollback tran

   end

else

  begin 

     print '找到记录，提交事务'

     commit tran

   end

select * from table1

go

三、验证脏读

事务1

select *from table1

begin tran

update table1  set a='a'

select * from table1

waitfor delay '00:00:10'

rollback tran

select * from table1

go

事务二

set transaction isolation level read uncommitted

print '脏读'

select * from table1

if @@rowcount>0

begin

waitfor delay '00:00:10'

print '不重复读'

select * from table1

end

go

验证：将隔离级别设为set transaction isolation level read  committed看看，脏数据不会读出

四、不可重复读

事务1

set transaction isolation level read committed

begin tran

print '初始'

select *from table1

waitfor delay'00:00:10'

print '不可重复读'

select * from table1

rollback tran

事务2：

set transaction isolation level read committed

update table1 set c='c'

验证：将隔离级别设为 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ看看，这个时候，此连接将锁住不能执行，一直等到A连接结束为止。而且A连接中两次读取到的数据相同，不受B连接干扰。

注，对于Read Committed和Read UnCommitted情况下，B连接不会锁住，等到A连接执行完以后，两条查询语句结果不同。

五、序列化读：SERIALIZABLE

事务1、在A连接中执行。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN t

UPDATE table1 SET a='a'

WAITFOR delay '00:00:20'

Select * from table1

COMMIT TRAN t

事务2. 在B连接中执行，并且要在A执行后的20秒内。

BEGIN TRAN tt

INSERT INTO table1 values('a4','b4','c4')

Select * from table1

COMMIT TRAN tt

在A连接的事务提交之前，B连接无法插入数据到表中，这就避免了幻觉读。

 注：幻觉读是指当事务不是执行时发生的一种现象，例如 第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好像发生了幻觉一样。

五、设置隔离级别

SET TRANSACTION ISOLATION LEVEL

    { READ COMMITTED

        | READ UNCOMMITTED

        | REPEATABLE READ

        | SERIALIZABLE 

    }

若要确定当前设置的事务隔离级别，请使用 DBCC USEROPTIONS 语句

提交读（SQL Server 默认级别）。

SQL Server 2005 还支持使用行版本控制的两个事务隔离级别。一个是已提交读隔离的新实现，另一个是新事务隔离级别（快照）。

将 READ_COMMITED_SNAPSHOT 数据库选项设置为 ON 时，已提交读隔离使用行版本控制提供语句级别的读取一致性。读取操作只需要 SCH-S 表级别的锁，不需要页锁或行锁。将 READ_COMMITED_SNAPSHOT 数据库选项设置为 OFF（默认设置）时，已提交读隔离的行为与在 SQL Server 的早期版本中相同。两个实现都满足已提交读隔离的 ANSI 定义。

快照隔离级别使用行版本控制来提供事务级别的读取一致性。读取操作不获取页锁或行锁，只获取 SCH-S 表锁。读取其它事务修改的行时，读取操作将检索启动事务时存在的行的版本。将 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON 时，将启用快照隔离。默认情况下，用户数据库的此选项设置为 OFF。

锁定提示对SQL语句进行特别指定，这个指定将覆盖事务的隔离级别。事务隔离级别是并发控制的整体解决方案，其实际上是综合利用各种类型的锁和行版本控制，来解决并发问题。

对用户来说，只有当事务隔离级别无法解决一些并发问题和需求时，才有必要在语句中手动设置锁。不适当的设置锁，可能会导致严重的阻塞和死锁。建议，只有在完全了解锁机制的情况下，才可以在语句中手动设置锁，否则应该使用事务隔离级别。

 1）排它锁   

  新建两个连接   

  在第一个连接中执行以下语句   

  begin   tran   

        update   table1   

        set   A='aa'   

        where   B='b2'   

        waitfor   delay   '00:00:30'     --等待30秒   

  commit   tran   

  在第二个连接中执行以下语句   

  begin   tran   

        select   *   from   table1   

        where   B='b2'         

  commit   tran   

      若同时执行上述两个语句，则select查询必须等待update执行完毕才能执行即要等待30秒   

      2）共享锁   

  在第一个连接中执行以下语句   

  begin   tran   

        select   *   from   table1   holdlock   -holdlock人为加锁   

        where   B='b2'     

        waitfor   delay   '00:00:30'     --等待30秒   

  commit   tran   

      在第二个连接中执行以下语句   

  begin   tran   

        select   A,C   from   table1   

        where   B='b2'     

        update   table1   

        set   A='aa'   

        where   B='b2'         

  commit   tran   

     若同时执行上述两个语句，则第二个连接中的select查询可以执行   

  而update必须等待第一个连接中的共享锁结束后才能执行   即要等待30秒   

    3）死锁   

  增设table2(D,E)   

  D         E   

  d1       e1   

  d2       e2   

  在第一个连接中执行以下语句   

  begin   tran   

        update   table1   

        set   A='aa'   

        where   B='b2'     

        waitfor     delay   '00:00:30'   

        update   table2   

        set   D='d5'   

        where   E='e1'     

  commit   tran   

   在第二个连接中执行以下语句   

  begin   tran   

        update   table2   

        set   D='d5'   

        where   E='e1'     

        waitfor     delay   '00:00:10'   

        update   table1   

        set   A='aa'   

        where   B='b2'       

  commit   tran   

      同时执行，系统会检测出死锁，并中止进程

检测数据库中是否存在锁

select spid,blocked,status,waittime,waittype,waitresource,db_name(dbid),cmd,hostname,loginame

from master.dbo.sysprocesses

where blocked<>0

示例2

数据库说明：

这是一个大学入学注册管理系统，包含五张表

classes开课

id唯一标识学生，major是专业，current_credits是该学生已修的学分数

classes表描述课程信息

department是开课系，course是课程号，department和course唯一决定该课程

max_students是该课程能容纳的学生数，current_students是当前选修的学生数

num_credits是该课程的学分数，room_id是所在的教室位置（和rooms表相关联）

rooms教室信息

room_id唯一标识教室，building是所在的教学楼，room_number是房间号

number_seats是教室的座位数

registered_students 学生课程的成绩

student_id是学生号（和students表关联），department和course描述具体课程

grade是该门课的成绩

1．查询特定学生所有课程的成绩（比如first_name是david,last_name是dinsmore）

2．查询所有学生所有课程的成绩

3．查询特定专业的所有学生名字（比如history专业）

4．列出所有专业的学生名字

5．查询cs系开的课所在教室的位置信息

6．查询所有系开的课所在教室的位置信息

7．统计music专业的学生总数和学分数

8．统计所有专业的学生总数和学分数

9．在students表中新加一个学生

10.该学生选了一门新课（比如his系开的301课），请改变相应的表

11.该学生修完新课，成绩为B，请改变相应表

12 .一个学生毕业，请删除该学生所有相应信息

13找出所有姓中以S.开头的学生。

14找出每个专业的学生人数、已得最高学分、最低学分、平均学分、学分总数。

15找出所有教室的座位数，最大教室的座位数、最小座位数。

16找出各大楼的最大教室，最小教室，平均座位数、座位总数。

17找出各课程尚可选修的人数。

18找出开课最多的系。

19找出开课最少的系。

20找出选课最多的学生。

21找出选课最少的学生。

22找出不及格的学生。

23找出各课程平均分以下的学生。

24找出各系所占教室的座位数。

25分别创建一个学生、选修课程的历史表，要求按时间存储学生、选修课程信息。将当前学生、选修信息全部存储到该历史表。当前学生毕业，将当前学生删除。

26找出选修了HIS系开的所有课程的学生。

参：

1查询特定学生所有课程的成绩（比如first_name是david,last_name是dinsmore）

select first_name last_name Grade from Registered_students and Students 

where first_name=david and last_name=dinsmore and students.ID=Regjested_students.Student_id.

2查询所有学生所有课程的成绩

select  ID course Grade from Registered_students and Students where students.ID=Regjested_students.Student_id.

3查询特定专业的所有学生名字（比如history专业）

select  Firstname Lastname from students where major=”history”

4列出所有专业的学生名字

select major Firstname lastname from students group by major (order by major asc?)

5查询cs系开的课所在教室的位置信息

select course roomid  building roomnumber from classes and rooms 

where classes.roomid =rooms.roomid  and department=”cs”加引号

6查询所有系开的课所在教室的位置信息

select department course building roomnumber from classes and roomes

where classes.roomid=rooms.roomid  group by department 

7统计music专业的学生总数和学分数

select  totalstudent tatalcredits from major_stats where major=”music” 

8统计所有专业的学生总数和学分数

select* from major_stats group by major

9在students表中新加一个学生

insert into students (id, firstname, lastname, major, currentcredits ) values(“4445”,”m”,”ml”,”cs”,”133”)

10该学生选了一门新课（比如his系开的301课），请改变相应的表

insert into registered_students(studentid, department,course) values(“4445”,”his”,”301”)

update classes set currentstudents+=1 where department=”his” and course=”301”

11该学生修完新课，成绩为B，请改变相应表

Update registeredstudents set grade=”B” where studentid=”4445”

Update students set currentcredits+=2 where studentid=”4445”

update classes set current_students=current_students-1 

where department="HIS" and course="301"

12一个学生毕业，请删除该学生所有相应信息

   delete from students

where id=1205

delete from regidtered_students

where student_id=1205

update major_stats

set total_students=total_students-1,total_credits=total_credits-12

where major_stats.major="Computer Science"

13找出所有姓中以S.开头的学生。

Select  * 

From students

Where Last_name like “S%”

14找出每个专业的学生人数、已得最高学分、最低学分、平均学分、学分总数。

  Select major,count(distinct id),max(current_credits),min(current_credits),avg(current_credits),sum(current_credits)

from students

group by major

15找出所有教室的座位数，最大教室的座位数、最小座位数。

Select (Number_seats_Seats,Max(Number_Seats),Min(Number_Seats)

     From Rooms

16找出各大楼的最大教室，最小教室，平均座位数、座位总数。

找出各大楼的最大教室，

select building,room_number as biggest_room

from rooms as R

where number_seats>=all(select number_seats

from rooms as T

where R.building=T.building)

最小教室，

select building,room_number as biggest_room

from rooms as R

where number_seats<=all(select number_seats

from rooms as T

where R.building=T.building)

平均座位数、座位总数。

select avg(number_seats),sum(number_seats)

from rooms

group by building

17找出各课程尚可选修的人数。

  select course,max_students-current_students as remain

from classes

18找出开课最多的系。

select department

from classes

group by department

having count(course)>=all(select count(course)

from classes

group by department)

19找出开课最少的系。

select department

from classes

group by department

having count(course)<=all(select count(course)

from classes

group by department)

20找出选课最多的学生。

select student_id,count(course)

from registered_students

group by student_id

having count(course)>=all(select count(course)

from registered_students

group by student_id)

21找出选课最少的学生。

select student_id,count(course)

from registered_students

group by student_id

having count(course)<=all(select count(course)

from registered_students

group by student_id)

22找出不及格的学生。

select distinct id,first_name,last_name

from registered_students,students

where id=student_id and grade="E"

23找出各课程平均分以下的学生。

select student_id,course,grade

from registered_students as R

where grade<=(select avg(grade)

from registered_students as G

where R.course=G.course)

24找出各系所占教室的座位数。

select department,sum(number_seats)

from classes,rooms

where classes.room_id=rooms.room_id

25分别创建一个学生、选修课程的历史表，要求按时间存储学生、选修课程信息。将当前学生、选修信息全部存储到该历史表。当前学生毕业，将当前学生删除。

creat table history_table

(student_id integer,

first_name char(20),

last_name char(20),

department char(3),

course integer,

register_time date,

primary key (student_id,course),

check (student_id in (select id from students)))

将当前学生、选修信息全部存储到该历史表。

select id,first_name,last_name,department,course,2002-5-1

from students,registered_students as R

where students.id=R.studnet_id and T.student_id=students.id and T.first_name=students.first_name and T.last_naem=students.last_name and T.department=R.department and T.course=R.course

当前学生毕业，将当前学生删除。

delete from history_table

where register_time=2002-5-1

26找出选修了HIS系开的所有课程的学生。

select id,first_name,last_name

from students as S

where not exists ((select course

     from classes

     where department="HIS")

    except

     (select R.course

     from registered_students as R

where R.student_id=S.id))

或者

Select student_id

From registered_students

Where department=”HIS”

Group by student_id

Having count(student_id)=

     （select count(distinct course)

       from registered_students

      where department=”HIS”

       )下载本文