会python真的可以为所欲为0(回)
350天前
这里还有人吗1(回)
535天前这里还有人吗0(回)
535天前
每天面对着电脑屏幕，敲打键盘。我所面对的并不只是代码，而是一种生活方式。0(回)
760天前到处都是羊，不想上班0(回)
840天前
鸽子
0(回)
844天前1、查询“1”课程比“2”课程成绩高的所有学生的学号； |
SELECT t1.student_id |
FROM |
(SELECT score, student_id FROM student_score WHERE course_id = 1 ) t1, |
(SELECT score, student_id FROM student_score WHERE course_id = 2 ) t2 |
WHERE t1.score > t2.score AND t1.student_id = t2.student_id |
2、查询平均成绩大于60分的同学的学号和平均成绩： |
select student_id, avg(score) from student_score group by student_id HAVING avg(score)>=60 ; |
3、查询所有同学的学号、姓名、选课数、总成绩； |
select ss.student_id, st.student_name, count(course_id), sum(score) from student_score ss |
left join student st on ss.student_id=st.student_id |
group by ss.student_id, st.student_name |
order by ss.student_id |
4、查询姓“李”的老师的个数； |
select count(teacher_name) from teacher where teacher_name like '李%' |
5、查询没学过“叶平”老师课的同学的学号、姓名； |
select student_id,student_name from student where student_id not in ( |
select ss.student_id from |
student_score ss |
left join course co on ss.course_id=co.course_id |
left join teacher te on te.teacher_id= co.teacher_id |
where te.teacher_name='叶平' |
group by ss.student_id |
) |
6、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名； |
select st.student_id,st.student_name from student st |
inner join (select student_id from student_score where course_id=1) t1 on st.student_id=t1.student_id |
inner join (select student_id from student_score where course_id=2) t2 on t1.student_id=t2.student_id |
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名； |
SELECT |
st.student_id,st.student_name |
FROM student_score ss |
inner join student st on ss.student_id=st.student_id |
inner join course co on ss.course_id=co.course_id |
inner join teacher te on te.teacher_id=co.teacher_id |
where te.teacher_name='叶平' |
8、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名； |
select st.student_id,st.student_name from student st |
inner join (select student_id, score from student_score where course_id=1) t1 on st.student_id=t1.student_id |
inner join (select student_id, score from student_score where course_id=2) t2 on t1.student_id=t2.student_id |
where t1.score<t2.score |
9、查询所有课程成绩小于60分的同学的学号、姓名； |
select st.student_id,st.student_name from student_score ss |
inner join student st on ss.student_id=st.student_id |
where ss.score<60 |
group by st.student_id,st.student_name |
10、查询没有学全所有课的同学的学号、姓名； |
select st.student_id, st.student_name from student_score ss |
inner join student st on st.student_id=ss.student_id |
group by st.student_id,st.student_name |
having count(ss.course_id)<(select count(course_id) from course) |
11、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名； |
select st.student_id,st.student_name from |
student st inner join student_score ss on st.student_id=ss.student_id |
where course_id in(select course_id from student_score where student_id=1) |
group by st.student_id,st.student_name |
14、查询和“1”号的同学学习的课程完全相同的其他同学学号和姓名； |
select ss.student_id from student_score ss |
inner join student st on ss.student_id=st.student_id |
where ss.course_id in (select course_id from student_score where student_id=1) |
group by ss.student_id |
having count(ss.course_id)=(select count(course_id) from course) |
17、按平均成绩从高到低显示所有学生的“语文”、“数学”、“英语”三门课程成绩 |
select st.student_id as 学号, |
(select ss.score from student_score ss where st.student_id=ss.student_id and course_id=1) as 语文, |
(select ss.score from student_score ss where st.student_id=ss.student_id and course_id=2) as 数学, |
(select ss.score from student_score ss where st.student_id=ss.student_id and course_id=3) as 英语, |
avg(ss2.score) as 平均成绩 |
from student st inner join student_score ss2 on st.student_id=ss2.student_id |
group by st.student_id |
order by st.student_id |
18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 |
SELECT course_id AS 课程id, MAX (score) AS 最高分, MIN (score) AS 最低分 |
FROM student_score |
GROUP BY course_id |
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序 |
SELECT |
co.course_name AS 科目名称, |
avg (ss.score) as 平均成绩, |
CAST (CAST (SUM(case WHEN COALESCE(ss.score,0)>=60 THEN 1 ELSE 0 END) AS FLOAT)/ |
(SELECT COUNT(*) FROM course) AS DECIMAL (10, 2)) |
FROM |
student_score ss |
INNER JOIN course co ON ss.course_id = co.course_id |
GROUP BY |
co.course_name |
或者 |
SELECT |
student_id, |
CAST (AVG(score) AS DECIMAL(10, 2)) AS 平均成绩, |
CAST (CAST (COUNT(*) AS FLOAT) / (SELECT COUNT(*) FROM course) AS DECIMAL (10, 2)) AS 及格率 |
FROM student_score |
WHERE score >= 60 |
GROUP BY student_id |
ORDER BY AVG (score),CAST (CAST (COUNT(*) AS FLOAT) / (SELECT COUNT(*) FROM course) AS DECIMAL (10, 2)) desc |
24、查询学生平均成绩及其名次 |
SELECT 1 + ( |
SELECT COUNT (DISTINCT 平均成绩) |
FROM |
( SELECT student_id, AVG (score) AS 平均成绩 FROM student_score GROUP BY student_id ) AS T1 |
WHERE 平均成绩 > T2.平均成绩 |
) AS 名次, |
student_id AS 学生学号, |
平均成绩 |
FROM |
( SELECT student_id, AVG (score) 平均成绩 FROM student_score GROUP BY student_id ) AS T2 |
ORDER BY |
平均成绩 DESC; |
25、查询各科成绩前三名的记录:(不考虑成绩并列情况) |
SELECT ss.student_id, ss.course_id, ss.score |
FROM student_score ss |
GROUP BY ss.student_id, ss.course_id, ss.score |
HAVING student_id in (select student_id from student_score where ss.course_id=course_id LIMIT 3) |
ORDER BY ss.course_id, ss.score |
26、查询每门课程被选修的学生数 |
SELECT course_id, COUNT (course_id) |
FROM student_score |
GROUP BY course_id |
ORDER BY course_id |
27、查询出只选修了一门课程的全部学生的学号和姓名 |
SELECT ss.student_id,st.student_name, COUNT (ss.course_id) |
FROM student_score ss |
inner join student st on st.student_id=ss.student_id |
GROUP BY ss.student_id,st.student_name |
having COUNT (ss.course_id)=1 |
ORDER BY ss.student_id |
28、查询男生、女生人数 |
SELECT student_sex, COUNT (student_id) |
FROM student |
GROUP BY student_sex |
29、查询姓“张”的学生名单 |
SELECT * FROM student WHERE student_name LIKE '张%' |
30、查询同名同性学生名单，并统计同名人数 |
SELECT student_name,count(*) FROM student group by student_name having count(*)>1 |
32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列 |
select course_id,avg(score) from student_score group by course_id order by avg(score) asc,course_id |
33、查询平均成绩大于70的所有学生的学号、姓名和平均成绩 |
SELECT ss.student_id,st.student_name, AVG (ss.score) |
FROM student_score ss |
inner join student st on ss.student_id=st.student_id |
GROUP BY ss.student_id,st.student_name |
ORDER BY AVG (ss.score) ASC, ss.student_id |
34、查询课程名称为“语文”，且分数低于60的学生姓名和分数 |
select st.student_name,ss.score |
from student_score ss |
inner join student st on ss.student_id=st.student_id |
inner join course co on ss.course_id=co.course_id |
where co.course_name='语文' and ss.score<60 |
35、查询所有学生的选课情况； |
select st.student_name,co.course_name,ss.score |
from student_score ss |
inner join student st on ss.student_id=st.student_id |
inner join course co on ss.course_id=co.course_id |
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数； |
select st.student_name,co.course_name,ss.score |
from student_score ss |
inner join student st on ss.student_id=st.student_id |
inner join course co on ss.course_id=co.course_id |
where ss.score>70 |
40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩 |
select st.student_name,co.course_name,te.teacher_name,ss.score |
from student_score ss |
inner join student st on ss.student_id=st.student_id |
inner join course co on ss.course_id=co.course_id |
inner join teacher te on co.teacher_id=te.teacher_id |
where te.teacher_name='叶平' |
order by ss.score desc |
limit 1 |
41、查询各个课程及相应的选修人数 |
select course_id,count(course_id) from student_score group by course_id order by course_id |
43、查询每门功成绩最好的前两名 |
SELECT |
t0.student_id, t0.course_id, t0.score |
FROM student_score t0 |
WHERE |
t0.student_score_id IN ( |
SELECT student_score_id |
FROM student_score |
WHERE t0.course_id = course_id |
ORDER BY score DESC |
limit 2 |
) |
44、统计每门课程的学生选修人数，要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人 数相同，按课程号升序排列 |
select course_id,count(student_id) from student_score group by course_id order by count(student_id) desc, course_id |
45、检索至少选修两门课程的学生学号 |
select student_id,count(*) |
from student_score |
group by student_id |
having count(*)>=2 |
order by count(*) desc, student_id |
46、查询全部学生都选修的课程的课程号和课程名 |
SELECT co.course_id, co.course_name |
FROM student_score ss |
INNER JOIN course co ON co.course_id = ss.course_id |
GROUP BY co.course_id, co.course_name |
HAVING COUNT(co.course_id) = (SELECT COUNT (student_id) FROM student) |
48、查询两门以上不及格课程的同学的学号及其平均成绩 |
select student_id,avg(score) from student_score where score<60 group by student_id having count(1)>=2 |
by: 发表于：2017-09-08 09:55:57 顶(0) | 踩(0) 回复
？？
回复评论