MySQL50题

原创 于 2023-07-12 19:09:31 发布
· 110 阅读 · 1 ·
版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#数据库 #sql #oracle

数据库共有四张表如下，有的数据是直接在表格中插入的，大部分数据都在查询需要处对应做了新增。
Student(S_ID,Sname,Sage,Ssex) 学生表
Course(C_ID,Cname,T_ID) 课程表
SC(S_ID,C_ID,score) 成绩表
Teacher(T_ID,Tname) 教师表

CREATE DATABASE School_SQL50;
USE School_SQL50;

1.学生表
Student(S_ID,Sname,Sage,Ssex)
学生编号,S_ID
学生姓名,Sname
出生年月,Sage
学生性别,Ssex

CREATE TABLE IF NOT EXISTS Student
(
S_ID varchar(20) not null primary key,
Sname varchar(20) not null,
Sage int,
Ssex char
);

2.课程表
Course(C_ID,Cname,T_ID)
课程编号,C_ID
课程名称,Cname
教师编号,T_ID

CREATE TABLE Course
(
C_ID varchar(20) not null primary key,
Cname varchar(20) not null,
T_ID varchar(20)
);

3.教师表
Teacher(T_ID,Tname)
教师编号,T_ID
教师姓名,Tname

CREATE TABLE Teacher
(
T_ID varchar(20) not null primary key,
Tname varchar(20) not null
);

4.成绩表
SC(S_ID,C_ID,score)
学生编号,S_ID
课程编号,C_ID
分数,score

CREATE TABLE SC
(
S_ID varchar(20) not null primary key,
C_ID varchar(20) not null,
score int
);

补充数据、修改不当表结构

ALTER TABLE Course ADD CONSTRAINT fk_course_teacher_tid foreign key (T_ID) references Teacher (T_ID);
ALTER TABLE SC ADD CONSTRAINT fk_SC_student_sid foreign key (S_ID) references Student (S_ID);
ALTER TABLE SC ADD CONSTRAINT fk_SC_course_cid foreign key (C_ID) references Course (C_ID);

alter table SC drop foreign key fk_SC_student_sid;
ALTER TABLE SC drop primary key;
ALTER TABLE SC ADD CONSTRAINT fk_SC_student_sid foreign key (S_ID) references Student (S_ID);

– 01、查询’01’课程比’02’课程成绩高的所有学生的学号；

INSERT INTO SC VALUES ('s1008', 'c1001', 80);

SELECT A.S_ID FROM (SELECT S_ID, score FROM SC WHERE C_ID = 'c1001')A, (SELECT S_ID, score FROM SC WHERE C_ID = 'c1002')B WHERE A.S_ID =B.S_ID AND A.score > B.score;

DELETE FROM SC WHERE S_ID = 'S1004';
SELECT * FROM SC;
SELECT S_ID, score FROM SC WHERE score>60;
SELECT score FROM SC WHERE (C_ID = 'c1001');

– 02、查询平均成绩大于60分的同学的学号和平均成绩；

SELECT S_ID, avg(score) FROM SC GROUP BY S_ID HAVING avg(score) > 60;

– 03、查询所有同学的学号、姓名、选课数、总成绩；

SELECT stu.S_ID AS '学号', stu.Sname AS '姓名', count_C_ID AS '选课数', sum_score AS '总成绩' FROM Student stu LEFT JOIN (SELECT S_ID, count(C_ID) AS count_C_ID, sum(score) AS sum_score FROM SC GROUP BY S_ID) SC ON stu.S_ID = SC.S_ID;

– 04、查询姓“李”的老师的个数；

SELECT count(Tname) FROM Teacher WHERE Tname LIKE '李%';

– 05、查询没学过“叶平”老师课的同学的学号、姓名；
插入一些数据

INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1005', 'c1012', 90);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1002', 'c1012', 85);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1009', 'c1012', 90);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1003', 'c1012', 90);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1003', 'c1011', 88);

SELECT S_ID, Sname FROM Student WHERE S_ID NOT IN (SELECT S_ID FROM SC WHERE C_ID IN (SELECT C_ID FROM course WHERE T_ID = (SELECT T_ID FROM teacher WHERE Tname = '叶平')));

– 06、查询学过“01”并且也学过编号“02”课程的同学的学号、姓名；

