数据库——SQL-SERVER练习(1)连接与子查询

一.实验准备

1.复制实验要求文件及“CREATE-TABLES.SQL”文件, 粘贴到本地机桌面。

2.启动SQL-SERVER服务。

3. 运行查询分析器, 点击菜单《文件》/《打开》, 打开CREATE-TABLES.SQL并运行, 建立学生数据库STUDB及表STUDENT, COURSE, SC

4. 点击菜单《文件》/《新建》, 建立一个空查询窗口。开始实验。

5.本次实验环境是xp虚拟机,SQL-SERVER迷你版。

 

CREATE-TABLES.SQL”文件 https://www.cnblogs.com/wkfvawl/p/10843788.html

 

二.实验内容

1.查询各个课程号及相应的选课人数(SC表,按课号分组)

 

SELECT CNO,COUNT(SNO)
FROM SC
GROUP BY CNO

 

 

 

2.查询每个学生的学号和选课门数, 最高成绩,最低成绩,平均成绩

SELECT SNO,COUNT(CNO),MAX(GRADE),MIN(GRADE),AVG(GRADE)
FROM SC
GROUP BY SNO

 

3.查询各系的系名, 性别,人数, 平均年龄(按系名, 性别分组)

SELECT SDEPT,SSEX,COUNT(*),AVG(SAGE)
FROM STUDENT
GROUP BY SDEPT,SSEX

 

 

 

4. 查询选修了>=3门课的学生号( HAVING )

SELECT SNO
FROM SC
GROUP BY SNO
HAVING COUNT(*)>=3

 

 

5. 查询选修学生人数>=2的课号

SELECT CNO
FROM SC
GROUP BY CNO
HAVING COUNT(*)>=2

 

 

6 .查询成绩在75以上超过两门课的学生号  ( WHERE+HAVING )

SELECT SNO
FROM SC
WHERE GRADE>75
GROUP BY SNO
HAVING COUNT(*)>=2

 

 

7. 查询每个学生及其选课情况 ( 连接STUDENT和SC )

SELECT *
FROM STUDENT,SC
WHERE STUDENT.SNO=SC.SNO

 

 

8. 查询CS系学生的学生号,学生名,课号,成绩(用连接)

SELECT STUDENT.SNO,SNAME,CNO,GRADE
FROM STUDENT,SC
WHERE STUDENT.SNO=SC.SNO AND SDEPT='CS'

 

 

9.查询选1号课的学生号,学生名,成绩(用连接)

SELECT STUDENT.SNO,SNAME,GRADE
FROM STUDENT,SC
WHERE STUDENT.SNO=SC.SNO AND CNO='1'

 

 

10.查询每个学号的平均成绩,总学分(连接SC和COURSE, 分组)

SELECT SNO,AVG(GRADE),SUM(CCREDIT)
FROM COURSE,SC
WHERE SC.CNO=COURSE.CNO AND GRADE>=60
GROUP BY SNO

 

 

11.查询CS系学生的学生号,选课课号,课名,成绩(三表连接)

SELECT STUDENT.SNO,COURSE.CNO,CNAME,GRADE
FROM COURSE,SC,STUDENT
WHERE STUDENT.SNO=SC.SNO AND SC.CNO=COURSE.CNO AND SDEPT='CS'

 

 

12. 查询与“刘晨”在同一个系学习的学生号,姓名,系名。

SELECT SNO,SNAME,SDEPT
FROM STUDENT
WHERE SDEPT =
(
SELECT SDEPT
FROM STUDENT
WHERE SNAME='刘晨'
)

 

13. 查询选1号课的学生号,学生名(用IN 子查询)

SELECT SNO,SNAME
FROM STUDENT
WHERE SNO IN
(
SELECT SNO
FROM SC
WHERE CNO='1'
)

 

14. 查询未选1号课的学生号,学生名(用NOT IN 子查询)

SELECT SNO,SNAME
FROM STUDENT
WHERE SNO NOT IN
(
SELECT SNO
FROM SC
WHERE CNO='1'
)

 

 

15. 查询没有不及格的学生号,学生名(用NOT IN 子查询)

SELECT SNO,SNAME
FROM STUDENT
WHERE SNO NOT IN
(
SELECT SNO
FROM SC
WHERE GRADE<60
)

 

 

16. 查询学生号,姓名,课号,成绩, 要包括未选修课的学生(用外连接)

SELECT STUDENT.SNO,SNAME,CNO,GRADE
FROM STUDENT,SC
WHERE STUDENT.SNO*=SC.SNO

 

 

17. 查询课号, 课名, 学号, 成绩 , 包括未被选修的课.( 用外连接)

SELECT COURSE.CNO,COURSE.CNAME,SNO,GRADE
FROM COURSE,SC
WHERE COURSE.CNO*=SC.CNO

 

 

18.查询比刘晨年龄大的学生号,姓名。(>子查询)

SELECT SNO,SNAME
FROM STUDENT
WHERE SAGE >
(
 SELECT SAGE
 FROM STUDENT
 WHERE SNAME='刘晨'
)

 

 

19. 查询比信息系任意一个学生年龄小的学生姓名和年龄。(<ANY子查询)

SELECT SNAME,SAGE
FROM STUDENT
WHERE SAGE <ANY
(
 SELECT SAGE
 FROM STUDENT
 WHERE SDEPT='IS'
)

 

 

20.查询选’数据库原理课的学号(用 IN子查询)

SELECT SNO
FROM STUDENT
WHERE SNO IN
( 
  SELECT SNO
  FROM SC
  WHERE CNO IN
  (
     SELECT CNO
     FROM COURSE
     WHERE CNAME='数据库原理'
   )
)

 

 

21. 查询选’数据库原理课的学号,姓名(用 IN子查询)

SELECT SNO,SNAME
FROM STUDENT
WHERE SNO IN
( 
  SELECT SNO
  FROM SC
  WHERE CNO IN
  (
     SELECT CNO
     FROM COURSE
     WHERE CNAME='数据库原理'
   )
)

 

 

posted @ 2019-05-09 09:44 王陸 阅读(...) 评论(...) 编辑 收藏