SQL语句训练2

1.分页语句：取出sql表中第31到40的记录（以自动增长ID为主键）。
select top 10 * from t where id not in (select top 30 id from t order by id ) orde by id;
2.根据部门号从高到低，工资从低到高列出每个员工的信息。
select * from employee order by deptid desc,salary;
3.已知如下两个表，完成后面的问题:
Create Table Department (
dept_id varchar(2) not null, – 部门编号
dept_name varchar(20) not null, – 部门名称
dept_leader varchar(10) – 部门经理 );

Create Table Personnel (
id varchar(4) not null, --员工号
name varchar(10) not null, --姓名
dept_id varchar(2) not null, --部门编号
age integer, --年龄
gzsj date, --参加工作时间
technical_post varchar(10), --职称
salary integer – 薪水 );
1)写出表 Department 增加一条记录和更新一条记录的 SQL 语句，增加记录值 (‘ 12’ , ‘ 研发部 ’ , ‘ 张三 ’ ) ;更新 dept_id=’ 12’ 的记录 (‘ 12’ , ‘ 研发部 ’ , ‘ 张三新 ’ ) ;
insert into Department values(‘ 12’ , ‘ 研发部 ’ , ‘ 张三 ’);
update Department set dept_leader=’张三新’ where dept_id=’ 12’;
2)查找工资大于 2000元的员工记录,并按员工号 id 升序排列
select * from Personnel where salary>2000 order by id;
3)查找工资大于 2000元的员工所在部门、部门编号、部门经理、员工名称
select d.dept_name 所在部门,d.dept_id 部门编号, d.dept_leader 部门经理,p.name 员工名称 from Personnel p inner join Department d on p.dept_id=d.dept_id where p.salary>200;
4)查找张三和李四所在部门所有人员的姓名
select name from Personnel where dept_id in(select dept_id from Personnel where name =’张三’ or name=’李四’);
5)查看每个部门的部门经理和部门人数,按部门人数排序?
select d.dept_leader,count(p.id) from Department d left outer join Personnel p on p.dept_id=d.dept_id group by d.dept_leader order by count(p.id);
6)删除表 研发部 的所有员工记录
delete from Personnel where dept_id in(select dept_id from Department where dept_name=’研发部’);
7)编写存储过程proc_addSalary，给指定部门的员工涨薪
create procedure proc_addSalary(@deptId int,@sal int ) as
update Personnel set salary=salary+@sal where dept_id=@deptId
Go
8)编写函数fun_getAveSalary，获取指定部门的平均薪水
create function fun_getAveSalary(@deptId int) returns int
As
begin
declare @avgSal int
select @avgSal=avg(salary) from Personnel where dept_id=@deptId
return @avgSal
end
9) 编写视图view_avgSalByDept，查询每个部门的平均薪资及员工人数
create view view_avgSalByDept
As
select d.dept_name 部门,count(p.id) 员工人数, avg(p.salary) 平均薪资 from personnal p inner join department d on p.dept_id=p.dept_id
10) 编写触发器trig_delDept，实现删除指定部门时，该部门的所有员工也将随之删除
create trigger trig_delDept on department for delete
As
delete from personnal where dept_id in (select dept_id from deleted)

4.有如下四个表，根据要求完成相应的题目。
Student表和Course表结构及数据如下：

Score表结构及数据如下：

Teacher表结构及数据如下：

题目：
1)查询Student表中的所有记录的Sname、Ssex和Class列。
select Sname,Ssex,Class from student;
2)查询Score表中成绩在60到80之间的所有记录。
select * from Score where DEGREE between 60 and 80;
或select * from Score where DEGREE>=60 and degree<=80;
3)查询和“李军”同性别的所有同学的Sname。
select sname from Student where SSEX=(select SSEX from student where sname=‘李军’)
4)查询最低分大于70，最高分小于90的Sno列。
select sno from Score group by SNO having MIN(DEGREE)>70 and MAX(DEGREE)<90;
5)以Cno升序、Degree降序查询Score表的所有记录。
Select * from Score order by Cno,degree desc
6)查询“95031”班的学生人数。
select COUNT() from Student where CLASS=‘95031’;
7)查询Score表中的最高分的学生学号和课程号。
select sno,cno,degree from Score where DEGREE=(select MAX(DEGREE) from Score);
8)查询‘3-105’号课程的平均分。
select AVG(DEGREE) from Score where CNO=‘3-105’;
9)查询“男”教师及其所上的课程。
select t.tno,t.tname,c.cno,c.cname from teacher t left join course c on t.tno=c.tno where t.tsex=”男”;
10)查询所有未讲课的教师的Tname和Depart。
select tname,depart from teacher where tno not in(select tno from course)
11)查询选修某课程的同学人数多于5人的教师姓名
Select tname from teacher where tno in(select tno from course where cno in(select cno from score group by cno having count(sno)>5))
12)查询教师所有的单位即不重复的Depart列。
Select distinct depart from teacher;
13)查询课程名称为“操作系统”，且分数低于60的学生姓名和分数
Select stuName,isnull(score,0) from Student,Scores,Course
where Scores.stuId=Student.stuId and Scores.courseId=Course.courseId and Course.courseName=‘数据库’ and score <60;
14)检索"01"课程分数小于60，按分数降序排列的学生信息
select student.,score.* from student,score where student.s_id=score.s_id
and c_id=‘01’ and s_score<60 order by s_score desc