SQL select查询实验
一、 实验目的
- 了解查询的概念和方法
- 掌握SQL Server集成管理器查询子窗口中执行select操作的方法
- 掌握select语句在单表查询中的应用
- 掌握select语句在多表查询中的应用
- 掌握select语句在复杂查询中的使用方法
二、实验内容与要求
请有选择地实践以下各题,
(1)基于“教学管理”数据库jxgl,试用SQL的查询语句表达下列查询
①检索年龄大于23岁的男学生的学号和姓名
②检索至少选修一门课程的女学生姓名
③检索王同学不学的课程的课程号;
④检索至少选修两门课程的学生学号
⑤检索全部学生都选修的课程的课程号与课程名
⑥检索选修了所有3学分课程的学生学号,人
(2)基于“教学管理”数据库ⅸxg,试用SQL的查询语句表达下列查询
①统计所有学生选修的课程门数:
②求选修4号课程的学生的平均年龄
③求学分为3的每门课程的学生平均成绩
统计没门课程的学生的选修人数，超过三人的课程才统计，要求输出课程号和选修人数，查询结果按人数降序排列,若人数相同,按课程号升序排列
检索学号比王非同学大面年龄比他小的学
⑥检索姓名以王打头的所有学生的姓名和年龄
⑦在SC中检索成绩为空值的学生学号和课程号
③求年龄大于女同学平均年龄的男学生的姓名和年龄
②求年龄大于所有女同学年龄的男学生的姓名和年龄
①检索所有比“王华”年龄大的学生姓名、年龄和性别
①检索选修2号课程的学生中成绩最高的学生的学号
⑩检索学生姓名及其所选修课程的课程号和成绩:
0检索选修4门以上课程的学生总成绩(不统计不及格的课程),并要求按总成绩的降序排列出来。
(3)设有如下4个基本表(表结构与表内容是假设的),如表41、表42、表43、表44所示,请先创建数据库及根据表内容创建表结构,并添加表记录,实践以下各题的SQL命令操作
①查询选修课程“8105”且成绩在80到90之间的所有记录;
②查询成绩为79、89或90的记录;
③查询9803班的学生人数;
④查询至少有20名学生选修的并以8开头的课程的平均
查询最低分大于80，最高分小于95的sno与平均分
查询9803班学生所选各课程的课程号及其平均成绩
查询选修“8105”课程的成绩高于9809号同学所有同学的记录
②查询与学号为“9808”的同学同岁的所有学生的SNOSNAME和AGE
②查询钱军“教师任课的程见选修其课程字生的学号和成绩
①查询选修某课程的学生人数多于20人的教师姓名
查询同学选修编号为8105课程且成绩至少高于“8245°同学的sno即8105课程成绩,成绩从高到低次序排列:4o
其选修编号为“8245”课程的同午
查询选修编号为8105课程且成绩高于所有选修编号为“8245”课程成绩的同学飞
列出所有教师和同学的姓名,SEX、AGE,
④查询成绩比该课程平均成绩高的学生的成绩表;
0列出所有任课教师的TNAME和DEPT:
⑤列出所有未讲课教师的TNAME和DEPT
①列出至少有4名男生的班号
③查询不姓“张”的学生的记录:
①查询每门课最高分的学生的SNO、CNO、GRADE
②查询与“李华”同性别并同班的同学的SNAME:
如查询“女”教师及其所上的课程:
②查询选修“数据库系统”课程的“男”同学的成绩表;
②查询所有比刘涛年龄大的教师姓名、年龄和刘涛的年龄
@查询不讲授“8104”号课程的教师姓名
写出实现以下各题功能的SQL语句,
①取出所有工程的全部细节
②取出所在域市为上海的所有工程的全部细节;
③取出重量最轻的零件代号
④取出为工程J1提供零件的供应商代号
取出为工程J1提供零件P的俱应商代号
⑥取出由供应商S提供零件的工程名称
②取出供应商S提供的零件的颜色;记
③取出为工程1和12提供零件的供应商代号
②取提供红色零件的供应商代号:
取出为所在城市为上海的工程提供零件的供应商代号
①乘出为所在城
市为国或电政B战逛包要再的
共应商代号;)√
0取出供应商与工程所在城市相同的供应商提供的考线代号
0取出上海的供应商提供给上海的任一工程的零件的代号;
0取出至少有一个和工程不在同一城市的供应商提供零件的工程代号;
⑤取出上海供应商不提供任何零件的工程代号;
取出这样一些供应商代号,他们能够提供至少一种提供红色零件的供应出由供应商S1提供零件的工程代号;
③取出所有这样的二元组<CITY,CIY>,使得第1个城市的供应商为第2供应商程提供零件;
取出所有这样的三元组<CTY,P#,CITY>,使得第1个城市的供应商的工程提供指定的零件八
②重复①题,但不检索两个CITY值相同的二元组;
决求没有使用天津单位生产的红色零件的工程号:
彭至少用了单位S所供应的全部军件的工程号
4完成如下更新操作:把全部红色零件的颜色改成蓝色,由S6供给J4的零件
供应)请作必要的修改,从供应商关系中删除相关记录,删除工程j1订购的S4的零件,请将(s9,18,P4,200插人供应零件关系
create table student1
(
sno char(5) primary key,
sname varchar(20),
sage smallint check(sage>=15and sage<=45),
ssex char(2) default '男' check(ssex='男'or ssex='女'),
sdept char(2),
);
create table course2
(
cno char(2) primary key,
cname varchar(20),
cpno char(2),
ccredit smallint,
);
create table sc
(
sno char(5) foreign key references student1(sno),
cno char(2) not null,
grade smallint check((grade is null) or (grade between 0 and 100 )),
primary key (sno ,cno),
foreign key(cno) references course2(cno),
);
insert into student1 values('98001','钱横',18,'男','CS');
insert into student1 values('98002','王林',19,'女','CS');
insert into student1 values('98003','李民',20,'男','IS');
insert into student1 values('98004','赵兰',16,'女','MA');
insert into course2 values('1','数据库系统','5',4);
insert into course2 values('2','数学分析',null,2);
insert into course2 values('3','信息系统导论','1',3);
insert into course2 values('4','操作系统原理','6',3);
insert into course2 values('5','数据结构','7',4);
insert into course2 values('6','数据处理基础',null,4);
insert into course2 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);
create table student(sno char(6)not null primary key(sno),
sname varchar(20),
sex char(2) default '男'check(sex='男' or sex='女'),
age smallint check(age>=15 and age<=45),
class char(4));
create table teacher(tno char(3) not null primary key(tno),
tname varchar(20),
sex char(2) default '男'check(sex='男' or sex='女'),
age smallint,
prof varchar(10),
dept varchar(20));
create table course (cno char(4) not null primary key(cno),
cname varchar(20),
tno char(3),
xf int,
foreign key(tno) references teacher(tno));
create table sc (sno char(6)not null constraint s_f foreign key references student(sno),
cno char(4) not null,
grade smallint check((grade is NULL)or (grade between 0 and 100)),
primary key(sno,cno),
foreign key(cno) references course(cno));
INSERT INTO student VALUES('980101','李华','男', 19, '9801');
INSERT INTO student VALUES('980102','张军','男', 18, '9801');
INSERT INTO student VALUES('980302','大卫','男', 16, '9803');
INSERT INTO student VALUES('980303','赵峰','男', 25, '9803');
INSERT INTO student VALUES('980103','王华','女', 19, '9801');
INSERT INTO student VALUES('980301','王非','女', 19, '9803');
INSERT INTO student VALUES('980304','孙娟','女', 19, '9803');
INSERT INTO teacher VALUES('801','李新','男',38,'副教授','计算机系');
INSERT INTO teacher VALUES('802','钱军','男',45,'教授','计算机系');
INSERT INTO teacher VALUES('803','王立','女',35,'副教授','食品系');
INSERT INTO teacher VALUES('804','李丹','女',22,'讲师','食品系');
INSERT INTO teacher VALUES('805','刘涛','女',35,'讲师','食品系');
INSERT INTO course VALUES('8104','计算机导论','801',2);
INSERT INTO course VALUES('8244','数据库系统','803',3);
INSERT INTO course VALUES('8105','C语言','802',3);
INSERT INTO course VALUES('8245','数据结构','804',4);
INSERT INTO sc VALUES('980101','8104',67);
INSERT INTO sc VALUES('980101','8105',86);
INSERT INTO sc VALUES('980102','8244',96);
INSERT INTO sc VALUES('980102','8245',76);
INSERT INTO sc VALUES('980103','8104',86);
INSERT INTO sc VALUES('980103','8105',56);
INSERT INTO sc VALUES('980301','8244',76);
INSERT INTO sc VALUES('980302','8245',96);
INSERT INTO sc VALUES('980302','8104',67);
INSERT INTO sc VALUES('980303','8244',76);
INSERT INTO sc VALUES('980303','8245',79);
INSERT INTO sc VALUES('980304','8104',86);
INSERT INTO sc VALUES('980304','8105',95);
--我另外插入的数据，可以查询所有学生都选了的课程
INSERT INTO sc VALUES('980102','8104',75);
INSERT INTO sc VALUES('980301','8104',80);
INSERT INTO sc VALUES('980303','8104',85);
--我另外插入的数据，可以查询所有课程都选了的学生
INSERT INTO sc VALUES('980101','8244',50);
INSERT INTO sc VALUES('980101','8245',40);
create table s
(
sn char(2) primary key,
sname char(3),
city varchar(8),
);
create table p
(
pn char(2) primary key,
pname char(3),
color char(2),
weight int,
);
create table j
(
jn char(2) primary key,
jname char(3),
city varchar(8),
);
create table spj
(
sn char(2),
pn char(2),
jn char(2),
qty int,
primary key(sn,pn,jn),
foreign key(sn) references s(sn),
foreign key(pn) references p(pn),
foreign key(jn) references j(jn),
);
insert into s values('s1','sn1','上海');
insert into s values('s2','sn2','北京');
insert into s values('s3','sn3','南京');
insert into s values('s4','sn4','西安');
insert into s values('s5','sn5','上海');
insert into p values('p1','pn1','红',12);
insert into p values('p2','pn2','绿',18);
insert into p values('p3','pn3','蓝',20);
insert into p values('p4','pn4','红',13);
insert into p values('p5','pn5','白',11);
insert into p values('p6','pn6','蓝',18);
insert into j values('j1','jn1','上海');
insert into j values('j2','jn2','广州');
insert into j values('j3','jn3','武汉');
insert into j values('j4','jn4','北京');
insert into j values('j5','jn5','南京');
insert into j values('j6','jn6','上海');
insert into j values('j7','jn7','上海');
insert into spj values('s1','p1','j1',200);
insert into spj values('s1','p1','j4',700);
insert into spj values('s2','p3','j1',800);
insert into spj values('s2','p3','j2',200);
insert into spj values('s2','p3','j3',30);
insert into spj values('s2','p3','j4',400);
insert into spj values('s2','p3','j5',500);
insert into spj values('s2','p3','j6',200);
insert into spj values('s2','p3','j7',300);
insert into spj values('s2','p5','j2',200);
insert into spj values('s3','p3','j1',100);
insert into spj values('s3','p4','j2',200);
insert into spj values('s4','p6','j3',300);
insert into spj values('s4','p6','j7',500);
insert into spj values('s5','p2','j2',500);
insert into spj values('s5','p2','j4',250);
insert into spj values('s5','p5','j5',300);
insert into spj values('s5','p5','j7',100);
insert into spj values('s5','p6','j2',200);
insert into spj values('s5','p1','j4',300);
insert into spj values('s5','p3','j4',100);
insert into spj values('s5','p4','j4',200);
查询
select sno,sname
from student1
where sage>23 and ssex='男';
select sname
from sc,student1
where sc.sno=student1.sno and ssex='女'
group by sname
having count(*)>=1;
select cno
from course2
where cno not in
(
select cno
from sc ,student1
where sc.sno=student1.sno and student1.sname='王林'
);
select *
from course2;
select * from student1;
select * from sc;
select student1.sno
from sc,student1
where sc.sno=student1.sno
group by student1.sno
having count(*)>=2;
select cno,cname
from course2
where not exists
(
select *
from student1
where not exists
(
select *
from sc
where sc.sno=student1.sno and sc.cno=course2.cno
)
);
select distinct sno
from sc
where exists
(
select student1.sno
from student1,sc,course2
where student1.sno=sc.sno and course2.cno=sc.cno and ccredit=3
);
select count(distinct cno) from sc;
select avg(sage)
from sc,student1,course2
where sc.sno=student1.sno and course2.cno=sc.cno and course2.cno=2;
select avg(grade)
from sc,course2
where sc.cno=course2.cno and course2.ccredit=3;
select cno,count(*)
from sc
group by(cno)
having count(*)>1
order by count(*);
select cno,count(*)
from sc
group by(cno)
having count(*)>1--改数据 表中最多只有一门课程两个人选
order by count(*),cno;
select sname
from student1
where sno<(select sno from student1 where sname='王林')and sage<(select sage from student1 where sname='王林');
select sname,sage
from student1
where sname like '王%';
select sno,cno
from sc
where grade=null;
select sname,sage
from student1
where ssex='男' and sage>(select AVG(sage) from student1 where ssex='女');
select sname,sage
from student1
where ssex='男' and sage>(select max(sage) from student1 where ssex='女');
select sname,sage,ssex
from student1
where sage>(select sage from student1 where sname='赵兰');
select sno
from sc x
where not exists
(
select sno
from sc y
where y.grade>x.grade
)
;
select sname,cno,grade
from student1,sc
where student1.sno=sc.sno ;
select sno,sum(grade)
from sc
group by sno
having count(*)>1 and exists (select * from sc where grade >60)
order by sum(grade) desc;
select student.*,grade
from student,sc
where cno='8105' and grade between 80 and 90;
select student.*,grade,cno
from sc,student
where grade='79' or grade='89' or grade='99';
select count(sno)
from student
where class='9803';
select course.cno,avg(grade)
from course,sc
group by course.cno
having count(*)>=10 and course.cno in(select cno from sc where cno like '8%') ;
-------------------
select sno,avg(grade)
from sc
group by(sno)
having max(grade)<96 and min(grade)>75;
select student.*
from student, sc
where student.sno=sc.sno and cno='8105' and grade>(select grade from sc where sno='980101' and cno='8105');
select sno,sname,age
from student
where age=(select age from student where sno='980102');
select distinct course.cno , sno,grade
from course ,sc,teacher
where sc.cno=course.cno and course.cno in (
select course.cno
from course,teacher
where teacher.tno=course.tno and tname='钱军'
);
select tname from teacher where tno in (select tno from course where cno in(select cno from sc group by cno having count(*) >3));
select distinct sno,grade
from sc,course
where sc.cno=course.cno and sc.cno='8105' and grade>any(select grade from sc where cno='8245')
order by grade desc;
select distinct sc.cno,sno,grade
from sc,course
where sc.cno=course.cno and sc.cno='8105' and grade>all(select grade from sc where cno='8104')
order by grade desc;
----------
select sname,sex,age from student
union
select tname,sex,age from teacher;
select * from sc a where grade<(select avg(grade) from sc b where b.cno =a.cno );
select tname,dept
from teacher
where tno in
(select tno from course where cno in(select cno from sc));
select tname,dept
from teacher
where tno not in
(select tno from course where cno in(select cno from sc));
select class from student where sex='男' group by class having count(*)>1;
select * from student where sname not like '张%';
select sno,cno,grade
from sc
where grade=(select MAX(grade) from sc ) ;
select sname
from student
where sex=(select sex from student where sname='李华') and class=(select class from student where sname='李华');
------------------------------------------------------------------------
select tname,cname
from teacher ,course
where sex='女' and course.tno=teacher.tno;
select sname,sc.*
from course,sc,student
where course.cno=sc.cno and student.sno=sc.sno and sex='男' and sc.cno=(select cno from course where cname='数据库系统');
select tname,age
from teacher
where age>(select age from teacher where tname='刘涛')
union
select null,age
from teacher where tname='刘涛'
select tname
from teacher ,course
where course.tno=teacher.tno and cno not in( select cno from course where cno='8104' );
----------1-8题--------
select *
from j;
select *
from j
where city='上海';
select min(weight)
from p;
select sn
from spj
where jn='j1';
select sn
from spj
where jn='j1' and pn='p1';
select sname
from s
where sn='s1';
select distinct color
from spj,p
where spj.pn=p.pn and sn='s1';
select distinct sn
from spj
where jn='j1' or jn='j2';
--------------10-17
select distinct s.sn
from spj,s
where spj.sn=s.sn and city='上海';
select distinct s.sn
from s
where city in ('上海','北京') and sn in (select sn from spj,p where spj.pn=p.pn and color='红');
select distinct pn
from s,j,spj
where s.sn=spj.sn and j.jn=spj.jn and s.city=j.city;
select distinct j.jn
from s,j,spj
where s.sn=spj.sn and j.jn=spj.jn and s.city='上海'and j.city='上海';
select distinct pn
from s,j,spj
where s.sn=spj.sn and j.jn=spj.jn and s.city=j.city;
select jn
from j
where jn not in(select jn from spj,s where spj.sn=s.sn and city='上海');
select distinct s.sn
from s,p,spj
where s.sn=spj.sn and p.pn=spj.pn and color='红'
select jn
from j
where jn in
(
select jn from spj,s
where spj.sn=s.sn and spj.sn='s1');
-------------------18-19-----
select distinct s.city as '供应商',j.city as '工程' from spj,s,j
where s.sn=spj.sn and j.jn=spj.jn
select distinct s.city as '供应商',p.pn,j.city as '工程'
from spj,s,j,p
where s.sn=spj.sn and j.jn=spj.jn and p.pn=spj.pn;
-------------------------20
select distinct s.city as '供应商',j.city as '工程' from spj,s,j
where s.sn=spj.sn and j.jn=spj.jn and j.city!=s.city;
select distinct jn 工程号
from spj
where jn not in
(
select spj.jn
from spj,j,p,s
where s.city='天津' and p.color='红' and s.sn=spj.sn and p.pn=spj.pn
);
select distinct jn 工程号
from spj as x
where not exists
(
select * from spj as y
where y.sn='s1' and not exists
(
select *
from spj as z
where z.pn=x.pn and z.jn=x.jn
)
);
----------最后一题
update p
set color='蓝'
where color='红';
update spj
set sn='s8'
where sn='s6' and pn='p6' and jn='j4';
delete from spj where sn='s2';
delete from s where sn='s2';
delete from spj where jn='j4' and sn='s1';
insert into spj values('s4','p3','j7',200);
select * from s;
select * from p;
select * from j;
select * from spj;
实验的结果截图过多不上传
4735
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
