mysql测试表格的年龄的语句是_mysql练习题

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='张三'