经典SQL面试题.docx
- 文档编号:26680009
- 上传时间:2023-06-21
- 格式:DOCX
- 页数:12
- 大小:20.22KB
经典SQL面试题.docx
《经典SQL面试题.docx》由会员分享,可在线阅读,更多相关《经典SQL面试题.docx(12页珍藏版)》请在冰豆网上搜索。
经典SQL面试题
表(MYSQL)
Student(sid,Sname,Sage,Ssex)学生表
CREATETABLEstudent(
sidvarchar(10)NOTNULL,
sNamevarchar(20)DEFAULTNULL,
sAgedatetimeDEFAULT'1980-10-1223:
12:
36',
sSexvarchar(10)DEFAULTNULL,
PRIMARYKEY(sid)
)ENGINE=InnoDBDEFAULTCHARSET=utf8;
Course(cid,Cname,tid)课程表
CREATETABLEcourse(
cidvarchar(10)NOTNULL,
cNamevarchar(10)DEFAULTNULL,
tidint(20)DEFAULTNULL,
PRIMARYKEY(cid)
)ENGINE=InnoDBDEFAULTCHARSET=utf8;
SC(sid,cid,score)成绩表
CREATETABLEsc(
sidvarchar(10)DEFAULTNULL,
cidvarchar(10)DEFAULTNULL,
scoreint(10)DEFAULTNULL
)ENGINE=InnoDBDEFAULTCHARSET=utf8;
Teacher(tid,Tname)教师表
CREATETABLEtaacher(
tidint(10)DEFAULTNULL,
tNamevarchar(10)DEFAULTNULL
)ENGINE=InnoDBDEFAULTCHARSET=utf8;
数据:
(MySQL)
insertintotaacher(tid,tName)values(1,'李老师'),(2,'何以琛'),(3,'叶平');
insertintostudent(sid,sName,sAge,sSex)values('1001','张三丰','1980-10-1223:
12:
36','男'),('1002','张无极','1995-10-1223:
12:
36','男'),('1003','李奎','1992-10-1223:
12:
36','女'),('1004','李元宝','1980-10-1223:
12:
36','女'),('1005','李世明','1981-10-1223:
12:
36','男'),('1006','赵六','1986-10-1223:
12:
36','男'),('1007','田七','1981-10-1223:
12:
36','女');
insertintosc(sid,cid,score)values('1','001',80),('1','002',60),('1','003',75),('2','001',85),('2','002',70),('3','004',100),('3','001',90),('3','002',55),('4','002',65),('4','003',60);
insertintocourse(cid,cName,tid)values('001','企业管理',3),('002','马克思',3),('003','UML',2),('004','数据库',1),('005','英语',1);
ORACLE(表+数据)
CREATETABLEstudent(
sidvarchar2(10)NOTNULL,
sNamevarchar2(20)DEFAULTNULL,
sAgedate,
sSexvarchar2(10)DEFAULTNULL,
PRIMARYKEY(sid)
)
CREATETABLEcourse(
cidvarchar2(10)NOTNULL,
cNamevarchar2(10)DEFAULTNULL,
tidnumber(20)DEFAULTNULL,
PRIMARYKEY(cid)
)
CREATETABLEsc(
sidvarchar2(10)DEFAULTNULL,
cidvarchar2(10)DEFAULTNULL,
scorenumber(10)DEFAULTNULL
)
CREATETABLEteacher(
tidnumber(10)DEFAULTNULL,
tNamevarchar2(10)DEFAULTNULL
)
insertintocourse(cid,cName,tid)values('001','企业管理',3);
insertintocourse(cid,cName,tid)values('002','马克思',3);
insertintocourse(cid,cName,tid)values('004','数据库',1);
insertintocourse(cid,cName,tid)values('005','英语',1);
insertintosc(sid,cid,score)values('1001','001',80);
insertintosc(sid,cid,score)values('1001','002',60);
insertintosc(sid,cid,score)values('1001','003',70);
insertintosc(sid,cid,score)values('1002','001',85);
insertintosc(sid,cid,score)values('1002','002',70);
insertintosc(sid,cid,score)values('1003','004',90);
insertintosc(sid,cid,score)values('1003','001',90);
insertintosc(sid,cid,score)values('1003','002',99);
insertintosc(sid,cid,score)values('1004','002',65);
insertintosc(sid,cid,score)values('1004','003',50);
insertintosc(sid,cid,score)values('1005','005',80);
insertintosc(sid,cid,score)values('1005','004',70);
insertintosc(sid,cid,score)values('1003','003',10);
insertintosc(sid,cid,score)values('1003','005',10);
insertintostudent(sid,sName,sAge,sSex)values('1001','张三丰',to_date('1980-10-1223:
12:
36','YYYY-MM-DDHH24:
MI:
SS'),'男');
insertintostudent(sid,sName,sAge,sSex)values('1002','张无极',to_date('1995-10-1223:
12:
36','YYYY-MM-DDHH24:
MI:
SS'),'男');
insertintostudent(sid,sName,sAge,sSex)values('1003','李奎',to_date('1992-10-1223:
12:
36','YYYY-MM-DDHH24:
MI:
SS'),'女');
insertintostudent(sid,sName,sAge,sSex)values('1004','李元宝',to_date('1980-10-1223:
12:
36','YYYY-MM-DDHH24:
MI:
SS'),'女');
insertintostudent(sid,sName,sAge,sSex)values('1005','李世明',to_date('1981-10-1223:
12:
36','YYYY-MM-DDHH24:
MI:
SS'),'男');
insertintostudent(sid,sName,sAge,sSex)values('1006','赵六',to_date('1986-10-1223:
12:
36','YYYY-MM-DDHH24:
MI:
SS'),'男');
insertintostudent(sid,sName,sAge,sSex)values('1007','田七',to_date('1981-10-1223:
12:
36','YYYY-MM-DDHH24:
MI:
SS'),'女');
insertintoteacher(tid,tName)values(1,'李老师');
insertintoteacher(tid,tName)values(2,'何以琛');
insertintoteacher(tid,tName)values(3,'叶平');
问题:
1.查询“001”课程比“002”课程成绩高的所有学生的学号;
selecta.sidfrom(selectsid,scorefromSCwherecid='001')a,(selectsid,score
fromSCwherecid='002')b
wherea.score>b.scoreanda.sid=b.sid;
2、查询平均成绩大于60分的同学的学号和平均成绩;
selectsid,avg(score)
fromsc
groupbysidhavingavg(score)>60;
3、查询所有同学的学号、姓名、选课数、总成绩;
selectStudent.sid,Student.Sname,count(SC.cid),sum(score)
fromStudentleftOuterjoinSConStudent.sid=SC.sid
groupbyStudent.sid,Sname
4、查询姓“李”的老师的个数;
selectcount(distinct(Tname))
fromTeacher
whereTnamelike'李%';
5、查询没学过“叶平”老师课的同学的学号、姓名;
selectStudent.sid,Student.Sname
fromStudent
wheresidnotin(selectdistinct(SC.sid)fromSC,Course,TeacherwhereSC.cid=Course.cidandTeacher.tid=Course.tidandTeacher.Tname='叶平');
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
A:
selectStudent.sid,Student.SnamefromStudent,SCwhereStudent.sid=SC.sidandSC.cid='001'andexists(Select*fromSCasSC_2whereSC_2.sid=SC.sidandSC_2.cid='002');
B:
SELECTs.sid,s.sName
FROMstudents,(SELECTsid,COUNT(cid)FROMscWHEREcidIN('001','002')GROUPBYsidHAVINGCOUNT(cid)>=2)tWHEREs.sid=t.sid
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
selectsid,Sname
fromStudent
wheresidin(selectsidfromSC,Course,TeacherwhereSC.cid=Course.cidandTeacher.tid=Course.tidandTeacher.Tname='叶平'groupbysidhavingcount(SC.cid)=(selectcount(cid)fromCourse,TeacherwhereTeacher.tid=Course.tidandTname='叶平'));
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
1>Selectsid,Snamefrom(selectStudent.sid,Student.Sname,score,(selectscorefromSCSC_2whereSC_2.sid=Student.sidandSC_2.cid='002')score2
fromStudent,SCwhereStudent.sid=SC.sidandcid='001')S_2wherescore2 2>SELECTs.sid,s.sNameFROMstudents, (SELECTsid,scoreFROMscWHEREcid='001')sc_1, (SELECTsid,scoreFROMscWHEREcid='002')sc_2 WHEREsc_1.sid=sc_2.sidANDs.sid=sc_2.sidANDsc_2.score 9、查询所有课程成绩小于60分的同学的学号、姓名; selectsid,Sname fromStudent wheresidnotin(selectStudent.sidfromStudent,SCwhereS.sid=SC.sidandscore>60); 10、查询没有学全所有课的同学的学号、姓名; 1> selectStudent.sid,Student.Sname fromStudent,SC whereStudent.sid=SC.sidgroupbyStudent.sid,Student.Snamehavingcount(cid)<(selectcount(cid)fromCourse); 2> SELECTs.sid,s.snameFROMstudents, (SELECTsid,COUNT(cid)FROMscGROUPBYsidHAVINGCOUNT(cid)<(SELECTCOUNT(cid)FROMcourse))t WHEREs.sid=t.sid 11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名; selectsid,SnamefromStudent,SCwhereStudent.sid=SC.sidandcidin(selectcidfromSCwheresid='1001'); 13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩; UPDATEsc,(SELECTc.cid,AVG(score)avgsFROMsc,coursec,teachertWHEREsc.cid=c.cidAND c.tid=t.tidANDt.tName='叶平'GROUPBYc.cid)sc_2SETsc.score=sc_2.avgsWHEREsc.cid=sc_2.cid 14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名; selectsidfromSCwherecidin(selectcidfromSCwheresid='1002') groupbysidhavingcount(*)=(selectcount(*)fromSCwheresid='1002'); 15、删除学习“叶平”老师课的SC表记录; DELETEFROMscWHEREsc.cidIN(SELECTsc.cidFROMcoursec,teachertWHEREsc.cid=c.cidANDc.tid=t.tidANDt.tName='叶平') 17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩,按如下形式显示: 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分 SELECTsidas学生ID (SELECTscoreFROMSCWHERESC.sid=t.sidANDcid='004')AS数据库 (SELECTscoreFROMSCWHERESC.sid=t.sidANDcid='001')AS企业管理 (SELECTscoreFROMSCWHERESC.sid=t.sidANDcid='005')AS英语 COUNT(*)AS有效课程数,AVG(t.score)AS平均成绩 FROMSCASt GROUPBYsid ORDERBYavg(t.score) 18、查询各科成绩最高和最低的分: 以如下形式显示: 课程ID,最高分,最低分 selectcid"课程ID",max(score)"最高分",min(score)"最低分"fromscgroupbycid 19、按各科平均成绩从低到高和及格率的百分数从高到低排序 oracle> SELECTt.cidAS课程号,MAX(course.Cname)AS课程名,nvl(AVG(score),0)AS平均成绩 100*SUM(CASEWHENnvl(score,0)>=60THEN1ELSE0END)/COUNT(*)AS及格百分数 FROMSCT,Course WHEREt.cid=course.cid GROUPBYt.cid ORDERBY100*SUM(CASEWHENnvl(score,0)>=60THEN1ELSE0END)/COUNT(*)DESC Mysql> SELECTt.cidAS课程号,MAX(course.Cname)AS课程名,IFNULL(AVG(score),0)AS平均成绩 100*SUM(CASEWHENIFNULL(score,0)>=60THEN1ELSE0END)/COUNT(*)AS及格百分数 FROMSCT,Course WHEREt.cid=course.cid GROUPBYt.cid ORDERBY100*SUM(CASEWHENIFNULL(score,0)>=60THEN1ELSE0END)/COUNT(*)DESC 20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理(001),马克思(002),OO&UML(003),数据库(004) SELECTSUM(CASEWHENcid='001'THENscoreELSE0END)/SUM(CASEcidWHEN'001'THEN1ELSE0END)AS企业管理平均分 100*SUM(CASEWHENcid='001'ANDscore>=60THEN1ELSE0END)/SUM(CASEWHENcid='001'THEN1ELSE0END)AS企业管理及格百分数 SUM(CASEWHENcid='002'THENscoreELSE0END)/SUM(CASEcidWHEN'002'THEN1ELSE0END)AS马克思平均分 100*SUM(CASEWHENcid='002'ANDscore>=60THEN1ELSE0END)/SUM(CASEWHENcid='002'THEN1ELSE0END)AS马克思及格百分数 SUM(CASEWHENcid='003'THENscoreELSE0END)/SUM(CASEcidWHEN'003'THEN1ELSE0END)ASUML平均分 100*SUM(CASEWHENcid='003'ANDscore>=60THEN1ELSE0END)/SUM(CASEWHENcid='003'THEN1ELSE0END)ASUML及格百分数 SUM(CASEWHENcid='004'THENscoreELSE0END)/SUM(CASEcidWHEN'004'THEN1ELSE0END)AS数据库平均分 100*SUM(CASEWHENcid='004'ANDscore>=60THEN1ELSE0END)/SUM(CASEWHENcid='004'THEN1ELSE0END)AS数据库及格百分数 FROMSC 21、查询不同老师所教不同课程平均分从高到低显示 SELECTMAX(t.tid)"教师ID",MAX(t.tName)"教师姓名",c.cid"课程ID",MAX(c.cName)"课程名称",AVG(sc.score)"平均成绩" FROMsc,coursec,teachertWHEREsc.cid=c.cidANDc.tid=t.tidGROUPBYc.tid,c.cid ORDERBYAVG(sc.score)DESC 23、统计列印各科成绩,各分数段人数: 课程ID,课程名称,[100-85],[85-70],[70-60],[<60] SELECTSC.cidas课程ID,Cnameas课程名称 SUM(CASEWHENscoreBETWEEN85AND100THEN1ELSE0END)AS[100-85] SUM(CASEWHENscoreBETWEEN70AND85THEN1ELSE0END)AS[85-70] SUM(CASEW
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 经典 SQL 试题