SQL数据库，SELECT语句的基本语法和查询条件表⽰⽅法
1. 了解SELECT语句的基本语法格式和执⾏⽅法；
2. 了解连接查询的表⽰及使⽤；
3. 掌握嵌套查询的使⽤的⽅法；
请使⽤T-SQL 语句实现进⾏以下操作
注意：以下所有实验基于该表
1. 查询名字中第2个字为‘⼩’的学⽣姓名和学号及选修的课程号、课程名；
select student.sname 姓名,student.s_id 学号
from grade,course,student
where grade.c_id=course.c_id
and student.s_id=grade.c_id
and sname like'_⼩%'
2. 列出选修了‘⾼等数学’或者‘⼤学英语’的学⽣学号、姓名、所在院系、选修课程号及成绩；
select student.s_id 学号,sname 姓名,sdepartment 所在院系,grade.c_id 选修课程号,grade 成绩
from stdent,course,grade
where cname in('⾼等数学'，'⼤学英语')
and grade.c_id=course.c_id
and student.s_id=grade.c_id
3. 查询与‘张三’(假设姓名唯⼀)年龄不同的所有学⽣的信息；
select*
from student,grade,course
where student.s_id=grade.s_id
and grade.c_id=course.c_id and sbirthday not in(
select sbirthday
from student
where sname='张三'
4. 按照“学号，姓名，所在院系，已修学分”的顺序列出学⽣学分的获得情况。其中已修学分为考试已经及格的课程学分之和；
select student.s_id,sname,sdepartment,SUM(credit)
from student,grade ,course
where student .s_id=grade .s_id
and grade.c_id=course.c_id
and grade>=60
group by student.s_id ,sname,sdepartment
5. 查选修了⾄少⼀门和张三选修课程⼀样的学⽣的学号、姓名及课程号；
select student.s_id,sname,c_id
from student,grade
where student .s_id=grade .s_id
and c_id in(
select c_id
from student,grade
where sname='张三'
and student.s_id=grade.s_id
)
6. 查询只被⼀名学⽣选修的课程的课程号、课程名；
select grade.c_id,cname
from course,grade
where course.c_id=grade .c_id
and grade.c_id=any(
select grade.c_id
from grade
group by grade.c_id
having COUNT(grade.c_id)=1
)
7. 查询选修了“数据结构”课程的学⽣学号和姓名；
select student.s_id,sname
from student
where s_id in(select s_id from grade
where c_id in(select c_id from course
where cname='数据结构')
)
8. 查询其它学院中年龄⼩于信息⼯程学院系的某个学⽣的学⽣姓名、年龄和院系；
select sname,sbirthday,sdepartment
from student
where sbirthday<any(select sbirthday  from student
where sdepartment ='信息⼯程学院')
and sdepartment!='信息⼯程学院'
9. 使⽤ANY、ALL 查询，列出其他院系中⽐外语学院所有学⽣年龄⼩的学⽣的姓名；
##any
select sname
from student
where sbirthday<any
(
select sbirthday
from student
where sdepartment ='外语学院'
)
and sdepartment<>'外语学院'
##all
select sname
from student
where sbirthday<all
(
select sbirthday
from student
where sdepartment ='外语学院'
)
and sdepartment<>'外语学院'
10. 分别使⽤连接查询和嵌套查询，列出与‘张三’在⼀个院系的学⽣的信息；
select*
from student
where sdepartment in(
select sdepartment from student
where sname='张三')
11. 使⽤集合查询列出信息⼯程学院的学⽣以及性别为⼥的学⽣学号及姓名；
select s_id,sname
from student
where sdepartment ='信息⼯程学院'
union
select s_id ,sname from student
where ssex='⼥'
12. 使⽤集合查询列出信息⼯程学院的学⽣与年龄不⼤于19岁的学⽣的交集、差集；
select*
from  student
except
select*
from student
where sdeparment='信息⼯程学院'
where sbirthday<=19
## ## ##
select*
from  student
intersect
select*
from student
where sdeparment='信息⼯程学院'
where sbirthday<=19
13. 查询选修课程1的学⽣与选修课程2的学⽣的学号与姓名；
select student.s_id,sname
from student,grade
where grade.c_id in
(
select c_id
from course
where c_id='1'or c_id='2'
)
and grade.s_id=student.s_id
group by student.s_id,snamesql查询语句实例大全
14. 查询既选修了1号课程⼜选修了2号课程的学⽣的学号与姓名。
select s_id
from grade
where c_id='1'
intersect
select s_id
from grade
where c_id='2'
提⽰
命令⽅式：在【SQL Server Management Studio】窗⼝左上⽅选择【新建查询】按钮，启动SQL编辑器窗⼝，在光标处输⼊T-SQL语句，单击【执⾏】按钮。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。