95992828九五至尊2

数据库知识必杀,Mysql有用的面试题

四月 1st, 2019  |  九五至尊老品牌值信赖

A.一道SQL语句面试题,关于group by
表内容:
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负

Student(S#,Sname,Sage,Ssex)
学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表

要是要生成下列结果, 该怎么写sql语句?

问题:
① 、查询“001”课程比“002”课程成绩高的享有学生的学号;
select a.S# from (select s#,score from SC where C#=’001′) a,(select
s#,score
from SC where C#=’002′) b
where a.score>b.score and a.s#=b.s#;
② 、查询平均成绩超越陆18分的同室的学号和平均成绩;
select S#,avg(score)
from sc
group by S# having avg(score) >60;
三 、查询全体同学的学号、姓名、选课数、总成绩;
select Student.S#,Student.Sname,count(SC.C#),sum(score)
from Student left Outer join SC on Student.S#=SC.S#
group by Student.S#,Sname
肆 、查询姓“李”的民间兴办教授的个数;
select count(distinct(Tname))
from Teacher
where Tname like ‘李%’;
⑤ 、查询没学过“叶平”老师课的同班的学号、姓名;
select Student.S#,Student.Sname
from Student
where S# not in (select distinct( SC.S#) from SC,Course,Teacher where
SC.C#=Course.C# and Teacher.T#=Course.T# and
Teacher.Tname=’叶平’);
⑥ 、查询学过“001”并且也学过数码“002”课程的同室的学号、姓名;
select Student.S#,Student.Sname from Student,SC where
Student.S#=SC.S# and SC.C#=’001’and exists( Select * from SC as
SC_2 where SC_2.S#=SC.S# and SC_2.C#=’002′);
⑦ 、查询学过“叶平”老师所教的全数课的同班的学号、姓名;
select S#,Sname
from Student
where S# in (select S# from SC ,Course ,Teacher where
SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=’叶平’
group by S# having count(SC.C#)=(select count(C#) from Course,Teacher
where Teacher.T#=Course.T# and Tname=’叶平’));
⑧ 、查询课程编号“002”的大成比课程编号“001”课程低的富有同学的学号、姓名;
Select S#,Sname from (select Student.S#,Student.Sname,score ,(select
score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#=’002′)
score2
from Student,SC where Student.S#=SC.S# and C#=’001′) S_2 where
score2 <score;
玖 、查询全体课程战表小于伍十八分的校友的学号、姓名;
select S#,Sname
from Student
where S# not in (select Student.S# from Student,SC where S.S#=SC.S#
and score>60);
⑩ 、查询没有学全全部课的同室的学号、姓名;
select Student.S#,Student.Sname
from Student,SC
where Student.S#=SC.S# group by Student.S#,Student.Sname having
count(C#) <(select count(C#) from Course);
1一 、查询至少有一门课与学号为“1001”的校友所学相同的同桌的学号和人名;
select S#,Sname from Student,SC where Student.S#=SC.S# and C# in
select C# from SC where S#=’1001′;
1贰 、查询至少学过学号为“001”同学全体一门课的别的同学学号和人名;
select distinct SC.S#,Sname
from Student,SC
where Student.S#=SC.S# and C# in (select C# from SC where
S#=’001′);
1③ 、把“SC”表中“叶平”老师教的课的成就都转移为此课程的平均成绩;
update SC set score=(select avg(SC_2.score)
from SC SC_2
where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and
Course.T#=Teacher.T# and Teacher.Tname=’叶平’);
1四 、查询和“1002”号的同班学习的科目完全相同的别的同学学号和人名;
select S# from SC where C# in (select C# from SC where S#=’1002′)
group by S# having count(*)=(select count(*) from SC where
S#=’1002′);
1伍 、删除学习“叶平”老师课的SC表记录;
Delect SC
from course ,Teacher
where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname=’叶平’;
1陆 、向SC表中插入一些记录,那几个记录需求符合以下规则:没有上过编号“003”课程的校友学号、② 、
号课的平分战绩;
Insert SC select S#,’002′,(Select avg(score)
from SC where C#=’002′) from Student where S# not in (Select S# from
SC where C#=’002′);
1柒 、按平均战绩从高到低展现全部学员的“数据库”、“企管”、“阿尔巴尼亚语”三门的课程成绩,按如下情势显得:
学生ID,,数据库,企管,克罗地亚语,有效课程数,有效平均分
SELECT S# as 学生ID
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=’004′) AS 数据库
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=’001′) AS 企管
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=’006′) AS 英语
,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩
FROM SC AS t
GROUP BY S#
ORDER BY avg(t.score)
1⑧ 、查询各科战绩最高和压低的分:以如下格局展示:课程ID,最高分,最低分
SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分
FROM SC L ,SC AS R
WHERE L.C# = R.C# and
L.score = (SELECT MAX(IL.score)
FROM SC AS IL,Student AS IM
WHERE L.C# = IL.C# and IM.S#=IL.S#
GROUP BY IL.C#)
AND
R.Score = (SELECT MIN(IR.score)
FROM SC AS IR
WHERE R.C# = IR.C#
GROUP BY IR.C#
);
1⑨ 、按各科平均战绩从低到高和及格率的比例从高到低依次
SELECT t.C# AS 课程号,max(course.Cname)AS 课程名,isnull(AVG(score),0)
AS 平均成绩
,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0
END)/COUNT(*) AS 及格百分数
FROM SC T,Course
where t.C#=course.C#
GROUP BY t.C#
ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0
END)/COUNT(*) DESC
20、查询如下课程平均成绩和及格率的百分比(用”1行”呈现):
企管(001),马克思(002),OO&UML (003),数据库(004)
SELECT SUM(CASE WHEN C# =’001′ THEN score ELSE 0 END)/SUM(CASE C# WHEN
‘001’ THEN 1 ELSE 0 END) AS 企管平均分
,100 * SUM(CASE WHEN C# = ‘001’ AND score >= 60 THEN 1 ELSE 0
END)/SUM(CASE WHEN C# = ‘001’ THEN 1 ELSE 0 END) AS
企管及格百分数
,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 WHEN C# = ‘002’ THEN 1 ELSE 0 END) AS Marx及格百分数
,SUM(CASE WHEN C# = ‘003’ THEN score ELSE 0 END)/SUM(CASE C# WHEN
‘003’ THEN 1 ELSE 0 END) AS UML平均分
,100 * SUM(CASE WHEN C# = ‘003’ AND score >= 60 THEN 1 ELSE 0
END)/SUM(CASE WHEN C# = ‘003’ THEN 1 ELSE 0 END) AS 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 WHEN C# = ‘004’ THEN 1 ELSE 0 END) AS 数据库及格百分数
FROM SC
2壹 、查询分裂老师所教分裂学科平均分从高到低显示
SELECT max(Z.T#) AS 教授ID,MAX(Z.Tname) AS 教授姓名,C.C# AS
课程ID,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均成绩
FROM SC AS T,Course AS C ,Teacher AS Z
where T.C#=C.C# and C.T#=Z.T#
GROUP BY C.C#
ORDER BY AVG(Score) DESC
2二 、查询如下课程成绩第 3 名到第 6
名的学员战表单:企管(001),Marx(002),UML
(003),数据库(004)
[学生ID],[学员姓名],企管,马克思,UML,数据库,平均战绩
SELECT DISTINCT top 3
SC.S# 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 T1
ON SC.S# = T1.S# AND T1.C# = ‘001’
LEFT JOIN SC AS T2
ON SC.S# = T2.S# AND T2.C# = ‘002’
LEFT JOIN SC AS T3
ON SC.S# = T3.S# AND T3.C# = ‘003’
LEFT JOIN SC AS T4
ON SC.S# = T4.S# AND T4.C# = ‘004’
WHERE student.S#=SC.S# and
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) +
ISNULL(T4.score,0)
NOT IN
(SELECT
DISTINCT
TOP 15 WITH TIES
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) +
ISNULL(T4.score,0)
FROM sc
LEFT JOIN sc AS T1
ON sc.S# = T1.S# AND T1.C# = ‘k1’
LEFT JOIN sc AS T2
ON sc.S# = T2.S# AND T2.C# = ‘k2’
LEFT JOIN sc AS T3
ON sc.S# = T3.S# AND T3.C# = ‘k3’
LEFT JOIN sc AS T4
ON sc.S# = T4.S# AND T4.C# = ‘k4’
ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) +
ISNULL(T4.score,0) DESC);

