SQL练习题一

本题来自于牛客网,答案为自己训练出,仅供参考。牛客网链接:https://www.nowcoder.com/activity/oj

 

题目一:

  查找最晚入职员工的所有信息
  CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` char(1) NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`));

       测试数据集:

  INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');

  INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');

  INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');

  INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');

  INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');

  INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');

  INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');

  INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');

  INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');

  INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');

  INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22'); 

  答案:

  select * from employees order by hire_date desc limit 1

  对应输出应该为:

  10008|1958-02-19|Saniya|Kalloufi|M|1994-09-15

 

题目二:

  查找入职员工时间排名倒数第三的员工所有信息(上表)

  select * from employees order by hire_date desc limit 2,1

 

题目三:

  查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号dept_no
  CREATE TABLE `dept_manager` (
  `dept_no` char(4) NOT NULL,
  `emp_no` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`dept_no`));
  CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`));

  答案:

  select salaries.*, dept_no 

  from salaries left join dept_manager on salaries.emp_no = dept_manager.emp_no

  where salaries.to_date='9999-01-01' AND

  dept_manager.to_date='9999-01-01'

 题目四:

  查找所有已经分配部门的员工的last_name和first_name
  CREATE TABLE `dept_emp` (
  `emp_no` int(11) NOT NULL,
  `dept_no` char(4) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`dept_no`));
  CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` char(1) NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`));

  答案:

  select last_name, first_name, dept_no 

  from dept_emp left join employees on dept_emp.emp_no = employees.emp_no

题目五:

  查找所有员工的last_name和first_name以及对应部门编号dept_no,也包括展示没有分配具体部门的员工(上表)

  答案:

  select last_name, first_name, dept_no 

  from employees left join dept_emp on dept_emp.emp_no = employees.emp_no

题目六:

  查找所有员工入职时候的薪水情况,给出emp_no以及salary, 并按照emp_no进行逆序
  CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` char(1) NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`));
  CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`));

  答案:

  select employees.emp_no, salary

  from employees left join salaries on employees.emp_no = salaries.emp_no

  where employees.hire_date = salaries.from_date

  order by employees.emp_no desc

题目七:

  查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
  CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`));

  答案:

  select emp_no, count(salary) t from salaries

  group by emp_no having t > 15

题目八:

 

上一篇:5.7 进阶7:子查询


下一篇:Oracle 过滤和排序数据