SQL题：sql语句查询(二)

 

第一题：

if object_id('tempdb.dbo.#') is not null drop table #

create table # (studentname varchar(5),classname varchar(7),grade int)

 

insert into #

select 'mike','english',65 union all

select 'mike','math',75 union all

select 'Jerry','english',80 union all

select 'Jerry','math',68 union all

select 'Lida','english',88 union all

select 'Lida','chinese',77 union all

select 'Lida','math',90

 

select * from #

题名是这样的：
studentname    classname      grade  
 mike           english          65
 mike           math             75
 Jerry          english          80
 Jerry          math             68
 Lida           english          88
 Lida           chinese          77
 Lida           math             90
求出各科成绩都大于 75 分的学生。

即得到如下结果
studentname    classname      grade
 Lida             english          88
 Lida             chinese          77
 Lida             math             90

 

select t1.studentname,classname,grade

from # t1,(select studentname

           from #

           where grade>75

           group by studentname

           having count(*)=(select count(*) from (select distinct classname from #)A)) t2

where t1.studentname=t2.studentname

 

注：

1 、 from (select distinct classname from #)A  中的 "A" 一定要有，将 ( select distinct classname from #) 得到的记录集看成一个新的表 A 。

2 、在 SQL 语句中，用 group by 语句时，一般都会用到 count (), sum (), avg (), max (), min () 等函数；

但，当不使用 group by 语句，而直接使用这些函数时，则表示将所有数据集看成一个组来处理。

 

第二题：

已知一个关系数据库的模式如下：
职工 EMP （职工号，姓名，工资，所在部门）
部门 DEPT （部门号，部门名，部门经理的职工号）
（ 1 ）、使用查询分析器建表，要求如下：
　　 A 、每个表的主外码。
　　 B 、职工姓名和部门名不能为空。
　　 C 、工资取 800 到 5000 这间整数。
（ 2 ）、插入如下数据：
职工（ E01  赵  2500    D02      

E02  钱  3000    D02      

E03  孙  6000    D01      

E04  李  5500    D01      

E05  周  3500    D03      

E06  吴  2000    D04      

E07  郑  3000    D04   ）
部门（ D01 ，人事， E04
　　　 D02 ，信息， E01
　　　 D03 ，销售， E05
　　　 D04 ，财务， E06 ）
（ 3 ）、用 SQL 表达以下的查询：找出那些工资高于其所在部门经理工资的职工的姓名及其工资。

select *

from emp A,(select emp. 姓名 , emp. 工资 , dept.*

            from emp,dept

            where emp. 职工号 = dept. 部门经理的职工号 ) B

where A. 所在部门 = B. 部门号 and A. 工资 > B. 工资

（ 4 ）、用 SQL 完成：将 D01 号部门经理的工资改为该部门所有职工的平均工资。

update emp

set 工资 =( select avg( 工资 ) from emp where 所在部门 = 'D01' )

from emp,dept

where emp. 职工号 = dept. 部门经理的职工号 AND 所在部门 = 'D01'

（5）、新增加一个广告部门，编号为D05。 

insert into dept values('D05','广告','E08')

小结：不要急于答题，先审好题看清表结构，再下笔！

   在（ 3 ）题中，职工号（ E01.. ）是主键，是标识字段。要先确定标识字段，再按照标识字段去理思路，用（ E01.. ）将两个表相连即可得到部门表中各经理的姓名、工资等情况数据集 B ，此数据集的记录数 = 部门 (dept) 表的记录数。然后再将数据集 B 与职工 (emp) 表相连，即可得到“出那些工资高于其所在部门经理工资的职工的姓名及其工资”。