胜 负
2005-05-09 2 2
2005-05-10 1 2
******************************************************
create table #tmp(rq varchar(10),shengfu nchar(1))

2叁 、计算列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[
<60]
SELECT SC.C# 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,Course
where SC.C#=Course.C#
GROUP BY SC.C#,Cname;

insert into #tmp values(‘2005-05-09′,’胜’)
insert into #tmp values(‘2005-05-09′,’胜’)
insert into #tmp values(‘2005-05-09′,’负’)
insert into #tmp values(‘2005-05-09′,’负’)
insert into #tmp values(‘2005-05-10′,’胜’)
insert into #tmp values(‘2005-05-10′,’负’)

2四 、查询学毕生均成绩及其排名
SELECT 1+(SELECT COUNT( distinct 平均成绩)
FROM (SELECT S#,AVG(score) AS 平均战绩
FROM SC
GROUP BY S#
) AS T1
WHERE 平均成绩 > T2.等分成绩) as 排行,
S# as 学生学号,平均成绩
FROM (SELECT S#,AVG(score) 平均成绩
FROM SC
GROUP BY S#
) AS T2
OCRUISERDERubicon BY 平均战绩 desc;

insert into #tmp values(‘2005-05-10′,’负’)

SELECT rq,sum(shengfu=’胜’) as
‘胜’,sum(shengfu=’负’) as ‘负’
FROM `#tmp`
GROUP BY `#tmp`.rq
*****************************************************************

