一昏滴，表關(guān)系的練習(xí)測試
請創(chuàng)建如下表關(guān)系猴鲫，并建立相關(guān)約束
image.png
一，創(chuàng)建表結(jié)構(gòu)數(shù)據(jù)：
創(chuàng)建的話肯定先創(chuàng)建沒有關(guān)聯(lián)的表谣殊，老師绵跷，課程（關(guān)聯(lián)老師）肚吏，年級乖杠，班級（關(guān)聯(lián)年級）瘦棋，學(xué)生（關(guān)聯(lián)班級），
班級任職表 （關(guān)聯(lián)老師鲜棠，課堂）
create table teacher(
tid int primary key auto_increment,
tname varchar(16) not null
);
create table class_grade(
gid int primary key auto_increment,
gname varchar(16) not null unique
);
create table course(
cid int primary key auto_increment,
cname varchar(16) not null,
teacher_id int not null,
foreign key(teacher_id) references teacher(tid)
);
create table class(
cid int primary key auto_increment,
caption varchar(16) not null,
grade_id int not null,
foreign key(grade_id) references class_grade(gid)
on update cascade
on delete cascade
);
create table student(
sid int primary key auto_increment,
sname varchar(16) not null,
gender enum('女','男') not null default '男',
class_id int not null,
foreign key(class_id) references class(cid)
on update cascade
on delete cascade
);
create table score(
sid int not null unique auto_increment,
student_id int not null,
course_id int not null,
score int not null,
primary key(student_id,course_id),
foreign key(student_id) references student(sid)
on delete cascade
on update cascade,
foreign key(course_id) references course(cid)
on delete cascade
on update cascade
);
create table teach2cls(
tcid int not null unique auto_increment,
tid int not null,
cid int not null,
primary key(tid,cid),
foreign key(tid) references teacher(tid)
on delete cascade
on update cascade,
foreign key(cid) references class(cid)
on delete cascade
on update cascade
);
2肌厨，插入表數(shù)據(jù)
插入數(shù)據(jù)
老師的數(shù)據(jù)
年級的數(shù)據(jù)
班級的數(shù)據(jù)
課程的數(shù)據(jù)
學(xué)生的數(shù)據(jù)
成績的數(shù)據(jù)
老師班級的數(shù)據(jù)
insert into teacher(tname) values
('張三'),
('李四'),
('王五');
insert into class_grade(gname) values
('一年級'),
('二年級'),
('三年級');
insert into class(caption,grade_id) values
('一年一班',1),
('一年二班',1),
('一年三班',1),
('二年一班',2),
('二年二班',2),
('二年三班',2),
('三年一班',3),
('三年二班',3),
('三年三班',3);
insert into course(cname,teacher_id) values
('生物',1),
('體育',1),
('物理',2),
('數(shù)學(xué)',2),
('馬克思',3),
('外語',3),
('計算機(jī)',3);
insert into student(sname,gender,class_id) values
('喬丹','男',1),
('艾弗森','男',1),
('科比','男',2);
insert into score(student_id,course_id,score) values
(1,1,60),
(1,2,59),
(1,3,58),
(2,1,99),
(2,2,99),
(2,3,89),
(3,1,59),
(3,3,30);
insert into teach2cls(tid,cid) values
(1,1),
(1,2),
(1,3),
(1,5),
(2,4),
(2,6),
(2,8),
(2,9),
(2,1),
(2,5),
(3,7),
(3,1),
(3,3),
(3,5),
(3,9);
補(bǔ)充數(shù)據(jù)
insert into teacher(tname) values
('趙六'),
('苗七');
insert into class_grade(gname) values
('四年級');
insert into class(caption,grade_id) values
('四年一班',4),
('四年二班',4),
('四年三班',4),
('四年四班',4);
insert into course(cname,teacher_id) values
('線性代數(shù)',4);
insert into student(sname,gender,class_id) values
('張一','女',3),
('詹姆斯','男',3),
('荷花','女',3),
('杜蘭特','男',3),
('哈登','男',4),
('尼克','男',4),
('青青','女',4),
('阿里扎','男',4);
insert into score(student_id,course_id,score) values
(3,4,60),
(4,1,59),
(4,2,100),
(4,3,90),
(4,4,80),
(5,1,59),
(5,2,33),
(5,3,12),
(5,4,88),
(6,1,100),
(6,2,60),
(6,3,59),
(6,4,100),
(7,1,20),
(7,2,36),
(7,3,57),
(7,4,60),
(8,1,61),
(8,2,59),
(8,3,62),
(8,4,59),
(9,1,60),
(9,2,61),
(9,3,21);
insert into teach2cls(tid,cid) values
(4,1),
(4,2),
(4,3),
(4,4),
(5,1),
(5,2),
(5,3),
(5,4);
二，操作表格內(nèi)容
1豁陆、自行創(chuàng)建測試數(shù)據(jù)柑爸；
上面已經(jīng)完成。
2盒音、查詢學(xué)生總?cè)藬?shù)表鳍；
select count(sid) from student;
3、查詢“生物”課程和“物理”課程成績都及格的學(xué)生id和姓名祥诽；
思路：獲取所有生物課程的人（學(xué)號譬圣，成績）--臨時表
獲取所有物理課程的人（學(xué)號，成績）--臨時表
根據(jù)學(xué)號連接兩個臨時表：學(xué)號雄坪，物理成績厘熟，生物成績
然后篩選及格的
select sid,sname
from student
where sid in(
select score.student_id from score inner join course on score.course_id=course.cid
where course.cname in('生物','物理') and score.score >=60
group by score.student_id having count(course_id) = 2);
4、查詢每個年級的班級數(shù)维哈，取出班級數(shù)最多的前三個年級绳姨；
思路：首先分析班級前三的情況，分為班級數(shù)相同的情況和班級數(shù)不同的情況
如果班級數(shù)相同阔挠，那么只需要考慮在班級里面統(tǒng)計班級數(shù)量即可飘庄，
然后在班級年級表中取出對應(yīng)的年級數(shù)目
如果班級數(shù)不相同，那么首先班級里面統(tǒng)計班級數(shù)量购撼，
然后在按照降序排列跪削，取前三即可
#包含班級數(shù)不相同的排名前三年級
select class_grade.gname from class_grade inner join(
select grade_id,count(id) as count_cid from class group by grade_id order by count_cid desc limit 3)
as t1 on class_grade.gid = t1.grade_id;
#包含了班級數(shù)相同的排名前三年級
select gname from class_grade where gid in (
select grade_id from class group by grade_id having count(cid) in (
5、查詢平均成績最高和最低的學(xué)生的id和姓名以及平均成績份招；
create view t1 as
select student_id avg(score) as avg_score from score group by student_id;
select sname,avg_score from t1 left join student on t1.student_id =student.sid
where t1.avg_score = ( select max(t1.avg_score) from t1) or t1.avg_score =
(select min(t1.avg_score) from t1);
6切揭、查詢每個年級的學(xué)生人數(shù)；
思路：先在學(xué)生表和班級表對應(yīng)一下, 然后在對應(yīng)班級表中查找學(xué)生人數(shù)
select t1.grade_id,count(t1.sid) as count_student from (
select student.sid ,class.grade_id from student,class
where student.class_id =class.cid) as t1 group by t1.grade_id;
7锁摔、查詢每位學(xué)生的學(xué)號廓旬，姓名，選課數(shù)谐腰，平均成績
思路：學(xué)生表中有學(xué)生學(xué)號孕豹，姓名，性別十气，班級 成績表中對應(yīng)成績励背，所以
我們可以聯(lián)立成績表和學(xué)生表，并按學(xué)生id分類砸西，直接查找即可叶眉。
select score.student_id,student.sname,sum(score.course_id),avg(score.score)
from score left join student on score.student_id = student.sid
group by score.student_id;
8址儒、查詢學(xué)生編號為“2”的學(xué)生的姓名、該學(xué)生成績最高的課程名衅疙、成績最低的課程名及分?jǐn)?shù)莲趣；
思路：首先在成績表中查找學(xué)生編號為2 的學(xué)生的最大最小成績，學(xué)生id饱溢，課程id喧伞，
然后在課程表和學(xué)生表中找到對應(yīng)的學(xué)生姓名和課程名稱，
最后聯(lián)立表格得出學(xué)生姓名绩郎，課程名稱潘鲫，分?jǐn)?shù)
select student.sname,course.cname,t1.score from (
select student_id,course_id,score from score where student_id = 2 and score in((
select max(score) from score where student_id = 2),
(select min(score) from score where student_id = 2))) as t1
inner join student on t1.student_id = student.sid
inner join course on t1.course_id = course.cid;
9、查詢姓“李”的老師的個數(shù)和所帶班級數(shù)肋杖；
思路：首先在老師表中尋找姓李老師的id
然后在teach2cls中找到老師和班級的聯(lián)系溉仑，并統(tǒng)計姓李老師所帶的班級數(shù)
最后在老師表中查詢老師id和姓名。
select teacher.tid as '姓李id', teacher.tname as '老師姓名' ,GROUP_CONCAT(teach2cls.cid) as '班級數(shù)'
from teacher left join teach2cls on teacher.tid = teach2cls.tid
where teacher.tname like '李%' group by teacher.tid;
10兽愤、查詢班級數(shù)小于5的年級id和年級名
思路：首先查詢班級表中班級小于5的年級id號碼
然后在年級表中查找對應(yīng)班級表中的年級id即可
select gid,gname from class_grade where gid in (
select grade_id from class group by grade_id having count(caption)<5
);
查詢班級信息彼念，包括班級id、班級名稱浅萧、年級逐沙、年級級別(12為低年級，34為中年級洼畅，56為高年級)吩案，示例結(jié)果如下；
班級id | 班級名稱| 年級| 年級級別
1 | 一年一班| 一年級| 低
select
class.cid as '班級id',
class.caption as '班級名稱',
class_grade.gname as '年級',
case
when class_grade.gid between 1 and 2 then '低'
when class_grade.gid between 3 and 4 then '中'
when class_grade.gid between 5 and 6 then '高' else 0
end as '年級級別'
from class
left join class_grade on class.grade_id = class_grade.gid;
12帝簇、查詢學(xué)過“張三”老師2門課以上的同學(xué)的學(xué)號徘郭、姓名；
首先找到張三老師的id丧肴，
然后聯(lián)立成績表和課程表残揉，并在成績表中查看選修張三老師課程數(shù)量大于2的學(xué)生id
最后在學(xué)生表中查找學(xué)生的學(xué)號，姓名芋浮。
select sid,sname from student
where sid in
(
select score.student_id from score
left join course
on score.course_id = course.cid
where course.teacher_id in
(
select tid from teacher
where tname = '張三'
)
group by student_id
having count(course.cid) >2
);
13抱环、查詢教授課程超過2門的老師的id和姓名
思路：先在course中按照老師的id進(jìn)行分組，并統(tǒng)計代課大于2門的老師id的總數(shù)---臨時表
然后在teacher表中查找老師的id和姓名
select tid,tname from teacher where tid in (
select teacher_id from course group by teacher_id having count(cid)>2);
14纸巷、查詢學(xué)過編號“1”課程和編號“2”課程的同學(xué)的學(xué)號镇草、姓名；
思路：創(chuàng)建一個虛擬表瘤旨，用于查找課程中的編號1和編號2課程
然后在學(xué)生表中查找學(xué)生的學(xué)號和姓名
select sid,sname from student where sid in (
select distinct student_id from score where course_id in (1,2));
15梯啤、查詢沒有帶過高年級的老師id和姓名；
思路：在班級表中設(shè)定高年級為五六年級存哲，---虛擬表
然后在teach2cls中找到老師和班級的聯(lián)系 ---虛擬表
最后在老師表中查詢老師id和姓名
select tid,tname from teacher where tid not in (select tid from teach2cls
where cid in (select cid from class where grade_id in (5,6)));
16因宇、查詢學(xué)過“張三”老師所教的所有課的同學(xué)的學(xué)號七婴、姓名；
思路：首先將張三老師的id從課程表中和老師表中對應(yīng)起來察滑，并找出他教的課程id ————虛擬表
然后在成績表中查找與上面表對應(yīng)的課程id所對應(yīng)的學(xué)生的id
最后在學(xué)生表中查找學(xué)生的學(xué)號本姥，姓名。
select sid,sname from student where sid in (
select student_id from score where course_id in (
select cid from course inner join teacher on teacher.tid = course.teacher_id
where teacher.tname = '張三'));
17杭棵、查詢帶過超過2個班級的老師的id和姓名；
思路： 先在teac2cls中找到班級cid大于2的老師id（ｔｉｄ）－－－－虛擬表
然后在老師表中找老師ｉｄ和姓名對應(yīng)的ｉｄ
select tid,tname from teacher where tid in (
select tid from teach2cls group by tid having count(cid)>2);
18氛赐、查詢課程編號“2”的成績比課程編號“1”課程低的所有同學(xué)的學(xué)號魂爪、姓名；
思路：先在成績表中查找課程2 的學(xué)生id和成績艰管，設(shè)為表1 滓侍，
再查找課程編號為1的學(xué)生id和成績，設(shè)為表二
最后在學(xué)生表中查找課程2比課程1的成績低的學(xué)生的學(xué)號和姓名牲芋。
select sid,sname from student where sid in (
select t1.student_id from (
select student_id, score from score where course_id = 2 group by student_id) as t1,
select student_id, score from score where course_id = 1 group by student_id) as t2
where t1.student_id = t2.student_id and t1.score < t2.score);
19撩笆、查詢所帶班級數(shù)最多的老師id和姓名；
思路：首先在老師-課程表中統(tǒng)計老師所帶的課程數(shù)量缸浦，并按照老師id分類夕冲，并取一個
然后在老師表中查找對應(yīng)老師id和姓名
select tid,tname from teacher where tid =(
select tid from teach2cls group by tid order by count(cid) desc limit 1);
20、查詢有課程成績小于60分的同學(xué)的學(xué)號裂逐、姓名歹鱼；
思路：先在成績表中查找成績小于６０分的學(xué)生ｉｄ
然后學(xué)生表中查找學(xué)生id與成績表中的學(xué)生id對應(yīng)的學(xué)生學(xué)號，姓名
select sid,sname from student where sid in (
select distinct student_id from score where score<60 );
21卜高、查詢沒有學(xué)全所有課的同學(xué)的學(xué)號弥姻、姓名；
思路：首先分析題目意思掺涛，是沒有學(xué)完所有課的同學(xué)
那么考慮學(xué)生應(yīng)該是選完課程庭敦，沒有考試，視為沒有學(xué)完
所以首先查找學(xué)生選擇的課程薪缆，在成績表中是否有對應(yīng)的成績秧廉，如果有則學(xué)完，如果沒有則沒有學(xué)完
select sid ,sname from student where sid not in(
select student_id from score group by student_id having count(course_id)=
(select count(cid) from course)
);
22矮燎、查詢至少有一門課與學(xué)號為“1”的同學(xué)所學(xué)相同的同學(xué)的學(xué)號和姓名定血；
思路：首先查找學(xué)號為1的學(xué)生的成績id，
然后在成績表中按照學(xué)號對應(yīng)上面的成績id
最后在學(xué)生表中查找學(xué)生的學(xué)號诞外，姓名澜沟。
select sid,sname from student where sid in (
select student_id from score where course_id in (
select course_id from score where student_id =1) group by student_id);
23、查詢至少學(xué)過學(xué)號為“1”同學(xué)所選課程中任意一門課的其他同學(xué)學(xué)號和姓名峡谊；
思路：首先查找學(xué)號為1的學(xué)生所選的課程id茫虽，
然后再對應(yīng)其他學(xué)生所選的課程id刊苍，
最后在學(xué)生表中查找學(xué)生的學(xué)號，姓名濒析。
select sid,sname from student where sid in (
select student_id from score where course_id in (
select course_id from score where student_id = 1)
group by student_id) and sid !=1;
24正什、查詢和“2”號同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的學(xué)號和姓名；
思路：首先在成績表中查詢學(xué)生2的課程id号杏，
然后進(jìn)行篩選其他人的課程id和姓名id婴氮，不包含2號學(xué)生，
最后在學(xué)生表中查找學(xué)生的學(xué)號盾致，姓名主经。
select sid,sname from student where sid in (
select score.student_id from score ,(
select course_id from score where student_id = 2) as t1
where score.course_id = t1.course_id and score.student_id !=2
group by score.student_id
having count(score.course_id) =(
select count(course_id) from score where student_id = 2));
25、刪除學(xué)習(xí)“張三”老師課的score表記錄庭惜；
思路：首先在score表中找到對應(yīng)張三老師課程罩驻，
然后刪除即可
delete from score where course_id in (
select course.cid from course,teacher where
course.teacher_id =teacher.tid and teacher.tname = '張三');
26、向score表中插入一些記錄护赊，這些記錄要求符合以下條件：
①沒有上過編號“2”課程的同學(xué)學(xué)號惠遏；
②插入“2”號課程的平均成績；
思路：首先在score找出沒有上過編號2課程的同學(xué)id骏啰，
然后在成績表中找到編號2的學(xué)生的所有成績节吮，取平均值
最后插入數(shù)據(jù)即可。
insert into score(student_id,course_id,score)
select t1.sid,2,t2.avg from (
select sid from student where sid not in (
select student_id from score where course_id = 2)) as t1,
(select avg(score) as avg from score group by course_id having course_id =2) as t2;
27判耕、按平均成績從低到高顯示所有學(xué)生的“語文”课锌、“數(shù)學(xué)”、“英語”三門的課程成績祈秕，按如下形式顯示： 學(xué)生ID,語文,數(shù)學(xué),英語,有效課程數(shù),有效平均分渺贤；
思路：注意平均成績是由低到高desc
最重要的是查詢各科成績，
在課程表中找到成績表中對應(yīng)的課程id请毛，然后在成績表中查找對應(yīng)的成績
select sc.student_id,
(select score.score from score left join course on score.course_id = course.cid
where course.cname = '語文' and score.student_id = sc.student_id) as Chinese,
(select score.score from score left join course on score.course_id = course.cid
where course.cname = '數(shù)學(xué)' and score.student_id = sc.student_id) as Math,
(select score.score from score left join course on score.course_id = course.cid
where course.cname = '外語' and score.student_id = sc.student_id) as English,
count(sc.course_id),avg(sc.score)
from score as sc group by sc.student_id order by avg(sc.score) asc;
28志鞍、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分方仿，最低分固棚；
思路：直接在score中查找課程id，最高分?jǐn)?shù)仙蚜，最低分?jǐn)?shù)
select course_id ,max(score),min(score) from score
group by course_id;
29此洲、按各科平均成績從低到高和及格率的百分?jǐn)?shù)從高到低順序；
思路：平均成績asc 及格率desc
在score中找到學(xué)生的平均成績委粉，并求出及格率呜师。
select course_id,avg(score) as avg_score,
sum(case when score.score > 60 then 1 else 0 end) / count(1) * 100 as percent
from score group by course_id order by avg(score) asc,percent desc;
30、課程平均分從高到低顯示（現(xiàn)實任課老師）贾节；
思路：查找成績表中的各科平均分?jǐn)?shù)汁汗，并讓分?jǐn)?shù)對應(yīng)課程表中的課程id
然后把課程表中的課程id對應(yīng)的老師課程表的老師id
最后在老師表中查詢老師id和姓名
select t1.course_id,t1.avg_score,teacher.tname from course,teacher,
(select course_id,avg(score) as avg_score from score group by course_id ) as t1
where course.cid = t1.course_id and course.teacher_id = teacher.tid
order by avg_score desc;
31衷畦、查詢各科成績前三名的記錄(不考慮成績并列情況)
select score.sid,score.student_id, score.course_id,score.score,
t1.first_score,t1.second_score,t1.third_score
from score inner join (
select s1.sid,(select score from score as s2 where s1.course_id = s2.course_id
order by score desc limit 0,1) as first_score,
(select score from score as s3 where s1.course_id = s3.course_id
order by score desc limit 1,1) as second_score,
(select score from score as s4 where s1.course_id = s4.course_id
order by score desc limit 2,1) as third_score
from score as s1) as t1 on score.sid = t1.sid
where score.score in (t1.first_score,t1.second_score,t1.third_score);
32、查詢每門課程被選修的學(xué)生數(shù)知牌；
思路：在成績表中查找課程id祈争，每門課的學(xué)生總數(shù)，
最后在課程表中找到對應(yīng)的課程名稱
select course.cname as '課程名稱',t1.student_num as '學(xué)生數(shù)量' from course,
(select course_id,count(student_id) as student_num from score
group by course_id) as t1 where course.cid = t1.course_id;
33角寸、查詢選修了2門以上課程的全部學(xué)生的學(xué)號和姓名菩混；
思路：在成績表中查找課程id大于2們的學(xué)生id
然后在學(xué)生表中查找對應(yīng)的學(xué)生的學(xué)號和姓名
select sid,sname from student where sid in (
select student_id from score group by student_id having count(course_id)>2);
34、查詢男生扁藕、女生的人數(shù)墨吓，按倒序排列；
思路： 在學(xué)生表中按照性別分類 按照數(shù)量排序desc
select gender,count(sid) as num from student
group by gender order by num desc;
35纹磺、查詢姓“張”的學(xué)生名單；
思路：在學(xué)生表中查找姓張的學(xué)生名單
select sid,sname,gender from student where sname like '張%';
36亮曹、查詢同名同姓學(xué)生名單橄杨，并統(tǒng)計同名人數(shù)；
思路：直接在學(xué)生表中查看學(xué)生姓名相同的學(xué)生照卦，并統(tǒng)計人數(shù)
select sname,count(sname) from student group by sname having count(sname)>1;
37式矫、查詢每門課程的平均成績，結(jié)果按平均成績升序排列役耕，平均成績相同時采转，按課程號降序排列；
思路：在成績表中按照課程id 查找學(xué)生的平均成績
select course_id,avg(score) as avg_score from score
group by course_id order by avg_score,course_id desc;
38瞬痘、查詢課程名稱為“數(shù)學(xué)”故慈，且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù)；
思路：先在course中查找課程為數(shù)學(xué)的課程id號碼框全，
然后在score中查找數(shù)學(xué)分?jǐn)?shù)低于60的學(xué)生id,分?jǐn)?shù)
最后在學(xué)生表中查找對于id 的學(xué)生姓名
select student.sname,score.score from score left join student
on score.student_id = student.sid where score.course_id = (
select cid from course where cname ='數(shù)學(xué)') and score.score <+60;
39察绷、查詢課程編號為“3”且課程成績在80分以上的學(xué)生的學(xué)號和姓名；
思路：先創(chuàng)建一個課程編號為3 且成績在80分以上的學(xué)生id表津辩，在score中 --虛擬表
然后在student中查找對應(yīng)id的學(xué)生姓名
select sid,sname from student where sid in (
select student_id from score where score> 80 and course_id = 3
);
40拆撼、求選修了課程的學(xué)生人數(shù)
思路：直接在成績表中按照課程id排序，并統(tǒng)計學(xué)生id即可
select course_id,count(student_id) from score group by course_id;
41喘沿、查詢選修“王五”老師所授課程的學(xué)生中闸度，成績最高和最低的學(xué)生姓名及其成績；
思路：首先在老師表中尋找姓王老師的id蚜印，
然后對應(yīng)課程表中對應(yīng)的所教課程id莺禁，
然后在score中查找課程所對應(yīng)的成績和學(xué)生id
最后在學(xué)生表中查找學(xué)生的學(xué)號，姓名窄赋。
select student.sname,score,score from score
left join student on score.student_id = student.sid where course_id in (
select cid from course where teacher_id in (
select tid from teacher where tname = '王五'))
order by score.score desc limit 1;
42睁宰、查詢各個課程及相應(yīng)的選修人數(shù)肪获；
思路：聯(lián)立課程表中的課程id和成績表中的課程id，
然后查找各個課程對應(yīng)的選修人數(shù)
select course.cname,count(student_id) from score
left join course on score.course_id = course.cid group by course_id;
43柒傻、查詢不同課程但成績相同的學(xué)生的學(xué)號孝赫、課程號、學(xué)生成績红符；
思路：查找不同學(xué)生之間青柄，課程不同成績相同
查找同一個學(xué)生，課程不同成績相同
的學(xué)生预侯，課程號致开，學(xué)生成績
#1，不同學(xué)生之間
select distinct s1.student_id,s2.student_id,s1.course_id,s2.course_id,s1.score,s2.score
from score as s1,score as s2
where s1.course_id != s2.course_id and s1.score = s2.score;
#2萎馅，同一個學(xué)生
select distinct s1.student_id,s2.student_id,s1.course_id,s2.course_id,s1.score,s2.score
from score as s1,score as s2
where s1.student_id = s2.student_id and
s1.course_id != s2.course_id and s1.score = s2.score;
44双戳、查詢每門課程成績最好的前兩名學(xué)生id和姓名；
select
student.sid,
student.sname,
t2.course_id,
t2.score,
t2.first_score,
t2.second_score
from
student
inner join (
select
score.student_id,
score.course_id,
score.score,
t1.first_score,
t1.second_score
from
score
inner join (
select
s1.sid,
(select s2.score from score as s2 where s1.course_id = s2.course_id order by s2.score desc limit 0,1) as first_score,
(select s3.score from score as s3 where s1.course_id = s3.course_id order by s3.score desc limit 1,1) as second_score
from
score as s1
) as t1 on score.sid = t1.sid
where
score.score in (
t1.first_score,
t1.second_score
)
) as t2 on student.sid = t2.student_id;
45糜芳、檢索至少選修兩門課程的學(xué)生學(xué)號飒货；
思路：在score表中直接查找大于2門課程的學(xué)生id
select student_id from score group by student_id having
count(course_id)>=2;
46、查詢沒有學(xué)生選修的課程的課程號和課程名峭竣；
思路：在成績表中按照課程id分組作為一個臨時表
如果在課程表中塘辅，id沒有在上面的臨時表中，則就是沒有學(xué)生選修
select cid,cname from course where cid not in
(select course_id from score group by course_id);
47皆撩、查詢沒帶過任何班級的老師id和姓名扣墩；
思路：在老師-課程表中按照老師分組作為一個臨時表
如果在老師表中，id沒有在這個臨時表扛吞，則就是沒有帶過任何班級
select tid tname from teacher where tid not in (
select tid from teach2cls group by tid);
48呻惕、查詢有兩門以上課程超過80分的學(xué)生id及其平均成績；
思路：首先滥比，在成績表中獲取有兩門課程成績大于80分的學(xué)生id蟆融，---臨時表
然后在成績表中查找其id和平均成績
或者在score表中直接查找大于2門課程的學(xué)生id和平均成績
select student_id,avg(score) from score
where student_id in (
select student_id from score where score > 80 group by student_id
having count(course_id) > 2);
select student_id,avg(score) from score
where score >80 group by student_id having count(course_id) >2;
49、檢索“3”課程分?jǐn)?shù)小于60守呜，按分?jǐn)?shù)降序排列的同學(xué)學(xué)號型酥；
思路：查找成績表中課程三而且分?jǐn)?shù)小于60的學(xué)生學(xué)號，并按照分?jǐn)?shù)降序排列desc
select student_id,score from score where course_id = 3 and score<60
order by score desc;
50查乒、刪除編號為“2”的同學(xué)的“1”課程的成績弥喉；
思路：首先在成績表中，先把編號為2和課程為1的找到玛迄，
然后刪除在成績表中對應(yīng)學(xué)生的成績
delete from score where sid =
(select sid from score where student_id = 2 and course_id=1
);
51由境、查詢同時選修了物理課和生物課的學(xué)生id和姓名；
思路：在課程中首先找到物理，生物的id虏杰，
然后在成績表中對應(yīng)課程的id讥蟆，此時找到了生物課和物理課的id
最后在學(xué)生表中，找到學(xué)生id和姓名
select sid,sname from student where sid in(
select student_id from score where course_id in (
select cid from course where course.cname in('物理','生物'))
group by student_id having count(course_id) = 2
);
補(bǔ)充說明:
1纺阔、自行創(chuàng)建測試數(shù)據(jù)瘸彤；
見create_tabledata.txt
insert_tabledata.txt
2、查詢學(xué)生總?cè)藬?shù)笛钝；
select count(sid) from student;
3质况、查詢“生物”課程和“物理”課程成績都及格的學(xué)生id和姓名；
思路：獲取所有生物課程的人（學(xué)號玻靡，成績）--臨時表
獲取所有物理課程的人（學(xué)號结榄，成績）--臨時表
根據(jù)學(xué)號連接兩個臨時表：學(xué)號，物理成績囤捻，生物成績
然后篩選及格的
select sid,sname
from student
where sid in(
select score.student_id from score inner join course on score.course_id=course.cid
where course.cname in('生物','物理') and score.score >=60
group by score.student_id having count(course_id) = 2);
***4臼朗、查詢每個年級的班級數(shù)，取出班級數(shù)最多的前三個年級蝎土；
思路：首先分析班級前三的情況视哑，分為班級數(shù)相同的情況和班級數(shù)不同的情況
如果班級數(shù)相同，那么只需要考慮在班級里面統(tǒng)計班級數(shù)量即可瘟则，
然后在班級年級表中取出對應(yīng)的年級數(shù)目
如果班級數(shù)不相同，那么首先班級里面統(tǒng)計班級數(shù)量枝秤，
然后在按照降序排列醋拧，取前三即可
#包含班級數(shù)不相同的排名前三年級
select class_grade.gname from class_grade inner join(
select grade_id,count(id) as count_cid from class group by grade_id order by count_cid desc limit 3)
as t1 on class_grade.gid = t1.grade_id;
#包含了班級數(shù)相同的排名前三年級
select gname from class_grade where gid in (
select grade_id from class group by grade_id having count(cid) in (
*****5、查詢平均成績最高和最低的學(xué)生的id和姓名以及平均成績淀弹；
create view t1 as
select student_id avg(score) as avg_score from score group by student_id;
select sname,avg_score from t1 left join student on t1.student_id =student.sid
where t1.avg_score = ( select max(t1.avg_score) from t1) or t1.avg_score =
(select min(t1.avg_score) from t1);
6丹壕、查詢每個年級的學(xué)生人數(shù)；
思路：先在學(xué)生表和班級表對應(yīng)一下
然后在對應(yīng)班級表中查找學(xué)生人數(shù)
select t1.grade_id,count(t1.sid) as count_student from (
select student.sid ,class.grade_id from student,class
where student.class_id =class.cid) as t1 group by t1.grade_id;
7薇溃、查詢每位學(xué)生的學(xué)號菌赖，姓名，選課數(shù)沐序，平均成績琉用；
思路：學(xué)生表中有學(xué)生學(xué)號，姓名策幼，性別邑时，班級 成績表中對應(yīng)成績，所以
我們可以聯(lián)立成績表和學(xué)生表特姐，并按學(xué)生id分類晶丘，直接查找即可。
select score.student_id,student.sname,sum(score.course_id),avg(score.score)
from score left join student on score.student_id = student.sid
group by score.student_id;
***8、查詢學(xué)生編號為“2”的學(xué)生的姓名浅浮、該學(xué)生成績最高的課程名沫浆、成績最低的課程名及分?jǐn)?shù)；
思路：首先在成績表中查找學(xué)生編號為2 的學(xué)生的最大最小成績滚秩，學(xué)生id专执，課程id，
然后在課程表和學(xué)生表中找到對應(yīng)的學(xué)生姓名和課程名稱叔遂，
最后聯(lián)立表格得出學(xué)生姓名他炊，課程名稱，分?jǐn)?shù)
select student.sname,course.cname,t1.score from (
select student_id,course_id,score from score where student_id = 2 and score in((
select max(score) from score where student_id = 2),
(select min(score) from score where student_id = 2))) as t1
inner join student on t1.student_id = student.sid
inner join course on t1.course_id = course.cid;
9已艰、查詢姓“李”的老師的個數(shù)和所帶班級數(shù)痊末；
思路：首先在老師表中尋找姓李老師的id
然后在teach2cls中找到老師和班級的聯(lián)系，并統(tǒng)計姓李老師所帶的班級數(shù)
最后在老師表中查詢老師id和姓名哩掺。
select teacher.tid as '姓李id', teacher.tname as '老師姓名' ,GROUP_CONCAT(teach2cls.cid) as '班級數(shù)'
from teacher left join teach2cls on teacher.tid = teach2cls.tid
where teacher.tname like '李%' group by teacher.tid;
10凿叠、查詢班級數(shù)小于5的年級id和年級名；
思路：首先查詢班級表中班級小于5的年級id號碼
然后在年級表中查找對應(yīng)班級表中的年級id即可
select gid,gname from class_grade where gid in (
select grade_id from class group by grade_id having count(caption)<5
);
*****11嚼吞、查詢班級信息盒件，包括班級id、班級名稱舱禽、年級炒刁、年級級別(12為低年級，34為中年級誊稚，56為高年級)翔始，
示例結(jié)果如下；
班級id 班級名稱 年級 年級級別
一年一班 一年級 低
select
class.cid as '班級id',
class.caption as '班級名稱',
class_grade.gname as '年級',
case
when class_grade.gid between 1 and 2 then '低'
when class_grade.gid between 3 and 4 then '中'
when class_grade.gid between 5 and 6 then '高' else 0
end as '年級級別'
from class
left join class_grade on class.grade_id = class_grade.gid;
12里伯、查詢學(xué)過“張三”老師2門課以上的同學(xué)的學(xué)號城瞎、姓名；
首先找到張三老師的id疾瓮，
然后聯(lián)立成績表和課程表脖镀，并在成績表中查看選修張三老師課程數(shù)量大于2的學(xué)生id
最后在學(xué)生表中查找學(xué)生的學(xué)號，姓名狼电。
select sid,sname from student
where sid in
(
select score.student_id from score
left join course
on score.course_id = course.cid
where course.teacher_id in
(
select tid from teacher
where tname = '張三'
)
group by student_id
having count(course.cid) >2
);
13蜒灰、查詢教授課程超過2門的老師的id和姓名；
思路：先在course中按照老師的id進(jìn)行分組肩碟，并統(tǒng)計代課大于2門的老師id的總數(shù)---臨時表
然后在teacher表中查找老師的id和姓名
select tid,tname from teacher where tid in (
select teacher_id from course group by teacher_id having count(cid)>2);
14卷员、查詢學(xué)過編號“1”課程和編號“2”課程的同學(xué)的學(xué)號、姓名腾务；
思路：創(chuàng)建一個虛擬表毕骡，用于查找課程中的編號1和編號2課程
然后在學(xué)生表中查找學(xué)生的學(xué)號和姓名
select sid,sname from student where sid in (
select distinct student_id from score where course_id in (1,2));
15、查詢沒有帶過高年級的老師id和姓名；
思路：在班級表中設(shè)定高年級為五六年級未巫，---虛擬表
然后在teach2cls中找到老師和班級的聯(lián)系 ---虛擬表
最后在老師表中查詢老師id和姓名
select tid,tname from teacher where tid not in (select tid from teach2cls
where cid in (select cid from class where grade_id in (5,6)));
16窿撬、查詢學(xué)過“張三”老師所教的所有課的同學(xué)的學(xué)號、姓名叙凡；
思路：首先將張三老師的id從課程表中和老師表中對應(yīng)起來劈伴，并找出他教的課程id ————虛擬表
然后在成績表中查找與上面表對應(yīng)的課程id所對應(yīng)的學(xué)生的id
最后在學(xué)生表中查找學(xué)生的學(xué)號，姓名握爷。
select sid,sname from student where sid in (
select student_id from score where course_id in (
select cid from course inner join teacher on teacher.tid = course.teacher_id
where teacher.tname = '張三'));
17跛璧、查詢帶過超過2個班級的老師的id和姓名；
思路： 先在teac2cls中找到班級cid大于2的老師id（ｔｉｄ）－－－－虛擬表
然后在老師表中找老師ｉｄ和姓名對應(yīng)的ｉｄ
select tid,tname from teacher where tid in (
select tid from teach2cls group by tid having count(cid)>2);
18新啼、查詢課程編號“2”的成績比課程編號“1”課程低的所有同學(xué)的學(xué)號追城、姓名；
思路：先在成績表中查找課程2 的學(xué)生id和成績燥撞，設(shè)為表1 座柱，
再查找課程編號為1的學(xué)生id和成績，設(shè)為表二
最后在學(xué)生表中查找課程2比課程1的成績低的學(xué)生的學(xué)號和姓名物舒。
select sid,sname from student where sid in (
select t1.student_id from (
select student_id, score from score where course_id = 2 group by student_id) as t1,
select student_id, score from score where course_id = 1 group by student_id) as t2
where t1.student_id = t2.student_id and t1.score < t2.score);
19色洞、查詢所帶班級數(shù)最多的老師id和姓名；
思路：首先在老師-課程表中統(tǒng)計老師所帶的課程數(shù)量冠胯，并按照老師id分類火诸，并取一個
然后在老師表中查找對應(yīng)老師id和姓名
select tid,tname from teacher where tid =(
select tid from teach2cls group by tid order by count(cid) desc limit 1);
20、查詢有課程成績小于60分的同學(xué)的學(xué)號荠察、姓名置蜀；
思路：先在成績表中查找成績小于６０分的學(xué)生ｉｄ
然后學(xué)生表中查找學(xué)生id與成績表中的學(xué)生id對應(yīng)的學(xué)生學(xué)號，姓名
select sid,sname from student where sid in (
select distinct student_id from score where score<60 );
21割粮、查詢沒有學(xué)全所有課的同學(xué)的學(xué)號盾碗、姓名媚污；
思路：首先分析題目意思舀瓢，是沒有學(xué)完所有課的同學(xué)
那么考慮學(xué)生應(yīng)該是選完課程，沒有考試耗美，視為沒有學(xué)完
所以首先查找學(xué)生選擇的課程京髓，在成績表中是否有對應(yīng)的成績，如果有則學(xué)完商架，如果沒有則沒有學(xué)完
select sid ,sname from student where sid not in(
select student_id from score group by student_id having count(course_id)=
(select count(cid) from course)
);
22堰怨、查詢至少有一門課與學(xué)號為“1”的同學(xué)所學(xué)相同的同學(xué)的學(xué)號和姓名；
思路：首先查找學(xué)號為1的學(xué)生的成績id蛇摸，
然后在成績表中按照學(xué)號對應(yīng)上面的成績id
最后在學(xué)生表中查找學(xué)生的學(xué)號备图，姓名。
select sid,sname from student where sid in (
select student_id from score where course_id in (
select course_id from score where student_id =1) group by student_id);
23、查詢至少學(xué)過學(xué)號為“1”同學(xué)所選課程中任意一門課的其他同學(xué)學(xué)號和姓名揽涮；
思路：首先查找學(xué)號為1的學(xué)生所選的課程id抠藕，
然后再對應(yīng)其他學(xué)生所選的課程id，
最后在學(xué)生表中查找學(xué)生的學(xué)號蒋困，姓名盾似。
select sid,sname from student where sid in (
select student_id from score where course_id in (
select course_id from score where student_id = 1)
group by student_id) and sid !=1;
***24、查詢和“2”號同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的學(xué)號和姓名雪标；
思路：首先在成績表中查詢學(xué)生2的課程id零院，
然后進(jìn)行篩選其他人的課程id和姓名id，不包含2號學(xué)生村刨，
最后在學(xué)生表中查找學(xué)生的學(xué)號告抄，姓名。
select sid,sname from student where sid in (
select score.student_id from score ,(
select course_id from score where student_id = 2) as t1
where score.course_id = t1.course_id and score.student_id !=2
group by score.student_id
having count(score.course_id) =(
select count(course_id) from score where student_id = 2));
25烹困、刪除學(xué)習(xí)“張三”老師課的score表記錄玄妈；
思路：首先在score表中找到對應(yīng)張三老師課程，
然后刪除即可
delete from score where course_id in (
select course.cid from course,teacher where
course.teacher_id =teacher.tid and teacher.tname = '張三');
26髓梅、向score表中插入一些記錄拟蜻，這些記錄要求符合以下條件：
①沒有上過編號“2”課程的同學(xué)學(xué)號；②插入“2”號課程的平均成績枯饿；
思路：首先在score找出沒有上過編號2課程的同學(xué)id酝锅，
然后在成績表中找到編號2的學(xué)生的所有成績，取平均值
最后插入數(shù)據(jù)即可奢方。
insert into score(student_id,course_id,score)
select t1.sid,2,t2.avg from (
select sid from student where sid not in (
select student_id from score where course_id = 2)) as t1,
(select avg(score) as avg from score group by course_id having course_id =2) as t2;
27搔扁、按平均成績從低到高顯示所有學(xué)生的“語文”、“數(shù)學(xué)”蟋字、“英語”三門的課程成績稿蹲，
按如下形式顯示： 學(xué)生ID,語文,數(shù)學(xué),英語,有效課程數(shù),有效平均分；
思路：注意平均成績是由低到高desc
最重要的是查詢各科成績鹊奖，
在課程表中找到成績表中對應(yīng)的課程id苛聘，然后在成績表中查找對應(yīng)的成績
select sc.student_id,
(select score.score from score left join course on score.course_id = course.cid
where course.cname = '語文' and score.student_id = sc.student_id) as Chinese,
(select score.score from score left join course on score.course_id = course.cid
where course.cname = '數(shù)學(xué)' and score.student_id = sc.student_id) as Math,
(select score.score from score left join course on score.course_id = course.cid
where course.cname = '外語' and score.student_id = sc.student_id) as English,
count(sc.course_id),avg(sc.score)
from score as sc group by sc.student_id order by avg(sc.score) asc;
28、查詢各科成績最高和最低的分：以如下形式顯示：課程ID忠聚，最高分设哗，最低分；
思路：直接在score中查找課程id两蟀，最高分?jǐn)?shù)网梢，最低分?jǐn)?shù)
select course_id ,max(score),min(score) from score
group by course_id;
29、按各科平均成績從低到高和及格率的百分?jǐn)?shù)從高到低順序赂毯；
思路：平均成績asc 及格率desc
在score中找到學(xué)生的平均成績战虏，并求出及格率拣宰。
select course_id,avg(score) as avg_score,
sum(case when score.score > 60 then 1 else 0 end) / count(1) * 100 as percent
from score group by course_id order by avg(score) asc,percent desc;
30、課程平均分從高到低顯示（顯示任課老師）烦感；
思路：查找成績表中的各科平均分?jǐn)?shù)徐裸，并讓分?jǐn)?shù)對應(yīng)課程表中的課程id
然后把課程表中的課程id對應(yīng)的老師課程表的老師id
最后在老師表中查詢老師id和姓名
select t1.course_id,t1.avg_score,teacher.tname from course,teacher,
(select course_id,avg(score) as avg_score from score group by course_id ) as t1
where course.cid = t1.course_id and course.teacher_id = teacher.tid
order by avg_score desc;
*****31、查詢各科成績前三名的記錄(不考慮成績并列情況）
select score.sid,score.student_id, score.course_id,score.score,
t1.first_score,t1.second_score,t1.third_score
from score inner join (
select s1.sid,(select score from score as s2 where s1.course_id = s2.course_id
order by score desc limit 0,1) as first_score,
(select score from score as s3 where s1.course_id = s3.course_id
order by score desc limit 1,1) as second_score,
(select score from score as s4 where s1.course_id = s4.course_id
order by score desc limit 2,1) as third_score
from score as s1) as t1 on score.sid = t1.sid
where score.score in (t1.first_score,t1.second_score,t1.third_score);
32啸盏、查詢每門課程被選修的學(xué)生數(shù)重贺；
思路：在成績表中查找課程id隙疚，每門課的學(xué)生總數(shù)剂买，
最后在課程表中找到對應(yīng)的課程名稱
select course.cname as '課程名稱',t1.student_num as '學(xué)生數(shù)量' from course,
(select course_id,count(student_id) as student_num from score
group by course_id) as t1 where course.cid = t1.course_id;
33、查詢選修了2門以上課程的全部學(xué)生的學(xué)號和姓名醋火；
思路：在成績表中查找課程id大于2們的學(xué)生id
然后在學(xué)生表中查找對應(yīng)的學(xué)生的學(xué)號和姓名
select sid,sname from student where sid in (
select student_id from score group by student_id having count(course_id)>2);
34怯晕、查詢男生潜圃、女生的人數(shù)，按倒序排列舟茶；
思路： 在學(xué)生表中按照性別分類 按照數(shù)量排序desc
select gender,count(sid) as num from student
group by gender order by num desc;
35谭期、查詢姓“張”的學(xué)生名單；
思路：在學(xué)生表中查找姓張的學(xué)生名單
select sid,sname,gender from student where sname like '張%';
36吧凉、查詢同名同姓學(xué)生名單隧出，并統(tǒng)計同名人數(shù)；
思路：直接在學(xué)生表中查看學(xué)生姓名相同的學(xué)生阀捅，并統(tǒng)計人數(shù)
select sname,count(sname) from student group by sname having count(sname)>1;
37胀瞪、查詢每門課程的平均成績，結(jié)果按平均成績升序排列饲鄙，平均成績相同時凄诞，按課程號降序排列；
思路：在成績表中按照課程id 查找學(xué)生的平均成績
select course_id,avg(score) as avg_score from score
group by course_id order by avg_score,course_id desc;
38忍级、查詢課程名稱為“數(shù)學(xué)”帆谍，且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù)；
思路：先在course中查找課程為數(shù)學(xué)的課程id號碼轴咱，
然后在score中查找數(shù)學(xué)分?jǐn)?shù)低于60的學(xué)生id,分?jǐn)?shù)
最后在學(xué)生表中查找對于id 的學(xué)生姓名
select student.sname,score.score from score left join student
on score.student_id = student.sid where score.course_id = (
select cid from course where cname ='數(shù)學(xué)') and score.score <+60;
39汛蝙、查詢課程編號為“3”且課程成績在80分以上的學(xué)生的學(xué)號和姓名；
思路：先創(chuàng)建一個課程編號為3 且成績在80分以上的學(xué)生id表嗦玖，在score中 --虛擬表
然后在student中查找對應(yīng)id的學(xué)生姓名
select sid,sname from student where sid in (
select student_id from score where score> 80 and course_id = 3
);
40患雇、求選修了課程的學(xué)生人數(shù)
思路：直接在成績表中按照課程id排序跃脊，并統(tǒng)計學(xué)生id即可
select course_id,count(student_id) from score group by course_id;
41宇挫、查詢選修“王五”老師所授課程的學(xué)生中，成績最高和最低的學(xué)生姓名及其成績酪术；
思路：首先在老師表中尋找姓王老師的id器瘪，
然后對應(yīng)課程表中對應(yīng)的所教課程id翠储，
然后在score中查找課程所對應(yīng)的成績和學(xué)生id
最后在學(xué)生表中查找學(xué)生的學(xué)號，姓名橡疼。
select student.sname,score,score from score
left join student on score.student_id = student.sid where course_id in (
select cid from course where teacher_id in (
select tid from teacher where tname = '王五'))
order by score.score desc limit 1;
42援所、查詢各個課程及相應(yīng)的選修人數(shù)；
思路：聯(lián)立課程表中的課程id和成績表中的課程id欣除，
然后查找各個課程對應(yīng)的選修人數(shù)
select course.cname,count(student_id) from score
left join course on score.course_id = course.cid group by course_id;
43住拭、查詢不同課程但成績相同的學(xué)生的學(xué)號、課程號历帚、學(xué)生成績滔岳；
思路：查找不同學(xué)生之間，課程不同成績相同
查找同一個學(xué)生挽牢，課程不同成績相同
的學(xué)生谱煤，課程號，學(xué)生成績
#1禽拔，不同學(xué)生之間
select distinct s1.student_id,s2.student_id,s1.course_id,s2.course_id,s1.score,s2.score
from score as s1,score as s2
where s1.course_id != s2.course_id and s1.score = s2.score;
#2刘离，同一個學(xué)生
select distinct s1.student_id,s2.student_id,s1.course_id,s2.course_id,s1.score,s2.score
from score as s1,score as s2
where s1.student_id = s2.student_id and
s1.course_id != s2.course_id and s1.score = s2.score;
*****44、查詢每門課程成績最好的前兩名學(xué)生id和姓名睹栖；
select
student.sid,
student.sname,
t2.course_id,
t2.score,
t2.first_score,
t2.second_score
from
student
inner join (
select
score.student_id,
score.course_id,
score.score,
t1.first_score,
t1.second_score
from
score
inner join (
select
s1.sid,
(select s2.score from score as s2 where s1.course_id = s2.course_id order by s2.score desc limit 0,1) as first_score,
(select s3.score from score as s3 where s1.course_id = s3.course_id order by s3.score desc limit 1,1) as second_score
from
score as s1
) as t1 on score.sid = t1.sid
where
score.score in (
t1.first_score,
t1.second_score
)
) as t2 on student.sid = t2.student_id;
45硫惕、檢索至少選修兩門課程的學(xué)生學(xué)號；
思路：在score表中直接查找大于2門課程的學(xué)生id
select student_id from score group by student_id having
count(course_id)>=2;
46野来、查詢沒有學(xué)生選修的課程的課程號和課程名疲憋；
思路：在成績表中按照課程id分組作為一個臨時表
如果在課程表中，id沒有在上面的臨時表中梁只，則就是沒有學(xué)生選修
select cid,cname from course where cid not in
(select course_id from score group by course_id);
47缚柳、查詢沒帶過任何班級的老師id和姓名；
思路：在老師-課程表中按照老師分組作為一個臨時表
如果在老師表中搪锣，id沒有在這個臨時表秋忙，則就是沒有帶過任何班級
select tid tname from teacher where tid not in (
select tid from teach2cls group by tid);
48、查詢有兩門以上課程超過80分的學(xué)生id及其平均成績构舟；
思路：首先灰追，在成績表中獲取有兩門課程成績大于80分的學(xué)生id，---臨時表
然后在成績表中查找其id和平均成績
或者在score表中直接查找大于2門課程的學(xué)生id和平均成績
select student_id,avg(score) from score
where student_id in (
select student_id from score where score > 80 group by student_id
having count(course_id) > 2);
select student_id,avg(score) from score
where score >80 group by student_id having count(course_id) >2;
49狗超、檢索“3”課程分?jǐn)?shù)小于60弹澎，按分?jǐn)?shù)降序排列的同學(xué)學(xué)號；
思路：查找成績表中課程三而且分?jǐn)?shù)小于60的學(xué)生學(xué)號努咐，并按照分?jǐn)?shù)降序排列desc
select student_id,score from score where course_id = 3 and score<60
order by score desc;
50苦蒿、刪除編號為“2”的同學(xué)的“1”課程的成績；
思路：首先在成績表中渗稍，先把編號為2和課程為1的找到佩迟，
然后刪除在成績表中對應(yīng)學(xué)生的成績
delete from score where sid =
(select sid from score where student_id = 2 and course_id=1
);
51团滥、查詢同時選修了物理課和生物課的學(xué)生id和姓名；
思路：在課程中首先找到物理报强，生物的id灸姊，
然后在成績表中對應(yīng)課程的id，此時找到了生物課和物理課的id
最后在學(xué)生表中秉溉，找到學(xué)生id和姓名
select sid,sname from student where sid in(
select student_id from score where course_id in (
select cid from course where course.cname in('物理','生物'))
group by student_id having count(course_id) = 2
);