SELECT stu.S_ID, stu.Sname FROM Student stu LEFT JOIN SC sc ON stu.S_ID = sc.S_ID WHERE sc.C_ID = 'C1001' AND stu.S_ID IN (SELECT S_ID FROM SC AS SC_1 WHERE SC_1.C_ID = 'c1002' AND SC_1.S_ID = SC.S_ID);

– 07、查询学过“Li Pengfei”老师所教的所有课的同学的学号、姓名；

INSERT INTO SC VALUES ('s1001', 'c1009', 80);
INSERT INTO SC VALUES ('s1002', 'c1009', 90);
INSERT INTO SC VALUES ('s1008', 'c1009', 82);
INSERT INTO SC VALUES ('s1003', 'c1010', 80);
INSERT INTO SC VALUES ('s1005', 'c1010', 100);

SELECT stu.S_ID, stu.Sname FROM Student stu LEFT JOIN SC sc ON stu.S_ID = sc.S_ID WHERE sc.C_ID IN (SELECT C_ID FROM Course WHERE T_ID = (SELECT T_ID FROM Teacher WHERE Tname = 'Li Pengfei'));

– 08、查询课程编号“02”的成绩比课程编号“01”课程低的所有同学的学号、姓名；

SELECT S_ID, Sname FROM student WHERE S_ID IN (SELECT B.S_ID FROM (SELECT S_ID, score FROM SC WHERE C_ID = 'c1001')A, (SELECT S_ID, score FROM SC WHERE C_ID = 'c1002')B WHERE A.S_ID =B.S_ID AND B.score < A.score);

– 09、查询所有课程成绩小于80分的同学的学号、姓名；

-- 左连接
SELECT DISTINCT stu.S_ID, stu.Sname FROM student stu LEFT JOIN SC sc ON stu.S_ID = sc.S_ID WHERE sc.S_ID IN (SELECT S_ID FROM SC GROUP BY S_ID HAVING MIN(score) < 80);

-- 内连接
SELECT DISTINCT stu.S_ID, stu.Sname FROM student stu INNER JOIN SC sc ON stu.S_ID = sc.S_ID AND sc.S_ID IN (SELECT S_ID FROM SC GROUP BY S_ID HAVING MIN(score) < 80);

-- 子查询
SELECT S_ID, Sname FROM student WHERE S_ID IN (SELECT S_ID FROM SC GROUP BY S_ID HAVING MIN(score) < 80);

– 10、查询没有学全所有课的同学的学号、姓名;

INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1010', 'c1007', 100);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1010', 'c1006', 100);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1010', 'c1005', 100);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1010', 'c1004', 100);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1010', 'c1003', 100);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1010', 'c1002', 100);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1010', 'c1001', 100);

-- 思路
SELECT C_ID FROM course; 
SELECT sc.S_ID FROM SC sc GROUP BY sc.S_ID HAVING sc.C_ID IN (SELECT C_ID FROM course);
SELECT sc.S_ID FROM SC sc GROUP BY sc.S_ID HAVING COUNT(sc.C_ID) = (SELECT COUNT(C_ID) FROM course);

SELECT stu.S_ID, stu.Sname FROM student stu WHERE stu.S_ID NOT IN (SELECT sc.S_ID FROM SC sc GROUP BY sc.S_ID HAVING COUNT(sc.C_ID) = (SELECT COUNT(C_ID) FROM course));

– 11、查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名;

SELECT DISTINCT stu.S_ID, stu.Sname FROM student stu LEFT JOIN SC sc ON stu.S_ID = sc.S_ID WHERE sc.C_ID IN (SELECT C_ID FROM SC WHERE S_ID = 's1001');

– 12、查询学过学号为“07”同学所有门课的其他同学学号和姓名；

SELECT stu.S_ID, stu.Sname FROM student stu WHERE stu.S_ID IN (SELECT S_ID FROM SC WHERE C_ID  IN (SELECT C_ID FROM SC WHERE S_ID = 's1007') GROUP BY S_ID);

– 14、查询和“07”号的同学学习的课程完全相同的其他同学学号和姓名；

