oracle sql 試題解答筆記

標籤：sql 試題

待驗證1.建立一張學生資訊表student_info，包含以下資訊：學號、姓名、性別、生日、家庭住址、聯絡電話；建立一張學產生績表student_score 包含學號、學科、成績。CREATE TABLE student_info(ID INT,NAME VARCHAR2(10),sex VARCHAR2(4),brithday DATE,address VARCHAR2(50),phone INT);CREATE TABLE student_score(ID INT,subject VARCHAR2(20),score NUMBER(4,1));2、根據題目中的兩張表，建立一張新表test，包含以下資訊：學號，姓名，學科，成績CREATE TABLE TEST AS SELECT a.id,a.name,b.subject,b.score FROM student_info a,student_score b;3、將根據字元查詢轉換後結果，規則為：‘A‘轉換為‘男‘，‘B‘轉換為‘女‘，其他字元轉換為‘未知’，試用一個SQL語句寫出。INSERT INTO student_info VALUES (1100,‘aaa‘,‘A‘,to_date(‘1990/3/3 12:12:12‘,‘yyyy/mm/dd hh24:mi:ss‘),‘asdg‘,333);SELECT DECODE(sex,‘A‘,‘男‘，‘B‘,‘女‘,‘其他‘) FROM student_info;4、提取題目1表中，學生張三的各科成績，需要以下資訊：學號、姓名、學科、成績，且按成績的降序排序。SELECT a.id,a.name,b.subject,b.score FROM student_info a,student_score b WHERE a.id=b.id AND a.name=‘張三‘ ORDER BY b.score DESC;5、題目1表中，按學生總成績由高到低排名，如果成績相同則排名一樣，輸出欄位為：排名、學號、姓名、總成績。SELECT dense_rank() OVER(ORDER BY sum(b.score) DESC) dense_rank,a.id,a.name,sum(b.score) total FROM student_info a,student_score b WHERE a.id=b.id GROUP BY a.name,a.id①ROW_NUMBER：Row_number函數返回一個唯一的值，當碰到相同資料時，排名按照記錄集中記錄的順序依次遞增。 ②DENSE_RANK：Dense_rank函數返回一個唯一的值，除非當碰到相同資料時，此時所有相同資料的排名都是一樣的。 ③RANK：Rank函數返回一個唯一的值，除非遇到相同的資料時，此時所有相同資料的排名是一樣的，同時會在最後一條相同記錄和下一條不同記錄的排名之間空出排名。http://www.cnblogs.com/wuyisky/archive/2010/02/24/oracle_rank.html6.題目1中，假設學科包含‘語文‘，‘英語‘，‘數學‘，‘地理‘，請按以下格式輸出學生的成績學號 姓名 語文 英語 數學 地理 總成績xxx xxx 70 70 70 70 280SELECT A.ID,       A.NAME,       SUM(DECODE(B.SUBJECT, ‘語文‘, SCORE, NULL)) AS 語文, --這裡沒有單引號       SUM(DECODE(B.SUBJECT, ‘數學‘, SCORE, NULL)) AS 數學,       SUM(DECODE(B.SUBJECT, ‘英語‘, SCORE, NULL)) AS 英語,       SUM(DECODE(B.SUBJECT, ‘地理‘, SCORE, NULL)) AS 地理,       SUM(B.SCORE) TOTAL  FROM STUDENT_INFO A, STUDENT_SCORE B WHERE A.ID = B.ID GROUP BY A.NAME, A.ID;http://blog.163.com/magicc_love/blog/static/185853662201371481247696/7、題目1表中，求出各個學科的平均成績，要求降序排序SELECT b.subject,AVG(b.score) average FROM student_info a,student_score b WHERE a.id=b.id GROUP BY b.subject ORDER BY average DESC8、下面兩端代碼有什麼區別select CUST.*,POS.bus_descfrom dw_ods.s00_sema_scmcustp CUSTleft join dw_pdm.t99_pos_code POSon CUST.cust_pos_code=POS.pos_codeand CUST.dw_etl_date=‘2013-07-31‘::date;符合這兩個條件的資料會被匯總到起來select CUST.*,POS.bus_descfrom dw_ods.s00_sema_scmcustp CUSTleft join dw_pdm.t99_pos_code POSon CUST.cust_pos_code=POS.pos_codewhere CUST.dw_etl_date=‘2013-07-31‘::date;符合上麵條件的資料被匯總起來，從其中取出符合下面資料的9、有一張表TEST，只有一個欄位COL，一共有4條記錄，分別是a，b，c，d，對應四個球隊，現在四個球隊進行比賽，用一條sql語句顯示所有可能的比賽組合。select a.col,b.col from TEST a ,TEST b where a.col<b.col;10、有表dm_acrm.test01,資料結構為班級（class）、學號（id_number）、數學成績（math）、英語成績（English）表內資料如下： （備忘：右下角打浮水印處被遮擋的是C02）請匯總各班級的人數、數學平均成績、英語平均成績，並計算所有學生的人數、數學平均成績、英語平均成績（建議使用rollup）。SELECT COUNT(id_number),AVG(match),AVG(english) FROM dm_acrm.test01 GROUP BY rollup(CLASS);http://blog.itpub.net/519536/viewspace-610995/11、有一張表TEST,有欄位客戶ID（cust_id）和交易日期（txn_dte），表結構如下Create test(Cust_id numberic,Txn_dte date)distributed by(cust_id);假設表中含有的資料量有千萬級以上（無法通過表自關聯），請產生一張表，裡面存放客戶ID、交易日期以及上一次交易日期。/*distributed by關鍵字查到是GP資料庫相關的，以後遇到再說*/某公司的資料開發初級工程師筆試題：一、SQL語句已知3張表的關係模式：S (SNO,SNAME） 學生關係。SNO 為學號，SNAME 為姓名C (CNO,CNAME,CTEACHER) 課程關係。CNO 為課程號，CNAME 為課程名，CTEACHER 為任課教師SC(SNO,CNO,SCGRADE) 選課關係。SCGRADE 為成績要求實現如下5個處理：1． 找出沒有選修過“李明”老師講授課程的所有學生姓名2． 列出有二門以上（含兩門）不及格課程的學生姓名及其平均成績3． 列出既學過“1”號課程，又學過“2”號課程的所有學生姓名4． 列出“1”號課成績比“2”號同學該門課成績高的所有學生的學號5． 列出“1”號課成績比“2”號課成績高的所有學生的學號及其“1”號課和“2”號課的成績1. SELECT SNAME  FROM S WHERE SNAME != (SELECT A.SNAME                  FROM S A, C B, SC C                 WHERE A.SNO = C.SNO                   AND B.CNO = C.CNO                   AND B.CTEACHER = ‘李明‘);2. SELECT A.SNAME, AVG(C.SCGRADE)  FROM S A, C B, SC C WHERE A.SNO = C.SNO   AND B.CNO = C.CNO   AND (SELECT COUNT(C.CNO)          FROM S A, C B, SC C         WHERE A.SNO = C.SNO           AND B.CNO = C.CNO           AND C.SCGRADE < 60         GROUP BY A.SNAME) >= 2 GROUP BY A.SNAME3. SELECT A.SNAME  FROM S A, C B, SC C WHERE A.SNO = C.SNO   AND B.CNO = C.CNO   AND (SELECT B.CNO          FROM S A, C B, SC C         WHERE A.SNO = C.SNO           AND B.CNO = C.CNO) = 1   AND (SELECT B.CNO          FROM S A, C B, SC C         WHERE A.SNO = C.SNO           AND B.CNO = C.CNO) = 2;4. SELECT A.SNO  FROM S A, C B, SC C WHERE A.SNO = C.SNO   AND B.CNO = C.CNO   AND (SELECT C.SCGRADE FROM SC) >       (SELECT C.SCGRADE          FROM S A, C B, SC C         WHERE A.SNO = C.SNO           AND B.CNO = C.CNO           AND A.SNO = 2           AND B.CNO = 1)5.  SELECT A.SNO, C.SCGRADE          FROM S A, C B, SC C         WHERE A.SNO = C.SNO           AND B.CNO = C.CNO           AND A.SNO =               (SELECT A.SNO                  FROM S A, C B, SC C                 WHERE A.SNO = C.SNO                   AND B.CNO = C.CNO                   AND (SELECT C.SCGRADE FROM SC WHERE CNO = 1) >                       (SELECT C.SCGRADE FROM SC WHERE CNO = 2))           AND C.CNO IN (1, 2);常問的面試題匯總：1、怎麼在預存程序中使用暫存資料表？2、口述一下預存程序的寫法3、你瞭解oracle表分區嗎？它有什麼優缺點4、oracle表分區的分區類型有哪幾種？它們的操作方法你知道嗎？5、談談你對執行計畫的理解，你主要看執行計畫的哪部分？6、你對sql語句最佳化有何看法，能說出幾種最佳化方法嗎？7、oracle最佳化器內部處理的表串連方式知道嗎？有哪幾種？8、會使用開窗函數嗎？說一下你對開窗函數、彙總函式、分析函數的理解9、遊標的屬性有哪幾種？顯式遊標和隱式遊標的使用方式分別是什嗎？10、plsql塊怎麼捕捉到異常？你能說出幾個常見的預定義異常嗎?11、說一下自訂異常如何在plsql塊中實現？12、你瞭解視圖嗎？視圖的優缺點是什嗎？13、假如讓你開發一張報表，資料量比較小，你會怎樣去實現它？讓報表的資料展現出來？14、hash join聽說過嗎？（回答 瞭解過 後，繼續追問 還有幾種串連方式你知道嗎？說一下看看） （這個其實跟7是差不多的，雜湊串連這個東西我面試4次被問了3次，印象深刻）15、你在學校裡有沒有接觸過資料庫？瞭解資料庫的體繫結構嗎？16、你知道SGA和PGA分別是什麼嗎？17、有沒有試著寫過觸發器？觸發器的寫法能大致描述一下嗎？18、函數和預存程序有什麼區別？19、對函數瞭解嗎？說出幾個常用的函數20、（說出SUM、COUNT、AVG等函數後）知道instr和substr嗎？他們的作用是什嗎？22、（面試官拿出個本子）寫一下decode函數的基本結構（decode（valus，if1，then1，if2，then2……））23、OLAP和OLTP分別指什嗎？24、談談你對索引的理解25、delete和truncate有什麼區別26、授權和撤銷許可權用的指令分別是？27、rowid和rownum有什麼不同？28、分頁預存程序裡的分頁sql語句部分是怎麼寫的？能不能直接先select * from table_name where rownum>=6 然後再rownum<=10。（回答不能之後被追問 為什嗎？）29、oracle作業中用到的包是？（追問 運行作業、刪除作業用到的包分別是？）30、你的oracle的發展前景怎麼看？31、如果有一天甲骨文公司倒閉了，你會做出什麼選擇？32、你知道oracle的最高認證是什麼嗎？（回答：OCM,追問：你想到達這種高度嗎？回答：想，追問：但是你知道通過OCM的人中國有多少嗎？這樣的高度是進階DBA的高度，有些人終其一生都達不到，在你被現實逼迫的時候，你會選擇放棄嗎？PS：這個考官當時很犀利，表情很嚴肅，內心很激動，一層一層追問下去，問得我都想哭了，小小應屆生，為了夢想而出來，容易麼。。。）

oracle sql 試題解答筆記