Shulou(Shulou.com)05/31 Report--

This article will explain in detail what are the sql test questions in oracle. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

To be verified 1. Create a student information table student_info that contains the following information: student number, name, gender, birthday, home address, contact number; create a student report form student_score including student number, subject, grade. 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 (4jue 1)); 2. According to the two tables in the topic, create a new table test, which contains the following information: student number, name, subject, grade CREATE TABLE TEST AS SELECT a.nameline B. score FROM student_info a study grade score b. 3, according to the character query conversion results, the rule is:'A'to 'male','B'to 'female', other characters converted to 'unknown', try a SQL statement to write out. INSERT INTO student_info VALUES (asdg',333); SELECT DECODE (sex,'A',' male', 'female', 'other') FROM student_info 4. in the table of extracting topic 1, the scores of student Zhang San need the following information: student number, name, subject, score, and sort by descending order of score. In the table of topic 1, the students are ranked from high to low according to their total scores. If their scores are the same, the output fields are: rank, student number, name and total score. The SELECT dense_rank () OVER (ORDER BY sum (b.score) DESC) dense_rank,a.id,a.name,sum (b.score) total FROM student_info a study entourage score b WHERE a.id=b.id GROUP BY a.namememe a.id ① ROW_NUMBER:Row_number function returns a unique value. When the same data is encountered, the ranking increases in turn according to the order of the records in the recordset. The ② DENSE_RANK:Dense_rank function returns a unique value, unless the ranking of all the same data is the same when the same data is encountered. The ③ RANK:Rank function returns a unique value, except when the same data is encountered, when the ranking of all the same data is the same, and the ranking is vacated between the last same record and the next different record. Http://www.cnblogs.com/wuyisky/archive/2010/02/24/oracle_rank.html6. In topic 1, it is assumed that the discipline includes' Chinese', 'English', 'mathematics' and 'geography' Please output the students' grades, student numbers, names, Chinese, mathematics and geography total scores xxx xxx 70 70 70 280SELECT A.ID, A.NAME, SUM (DECODE (B.SUBJECT, 'Chinese', SCORE, NULL)) AS Chinese in the following format-- there are no single quotation marks SUM (DECODE (B.SUBJECT, 'mathematics', SCORE, NULL)) AS mathematics, SUM (DECODE (B.SUBJECT, English) SCORE, NULL) AS English, SUM (DECODE (B.SUBJECT, 'Geography, SCORE, NULL)) AS Geography, 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, topic 1 table, find out the average scores of each discipline. It is required to sort in descending order SELECT b. Average FROM student_info (b.score). What is the difference between the following two ends of the code 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 Data that meets these two criteria will be summed up to 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 The data that meets the above conditions are collected, from which 9 that meet the following data are taken out. There is a table TEST and only one field COL, with a total of four records, namely, a meme, bpenery, cPerry d, corresponding to four teams, and now four teams are playing games, using a sql statement to show all possible match combinations. Select a.col.b.col from TEST a, TEST b where a.col2 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) Summary of frequently asked interview questions: 1. How to use temporary tables in stored procedures? 2. Dictate the writing of the stored procedure. 3. Do you know anything about oracle table partitioning? What are its advantages and disadvantages? 4. What are the partition types of oracle table partitions? Do you know how they operate? 5. Tell me about your understanding of the implementation plan. Which part of the implementation plan do you mainly look at? 6. What do you think of sql sentence optimization? can you name several optimization methods? 7. Do you know the table join method handled internally by the oracle optimizer? What kinds do you have? 8. Can you use the windowing function? Tell me about your understanding of windowing functions, aggregate functions, and analytical functions. 9. What are the properties of cursors? What is the use of explicit cursors and implicit cursors, respectively? 10. How does a plsql block catch an exception? Can you name a few common predefined exceptions? 11. Tell me how custom exceptions are implemented in plsql blocks. 12. Do you know anything about views? What are the advantages and disadvantages of views? 13. If you were asked to develop a report with a small amount of data, how would you achieve it? Let the data of the report be displayed? 14. Has hash join heard of it? (after the answer has been understood, continue to ask how many other ways to connect do you know? Tell me about it) (this is actually similar to 7, hash connection this thing I was asked for 4 interviews 3 times, impressed) 15, did you have access to the database at school? Understand the architecture of the database? Do you know what SGA and PGA are respectively? Have you ever tried to write a trigger? Can you give me a general description of the trigger? What's the difference between a function and a stored procedure? Do you know anything about functions? Name a few commonly used functions 20, (after saying SUM, COUNT, AVG, etc.) do you know instr and substr? What is their role? 22. (the interviewer takes out a book) write the basic structure of the decode function (decode (valus,if1,then1,if2,then2). ) what do 23, OLAP and OLTP refer to, respectively? Talk about your understanding of the index. 25. What's the difference between delete and truncate? 26. What are the instructions for authorization and revocation respectively? 27. What's the difference between rowid and rownum? 28. What is the paging sql statement part of the paging stored procedure? Can you just select * from table_name where rownum > = 6 and then rownum?

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.