部门信息:dept
+------+-----------+------------+
|deptno| deptname |deptlocation|
+------+-----------+------------+
| 1 |designing | workin121 |
+------+-----------+------------+
| 2 | coding | workin123 |
+------+-----------+------------+
| 3 | testing | workin125 |
+------+-----------+------------+
| 4 | manager | workin127 |
+------+-----------+------------+
| 5 |temp | nolocation |
+------+-----------+------------+
员工信息:emp
+-----+-------+---------+--------+----------+----------+
|empno|deptno | empname | empjob | empsalary|empmanager|
+-----+-------+---------+--------+----------+----------+
| 1 | 2 | zhang | code | 3000 | 5 |
+-----+-------+---------+--------+----------+----------+
| 2 | 2 | wang | manager| 4000 | 1 |
+-----+-------+---------+--------+----------+----------+
| 3 | 1 | li | code | 3600 | 1 |
+-----+-------+---------+--------+----------+----------+
| 4 | 5 | zhao | test | 2556 | 2 |
+-----+-------+---------+--------+----------+----------+
| 5 | 1 | wu | code | 6500 | 15 |
+-----+-------+---------+--------+----------+----------+
| 6 | 5 | guo | code | 5653 | 3 |
+-----+-------+---------+--------+----------+----------+
| 7 | 1 | wang | study | 1565 | 4 |
+-----+-------+---------+--------+----------+----------+
| 8 | 1 | sun | code | 9999.99 | 15 |
+-----+-------+---------+--------+----------+----------+
| 9 | 2 | feng | study | 1999 | 12 |
+-----+-------+---------+--------+----------+----------+
| 10 | 1 | ma | code | 2343 | 3 |
+-----+-------+---------+--------+----------+----------+
| 11 | 3 | tu | code | 9999.99 | 16 |
+-----+-------+---------+--------+----------+----------+
| 12 | 1 | xin | code | 7900 | 6 |
+-----+-------+---------+--------+----------+----------+
| 13 | 1 | si | code | 4546 | 2 |
+-----+-------+---------+--------+----------+----------+
| 14 | 1 | fu | test | 2464 | 15 |
+-----+-------+---------+--------+----------+----------+
| 15 | 3 | hao | manager| 9999.99 | NULL |
+-----+-------+---------+--------+----------+----------+
| 16 | 4 | liu | test | 3521 | 15 |
+-----+-------+---------+--------+----------+----------+
SQL代码:
+----------------------------------------------------------------+
drop table if exists dept;
drop table if exists emp;
create table dept
(
deptno int not null,
deptname char(20),
deptlocation char(50),
primary key (deptno)
);
create table emp
(
empno int not null,
deptno int,
empname char(20),
empjob char(20),
empsalary float,
empmanager int,
primary key (empno)
);
alter table emp add constraint FK_deptincludeemp foreign key (deptno)
references dept (deptno) on delete restrict on update restrict;
INSERT INTO `dept` VALUES (1,'designing','workin121');
INSERT INTO `dept` VALUES (2,'coding','workin123');
INSERT INTO `dept` VALUES (3,'testing','workin125');
INSERT INTO `dept` VALUES (4,'manager','workin127');
INSERT INTO `dept` VALUES (5,'temp','nolocation');
INSERT INTO `emp` VALUES (1,2,'zhang','code',3000,15);
INSERT INTO `emp` VALUES (2,2,'wang','manager',4000,1);
INSERT INTO `emp` VALUES (3,1,'li','code',3600,1);
INSERT INTO `emp` VALUES (4,5,'zhao','test',2556,2);
INSERT INTO `emp` VALUES (5,1,'wu','code',6500,15);
INSERT INTO `emp` VALUES (6,5,'guo','code',5653,3);
INSERT INTO `emp` VALUES (7,1,'wang','study',1565,4);
INSERT INTO `emp` VALUES (8,1,'sun','code',9999.99,15);
INSERT INTO `emp` VALUES (9,2,'feng','study',1999,12);
INSERT INTO `emp` VALUES (10,1,'ma','code',2343,3);
INSERT INTO `emp` VALUES (11,3,'tu','code',9999.99,16);
INSERT INTO `emp` VALUES (12,1,'xin','code',7900,6);
INSERT INTO `emp` VALUES (13,1,'si','code',4546,2);
INSERT INTO `emp` VALUES (14,1,'fu','test',2464,15);
INSERT INTO `emp` VALUES (15,3,'hao','manager',9999.99,NULL);
INSERT INTO `emp` VALUES (16,4,'liu','test',3521,15);
+----------------------------------------------------------+
问题:
1.列出emp表中各部门的部门号, 部门,最高工资,最低工资
+-------------------------------------------------------------+
|SELECT b.deptno, a.deptname, min( b.empsalary ) AS minsalary,|
|max( b.empsalary ) AS maxsalary |
|FROM emp b, dept a |
|WHERE b.deptno = a.deptno |
|GROUP BY b.deptno |
+-------------------------------------------------------------+
2.列出emp表中各部门job为'code'的员工的最低工资,最高工资
+--------------------------------------------------------------------+
|select a.deptname,min(b.empsalary)as minsal,max(b.empsalary) as maxs|
|from emp b,dept a |
|where empjob='code' and a.deptno=b.deptno |
|group by b.deptno; |
+--------------------------------------------------------------------+
3.对于emp中最低工资小于3000的部门,列出job为'code'的员工的部门号,最低工资,最高工资
+-------------------------------------------------------------------+
|select a.deptno,min(a.empsalary)as minsal,max(a.empsalary)as maxsal|
|from emp a,( |
| select deptno,min(empsalary) as minsal |
| from emp |
| group by deptno |
| having minsal<3000) b |
|where a.deptno=b.deptno and a.empjob='code' |
|group by a.deptno; |
+-------------------------------------------------------------------+
4.根据部门号由高而低,工资由低而高列出每个员工的姓名,部门号,工资
+--------------------------------------+
|select empname, deptno ,empsalary |
|from emp |
|group by deptno desc,empsalary asc; |
+--------------------------------------+
5.列出'zhang'所在部门中每个员工的姓名与部门号
+--------------------------------------------------------------+
|select empname,deptno from emp |
|where deptno = (select deptno from emp where empname='zhang');|
+--------------------------------------------------------------+
6.列出每个员工的姓名,工作,部门号,部门名
+----------------------------------------------+
|select b.empname,b.empjob,b.deptno,a.deptname |
|from dept a,emp b |
|where a.deptno=b.deptno; |
+----------------------------------------------+
7.列出emp中工作为'code'的员工的姓名,工作,部门号,部门名
+----------------------------------------------+
|select b.empname,b.empjob,b.deptno,a.deptname |
|from dept a,emp b |
|where b.empjob='code' and a.deptno=b.deptno |
+----------------------------------------------+
8.对于emp中有管理者的员工,列出姓名,管理者姓名(管理者外键为mgr)
+---------------------------------------------------------+
|select a.empname as emp,b.empname as manager |
|from emp a,emp b |
|where a.empmanager is not NULL and a.empmanager=b.empno; |
+---------------------------------------------------------+
9.对于dept表中,列出所有部门名,部门号,同时列出各部门工作为'code'的员工名与工作
+---------------------------------------------------------+
|SELECT dept.deptno,dept.deptname,emp.empname,emp.empjob |
|FROM dept, emp |
|WHERE dept.deptno = emp.deptno AND emp.empjob = 'code'; |
+---------------------------------------------------------+
10.对于工资高于本部门平均水平的员工,列出部门号,姓名,工资,按部门号排序
+-----------------------------------------------------+
|SELECT a.deptno, empname, empsalary |
|FROM emp a,( |
| SELECT deptno, avg( empsalary ) AS avgsal |
| FROM emp |
| GROUP BY deptno)b |
|WHERE a.deptno = b.deptno AND a.empsalary > b.avgsal |
|ORDER BY a.deptno |
+-----------------------------------------------------+
11.对于emp,列出各个部门中平均工资高于本部门平均水平的员工数和部门号,按部门号排序
+-----------------------------------------------------+
|SELECT COUNT(a.empno) AS allcount, a.deptno |
|FROM emp AS a,( |
| SELECT c.deptno AS deptno, AVG(c.empsalary)AS avgsal|
| FROM emp AS c |
| GROUP BY c.deptno |
| ) AS b |
|WHERE a.empsalary > b.avgsal AND a.deptno = b.deptno |
|GROUP BY a.deptno ORDER BY a.deptno; |
+-----------------------------------------------------+
12.对于emp中工资高于本部门平均水平,人数多于1人的,列出部门号,人数,按部门号排序
+------------------------------------------------------+
|SELECT a.deptno,count(a.deptno) AS nums FROM emp a,( |
| SELECT avg(empsalary) AS avgsal,deptno |
| FROM emp |
| GROUP BY deptno |
| )b |
|WHERE a.deptno = b.deptno |
|AND a.empsalary > b.avgsal |
|GROUP BY deptno |
|HAVING nums >1 |
|ORDER BY a.deptno; |
+------------------------------------------------------+
13.对于emp中低于自己工资至少5人的员工,列出其部门号,姓名,工资,以及工资少于自己的人数
+---------------------------------------+
|SELECT a.deptno,a.empname,a.empsalary,(|
| SELECT COUNT(b.empname) |
| FROM emp AS b |
| WHERE b.empsalary < a.empsalary |
| ) AS allcount |
|FROM emp AS a |
|WHERE ( |
| SELECT COUNT( b.empname ) |
| FROM emp AS b |
| WHERE b.empsalary < a.empsalary |
| )>5 |
+---------------------------------------+
14.对于emp中同一部门低于自己工资至少3人的员工,列出其部门号,姓名,工资,以及工资少于自己的人数
+---------------------------------------+
|SELECT a.deptno,a.empname,a.empsalary,(|
| SELECT COUNT(b.empname) |
| FROM emp AS b |
| WHERE b.empsalary < a.empsalary |
| AND a.deptno = b.deptno |
| ) AS allcount |
|FROM emp AS a |
|WHERE ( |
| SELECT COUNT( b.empname ) |
| FROM emp AS b |
| WHERE b.empsalary < a.empsalary |
| AND a.deptno = b.deptno |
| )>5 |
+---------------------------------------+
分享到:
相关推荐
SQL跟踪后,用该工具能够快速整理出整洁的SQL,减少人工的复制、帖贴
自制SQL文整理,非开源,非完整,写得很挫,有使用时限,强烈不建议下载
SqlServer索引碎片整理脚本,提据库查询效率,很有用。
《SQL》基本语句整理大全
对Sql语句的格式进行整理,使其易读。 操作简单,只需拷贝Sql语句,执行整理程序,然后再粘贴,就成为格式清晰的Sql语句了。
SQL Server面试常用sql整理
SQL格式整理工具,而且还可以把SQL语句转换成VB DELPHI C# JAVA里面用的格式
数据库整理工具 SQLserver专业数据整理
很实用的sql语句整理工具,把杂乱的语句变得规范和易读。
一些sql整理实例 sql整理实例 sql整理实例
sql优化总结,针对一般sql语句的优化编写进行总结整理,有助于形成良好的sql语句书写习惯
SQLSERVER基础实用必会SQL语句整理.docx
sql语句整理 sql增删改查 更新数据记录 删除数据记录
经典sql语句整理大全 喜欢的朋友可以下来看看 纯文字版 比较小
sqllice,一个自动整理SQL文格式的工具,很好用的,适合各语言的开发人员。
DBA日常维护SQL整理,涵盖操作系统、存储、数据库各个层面
自己整理的sql常用语句,比较适合0接触学习sql的人
我最近在CSDN上学的SQL笔记.如有错误请多多指教.
网上找的并汇总整理的SQL日期函数语句,应该比较全面了希望对大家有用
整理的一点资料,共享下,一同学习。适合于各类中级以下人群。呵呵