请根据给出的数据库表的结构和要求，写出相应的Sql语句。

我分析了下,必须要有的表:固定课程表,学生信息表,学习地点表,讲师表,课程表,

1SELECT a课程编号,a课程名 FROM 课程表 a left join 讲师表 b on a讲师编号=b讲师编号

where b讲师名称 like '刘%'

2select from 学生信息表 where 生日>='1990-1-1' --注意,生日一定要日期格式,否则这种写法错误

3有点不明白,课程表中为什么会有学分,难道要找这门课所有学员的学分还是找固定课程中的标准学分

4select a上课时间,b上课地点 from 课程表 a left join 上课地点表 b on a地点编号=b地点编号 where a固定课程编号='XXXX'

5SELECT b教师姓名,b性别,b联系电话 from 课程表 a left join 讲师表 on a讲师编号=b讲师编号 where a固定课程编号='XXXX'

1) select sName from Student s join StuCur c on sid=csid

join Course cu on cuid=ccid where cuName='自然'

2) select cName from Course c join StuCur sc on cid=scCID

join Student s on scsid=sid where sEntranceTime between '1999-01-01' and '2012-12-31'

建表语句

CREATE TABLE student

(

s# INT,

sname nvarchar(32),

sage INT,

ssex nvarchar(8)

)

CREATE TABLE course

(

c# INT,

cname nvarchar(32),

t# INT

)

CREATE TABLE sc

(

s# INT,

c# INT,

score INT

)

CREATE TABLE teacher

(

t# INT,

tname nvarchar(16)

)

插入测试数据语句

insert into Student select 1,N'刘一',18,N'男' union all

select 2,N'钱二',19,N'女' union all

select 3,N'张三',17,N'男' union all

select 4,N'李四',18,N'女' union all

select 5,N'王五',17,N'男' union all

select 6,N'赵六',19,N'女'

insert into Teacher select 1,N'叶平' union all

select 2,N'贺高' union all

select 3,N'杨艳' union all

select 4,N'周磊'

insert into Course select 1,N'语文',1 union all

select 2,N'数学',2 union all

select 3,N'英语',3 union all

select 4,N'物理',4

insert into SC

select 1,1,56 union all

select 1,2,78 union all

select 1,3,67 union all

select 1,4,58 union all

select 2,1,79 union all

select 2,2,81 union all

select 2,3,92 union all

select 2,4,68 union all

select 3,1,91 union all

select 3,2,47 union all

select 3,3,88 union all

select 3,4,56 union all

select 4,2,88 union all

select 4,3,90 union all

select 4,4,93 union all

select 5,1,46 union all

select 5,3,78 union all

select 5,4,53 union all

select 6,1,35 union all

select 6,2,68 union all

select 6,4,71

问题

