SQL面试题.docx
《SQL面试题.docx》由会员分享,可在线阅读,更多相关《SQL面试题.docx(18页珍藏版)》请在冰点文库上搜索。
SQL面试题
删除除了学号字段以外,其它字段都相同的冗余记录,只保留一条!
(也就是要删除凤姐和田七中一条重复数据只留一条)
要求结果数据:
原始数据:
CREATETABLEtbl_students(
idnumber(32)NOTNULL,
namevarchar(10)DEFAULTNULL,
saxvarchar(10)DEFAULTNULL,
agenumber(6)DEFAULTNULL,
PRIMARYKEY(id)
)
insertintotbl_students(id,name,sax,age)values('2','李四','男','21');
insertintotbl_students(id,name,sax,age)values('3','张三','女','17');
insertintotbl_students(id,name,sax,age)values('4','李四','男','12');
insertintotbl_students(id,name,sax,age)values('6','凤姐','女','20');
insertintotbl_students(id,name,sax,age)values('5','凤姐','女','20');
insertintotbl_students(id,name,sax,age)values('7','田七','男','18');
insertintotbl_students(id,name,sax,age)values('1','田七','男','18');
insertintotbl_students(id,name,sax,age)values('8','张三','男','17');
答案:
mySql
DELETEFROMstudentWHEREsidNOTIN(SELECTsidFROM((SELECTMIN(sid)sidFROMstudentGROUPBYsName,sSex))t)
oracle:
DELETEFROMstudentWHEREsidNOTIN(SELECTMIN(sid)sidFROMstudentGROUPBYsName,sSex)
查询各科成绩都及格的学员
(要求查询出参加考试的各科成绩都高于60分,不管参加了多少科考试)
要求结果:
表:
CREATETABLEtbl_score(
idNUMBER(10)NOTNULL,
usernamevarchar(20)DEFAULTNULL,
coursevarchar(20)DEFAULTNULL,
scoreNUMBER(10)DEFAULTNULL,
PRIMARYKEY(id)
)
数据:
insertintotbl_score(id,username,course,score)values('1','张三','语文','50');
insertintotbl_score(id,username,course,score)values('2','张三','数学','80');
insertintotbl_score(id,username,course,score)values('3','张三','英语','90');
insertintotbl_score(id,username,course,score)values('4','李四','语文','70');
insertintotbl_score(id,username,course,score)values('5','李四','数学','80');
insertintotbl_score(id,username,course,score)values('6','李四','英语','80');
insertintotbl_score(id,username,course,score)values('7','王五','语文','50');
insertintotbl_score(id,username,course,score)values('8','王五','英语','70');
insertintotbl_score(id,username,course,score)values('9','赵六','数学','90');
答案:
selectusername,scorfromtblwhereidnotin(selectidfromtblwherescore<60)
表(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”课程成绩高的所有学生的学号;
SELECTDISTINCTa.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_2wherescore22>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.score9、查询所有课程成绩小于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、按各科平均成绩从低到高和及格率的百分数从高到低排序
20、oracle>
SELECTt.