Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表
问题:
1、查询“001”课程比“002”课程成绩高的所有学生的学号;
select a.S# from (select s#,score from SC where C#=’001’) a,(select s#,score
from SC where C#=’002’) b
where a.score>b.score and a.s#=b.s#;
2、查询平均成绩大于60分的同学的学号和平均成绩;
select S#,avg(score) 重点内容
from sc
group by S# having avg(score) >60;
3、查询所有同学的学号、姓名、选课数、总成绩;
select Student.S#,Student.Sname,count(SC.C#),sum(score)
from Student left Outer join SC on Student.S#=SC.S#
group by Student.S#,Sname
4、查询姓“李”的老师的个数;
select count(distinct(Tname))
from Teacher
where Tname like ‘李%’;
5、查询没学过“叶平”老师课的同学的学号、姓名;
select Student.S#,Student.Sname
from Student
where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=’叶平’);
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#=’001’and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#=’002’);
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
select S#,Sname
from Student
where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=’叶平’ group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname=’叶平’));
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#=’002’) score2
from Student,SC where Student.S#=SC.S# and C#=’001’) S_2 where score2
-------------------------------------------------------------------------------------------------------------------------------------------------------------
--1.学生表
Student(S,Sname,Sage,Ssex) --S 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
--2.课程表
Course(C,Cname,T) --C --课程编号,Cname 课程名称,T 教师编号
--3.教师表
Teacher(T,Tname) --T 教师编号,Tname 教师姓名
--4.成绩表
SC(S,C,score) --S 学生编号,C 课程编号,score 分数
*/
--创建测试数据
create table Student(Svarchar(10),Sname varchar(10),Sage datetime,Ssex nvarchar(10))
insert into Student values('01' , '赵雷' , '1990-01-01' , '男')
insert into Student values('02' , '钱电' , '1990-12-21' , '男')
insert into Student values('03' , '孙风' , '1990-05-20' , '男')
insert into Student values('04' , '李云' , '1990-08-06' , '男')
insert into Student values('05' , '周梅' , '1991-12-01' , '女')
insert into Student values('06' , '吴兰' , '1992-03-01' , '女')
insert into Student values('07' , '郑竹' , '1989-07-01' , '女')
insert into Student values('08' , '王菊' , '1990-01-20' , '女')
create table Course(Cvarchar(10),Cname,varchar(10),T varchar(10))
insert into Course values('01' , '语文' , '02')
insert into Course values('02' , '数学' , '01')
insert into Course values('03' , '英语' , '03')
create table Teacher(T varchar(10),Tname,varchar(10))
insert into Teacher values('01' , '张三')
insert into Teacher values('02' , '李四')
insert into Teacher values('03' , '王五')
create table SC(S varchar(10),Cvarchar(10),score decimal(18,1))
insert into SC values('01' , '01' , 80)
insert into SC values('01' , '02' , 90)
insert into SC values('01' , '03' , 99)
insert into SC values('02' , '01' , 70)
insert into SC values('02' , '02' , 60)
insert into SC values('02' , '03' , 80)
insert into SC values('03' , '01' , 80)
insert into SC values('03' , '02' , 80)
insert into SC values('03' , '03' , 80)
insert into SC values('04' , '01' , 50)
insert into SC values('04' , '02' , 30)
insert into SC values('04' , '03' , 20)
insert into SC values('05' , '01' , 76)
insert into SC values('05' , '02' , 87)
insert into SC values('06' , '01' , 31)
insert into SC values('06' , '03' , 34)
insert into SC values('07' , '02' , 89)
insert into SC values('07' , '03' , 98)
mysql...
--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT a.*,b.score AS'01分数',c.scoreAS '02分数'
FROM student a
INNER JOIN sc b
ON a.s=b.s AND b.c='01'
INNER JOIN sc c
ON a.s=c.s AND c.c='02'
WHERE b.score>c.score
--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
--3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT a.s
,a.sname
,AVG(b.score) AS avgnum
FROM student a
INNER JOIN sc b
ON a.s=b.s
GROUP BY a.s
,a.sname
HAVING AVG(b.score)>60
--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT a.s
,a.sname
,COUNT(b.c)
,SUM(b.score)
FROM student a
LEFT JOIN sc b
ON a.s=b.s
GROUP BY 1,2
--6、查询"李"姓老师的数量
SELECT COUNT(1) FROM teacherWHERE tname LIKE '李%'
--7、查询学过"张三"老师授课的同学的信息
SELECT a.*
FROM student a
INNER JOIN sc b
ON a.s=b.s
INNER JOIN course c
ON b.c=c.c
INNER JOIN teacher d
ON c.t=d.t
WHERE d.tname='张三'
GROUP BY 1,2,3,4
--方法2
SELECT a.*
FROM student a
LEFT JOIN (
SELECT a.*
FROM student a
INNER JOIN sc b
ON a.s=b.s
INNER JOIN course c
ON b.c=c.c
INNER JOIN teacher d
ON c.t=d.t
WHERE d.tname='张三'
GROUP BY 1,2,3,4
)b
ON a.s=b.s
WHERE b.s IS NULL
--8、查询没学过"张三"老师授课的同学的信息
SELECT a.*
FROM student a
LEFT JOIN sc b
ON a.s=b.s
WHERE NOT EXISTS(
SELECT *
FROM course aa
INNER JOIN teacher b
ON aa.t=b.t
INNER JOIN sc c
ON aa.c=c.c
WHERE b.tname='张三'