SQL语句（笔试）

转帖地址：http://caihong19870228.blog.163.com/blog/static/123806238201052410394457/

http://blog.sina.com.cn/s/blog_627b25430100np11.html

http://hi.baidu.com/guan2ye/blog/item/040d03d293296e073af3cfb8.html

 

 

1，

查询各科成绩在80分以上的学生：表结构如下

name kc_name kc_score
张三 语文 80
李四 语文 70
钟情 语文 75
张三 数学 85
李四 数学 88
钟情 数学 79
张三 英语 80
李四 英语 55

【select name from stu group by name having  min(kc_score)>=80】

【select distinct name from stu where name not in (select distinct name from stu where kc_score<=80)】

 

2，

riqi  riliang                                riqi    riliang    leiliang

1     1                                       1       1           1

2     3                                       2       3           4

3     5                      ====>      3       5           9

4     7                                       4       7           16

5     9                                       5       9           25

 

【select riqi,riliang,(select sum(riliang) from table t2 where t2.riliang<=t1.riliang) leiliang from table t1】

 

3，

怎么把这样一个表儿
year  month amount
1991   1     1.1
1991   2     1.2
1991   3     1.3
1991   4     1.4
1992   1     2.1
1992   2     2.2
1992   3     2.3
1992   4     2.4
查成这样一个结果
year m1  m2  m3  m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4

 

 

【select year,
(select amount from  aaa m where month=1  and m.year=aaa.year) as m1,
(select amount from  aaa m where month=2  and m.year=aaa.year) as m2,
(select amount from  aaa m where month=3  and m.year=aaa.year) as m3,
(select amount from  aaa m where month=4  and m.year=aaa.year) as m4
from aaa 

group by year 】

 

 

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

 

查询数据库前十个数据

 

oracle:
select * from (select * from student order by id desc) where rownum <= 10 order by rownum asc

mssql:
select top 10 * from student order by id desc

mysql:
select * from student order by id desc limit 0,10 

 

 

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

 

 

1）复制表(只复制结构,源表名：a 新表名：b)

select * into b from a where 1<>1

 

2）拷贝表(拷贝数据,源表名：a 目标表名：b)

insert into b(a, b, c) select d,e,f from b;

 

3）显示文章、提交人和最后回复时间

select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

 

4）外连接查询(表名1：a(a,b,c) 表名2：b(c,d,f))

select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

 

5）两张关联表，删除主表A中已经在副表B中没有的信息

delete from A where not exists ( select * from B where A.id=B.id )

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

 

 

 

--1列出emp表中各部门的部门号，最高工资，最低工资
select deptno as 部门号,max(sal) as 最高工资,min(sal) as 最低工资 from
emp group by deptno;

 

--2 列出emp表中各部门job为'CLERK'的员工的最低工资，最高工资
select max(sal) as 最高工资,min(sal) as 最低工资,deptno as 部门号 from emp where
job='clerk' group by deptno;

 

--3 对于emp中最低工资小于2000的部门，列出job为'CLERK'的员工的部门号，最低工资，最高工资
select b.deptno as 部门号,max(sal) as 最高工资,min(sal) as 最低工资 from emp as b
where job='clerk' and (select min(sal)from emp as a where a.deptno=b.deptno)<2000 group by
b.deptno;

 

--4 根据部门号由高而低，工资有低而高列出每个员工的姓名，部门号，工资
select ename as 姓名,deptno as 部门号,sal as 工资 from emp order by deptno desc,sal asc;

 

--5 列出'buddy'所在部门中每个员工的姓名与部门号
select b.ename as 姓名,b.deptno as 部门号 from emp as b where b.deptno=
(select a.deptno from emp as a where a.ename='buddy');

 

--6 列出每个员工的姓名，工作，部门号，部门名
select ename as 姓名,job as 工作,dept.deptno as 部门号,dept.dname as 部门名 from emp,dept
where emp.deptno=dept.deptno;

 

--7列出emp中工作为'CLERK'的员工的姓名，工作，部门号，部门名
select ename as 姓名,job as 工作,dept.deptno as 部门号,dept.dname as 部门名 from emp,dept
where emp.deptno=dept.deptno and job='clerk';

 

--8对于emp中有管理者的员工，列出姓名，管理者姓名（管理者外键为mgr）
select a.deptno as 部门号,a.ename as 员工,b.ename as 管理者 from emp as a,emp as b where a.mgr is not null and a.mgr=b.ename;

 

--9 对于dept表中，列出所有部门名，部门号，同时列出各部门工作为'CLERK'的员工名与工作
select a.deptno as 部门号,a.dname as 部门名,b.ename as 员工名,b.job as 工作 from dept as a,
emp as b where a.deptno=b.deptno and b.job='clerk';

 

--10 对于工资高于本部门平均水平的员工，列出部门号，姓名，工资，按部门号排序
select b.deptno as 部门号,b.ename as 姓名,b.sal as 工资 from emp as b
where b.sal>(select avg(a.sal) from emp as a where a.deptno=b.deptno) order by b.deptno;

 

--11对于emp，列出各个部门中工资高于本部门平均工资的员工数和部门号，按部门号排序
select a.deptno as 部门号,count(a.sal) as 员工数 from emp as a
where a.sal>(select avg(b.sal) from emp as b where a.deptno=b.deptno) group by a.deptno order
by a.deptno;

 

--12对于emp中工资高于本部门平均水平，人数多与1人的，列出部门号，人数，平均工资，按部门号排序
select count(a.empno) as 员工数,a.deptno as 部门号,avg(sal) as 平均工资
from emp as a where (select count(c.empno) from emp as c where c.deptno=a.deptno and
c.sal>(select avg(sal) from emp as b where c.deptno=b.deptno))>1
group by a.deptno order by a.deptno;

 

--13对于emp中低于自己工资至少5人的员工，列出其部门号，姓名，工资，以及工资少于自己的人数
select a.deptno as 部门号,a.ename as 姓名,a.sal as 工资,(select count(b.ename) from emp as b
where b.sal<a.sal) as 人数 from emp as a
where (select count(b.ename) from emp as b where b.sal<a.sal)>=5