mysql所有选修课程都及格_MySQL 练习题目 二刷 - 2019-11-4 5:55 am

Log：2020-6-24 23:18 三刷，并重新整理了下题目

PS：练习过程中，为了更好地查看查询效果，会对数据做一些修改符合题目，请注意

建表的过程

create tablestudent(

sidint not null primary key,

snamevarchar(20) not null,

sborn date,

ssexvarchar(20) not null);create tablecourse(

cidint not null primary key,

cnamevarchar(20) not null,

tidint not null);create tableteacher(

tidint not null primary key,

tnamevarchar(20));create tablesc(

sidint not null,

cidint not null,

scoreint not null,primary key( sid, cid) );

插入数据到student表

insert into Student values(1 , '赵雷' , '1990-01-01' , '男');insert into Student values(2 , '钱电' , '1990-12-21' , '男');insert into Student values('03' , '孙风' , '1990-12-20' , '男');insert into Student values('04' , '李云' , '1990-12-06' , '男');insert into Student values('05' , '周梅' , '1991-12-01' , '女');insert into Student values('06' , '吴兰' , '1992-01-01' , '女');insert into Student values('07' , '郑竹' , '1989-01-01' , '女');insert into Student values('08' , '张三' , '2017-12-20' , '女');insert into Student values('9' , '李四' , '2017-12-25' , '女');insert into Student values('10' , '李四' , '2012-06-06' , '女');insert into Student values('11' , '赵六' , '2013-06-13' , '女');insert into Student values('12' , '孙七' , '2014-06-01' , '女');

insert into Course values('01' , '语文' , '02'),('02' , '数学' , '01'),('03' , '英语' , '03'),(4,'物理',4);

insert into Teacher values('01' , '张三'),('02' , '李四'),('03' , '王五'),(4,'孙杨');

insert into SC values('01' , '01' , 80),

('01' , '02' , 90),

('01' , '03' , 99),

(1,4,46),

('02' , '01' , 70),

('02' , '02' , 60),

('02' , '03' , 80),

(2,4,76),

('03' , '01' , 80),

('03' , '02' , 80),

('03' , '03' , 80),

('04' , '01' , 50),

('04' , '02' , 30),

('04' , '03' , 20),

(4,4,87),

('05' , '01' , 76),

('05' , '02' , 87),

('06' , '01' , 31),

('06' , '03' , 34),

(6,4,93),

('07' , '02' , 89),

('07' , '03' , 98);

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

select student.*,a.scorefrom (sc a join sc b on a.sid=b.sidand a.cid=1

and b.cid=2

and a.score>b.score)join student on a.sid=student.sid;

或

select student.*, a.scorefrom (sc a left join sc b on a.sid=b.sid) inner join student on a.sid=student.sidwhere a.cid=1 and b.cid=2 and a.score>b.score;

2、查询同时选修" 01 "课程和" 02 "课程的学生情况

select student.*

from sc left join student on sc.sid=student.sidwhere cid=1 or cid=2 #注意这里只能用or，不能用and

group bysc.sidhaving count(cid)=2;

或

select student.*

from (sc a join sc b on a.sid=b.sid and a.cid=1 and b.cid=2) join student on a.sid=student.sid;

<