您的位置:新葡亰496net > 网络数据库 > 面试之数据库基础练习

面试之数据库基础练习

发布时间:2019-10-30 00:39编辑:网络数据库浏览(146)

    简介

    新近在操演SQL基础,首先惊讶一下,在机械上写和在纸上写依旧有分其余。

    正文的幼学壮行标题请点击此链接实行查看:


    提要:本文只列举个人以为有个别难度,且有必供给重复知识点的标题,详细还请查看上边所给的链接实行练习。

    Student(Sid,Sname,Sage,Ssex) 学生表Course(Cid,Cname,Tid) 课程表SC(Sid,Cid,score) 成绩表Teacher(Tid,Tname) 教师表

    练习题

    难点:1、查询“001”课程比“002”课程成绩高的持有学员的学号;

    (1)查询学过“001”並且也学过数码“002”课程的同桌的学号、姓名;

    1. 思路1:使用 intersect (intersect的使用面试之数据库基础练习。)
    2. 思路1:使用 EXISTS (EXISTS的用法)
    --解法一:求交集
    select s.S#,s.Sname
    from Student s,SC sc
    where s.S#=sc.S# and sc.C#='001'
    intersect
    select s.S#,s.Sname
    from Student s,SC sc
    where s.S#=sc.S# and sc.C#='002'
    --解法二:使用exists
    select s.S#,s.Sname
    from Student s,SC sc
    where s.S#=sc.S# and sc.C#='001' and exists
    (
        select * from SC sc2 where sc.S#=sc2.S# and sc2.C#='002'
    )
    

    小知识:EXISTS与IN的采纳作用的主题材料,常常情形下使用exists要比in作用高,因为IN不走索引,但要看其实情况具体使用:
    IN相符于外界大而内表小的景况;EXISTS相符于表面小而内表大的情景。

    select a.Sid from (select Sid,score from SC where Cid='001') a (select Sid,score from SC where Cid='002') b wherea.score>b.score and a.Sid=b.Sid; 
    

    (7)查询学过“叶平”老师所教的全数课的同校的学号、姓名

    面试之数据库基础练习。解析:那题要留意一个老师只怕教多门课

    1. 先在成就单上询问到老师为“叶平”老师的音讯,通过学子ID进行分组并获得试验门数
    2. 接下来总计课程表上的“叶平”老师的课程数
      3.率先个规范的上学的小孩子考试门数跟第一个标准的“叶平”老师所教学程数实行自己检查自纠,相等即时答案
    select s.S#,s.Sname 
    from Student s
    where s.S# in 
    (
        select sc.S# 
        from SC sc,Course c,Teacher t
        where c.C#=sc.C# and c.T#=t.T# and t.Tname='叶平'
        group by sc.S#
        having COUNT(sc.C#)=
        (
            select COUNT(c1.C#) 
            from Course c1,Teacher t1 
            where c1.T#=t1.T# and t1.Tname='叶平'
        )
    )
    

    2、查询平均成绩超乎60分的同室的学号和平均战绩;

    (14)查询和“002”号的校友学习的学科完全相仿的别的同学学号和人名

    只顾:查询的是跟“002”完全相通,且是别的,记得得消释“002”

    1. 首先鲜明学子课程范围在“002”学子所选的科目范围内;
    2. 接下来正是对待他们间的科目数。

    产生地点多个规范化就能够鲜明他们所选的有所课程是不是都等于了

    select s.S#,s.Sname 
    from Student s
    where s.S#!='002' and s.S# in 
    (
        select distinct(S#) from SC
        where C# in (select C# from SC where S#='002')
        group by S#
        having COUNT(distinct C#)=
        (
            select COUNT(distinct C#) from SC
            where S#='002'
        )
    )
    
     select Sid,avg from sc group by Sid having avg >60; 
    

    (16)向SC表中插入一些笔录,那个记录供给切合以下条件:①未有上过编号“002”课程的同室学号;②插入“002”号课程的平分成绩

    1. 先查询第叁个原则的学子,因为插入的学号必要运用;
    2. 然后拿走课程“002”的平分分,这里是猎取分数用于插入;

    最后答案就义正辞严了:

    INSERT INTO dbo.Sc
    SELECT s.S#,'002',(SELECT AVG(score) FROM dbo.Sc WHERE C#='002')
    from  dbo.Student s
    WHERE s.S# NOT IN(SELECT DISTINCT(sc.S#) FROM dbo.Sc sc WHERE sc.C#='002')
    

    3、查询全体同学的学号、姓名、选课数、总战绩;

    (17)按平均成绩从低到高显示全数学子的“语文”、“数学”、“立陶宛(Lithuania)语”三门的科目战绩,按如下情势显得: 学子ID,语文,数学,意大利语,有效课程数,有效平均分

    这里提到到行转列的文化,在查询语数英那三列的时候使用了子查询和主查询的关联合有限支撑证了与其余列数据的关联性

    1. 请各位要留意对分组(Group by)的定义相比清晰,着重就在学子的ID:S#
    2. 行转列知识:
    select t.S# as '学生ID',
    (select Score from SC where S#=t.S# and C#='002') as '语文',
    (select Score from SC where S#=t.S# and C#='003') as '数学',
    (select Score from SC where S#=t.S# and C#='004') as '英语',
    COUNT(t.C#) as '有效课程数',
    AVG(t.Score) as '有效平均分'
    from SC t
    group by t.S#
    order by AVG(t.Score)
    
     select Student.Sid,Student.Sname,count,sum from Student left Outer join SC on Student.Sid=SC.Sid group by Student.Sid,Sname 
    

    (19)按各科平均成绩从低到高和及格率的比例从高到低依次

    1. CASE WHEN *** THEN *** ELSE *** END 的使用
    2. 及格率SUM(PassedCounts)/COUNT(AllCounts)的计算
    3. 另外,这里[Percent(%)]可以应用100 * SUM(CASE WHEN ISNULL(sc.Score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*)替代。
     SELECT sc.C#,c.Cname,AVG(sc.score) AS avgScore,100*SUM(CASE WHEN ISNULL(score,0)>=60 THEN 1 ELSE 0 END )/COUNT(*) 
     FROM dbo.Sc sc,dbo.Course c
     WHERE sc.C#=c.C#
     GROUP BY sc.C#,c.Cname
     order BY AVG(sc.score)
    

    4、查询姓“李”的教师职员和工人的个数;

    (20)查询如下课程平均战绩和及格率的比重(备注:要求在1行内展现): 集团管理(002),OO&UML (003),数据库(004)

    那边也是涉及行转列的标题,由于列的多寡都是对峙独立的,也就从不关联子查询的的主题材料了

    select 
    SUM(CASE WHEN C#='002' THEN Score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) as '企业管理平均分',
    100 * SUM(CASE WHEN C#='002' and Score>=60 THEN 1 ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) as '企业管理及格百分比',
    SUM(CASE WHEN C#='003' THEN Score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) as 'OO&UML平均分',
    100 * SUM(CASE WHEN C#='003' and Score>=60 THEN 1 ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) as 'OO&UML及格百分比',
    SUM(CASE WHEN C#='004' THEN Score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) as '数据库平均分',
    100 * SUM(CASE WHEN C#='004' and Score>=60 THEN 1 ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) as '数据库及格百分比'
    from SC
    
     select count(distinct from Teacher where Tname like '李%'; 
    

    (24)查询学一生均战表及其排行;

    近似轻巧,排名这里实在不佳弄。

    1. 先是采用group by获取须要的字段(学子ID,平均分)
    2. 下一场这段sql须要再拿来新建并拓宽对照
    select s.S#,s.Sname,T2.AvgScore,
        (select COUNT(AvgScore) from 
        (select S#,AVG(Score) as 'AvgScore' from SC group by S#) as T1 
        where T2.AvgScore<T1.AvgScore) 1 as 'Rank'
    from 
        (select S#,AVG(Score) as 'AvgScore' from SC
        group by S#) as T2,
        Student s
    where s.S#=T2.S#
    order by AvgScore desc
    

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

    (25)查询各科成绩前三名的记录:(不考虑战表并列情状)

    1. 创立子查询获得每科前三名,这里必要跟主查询实行关联,而关乎的字段就是科目字段C#
    2. 主查询难度超级小,查询条件包涵分数区域在子查询的分数区域内同临时间主和子查询的科目实行关联
    select sc.C#,c.Cname,sc.S#,s.Sname,sc.Score 
    from Student s,SC sc,Course c
    where sc.C#=c.C# and sc.S#=s.S# and sc.Score in
    (
        select top 3 Score from SC sc2
        where sc.C#=sc2.C#
        Order by Score desc
    )
    order by sc.C#,sc.Score desc
    

    以上正是本身用纸张手写的时候境遇有难度的问题,希望能给诸位一些参谋。

    select Student.Sid,Student.Sname from Student where Sid not in (select distinct from SC,Course,Teacher where SC.Cid=Course.Cid and Teacher.Tid=Course.Tid and Teacher.Tname='叶平'); 
    

    6、查询学过“001”况兼也学过数码“002”课程的同校的学号、姓名;

    select Student.Sid,Student.Sname from Student,SC where Student.Sid=SC.Sid and SC.Cid='001'and exists( Select * from SC as SC_2 where SC_2.Sid=SC.Sid and SC_2.Cid='002'); 
    

    新葡亰496net,7、查询学过“叶平”老师所教的全部课的同窗的学号、姓名;

    SELECT Sid, SnameFROM StudentWHERE Sid IN ( SELECT Sid FROM SC, Course, Teacher WHERE SC.Cid = Course.Cid AND Teacher.Tid = Course.Tid AND Teacher.Tname = '叶平' GROUP BY Sid HAVING count = ( SELECT count FROM Course, Teacher WHERE Teacher.Tid = Course.Tid AND Tname = '叶平' ) );
    

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

    SELECT Sid, SnameFROM ( SELECT Student.Sid, Student.Sname, score, ( SELECT score FROM SC SC_2 WHERE SC_2.Sid = Student.Sid AND SC_2.Cid = '002' ) score2 FROM Student, SC WHERE Student.Sid = SC.Sid AND Cid = '001' ) S_2WHERE score2 < score;
    

    9、查询全部课程战表小于60分的校友的学号、姓名;

    SELECT Sid, SnameFROM StudentWHERE Sid NOT IN ( SELECT Student.Sid FROM Student, SC WHERE S.Sid = SC.Sid AND score > 60 );
    

    10、查询未有学全全数课的同学的学号、姓名;

    SELECT Student.Sid, Student.SnameFROM Student, SCWHERE Student.Sid = SC.SidGROUP BY Student.Sid, Student.SnameHAVING count < (SELECT count FROM Course);
    

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

    SELECT Sid, SnameFROM Student, SCWHERE Student.Sid = SC.SidAND Cid IN SELECT CidFROM SCWHERE Sid = '1001';
    

    12、查询起码学过学号为“001”同学全部一门课的别的同学学号和姓名;

    SELECT DISTINCT SC.Sid, SnameFROM Student, SCWHERE Student.Sid = SC.SidAND Cid IN ( SELECT Cid FROM SC WHERE Sid = '001');
    

    13、把“SC”表中“叶平”老师教的课的成就都更换为此课程的平均成绩;

    UPDATE SCSET score = ( SELECT avg(SC_2.score) FROM SC SC_2 WHERE SC_2.Cid = SC.Cid)FROM Course, TeacherWHERE Course.Cid = SC.CidAND Course.Tid = Teacher.TidAND Teacher.Tname = '叶平');
    

    14、查询和“1002”号的同学学习的学科完全相通的其余同学学号和人名;select Sid from SC where Cid in (select Cid from SC where Sid='1002')group by Sid having count=(select count from SC where Sid='1002');15、删除学习“叶平”老师课的SC表记录;Delect SCfrom course ,Teacherwhere Course.Cid=SC.Cid and Course.Tid= Teacher.Tid and Tname='叶平';16、向SC表中插入一些笔录,那几个记录必要切合以下规范:没有上过编号“003”课程的同班学号、2、号课的平均成绩;Insert SC select Sid,'002',(Select avgfrom SC where Cid='002') from Student where Sid not in (Select Sid from SC where Cid='002');17、按平均战表从高到低显示全数学子的“数据库”、“公司处理”、“保加乌兰巴托语”三门的科目成绩,按如下格局显得: 学子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='006') AS 德文,COUNT AS 有效课程数, AVG AS 平均战表FROM SC AS tGROUP BY SidO纳瓦拉DER BY avg18、查询各科战表最高和压低的分:以如下形式展现:课程ID,最高分,最低分SELECT L.Cid As 课程ID,L.score AS 最高分,奥迪Q7.score AS 最低分FROM SC L ,SC AS 昂CoraWHERE L.Cid = 凯雷德.Cid andL.score = (SELECT MAXFROM SC AS IL,Student AS IMWHERE L.Cid = IL.Cid and IM.Sid=IL.SidGROUP BY IL.Cid)AND奥迪Q5.Score = (SELECT MINFROM SC AS IRAV4WHERE 揽胜.Cid = I宝马X3.CidGROUP BY IRubicon.Cid);19、按各科平均战绩从低到高和及格率的百分比从高到低依次SELECT t.Cid AS 课程号,max(course.Cname)AS 课程名,isnull(AVG AS 平均战表,100 * SUM(CASE WHEN isnull>=60 THEN 1 ELSE 0 END)/COUNT AS 及格百分数FROM SC T,Coursewhere t.Cid=course.CidGROUP BY t.CidO索罗德DERubicon BY 100 * SUM(CASE WHEN isnull>=60 THEN 1 ELSE 0 END)/COUNT DESC20、查询如下课程平均战表和及格率的比重: 集团管理,马克思,OO&UML ,数据库SELECT 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 Marx平均分,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

    21、查询分歧老师所教区别科目平均分从高到低显示SELECT max AS 教授ID,MAX AS 教授姓名,C.Cid AS 课程ID,MAX AS 课程名称,AVG AS 平均战表FROM SC AS T,Course AS C ,Teacher AS Zwhere T.Cid=C.Cid and C.Tid=Z.TidGROUP BY C.CidO奥迪Q7DEEvoque BY AVG DESC22、查询如下课程战表第 3 名到第 6 名的上学的小孩子成绩单:集团管理,马克思,UML ,数据库[学生ID],[学子姓名],公司管理,Marx,UML,数据库,平均成绩SELECT DISTINCT top 3SC.Sid As 学子学号,Student.Sname AS 学子姓名 ,T1.score AS 集团管理,T2.score AS 马克思,T3.score AS UML,T4.score AS 数据库,ISNULL(T1.score,0) ISNULL(T2.score,0) ISNULL(T3.score,0) ISNULL(T4.score,0) as 总分FROM Student,SC LEFT JOIN SC AS T1ON SC.Sid = T1.Sid AND T1.Cid = '001'LEFT JOIN SC AS T2ON SC.Sid = T2.Sid AND T2.Cid = '002'LEFT JOIN SC AS T3ON SC.Sid = T3.Sid AND T3.Cid = '003'LEFT JOIN SC AS T4ON SC.Sid = T4.Sid AND T4.Cid = '004'WHERE student.Sid=SC.Sid andISNULL(T1.score,0) ISNULL(T2.score,0) ISNULL(T3.score,0) ISNULL(T4.score,0)NOT IN(SELECTDISTINCTTOP 15 WITH TIESISNULL(T1.score,0) ISNULL(T2.score,0) ISNULL(T3.score,0) ISNULL(T4.score,0)FROM scLEFT JOIN sc AS T1ON sc.Sid = T1.Sid AND T1.Cid = 'k1'LEFT JOIN sc AS T2ON sc.Sid = T2.Sid AND T2.Cid = 'k2'LEFT JOIN sc AS T3ON sc.Sid = T3.Sid AND T3.Cid = 'k3'LEFT JOIN sc AS T4ON sc.Sid = T4.Sid AND T4.Cid = 'k4'O大切诺基DEEvoque BY ISNULL(T1.score,0) ISNULL(T2.score,0)

    • ISNULL(T3.score,0) ISNULL(T4.score,0) DESC);

    23、总括列印各科战表,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]SELECT SC.Cid as 课程ID, Cname as 课程名称,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85],SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70],SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60],SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -]FROM SC,Coursewhere SC.Cid=Course.CidGROUP BY SC.Cid,Cname;

    24、查询学一生均战绩及其排名SELECT 1 (SELECT COUNT( distinct 平均成绩)FROM (SELECT Sid,AVG AS 平均成绩FROM SCGROUP BY Sid) AS T1WHERE 平分战表 > T2.平均战表) as 排行,Sid as 学子学号,平均成绩FROM (SELECT Sid,AVG 平均成绩FROM SCGROUP BY Sid) AS T2ORubiconDERAV4 BY 平均成绩 desc;

    25、查询各科战表前三名的记录:(不思索成绩并列情状)SELECT t1.Sid as 学子ID,t1.Cid as 课程ID,Score as 分数FROM SC t1WHERE score IN (SELECT TOP 3 scoreFROM SCWHERE t1.Cid= CidOKugaDE索罗德 BY score DESC)O昂科拉DE卡宴 BY t1.Cid;26、查询每门科目被选修的学习者数select Cid,count from sc group by Cid;27、查询出只选修了一门学科的全方位学子的学号和姓名select SC.Sid,Student.Sname,count AS 选课数from SC ,Studentwhere SC.Sid=Student.Sid group by SC.Sid ,Student.Sname having count=1;28、查询男子、女子人数Select count as 匹内人数 from Student group by Ssex having Ssex='男';Select count as 女孩子人数 from Student group by Ssex having Ssex='女';29、查询姓“张”的学员名单SELECT Sname FROM Student WHERE Sname like '张%';30、查询同名同种性别学子名单,并总括同名家数select Sname,count from Student group by Sname having count>1;;31、1984年诞生的学子名单(注:Student表中Sage列的门类是datetime)select Sname, CONVERT,DATEPART(year,Sage)) as agefrom studentwhere CONVERT,DATEPART(year,Sage))='一九八二';32、查询每门学科的平均战表,结果按平均成绩升序排列,平均战表同样时,按学科号降序排列Select Cid,Avg from SC group by Cid order by Avg,Cid DESC ;33、查询平均战绩超过85的兼具学员的学号、姓名和平均成绩select Sname,SC.Sid ,avgfrom Student,SCwhere Student.Sid=SC.Sid group by SC.Sid,Sname having avg>85;34、查询课程名叫“数据库”,且分数低于60的上学的小孩子姓名和分数Select Sname,isnullfrom Student,SC,Coursewhere SC.Sid=Student.Sid and SC.Cid=Course.Cid and Course.Cname='数据库'and score <60;35、查询全数学子的选课情形;SELECT SC.Sid,SC.Cid,Sname,CnameFROM SC,Student,Coursewhere SC.Sid=Student.Sid and SC.Cid=Course.Cid ;36、查询别的一门学科成绩在70分以上的人名、课程名称和分数;SELECT distinct student.Sid,student.Sname,SC.Cid,SC.scoreFROM student,ScWHERE SC.score>=70 AND SC.Sid=student.Sid;37、查询比不上格的科目,并按学科号从大到小排列select Cid from sc where scor e <60 order by Cid ;38、查询课程编号为003且课程成绩在80分以上的学习者的学号和姓名;select SC.Sid,Student.Sname from SC,Student where SC.Sid=Student.Sid and Score>80 and Cid='003';39、求选了课程的学员人数select count from sc;40、查询选修“叶平”老师所授课程的学习者中,战绩最高的学习者姓名及其战绩select Student.Sname,scorefrom Student,SC,Course C,Teacherwhere Student.Sid=SC.Sid and SC.Cid=C.Cid and C.Tid=Teacher.Tid and Teacher.Tname='叶平' and SC.score=(select maxfrom SC where Cid=C.Cid );41、查询各种科目及相应的选修人数select count from sc group by Cid;42、查询分裂科目成绩同样的学习者的学号、课程号、学子成绩select distinct A.Sid,B.score from SC A ,SC B where A.Score=B.Score and A.Cid <>B.Cid ;43、查询每门功战表最棒的前两名SELECT t1.Sid as 学子ID,t1.Cid as 课程ID,Score as 分数FROM SC t1WHERE score IN (SELECT TOP 2 scoreFROM SCWHERE t1.Cid= CidO奥迪Q3DE讴歌ZDX BY score DESC)O牧马人DEEnclave BY t1.Cid;44、总计每门课程的学生选修人数(超过10人的科目才计算)。须要输出课程号和选修人数,查询结果按人头降序排列,查询结果按人口降序排列,若人数相近,按学科号升序排列select Cid as 课程号,count as 人数from scgroup by Cidorder by count desc,Cid45、检索最少选修两门学科的上学的小孩子学号select Sidfrom scgroup by Sidhaving count > = 246、查询任何学员都选修的教程的课程号和课程名select Cid,Cnamefrom Coursewhere Cid in (select Cid from sc group by Cid)47、查询没学过“叶平”老师助教的任一门科目标上学的儿童姓名select Sname from Student where Sid not in (select Sid from Course,Teacher,SC where Course.Tid=Teacher.Tid and SC.Cid=course.Cid and Tname='叶平');48、查询两门以上不如格课程的同窗的学号及其平均战绩select Sid,avg(isnull from SC where Sid in (select Sid from SC where score <60 group by Sid having count>2)group by Sid;49、检索“004”课程分数小于60,按分数降序排列的同桌学号select Sid from SC where Cid='004'and score <60 order by score desc;50、删除“002”同学的“001”课程的大成

    本文由新葡亰496net发布于网络数据库,转载请注明出处:面试之数据库基础练习

    关键词: