DataBase实验三代码 SQL

1.简单查询:

（1）查询班号为“g9940206”班的学生的学号和姓名；

select 学号,学生姓名

from [学生表] WHERE 班级号='g9940206'

没有查询到符合条件的数据。

 

（2）查询“网络技术与实践”课程的周学时和学分；

select 周学时,课程学分

from [课程表] WHERE 课程名称='网络技术与实践';

 

（3）查询选修了课程编号为“dep04_s001”的学生的学号和成绩，并将成绩按降序输出；

select 学号,成绩

from [学生选课表] WHERE 课程号='dep04_s001'

ORDER BY 成绩 DESC

 

（4）查询所有姓张的学生的信息；

select *

from [学生表] WHERE 学生姓名 LIKE '张%'

 

（5）查询出生在1984年和1985年间的学生信息；

select *

from [学生表]

WHERE  substring(出生时间,1,4)='1984' or substring(出生时间,1,4)='1985'

没有查询到符合条件的数据。

（6）查询1982年以后出生的女学生的学号、姓名、性别和出生日期，结果按年龄从小到大排序；

select 学号,学生姓名,性别,出生时间

from [学生表]

WHERE  cast(substring(出生时间,1,4) as int)>1984 and 性别='女'

ORDER By cast(出生时间 as date) DESC

（7）显示所有学生的相关信息，并汇总学生总人数。

select count(*) from [学生表]

select * FROM [学生表]

 

（8）查询选修了课程编号为“dep04_s002”且成绩高于85分的学生的学号、姓名和成绩；

select 学生表.学号,学生姓名,成绩

From [学生表],[学生选课表]

WHERE 课程号='dep04_s002' and 成绩>85 and 学生表.学号=学生选课表.学号

 

（9）查询所有学生的学号、姓名、选修的课程名称和成绩；

select 学生表.学号,学生姓名,课程名称,成绩

From [学生表],学生选课表,课程表

WHERE 学生选课表.课程号=课程表.课程号 and 学生表.学号=学生选课表.学号

 

（10）查询计算机科学系林红同学选修的课程名称、学分和成绩(考试成绩>＝60有学分，否则无学分)。

select 课程名称,学分,成绩

From [学生表],学生选课表,课程表

WHERE 学生选课表.课程号=课程表.课程号 and 学生表.学号=学生选课表.学号 and 学生姓名='林红' and 成绩>=60

 

（11）查询每个教师的基本信息及教师类别；

select 教师编号,教师姓名,性别,出生时间,所在系编号,职称,联系电话,家庭地址,邮政编码,类别名称

From 教师类别表,教师表

WHERE 教师类别表.类别编号=教师表.类别编号

 

（12）使用完全外联接查看StuSelDB数据库中每位教师的授课情况；

select 教师姓名,课程名称

from

(教师表 full join [teacher_course_class]

on [teacher_course_class].教师编号=教师表.教师编号)

FULL JOIN [课程表] ON [teacher_course_class].课程号=课程表.课程号

 

（13）查询选课程最多的学生由高到低排序；

select 学生姓名,count(课程号)

from [学生表] FULL JOIN 学生选课表

on 学生表.学号=学生选课表.学号

GROUP BY 学生姓名

ORDER BY COUNT(课程号) DESC

 

（14）查询计算机工程系的教师授课课程的选修情况；

select 教师姓名,课程名称

from ([教师表] LEFT JOIN [teacher_course_class] ON [teacher_course_class].教师编号=教师表.教师编号)

LEFT JOIN [系表] ON [系表].系编号=教师表.所在系编号

LEFT JOIN [课程表] ON [teacher_course_class].课程号=课程表.课程号

where 系名称='计算机工程'

 

（15）查询严为老师和李红老师教授的同1门课程并列出课程名称；

select 课程名称

from [课程表]

WHERE 课程号 IN (

(select 课程号 from [teacher_course_class] join [教师表] ON [teacher_course_class].教师编号=教师表.教师编号 where 教师表.教师姓名='严为')

     intersect (select 课程号 from [teacher_course_class] join [教师表] ON [teacher_course_class].教师编号=教师表.教师编号 where 教师表.教师姓名='李红')

)

 

（16）查询由严为老师授课而没有由李红老师授课的课程。

select 课程名称

from [课程表]

WHERE 课程号 IN ((select 课程号 from [teacher_course_class] join [教师表] ON [teacher_course_class].教师编号=教师表.教师编号 where 教师表.教师姓名='严为')

     except (select 课程号 from [teacher_course_class] join [教师表] ON [teacher_course_class].教师编号=教师表.教师编号 where 教师表.教师姓名='李红'))

（17）查询至少选修了三门课程的学生的学号和姓名；

select 学生姓名,学生表.学号

from [学生表] LEFT JOIN 学生选课表 on [学生表].学号=学生选课表.学号

GROUP BY 学生姓名,学生表.学号

having count(课程号)>=3

 

（18）查询选修课程号为“dep04_b001”的学生的平均成绩；

select avg(成绩)

from [学生选课表]

GROUP BY 课程号

having 课程号='dep04_b001'

 

（19）查询所有学生的学号和其选修课程的最高成绩，要求他的选修课程中没有成绩为空的；

select 学生表.学号,max(成绩)

from [学生选课表] JOIN 学生表 on [学生选课表].学号=学生表.学号

GROUP BY 学生表.学号

HAVING 学生表.学号 NOT IN(SELECT 学号 from [学生选课表] where 成绩 IS NULL )

 

（20）查询严为老师2011/2012学年所授“软件开发技术”课程的最高成绩及此学生的学号、姓名、班级；

SELECT 成绩,学生表.学号,学生姓名,学生表.班级号,课程名称

from 教师表 join [teacher_course_class] on [teacher_course_class].教师编号=教师表.教师编号

JOIN [课程表] ON [teacher_course_class].课程号=课程表.课程号

join [学生选课表] ON [teacher_course_class].课程号=学生选课表.课程号

join [学生表] ON [学生选课表].学号=学生表.学号

where 成绩 IN (

    select max(成绩)

    from 学生选课表

    GROUP BY 课程号,学年)

and 教师姓名='严为' and [teacher_course_class].学年='2011/2012' and 课程名称='软件开发技术'

 

（21）查询数据库开发技术课程用过的教材名称，作者和出版社；

SELECT 教材名,作者,出版社

from 教材 JOIN [课程表] ON 书号=教材编号

where 课程名称='数据库开发技术'

 

（22）查询计算机科学系讲授过“JAVA程序设计与开发”的老师姓名和职称。

SELECT 教师姓名,职称

from 教师表 JOIN [teacher_course_class] ON [teacher_course_class].教师编号=教师表.教师编号

JOIN [系表] ON [教师表].所在系编号=系表.系编号

JOIN [课程表] ON [teacher_course_class].课程号=课程表.课程号

WHERE 系名称='计算机科学' and 课程名称='JAVA程序设计与开发'

 

3.嵌套查询：

（23）查询所有已获“副教授”职称的女教师的--姓名、性别、学历和职称；

Select 教师姓名,性别,学历,职称

From 教师表

Where 性别=’女’ and 职称=’副教授’

 

（24）查询开设了课程ID为*的课程的教师的相关信息；

select * FROM [教师表] WHERE 教师编号 IN

( SELECT 教师编号 FROM [teacher_course_class] WHERE 课程号='*')

 

（25）查询选修了*号课程的学生学号、姓名和联系电话；

select 学号,学生姓名,联系电话 FROM [学生表] WHERE 学号 IN

( SELECT 学号 FROM [学生选课表] WHERE 课程编号='*')

//好像没有联系电话？？？

 

（26）查询课程考试不及格的学生的姓名、性别；

select 学生姓名,性别 FROM [学生表] WHERE 学号 IN

( SELECT 学号 FROM [学生选课表] WHERE 成绩<60)

 

（27）查询网页设计课程不及格的学生姓名、性别和联系电话；

select 学生姓名,性别,联系电话 FROM [学生表] WHERE 学号 IN

( SELECT 学号 FROM [学生选课表] WHERE 成绩<60 and 课程号 IN

  ( SELECT  课程号 FROM 课程表 where 课程名称='网页设计'))

 

（28）查询选修了“计算机基础”的学生的学号和姓名；

select 学生姓名,学号 FROM [学生表] WHERE 学号 IN

( SELECT 学号 FROM [学生选课表] WHERE 课程号 IN

  ( SELECT  课程号 FROM 课程表 where 课程名称='计算机基础'))

 

（29）查询没有选修“计算机基础”的学生的学号和姓名；

select 学号,学生姓名

from [学生表]

WHERE

学号 IN (

select 学号from 学生选课表where 课程号 IN (

SELECT 课程号from 课程表where 课程名称='计算机基础'

))

（30）查询至少选修了学号为“g9940201”的学生所选修的所有课程的学生的学号和姓名。

SELECT 学号,学生姓名 FROM [学生表] WHERE 学号 IN

( SELECT 学号 from [学生选课表] WHERE 课程号 IN

 ( SELECT 课程号 from [学生选课表] WHERE 学号='g9940201'))