oracle考试编程题

CREATE OR REPLACE FUNCTION FUN_姓名拼音(USERID IN NUMBER) RETURN VARCHAR2 IS
  DEPTNAME VARCHAR2;
BEGIN
  SELECT 部门名称 INTO DEPTNAME FROM TABLE WHERE USERID = USERID;
  RETURN DEPTNAME;
END; 

求解几道Oracle程序题

(1)建立一个名字和密码都是dropme的用户，为该用户赋予建立会话和建立表的权限，然后使用该用户连接上来
(2)将存储过程my_proc的执行权限赋予bush用户，然后使用bush用户登录并执行
(3)查询emp表中薪水大于3000的人的信息，其中columnname为绑定的变量
(4)在indextest表的object_name列上建立索引，并通过查询测试该索引
(6)设置并测试只读事务
(7)设置日期格式为"年-月-日"，然后进行查询测试
(8)建立一个触发器，在scott.schema上删除记录时将删除的信息写入dropped_objects
(9)设置一个输出标题
(10)创建一个用户名和密码都是oracle_adin的用户

关于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的SQL语句编写题

A、select abs(sum(case when dept=Java then 1
when dept=C then -1
end)) as Java专业和C 专业相差的人数
from student
where
dept =Java or dept =C ;

B、select name,age from student
where age >(select max(age) from student where dept =Java)

C、
select * from
(
select name, age from student
where age >(select avg(age) from student)
order by age
)
where rownum<=3