sql语句练习题及答案(01课程比02课程成绩高)

时间:2024-05-20 09:49:23

表结构如图:

sql语句练习题及答案(01课程比02课程成绩高)sql语句练习题及答案(01课程比02课程成绩高)

sql语句练习题及答案(01课程比02课程成绩高)sql语句练习题及答案(01课程比02课程成绩高)sql语句练习题及答案(01课程比02课程成绩高)

如果需要表结构和数据给我留言。

 

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

 

SELECT stu.*,c1.score '01课程',c2.score '02课程',c3.score '03课程' from (SELECT score,Scid from sc

WHERE cid=01) c1,(SELECT score,Scid from sc

WHERE Cid=02) c2,student stu,(SELECT score,Scid from sc

WHERE Cid=02) c3

WHERE c1.Scid=c2.scid and stu.Sid=c1.scid and c3.scid=c2.scid and

c1.score>c2.score

 

sql语句练习题及答案(01课程比02课程成绩高)

--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

 

SELECT stu.*,c1.score '01课程',c2.score '02课程',c3.score '03课程' from (SELECT score,Scid from sc

WHERE cid=01) c1,(SELECT score,Scid from sc

WHERE Cid=02) c2,student stu,(SELECT score,Scid from sc

WHERE Cid=02) c3

WHERE c1.Scid=c2.scid and stu.Sid=c1.scid and c3.scid=c2.scid and

c1.score<c2.score

 

sql语句练习题及答案(01课程比02课程成绩高)

--3、查询平均成绩大于等于70分的同学的学生编号和学生姓名和平均成绩

SELECT student.sid,student.sname,AVG(sc.score)

FROM student,sc

WHERE student.sid=sc.scid GROUP BY student.sid

HAVING AVG(sc.score)>=70

 

sql语句练习题及答案(01课程比02课程成绩高)

 

--4、查询平均成绩小于70分的同学的学生编号和学生姓名和平均成绩

 

SELECT student.sid,student.sname,AVG(sc.score)

FROM student,sc

WHERE student.sid=sc.scid GROUP BY student.sid

HAVING AVG(sc.score)<70

sql语句练习题及答案(01课程比02课程成绩高)

 

--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

 

SELECT student.sid,student.sname,COUNT(sc.cid),sum(sc.score)

FROM student,sc

WHERE student.sid=sc.scid GROUP BY student.sid

sql语句练习题及答案(01课程比02课程成绩高)

 

--6、查询"李"姓老师的数量

 

SELECT count(tname) FROM teacher where tname like "李%"

 

sql语句练习题及答案(01课程比02课程成绩高)

 

--7、查询学过"张三"老师授课的同学的信息

 

SELECT student.* from student,teacher,sc

WHERE student.sid=sc.scid and teacher.tid=sc.cid and teacher.tname ="张三"

group BY student.sname

sql语句练习题及答案(01课程比02课程成绩高)

 

--8、查询没学过"张三"老师授课的同学的信息

 

SELECT student.* from student,teacher,sc

WHERE teacher.tid=sc.cid

and student.sname not in (SELECT student.sname from student,teacher,sc

WHERE student.sid=sc.scid and teacher.tid=sc.cid and teacher.tname ="张三")

group BY student.sname

 

sql语句练习题及答案(01课程比02课程成绩高)

 

--9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

 

SELECT * FROM student WHERE sid in (SELECT s1.scid FROM (SELECT scid

FROM sc WHERE cid='01') s1,(SELECT scid FROM sc WHERE cid='02') s2

WHERE s1.scid=s2.scid)

 

sql语句练习题及答案(01课程比02课程成绩高)

 

--10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

 

SELECT student.* FROM student

where sid =(SELECT scid from sc where scid not in (SELECT scid FROM sc WHERE cid='02') GROUP BY scid )

 

sql语句练习题及答案(01课程比02课程成绩高)

--11、查询没有学全所有课程的同学的信息 ok

select stu.*
from student stu LEFT JOIN sc 
on sc.scid = stu.sid 
GROUP BY sc.scid having COUNT(sc.cid) < (select COUNT(*) from course)

--12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息  ok

SELECT student.* FROM student,sc 
where sc.scid=student.sid
and sc.cid in (SELECT cid FROM sc WHERE sc.scid =01)
and not student.sid=01
GROUP BY student.sid

--13、查询和"01"号的同学学习的课程完全相同的其他同学的信息 ok

SELECT * from (SELECT student.* from
(select Count(cid) cc,scid from sc where scid not in (01) GROUP BY scid) A 
left JOIN student on A.scid=student.sid and A.cc=(SELECT count(cid) from sc where sc.scid=01)) d
where sid is not null


--14、查询没学过"张三"老师讲授的任一门课程的学生姓名   ok

SELECT student.sname from student,teacher,sc
WHERE teacher.tid=sc.cid 
and student.sname not in (SELECT student.sname from student,teacher,sc
WHERE student.sid=sc.scid and teacher.tid=sc.cid and teacher.tname ="张三")
group BY student.sname

