关于group by 的一个SQL题目

获得部门最高工资比别的部门平均工资还低的部门及平均公司信息。

 

表如下： employee table

+----+--------+--------+------+
| id | name   | salary | dept |
+----+--------+--------+------+
|  1 | test1  |     99 | dep1 |
|  2 | test2  |     89 | dep1 |
|  3 | test3  |     73 | dep1 |
|  4 | test4  |     87 | dep1 |
|  5 | test5  |     85 | dep1 |
|  6 | test6  |     84 | dep2 |
|  7 | test7  |     82 | dep2 |
|  8 | test8  |     80 | dep2 |
|  9 | test9  |     86 | dep2 |
| 10 | test10 |     79 | dep2 |
| 11 | test11 |     83 | dep3 |
| 12 | test12 |     80 | dep3 |
+----+--------+--------+------+
12 rows in set (0.00 sec)

 

有两个sql解决方案：

1，select a.*,b.* from
(select dept,max(salary) maxsalary from test group by dept) a,
(select dept,avg(salary) avgsalary from test group by dept) b
where a.maxsalary<b.avgsalary

2，select dept, max(salary) from employee group by dept having max(salary) in (select max(salary) from employee group by dept having max(salary) < some (select avg(salary) from employee group by dept))

 

 

 

第二题， 写一个SQL语句用来检查某个字段中是否存在重复的值,并输出重复的值和次数。

 

select dept, count(dept) from test.employee group by dept

 

第三题 横表变竖表

 

第四题，oracle如何更新一个表的sequence

 

alter seq increment by 1000;

select seq.nextval from seq;

alter seq increment by 1;

 

 

第五题， 如果更新一个表中的时间字段

update employee set start_date = SYSDATE - 5 where id = 1234;

or update employee set  start_date = to_date('2003-06-13 15:18','YYYY-MM-DD hh:mm');