数据库SQL查询语句面试题

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 -- 变动日期
);