查询综合练习

多表及子查询准数据

  • 员工信息表
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 员工信息表 employee
CREATE TABLE emp(
    empno int comment '员工编号',
    ename varchar(50) comment '员工姓名',
    job varchar(50) comment '员工工作',
    mgr int comment '员工经理编号',
    hiredate date comment '员工入职日期',
    sal decimal(7,2) comment '员工工资', 
    comm decimal(7,2) comment '员工奖金',
    deptno int comment '员工所处部门'
);

INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
INSERT INTO emp values(7981,'MILLER2','CLERK',7788,'1992-01-23',2600,500,20);
  • 部门信息表
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- 部门表
CREATE TABLE dept(
    deptno int comment '部门编号',
    dname varchar(14) comment '部门的名称',  
    loc varchar(13) comment '部门所处的位置'
);

INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept values(30, 'SALES', 'CHICAGO');
INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON');
  • 基本查询
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- 所有员工的信息
-- 薪资大于等于1000并且小于等于2000的员工信息
-- 从员工表中查询出所有的部门编号
-- 查询出名字以A开头的员工的信息
-- 查询出名字第二个字母是L的员工信息
-- 查询出没有奖金的员工信息
-- 所有员工的平均工资
-- 所有员工的工资总和
-- 所有员工的数量
-- 最高工资
-- 最少工资
  • 分组查询
1
-- 每个部门的平均工资
  • 多表查询
1
-- 查询员工编号,员工姓名,经理编号,经理姓名
  • 子查询
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- 单行子查询(> < >= <= = <>)
    -- 查询出高于10号部门的平均工资的员工信息

-- 多行子查询(in  not in any all)    >any  >all
    -- 查询出比20号部门任何员工薪资高的员工信息


-- Select接子查询
    -- 获取员工的名字和部门的名字
-- from后面接子查询
    -- 查询emp表中经理信息
-- where 接子查询
    -- 薪资高于10号部门平均工资的所有员工信息
-- having后面接子查询
    -- 有哪些部门的平均工资高于30号部门的平均工资

-- exists的用法
   -- 查询所有有员工的部门信息

-- 最高工资的员工信息
-- 最低工资的员工信息
-- 工资>JONES工资
-- 查询与SCOTT同一个部门的员工
-- 工资高于30号部门所有人的员工信息
-- 查询工作和工资与MARTIN完全相同的员工信息
-- 有两个以上直接下属的员工信息
-- 查询员工编号为7788的员工名称,员工工资,部门名称,部门地址
-- 查询薪资大于薪资最高的员工所在部门的平均工资和薪资最低的员工所在部门的平均工资的平均工资的员工信息
-- 查询出高于本部门平均工资的员工信息 
-- 列出达拉斯加工作的人中,比纽约平均工资高的人
-- 查询7369员工编号,姓名,经理编号和经理姓名
-- 查询出各个部门薪水最高的员工所有信息

面试题

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
CREATE TABLE test(
  name CHAR(20),
  kecheng CHAR(20),
  fenshu CHAR(20)
);
INSERT INTO test VALUES('张三','语文',81),
('张三','数学',75),
('李四','语文',76),
('李四','数学',90),
('王五','语文',81),
('王五','数学',82);

--请用一条Sql语句查处每门分数都大于80的学生