首页 > 对照材料 > 嵌套统计查询

嵌套统计查询

2020-10-17 08:34:56

实验四 嵌套统计查询

实验目的:掌握表数据的各种查询与统计SQL命令操作,具体分为:

了解查询的概念和方法

掌握SQL SERVER集成管理器查询子窗口中执行SELECT操作的方法

掌握SELECT语句在多表查询中的应用

掌握SELECT语句在复杂查询中的应用

一、在SQL Server2005中创建“简易教学管理”数据库XSGL

CREATE DATABASE XSGL

ON (NAME='XSGL_DATA',

FILENAME='E:\XSGL.MDF',

SIZE=10MB,

MAXSIZE=50MB,

FILEGROWTH=5%)

LOG ON

(NAME='XSGL_Log',

FILENAME='e:\XSGL_Log.ldf',

SIZE=2MB,

MAXSIZE=5MB,

FILEGROWTH=1MB)

二、在XSGL中创建表

Use XSGL

create table sc

(

sno char(5) not null constraint s_f foreign key references student(sno),

cno char(2) not null,

grade smallint check((grade is null)or (grade between 0 and 100)),

primary key(sno,cno),

constraint c_f foreign key(cno) references course(cno),

);

insert into student values('98001','钱横',18,'M','CS');

insert into student values('98002','王林',19,'F','CS');

insert into student values('98003','李民',20,'M','IS');

insert into student values('98004','赵三',16,'F','MA');

insert into course values('1','数据库系统','5',4);

insert into course values('2','数学分析',null,2);

insert into course values('3','信息系统导论','1',3);

insert into course values('4','操作系统原理','6',3);

insert into course values('5','数据结构','7',4);

insert into course values('6','数据处理基础',null,4);

insert into course values('7','C语言','6',3);

insert into sc values('98001','1',87);

insert into sc values('98001','2',67);

insert into sc values('98001','3',90);

insert into sc values('98002','2',95);

insert into sc values('98002','3',88);

三、用T-SQL查询

例3.1 查询与“钱恒”在同一个系学习的学生信息。

select *

from S

where sdept in(

select sdept

from S

where sname='钱恒')

或者

select S1.*

from S S1,S S2

where S1.sdept=S2.sdept and S2.sname='钱恒'

例3.2 查询选修了课程名为“数据库系统”的学生学号、姓名和所在系。

select S.sno,sname,sdept

from S,SC,C

where S.Sno=SC.sno and SC.cno=C.cno and cname='数据库系统'

例3.3 检索至少不学2和4两门课程的学生学号和姓名

select sno,sname

from S

where sno not in(

select sno

from SC

where cno in('2','4'))

例3.4 查询其他系中比信息系IS所有学生年龄均大的学生名单,并排序输出。

select sname

from S

where sage>ALL(select sage

from s

where sdept='IS')and sdept<>'IS'

order by sname

例3.5查询计算机科学系的学生与年龄不大于19岁的学生的交集。

select *

from s

where sdept='CS'

intersect

select *

from s

where sage<=19

例3.6 查询平均成绩大于85分的学号、姓名、平均成绩。

select s.sno,sname,avg(grade) avg_grade

from s,sc

where s.sno=sc.sno

group by s.sno,sname

having avg(grade)>85

实验内容:

(1)基于“教学管理”数据库XSGL,试用SQL的查询语句表达下列查询

检索王同学不学的课程的课程号。

检索至少选修两门课程的学生学号。

(2)基于“教学管理”数据库XSGL,试用SQL的查询语句表达下列查询

统计有学生选修的课程门数。

统计每门课程的学生选修人数,要求超过3人的课程才统计,要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列。

检索学号比“王菲”同学大二年龄比他小的学生学号。

求年龄大于女学生平均成绩的男学生姓名和年龄。

求年龄大于所有女学生年龄的男学生姓名和年龄。

检索选修“2”课程的学生中成绩最高的学生的学号。