SQL查询语句

1、（1）查询person表中所有不重复的职称

use pay115

select distinct professor

from person

 

（2）查询person中男员工的信息

use pay115

select *

from person

where sex = '男'

 

（3）查询person表中00102部门女员工的信息

use pay115

select *

from person

where sex = '女' and deptno = '00102'

 

 

（4）查询000001员工的基本工资增长1.5倍后的实际收入

use pay115

select base*1.5+bonus-deduct as real_fact

from pay

where no = '000001'

 

（5）查询00102部门的员工的基本信息和工资情况，按实发工资降序排列

use pay115

select *

from person left outer join pay on (person.No = pay.no)

where person.DeptNo = '00102'

order by fact desc

 

（6）查询各部门2005年实发工资总数

use pay115

select deptno as 部门,sum(fact) as 总额

from pay,person

where pay.no=person.no and year=2005

group by deptno

 

（7）查询2005年1月平均奖金不少于400的部门，按平均奖金升序排列

use pay115

select dept.deptno,deptname ,avg(bonus) as 平均奖金

from dept,person,pay

where year = 2005 and month = 1 and dept.deptno = person.DeptNo and pay.No = person.No

group by dept.deptno,deptname

having avg(bonus)>=400

order by avg(bonus) asc

 

（8）查询市场部所有员工姓名和2005年1月工资明细

use pay115

select name,base,bonus,deduct,fact

from person,pay,dept

where person.no = pay.no and year = 2005 and month = 1 and dept.deptname = '市场部' and dept.DeptNo = person.DeptNo

 

2、利用SQL语句嵌套查询

（1）查询2005年1月比000001员工实发工资高的所有员工的基本信息

use pay115

select *

from person

where no in

(

select no from pay

where year = 2005 and month = 1 and 

fact>(select fact from pay

where year = 2005 and month = 1 and no = '000001')

)

 

 

（2）查询2005年1月比00102部门的所有员工实发工资都高的员工信息

use pay115

select *

from person

where no in

(

select no from pay

where year = 2005 and month = 1 and 

fact>(select max(fact) 

from pay,person

where year = 2005 and month = 1 and deptno = '00102' and person.no = pay.no)

)

 

（3）查询2005年1月比所有员工平均实发工资都高的员工基本信息和工资明细

use pay115

select *

from person,pay

where person.no = pay.no and pay.no in

(

select no from pay

where year = 2005 and month = 1 and 

fact>(select avg(fact) 

from pay

where year = 2005 and month = 1)

)

 

3、利用SQL语句创建视图

（1）在基表person基础上创建员工视图view_person，其中包括工号，姓名、性别、职称、部门代码字段

create view dbo.view_person(No,Name,Sex,professor,deptno)

as

select no,name,sex,professor,deptno

from dbo.person

 

（2）

create view dbo.view_pay(year,month,no,name,sex,professor,deptname,base,bonus,deduct,fact)

as

select year,month,person.no,name,sex,professor,deptname,base,bonus,deduct,fact

from dbo.person,dbo.pay,dbo.dept

where dept.DeptNo = person.DeptNo and person.no = pay.no

 

 

 

三、SQL查询语言实验：

补充题：必做题4题，另外书上例题自选2题：

1、查询平均成绩大于60分的课程的课程号和平均成绩。

use school 

select cno,avg(grade) from sc group by cno having avg(grade)>60

 

 

2、查询没有选修“数据库”课程的学生的学号和姓名。

use school

select sno,sname

from student

where sno not in

(select sno

from sc,course

where cname = '数据库' and course.cno = sc.cno)

 

 

3、查询选修人数超过2人的课程的课程号（如果加课程名称，可以实现吗?）和学生人数。查询结果按人数降序排列，若人数相同，按课程号升序排列。

use school

select cno,count(sno)

from sc

group by cno

having count(sno)>2           

order by count(sno) desc,cno asc

 

加课程名：

use school

select sc.cno,count(sno),cname

from sc,course

where course.cno = sc.cno

group by sc.cno,cname

having count(sno)>1          

order by count(sno) desc,cno asc

 

4、创建视图view_sc,查询有学生选修的课程的课程号，课程名，学分和选修人数。

create view view_sc(cno,cname,ccredit, 选课人数)

as 

select course.cno,cname,ccredit,count(sno)

from course,sc

where course.cno = sc.cno

group by course.cno,cname,ccredit

having count(sno)>0

 

例3.22 查询计算机科学系全体学生的名单

select sname

from student

where sdept = 'cs'