web数据困编程.docx
《web数据困编程.docx》由会员分享，可在线阅读，更多相关《web数据困编程.docx（10页珍藏版）》请在冰豆网上搜索。
web数据困编程
《Web数据库编程》课程系列题目
（一）
----Oracle10gSQL高级查询
在教学数据库中有3个关系，分别是：
学生student、课程course、成绩sc
其模式描述分别是：
Student（sno,sname,dept,sex,age）Course（cno,cname,credit）
SC（sno,cno,grade）
试用SQL语言完成以下查询请求
1、分别定义student表、course表和SC表，要求定义PKFK和相应的用户完整性约束（自己设计）
setfeedbackoff;
connectsystem/system;
showuser;
droptablesc;
droptables;
droptablec;
createtables（
snochar（4）notnullprimarykey,
snamevarchar2（10）,
sexchar（3）,
deptvarchar2（4）,
ageint
）;
insertintosvalues（'801','张三','女','CS',20）;
insertintosvalues（'802','文章','男','MA',20）;
insertintosvalues（'803','张惠妹','女','CS',22）;
insertintosvalues（'804','马伊利','女','MA',29）;
insertintosvalues（'805','王二小','男',null,19）;
insertintosvalues（'806','王小波','男','CS',16）;
insertintosvalues（'807','李银河','女','MA',19）;
insertintosvalues（'808','苏小妹','女','CS',20）;
insertintosvalues（'809','李宇春','女','MA',21）;
insertintosvalues（'810','刘德华','男',null,22）;
createtablec（
cnochar（3）primarykey,
cnamevarchar2（20）,
creditint
）;
insertintocvalues（'01','数据库',3）;
insertintocvalues（'02','OS',4）;
insertintocvalues（'03','C++',2）;
insertintocvalues（'04','Java',3）;
createtablesc（
snochar（4）,
cnochar（3）,
gradenumeric（3）,
constraintsc_pkprimarykey（sno,cno）,
constraintsc_fk1foreignkey（sno）referencess（sno）,
constraintsc_fk2foreignkey（cno）referencesc（cno）,
constraintsc_gradecheck（gradebetween0and100orgradeisnull）
）;
insertintoscvalues（'801','01',81）;
insertintoscvalues（'801','02',null）;
insertintoscvalues（'801','03',92）;
insertintoscvalues（'802','03',68）;
insertintoscvalues（'803','01',59）;
insertintoscvalues（'803','04',61）;
commit;
select*froms;
select*fromc;
select*fromsc;
2、查询年龄小于18岁男生的名字和年龄。
selectsname,age
froms
whereage<18andsex='男';
3、查询学分不小于4的课程的课程号、课程名和学分。
selectcno,cname,credit
fromc
wherecredit>=4;
4、查询学生选课情况（列出学号、姓名、课程号、成绩；要求选课和未选课的学生信息都要列出来）
selects.sno,sname,o,grade
froms,sc
wheres.sno=sc.sno
union
selectsno,sname
froms
wheresnonotin（selectsno
fromsc
wherecnoisnotnull）;
5、查询“刘德华”选学“数据库”的成绩。
selectgrade
fromsc
wheresnoin（selectsnofromswheresname='张三'）
andcnoin（selectcnofromcwherecname='数据库'）;
6、查询没有选修“数据库”课程的学生信息（学号、姓名、性别）。
selectsno,sname,sex
froms
wheresnonotin（selectsno
fromscwherecno=（selectcno
fromc
wherecname='数据库'））;
7、查询至少选修了“数据库”和“操作系统”课程的学生的学号和姓名。
selectsno,sname
froms
wheresnoin（selectsno
fromsc
wherecno=（selectcno
fromc
wherecname='数据库'）
andsnoin（selectsno
fromsc
wherecno=（selectcno
fromc
wherecname='OS'）））;
8、查询至少有一门课程不及格的学生的姓名和成绩。
selects.sname,grade
froms,sc
wheres.sno=sc.snoandgrade<60;
9、查询没有“挂科”（成绩<60）学生的学号和姓名。
***
selectsno,sname
froms
wheresnonotin（selectsno
fromsc
wheregrade<60）;
10、统计学生的人数和平均年龄。
selectcount（*）,avg（age）
froms;
11、查询男生中最大年龄和最小年龄。
selectmax（age）,min（age）
froms
wheresex='男';
12、统计已选课的学生人数。
selectcount（distinctsno）
fromsc;
13、统计从未选课的学生人数。
selectcount（sno）
froms
wheresnonotin（selectsno
fromsc
wherecnoisnotnull）;
14、统计目前学生已选修的课程门数。
selectcount（distinctcno）
fromsc;
15、查询课容量小于70的课程名称和学分。
selectcname,credit
fromc
where（selectcount（*）
fromsc
wherecno=o）<70;
16、统计每个年龄所对应的学生人数。
***
selectage,count（*）
froms
groupbyage;
17、查询每门课选课的人数，给出课程号和人数。
***
selectcno,count（*）
fromsc
groupbycno;
18、查询每个学生选课的门数，给出学号和课程门数。
***
selectsno,count（*）
fromsc
groupbysno;
19、查询选课门数超过15的学生，给出学号，姓名和性别。
####
selectsno,sname,sex
fromswhere（selectcount（*）
fromsc
wheresno=s.sno）>15;
20、查询课程的名称和学分，该课程的选修人数在100至150之间。
####
selectcno,credit
fromc
where1<=（selectcount（*）
fromsc
wherecno=o）
and（selectcount（*）
fromsc
wherecno=o）<=3;
21、查询男女生的平均成绩个是多少。
selectavg（grade）
fromsc
wheresnoin（selectsno
froms
wheresex='男'）
union
selectavg（grade）
fromsc
wheresnoin（selectsno
froms
wheresex='女'）;
查询每个人的平均成绩：
selectsno,avg（grade）
fromsc
groupbysno;
22、查询平均成绩比每个女生平均成绩都高的男生的姓名和平均成绩。
***
selectsname,avg（grade）
froms,sc
wheres.sno=sc.snoandsex='男'
groupbysname
havingavg（grade）>=（selectavg（grade）
froms,sc
wheres.sno=sc.snoandsex='女'）;
23、查询平均成绩不及格的学生的学号、姓名和性别。
selectsno,sname,sex
froms
where（selectavg（grade）
fromsc
wheresno=s.sno）<60;
24、查询总成绩最高的学生的学号、姓名和性别。
####
selectsno,sname,sex
froms
where（selectsum（grade）
fromsc
wheresno=s.sno）>=all（selectsum（grade）
fromsc
groupbysno）;
25、查询所有的学生都选修的课程。
####
selectcno
fromc
wherecnoin（selectcno
fromsc
groupbycno
havingcount（cno）=（selectcount（*）
froms））;
26、学生成绩排榜（平均成绩从大到小顺序）。
selectsno,avg（grade）
fromsc
groupbysno
orderbyavg（grade）desc;
27、查询平均成绩不算最低的学生姓名和学号。
****
selectsno,sname
fromswhere（selectavg（grade）
fromsc
wheresno=s.sno）>some（selectavg（grade）
fromsc
groupbysno）;
28、将选修“数据库”课程的全体学生的成绩置零
updatescsetgrade=0
wherecno=1;
29、删除计算机专业所有学生的选课记录
delete
fromsc
wheresnoin（selectsno
froms
wheredept='CS'）;
commit;
select*fromsc;
30、建立计算机系选修了“数据库”课程且成绩在90分以上的学生的视图
createviews_sc
asselectsno,cno,grade
fromsc
wherecno=1andgrade>60;
commit;
select*froms_sc;
31、把查询student表和修改学生学号的权限授给用户U4
connectsystem/system;
showuser;
dropuserU4cascade;
createuserU4identifiedbyU4;
grantCreateSessiontoU4;
grantselectonstoU4;
grantupdate（sno）onstoU4;
connectU4/U4;
showuser;
select*fromsystem.s;
updatesystem.ssetsno=sno+1;
select*fromsystem.s;
32、建立账户U4,然后授予对话（session）和建立数据表的权利；在此基础上，实现查询超级用户（如System）中student表的信息（查阅oracle相应文档）
connectsystem/system;
showuser;
dropuserU4cascade;
createuserU4identifiedbyU4;
grantCreateSessiontoU4;
grantCreateTabletoU4;
grantUnlimitedTablespacetoU4;
grantselectonstoU4;
connectU4/U4;
showuser;
droptablestudent;
createtablestudent（
snochar（4）notnullprimarykey,
snamevarchar2（10）,
sexchar（3）,
deptvarchar2（4）,
ageint
）;
insertintostudentvalues（'801','张三','女','CS',19）;
insertintostudentvalues（'802','王五','男','MA',20）;
insertintostudentvalues（'803','张惠妹','女','CS',22）;
insertintostudentvalues（'804','赵六','女','MA',20）;
insertintostudentvalues（'805','钱七','男',null,19）;
select*fromstudent;
select*fromsystem.s;
33题暂时不做
33、假设教师表模式描述如下
Teacher（eno,ename,sex,titlte,salary）
试建立触发器，定义完整性规则：
“教授的工资不低于4000，否则自动变为4000”
在此基础上，增加约束为教授的工资只能增加不不能减少，你能实现吗？
试试看
要求：
1、使用Oracle提供的操作符和函数，如自然连接jion、外连接等
2、学生根据自己实际水平，自行选题，最后都要完成题目，只是时间不同
3、采用多种方法完成题目。
例如使用连接查询（含外连接）、嵌套查询（相关
子查询和不相关子查询）；鼓励大胆使用带有any或all或exists谓词的子
查询
4、熟练掌握select语句的一般形式。
重点掌握聚集函数的使用方法；where<条件表达式>、分组、having<条件表达式>在解决查询问题中的使用技巧
5、题目标记，由易到难分别是：
无标记、***、####
6、考核方法：
提交电子版作业，第四周周一验收记分（第一次作业的分数）
郑海旭2012.3.2
升级会员 