一、实验目的
1、掌握SQL Server Management Studio的新建查询的用法,加深对SQL的语句的理解;
2、使用SQL Server Management Studio的新建查询用SELECT语句进行数据查询;
3、熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。
二、实验内容
1、完毕简单查询操作。该实验包含投影、选择条件表达,数据排序等。
2、完毕连接查询、嵌套查询、组合查询和统计查询等操作。
三、实验步骤
1、设学生选课库中有3个数据表:
S(sno,sname,birthday,sdeptartment,tel,sex),
SC(sno,cno,grade), C(cno,cname,teacher,pcno)。
2、利用SQL Server Management Studio在数据库S_C的三个表中分别插入下面数据:学生表S:
sno
sname
birthday
sdeptartment
tel
sex
J0401
李军
1981-2-12
计算机系
0576-85123464
男
J0402
刘晨
1980-5-22
数学系
0576-85123466
女
J0403
王敏
1980-4-28
计算机系
0576-85123464
女
J0404
张立
1979-9-8
计算机系
0576-85123464
男
课程表C:
cno
cname
teacher
pcno
C01
数据库
GUO
C03
C02
VB
LIU
C03
C03
计算机基础
LI
C04
数学
WANG
C05
数据结构
ZHANG
C03
C06
C语言
CHEN
C03
成绩表SC:
sno
cno
grade
J0401
C05
92
J0401
C06
85
J0401
C02
88
J0401
C01
90
J0402
C04
80
J0403
C02
76
J0404
C03
88
3、在查询窗体下用SELECT语句完毕下列查询:
1)检索至少选修课程“数据结构”和“C语言”的学生学号;
SELECT SC.sno FROM SC, C
WHERE SC.cno = C.cno AND (C.cname = '数据结构' OR C.cname = 'C语言')
GROUP BY SC.sno
HAVING COUNT(SC.cno) >= 2
查询结果描写叙述:
2)检索学习课程号为C02的学生学号和姓名。
Sql语句:
SELECT S.sno, S.sname FROM S, SC
WHERE S.sno = SC.sno AND SC.cno = 'C02'
查询结果描写叙述:
3)检索选修课程“数据库”的学生学号和姓名;
Sql语句:
SELECT S.sno, S.sname FROM S, SC, C
WHERE S.sno = SC.sno AND SC.cno = C.cno AND C.cname = '数据库'
查询结果描写叙述:
4)检索选修课程号为C02或C04的学生学号;
Sql语句:
SELECT sno FROM SC WHERE cno = 'C02' OR cno = 'C04'
查询结果描写叙述:
5)检索至少选修课程号为C02和C04的学生姓名;
Sql语句:
SELECT S.sname FROM S, SC
WHERE S.sno = SC.sno AND (SC.cno = 'C02' OR SC.cno = 'C04')
GROUP BY S.sname
HAVING COUNT(SC.cno) >= 2
查询结果描写叙述:
6)检索没有选修课程“数据库”的学生姓名和年龄。
Sql语句:
SELECT DISTINCT S.sname, YEAR(GETDATE())-YEAR(S.birthday) FROM S
WHERE NOT EXISTS (SELECT * FROM SC, C WHERE S.sno = SC.sno AND SC.cno = C.cno AND C.cname = '数据库')
查询结果描写叙述:
7)在SC表中检索男生选修的课程名;
Sql语句:
SELECT DISTINCT C.cname FROM S, C, SC
WHERE S.sex = '男' AND S.sno = SC.sno AND SC.cno = C.cno查询结果描写叙述:
8)检索每一个学生的出生年份;
Sql语句:
SELECT S.sname, YEAR(S.birthday) AS 出生年份 FROM S
查询结果描写叙述:
9)在S中检索学生的姓名和出生年份。输出的列名分别为STUDENT_NAME和BIRTH_YEAR;
Sql语句:
SELECT S.sname AS STUDENT_NAME, YEAR(GETDATE())-YEAR(S.birthday) AS BIRTH_YEAR FROM S
查询结果描写叙述:
10)列出选修课程超过3门的学生姓名及选修门数;
Sql语句:
SELECT S.sname, COUNT(SC.cno) FROM S, SC
WHERE S.sno = SC.sno
GROUP BY S.sname
HAVING COUNT(SC.cno) > 3
查询结果描写叙述:
11)求选修了各课程的学生人数。
Sql语句:
SELECT COUNT(SC.cno) FROM SC
GROUP BY SC.sno
HAVING COUNT(SC.cno) = (SELECT COUNT(C.cno) FROM C)
查询结果描写叙述:
12)在SC中,求选修课程C01的学生的学号和得分,并将结果按分数降序排序;
Sql语句:
SELECT SC.sno, SC.grade FROM SC
WHERE SC.cno = 'C01'
ORDER BY SC.grade DESC
查询结果描写叙述:
13)查找每一个同学的学号及选修课程的平均成绩情况。
Sql语句:
SELECT SC.sno, AVG(SC.grade) FROM SC
GROUP BY SC.sno
查询结果描写叙述:
14)列出学生全部可能的选课情况。
Sql语句:
SELECT DISTINCT A.cno, A.pcno FROM C A, C B
WHERE A.pcno = B.cno OR A.pcno = ''
查询结果描写叙述:
15)列出每一个同学的学号及选修课程的平均成绩情况,没有选修的同学也列出;
Sql语句:
SELECT SC.sno, AVG(SC.grade) FROM SC
GROUP BY SC.sno
查询结果描写叙述:
16)列出每一个同学的学号及选修课程号,没有选修的同学也列出。
Sql语句:
SELECT S.sno, SC.cno FROM S
LEFT JOIN SC ON S.sno = SC.sno
查询结果描写叙述:
17)检索至少有两名男生选修的课程名;
Sql语句:
SELECT C.cname FROM S, C, SC
WHERE S.sex = '男' AND S.sno = SC.sno AND SC.cno = C.cno
GROUP BY C.cname
HAVING COUNT(SC.sno) >= 2
查询结果描写叙述:
18)检索S中不姓“王”同学记录;
Sql语句:
SELECT * FROM S
WHERE S.sname NOT LIKE '王%'
查询结果描写叙述:
19)检索和“李军”同性别并同系的同学姓名;
Sql语句:
SELECT A.sname FROM S A, S B
WHERE A.sdeptartment = B.sdeptartment AND A.sex = B.sex AND B.sname = '李军'
查询结果描写叙述:
20)统计被学生选修的课程门数。
Sql语句:
SELECT COUNT(DISTINCT SC.cno) FROM SC
查询结果描写叙述:
21)求选修C04课程的学生的平均年龄;
Sql语句:
SELECT AVG(YEAR(GETDATE())-YEAR(S.birthday)) FROM S, SC
WHERE SC.cno = 'C04' AND S.sno = SC.sno
查询结果描写叙述:
22)求LIU老师所授课程的每门课程的学生平均成绩;
Sql语句:
SELECT C.cno, AVG(SC.GRADE) FROM C, SC
WHERE C.teacher = 'LIU' AND C.cno = SC.cno
GROUP BY C.cno
查询结果描写叙述:
23)统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列。若人数同样,按课程号升序排列;
Sql语句:
SELECT SC.cno, COUNT(SC.sno) FROM SC
GROUP BY SC.cno
ORDER BY COUNT(SC.sno) DESC, SC.cno
查询结果描写叙述:
24)求年龄大于全部女同学年龄的男同学姓名和年龄。
Sql语句:
SELECT S.sname, YEAR(GETDATE())-YEAR(S.birthday) FROM S
WHERE S.sex = '男' AND YEAR(GETDATE())-YEAR(S.birthday) > (SELECT MAX(YEAR(GETDATE())-YEAR(S.birthday)) FROM S WHERE S.sex = '女')
查询结果描写叙述:
四、实验总结
多写写即可了