1-1-典型SQL语句试题整理
Discuss(user1, user2, topic)
(1)SQL：所有被Alice和Bob讨论但不被Alice和Crowe讨论的话题
select topic from Discuss where user1='Alice' and user2='Bob'
EXCEPT
select topic from Discuss where user1='Alice' and user2='Crowe'
intersect（交）
(2)SQL：被超过10对用户讨论的话题的个数
select count(*) from (
select topic from Discuss
group by topic
having count(*)>10
) as T
职员表EMP（eno，ename，job，hire，salary，dno）eno是员工编号，dno是部门编号，hire是雇佣日期就是进公司的日期。
部门表DEPT（dno，dname，loc）
①dname=RI的员工工资的中间数。中间数就是若有九个员工，将员工工资从大到小排列，中间数就是第五个，若有十个就是从大到小排列的第五和第六个数的平均数。
set @id=-1
select avg(salary) from(
select @id:[email protected]+1 as id, salary from dept d, emp e
where d.dno=e.dnp=o and dname='RI'
order by salary) as t
where t.id in(FLOOR(@id/2),CEIL(@id/2))
set @id=0
select avg(salary) from(
select @id:[email protected]+1 as id, salary from dept d, emp e
where d.dno=e.dnp=o and dname='RI'
order by salary) as t
where IF( ID%2=0,ID in (ROUND(@ID/2,0),@ID/2 + 1), ID=ROUND(@ID/2,0) )
ceil() / ceiling（） 向上取整
ex： ceil（1.2） = 2
2.floor() 向下取整
ex： floor（1.2） = 1
3.round() 四舍五入
select时必须用“：=赋值”
③每个部门第一个进部门和最后一个进部门的员工的时间上的时间差换算成年是差几年，换算成月又是差几个月。
select hire into @firstdate from emp order by hire limit 0,1
另一种写法：
select @firstdate:=hire from emp order by hire limit 0,1
select hire into @lastdate from emp order by hire desc limit 0,1
desc从大到小排列
select TIMESTAMPDIFF(YEAR,DATE_FORMAT(@firstdate,'%Y-%m-%d'),DATE_FORMAT(@lastdate,'%Y-%m-%d'))
select TIMESTAMPDIFF(MONTH,DATE_FORMAT(@firstdate,'%Y-%m-%d'),DATE_FORMAT(@lastdate,'%Y-%m-%d'))
Emp(eid, ename, age, salary)
Works(eid, did, pct_time)
Dept(did,dname , budget, managerid)
1.1)同时工作于“硬件”和“软件”两个部门的每个雇员的名字和年龄
select e.ename, e.age from emp e, works w, dept d
where e.eid=w.eid and d.did=w.did
where dname='硬件'
Intersect
select e.ename, e.age from emp e, works w, dept d
where e.eid=w.eid and d.did=w.did
where dname='软件'
2)考虑以下数据库设计模式：
EMP(EMPNO, ENAME, JOB, HIREDATE, SAL, DEPTNO)
DEPT(DEPTNO, DNAME, LOC)
2.2)求第一个员工和最后一个员工雇佣（HIREDATE）之间相差的月份数，以及这些月折合的年数
SELECT TIMESTAMPDIFF(MONTH, MIN(e.HIREDATE), MAX(e.HIREDATE)) month,
TIMESTAMPDIFF(MONTH, MIN(e.HIREDATE), MAX(e.HIREDATE))/12 year
FROM EMP e
2.3)计算2017年中周内各日期（星期日、星期一......星期六）的次数（完全用SQL完成，可以分成多步骤完成，包括构建数据表和查询计算结果）
create table week(
dateno int identity(1,1) primary key,
dateInYear datetime,
weekday varchar(100)
)
delimiter $$
create procedure insert_calendar(s_date DATE, e_date DATE)
begin
declare insertdayofweek int;
declare dateofweek varchar(100);
while s_date <= e_date DO
dateofweek=DAYOFWEEK(s_date);
IF dateofweek=1 then insertdayofweek='星期日'
ELSE IF dateofweek=2 then insertdayofweek='星期一'
ELSE IF dateofweek=3 then insertdayofweek='星期二'
ELSE IF dateofweek=4 then insertdayofweek='星期三'
ELSE IF dateofweek=5 then insertdayofweek='星期四'
ELSE IF dateofweek=6 then insertdayofweek='星期五'
ELSE IF dateofweek=7 then insertdayofweek='星期六'
end if
INSERT INTO week(dateInYear, weekday) values(s_date, dateofweek);
set = s_date + INTERVAL 1 DAY ;
end while
end $$
delimiter
call insert_calendar('2017-01-01','2017-12-31')
select weekday,count(*) from week group by weekday
order by weekday
2.4)计算每个员工的DEPTNO、ENAME和SAL以及同一部门（DEPTNO值相同的）员工之间的SQL的差。该差值在当前员工及同部门内紧随其后的员工之间计算而来，（对最新聘用的员工这个差值是N/S，没看懂这句什么意思）
select A.DEPTNO,A.SAL, A.SAL - B.SAL as SAL1
from(
select a.*,(@i := @i + 1) as ord_num from EMP a,(select @i := 0) d order by DEPTNO,
HIREDATE desc
) as A
LEFT JOIN (
select a.*,(@j := @j + 1) as ord_num from EMP a,(select @j := 1) c order by DEPTNO,
HIREDATE desc
)as B on A.ord_num=B.ord_num and A.DEPTNO=B.DEPTNO
3)考虑以下数据库设计模式，orderstatus是记录订单状态的表，主键是oid和status的复合键，订单包含多种状态，同时某一订单可能会有某一状态多次出现的情况，比如换货的过程。
Customers(cid, cname, city)
Orders(oid, cid, date)
Orderstatus(oid, status, statusdate)
请完成查询，列出所有尚未标记为完成状态的订单的以下字段：oid,cname, 订单的最后状态status以及设置状态的时间 statusdate
select c.cname, oo.oid, oo.`status`, oo.statusdate
from Orderstatus oo
left join Orders o on o.oid = oo.oid
left join Customers c on c.cid = o.cid
where oo.statusdate in(
select max(statusdate) from Orderstatus t group by t.oid) and oo.status != '完成';
select c.cname, a.*
from Orderstatus a, (select oid, max(statusdate) as statusdate from Orderstatus group by
oid) b
left join Orders o on o.oid = b.oid
left join Customers c on c.cid = o.cid
where a.oid = b.oid and b.statusdate = a.statusdate and a.status != '完成';
某商场部分关系模式如下
店铺表store(store_id, store_name, store_ floor, room_no)
商品表commodity(com_id, com_name, com_price, store_id)
销售员表saler(saler_id, saler_name, saler__gender, saler__age, store_id)
流水账单表 bill(bill_id, saler_id, com_id, purchase_quantity, total_price, bill_time)
其中,店铺表记录了该商场入驻的店铺信息,商品为每个店铺中销售的商品信息,销售员为店铺中的工作人员,流水账单表为为商场收银台交易的纪录, bill time交易发生的时间,为 datetime类型,每个流水账单只包含了一种商品,不同类商品为不同的账单,purchase_ quantity数量为正整数
(1)在生成账单记录时, total_price是由传入的 com_ id和 purchase_quantity查询到相关的单价以及数量计算出来的,请完成这样一条插入
create procedure insert(saler_id_in int,com_ id_in int, purchase_quantity_in int)
begin
declare com_price float
declare total_price float
com_price=select com_price from commodity where com_id=com_ id_in
total_price=com_price*purchase_quantity_in
insert into bill(saler_id, com_id, purchase_quantity, total_price, bill_time) values(saler_id_in,com_ id_in, purchase_quantity_in, total_price,now())
end
set @saler_id_in=...
set @com_ id_in=...
ser @purchase_quantity_in=...
call insert(@saler_id_in, @com_ id_in, @purchase_quantity_in)
(2)A店铺的老板想要了解一下店铺员工每天的销售业绩,请查询 store_id为"MCSA"的店铺里的所有员工在2015年11月11日的完成的交易笔数,以及每个员工完成的总交易额
select count(total_price) as dealCount, sum(total_price) as sum from saler s
left join bill b on s.saler_id=b.saler_id
where store_id='MCSA' and date(bill_time)='2015-11-11'
select s.saler_id, s.saler_name, count(bill_id) as bill_count, sum(total_price) as bill_total from saler s, bill b
where s.store_id='MCSA' and s.saler_id=b.saler_id and date_format(bill_time, '%Y-%m-%d')='2015-11-11'
union
select saler_id, saler_name, 0 as bill_count, 0 as bill_total from saler
where store_id='MCSA' and saler_id not in(
select saler_id from bill where date_format(bill_time, '%Y-%m-%d')='2015-11-11')
商场的管理人员想了解2015年11月11日哪个店铺的交易额最大,查询其店铺名称，店铺所在楼层,店铺编号以及当天的交易额。
select store_id, store_name, store_floor, room_no, sumPrice from
(select t.store_id, store_name, t.store_floor, t.room_no, sum(total_price) as sumPrice
from store t, saler s, bill b
where t.store_id=s.store_id
and s.saler_id=b.saler_id
and date_format(bill_time, '%Y-%m-%d')='2015-11-11'
group by t.store_id) as T
where T.sumPrice=max(T.sumPrice)
四、(代码题。本题满分20分。)
教师表teacher(teacher_id, teacher_name, teacher_email, teacher_gender, teacher_title)
课程表course(course_id, course_name, teacher_id, term, year)
学生表student(student_id, student_name, student_grade, student_gender)
选课情况course_election(ce_id, course_id, student_id, marks)
(2)为了方便查询,希望创建一个课程视图,包括2015年第一学期的所有课程,包含课程表的所有字段,以及每门课的总选修人数。若没人选修,则选修人数为0
create view as
select c.course_id, c.course_name, c.teacher_id, c.term, c.year, count(ce_id) as sumPeople from course c
left join course_election ce on ce.course_id=c.course_id
group by c.course_id
(3)教务处现在进行了一些修改,当有不及格的成绩录入时,需要对成绩进行规范处理。对于低于60分的成绩按照60分算,使用触发器完成相应的要求
create trigger grade_trigger
before insert or update on course_election
for each row
begin
if new.marks<=60 then
new.marks=60
end if;
end;
设有一个公司内部信息管理数据库,其关系模式如下:
职工E(工号eno,姓名 ename,年薪 salary,部门编号dno)
项目P(项目编号pno,项目名称 pname,所在城市cy,负责部门编号dno)
工作W(职工工号eno,项目编号pno,工作时间 hours)
职工家属R(职工工号eno,家属的姓名name,家属的性别sex)
4)在每一个部门中查询其年薪收入最高的职工,结果返回部门编号和该部门中年薪收入最高职工的工号。
select t.eno from(
select eno,ename,salary,dno from e order by salary desc
) t
GROUP BY t.dno
select eno from e where (eno,salary) in(
select eno , max(salary) as salary from e group by dno)
https://leetcode-cn.com/problems/department-highest-salary/
create view am_sum(aid, year, month, m_sum) as
select aid, year, month, sum(dols)
from O
group by aid, year, month
有一个船员租赁船只的系统，表结构如下：
Sailors(sid, sname, rating, age);
Boats(bid, bname, color);
Reaserves(sid, bid, day)
2)找出年龄在35岁以上，rating>5的，一个月内，同时预定了红色船和绿色船的水手姓名（8）
select sname from Sailors s, Boats b, Reaserves r
where s.sid=r.sid and b.bid=r.bid and sysdate-day<=30 and color='red' and rating>5
Intersect
select sname from Sailors s, Boats b, Reaserves r
where s.sid=r.sid and b.bid=r.bid and sysdate-day<=30 and color='green' and rating>5
3)找出预定了所有船的水手的名字（附加题5分）
select s.name from Sailors where s.sid in (
select distinct(r.sid) from Reaserves r group by r.sid
having count(distinct(r.bid))=(select count(*) from Boats))
select s.sname from Sailor s
where not exits (
Select null from Reaserves r
Where r.sid = s.sid and not exits(
Select null from Boats b where b.bid = r.bid) )
六、(代码题。本题满分20分。)
假设: Circo是一款面向于大学生的群组类的社交应用,致力于让同学们能够体验到校园生活更加有趣有活力的一面,营造和谐自然的校园氛围。在这里,你可以关注与自己志同道合的小伙伴(User),发布实时动态( Moment),创建或者加入各种各样有趣的兴趣圈子( Group),在圈子内Po上自己的美丽心情(red),参与各种话题( Topic)的讨论
其中涉及到一些实体数据表和属性,包括
用户User 编号_id,昵称 nickname,,头像 avatar,学校编号 schooled,创建时间 createdAt
学校 School 编号_id,学校名name
新鲜事 Moment 编号_id,发布者编号 author,文字内容 content,图片 Image,创建时间createdAt
新鲜事的点赞LikingMoment 用户编号 userid,新鲜事编号 momentId,创建时间 createdAt
话题 Topic 编号id,话题名称name,热度heat,创建时间 createdAt,更新时间 updated
话题关系表TopicRelation 话题编号 topica, moment编号 momentId
其中,话题热度根据时间推移按比例持续衰减,每24小时衰减10%,不足24小时则不进行衰减。(提示: power(xy),返回x的y次方)
1)选出当前热度前十的话题,包括话题的编号、名称、热度、参与的 moments数量,创建时间
select t._id, t.name,
power(0.9, TIMESTAMPDIFF(SECOND, now(), t.createAt)/(24*3600))*heat as current_heart,
count(tr.moment_id), t.createdAt from Topic t
join TopicRelation tr on t._id=tr.topicId
group bt t._id
order by current_heart limit 0,10
TIMESTAMPDIFF（单位，开始时间，结束时间）
8) 检索参加了所有项目的职工的工号；
select eno from E where NOT EXISTS(
select * from P where NOT EXISTS(
select * from W where W.eno=E.eno and W.pno=P.pno))
select eno from E where NOT EXISTS(
select * from P where P.pno NOT IN(
select W.pno from W where W.eno=E.eno))
9) 检索全体 3 号部门的职工都参加了的项目的编号和名称；
select pno,pname from P where NOT EXISTS(
select * from E where E.dno=3 and NOT EXISTS(
select * from W where W.eno=E.eno and W.pno=P.pno))
select pno,pname from P where NOT EXISTS(
select * from E where E.dno=3 and E.eno NOT IN(
select W.eno from W where W.pno=P.pno))
顾 客 客 C ( cid, cname, city, discnt )
供应商 A ( aid, aname, city )
商 品 品 P ( pid, pname, quantity, price )
订 单 单 O ( ordno, orddate, cid, aid, pid, qty, dols )
(SQL) 查询 每一个顾客的最近一份订单 ， 结果返回顾客编号和最近一份订单的购买日期 ， 并按照购买日期的降序输出查询结果。
（SQL ）
参考答案 1 ：
SELECT cid, orddate
FROM O O1
WHERE orddate >= ALL(
SELECT O2.orddate FROM O O2 WHERE O2.cid=O1.cid)
ORDER BY orddate DESC;
参考答案 2 ：
SELECT cid, orddate
FROM O O1
WHERE NOT EXISTS (
SELECT *
FROM O O2
WHERE O2.cid=O1.cid and O2.orddate > O1.orddate )
ORDER BY orddate DESC;
参考答案 3 ：
SELECT cid, orddate
FROM O, (SELECT cid, max(orddate) FROM O GROUP BY cid) AS X(cid, m_date)
WHERE O.cid=X.cid and O.orddate=X.m_date
ORDER BY orddate DESC;
参考答案 4 ：
SELECT cid, orddate
FROM O
WHERE ordno NOT IN ( SELECT O1.ordno
FROM O O1, O O2
WHERE O1.cid=O2.cid and O1.orddate < O2.orddate)
ORDER BY orddate DESC;