Shulou(Shulou.com)06/01 Report--

Preface

This article collates 4 classic Oracle interview questions and shares them with you. This may be the article you've been looking forward to. I won't say much below. Let's take a look at the detailed introduction.

The first question

Create table test (id number (10) primary key, type number (10), t_id number (10), value varchar2 (6)); insert into test values (100); insert into test values (200); insert into test values (300); insert into test values (101); insert into test values (2011); insert into test values (301) Insert into test values; select * from test

Code generation forms such as:

Write a query statement based on the table generated by the above code, and the query result is as follows:

Name, sex, age, Zhang San male 50 Liu 2 male 30 Liu 3 female 10) * according to the table, we can analyze that 1 represents name, 2 represents gender, 3 represents age in type column, while the column in t_id which is the same as id for the same person is name, sex and age in turn, while type column determines name, sex, age * / * grouping t_id first, and then filtering data by decode function Example: decode (type, 1, value) type=1 is displayed as value because the column fields after grouping select can only be grouped fields or group functions, all using max (). The type of the same person does not repeat the value, so decode (type, 1, value) returns only one value, and the maximum value is this value * / select max (decode (type, 1, value)) "name", max (decode (type, 2, value)) "gender", max (decode (type, 3, value)) "age" from test group by t_id / * use concatenated tables Through where filtering, three virtual tables with type equal to 1 (name), 2 (gender) and 3 (age) are generated. For example, through where, the three tables with equal t_id are the same person or the same record (row) * / select t1.value "name", t2.value "gender", t3.value "age" from (select value,t_id from test where type=1) T1, (select value,t_id from test where type=2) T2. (select value,t_id from test where type=3) t3where t1.t_id=t2.t_id and t1.t_id=t3.t_id

Second question

