数据库面试题+sql语句解析

create table t_user(
  uid varchar(10) not null comment '用户ID',
  order_time timestamp null comment '下单时间',
  order_category varchar(20) not null comment '类型',
  order_amt float not null default '0.00' comment '价格',
  shop_id varchar(10) not null comment '商铺ID'
)comment '用户信息表';
create table t_shop(
  shop_id varchar(10) not null comment '商铺ID',
  bd_name varchar(10) not null comment '销售经理',
  bd_team varchar(10) not null comment '销售团队',
  start_time varchar(10) not null comment '开始时间',
  end_time varchar(10) not null comment '结束时间'
)comment '商铺信息表';
insert into t_shop(shop_id,bd_name,bd_team,start_time,end_time) values
('ZL123','小明','销售A组','2018-01-01','2018-01-14'),
('ZL123','小张','销售B组','2018-01-15','2099-12-31'),
('SM456','小张','销售B组','2016-01-01','2019-01-14'),
('HBW123','小李','销售C组','2015-01-01','2020-12-31'),
('XM456','小李','销售C组','2015-01-01','2016-01-14');
insert into t_user(uid,order_time,order_category,order_amt,shop_id) values
('A123','2018-01-01 12:34:00','麻辣烫',25.30,'ZL123'),
('A123','2018-01-06 12:34:00','粥',34.20,'SM456'),
('B456','2018-01-15 12:34:00','麻辣烫',25.30,'ZL123'),
('B456','2018-01-25 12:34:00','汉堡',36.30,'HBW123'),
('C789','2018-02-01 12:34:00','小龙虾',19.80,'XM456');
select * from t_user;
select * from t_shop; 
#1月每笔消费均大于20元的用户的总消费金额
#条件：1月+大于20+
select month(now()) from daul;
select round(sum(order_amt),2) from t_user where month(order_time)=1
and order_amt>20;
#1月只吃了麻辣烫和汉堡的人数
#条件：1月+（'麻辣烫' and '汉堡'）
select count(uid) from t_user where month(order_time)=1 and order_category in ('麻辣烫','汉堡');
#计算每个人bd_name的BD对应门店的销售额
select s.shop_id,round(sum(u.order_amt),2) from t_user u,t_shop s where
u.shop_id=s.shop_id GROUP BY s.shop_id;
select shop_id,round(sum(order_amt),2)from t_user GROUP BY shop_id;

Create Table Department(
dept_id varchar(2) not null comment '部门编号',
dept_name varchar(20) not null comment '部门名称',
dept_leader varchar(10) comment '部门经理'  
)comment '部门表';
Create Table Personnel(
id varchar(4) not null comment '员工号',       
name varchar(10) not null comment '姓名',      
dept_id varchar(2) not null comment '部门编号',   
age integer comment '年龄',                   
gzsj date comment '参加工作时间',                     
technical_post varchar(10) comment '职称',   
salary integer comment '薪水'                
)comment '员工表';
select * from Department;
select * from Personnel;
#1写出表Department增加一条记录 和 更新一条记录的 SQL语句
#增加记录值 ('12', '研发部', '张三') ； 
#更新 dept_id='12'的记录 ('12', '研发部', '张三新') ;
insert into Department(dept_id,dept_name,dept_leader) values('12','研发部','张三');
insert into Department(dept_id,dept_name,dept_leader) values('13','研发部','李四');
insert into Department(dept_id,dept_name,dept_leader) values('14','研发部','王五');
insert into Department(dept_id,dept_name,dept_leader) values('15','研发部','赵六');
update Department set dept_leader='张三发' where dept_leader='张三';
#2需要给表Department增加一列字段notes，长度为10的字符串，默认值为‘0’ , 请写出相关SQL语句
alter table Department add notes varchar(10) default 0;
#3查找工资大于2000元的员工记录，并按员工号id升序排列
insert into Personnel(id,name,dept_id,age,technical_post,salary) values
('1','小明','12',23,'技术总监',12000),
('2','小张','13',18,'项目经理',10500),
('3','小胡','14',20,'产品经理',20000),
('4','小李','15',21,'执行总裁',30000);
select * from Personnel where salary>2000 order by id;
#4查找工资大于2000元的员工所在部门、部门编号、部门经理、员工名称
select d.dept_name,d.dept_id, d.dept_leader,p.name from Personnel p inner join Department d on p.dept_id=d.dept_id where p.salary>2000;
#5查找张三和李四所在部门所有人员的姓名
select name from Personnel where name ='张三' and name='李四';
select name from Personnel where dept_id in(select dept_id from Personnel where name ='张三' and name='李四');
#6查看每个部门的部门经理和部门人数，按部门人数排序？
select d.dept_leader,count(p.id) from Department d left outer join Personnel p on p.dept_id=d.dept_id group by d.dept_leader order by count(p.id);
#7删除表Department中的所有记录
delete from Department;
#8删除表Department
drop table Department;

