sql语句练习题1
数据库有如下四个表格:
student(sno,sname,sage,ssex,sdpt) 学生表
系表(dptno,dname)
course(cno,cname, gradet, tno) 课程表
sc(sno,cno,score) 成绩表
teacher(tno,tname) 教师表
要求:完成以下操作
1.查询姓"欧阳"且全名为三个汉字的学生的姓名。selectsnamefromstudent wheresnamelike“欧阳__;
2.查询名字中第2个字为"阳"字的学生的姓名和学号。
selectsname,snofromstudent wheresnamelike'_阳%';
3.查询所有不姓刘的学生姓名。
selectsname,sno,ssex
fromstudent
wheresnamenotlike“刘%”;
4.查询db_design课程的课程号和学分。
selectcno,ccredit fromcourse
wherecnamelike'db_design'
5.查询以"db_"开头,且倒数第3个字符为i的课程的详细情况。
select*fromcourse wherecnamelike'db%i__';
6.某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩。查
询缺少成绩的学生的学号和相应的课程号。
selectsno,cnofromscwheregradeisnull;
7.查所有有成绩的学生学号和课程号。
selectsno,cnofromscwheregradeisnotnull;
8.查询计算机系年龄在20岁以下的学生姓名。selectsnamefromstudent wheresdept='cs'andsage<20;
9.查询选修了3号课程的学生的学号及其成绩,查询结果按分数降序排列。selectsno,gradefromsc wherecno='3' orderbygradedesc;
10.查询学生总人数。
selectcount(*)fromstudent;
11.查询选修了课程的学生人数。
selectcount(distinctsno)fromsc;
12.计算1号课程的学生平均成绩。
selectavg(grade)fromsc wherecno='1';
13.查询选修1号课程的学生最高分数。
selectmax(grade)fromsc wherecno='1';
14.查询学生2选修课程的总学分数。
selectsum(grade)fromsc,course
where sno='2'and=;
15.查询选修了3门以上课程的学生学号。
selectsnofromscgroupbysno havingcount(*)>3;
16.查询每个学生及其选修课程的情况。
select student.*,sc.*, course.* from student,sc , course
where = and =;
17.查询每个学生及其选修课程的情况包括没有选修课程的学生
18.查询选修2号课程且成绩在90分以上的所有学生的学号、姓名select ,
from student,sc
where = and =”2and >90;
19.查询每个学生的学号、姓名、选修的课程名及成绩。
select ,sname,ssex,sage,sdept,cno,grade
from student left outjoin sco on=;
20.查询与“刘晨”在同一个系学习的学生。
selectsno,sname,sdept
from student
where sdept in
(select sdept from student where sname=”刘晨);
21.查询选修了课程名为“信息系统”的学生学号和姓名
select sno,sname from student where sno in
(select sno from sc where cno in
(select cno from course where cname=”信息系统));
22.找出每个学生超过他选修课程平均成绩的课程号。
select sno,cno from sc x where grade>=
(select avg(grade) from sc y where =;
23.将一个新学生记录(学号:8;姓名:陈冬;性别:男;所在系:is;年龄:
18岁)插入到student表中。
insert into student values ('8','陈冬','男','is',18);
24.将学生1的年龄改为22岁。
update student setsage=22 where sno='1';
25.将所有学生的年龄增加1岁。
update student setsage=sage+1;
26.将计算机科学系全体学生的成绩置零。
update sc set grade=0 where exits
(selete * from student where = and sdept=”计算机科学系”);
27.删除学号为的学生记录
delete from student where sno=”8';
28.删除所有的学生选课记录。
delete from sc;
29.删除2号课程的所有选课记录。
delete from sc where cno='2';
30.删除计算机科学系所有学生的选课记录。
delete from sc where sno in
(selete sno from student where sdept=”计算机科学系”);
31.建立信息系学生的视图。
create view is_student as
select sno,sname,sage from student where sdept='is';
sql语句练习题2
设教学数据库education,有三个关系:
学生关系s(sno,sname,age,sex,sdept);
学习关系sc(sno,cno,grade);
课程关系c(cno,cname,cdept,tname)
查询问题:
1:查所有年龄在20岁以下的学生姓名及年龄。
select sname,sage
from s
where sage<20;(not age>=20);
2:查考试成绩有不及格的学生的学号
select distinct sno
from sc
where grade<60;
3:查所年龄在20至23岁之间的学生姓名、系别及年龄。select sname,sdept,sage
from s
where sage between 20 and 23;
4:查计算机系、数学系、信息系的学生姓名、性别。select sname,ssex from s where sdept in(‘cs’,’is’,’math’);
5:查既不是计算机系、数学系、又不是信息系的学生姓名、性别select sname,ssex from s where sdept not in(‘cs’,’is’,’math’);6:查所有姓“刘”的学生的姓名、学号和性别。
select sname,sno,ssex from s where sname like‘刘%’;
7:查姓“上官”且全名为3个汉字的学生姓名。
select sname from s where sname like ‘上官__’;
8:查所有不姓“张”的学生的姓名。
select sname,sno,ssex from s where sname not like ‘张%’;9:查db_design课程的课程号。
select cno from c where cname like ‘db_design’;
10:查缺考的学生的学号和课程号。
select sno,cno from sc where grade is null;
11:查年龄为空值的学生的学号和姓名。
select sno,sname from s where sage is null;
12:查计算机系20岁以下的学生的学号和姓名。
select sno,sname
from s
where sdept=’cs’ and sage<20;
13:查计算机系、数学系、信息系的学生姓名、性别。
select sname,ssex
from s
where sdept=’cs’ or sdept=’is’ or sdept=’math’;
14:查询选修了c3课程的学生的学号和成绩,其结果按分数的
降序排列。
select sno,grade
from sc
where cno=’c3’
order by grade desc;
15:查询全体学生的情况,查询结果按所在系升序排列,对同一系中的学生按年龄降序排列。
select *
from s
order by sdep,sage desc;
16:查询学生总人数。
select count(*) from s;
17:查询选修了课程的学生人数。
select count(distinct sno) from sc
18:计算选修了c1课程的学生平均成绩。
select avg(grade)
from sc
where cno=’c1’;
19:查询学习c3课程的学生最高分数。
select max(grade)
from sc
where cno=’c3’;
20:查询各个课程号与相应的选课人数。
select cno, count(sno)
from sc
group by cno;
21:查询计算机系选修了3门以上课程的学生的学号。
select sno
from sc
where sdept=’cs’
group by sno
having count(*)>3;
22:求基本表s中男同学的每一年龄组(超过50人)有多少人要求查询结果按人数升序排列,人数相同按年龄降序排列。select sage,count(sno)
from s
where ssex='m'
group by sage
having count(*)>50
order by 2,sage desc;
23:查询每个学生及其选修课程的情况。
select , sname, sage, ssex, sdept, cno, grade
from s, sc
where =;
24:查询选修了c2课程且成绩在90分以上的所有学生。select ,sname
from s,sc
where =
and =’c2’
and >90;
25:查询每个学生选修的课程名及其成绩。
select ,sname,cname,
from s,sc,c
where = and =
26:统计每一年龄选修课程的学生人数。
select sage,count(distinct )
from s,sc
where =
group by sage;
27:查询选修了c2课程的学生姓名。
select sname from s where sno in(
select sno from sc where cno=’c2’);
28:查询与“张三”在同一个系学习的学生学号、姓名和系别。select sno,sname,sdept from where sdept=(
select sdept from s where sname=’张三’);
29:查询选修课程名为“数据库”的学生学号和姓名。
select sno,sname from s where sno in (
select sno from sc where cno in (
select cno from c where cname=’db’));
30:查询与“张三”在同一个系学习的学生学号、姓名和系别。select sno,sname,sdept from s where sdept=(
select sdept from s where sname=‘张三’);
31:查询选修课程名为“数据库”的学生学号和姓名。
select sno,sname from s where sno in
(select sno from sc where cno=
(select cno from c where cname=’db’));
32:查询选修了c2课程的学生姓名。
1. select sname from s where sno in
(select sno from sc where cno=’c2’);
2. select sname from s where exists
(select * from sc where = and cno=’c2’);
33:查询选修了全部课程的学生姓名。
select sname from s where not exists
(select * from c where not exists
(select * from sc where = and =);
36:查询所学课程包含学生s3所学课程的学生学号select distinct sno from sc as x where not exists (select * from sc as y where =’s3’ and not exists (select * from sc as z where = and =);
sql语句练习题3
一、简单查询
1、列出全部学生的信息。
select * from学生
2、列出软件专业全部学生的学号及姓名。
select学号,姓名from学生where专业="软件"
3、列出所有必修课的课号。
select distinct课号from必修课
4、求1号课成绩大于80分的学生的学号及成绩,并按成绩由高到低列出。select学号,成绩from选课where课号="1"and 成绩>80 order by成绩desc
5、列出非软件专业学生的名单。
方法一:select姓名from学生where专业<>"软件"
方法二:select姓名from学生where not专业="软件"
方法三:select姓名from学生where专业!="软件"
6、查询成绩在70~80分之间的学生选课得分情况
方法一:select*from选课where成绩>=70and成绩<=80
方法二:select*from选课where成绩between70and80
不在此范围内的查询:(注意写出和以下语句等价的语句)
select * from 选课where成绩not between70and80
7、列出选修1号课或3号课的全体学生的学号和成绩。
方法一:select学号,成绩from选课where课号="1"or课号="3"
方法二:select学号,成绩from选课where课号in("1","3")
相反条件查询:select学号,成绩from选课where课号notin("1","3")
8、列出所有98级学生的学生成绩情况。
select*from选课where学号like"98%"
select*from选课where学号like"98____"
相反条件查询:select*from选课where学号notlike"98%"
9、列出成绩为空值(或不为空值)的学生的学号和课号。
答案一:select学号,课号from选课where成绩isnull
答案二:select学号,课号from选课where成绩isnotnull
10、求出所有学生的总成绩。
select sum(成绩) as总成绩from 选课
11、列出每个学生的平均成绩。
select学号,avg(成绩) as 平均成绩from选课group by学号
12、列出各科的平均成绩、最高成绩、最低成绩和选课人数。
select课号,avg(成绩)as平均成绩,max(成绩)as最高分,;
min(成绩)as最低分,count(学号) as 选课人数from 选课group by 课号
二、连接查询
(一)简单连接
1、列出选修1号课的学生姓名及成绩。
select 姓名,成绩from学生,选课where学生.学号=选课.学号and 课号="1"
2、列出选修1号课的学生的学号、姓名及成绩。
select学生.学号,姓名,成绩from学生s,选课xwheres.学号=x.学号and课号="1"
3、求出总分大于150的学生的学号、姓名及总成绩。
select学生.学号,姓名,sum(成绩)as总成绩from学生,选课;
where学生.学号=选课.学号groupby选课.学号havingsum(成绩)>150
(二)自连接查询
1、列出那些专业相同的学生相应的姓名及专业信息。
select a.姓名,b.姓名,专业from学生a,学生bwherea.学号<>b.学号anda.专业=b.专业
2、求至少选修1号课和2号课的学生的学号。
selectx.学号from选课x,选课ywherex.学号=y.学号andx.课号="1"andy.课号="2"
3、有以下表
币种1代码c(2)、币种2代码c(2)、买入价n(8,4)、卖出价n(8,4)
外汇汇率.dbf
币种1c(4)、币种2c(4)、买入价n(8,4)、卖出价n(8,4)
外汇代码.dbf
外汇名称c(10)、外汇代码c(10)
要求:将所有“外汇汇率”表中的数据插入rate表中并且顺序不变,由于“外汇汇率”中的币种1和币种2存放的是外币名称,而rate表中的币种1代码和币种2代码应该存放外币代码,所以插入时要做相应的改动,外币名称与外向代码的对应关系存储在“外汇代码”表中。
selecta.外币代码as币种1代码,b.外币代码as币种2代码,;
买入价,卖出价from外汇代码a,外汇汇率,外汇代码b;
wherea.外币名称=外汇汇率.币种1andb.外币名称=外汇汇率.币种2intotablerate 4、假定有“雇员”表(雇员号c(2),雇员姓名c(6),经理号c(2)),根据雇员关系列出上一级经理及其所领导的职员清单。(教案中的例题)select"领导",s.雇员姓名,"雇员",e.雇员姓名from雇员s,雇员ewheres.雇员号=e.经理
(三)超连接
1、列出选修1号课的学生姓名及成绩。
方法一:(使用简单连接查询格式)
select姓名,成绩from学生,选课where学生.学号=选课.学号and课号="1"
方法二:(使用内部连接格式)
select姓名,成绩from学生innerjoin选课on学生.学号=选课.学号where课号="1"方法三:内部连接的inner短语可以省略。(与方法二等价)
select姓名,成绩from学生join选课on学生.学号=选课.学号where课号="1" 2、查询订货管理数据库中数据的仓库号、城市、供应商名和地址信息。
方法一:使用简单连接格式。
select仓库.仓库号,城市,供应商名,地址from供应商,订购单,职工,仓库;
where供应商.供应商号=订购单.供应商号and订购单.职工号=职工.职工号;
and职工.仓库号=仓库.仓库号
方法二:使用超连接的内部连接格式。(注意连接条件的顺序)
select仓库.仓库号,城市,供应商名,地址from供应商join订购单join职工join仓库;
on职工.仓库号=仓库.仓库号on订购单.职工号=职工.职工号on供应商.供应商号=订购单.供应商号
3、查询没有选修任何课程的学生姓名。
方法一:使用嵌套查询
select姓名from学生where学号notin(select学号from选课)
方法二:使用超连接的右连接。
select姓名from选课rightjoin学生on选课.学号=学生.学号where选课.学号<>学生.学号
方法三:使用超连接的左连接。(注意表名顺序和方法二的不同)
select姓名from学生leftjoin选课on选课.学号=学生.学号where选课.学号<>学生.学号
三、嵌套查询
(一)普通嵌套与谓词exists
1、列出选修汇编语言课的学生的学号。
方法一:
select学号from选课where课号=(select课号from课程where课名="汇编语言")方法二:使用谓词exists。注意和方法一格式上的不同。
select学号from选课whereexist(select*from课程;
where课名="汇编语言"and选课.课号=课程.课号)
2、求软件专业所有必修课的课程信息。
方法一:
select*from课程where课号in;(select课号from必修课where必修专业="软件")方法二:
select*from课程whereexist(select*from必修课where必修专业="软件";
and课程.课号=必修课.课号)
(二)量词any、some、all
1、求选修2号课的学生中,成绩比选修1号课的最低成绩要高的学生的学号和成绩。
方法一:
select学号,成绩from选课where课号="2"and成绩>;
(selectmin(成绩)from选课where课号="1")
方法二:any等价于some,所以可将any换成some。
select学号,成绩from选课where课号="2"and成绩>any;
(select成绩from选课where课号="1")
2、求选修2号课的学生中,成绩比选修1号课的任何学生的成绩都要高的那些学生的学号和成绩。
方法一:
select学号,成绩from选课where课号="2"and成绩>;
(selectmax(成绩)from选课where课号="1")
方法二:
select学号,成绩from选课where课号="2"and成绩>all;
(select成绩from选课where课号="1")
(三)内外层互相关嵌套(外层依赖于内层的查询结果,内层依赖于外层来进一步查询)
1、列出每门课程中成绩最高的选课信息。
select*from选课awhere成绩=(selectmax(成绩)from选课bwherea.课号=b.课号) 2、列出每个学生中成绩低于本人平均成绩的选课信息。
select*from选课awhere成绩<(selectavg(成绩)from选课bwherea.学号=b.学号) 3、列出表“订购单”(其内容就是在订购单表的基础上增加一个总金额字段)中每个职工经手的具有最高总金额的订购单信息。(教案中例题)
selectout.职工号,out.供应商号,out.订货单号,out.订货日期,out.总金额;
from订购单outwhere总金额=(selectmax(总金额)from订购单inner1; whereout.职工号=inner1.职工号)
四、操作功能
1、在课程表中插入新的元组(5,大学英语)。
insertinto课程(课号,课名)values("5","大学英语")
2、给学生表中男生的记录加上删除标记。
deletefrom学生where性别="男"
3、将选课表中所有课程的成绩分别提高5分。将课号为“4”的课程的成绩置为空值。
update选课set成绩=成绩+5
update选课set成绩=nullwhere课号="4"
五、定义表结构
1、建立研究生表,该表不属于任何数据库,其结构如表所示:
createtable研究生free(学号c(6),姓名c(8),性别c(2),年龄n(3,0),入学年月dnull) 2、假设已经建立了xsk数据库,在xsk中建立“学生1”表,其结构如表所示
createtable学生1(学号c(6)primarykey,姓名c(8)notnull,性别c(2),;
年龄n(3,0)check年龄>10and年龄<45error"年龄必须大于10小于45",是否党员l,;入学年月ddefault{^1999-9-1})
3、在xsk数据库建立“课程1”表,其结构如表所示:
createtable课程1(课号c(4)primarykey,课程名c(10)notnull,学分n(2))
4、在xsk数据库建立“选课1”表,其结构如表所示:
createtable选课1(学号c(6)reference学生,课号c(4)reference课程,成绩n(5,2))
六、修改表结构
1、为“课程1”表添加一个开课学期字段,字段类型为数值型,长度为1。altertable课程1addcolumn开课学期n(1)
2、修改开课学期字段为字符型,合法值为1或2。
altertable课程1altercolumn开课学期c(1)check开课学期="1"or开课学期="2"
3、删除“课程1”表中对开课学期字段的合法值约束,设置默认值为1。altertable课程1alter开课学期dropcheck
altertable课程1alter开课学期setdefault"1"
4、删除“课程1”表中的开课学期字段。修改课程名字段为“课名”字段。altertable课程1drop开课学期rename课程名to课名
5、在“学生1”表的“年龄”字段上建立候选索引。
altertable学生1addunique年龄tag年龄
6、在“学生1”表中添加一个出生日期字段,删除年龄字段。
altertable学生1add出生日期d
altertable学生1dropuniquetag年龄drop年龄