/ * 2. A SQL sentence interview question, about the contents of the group by table: 2005-05-09 win 2005-05-09 win 2005-05-09 negative 2005-05-09 negative 2005-05-10 win 2005-05-10 negative 2005-05-10 if you want to generate the following results, how to write the sql statement? Win or lose 2005-05-09 2 22005-05-101 2--create table tmp (rq varchar2 (10), shengfu varchar2 (5)); insert into tmp values ('2005-05-09)); insert into tmp values (' 2005-05-09)); insert into tmp values ('2005-05-09) Insert into tmp values ('2005-05-09); insert into tmp values (' 2005-05-10); select * from tmp * /-use grouping-grouping by date, using conut function to calculate the number of times select rq "date", count (decode (shengfu, 'win', 1)) "win", count (decode (shengfu, 'negative', 1)) "negative" from tmp group by rq order by rq -- using concatenated tables-- this question itself needs to be grouped, and it is not recommended to use concatenated tables-- the following is SQL1999's concatenated table, and the syntax is different from that of SQL1992 in the first question, which is the same as select t1.rqQuery T1. Win, T2. Negative from (select count (decode (shengfu, 'win', 1)) "win", rq from tmp group by rq) t1join (select count (decode (shengfu, 'negative ", 1))" negative ", rq from tmp group by rq) t2on t1.rq=t2.rq

The third question

/ * 3. Create table STUDENT_SCORE (name VARCHAR2 (20), subject VARCHAR2 (20), score NUMBER (4jue 1)); insert into student_score (NAME, SUBJECT, SCORE) values ('Zhang San', 'Chinese', 78.0); insert into student_score (NAME, SUBJECT, SCORE) values ('Zhang San', 'Mathematics', 88.0); insert into student_score (NAME, SUBJECT, SCORE) values ('Zhang San', 'English', 98.0) Insert into student_score (NAME, SUBJECT, SCORE) values ('Li Si', 'Chinese', 89.0); insert into student_score (NAME, SUBJECT, SCORE) values ('Li Si', 'Mathematics', 76.0); insert into student_score (NAME, SUBJECT, SCORE) values ('Li Si', 'English', 90.0); insert into student_score (NAME, SUBJECT, SCORE) values ('Wang Wu', 'Chinese', 99.0) Insert into student_score (NAME, SUBJECT, SCORE) values ('Wang Wu', 'Mathematics', 66.0); insert into student_score (NAME, SUBJECT, SCORE) values ('Wang Wu', 'English', 91.0); 3.1 get similar results Chinese language Mathematical English Wang Wu 89 56 89 Li Si xx xx xxselect * from STUDENT_SCORE;3.2 has a table with three fields: Chinese, mathematics, English. Among them, three records represent 70 points in Chinese, 80 points in math and 58 points in English. Please use a sql sentence to query these three records and show them according to the following conditions (and write your ideas): greater than or equal to 80 means excellent, greater than or equal to 60 means passing, and less than 60 points means failing. Display format: Chinese, maths, English, pass, excellent, fail-- * /-- 3.1Math-use grouping select name "name", max (decode (subject, 'Chinese', score)) "Chinese", max (subject, 'math') Score) "Mathematics", max (decode (subject, 'English', score)) English from STUDENT_SCORE group by name -- use the conjunction table select t1.name name, t1.score language, t2.score mathematics, t3.score English from (select name,score from STUDENT_SCORE where subject=' language') t1join (select name,score from STUDENT_SCORE where subject=' mathematics') t2on t1.name=t2.namejoin (select name,score from STUDENT_SCORE where subject=' English') t3on t 1. Name, (case when t. Chinese > = 80 then 'excellent' when t. Language > = 60 then 'pass' else 'fail' end) language, (case when t. Mathematics > = 80 then 'excellent' when t. Mathematics > = 60 then 'pass' else 'fail' end) Mathematics, (case when t. 80 then 'excellent' when t. English > = 60 then 'pass' else 'fail' end) English from (select t1.name name, t1.score language, t2.score Mathematics, t3.score English from (select name,score from STUDENT_SCORE where subject=' language') t1join (select name,score from STUDENT_SCORE where subject=' Mathematics') t2on t1.name=t2.namejoin (select name,score from STUDENT_SCORE where subject=' English') t3on t1.name=t3.name) t

Question 4 (this question is relatively difficult)

/ * 4. Please use a sql statement to get the results. Take out the data in the format listed in table3 from table1,table2. Note that the data and results provided are not accurate. Just ask for advice as a format. Table1 month mon department dep performance yj-- January 01 10 January 02 10 January 03 5 February 02 8 February 04 9 March 03 8table2 department dep department name dname-- country Internal Business one domestic Business two domestic Business three International Business table3 (result) Department dep January February March-10 null null 10 8 null null 58 null null 9- -create table yj01 (month varchar2 (10) Deptno number (10), yj number (10) insert into yj01 (month,deptno,yj) values (January, 01) Insert into yj01 (month,deptno,yj) values (February, 02jue 10); insert into yj01 (month,deptno,yj) values (Feb.03); insert into yj01 (month,deptno,yj) values (March8); insert into yj01 (month,deptno,yj) values (March, 04jue 9); insert into yj01 (month,deptno,yj) values (March8) Create table yjdept (deptno number (10), dname varchar2 (20)) insert into yjdept (deptno,dname) values (01jie 'domestic Business Department'); insert into yjdept (deptno,dname) values (02 'domestic Business Department'); insert into yjdept (deptno,dname) values (03 'domestic Business Department'); insert into yjdept (deptno,dname) values (04 'International Business Department'); * / select * from yj01;select * from yjdept -- use grouping select deptno,max (decode (month,' January', yj)) January, max (decode (month,' February', yj)) February, max (month,' March', yj)) March from yj01 group by deptnoorder by deptno This question gives two tables, which can be done by grouping and using the yj01 table, so this question should examine the knowledge of connected tables / * some departments' performance is empty in some months in these two tables, while using the practice of the previous questions, the values of mismatching conditions will be filtered out, for example, only one department in month= in January Only the deptno in the table formed in January, February and March cannot match the deptno in the table formed in March, while the yjdept table contains all the department numbers deptno, then you can use the feature of external join (on the basis of meeting the display of the contents of one table, join another table, if the connection matches, it will display normally, and the connection does not match. Another table complements null) * / select t1.deptno, t1.yj January, t2.yj February, t3.yj March from (select y2.deptnorey1.yj from (select yj, deptno from yj01 where month=' January') y1 right join yjdept y2 on y1.deptno=y2.deptno) t1join (select y2.deptnoLines y1.yj from (select yj, deptno from yj01 where month=' February') y1 right join yjdept y2 on y1.deptno=y2.deptno) t2on t1.deptno=t2.deptnojoin (select y2.deptnoreum y1.yj from (select yj) Deptno from yj01 where month=' March') y1 right join yjdept y2 on y1.deptno=y2.deptno) t3on t1.deptno=t3.deptnoorder by t1.deptno

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support.

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.