Ⅰ 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;