数据库面试题 2

http://blog.csdn.net/qiyuexuelang/article/details/10073607

Question 1:

---------------------------------------------------------------------------------------
写一个函数 把传入的数组倒置 可以用任何编程语言
不能用现有函数，除了coun或者size之类的基本操作

Question 2:
---------------------------------------------------------------------------------------
In database XYZ, there's a table 'run_key' with the following rows:

id batch last_processed
------------------------------
1  5           999
2 6           999
3  7             0
4  5             0
5  6           999
6  7             0
7  5             0
8  6             0
9 7           999
10 7             0


Write a SQL statement to return the number of rows with last_processed=0 for each batch.
The expected output is:

batch count
-------------
5            2
6            1
7            3

Question 3:
---------------------------------------------------------------------------------------
In table 'info', there's a column named 'graduation_date' of type date. Some rows have 
incorrect graduation_date such as '1907-11-09' and '1908-03-17'; they should be 
'2007-11-09' and '2008-03-17'. 
Write a SQL statement to update all the rows with graduation_date < '1909-01-01' by 
setting graduation_date to '20yy-mm-dd'.
For example, '1907-11-09' updated to '2007-11-09', and '1908-03-17' updated to '2008-03-17'.

Question 4:
---------------------------------------------------------------------------------------
in case you are not familiar with regular expression matching, here's a quick example:

if we have a string "<year>2009</year>" and we want to extract '2009', we can use
/<year>(.*)<\/year>/.

based on the example, write ONE regular expression that will match "BALTIMORE COUNTY" in 
"<p><strong>BALTIMORE COUNTY</strong></p>" AND "NEW YORK"
in "<p><strong>NEW YORK</strong></p>"

Question 5:
---------------------------------------------------------------------------------------
in case you are not familiar with regular expression substitution, here's a quick tutorial:

the syntax of string substitution is:
VARIABLE =~ s/SEARCH_PATTERN/NEW_STRING/

For example,
$a = 'abc,123';
$a =~ s/(\w+),(\w+)/\2,\1/; # $a is now '123,abc' because \1='abc' and \2='123'

Here's the question:
write ONE substitution statement(ie. s/SEARCH_PATTERN/NEW_STRING/) so that
"<date>1999-02-25</date>" will be updated to "<date>02-25-1999</date>" AND

"<date>2005-11-03</date>" will be updated to "<date>11-03-2005</date>"

Question 6:
---------------------------------------------------------------------------------------
learn the concept of 'hash table' first. then solve this:

array 1 has some integers (for example: 1, 3, 5, 7) and array 2 has some integers (for example: 8, 5, 6, 1).

write a function to find the integers that exist in both arrays.

Question 7:
---------------------------------------------------------------------------------------
mysql> select * from a;
+--------+
| letter |
+--------+
| x      |
| y      |
| z      |
+--------+
mysql> select * from b;
+--------+
| letter |
+--------+
| a      |
| b      |
| y      |
+--------+

write a query to return letters that exist in both table a and table b;
write a query to return letters that exist in table a but not in table b.

Question 8:
---------------------------------------------------------------------------------------
写一个函数 传入一个数组和N 要求把前N个元素移到最后 需要占用最少内存

比如传入[a b c d e], N=2, 要求返回数组[c d e a b]

答案：（稍后）

answer1:

下面是我用java语言写的一维数组和二维数组的转置程序。
一维数组的转置：

[java] view plain copy

1. package arraytest;  
2. public class ArrayInversion1 {  
3. public static void main(String[] args) {  
4. int[] a={2,3,4,5};  
5. arrayInversion(a);  
6. for(int i:a){  
7. System.out.println(i);  
8. }  
9. }  
10. //一位数组倒置  
11. public static void arrayInversion(int[] a){  
12. int temp;  
13. for(int i =0;i<=(a.length)/2;i++){  
14. temp = a[i];  
15. a[i]=a[a.length-i-1];  
16. a[a.length-i-1] =temp;  
17. }  
18. }  
19. }  

二维数组的转置：

[java] view plain copy

1. package arraytest;  
2. public class ArrayInversion2 {   
3.     public static void main(String[] args) {    
4.      //二维数组可以不规则  
5.      
6.         int a[][]={{1,2,3},{4,5,6},{7,8}};  
7.           
8.         show(a);  
9.        
10.         int[][] rea =arrayInversion(a);  
11.             
12.         show(rea);  
13.             
14.     }    
15.       
16.       
17.     //倒置方法  
18.      public static int[][] arrayInversion(int[][] a){  
19.      //创建临时变量count，记录数组中长度最大的数组length  
20.         int count =0;  
21.             
22.         for(int i = 0;i  
23.          
24.         if(count  
25.         count = a[i].length;  
26.         }  
27.         }  
28.           
29.        // System.out.println("max"+count);  
30.           
31.         //创建数组用来装载倒置的数组数据  
32.         int rea[][]=new int[count][a.length];  
33.          
34.         //倒置  
35.         for(int i=0 ; i  
36.             for(int j=0; j  
37.                 rea[j][i]=a[i][j];     
38.             }       
39.         }    
40.      
41.     return rea;  
42.     }  
43.       
44.        
45.      //输出数组  
46.     public static void show(int[][] a){  
47.         for (int x[]:a){    
48.             for(int e:x){    
49.                 System.out.print(e+" ");    
50.             }    
51.             System.out.println();    
52.         }    
53.         System.out.println();    
54.     }  
55.     
56. }    

answer2：

[sql] view plain copy

1. select batch,count(batch) as count from run_key where last_processed=0 group by batch;  

answer5：

[javascript] view plain copy

1. $a = '1999-02-25';  
2. $a =~ s/(\w+)-(\w+)-(\w+)/\2-\3-\1/;  

answer6: 对其封装即可 

[java] view plain copy

1.     int a[] ={1,3,5,7,9};  
2.     int b[]={2,3,5,4,6,7,8,9};  
3. Hashtable<String, Integer> numbers= new Hashtable<String, Integer>();  
4. for(int x:a){  
5.     numbers.put(""+x,1);          
6. }  
7. for(int y:b){  
8.    if(numbers.get(""+y)!=null&&numbers.get(""+y)==1){  
9.        System.out.println(y+",");  
10.        numbers.put(""+y, 2);  
11.    }      
12. }  

answer8:

题目相当于循环左移n位。移位问题可以转换为两次反转交换问题。
如：传入[a b c d e], N=2, 要求返回数组[c d e a b]

第一步： 把ab反转交换为ba     把cde反转交换为edc   字符串变为baedc

第二步：  把整个字符反转交换  变为：cdeab。
空间复杂度为O（1）
时间复杂度为O（n）

代码如下：

[cpp] view plain copy

1. void swap(int A[],int n,int k)  
2. {  
3.   for(int i=0,j=k-1;i<j;i++,j--)  
4.   {  
5.       int temp=A[i];  
6.       A[i]=A[j];  
7.       A[j]=A[i];  
8.    }  
9.    for(int i=k,j=n-1;i<j;i++,j--)  
10.   {  
11.       int temp=A[i];  
12.       A[i]=A[j];  
13.       A[j]=A[i];  
14.    }  
15.     for(int i=0,j=n-1;i<j;i++,j--)  
16.   {  
17.       int temp=A[i];  
18.       A[i]=A[j];  
19.       A[j]=A[i];  
20.    }