資料庫之經典面試

1，獲取各個部門最高薪水的人員

使用連接查詢

select e.department,e.`name`,t.dept,t.maxMoney
from 
	(select e.department as dept,max(e.money) as maxMoney from employee e group by e.department)t
	join
		employee e
	on 
		t.dept = e.department
	where 
		e.money = t.maxMoney;

2，哪些人的薪水在部門的平均薪水之上

select
	e.department,e.`name`,t.`平均薪水`,e.money
from
	(select
		e.department as dept,avg(e.money) as 平均薪水
	from
		employee e
	group by 
		e.department)t
join
	employee e
on
	e.department = t.dept
where
	e.money > t.`平均薪水`;

3，求各個部門平均薪水的等級

表：

select 
	t.`部門` as 部門,t.`平均薪水` as 平均薪水,m.grade
from 
	(select e.department as 部門,avg(e.money) as 平均薪水 from employee e group by e.department)t
join
	moneygrade m
on
	t.`平均薪水` between m.low and m.high;

4, 不使用組函式max()，獲取最高薪水

方案一：

select
	e.name,e.money
from 
	employee e
order by 
	e.money desc limit 1;

方案二:
第一步

select
	distinct e.name ,e.money
from
	employee e
join
	employee b
on 
	e.money < b.money;

可以發現除了最大值，其他的值都符合要求
第二步

select e.name,e.money from employee e where e.money not in (select
	distinct e.money
from
	employee e
join
	employee b
on 
	e.money < b.money);

5，求平均薪水最低的等級的部門名稱

select 
	t.department
from 
	(select e.department ,avg(e.money) as 平均薪水 from employee e group by e.department)t
join
	moneygrade m 
on 
	t.平均薪水 between m.low and m.high
where m.grade = (
	select 
		min(m.grade) 
	from
			(select 
				t.department,t.`平均薪水`,m.grade 
				from 
					(select e.department ,avg(e.money) as 平均薪水 from employee e group by e.department)t
				join moneygrade m 
				on t.平均薪水 between m.low and m.high)m); 

6,三個表，學生表（S），課程表( C)，學生選課表(SC)

課程表：分別對應課號（cno），課名（cname），老師（cteacher）

學生表：學號(sno)，姓名(sname)

課程表：學號(sno)，課號(cno)，成績(scgrade)
學號，課號分別為外鍵

向學生表中添加資料

課程表添加資料

選課表添加資料

問題1:找出沒選過鄭老師的所有學生的姓名

select sname from s where sno not in 
	(select sno from sc where cno = 
		(select cno from c where cteacher = '鄭老師'));

問題2：列出兩門（含兩門）以上不及格學生姓名及平均成績

#分組先求每個人成績小于60分的門數

select 
	sc.sno , s.sname ,count(*) as studentSum
from
	sc
join
	s
on
	s.sno = sc.sno
where
	sc.scgrade < 60
group by
	sc.sno
having
	studentSum >= 2;

問題3：即學過1號課程和2號課程所有學生的姓名

select
	s.sname 
from 
	sc
join
	s
on
	sc.sno = s.sno
where
	cno = 1 and sc.sno in(
		select sno from sc where cno = 2
	);

7，新建兩個表，分別為dept部門資訊表和emp員工表

然后呢插入資料，emp表如下

dept表如下

1，列出所有的員工及領導的名字

select
	e.ename,
	b.ename as leadername
from
	emp e
left join
	emp b
on
	e.mgr = b.empno;

并出現結果

外連接查詢的條數永遠大于等于內連接

2，列出受雇日期早于其上級的所有的員工編號，姓名，部門名稱

#列出受雇日期早于其上級的所有的員工編號，姓名，部門名稱
 select 
	d.dname,e.empno,e.ename
from
	emp e
join
	emp b
on
	e.mgr = b.empno
join
	dept d
on
	e.deptno = d.deptno
where
	e.hiredate < b.hiredate;

最終的資料為

3，列出部門名稱和這些部門的員工資訊，同時列出那些沒有員工的部門

select
	d.dname,e.*
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno;

4,列出至少有5個員工的所有部門

select 
	e.deptno,count(e.ename) 
as
	totalEmp
from 
	emp e 
group by 
	e.deptno 
having totalEmp >= 5;

5,列出在部門"SALES"<銷售部>作業的員工的姓名，假定不知道銷售部門的部門編號

select 
	ename
from
	emp
where
	deptno = (select deptno from dept where dname = 'SALES');

6,列出薪資高于公司平均水平的所有員工，所在部門，上級領導

select 
	e.ename,
	d.dname,
	b.ename
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
left join
	emp b
on
	e.mgr = b.empno
where
	e.sal > (select avg(sal) as angsal from emp);

7,列出薪水高于在部門30狗熊的所有員工的薪金的員工姓名和薪金，部門名稱

select 
	d.dname,
	e.ename,
	e.sal
FROM
	emp e
join
	dept d
on
	e.deptno = d.deptno
where
	e.sal > (select max(sal) as maxSal from dept);

8,列出在每個部門作業的員工數量，平均工資

select 
	d.dname,count(*) as '人數',avg(e.sal) as '平均工資' 
from 
	emp e 
join 
	dept d 
on 
	d.deptno = e.deptno 
group by
	e.deptno; 

9,列出所有的員工的姓名，部門名稱和工資

select
	e.ename as '員工姓名',
	d.dname as '部門名稱',
	e.sal as '工資'
from
	emp e
join
	dept d
on
	e.deptno = d.deptno

10,列出所有的部門的詳細資訊和人數

select 
	d.deptno,d.dname,d.loc,count(e.ename) as totalEmp
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno
group by
	d.deptno,d.dname,d.loc;

11,列出各種作業的最低工資以及從事此作業的雇員姓名

select distinct
	e.job,t.minSal,e.ename
from 
	emp e
join
		(select 
		e.job,min(e.sal) as minSal
	from
		emp e
	join
		dept d
	group by
		e.job)t
on
	e.sal = t.minSal

12,列出各個部門MANAGER的最低薪金

select 
	w.deptno,min(w.sal) as minSal
from
	emp w
where
	w.job = 'MANAGER'
group by
	w.deptno;

13,求出員工領導超過3000的員工名稱和領導名稱

select
	e.ename,
	b.ename as leadername
from
	emp e
join
	emp b
on
	e.mgr = b.empno
where
	b.sal > 3000;

14,名字中帶S字符的部門的工資合計和部門人數

select 
	d.dname,
	sum(e.sal) as sumMoney,
	count(e.ename) as sumPeople
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
where
	d.dname like '%S%'
group by
	d.dname

15,給任職期間超過30年的員工加薪

update
	emp
set
	sal = sal * 1.1
where
	(to_days(now()) - to_days(hiredate))/365 > 30;