数据库SQL查询语句面试题

Question

如题所述

Answer 1

5.1
select a.username,b.deptname from users a,dept b where a.dept_id=b.id;

5.2
update users set dept_id='9' where dept_id='2';

5.3
select a.deptname,b.count_id from dept a,(select dept_id,count(id) as count_id from users group by dept_id having count(id)>1) b where a.id=b.dept_id;

5.4
select a.deptname,b.count_man,c.count_woman from dept a,(select dept_id,count(sex) as count_man from users where sex='男' group by dept_id) b,(select dept_id,count(sex) as count_woman from users where sex='女' group by dept_id) c where a.id=b.dept_id and a.id=c.dept_id;

5.5
添加历史记录表
create table history(
id number(8), -- 记录编号
dept_id varchar2(5), -- 部门ID
user_id varchar2(5), -- 用户ID
change_date date -- 变动日期
);追问

第二题 题的意思是将所有部门2员工调入部门9 是调入 调入后部门2还是存在的
并不是把部门2修改成部门9 想我这样理解就不单纯是这样做了吧

追答

不好意思。还想问一下，调入后数据应该存储的形式是什么样的？