问题： 1、查询“001”课程比“002”课程成绩高的所有学生的学号； select aS# from (select s#,score from SC where C#='001') a,(select s#,score from SC where C#='002') b where ascore>bscore and as#=bs#; 2、查询平均成绩大于60分的同学的学号和平均成绩； select S#,avg(score) from sc group by S# having avg(score) >60; 3、查询所有同学的学号、姓名、选课数、总成绩； select StudentS#,StudentSname,count(SCC#),sum(score) from Student left Outer join SC on StudentS#=SCS# group by StudentS#,Sname 4、查询姓“李”的老师的个数； select count(distinct(Tname)) from Teacher where Tname like '李%'; 5、查询没学过“叶平”老师课的同学的学号、姓名； select StudentS#,StudentSname from Student where S# not in (select distinct( SCS#) from SC,Course,Teacher where SCC#=CourseC# and TeacherT#=CourseT# and TeacherTname='叶平'); 6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名； select StudentS#,StudentSname from Student,SC where StudentS#=SCS# and SCC#='001'and exists( Select from SC as SC_2 where SC_2S#=SCS# and SC_2C#='002'); 7、查询学过“叶平”老师所教的所有课的同学的学号、姓名； select S#,Sname from Student where S# in (select S# from SC ,Course ,Teacher where SCC#=CourseC# and TeacherT#=CourseT# and TeacherTname='叶平' group by S# having count(SCC#)=(select count(C#) from Course,Teacher where TeacherT#=CourseT# and Tname='叶平')); 8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名； Select S#,Sname from (select StudentS#,StudentSname,score ,(select score from SC SC_2 where SC_2S#=StudentS# and SC_2C#='002') score2 from Student,SC where StudentS#=SCS# and C#='001') S_2 where score2 <score; 9、查询所有课程成绩小于60分的同学的学号、姓名； select S#,Sname from Student where S# not in (select SS# from Student AS S,SC where SS#=SCS# and score>60); 10、查询没有学全所有课的同学的学号、姓名； select StudentS#,StudentSname from Student,SC where StudentS#=SCS# group by StudentS#,StudentSname having count(C#) <(select count(C#) from Course); 11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名； select distinct S#,Sname from Student,SC where StudentS#=SCS# and SCC# in (select C# from SC where S#='1001'); 12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名； select distinct SCS#,Sname from Student,SC where StudentS#=SCS# and C# in (select C# from SC where S#='001'); 13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩； update SC set score=(select avg(SC_2score) from SC SC_2 where SC_2C#=SCC# ) from Course,Teacher where CourseC#=SCC# and CourseT#=TeacherT# and TeacherTname='叶平'); 14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名； select S# from SC where C# in (select C# from SC where S#='1002') group by S# having count()=(select count() from SC where S#='1002'); 15、删除学习“叶平”老师课的SC表记录； Delect SC from course ,Teacher where CourseC#=SCC# and CourseT#= TeacherT# and Tname='叶平'; 16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、2、 号课的平均成绩； Insert SC select S#,'002',(Select avg(score) from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002'); 17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示： 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分 SELECT S# as 学生ID ,(SELECT score FROM SC WHERE SCS#=tS# AND C#='004') AS 数据库 ,(SELECT score FROM SC WHERE SCS#=tS# AND C#='001') AS 企业管理 ,(SELECT score FROM SC WHERE SCS#=tS# AND C#='006') AS 英语 ,COUNT() AS 有效课程数, AVG(tscore) AS 平均成绩 FROM SC AS t GROUP BY S# ORDER BY avg(tscore) 18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 SELECT LC# As 课程ID,Lscore AS 最高分,Rscore AS 最低分 FROM SC L ,SC AS R WHERE LC# = RC# and Lscore = (SELECT MAX(ILscore) FROM SC AS IL,Student AS IM WHERE LC# = ILC# and IMS#=ILS# GROUP BY ILC#) AND RScore = (SELECT MIN(IRscore) FROM SC AS IR WHERE RC# = IRC# GROUP BY IRC# );

自己写的:select c# ,max(score)as 最高分 ,min(score) as 最低分 from dbosc group by c# 19、按各科平均成绩从低到高和及格率的百分数从高到低顺序 SELECT tC# AS 课程号,max(courseCname)AS 课程名,isnull(AVG(score),0) AS 平均成绩 ,100 SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT() AS 及格百分数 FROM SC T,Course where tC#=courseC# GROUP BY tC# ORDER BY 100 SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT() DESC 20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004） SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分 ,100 SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数 ,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分 ,100 SUM(CASE WHEN C# = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END) AS 马克思及格百分数 ,SUM(CASE WHEN C# = '003' THEN score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分 ,100 SUM(CASE WHEN C# = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END) AS UML及格百分数 ,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分 ,100 SUM(CASE WHEN C# = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END) AS 数据库及格百分数 FROM SC

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

