数据库开发面试题.doc

SQL exam(Please wirte your answer in-line in red font)1. You need to extract details of those products in the SALES table where the PROD_ID column contains the string _D123. Which WHERE clause could be used in the SELECT statement to get the required output?A. WHERE prod_id LIKE %_D123% ESCAPE _B. WHERE prod_id LIKE %_D123% ESCAPE C. WHERE prod_id LIKE %_D123% ESCAPE %_D. WHERE prod_id LIKE %_D123% ESCAPE _2. Evaluate the following two queries: SQL SELECT cust_last_name, cust_city FROM customers WHERE cust_credit_limit IN (1000, 2000, 3000); SQL SELECT cust_last_name, cust_city FROM customers WHERE cust_credit_limit = 1000 OR cust_credit_limit = 2000 OR cust_credit_limit = 3000; Which statement is true regarding the above two queries?A. Performance would improve in query 2.B. Performance would degrade in query 2.C. There would be no change in performance.D. Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column.3. Which three statements/commands would cause a transaction to end? (Choose three.)A. COMMITB. SELECTC. CREATED. ROLLBACKE. SAVEPOINT4. There are two table: T_A (name VARCHAR2(100)T_B (name VARCHAR2(100), name_a VARCHAR2(100);The values are:T_AT_BnameJaneLeoDavidnamename_aAppleJaneOrangeJaneAppleLeoOrangeLeoBananaJaneBananaDavid(1) Please write a SQL to output below result:Name Apple Orange Banana- - - -Jane Yes Yes YesLeo Yes Yes NoDavid No No YesSelect name, case when Ap =1 then Yes else No end Apple,case when Og =1 then Yes else No end Orange,case when Bn =1 then Yes else No end Bananafrom (Select name, sum(Ap) Ap , sum(Og) Og, sum(Bn) Bn(select name_a ,Decode( T_B.name, Apple , 1 ,0 ) Ap ,Decode( T_B.name, Orange , 1 ,0 ) Og,Decode( T_B.name, Banana , 1 ,0 ) Bn,from T_B) T group by name ) T2;(2) Please write a SQL to output below result:(calculate the number of owner for each row in T_B, the number of returned row should be the same as T_B)Name Owner_Count- -Apple 2Orange 2Banana 2 Apple 2Orange 2Banana 2Select name Name, count(1) over (partition by name) Owner_CountFrom T_B;(3) Please write a SQL to output below result:(Find rows between 2 and 4, using ROWNUM)Name Name_a- -Orange JaneBanana JaneApple Leo Select name Name, Name_aFrom T_BWhere ROWNUM between 2 and 4;(4) Please write a Function:This function has a parameter which has the same data type as name in T_A and return the second fruit name in T_B. if one person has no the second fruit, the function return the first fruit.Example: pass Jane as parameter, return Orange; pass David, return Banana.(5) Please add a Trigger on table T_B:Assuming there is a new row in T_A, name is Jacob, If inserting a row to T_B with name_a=Jacob, please change the name in T_B to Apple regardless of its original value.(We may insert many rows one time)Example: SQL INSERT INTO T_B (name, name_a) VALUES (Banana, Jacob );SQL COMMIT;SQL SELECT * FROM T_B WHERE name_a=Jacob;Name Name_a- -Apple Jacob5. Table : tab (col INT). The values are:colnull12QWhat will be result of the following query:select col from tab where col in (null,1)A only 1.6. Table : T1 (id INT, name VARCHAR(100). The values are:idname1ABC2PQR3XYZ4XYZ5ABCQWhat will be result of the following query : select * from T1 where rownum = 3A XYZQWrite a query to delete duplicate records in T1 based on column name.ADelete from T1 where rowid in (Select max(rowid),name from T1Group by name);having count(name) 1)QWrite a query to return first 3 records(sort by column name) of T1.A select * from (select * from T1 order by name) where rownum select * from emp where ename = JOHN;sql2select * from emp where ename = KATE;Qwhile running second sql, will it reuse the execution plan generate by first sql?ANO.It wil not use the explain plan of the first one9. What are the steps included in the compilation process of a pl/sql blocksyntax checking ,binding, p-code generation10. Can we create two procedures with same name but different parameters?11. To insert a lot of data into existing table, how to speed up the operation? Please consider as many situations an you can.Way1: drop the index, disable the constraintWay2: use hint /*+append nologging*/Way3: the insert statement must not have performance issue12. If you want to update a column value for all rows of a very big table(e.g. UPDATE t SET code = code|-EX), how to speed up the operation? Please consider as many situations an you can.Way1: Partition the tab