B.请教三个面试中遇到的SQL语句的查询难点
表中有A B

2五 、查询各科战表前三名的笔录:(不考虑成绩并列意况)
SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
FROM SC t1
WHERE score IN (SELECT TOP 3 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC
)
ORDER BY t1.C#;
2⑥ 、查询每门课程被选修的学习者数
select c#,count(S#) from sc group by C#;
2七 、查询出只选修了一门学科的成套学生的学号和姓名
select SC.S#,Student.Sname,count(C#) AS 选课数
from SC ,Student
where SC.S#=Student.S# group by SC.S# ,Student.Sname having
count(C#)=1;
2捌 、查询男人、女孩子人数
Select count(Ssex) as 男人人数 from Student group by Ssex having
Ssex=’男’;
Select count(Ssex) as 女人人数 from Student group by Ssex having
Ssex=’女’;
2玖 、查询姓“张”的学生名单
SELECT Sname FROM Student WHERE Sname like ‘张%’;
30、查询同名同性学生名单,并总计同有名的人数
select Sname,count(*) from Student group by Sname having
count(*)>1;;
3壹 、一九八五年出生的学生名单(注:Student表中Sage列的品种是datetime)
select Sname, CONVERT(char (11),DATEPART(year,Sage)) as age
from student
where CONVERT(char(11),DATEPART(year,Sage))=’1981′;
3二 、查询每门科目标平均战绩,结果按平均成绩升序排列,平均成绩同样时,按学科号降序排列
Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC
;
3三 、查询平均成绩超过85的具备学生的学号、姓名和平均战绩
select Sname,SC.S# ,avg(score)
from Student,SC
where Student.S#=SC.S# group by SC.S#,Sname having   
avg(score)>85;
3四 、查询课程名称为“数据库”,且分数低于60的上学的小孩子姓名和分数
Select Sname,isnull(score,0)
from Student,SC,Course
where SC.S#=Student.S# and SC.C#=Course.C# and
Course.Cname=’数据库’and score <60;
3⑤ 、查询全数学员的选课情况;
SELECT SC.S#,SC.C#,Sname,Cname
FROM SC,Student,Course
where SC.S#=Student.S# and SC.C#=Course.C# ;
3六 、查询任何一门科目战表在六贰12分以上的姓名、课程名称和分数;
SELECT distinct student.S#,student.Sname,SC.C#,SC.score
FROM student,Sc
WHERE SC.score>=70 AND SC.S#=student.S#;
3⑦ 、查询比不上格的学科,并按学科号从大到小排列
select c# from sc where scor e <60 order by C# ;
3⑧ 、查询课程编号为003且课程成绩在77分以上的学生的学号和人名;
select SC.S#,Student.Sname from SC,Student where SC.S#=Student.S# and
Score>80 and C#=’003′;
3玖 、求选了课程的学习者人数
select count(*) from sc;
40、查询选修“叶平”老师所授课程的上学的儿童中,战绩最高的学习者姓名及其成绩
select Student.Sname,score
from Student,SC,Course C,Teacher
where Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# and
Teacher.Tname=’叶平’ and SC.score=(select max(score)from SC where
C#=C.C# );
4① 、查询各样学科及相应的选修人数
select count(*) from sc group by C#;
4② 、查询差别科目战绩一样的学员的学号、课程号、学生战绩
select distinct A.S#,B.score from SC A ,SC B where A.Score=B.Score and
A.C# <>B.C# ;
4③ 、查询每门功成绩最佳的前两名
SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
FROM SC t1
WHERE score IN (SELECT TOP 2 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC
)
ORDER BY t1.C#;
4肆 、总括每门课程的学员选修人数(超过拾1人的科目才总括)。要求输出课程号和选修人数,查询结果按人口降序排列,查询结果按人口降序排列,若人数相
同,按学科号升序排列
select C# as 课程号,count(*) as 人数
from sc
group by C#
order by count(*) desc,c#
4伍 、检索至少选修两门学科的学生学号
select S#
from sc
group by s#
having count(*) > = 2
4六 、查询全部学生都选修的课程的课程号和科目名
select C#,Cname
from Course
where C# in (select c# from sc group by c#)
4⑦ 、查询没学过“叶平”老师教学的任一门科指标上学的小孩子姓名
select Sname from Student where S# not in (select S# from
Course,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C# and
Tname=’叶平’);
4捌 、查询两门以上不如格课程的同室的学号及其平均战表
select S#,avg(isnull(score,0)) from SC where S# in (select S# from SC
where score <60 group by S# having count(*)>2)group by S#;
4玖 、检索“004”课程分数小于60,按分数降序排列的校友学号
select S# from SC where C#=’004’and score <60 order by score
desc;
50、删除“002”同学的“001”课程的成就
delete from Sc where S#=’001’and C#=’001′;

C三列,用SQL语句完成:当A列大于B列时精选A列不然选拔B列,当B列大于C列时接纳B列不然采取C列。

select (case when a>b then a
else b end),
(case when b>c then b esle c
end)
from taname
***************************************************

C.面试题:一个日期判断的sql语句?

 

请取出tb_send表中国和东瀛期(SendTime字段)为当天的持有记录?(SendTime字段为datetime型,包括日期与时间)

select * from time where
datediff(SendTime,CURDATE())=0

CUSportageDATE()获取当今天子
DATEDIFF() 再次回到起初时间
expr和竣事作时间间expr2之间的天数。Expr和expr2 为日期或 date-and-time
表明式。总计中只用到那几个值的日子部分。

TIMEDIFF(expr,expr2)
TIMEDIFF() 再次回到发轫时间 expr
和告竣时间expr2 之间的时辰。 expr 和expr2 为时间或 date-and-time
表明式,三个的门类必须一致。
**************************************************************************************************************************

D.有一张表,里面有3个字段:语文,数学,乌克兰语。当中有3条记下分别代表语文六二十分,数学八十多分,马耳他语5五分,请用一条sql语句询问出那三条记下并按以下原则显得出来(并写出你的笔触):
胜出或等于80象征出色,大于或等于60代表及格,小于五十4分表示比不上格。
突显格式:
语文 数学 英语

(转
http://blog.sina.com.cn/s/blog_3d4c358f0100lcf3.html

及格 优秀 不及格

壹 、一定有分三类,语文、数学、越南语
二 、对查询到的分数举办判断
叁 、先摸清多个字段呈现
SELECT
CASE WHEN
yuwen >= ’80’ THEN’优秀’WHEN
yuwen >=’60’ THEN’及格’ELSE’不及格’END yuwen,
CASE WHEN
shuxue >= ’80’
THEN’优秀’WHEN shuxue >=’60’ THEN’及格’ELSE’不及格’END
shuxue,
CASE WHEN
yingyu >= ’80’
THEN’优秀’WHEN yingyu >=’60’ THEN’及格’ELSE’不及格’END
yingyu
FROM
kecheng

E.请用三个sql语句得出结果
从table1,table第22中学取出如table3所列格式数据,注意提供的数据及结果不准确,只是作为一个格式向我们请教。
如利用存款和储蓄进度也足以。

table1

月份mon 部门dep 业绩yj

一月份 01 10
一月份 02 10
一月份 03 5
二月份 02 8
二月份 04 9
三月份 03 8

table2

单位dep 部门名称dname

01 国内工作一部
02 国内事务二部
03 国内工作三部
04 国际业务部

table3 (result)

部门dep 一月份 二月份 三月份

01         10       null    null
02         10       8       null
03         null      5          8

04         null     null       9

select a.dname, a.dep,
sum(case when b.mon=1 then b.yj
else 0 end) as ‘一月份’,
sum(case when b.mon=2 then b.yj
else 0 end) as ‘二月份’,
sum(case when b.mon=3 then b.yj
else 0 end) as ‘三月份’
from table2 a left join table1
b on a.dep=b.dep
GROUP BY
a.dep

 

 

F.一道面试题

三个表中的Id有四个记录,把具有那几个id的记录查出来,并显示共有多少条记录数。

select id, Count(*) from tb
group by id having count(*)>1
select*from(select count(ID)
as count from table group by ID)T where T.count>1

G、table表情势如下:
Year Salary
2000 1000
2001 2000
2002 3000
2003 4000
想获得如下方式的询问结果
Year Salary
2000 1000
2001 3000
2002 6000
2003 10000
sql语句怎么写?
****************************************
SELECT a.year, SUM(b.salary) AS
sala
FROM table AS a,table AS
b
WHERE
b.salary<=a.salary
GROUP BY a.salary
***************************************************
H.用一条SQL语句询问出每门课都大于七十九分的学生姓名
name kecheng fenshu
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90

****************************************************************************
selet name from tablename where
name in (select name from tablename where fenshu >80 )
select distinct name from table
where name not in (select distinct name from table where
fenshu<=80)

 

**************************************************
I.学生表 如下:
活动编号 学号 姓名 课程编号 课程名称 分数
1 2005001 张三 0001 数学 69
2 2005002 李四 0001 数学 89
3 2005001 张三 0001 数学 69
剔除除了自行编号区别,其余都同样的上学的小孩子冗余音信
*******************************************************
delete tablename where 自动编号 not in() 能运营
select min(自动编号) from tablename group by
学号,姓名,课程编号,课程名称,分数 能运作
delete tablename where 自动编号 not in(select min(自动编号) from
tablename group by 学号,姓名,课程编号,课程名称,分数 )

运转不了。会报《 You can’t specify target table ‘tb’ for update in FROM

clause》那样的荒谬

J.二个叫department的表,里面唯有三个字段name,一共有4条纪录,分别是a,b,c,d,对应多少个球对,今后八个球对展开较量,用一条sql语句显示全体可能的交锋组合.

您先按您本身的想法做一下,看结果有自己的那几个大概吗?

答:select a.name, b.name
from team a, team b
where a.name < b.name

********************************************************************************************************************************
K.请用SQL语句实现:从TestDB数据表中询问出具有月份的发生额都比101科目相应月份的发生额高的课程。请小心:TestDB中有成都百货上千课程,都有1-三月份的产生额。
AccID:科目代码,Occmonth:发生额月份,DebitOccur:发生额。
数据库名:Jcy奥迪(Audi)t,数据集:Select * from TestDB

答:select a.*
from TestDB a
,(select Occmonth,max(DebitOccur) Debit101ccur from TestDB where
AccID=’101′ group by Occmonth) b
where a.Occmonth=b.Occmonth and a.DebitOccur>b.Debit101ccur

 

************************************************************************************
L.面试题:怎么把这么1个表儿
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成那样2个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4

******************************************************
答案一、
select year,
(select amount from aaa m where month=1 and m.year=aaa.year) as m1,
(select amount from aaa m where month=2 and m.year=aaa.year) as m2,
(select amount from aaa m where month=3 and m.year=aaa.year) as m3,
(select amount from aaa m where month=4 and m.year=aaa.year) as m4
from aaa group by year

这个是ORACLE 中做的:
select * from (select name, year b1, lead(year) over
(partition by name order by year) b2, lead(m,2) over(partition by name
order by year) b3,rank()over(
partition by name order by year) rk from t) where rk=1;

 

Student(S#,Sname,Sage,Ssex)
学生表      

 S#:学号;Sname:学生姓名;Sage:学生年龄;Ssex:学生性别
Course(C#,Cname,T#) 课程表                    C#,课程编号;Cname:课程名字;T#:教授编号
SC(S#,C#,score) 成绩表                             S#:学号;C#,课程编号;score:战表
Teacher(T#,Tname) 教师表                        T#:教授编号;
Tname:教授名字

 

问题:
一 、查询“001”课程比“002”课程战绩高的兼具学员的学号;
 

 select a.S# from (select s#,score from
SC where C#=’001′) a,(select s#,score
  from SC where C#=’002′) b
  where a.score>b.score and
a.s#=b.s#;
② 、查询平均成绩超乎56分的同班的学号和平均战绩;
    select S#,avg(score)
    from sc
    group by S# having avg(score)
>60;
叁 、查询全数同学的学号、姓名、选课数、总成绩;
  select
Student.S#,Student.Sname,count(SC.C#),sum(score)
  from Student left Outer join SC on
Student.S#=SC.S#
  group by Student.S#,Sname
肆 、查询姓“李”的教员职员和工人的个数;
  select count(distinct(Tname))
  from Teacher
  where Tname like ‘李%’;
伍 、查询没学过“叶平”老师课的同桌的学号、姓名;
    select
Student.S#,Student.Sname
    from Student 
    where S# not in (select
distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and
Teacher.T#=Course.T# and Teacher.Tname=’叶平’);
六 、查询学过“001”并且也学过数码“002”课程的同窗的学号、姓名;
  select Student.S#,Student.Sname
from Student,SC where Student.S#=SC.S# and SC.C#=’001’and exists(
Select * from SC as SC_2 where SC_2.S#=SC.S# and
SC_2.C#=’002′);
柒 、查询学过“叶平”老师所教的全体课的同校的学号、姓名;
  select S#,Sname
  from Student
  where S# in (select S# from SC
,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and
Teacher.Tname=’叶平’ group by S# having count(SC.C#)=(select
count(C#) from Course,Teacher  where Teacher.T#=Course.T# and
Tname=’叶平’));
捌 、查询课程编号“002”的成绩比课程编号“001”课程低的兼具同学的学号、姓名;
  Select S#,Sname from (select
Student.S#,Student.Sname,score ,(select score from SC SC_2 where
SC_2.S#=Student.S# and SC_2.C#=’002′) score2
  from Student,SC where
Student.S#=SC.S# and C#=’001′) S_2 where score2 <score;
九 、查询全体科目战绩小于63分的同室的学号、姓名;
  select S#,Sname
  from Student
  where S# not in (select
Student.S# from Student,SC where S.S#=SC.S# and score>60);
十 、查询没有学全全体课的同班的学号、姓名;
    select
Student.S#,Student.Sname
    from Student,SC
    where Student.S#=SC.S# group
by  Student.S#,Student.Sname having
count(C#) <(select count(C#) from
Course);

 

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

 select S#,Sname from Student,SC where
Student.S#=SC.S# and C# in select C# from SC where S#=’1001′;
1贰 、查询至少学过学号为“001”同学全部一门课的别的同学学号和姓名;
    select distinct SC.S#,Sname
    from Student,SC
    where Student.S#=SC.S# and C#
in (select C# from SC where S#=’001′);
1三 、把“SC”表中“叶平”老师教的课的战表都改成为此课程的平分成绩;
    update SC set score=(select
avg(SC_2.score)
    from SC SC_2
    where SC_2.C#=SC.C# ) from
Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and
Teacher.Tname=’叶平’);
1四 、查询和“1002”号的同桌学习的课程完全相同的别的同学学号和人名;
    select S# from SC where C# in
(select C# from SC where S#=’1002′)
    group by S# having
count(*)=(select count(*) from SC where S#=’1002′);
1伍 、删除学习“叶平”老师课的SC表记录;
    Delect SC
    from course ,Teacher 
    where Course.C#=SC.C# and
Course.T#= Teacher.T# and Tname=’叶平’;
1六 、向SC表中插入一些记下,这几个记录供给符合以下条件:没有上过编号“003”课程的同室学号、② 、
    号课的平均成绩;
    Insert SC select
S#,’002′,(Select avg(score)
    from SC where C#=’002′) from
Student where S# not in (Select S# from SC where C#=’002′);
1⑦ 、按平均战绩从高到低展现全体学生的“数据库”、“企业管理”、“波兰语”三门的教程战绩,按如下格局显得:
学生ID,,数据库,企管,丹麦语,有效课程数,有效平均分
    SELECT S# as 学生ID
        ,(SELECT score FROM SC WHERE
SC.S#=t.S# AND C#=’004′) AS 数据库
        ,(SELECT score FROM SC WHERE
SC.S#=t.S# AND C#=’001′) AS 企管
        ,(SELECT score FROM SC WHERE
SC.S#=t.S# AND C#=’006′) AS 英语
        ,COUNT(*) AS 有效课程数,
AVG(t.score) AS 平均成绩
    FROM SC AS t
    GROUP BY S#
    ORDER BY avg(t.score) 
1⑧ 、查询各科战表最高和压低的分:以如下情势展现:课程ID,最高分,最低分
    SELECT L.C# As 课程ID,L.score
AS 最高分,R.score AS 最低分
    FROM SC L ,SC AS R
    WHERE L.C# = R.C# and
        L.score = (SELECT
MAX(IL.score)
                      FROM SC AS
IL,Student AS IM
                      WHERE L.C# =
IL.C# and IM.S#=IL.S#
                      GROUP BY
IL.C#)
        AND
        R.Score = (SELECT
MIN(IR.score)
                      FROM SC AS
IR
                      WHERE R.C# =
IR.C#
                  GROUP BY IR.C#
                    );
1⑨ 、按各科平均成绩从低到高和及格率的比例从高到低依次
    SELECT t.C# AS
课程号,max(course.Cname)AS 课程名,isnull(AVG(score),0) AS 平均成绩
        ,100 * SUM(CASE WHEN  isnull(score,0)>=60 THEN 1 ELSE 0
END)/COUNT(*) AS 及格百分数
    FROM SC T,Course
    where t.C#=course.C#
    GROUP BY t.C#
    ORDER BY 100 * SUM(CASE
WHEN  isnull(score,0)>=60 THEN 1
ELSE 0 END)/COUNT(*) DESC
20、查询如下课程平均战绩和及格率的百分比(用”1行”展现):
企管(001),马克思(002),OO&UML (003),数据库(004)
    SELECT SUM(CASE WHEN C# =’001′
THEN score ELSE 0 END)/SUM(CASE C# WHEN ‘001’ THEN 1 ELSE 0 END) AS
企管平均分
        ,100 * SUM(CASE WHEN C# =
‘001’ AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = ‘001’
THEN 1 ELSE 0 END) AS 企管及格百分数
        ,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 WHEN C# = ‘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
UML平均分
        ,100 * SUM(CASE WHEN C# =
‘003’ AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = ‘003’
THEN 1 ELSE 0 END) AS 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 WHEN C# = ‘004’
THEN 1 ELSE 0 END) AS 数据库及格百分数
  FROM SC
2① 、查询差别老师所教区别科目平均分从高到低展现
  SELECT max(Z.T#) AS
教授ID,MAX(Z.Tname) AS 助教姓名,C.C# AS 课程ID,MAX(C.Cname) AS
课程名称,AVG(Score) AS 平均成绩
    FROM SC AS T,Course AS C
,Teacher AS Z
    where T.C#=C.C# and
C.T#=Z.T#
  GROUP BY C.C#
  ORDER BY AVG(Score) DESC
2二 、查询如下课程成绩第 3 名到第 6
名的上学的小孩子成绩单:企管(001),马克思(002),UML
(003),数据库(004)
    [学生ID],[学生姓名],企管,马克思,UML,数据库,平均战表
    SELECT  DISTINCT top 3
      SC.S# 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 T1
                      ON SC.S# =
T1.S# AND T1.C# = ‘001’
            LEFT JOIN SC AS T2
                      ON SC.S# =
T2.S# AND T2.C# = ‘002’
            LEFT JOIN SC AS T3
                      ON SC.S# =
T3.S# AND T3.C# = ‘003’
            LEFT JOIN SC AS T4
                      ON SC.S# =
T4.S# AND T4.C# = ‘004’
      WHERE student.S#=SC.S# and
      ISNULL(T1.score,0) +
ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
      NOT IN
      (SELECT
            DISTINCT
            TOP 15 WITH TIES
            ISNULL(T1.score,0) +
ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
      FROM sc
            LEFT JOIN sc AS T1
                      ON sc.S# =
T1.S# AND T1.C# = ‘k1’
            LEFT JOIN sc AS T2
                      ON sc.S# =
T2.S# AND T2.C# = ‘k2’
            LEFT JOIN sc AS T3
                      ON sc.S# =
T3.S# AND T3.C# = ‘k3’
            LEFT JOIN sc AS T4
                      ON sc.S# =
T4.S# AND T4.C# = ‘k4’
      ORDER BY ISNULL(T1.score,0) +
ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
DESC);

 

2③ 、总括列印各科战绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[
<60]
   

 SELECT SC.C# 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,Course
    where SC.C#=Course.C#
    GROUP BY
SC.C#,Cname;

 

2④ 、查询学一生均成绩及其排行
     

 SELECT 1+(SELECT COUNT( distinct
平均战表)
              FROM (SELECT
S#,AVG(score) AS 平均战绩
                      FROM SC
                  GROUP BY S#
                  ) AS T1
            WHERE 平均成绩 >
T2.平分成绩) as 排行,
      S# as 学生学号,平均成绩
    FROM (SELECT S#,AVG(score)
平均成绩
            FROM SC
        GROUP BY S#
        ) AS T2
    O劲客DER BY 平均成绩 desc;
 
2⑤ 、查询各科成绩前三名的记录:(不考虑战表并列境况)
      SELECT t1.S# as 学生ID,t1.C#
as 课程ID,Score as 分数
      FROM SC t1
      WHERE score IN (SELECT TOP 3
score
              FROM SC
              WHERE t1.C#= C#
            ORDER BY score DESC
              )
      ORDER BY t1.C#;
2⑥ 、查询每门科目被选修的学习者数
  select c#,count(S#) from sc
group by C#;
2七 、查询出只选修了一门科目标成套学员的学号和人名
  select
SC.S#,Student.Sname,count(C#) AS 选课数
  from SC ,Student
  where SC.S#=Student.S# group by
SC.S# ,Student.Sname having count(C#)=1;
2⑧ 、查询匹夫、女人人数
    Select count(Ssex) as 匹爱妻数
from Student group by Ssex having Ssex=’男’;
    Select count(Ssex) as 女子人数
from Student group by Ssex having Ssex=’女’;
2玖 、查询姓“张”的学生名单
    SELECT Sname FROM Student WHERE
Sname like ‘张%’;
30、查询同名同性学生名单,并总结同有名的人数
  select Sname,count(*) from
Student group by Sname having  count(*)>1;;
3壹 、1985年降生的学生名单(注:Student表中Sage列的品类是datetime)
    select Sname,  CONVERT(char (11),DATEPART(year,Sage)) as
age
    from student
    where  CONVERT(char(11),DATEPART(year,Sage))=’1981′;
3二 、查询每门学科的平分战表,结果按平均成绩升序排列,平均战绩一样时,按学科号降序排列
    Select C#,Avg(score) from SC
group by C# order by Avg(score),C# DESC ;
3叁 、查询平均成绩超乎85的兼具学员的学号、姓名和平均战表
    select Sname,SC.S#
,avg(score)
    from Student,SC
    where Student.S#=SC.S# group
by SC.S#,Sname having    avg(score)>85;
3肆 、查询课程名称为“数据库”,且分数低于60的学习者姓名和分数
    Select Sname,isnull(score,0)
    from Student,SC,Course
    where SC.S#=Student.S# and
SC.C#=Course.C# and  Course.Cname=’数据库’and score <60;
3⑤ 、查询全体学生的选课意况;
    SELECT
SC.S#,SC.C#,Sname,Cname
    FROM SC,Student,Course
    where SC.S#=Student.S# and
SC.C#=Course.C# ;
3陆 、查询任何一门学科战表在六十7分以上的真名、课程名称和分数;
    SELECT  distinct
student.S#,student.Sname,SC.C#,SC.score
    FROM student,Sc
    WHERE SC.score>=70 AND
SC.S#=student.S#;
3⑦ 、查询不及格的科目,并按学科号从大到小排列
    select c# from sc where scor e
<60 order by C# ;
3八 、查询课程编号为003且课程成绩在七十六分以上的学员的学号和姓名;
    select SC.S#,Student.Sname from
SC,Student where SC.S#=Student.S# and Score>80 and C#=’003′;
3⑨ 、求选了学科的学生人数
    select count(*) from sc;
40、查询选修“叶平”老师所授课程的学生中,战表最高的学生姓名及其成绩
    select Student.Sname,score
    from Student,SC,Course
C,Teacher
    where Student.S#=SC.S# and
SC.C#=C.C# and C.T#=Teacher.T# and Teacher.Tname=’叶平’ and
SC.score=(select max(score)from SC where C#=C.C# );
4① 、查询各样科目及相应的选修人数
    select count(*) from sc group
by C#;
4② 、查询不一样学科成绩同样的学习者的学号、课程号、学生战绩
  select distinct  A.S#,B.score from SC A  ,SC B where A.Score=B.Score and A.C#
<>B.C# ;
4叁 、查询每门功成绩最棒的前两名
    SELECT t1.S# as 学生ID,t1.C#
as 课程ID,Score as 分数
      FROM SC t1
      WHERE score IN (SELECT TOP 2
score
              FROM SC
              WHERE t1.C#= C#
            ORDER BY score DESC
              )
      ORDER BY t1.C#;
4四 、总括每门课程的学童选修人数(当先11位的课程才计算)。须求输出课程号和选修人数,查询结果按人头降序排列,查询结果按人头降序排列,若人数相同,按学科号升序排列 
    select  C# as 课程号,count(*) as 人数
    from  sc 
    group  by  C#
    order  by  count(*) desc,c# 
4五 、检索至少选修两门课程的学员学号
    select  S# 
    from  sc 
    group  by  s#
    having  count(*)  >  =  2
4六 、查询任何学童都选修的教程的课程号和学科名
    select  C#,Cname 
    from  Course 
    where  C#  in  (select  c#  from  sc group  by  c#) 
4七 、查询没学过“叶平”老师教师的任一门学科的学习者姓名
    select Sname from Student where
S# not in (select S# from Course,Teacher,SC where
Course.T#=Teacher.T# and SC.C#=course.C# and Tname=’叶平’);
4⑧ 、查询两门以上比不上格课程的同窗的学号及其平均战绩
    select S#,avg(isnull(score,0))
from SC where S# in (select S# from SC where score <60 group by S#
having count(*)>2)group by S#;
4⑨ 、检索“004”课程分数小于60,按分数降序排列的同室学号
    select S# from SC where
C#=’004’and score <60 order by score desc;
50、删除“002”同学的“001”课程的战绩
delete from Sc where S#=’002’and
C#=’001′;

 

Your Comments

近期评论

    功能


    网站地图xml地图