SELECT max(ZT#) AS 教师ID,MAX(ZTname) AS 教师姓名,CC# AS 课程ID,MAX(CCname) AS 课程名称,AVG(Score) AS 平均成绩

FROM SC AS T,Course AS C ,Teacher AS Z

where TC#=CC# and CT#=ZT#

GROUP BY CC#

ORDER BY AVG(Score) DESC

22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理（001），马克思（002），UML （003），数据库（004）

[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩

SELECT DISTINCT top 3

SCS# As 学生学号,

StudentSname AS 学生姓名 ,

T1score AS 企业管理,

T2score AS 马克思,

T3score AS UML,

T4score AS 数据库,

ISNULL(T1score,0) + ISNULL(T2score,0) + ISNULL(T3score,0) + ISNULL(T4score,0) as 总分

FROM Student,SC LEFT JOIN SC AS T1

ON SCS# = T1S# AND T1C# = '001'

LEFT JOIN SC AS T2

ON SCS# = T2S# AND T2C# = '002'

LEFT JOIN SC AS T3

ON SCS# = T3S# AND T3C# = '003'

LEFT JOIN SC AS T4

ON SCS# = T4S# AND T4C# = '004'

WHERE studentS#=SCS# and

ISNULL(T1score,0) + ISNULL(T2score,0) + ISNULL(T3score,0) + ISNULL(T4score,0)

NOT IN

(SELECT

DISTINCT

TOP 15 WITH TIES

ISNULL(T1score,0) + ISNULL(T2score,0) + ISNULL(T3score,0) + ISNULL(T4score,0)

FROM sc

LEFT JOIN sc AS T1

ON scS# = T1S# AND T1C# = 'k1'

LEFT JOIN sc AS T2

ON scS# = T2S# AND T2C# = 'k2'

LEFT JOIN sc AS T3

ON scS# = T3S# AND T3C# = 'k3'

LEFT JOIN sc AS T4

ON scS# = T4S# AND T4C# = 'k4'

ORDER BY ISNULL(T1score,0) + ISNULL(T2score,0) + ISNULL(T3score,0) + ISNULL(T4score,0) DESC);

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

SELECT SCC# as 课程ID, Cname as 课程名称

,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85]

,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70]

,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60]

,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -]

FROM SC,Course

where SCC#=CourseC#

GROUP BY SCC#,Cname;

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

SELECT 1+(SELECT COUNT( distinct 平均成绩)

FROM (SELECT S#,AVG(score) AS 平均成绩

FROM SC

GROUP BY S#

) AS T1

WHERE 平均成绩 > T2平均成绩) as 名次,

S# as 学生学号,平均成绩

FROM (SELECT S#,AVG(score) 平均成绩

FROM SC

GROUP BY S#

) AS T2

ORDER BY 平均成绩 desc;

原文地址：>

答案如下：

1）创建教师表、课程表和教师授课表

create table Teacher

( Tno char(5) primary key,

Tname char(8),

Tsex char(2),

Tbirth int,

Tdept varchar(30)

);

create table Course

( Cno char(3) primary key,

Cname varchar(30),

Cpno char(3),

Ccredit int

);

create table 教师授课表

( Tno char(5),

Cno char(3),

time datetime

) ;

2）在教师表、课程表和教师授课表上建立主外键约束

alter table 教师授课表

add (foreign key(Tno) references Teacher(Tno),

foreign key(Cno) references Course(Cno));

3）建立约束：教师表中教师年龄介于25至60之间

alter table Teacher

add check(Tbirth>=25 and Tbirth<=60);

4）向学生表插入一条记录20050204,'赵勤','女',20，'国贸系'。

insert into Student

values('20050204','赵勤','女',20，'国贸系');

5）在“课程表”的“课程号”列上创建唯一聚集索引。

create unique clustered index idx_cno on Course(Cno);

6）以“课程号”分组，统计各门课程的平均分数大于80分的行。

select Cno,avg(Grade) from SC

group by Cno

having avg(Grade)>80;

7）修改学号为“20050306”的学生信息为：20050206，魏海平，男，企管系。

update Student

set Sno='20050206',Sname='魏海平',Ssex='男',Sdept='企管系'

where Sno='20050306';

8）将学号为“20050304”的学生信息从表中删除。

delete from Student

where Sno='20050304';

9）查询所有任课教师的课程，没有课程的教师对应的课程为空。

select Tname,Cname from Teacher

left outer join 教师授课表 on TeacherTno=教师授课表Tno

left outer join Course on 教师授课表Cno=CourseCno;

10）按课程分组查询计算机学院课程的最低分、最高分和平均分。

select Cno,min(Grade),max(Grade),avg(Grade)

from SC,Student

where SCSno=StudentSno

group by Cno

having Sdept='计算机学院';

以上就是关于请根据给出的数据库表的结构和要求，写出相应的Sql语句。全部的内容，包括:请根据给出的数据库表的结构和要求，写出相应的Sql语句。、数据库中有四张表：Teacher(教师)表、Student(学生)表、Course(课 程)表和StuCur(选课)表、Student 学生表 ，Course 课程表 ，SC成绩表 ，Teacher 教师表，sql *** 作运用等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！