--15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩  ok

select stu.sid,stu.sname,b.ss
from student stu 
right JOIN (
    SELECT scid,avg(score) as ss from sc where score <60
GROUP BY scid HAVING count(score)>=2) b
on b.scid = stu.sid 

--16、检索"01"课程分数小于60,按分数降序排列的学生信息  ok

SELECT s.*,sc.score from student s,sc
where s.sid=sc.scid and score<60 and sc.cid=01 

--17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩  ok

SELECT student.sid,b.01,b.02,b.03,b.平均成绩 
FROM student 
LEFT JOIN 
(SELECT scid,max(CASE cid when '01' THEN score ELSE 0 END) '01',
max(CASE cid when '02' THEN score ELSE 0 END) '02',
max(CASE cid when '03' THEN score ELSE 0 END) '03',
avg(score) '平均成绩'
from sc GROUP BY scid) B
on student.sid=b.scid
ORDER BY b.平均成绩 desc


--18、查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
--及格为>=60,中等为:-80,优良为:-90,优秀为:>=90   ok

select distinct c.Cid,c.Cname,
       B.`平均分`,B.`最低分`,B.`最高分`,
       b.`及格率`
  from Course c 
  left join (
   select avg(sc.score) `平均分`,max(sc.score) `最高分`,
       min(sc.score) `最低分`,cid,
CONCAT(round(sum(case when score>=60 then 1 else 0 end)/count(*)*100,2),'%') as `及格率`
  from SC 
 group by sc.Cid
) B
on c.cid=B.Cid


--19、按各科成绩进行排序,并显示排名


SELECT A.sname,B.score,B.cid,(SELECT count(C.score)+1 from (SELECT C.score,count(C.score) from sc C GROUP BY C.score ORDER BY c.score desc) C
WHERE C.score>B.score) as paiming
from (SELECT sname,sid from student) A LEFT JOIN sc B
on B.scid=A.sid 
WHERE b.score >0
ORDER BY b.cid,b.score desc


--20、查询学生的总成绩并进行排名 ok

SELECT student.sname,sum(sc.score) 
from student LEFT JOIN sc
on sc.scid=student.sid
GROUP BY student.sid
ORDER BY SUM(sc.score) desc
 

21-25略

--26、查询每门课程被选修的学生数

SELECT cid,COUNT(scid) 学生数 from sc GROUP BY cid

--27、查询出只有两门课程的全部学生的学号和姓名

SELECT sid,sname from student
where sid in (SELECT scid from (SELECT scid,count(cid) 课程数 FROM sc 
GROUP BY scid) A where A.课程数=2)

--28、查询男生、女生人数

SELECT ssex,COUNT(ssex) 人数 from student GROUP BY ssex

--29、查询名字中含有"风"字的学生信息

SELECT * from student where sname like '%风%'

--30、查询同名同性学生名单,并统计同名人数

SELECT A.*,B.同名人数 from student A
LEFT JOIN
(SELECT sname,ssex,COUNT(*) 同名人数 from student GROUP BY sname,ssex) B
on A.sname=B.sname and a.ssex=b.ssex
where b.同名人数>1
 
--31、查询1990年出生的学生名单(注:Student表中Sage列的类型是datetime) 

SELECT * FROM student where year(sage)=1990

--32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列

SELECT cid,avg(score) 平均成绩 from sc GROUP BY cid ORDER BY 平均成绩 desc,cid

--33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

SELECT A.sid,a.sname,b.平均成绩 FROM student a
LEFT JOIN
(SELECT scid,avg(score) 平均成绩 from sc GROUP BY scid) B 
on A.sid=B.scid
WHERE b.平均成绩>85

--34、查询课程名称为"数学",且分数低于 60 的学生姓名和分数

SELECT b.sname,a.score from (SELECT * from sc where score<60 
and cid=(SELECT cid from course WHERE cname='数学')) A
LEFT JOIN student B 
on A.scid=b.sid

--35、查询所有学生的课程及分数情况;

SELECT a.sid,b.cid,b.score FROM student a LEFT JOIN sc b 
on a.sid=b.scid

--36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;

SELECT a.sname,d.cname,d.score FROM
(SELECT b.*,c.cname from (SELECT * from sc where score>70) b LEFT JOIN course C
on b.cid=c.cid) d
LEFT JOIN student a on d.scid=a.sid

--37、查询不及格的课程

SELECT * FROM sc where score<60

--38、查询课程编号为 01 且课程成绩在70分以上的学生的学号和姓名;

SELECT a.sid,b.sname 
from (SELECT * from sc where score>70) 


39-45略

--46、查询各学生的年龄

SELECT *,(case when convert(int,'1'+substring(convert(varchar(10),sage,112),
5,8))
<convert(int,'1'+substring(convert(varchar(10),getDate(),112),5,8))
then datediff(yy,sage,getdate())
else datediff(yy,sage,getdate())-1
end) age
from student

47-50略