Ⅰ 关于Oracle数据库编程题的解答。谢谢各位！
1、查询姓“李”的老师的个数；
SELECT COUNT(Tno) FROM Teacher WHERE Tname LIKE '李%'
2、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；
SELECT Sno, Sname
FROM Student
WHERE sno IN(SELECT sno
FROM (SELECT Sno FROM SC WHERE Cno = 'c001') t1
(SELECT Sno FROM SC WHERE Cno = 'c002') t2
WHERE t1.sno = t2.sno)
3、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
SELECT Sno, Sname
FROM student
WHERE Sno IN(SELECT SC
FROM SC
WHERE CNO IN (SELECT CNO FROM Course WHERE Cname = '叶平')
GROUP BY SC HAVING COUNT(CNO) = (SELECT COUNT(CNO) FROM Course WHERE Cname = '叶平'))
4、查询“c001”课程比“c002”课程成绩高的所有学生的学号
SELECT C1.SC
FROM (SELECT SC, Score FROM SC WHERE Cno = 'c001') c1
(SELECT SC, Score FROM SC WHERE Cno = 'c002') c2
WHERE c1.SC = c2.SC
AND c1.Score > c2.Score
如果只有C1,而没有C2成绩，用这个好一点
SELECT SC
FROM (SELECT SC, Score FROM SC WHERE Cno = 'c001') c1
left join (SELECT SC, Score FROM SC WHERE Cno = 'c002') c2
ON c1.SC = c2.SC AND c1.Score > c2.Score
5、查询平均成绩大于60分的同学的学号和平均成绩；
SELECT Sno, AVG(score)
FROM SC GROUP BY Sno Having AVG(score) > 60
6、查询所有同学的学号、姓名、选课数、总成绩；
SELECT stu.Sno, stu.Sname, COUNT(Cno), SUM(score)
FROM Student stu, SC
WHERE stu.Sno = Sc.sno
GROUP BY stu.sno, stu.Sname
7、查询没有学全所有课的同学的学号、姓名；
SELECT Sno, Sname
FROM SC
WHERE Sno NOT IN (SELECT Sno
FROM SC
GROUP BY SC
HAVING COUNT(CNO) < (SELECT COUNT(DISTINCT CNO) FROM SC))
Ⅱ oracle璇鍙ョ紪绋嬮
锻桦伐琛
Create table worker(
wno number(2) primary key,
wname varchar2(10) not null,
wsex char(2) check (wsex in ('鐢','濂')),
wage number(4) check (wage>18),
wdept varchar2(18));
搴忓垪
Create sequence seq_worker increment by 1 start
with 1 maxvalue 200000 minvalue 0;
椤圭洰琛
Create table project(
pno number(6) primary key,
pname varchar2(20) not null,
pleader varchar2(10));
濂栭噾琛
Create table wp(
wno number(6) references worker(wno),
pno number(7) references project(pno),
bonus number,
primary key (wno,pno));
1. insert into worker values(seq_worker.nextval,'𨱒庡溅','鐢',46,'');
2. insert into project values(101,'java瀹炶椤圭洰','');
3. insert into wp values(1,101,6000);
2. update worker set wage=36 where wno=1;
3. select * from worker where wage between 20 and 40;
4. select wno,wname,wsex,wsex,wage,wage,nvl(wdept,'镞犻儴闂') from worker;
5. select * from worker where wname like '鐜%' and length(wname)=3 order by wage;
6. select * from project where pleader is not null;
7. select sum(bonus) from wp;
8. select count(distinct wno) from wp;
9. select wno,avg(bonus),count(distinct pno) from wp group by wno;
10. select pno from wp having count(distinct wno)>2;
12. select * from worker where wno in (select wno from wp having sum(bonus)<500 goup by wno);
13. select wno,wname,pno,pname from worker x,project y,wp
where x.wno=wp.wno and y.pno=wp.pno and
wp.wno in (select wno fron worker where wname='𨱒庡溅');
14. select x.wno,wname,y.pno,pname,bonus from worker x,project y,wp
where x.wno=wp.wno(+) and y.pno=wp.pno(+) order by x.wno,y.pno,bonus;
15. select wno,wname from worker where wno in (
select wno from wp where bonus>(select avg(bonus) from wp
where pno=(select pno from project where pname='java瀹炶椤圭洰')));
16. select wname from worker where wno in (select wno from wp
where bonus in (select max(bonus) from wp
where pno=(select pno from project where pname='java瀹炶椤圭洰')));
17. select x.*,y.* from wp,worker x,project y
where wp.wno=x.wno and wp.pno=y.pno and
wp.wno in (select wno from wp having count(distinct pno)>1 group by wno);
18. select pno,bonus,x.* from wp,worker x where wp.wno=x.wno and wp.wno in
(select wno from wp where (pno,bonus) in (select pno,max(bonus) from wp group by pno));
19. select x.*,c_num 椤圭洰鏁 from worker x,(select wno,max(c_p) c_num
from (select wno,count(pno) c_p from wp group by wno) y group by wno) y where x.wno=y.wno;
20. delete worker where wno not in (select wno from wp);
Ⅲ oracle的编程代码
createprocerepro(pidinvarchar2)
is
cursorcur(param_idvarchar2)
is
selectid,name,sex,agefromstudentwhereid=param_id;
cur_recordcur%rowtype;
begin
forcur_recordincur(pid)loop
dbms_output.putline('id:'||cur_record.id||'name:'||cur_record.name||'sex:'||cur_record.sex||'age:'||cur_record.age);
endloop;
endpro;
这样写试一下，看看是否好用。