drop table kingstar;
select * from kingstar;
create table kingstar(
  dept_no char(4),
  person_no int,
  sex char(1),
  salary decimal(19,4)
);
insert into kingstar(dept_no,person_no,sex,salary) values('H001',1210,'M',1234.00);
insert into kingstar(dept_no,person_no,sex,salary) values('H001',1211,'f',900.00);
insert into kingstar(dept_no,person_no,sex,salary) values('H002',1212,'f',3000.00);
insert into kingstar(dept_no,person_no,sex,salary) values('H002',1213,'M',4500.00);
insert into kingstar(dept_no,person_no,sex,salary) values('H003',1214,'M',6394.00);
insert into kingstar(dept_no,person_no,sex,salary) values('H003',1215,'f',7900.00);
insert into kingstar(dept_no,person_no,sex,salary) values('H004',1216,'M',2300.00);
insert into kingstar(dept_no,person_no,sex,salary) values('H004',1217,'M',3400.00);
insert into kingstar(dept_no,person_no,sex,salary) values('H005',1218,'M',3200.00);
#1显示每个部门的男生人数、女生人数和总人数
select dept_no,
sum(case when sex = 'M' then 1 else 0 end)男生人数,
sum(case  when sex = 'f' then 1 else 0 end)女生人数,
count(*)总人数
from kingstar GROUP BY dept_no;
#2显示每个部门的男生人数、女生人数和总人数，且该部门的额女生人数>=1,且按部门标号降序排序
select dept_no,
sum(case when sex = 'M' then 1 else 0 end)男生人数,
sum(case when sex = 'f' then 1 else 0 end)女生人数,
count(*)总人数
from kingstar GROUP BY dept_no having sum(case when sex = 'f' then 1 else 0 end)>=1 ORDER BY dept_no desc;

#1列出薪资高于公司平均薪资的所有员工
select * from emp where sal in(select(sal) from emp);
#2列出薪资高于在部门30工作的所有员工的薪金的员工姓名和薪金
select e.ename,e.sal,d.dname from emp e join dept d on e.deptno = d.deptno where e.sal > (select max(sal) from emp where deptno=30);
#3列出在每个部门工作的员工数量，平均工资和平均服务期限
#发现avgTime,在看看表中的数据，原来sum（avg_time）只是简单的把字符串去掉特殊字符后的结果相加而已。
select d.deptno, count(e.ename) as total_emp, ifnull(avg(sal), 0) as avgsal,ifnull(avg((TO_DAYS(NOW())-TO_DAYS(e.hiredate))/365),0) as avgTime from emp e right join dept d on e.deptno = d.deptno GROUP BY d.deptno;
#4列出所有部门的详细信息和部门人数
select d.*, count(e.ename) from emp e right join dept d on e.deptno = d.deptno GROUP BY d.deptno,d.dname,d.loc;
#5列出各种工作的最低工资
select job,min(sal) 最低工资 from emp GROUP BY job;
#6列出各个部门MANAGER(经理)的最低薪金
select deptno,min(sal) from emp where job='MANAGER' GROUP BY deptno;