-- 思路：在07同学学习的课程中  且  不在07同学没学的课程中
SELECT stu.S_ID, stu.Sname FROM student stu WHERE stu.S_ID IN (SELECT S_ID FROM SC WHERE C_ID IN (SELECT C_ID FROM SC WHERE S_ID = 's1007') GROUP BY S_ID) AND stu.S_ID NOT IN (SELECT S_ID FROM SC WHERE C_ID IN (SELECT C_ID FROM course WHERE C_ID NOT IN (SELECT C_ID FROM SC WHERE S_ID = 's1007') GROUP BY S_ID));

– 15、删除学习“Li Pengfei”老师课的SC表记录；

DELETE FROM SC WHERE C_ID IN (SELECT C_ID FROM Course WHERE T_ID = (SELECT T_ID FROM teacher WHERE Tname = 'Li Pengfei'));

– 16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“03”课程的同学学号，“02”，以及“02”课的平均成绩；

INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) SELECT DISTINCT stu.S_ID, 'c1002', (SELECT AVG(score) FROM SC WHERE C_ID = 'C1002') FROM Student stu LEFT JOIN SC sc ON stu.S_ID = sc.S_ID WHERE stu.S_ID NOT IN (SELECT S_ID FROM SC WHERE C_ID = 'c1003');
SELECT * FROM SC;

– 17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示；

INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1001', 'c1013', 100);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1003', 'c1013', 66);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1005', 'c1013', 92);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1008', 'c1013', 88);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1002', 'c1013', 80);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1005', 'c1014', 83);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1002', 'c1014', 100);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1009', 'c1014', 79);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1004', 'c1014', 100);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1003', 'c1014', 90);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1003', 'c1015', 99);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1001', 'c1015', 98);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1010', 'c1015', 95);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1009', 'c1015', 97);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1002', 'c1015', 96);

SELECT S_ID AS 学生ID,
(SELECT score FROM SC WHERE SC.S_ID=t.S_ID AND C_ID='c1013') AS 英语 ,
(SELECT score FROM SC WHERE SC.S_ID=t.S_ID AND C_ID='c1014') AS 数据库 ,
(SELECT score FROM SC WHERE SC.S_ID=t.S_ID AND C_ID='c1015') AS 企业管理 ,
COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩
FROM SC AS t
GROUP BY S_ID
ORDER BY AVG(t.score) DESC

– 18、查询各科成绩最高和最低的分：以如下形式显示：course_id，max，min

SELECT C_ID, MAX(score) AS '最高分', MIN(score) AS '最低分' FROM SC GROUP BY C_ID ;

– 19、按各科平均成绩从低到高和及格率的百分数从高到低顺序

SELECT C_ID, SUM(score >= 60)/COUNT(score) AS '及格率' FROM SC GROUP BY C_ID ORDER BY COUNT(score >= 60)/COUNT(score) DESC;

– 20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): math（01），chinese（02），english（03）

SELECT
AVG(math.score) AS 'math平均成绩', CONCAT(TRUNCATE((SUM(math.score >= 60)/COUNT(math.score)) * 100, 2), '%') AS 'math及格率',
AVG(chinese.score) AS 'chinese', CONCAT(TRUNCATE((SUM(chinese.score >= 60)/COUNT(chinese.score)) * 100, 2), '%') AS 'chinese及格率',
AVG(english.score) AS 'english平均成绩', CONCAT(TRUNCATE((SUM(english.score >= 60)/COUNT(english.score)) * 100, 2), '%') AS 'english及格率'
FROM SC math, SC chinese, SC english
WHERE math.C_ID = (SELECT C_ID FROM course WHERE Cname = 'math') AND chinese.C_ID = (SELECT C_ID FROM course WHERE Cname = 'chinese') AND english.C_ID = (SELECT C_ID FROM course WHERE Cname = 'english');

-- 分三行展示
SELECT AVG(score), SUM(score >= 60)/COUNT(score) AS '及格率' FROM SC WHERE C_ID IN (SELECT C_ID FROM course WHERE Cname = 'math' OR Cname = 'chinese' OR Cname = 'english') GROUP BY C_ID;

– 21、查询不同老师所教不同课程平均分从高到低显示

SELECT Tname, Cname, AVG(score) FROM teacher tea, course cou, SC WHERE tea.T_ID = cou.T_ID AND cou.C_ID = SC.C_ID GROUP BY SC.C_ID ORDER BY AVG(score) DESC;

– 22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：math(01),chinese(02),english(03)——student_id,student_name,math,chinese,english,avg_score;

INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1001', 'c1010', 69);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1002', 'c1010', 88);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1004', 'c1010', 42);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1008', 'c1010', 92);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1009', 'c1010', 70);

-- 不是很成功的【哭】
SELECT cou.Cname, stu.S_ID, stu.Sname, score
FROM course cou, student stu, SC
WHERE cou.C_ID = SC.C_ID AND stu.S_ID = SC.S_ID AND SC.C_ID IN (SELECT C_ID FROM course WHERE Cname = 'math' OR Cname = 'chinese' OR Cname = 'english')
ORDER BY cou.C_ID, score DESC
LIMIT 3, 4

– 23、统计列印各科成绩的各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[-60]

-- LEFT JOIN 可以，BETWEEN TO 不可以，结构 Then 1 ELSE 0 End 不能少！但是 BETWEEN AND 可以【笑哭】
Select cou.C_ID, cou.Cname, 
SUM(Case When score >= 85 AND score < 100 Then 1 ELSE 0 End) As '[100-85]', 
SUM(Case When score >= 70 AND score < 84 Then 1 ELSE 0 End) As '[84-70]', 
SUM(Case When score >= 60 AND score < 69 Then 1 ELSE 0 End) As '[69-60]', 
SUM(Case When score < 60 Then 1 ELSE 0 End) As '[60-]'
FROM SC, course cou WHERE SC.C_ID = cou.C_ID GROUP BY cou.C_ID, cou.Cname;

– 24、查询学生平均成绩及其名次

SELECT S_ID, AVG(score) AS '平均成绩', rank() OVER(ORDER BY AVG(score) DESC) '名次' FROM SC GROUP BY S_ID;

– 25、查询各科成绩前三名的记录:(不考虑成绩并列情况)

-- 这好像是oracle语法
SELECT * FROM (SELECT C_ID, S_ID, score, ROW_NUMBER() OVER(PARTITION BY C_ID ORDER BY score DESC) rk FROM SC) stu WHERE stu.rk < 4;

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

SELECT C_ID, COUNT(S_ID) AS '选修人数' FROM SC GROUP BY C_ID;

– 27、查询出只选修了2门课程的全部学生的学号和姓名

SELECT stu.S_ID, stu.Sname FROM student stu WHERE stu.S_ID IN (SELECT sc.S_ID FROM SC sc GROUP BY sc.S_ID HAVING COUNT(sc.C_ID) = 2);

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

SELECT SUM(Ssex = '男') AS '男生人数', SUM(Ssex = '女') AS '女生人数' FROM student;

– 29、查询姓“张”的学生名单

SELECT Sname FROM student WHERE Sname LIKE '张%';

– 30、查询同名同性学生名单，并统计同名人数

SELECT Sname, COUNT(Sname) FROM student GROUP BY Sname HAVING COUNT(Sname) > 1;

– 31、1990年出生的学生名单(注：Student表中Sage列的类型是datetime)

-- 因为Sage为INT型数据，涉及到其它查询，所以新增一列
ALTER TABLE student ADD COLUMN birthday DATETIME;

SELECT Sname, birthday FROM student WHERE birthday BETWEEN '1990-01-01' AND '1990-12-31';

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

SELECT C_ID, AVG(score) FROM SC GROUP BY C_ID ORDER BY AVG(score), C_ID DESC;

– 34、查询课程名称为“MATH”，且分数低于60的学生姓名和分数

SELECT * FROM SC WHERE C_ID = 'c1009';
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1001', 'c1009', 88);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1002', 'c1009', 77);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1003', 'c1009', 66);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1005', 'c1009', 55);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1010', 'c1009', 44);

SELECT Sname, score FROM student stu, SC sc WHERE stu.S_ID = sc.S_ID AND score < 60 AND C_ID = (SELECT C_ID FROM course WHERE Cname = 'math');

– 35、查询所有学生的选课情况；

SELECT stu.S_ID, stu.Sname, cou.Cname FROM student stu, SC, course cou WHERE stu.S_ID = SC.S_ID AND SC.C_ID = cou.C_ID;

– 36、查询每门课程成绩在70分以上的姓名、课程名称和分数；
– 分组错误

SELECT stu.Sname, cou.Cname, score FROM student stu, SC, course cou WHERE stu.S_ID = SC.S_ID AND SC.C_ID = cou.C_ID AND score > 70;

