(三) 通用试题——SQL相关

数据库花式查询语句题目

-- 查询当年及以前年份的累计薪资, 如2000年开始, 2000年对应查询出2000年的薪资,
-- 2001年对应查询出2000年与2001年薪资之和, 2002查询出00,01,02三年的薪资之和
-- 方法1
select b.year 年份, sum(a.salary) 累计薪资
from salary as a, salary as b
where a.year<=b.year
group by b.year;
-- 方法2
select a.year,
(select sum(b.salary) from salary as b where b.year<=a.year)
from salary as a;

select b., "", a.
from salary as a, salary as b
where a.year<=b.year
order by b.year;

-- 查询出不同年份组合 如00-01 00-02 00-03 01-02...
select a.year 1队, b.year 2队
from salary a, salary b
where a.year<b.year;

-- 查询出所有月份销售额都比A同学相应月份销售额高的科目???
select a.*
from TestDB a,
(select Occmonth,max(DebitOccur) Debit101ccur from TestDB where AccID='101' group by Occmonth) b
where a.Occmonth=b.Occmonth and a.DebitOccur>b.Debit101ccur

/*
从(yy年份, mm月份, amount数值)
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
*/
select ymca.yy,
(select a1.amount from ymca as a1 where a1.yy=ymca.yy and a1.mm=1) m1,
(select a2.amount from ymca as a2 where a2.yy=ymca.yy and a2.mm=2) m2,
(select a3.amount from ymca as a3 where a3.yy=ymca.yy and a3.mm=3) m3,
(select a4.amount from ymca as a4 where a4.yy=ymca.yy and a4.mm=4) m4
from ymca
group by ymca.yy;

-- 分组查询

-- 查询各天比赛胜负数量
select win.day, win.win, lose.lose
from
(select count(1) win, day from champion as chmp2 where chmp2.res='胜' group by chmp2.day) win
inner join
(select count(1) lose, day from champion as chmp2 where chmp2.res='败' group by chmp2.day) lose
on
win.day= lose.day;

-- 方法2
select win.day, win.win win, count(chmp1.day)-win.win
from
champion as chmp1 inner join
(select count(1) win, day from champion as chmp2 where chmp2.res='胜' group by chmp2.day) win
on
win.day= chmp1.day
group by chmp1.day;

-- 方法3
select day,
sum(case when chmp.res='胜' then 1 else 0 end) win,
sum(case when chmp.res!='胜' then 1 else 0 end) lose
from champion as chmp
group by chmp.day;

-- 表中有A B C三列, 用SQL语句实现：当A列大于B列时选择A列否则选择B列, 当B列大于C列时选择B列否则选择C列

select (case when a>b then a else b end ),
(case when b>c then b esle c end)
from table_name;

select curdate();

select * from era where datediff(era.date, curdate())=0;

-- sql语句执行顺序

-- 查询各班平均成绩大于80分的学生数量和班级号
-- 1
select class, count(scoretmp.sname)
from stu inner join
(select sname
from score group by score.sname
having avg(score.score)>80
) as scoretmp
on stu.sname=scoretmp.sname
group by class
-- 2

-- ?
-- 查询各班平均分大于80分的人名和平均分
-- 1
select class, stu.sname, scoretmp.scoreavg
from stu inner join
(select sname, avg(score.score) as scoreavg from score
-- where score.score is not null
group by score.sname
having avg(score.score)>80
) as scoretmp
on stu.sname=scoretmp.sname
-- 2
select class, stu.sname, avg(score.score)
from stu inner join score
on stu.sname=score.sname
group by score.sname
having avg(score.score)>80

--
select * from score;
-- 查询英语成绩比语文成绩好的学生
select sname,
sum(case when score.course='语文' then score.score end) Chinese,
sum(case when score.course='英语' then score.score end) English
from score
group by score.sname
having Chinese<English;
-- failed
select score1.sname, score1.score ch, score2.score en
from (select * from score where score.course='语文') score1,
(select * from score where score.course='英语') score2
where score1.sname=score2.sname
and score1.score < score2.score

-- 查询选修了某两门课的学生
select sname
from score
where score.course='语文'
and score.sname
in (select sname from score where score.course='物理');

select sname
from score
where score.course='语文'
and exists
(select sname from score as sc2 where sc2.sname=score.sname and sc2.course='物理')

--查没选所有课程的学生
select sname from
score group by sname
having count(course)<(select count(distinct course) from score)

