Ⅰ ORACLE sql语句 拆分字符串 并换行(在ORACLE数据库中完成)
SELECT
stu.id,
REGEXP_SUBSTR(stu.name,'[^,]+',1,lv)stuname
FROM
stu,
(
SELECT
LEVELlv
FROM
alCONNECTBYLEVEL<10
)b
WHERE
b.lv<=REGEXP_COUNT(stu.name,',')+1
ORDERBY
stu.id,stuname;
如果字段子项多，调整level<10为更大值即可，应该满足你的需求
Ⅱ 关于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语句
1\SELECT TO_CHAR(REGISTER, 'YYYYMM') AS MONTH, COUNT(*) FROM A GROUP BY TO_CHAR(REGISTER, 'YYYYMM');
2\SELECT COUNT(*) FROM (SELECT NAME FROM A GROUP BY NAME HAVING COUNT(*) > 1);
3\INSERT INTO C
SELECT A.ID, A.NAME
FROM A
INNER JOIN (SELECT NAME FROM A GROUP BY NAME HAVING COUNT(*) > 1) B ON A.NAME = B.NAME;
4\SELECT MAX(REGISTER), ID, NAME
FROM A
GROUP BY ID, NAME;
Ⅳ oracle sql 语句 面试题
(1)统计有学生选修的课程门数
select count(distinct c#) from SC
2)求选修C4课程的女学生的平均年龄
select avg(s.age) --最好都带上前缀，养成好习惯
from s,c,sc where s.s#=sc.s# and c.c#=sc.c#
and c.cname='C4' and s.sex='女'--字符类型带引号，必须注意大小写，你那么写好麻烦
3)求刘老师所授的课程的每门课程的平均成绩
select c.cname , avg(grade) from sc , c
where c.teacher =' liu' and sc.c# = c.c#
group by c.cname --select后是什么字段，这地方你也得最少有这个字段
(4)统计每门课程的学生选修人数(超过10人的课程才统计)。要求显示课程号和人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。
select t.*
from
(select sc.c#, count(s#) counnt_s from s,sc where s.s# = sc.s# group by sc.c# having count(s#) >10) t
order by counnt_s desc,c# asc --你排序不对，另外oracle不可根据别名排序，只可再做嵌套
5)检索学号比王军同学大，而年龄比他小的学生姓名
select a.s#
from
(select s# from s where s#>(select s# from s where sname='王军') a,
select s# from s where age>(select age from s where sname='王军') b
where a.s#=b.s#
6)求年龄大于女同学平均年龄的男学生的姓名和年龄
select sname,age from s
where age>
(select avg(age) from s where sex = 'nv') and sex = 'nan' --没问题
7)求年龄大于所有女同学年龄的男学生的姓名和年龄
select sname ,age from s
where age>(select max(age) from s where sex = 'nv') and sex = 'nan' --没问题
Ⅳ 请帮忙解决一道Oracle数据库试题：
用管道函数：
create or replace type rec_list is table of number;
CREATE OR REPLACE FUNCTION pipe_rec (pmax NUMBER)
RETURN rec_list PIPELINED
IS
BEGIN
FOR i IN 1 .. pmax
LOOP
PIPE ROW (i);
END LOOP;
RETURN;
END;
/
insert into a
select a.*,1 from table(pipe_rec(10000000)) b
Ⅵ SQL题：怎么把ORACLE数据库中的数据列统计转化成行显示
--将分组统计存入临时表
select substring([DATE],5,2),sum([IN]) into #tmp from 表 where substring([DATE],1,4)='1900' group by substring([DATE],5,2)
--生成行列转换的SQL语句
declare @sql varchar(1024)
set @sql = 'select '
select @sql = @sql + 'sum(case [DATE] when '''+[DATE]+''' then [IN] end) as '+[DATE] from (select distinct [DATE] from #tmp) as a
set @sql = @sql+' from #tmp'
--执行语句并清理临时表
exec(@sql)
drop table #tmp
Ⅶ 一道SQL题目(Oracle)
select decode(t.grade_id,'1','一年级','2','二年级','3','三年级',4,'四年级',5,'五年级','未知年级') 年级,nvl(t.max_score,'0') 语文,nvl(tt.max_score,'0') 数学,nvl(ttt.max_score,'0') 政治 from (
select grade_id,subject_name,max_score from test where subject_name='语文'
) t
left join (
select grade_id,subject_name,max_score from test where subject_name='数学'
)tt on t.grade_id=tt.grade_id
left join (
select grade_id,subject_name,max_score from test where subject_name='政治'
)ttt on t.grade_id=ttt.grade_id
order by t.grade_id
Ⅷ ORACLE选择题
11) b) IS NULL
12） b) CREATE PUBLIC SYNONYM cust FOR mary.customer
13) c) PL/SQL
14) c) test(‘string',v-numl, v-str2)
15) c) set serverout on
我考过ORACLE的铜牌。有些问题我也帮你在网上查了一下。
Ⅸ oracle数据库SQL
1、写出创建此数据库表的sql语句（student,course,score三个表任意写一个创建语句）
CREATE TABLE STUDENT
(
stu_id VARCHAR2(20),
name VARCHAR2(20),
Age INTEGER,
sex CHAR(2),
stu_class VARCHAR2(12)
)
2、给表course的课程号（co_id）字段添加主键约束，给成绩表score的课程号（co_id）字段添加外键约束，其中主表是course，从表示score，写出sql语句
ALTER TABLE COURSE ADD CONSTRAINT COURSE_P_COID PRIMARY KEY (CO_ID);
ALTER TABLE SCORE ADD CONSTRAINT SCORE_F_COID FOREIGN KEY(CO_ID) REFERENCES COURSE(CO_ID) ;
3、向score表中插入一条记录，写出此sql语句
INSERT INTO SCORE VALUES ('1111000',1,90);
4、查询课程成绩大于80分的学生姓名和课程名，写出sql语句
SELECT B.NAME,C.CO_NAME FROM SCORE A,STUDENT B,COURSE C
WHERE A.STU_ID=B.STU_ID
AND A.CO_ID=C.CO_ID
AND A.SCORE>80
5、编写函数返回女同学的平均课程成绩是多少（女同学：sex=’ 女），写出sql语句
CREATE OR REPLACE FUNCTION FUN_GETSCORE
RETURN NUMBER
IS
V_SCORE NUMBER;
BEGIN
SELECT AVG(A.SCORE) INTO V_SCORE FROM SCORE A,STUDENT B
WHERE A.STU_ID=B.STU_ID
AND B.SEX='女';
RETURN V_SCORE;
END;
6、编写存储过程，更新学生成绩表score的课程成绩(score)字段,更新语句为：update score set score=newscore where stu_id=stuid and co_id=coid其中新的成绩，学号，课程号作为传人的形式参数，定义为：newscore in number,stuid in varchar2,coid in varchar2，写出此sql语句
CREATE OR REPLACE PROCEDURE P_UPDATE_SCORE(newscore in number,stuid in varchar2,coid in varchar2)
AS
BEGIN
update score set score=newscore where stu_id=stuid and co_id=coid;
COMMIT;
END;