– 37、查询不及格的课程，显示学号、姓名、课程号、成绩

SELECT stu.S_ID, stu.Sname, C_ID, score FROM student stu, SC WHERE stu.S_ID = SC.S_ID AND score < 60;

– 38、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名；

SELECT stu.S_ID, stu.Sname FROM student stu, SC WHERE stu.S_ID = SC.S_ID AND SC.C_ID = 'c1003' AND score > 80;

– 39、求选了课程的学生人数

SELECT COUNT(S_ID) FROM student WHERE S_ID IN (SELECT S_ID FROM SC);

– 40、查询选修“Li Pengfei”老师所授课程的学生中，成绩最高的学生姓名及其成绩

INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1003', 'c1010', 88);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1005', 'c1010', 72);

SELECT S_ID, score FROM SC WHERE C_ID IN (SELECT C_ID FROM course WHERE T_ID = (SELECT T_ID FROM teacher WHERE Tname = 'Li Pengfei')) ORDER BY score DESC LIMIT 1;

– 41、查询各个课程及相应的选修人数

SELECT C_ID, COUNT(C_ID) FROM SC GROUP BY C_ID;

– 42、查询不同课程成绩相同的学生的学号、课程号、学生成绩

SELECT sc1.S_ID, sc1.C_ID, sc1.score, sc2.S_ID, sc2.C_ID FROM SC sc1, SC sc2 WHERE sc1.score = sc2.score AND sc1.C_ID != sc2.C_ID;

– 43、查询每门课程成绩最好的前两名

SELECT * FROM (SELECT C_ID, S_ID, score, ROW_NUMBER() OVER(PARTITION BY C_ID ORDER BY score DESC) rk FROM SC) stu WHERE stu.rk < 3;

– 44、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

-- 这里不能用WHERE
SELECT C_ID, COUNT(C_ID) FROM SC GROUP BY C_ID HAVING COUNT(C_ID) > 5 ORDER BY COUNT(C_ID) DESC, C_ID ASC;

– 45、检索至少选修3门课程的学生学号

SELECT stu.S_ID FROM student stu WHERE stu.S_ID IN (SELECT sc.S_ID FROM SC sc GROUP BY sc.S_ID HAVING COUNT(sc.C_ID) >= 3);

– 46、查询全部学生都选修的课程的课程号和课程名

SELECT C_ID, COUNT(C_ID) FROM SC GROUP BY C_ID;
SELECT S_ID FROM SC WHERE C_ID = 'c1002' ORDER BY S_ID;
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1006', 'c1002', 90);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1007', 'c1002', 90);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1011', 'c1002', 90);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1012', 'c1002', 90);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1013', 'c1002', 90);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1014', 'c1002', 90);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1015', 'c1002', 90);

SELECT C_ID, Cname FROM course WHERE C_ID IN (SELECT C_ID FROM SC GROUP BY C_ID HAVING COUNT(S_ID) = (SELECT COUNT(S_ID) FROM student));

– 47、查询没学过“叶平”老师讲授的任一门课程的学生姓名

SELECT Sname FROM student WHERE S_ID NOT IN (SELECT S_ID FROM SC WHERE C_ID IN (SELECT C_ID FROM course WHERE T_ID = (SELECT T_ID FROM teacher WHERE Tname = '叶平')));

– 48、查询两门以上不及格课程的同学的学号及其平均成绩

INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1009', 'c1001', 48);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1009', 'c1003', 85);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1003', 'c1003', 56);

SELECT S_ID, AVG(score) FROM SC WHERE S_ID IN (SELECT S_ID FROM SC WHERE score < 60 GROUP BY S_ID HAVING COUNT(S_ID) >= 2) GROUP BY S_ID;

– 49检索“04”课程分数小于60，按分数降序排列的同学学号

INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1001', 'c1004', 98);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1002', 'c1004', 59);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1003', 'c1004', 80);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1004', 'c1004', 82);
INSERT INTO `school_sql50`.`sc`(`S_ID`, `C_ID`, `score`) VALUES ('s1009', 'c1004', 52);

SELECT S_ID, score FROM SC WHERE C_ID = 'c1004' AND score < 60 ORDER BY score;

– 50、删除“002”同学的“01”课程的成绩

DELETE FROM SC WHERE S_ID = 's1002' AND C_ID = 'c1001';
W♡
关注
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值