面试之数据库基础练习

简介

不久前在练习SQL基础,首先惊讶一下,在机械上写和在纸上写依旧有分别的。

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


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

练习题

(1)查询学过“001”并且也学过数码“00贰”课程的同校的学号、姓名;

  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. 先在成绩单上查询到师资为“叶平”老师的新闻,通过学生ID实行分组并拿走试验门数
  2. 下一场计算课程表上的“叶平”老师的科目数
    三.先是个规格的学生考试门数跟第1个规格的“叶平”老师所教学程数进行自己检查自纠,相等即时答案

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

(1四)查询和“00二”号的同桌学习的课程完全同样的别的同学学号和姓名

留神:查询的是跟“00二”完全同样,且是任何,记得得排除“002”

  1. 首先分明学生课程范围在“00贰”学生所选的科目范围内;
  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'
    )
)

(1陆)向SC表中插入壹些笔录,那个记录要求符合以下规则:1平素不上过编号“00二”课程的同桌学号;二插入“00贰”号课程的平均战绩

  1. 先查询第七个规范的上学的儿童,因为插入的学号需求采用;
  2. 接下来拿走课程“00二”的平分分,那里是获得分数用于插入;

末尾答案就天经地义了:

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)按平均成绩从低到高显示全数学生的“语文”、“数学”、“克罗地亚共和国(Republika Hrvatska)语”三门的教程战绩,按如下情势显得: 学生ID,语文,数学,加泰罗尼亚语,有效课程数,有效平均分

必发bifa88手机客服端,此间提到到行转列的学问,在查询语数英那3列的时候使用了子查询和主查询的关系保障了与此外列数据的关联性

  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)

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

  1. CASE WHEN *** THEN *** ELSE *** END 的使用
  2. 及格率SUM(PassedCounts)/COUNT(AllCounts)的计算
  3. 另外,这里[Percent(%)]能够利用拾0 * 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行内突显): 企管(00二),OO&UML (003),数据库(00四)

那边也是关乎行转列的标题,由于列的数目都以绝对独立的,也就向来不关联子查询的的难题了

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

(二肆)查询学平生均战表及其排名;

恍如简单,排行那里实在倒霉弄。

  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

(二伍)查询各科战表前3名的记录:(不思念战绩并列景况)

  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

上述正是自身用纸张手写的时候遭遇有难度的主题素材,希望能给诸位一些参照。

发表评论

电子邮件地址不会被公开。 必填项已用*标注