首页 > 试题广场 >

使用含有关键字exists查找未分配具体部门的员工的所有信息

[编程题]使用含有关键字exists查找未分配具体部门的员工的所有信息

热度指数：111167 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 32M，其他语言64M
算法知识视频讲解

使用含有关键字exists查找未分配具体部门的员工的所有信息。
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 `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`));
输出格式:

emp_no	birth_date	first_name	last_name	gender	hire_date
10011	1953-11-07	Mary	Sluis	F	1990-01-22

示例1

输入

drop table if exists employees;
drop table if exists dept_emp;
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 `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`));
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');
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d004','1995-12-03','9999-01-01');
INSERT INTO dept_emp VALUES(10004,'d004','1986-12-01','9999-01-01');
INSERT INTO dept_emp VALUES(10005,'d003','1989-09-12','9999-01-01');
INSERT INTO dept_emp VALUES(10006,'d002','1990-08-05','9999-01-01');
INSERT INTO dept_emp VALUES(10007,'d005','1989-02-10','9999-01-01');
INSERT INTO dept_emp VALUES(10008,'d005','1998-03-11','2000-07-31');
INSERT INTO dept_emp VALUES(10009,'d006','1985-02-18','9999-01-01');
INSERT INTO dept_emp VALUES(10010,'d005','1996-11-24','2000-06-26');
INSERT INTO dept_emp VALUES(10010,'d006','2000-06-26','9999-01-01');

输出

10011|1953-11-07|Mary|Sluis|F|1990-01-22

算法知识视频讲解

此用户名涉嫌违规

发表于 2021-03-20 11:06:57

题目描述：使用含有关键字exists查找未分配具体部门的员工的所有信息。 select * from employees where not exists ( select emp_no from dept_emp where employees.emp_no=dept_e 展开全文

无敌大花彪

发表于 2020-06-20 19:25:02

用exists：select * from employees ewhere not exists(select emp_no from dept_emp d where d.emp_no = e.emp_no);不用exists:select * from employees eleft joi 展开全文

fejxc

发表于 2021-09-23 21:02:01

/* 1.什么时候用EXISTS，什么时候用IN？主表为employees，从表为dept_emp，在主表和从表都对关联的列emp_no建立索引的前提下：当从表比主表大时，EXISTS查询的效率较高；原因如下： in是先执行子查询，得到一个结果集，将结果集代入外层谓词条件执行主查询，子查询只需展开全文

高质量搬砖人

发表于 2021-01-31 10:36:10

方法）使用EXISTS关键字 SELECT * FROM employees WHERE NOT EXISTS (SELECT emp_no & 展开全文

早起的虫儿e

发表于 2021-04-09 16:03:29

问题描述：使用含有关键字exists查找未分配具体部门的员工的所有信息。解答： SELECT e.* FROM employees e WHERE NOT EXISTS (SELECT * FROM dept_emp d WHERE d.emp_no = e.em 展开全文

new--coder

发表于 2022-05-23 11:39:02

本题目的：学习如何使用exists 用法： select * from table_name where [not] exists(子查询)； exists的执行过程：首先查询select * fro 展开全文

Java编程白哥

发表于 2023-03-15 08:42:42

方法一;思路：①重点理解谓词exists，谓词exists的作用是“判断是否存在满足某些条件的记录”，如果存在这样满足条件的记录，返回真，不存在，返回假。题目要查找未分配具体部门的员工的所有信息，那么，如果employees表的员工编号=dept_emp的员工编号，则表明该员工已经分配了部门。我们要展开全文

DAMES

发表于 2022-10-12 08:11:36

select * from employees e where not exists (select dept_no from dept_emp de where e.em 展开全文

似雨游离

发表于 2022-03-04 14:35:59

exists 与 not exists 的用法： 1、该子查询如果“有数据结果”(至少返回一行数据)，则该EXISTS() 的结果为“true”，外层查询执行 2、该子查询如果“没有数据结果”（没有任何数据返回），则该EXISTS()的结果为“false”，外层查询不执行 3、EXISTS后面的子展开全文

Mr_jin

发表于 2021-06-25 10:49:20

exists详细解释exists语法：exists(完整的查询语句），结果返回true或false若返回true，则将外层查询表的记录保存，若为false,则删除记录。 from employees e where not exists (select emp_no from dept_emp d 展开全文

问题信息

难度：

145条回答 2855收藏 14015浏览

通过挑战的用户

查看代码

淡定的Zero

2023-03-14 17:32:25
bbq了的大菠萝

2023-03-08 21:45:22
牛客51600...

2023-03-04 13:32:22
爱吃肉的小饼干...

2023-02-07 02:29:37
牛客15063...

2023-02-02 02:39:47

相关试题

扫描二维码，关注牛客网
意见反馈
下载牛客APP，随时随地刷题

扫一扫，把题目装进口袋

求职之前，先上牛客: 扫描二维码，进入QQ群



扫描二维码，关注牛客公众号

公司地址：北京市朝阳区北苑路北美国际商务中心K2座一层-北京牛客科技有限公司
联系方式：010-60728802 投诉举报电话：010-57596212（朝阳人力社保局）
京ICP备14055008号-4 增值电信业务经营许可证营业执照人力资源服务许可证
京公网安备 11010502036488号

drop table if exists employees;
drop table if exists dept_emp;
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 `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`));
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');
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d004','1995-12-03','9999-01-01');
INSERT INTO dept_emp VALUES(10004,'d004','1986-12-01','9999-01-01');
INSERT INTO dept_emp VALUES(10005,'d003','1989-09-12','9999-01-01');
INSERT INTO dept_emp VALUES(10006,'d002','1990-08-05','9999-01-01');
INSERT INTO dept_emp VALUES(10007,'d005','1989-02-10','9999-01-01');
INSERT INTO dept_emp VALUES(10008,'d005','1998-03-11','2000-07-31');
INSERT INTO dept_emp VALUES(10009,'d006','1985-02-18','9999-01-01');
INSERT INTO dept_emp VALUES(10010,'d005','1996-11-24','2000-06-26');
INSERT INTO dept_emp VALUES(10010,'d006','2000-06-26','9999-01-01');

使用含有关键字exists查找未分配具体部门的员工的所有信息

输入

输出

问题信息

热门推荐

通过挑战的用户

相关试题