Ⅰ 關於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;
這樣寫試一下，看看是否好用。