95992828九五至尊2

面试之数据库基础演习

三月 16th, 2019  |  九五至尊老品牌值信赖

九五至尊老品牌值信赖,简介

方今在演练SQL基础,首先感叹一下,在机器上写和在纸上写依旧有分别的。

正文的演练标题请点击此链接进行查看:http://www.cnblogs.com/edisonchou/p/3878135.html


提要:本文只列举个人认为某个难度,且有必须求重温知识点的难点,详细还请查看下边所给的链接进行演练。

简介

近期在练习SQL基础,首先惊叹一下,在机器上写和在纸上写依然有分别的。

正文的勤学苦练标题请点击此链接举办查看:http://www.cnblogs.com/edisonchou/p/3878135.html


提要:本文只列举个人觉得有个别难度,且有必须求重复知识点的题材,详细还请查看上边所给的链接举行演练。

练习题

练习题

(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适合于外部小而内表大的情状。

(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适合于外部小而内表大的情状。

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

解析:那题要留意三个导师或者教多门课

  1. 先在实际业绩单上询问到老师为“叶平”老师的新闻,通过学生ID实行分组并获得试验门数
  2. 下一场总括课程表上的“叶平”老师的教程数
    3.第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='叶平'
    )
)

(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='叶平'
    )
)

(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'
    )
)

(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'
    )
)

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

  1. 先查询第3个原则的学习者,因为插入的学号须求动用;
  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')

(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')

(17)按平均成绩从低到高展现全部学生的“语文”、“数学”、“克罗地亚语”三门的教程成绩,按如下情势显得: 学生ID,语文,数学,罗马尼亚语,有效课程数,有效平均分

那里提到到行转列的学问,在查询语数英那三列的时候使用了子查询和主查询的关系保险了与其它列数据的关联性

  1. 请各位要留意对分组(Group by)的定义相比清晰,重点就在学员的ID:S#
  2. 行转列知识:http://www.cnblogs.com/kerrycode/archive/2010/07/28/1786547.html

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)

(17)按平均战绩从低到高彰显全体学员的“语文”、“数学”、“立陶宛共和国(Republic of Lithuania)语”三门的课程成绩,按如下形式体现: 学生ID,语文,数学,罗马尼亚语,有效课程数,有效平均分

此处涉及到行转列的知识,在查询语数英那三列的时候利用了子查询和主查询的关联合保证证了与别的列数据的关联性

  1. 请各位要注意对分组(Group by)的概念相比清晰,重点就在上学的孩童的ID:S#
  2. 行转列知识:http://www.cnblogs.com/kerrycode/archive/2010/07/28/1786547.html

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)

(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)

(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)

(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

(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

(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

(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

(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

如上正是自笔者用纸张手写的时候碰着有难度的难点,希望能给诸位一些参阅。

(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

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

相关文章

Your Comments

近期评论

    功能


    网站地图xml地图