sql面试练习题

复习一些不太难的sql,有比较难的先暂时不看,因为应付面试,手写的话应该不会有特别复杂的吧

较简单的

要求查询出参加考试的各科成绩都高于60分,不管参加了多少科考试


CREATE TABLE tbl_score (
  id int(10) NOT NULL,
  username varchar(20) DEFAULT NULL,
  course varchar(20) DEFAULT NULL,
  score int(10) DEFAULT NULL,
  PRIMARY KEY (id)
)



insert into tbl_score (id, username, course, score) values('1','张三','语文','50');
insert into tbl_score (id, username, course, score) values('2','张三','数学','80');
insert into tbl_score (id, username, course, score) values('3','张三','英语','90');
insert into tbl_score (id, username, course, score) values('4','李四','语文','70');
insert into tbl_score (id, username, course, score) values('5','李四','数学','80');
insert into tbl_score (id, username, course, score) values('6','李四','英语','80');
insert into tbl_score (id, username, course, score) values('7','王五','语文','50');
insert into tbl_score (id, username, course, score) values('8','王五','英语','70');
insert into tbl_score (id, username, course, score) values('9','赵六','数学','90');



select * from tbl_score where username in 
(select username from tbl_score group by username having min(score) > 60)



























一个叫team的表,里面只有一个字段name,一共有4条纪录,分别是a,b,c,d,对应四个球对,现在四个球对进行比赛,用一条sql语句显示所有可能的比赛组合


CREATE TABLE team(
	name VARCHAR(10)
)


INSERT INTO team VALUES('a');
INSERT INTO team VALUES('b');
INSERT INTO team VALUES('c');
INSERT INTO team VALUES('d');


SELECT * FROM team;


SELECT *
FROM team t1,team t2 WHERE t1.name!=t2.name;























CREATE TABLE student (
  sid varchar(10) NOT NULL,
  sName varchar(20) DEFAULT NULL,
  sAge datetime DEFAULT '1980-10-12 23:12:36',
  sSex varchar(10) DEFAULT NULL,
  PRIMARY KEY (sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;





CREATE TABLE course (
  cid varchar(10) NOT NULL,
  cName varchar(10) DEFAULT NULL,
  tid int(20) DEFAULT NULL,
  PRIMARY KEY (cid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;





CREATE TABLE sc (
  sid varchar(10) DEFAULT NULL,
  cid varchar(10) DEFAULT NULL,
  score int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;





CREATE TABLE taacher (
  tid int(10) DEFAULT NULL,
  tName varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



insert  into taacher(tid,tName) values (1,'李老师'),(2,'何以琛'),(3,'叶平');

insert  into student(sid,sName,sAge,sSex) values ('1001','张三丰','1980-10-12 23:12:36','男'),
('1002','张无极','1995-10-12 23:12:36','男'),
('1003','李奎','1992-10-12 23:12:36','女'),
('1004','李元宝','1980-10-12 23:12:36','女'),
('1005','李世明','1981-10-12 23:12:36','男'),
('1006','赵六','1986-10-12 23:12:36','男'),
('1007','田七','1981-10-12 23:12:36','女');

insert  into sc(sid,cid,score) values ('1001','001',80),('1001','002',60),('1001','003',75),('1002','001',85),
('1002','002',70),('1003','004',100),('1003','001',90),('1003','002',55),('1004','002',65),('1004','003',60);

insert  into course(cid,cName,tid) values ('001','企业管理',3),
('002','马克思',3),('003','UML',2),('004','数据库',1),('005','英语',1);




SELECT * FROM student;
SELECT * FROM course;
SELECT * FROM sc;
SELECT * FROM taacher;


查询“001”课程比“002”课程成绩高的所有学生的学号


SELECT sc1.sid FROM
(SELECT * FROM sc WHERE cid=001) sc1,
(SELECT * FROM sc WHERE cid=002) sc2
WHERE sc1.score > sc2.score AND sc1.sid=sc2.sid;




查询平均成绩大于60分的同学的学号和平均成绩

SELECT sid FROM sc GROUP BY sid HAVING avg(score)>60;




查询所有同学的学号、姓名、选课数、总成绩;

SELECT sc.*,st.sName FROM student st,
(SELECT sid,count(*),sum(score) FROM sc GROUP BY sid) sc
WHERE st.sid = sc.sid;



查询姓“李”的老师的个数

SELECT count(*) FROM taacher WHERE tName LIKE "李%";




查询没学过“叶平”老师课的同学的学号、姓名; 


SELECT * FROM student WHERE sid not in(
SELECT distinct sc.sid
FROM sc,(SELECT cid
FROM course WHERE tid IN (SELECT tid FROM taacher WHERE tName="叶平")) tt
WHERE sc.cid=tt.cid);









查询学过“001”并且也学过编号“002”课程的同学的学号、姓名


SELECT st.sid,st.sName
FROM student st,
(SELECT sid
FROM sc WHERE cid=001 or cid=002 GROUP BY sid HAVING count(*) =2) sc
WHERE st.sid = sc.sid;







查询学过“叶平”老师课的同学的学号、姓名; 

SELECT * FROM student WHERE sid in(
SELECT distinct sc.sid
FROM sc,(SELECT cid
FROM course WHERE tid IN (SELECT tid FROM taacher WHERE tName="叶平")) tt
WHERE sc.cid=tt.cid);





查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名; 

SELECT st.sid,st.sName FROM student st
WHERE sid in(
SELECT sc1.sid
FROM (SELECT * FROM sc WHERE cid=001) sc1,
(SELECT * FROM sc WHERE cid=002) sc2 WHERE sc1.sid=sc2.sid AND sc2.score < sc1.score);







查询所有课程成绩小于70分的同学的学号、姓名; 

SELECT sid,sName FROM student WHERE sid in
(SELECT sc1.sid
FROM (SELECT sid,count(*) ct
FROM sc WHERE score<70 GROUP BY sid HAVING count(*)>0) sc1,
(SELECT sid,count(*) ct FROM sc GROUP BY sid) sc2
WHERE sc1.sid=sc2.sid AND sc1.ct=sc2.ct);






查询没有学全所有课的同学的学号、姓名


SELECT sid,sName FROM student WHERE sid IN(
SELECT sid
FROM sc GROUP BY sid HAVING count(*)=(SELECT count(*) FROM course));








查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名


SELECT sid,sName FROM student WHERE sid in(
SELECT distinct sid
FROM sc WHERE cid IN (SELECT cid FROM sc WHERE sid=1001));







把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩

UPDATE sc sc3,
(SELECT sc1.cid,avg(score) score
FROM sc sc1,(SELECT cid
FROM course WHERE tid IN(SELECT tid FROM taacher WHERE tName='叶平')) sc2 
WHERE sc1.cid=sc2.cid GROUP BY cid) sc4
SET sc3.score=sc4.score WHERE sc3.cid=sc4.cid;





删除学习“叶平”老师课的SC表记录


DELETE FROM sc WHERE cid in(
SELECT cid
FROM course WHERE tid=(SELECT tid FROM taacher WHERE tName='叶平'));
SELECT tid FROM taacher WHERE tName='叶平');








查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分

SELECT cid '课程ID',max(score) '最高分',min(score) '最低分'
FROM sc
GROUP BY cid





按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩,
按如下形式显示: 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分


SELECT sid as 学生ID 
,(SELECT score FROM sc WHERE sc.sid=t.sid AND cid='004') AS 数据库 
,(SELECT score FROM sc WHERE sc.sid=t.sid AND cid='001') AS 企业管理 
,(SELECT score FROM sc WHERE sc.sid=t.sid AND cid='005') AS 英语 
,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩 
FROM sc AS t 
GROUP BY sid 
ORDER BY avg(t.score) 







查询不同老师所教不同课程平均分从高到低显示 要求显示:教师ID,教师姓名,课程ID,课程名称,平均成绩


SELECT max(co.tid) '教师id',max(ta.tName) '教师姓名', max(sc.cid) '课程id', max(co.cName) '课程名称', avg(score) '平均成绩'
FROM course co,sc sc,taacher ta
WHERE co.tid=ta.tid AND co.cid=sc.cid
GROUP BY sc.cid





统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60] 

SELECT sc.cid,co.cName,
sum(CASE WHEN score>=85 THEN 1 ELSE 0 END) '100-85',
sum(CASE WHEN score>=70 AND score<=85 THEN 1 ELSE 0 END) '85-70',
sum(CASE WHEN score>=60 AND score<=70 THEN 1 ELSE 0 END) '70-60',
sum(CASE WHEN score<60 THEN 1 ELSE 0 END) '<60'
FROM sc sc,course co
WHERE sc.cid=co.cid
GROUP BY sc.cid,co.cName



查询每门课程被选修的学生数

SELECT cid,count(*)
FROM sc
GROUP BY cid




查询出只选修了一门课程的全部学生的学号和姓名

SELECT max(st.sName),max(st.sid)
FROM sc sc,student st
WHERE sc.sid=st.sid
GROUP BY cid HAVING count(*)=1






查询男生、女生人数

SELECT sSex,count(sSex)
FROM student
GROUP BY sSex





查询姓“张”的学生名单
SELECT * FROM student WHERE sName LIKE "张%";

查询同名同性学生名单,并统计同名人数
SELECT st1.sName
FROM student st1,student st2
WHERE st1.sName=st2.sName
GROUP BY st1.sName
HAVING count(*) >1







查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列 

SELECT cid,avg(score)
FROM sc
GROUP BY cid
ORDER BY avg(score) asc,cid



查询平均成绩大于85的所有学生的学号、姓名和平均成绩 

SELECT sc.sid,max(st.sName),avg(sc.score)
FROM sc sc,student st
WHERE sc.sid=st.sid
GROUP BY sc.sid
HAVING avg(sc.score)>75;




查询课程名称为“数据库”,且分数低于60的学生姓名和分数

SELECT *
FROM course co,sc sc,student st
WHERE co.cName='数据库'
AND co.cid=sc.cid
AND sc.sid=st.sid
AND sc.score<60





查询任何一门课程成绩在70分以上的姓名、课程名称和分数; 

SELECT st.sName,co.cName,sc.score
FROM sc sc,course co,student st
WHERE sc.cid=co.cid
AND sc.sid=st.sid
AND sc.score>70;





查询不及格的课程,并按课程号从大到小排列 
SELECT *
FROM sc
WHERE score<60
ORDER BY cid desc;




查询选修“叶平”老师所授课程的学生中,成绩最高的学生姓名及其成绩

SELECT st.sName,sc.score
FROM taacher ta,student st,course co,sc sc
WHERE ta.tName='叶平'
AND ta.tid=co.tid
AND co.cid=sc.cid
AND sc.sid=st.sid
ORDER BY score desc LIMIT 1







查询各个课程及相应的选修人数

SELECT cid,count(*)
FROM sc
GROUP BY cid







查询不同课程成绩相同的学生的学号、课程号、学生成绩

SELECT st.sid,sc1.cid,sc2.cid,sc1.score
from sc sc1,sc sc2,student st
WHERE sc1.cid!=sc2.cid
AND sc1.score=sc2.score
AND st.sid=sc1.sid;




统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列

SELECT cid,count(*)
FROM sc
GROUP BY cid
HAVING count(*) > 2
ORDER BY count(*) desc,
cid asc;




检索至少选修两门课程的学生学号

SELECT sid FROM sc
GROUP BY sid
HAVING count(*) > 2;




查询全部学生都选修的课程的课程号和课程名


SELECT sc.cid,co.cName
FROM sc,course co
WHERE sc.cid=co.cid
GROUP BY sc.cid
HAVING count(*) = (SELECT count(*) FROM student)





查询没学过“叶平”老师讲授的任一门课程的学生姓名
SELECT st.sName
FROM student st,sc sc
WHERE sc.sid=st.sid
AND sc.sid not in(
SELECT sc.sid
FROM course co,sc sc
WHERE co.tid=3
AND co.cid=sc.cid)




较复杂的

或者是不太好理解的,或者是有歧义的
CREATE TABLE student (
  sid varchar(10) NOT NULL,
  sName varchar(20) DEFAULT NULL,
  sAge datetime DEFAULT '1980-10-12 23:12:36',
  sSex varchar(10) DEFAULT NULL,
  PRIMARY KEY (sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;





CREATE TABLE course (
  cid varchar(10) NOT NULL,
  cName varchar(10) DEFAULT NULL,
  tid int(20) DEFAULT NULL,
  PRIMARY KEY (cid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;





CREATE TABLE sc (
  sid varchar(10) DEFAULT NULL,
  cid varchar(10) DEFAULT NULL,
  score int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;





CREATE TABLE taacher (
  tid int(10) DEFAULT NULL,
  tName varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



insert  into taacher(tid,tName) values (1,'李老师'),(2,'何以琛'),(3,'叶平');

insert  into student(sid,sName,sAge,sSex) values ('1001','张三丰','1980-10-12 23:12:36','男'),
('1002','张无极','1995-10-12 23:12:36','男'),
('1003','李奎','1992-10-12 23:12:36','女'),
('1004','李元宝','1980-10-12 23:12:36','女'),
('1005','李世明','1981-10-12 23:12:36','男'),
('1006','赵六','1986-10-12 23:12:36','男'),
('1007','田七','1981-10-12 23:12:36','女');

insert  into sc(sid,cid,score) values ('1001','001',80),('1001','002',60),('1001','003',75),('1002','001',85),
('1002','002',70),('1003','004',100),('1003','001',90),('1003','002',55),('1004','002',65),('1004','003',60);

insert  into course(cid,cName,tid) values ('001','企业管理',3),
('002','马克思',3),('003','UML',2),('004','数据库',1),('005','英语',1);








查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名


SELECT sc5.sid
FROM
(SELECT sc1.sid
FROM sc sc1 GROUP BY sid HAVING count(*) = (SELECT count(*) FROM (SELECT * FROM sc WHERE sid=1002) sc2)) sc5,

(SELECT sc3.sid
FROM sc sc3,
(SELECT cid,sid FROM sc WHERE sid=1002) sc4 WHERE sc3.cid=sc4.cid GROUP BY sid HAVING count(*)=2) sc6

WHERE sc5.sid=sc6.sid;











按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩,
按如下形式显示: 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分




SELECT sid '学生id',avg(score) '有效平均分',
(SELECT score FROM sc WHERE cid='004' AND sid=sc1.sid) '数据库',
(SELECT score FROM sc WHERE cid='001' AND sid=sc1.sid) '企业管理',
(SELECT score FROM sc WHERE cid='005'AND sid=sc1.sid) '英语',
count(*) '有效课程数'
FROM sc sc1
GROUP BY sid
ORDER BY avg(score);












用到了不认识的函数,现在的水平笔试应该不会到这个水平


19、按各科平均成绩从低到高和及格率的百分数从高到低排序 
oracle>
SELECT t.cid AS 课程号,MAX(course.Cname)AS 课程名,nvl(AVG(score),0) AS 平均成绩 
,100 * SUM(CASE WHEN nvl(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数 
FROM SC T,Course 
WHERE t.cid=course.cid 
GROUP BY t.cid 
ORDER BY 100 * SUM(CASE WHEN nvl(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC 
Mysql>
SELECT t.cid AS 课程号,MAX(course.Cname)AS 课程名,IFNULL(AVG(score),0) AS 平均成绩 
,100 * SUM(CASE WHEN IFNULL(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数 
FROM SC T,Course 
WHERE t.cid=course.cid 
GROUP BY t.cid 
ORDER BY 100 * SUM(CASE WHEN IFNULL(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC 










查询如下课程平均成绩和及格率的百分数("1行"显示): 企业管理(001),马克思(002),OO&UML003),数据库(004SELECT SUM(CASE WHEN cid ='001' THEN score ELSE 0 END)/SUM(CASE cid WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分 
,100 * SUM(CASE WHEN cid = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数 
,SUM(CASE WHEN cid = '002' THEN score ELSE 0 END)/SUM(CASE cid WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分 
,100 * SUM(CASE WHEN cid = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = '002' THEN 1 ELSE 0 END) AS 马克思及格百分数 
,SUM(CASE WHEN cid = '003' THEN score ELSE 0 END)/SUM(CASE cid WHEN '003' THEN 1 ELSE 0 END) AS UML平均分 
,100 * SUM(CASE WHEN cid = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = '003' THEN 1 ELSE 0 END) AS UML及格百分数 
,SUM(CASE WHEN cid = '004' THEN score ELSE 0 END)/SUM(CASE cid WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分 
,100 * SUM(CASE WHEN cid = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = '004' THEN 1 ELSE 0 END) AS 数据库及格百分数 
FROM SC 


















231981年出生的学生名单(注:Student表中Sage列的类型是datetime) 
Mysql>
select Sname, CONVERT(char (11),DATEPART(year,Sage)) as age 
from student 
where CONVERT(char(11),DATEPART(year,Sage))='1981'; 
Oracle>
select * from student where substr(to_char(sage,'yyyy-MM-dd'),1,4)= '1981'







# 这么写实际上是有bug的,如果出现该课程所有成绩都相同并且课程数大于2的时候,就不会显示该课程成绩了

查询每门功课成绩最好的前两名 
SELECT * 
FROM sc t1
WHERE (
  SELECT COUNT(*)
  FROM sc t2
  WHERE t1.cid=t2.cid
  AND t2.score>=t1.score
) <=2 ORDER BY t1.cid






// 行转列
CREATE TABLE stu_score (
  grade_id varchar(10) DEFAULT NULL,
  subject_name varchar(10) DEFAULT NULL,
  max_score int(10) DEFAULT NULL
)

insert  into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('1','语文',98);
insert  into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('2','数学',95);
insert  into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('2','政治',87);
insert  into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('5','语文',97);
insert  into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('5','数学',100);
insert  into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('5','政治',92);



SELECT
 CASE grade_id WHEN 1 THEN '一年级' 
		WHEN 2 THEN '二年级'
		WHEN 5 THEN '五年级'
 END AS '年级',
 MAX(IFNULL(CASE subject_name WHEN '语文' THEN max_score END,0)) AS '语文',
 MAX(IFNULL(CASE subject_name WHEN '数学' THEN max_score END,0)) AS '数学',
 MAX(IFNULL(CASE subject_name WHEN '政治' THEN max_score END,0)) AS '政治'
FROM stu_score
GROUP BY grade_id

部分知识引用自:
https://zhuanlan.zhihu.com/p/38354000
https://blog.csdn.net/hellosweet1/article/details/81637307

“老人走失后,能被寻找到的点滴踪迹是碎片式的,志愿救援队要尽量多寻找他们出现的点,再将点连成线。但每当快连成线了,线索却经常断掉。”马健说。有一次他们寻找一位老人,查到他进了一个胡同,前一个监控能看到他,隔了一段距离的监控里,就没影儿了。第二天再把范围缩小,查到中间的一个商店,发现老人晚上站在这家店的玻璃门前,对着自己的影子说了五个小时的话。可这时机一错过,他们一直跑到石景山的水泥厂才找到他。

https://new.qq.com/omn/20220329/20220329A0AQ0000.html?pgv_ref=aio2015&ptlang=2052

  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值