SQL查询-职员-部门emp-dept表查询
-- 创建员工信息表
CREATE TABLE emp (
empno varchar(10) NOT NULL,
ename varchar(10) DEFAULT NULL,
job varchar(10) DEFAULT NULL,
mgr varchar(10) DEFAULT NULL,
sal varchar(10) DEFAULT NULL,
deptno varchar(10) DEFAULT NULL,
PRIMARY KEY (empno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 创建部门信息表
CREATE TABLE dept (
deptno varchar(10) NOT NULL,
dname varchar(10) DEFAULT NULL,
PRIMARY KEY (deptno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--
-- 转存表中的数据 dept
--
INSERT INTO dept (deptno, dname) VALUES
('1', '事业部'),
('2', '销售部'),
('3', '技术部');

--
-- 转存表中的数据 emp
--

INSERT INTO emp (empno, ename, job, mgr, sal, deptno) VALUES
('01', 'jacky', 'clerk', 'tom', '1000', '1'),
('02', 'tom', 'clerk', 'tom', '2000', '1'),
('03', 'jenny', 'sales', 'pretty', '1000', '2'),
('04', 'pretty', 'sales', '', '800', '2'),
('05', 'buddy', 'jishu', 'canndy', '1000', '3'),
('06', 'canddy', 'jishu', '', '1000', '3'),
('07', 'biddy', 'clerk', 'tom', '2000', '1');

-- 1.查询各部门中薪资最低和最高的数据，包括的字段有部门编号、部门名称、员工名称、最高薪资、最第薪资.
select dept.deptno, dept.dname, max(emp.sal) 最高薪水, min(emp.sal) 最低薪水
from dept inner join emp on dept.deptno=emp.deptno
group by dept.deptno;

-- 2.查询出各部门中job为CLERK的最高薪资和最低薪资
select dept.deptno, dept.dname, max(emp.sal)最高薪水, min(emp.sal) 最低薪水
from dept inner join emp on dept.deptno=emp.deptno
where emp.job='clerk'
group by dept.deptno;

-- 3.查询出emp表中最低薪资小于2000的部门且job为clerk的部门编号、最低薪资和最高薪资.
select
emp.deptno, min(emp.sal)最低薪资, max(emp.sal) 最高薪资
from emp inner join dept
on emp.deptno=dept.deptno
where emp.job = 'clerk'
group by dept.deptno
having min(emp.sal)<2000;

-- 4.查询出emp表中薪资小于2000的 且job为clerk的部门编号、最低薪资和最高薪资.
select dept.deptno, dept.dname, max(emp.sal)最高薪水, min(emp.sal) 最低薪水
from dept inner join emp on dept.deptno=emp.deptno
where emp.job='clerk' and emp.sal<2000
group by dept.deptno;

-- 5.查询出emp表中员工编号、员工名称、员工薪资并按照薪资由低到高、编号有高到低的顺序进行排序.
select emp.empno, emp.ename, emp.sal
from emp
order by emp.sal, emp.empno;

-- 6.查询出emp中名字为buddy的所在部门编号以及该部门所有的员工，只查询部门编号与员工名词.
select emp.deptno, emp.ename
from emp where
emp.deptno=(select emp.deptno from emp where emp.ename='buddy');

-- 7.查询emp中job为clerk的员工信息.
select *
from emp where emp.job='clerk';

-- 8.查询emp中员工有管理者的员工姓名、管理者的信息
select emp.ename, emp.mgr from emp
where emp.mgr!='' and emp.mgr is not null;

-- 9.查询出emp表中，部门名称、部门编号并且工作内容为clerk的员工名称和员工岗位.
select emp.empno, dept.dname, emp.ename, emp.job from emp, dept
where emp.job='clerk' and emp.deptno=dept.deptno;

-- 10.查询本部门中，高于平均薪资的员工的员工编号、员工名称、部门名称、员工工资
select emp.empno empno, emp.ename ename, dept.dname dname, emp.sal sal
from dept inner join emp on dept.deptno=emp.deptno
where sal> (select avg(emp2.sal) from emp as emp2 where emp2.deptno = emp.deptno)
order by dname;

-- 11.对于emp中工资高于本部门平均水平，人数多于1人的，列出部门号，人数，平均工资，按部门号排序.
select count(emp.empno) 全部员工数量, emp.deptno 部门编号, avg(emp.sal) 平均工资
from emp
where
(
select count(emp2.empno) from emp as emp2
where emp2.deptno=emp.deptno
and emp2.sal>(
select avg(sal) from emp as emp3
where emp2.deptno=emp3.deptno
)
)>1
group by emp.deptno
order by emp.deptno;

-- 12.查询工资高于本部门平均水平的员工，列出部门号，姓名，工资，按部门号排序
select emp.empno empno, emp.ename ename, dept.dname dname, emp.sal sal
from dept inner join emp on dept.deptno=emp.deptno
where sal> (select avg(emp2.sal) from emp as emp2 where emp2.deptno = emp.deptno)
order by dname;

-- 13.查询出各个部门中工资高于本部门平均工资的员工数和部门号，按照部门号进行排序.
select empAllNum-count(1) 高于平均工资人数, emp.deptno 部门号
from
emp inner join
(select dept1.deptno deptNo, avg(emp1.sal) deptAvg, count(1) empAllNum
from dept as dept1 inner join emp as emp1 on dept1.deptno=emp1.deptno
group by dept1.deptno) as dept2
on emp.deptno = dept2.deptNo
where emp.sal<dept2.deptAvg
group by emp.deptno
order by emp.deptno;

;
;
;
;
;
;