Union order by

select .... union select ... order by后面可以跟什么

只可以跟两样东西

1. 数字N，1<=N<=列数
2. union左边的select clause里的字段，如果有别名，必须用别名，不可以再用原来的字段名。不可以用字符串。

一个例子说明第二点

select employee_id id, first_name, 'c', 'c' ca from employees
union
select employee_id, first_name f_name, 'c', 'c' from employees
order by ?

order by 3 可以
order by employee_id 不可以，因为这个字段有了别名，本来的字段名就不能用了
order by id 可以
order by first_name 可以
order by f_name 不可以，union右边select clause的字段根本不用看
order by 'c' 不可以，因为是字符串
order by ca 可以，因为是别名，不是字符串了
order by lower(first_name) 不可以，表达式一律不允许

Modify constraint

SQL expert (1z0-047)的题库里有这样一道题，我觉得挺有意思的。

The ORDERS table contains data and all orders have been assigned a customer ID. Which statement would add a NOT NULL constraint to the existing CUSTOMER_ID column?

A. ALTER TABLE orders ADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
B. ALTER TABLE orders MODIFY customer_id CONSTRAINT orders_cust_id_nn NOT NULL;
C. ALTER TABLE orders MODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
D. ALTER TABLE orders ADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;

粗一看觉得答案是A。D肯定是错的，因为customer_id这个字段已经存在了。觉得BC错是因为我压根就没见过MODIFY CONSTRAINT这种用法。不是只有drop和add constraint吗？

答案却是B。

这是套用了ALTER TABLE table MODIFY column CONSTRAINT constraint inline_constraint的用法。

https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_3001.htm#i2103924

A为什么错了？因为ADD CONSTRAINT的用法是ALTER TABLE table ADD CONSTRAINT constraint out_of_line_constraint。

inline_constraint和out_of_line_constraint有什么区别？

http://docs.oracle.com/cd/E11882_01/server.112/e41084/clauses002.htm#SQLRF52180

主要区别就在于out_of_line_constraint不支持NULL/NOT NULL。其他的什么PRIMARY KEY, UNIQUE, CHECK, FOREIGN KEY都支持，当然格式有点不一样。

关于C，其实MODIFY CONSTRAINT是有的，用法是ALTER TABLE table MODIFY CONSTRAINT constraint_state。什么是constraint_state?

就是INITIALLY IMMEDIATE/DEFERRED, ENABLE/DISABLE, VALIDATE/NOVALIDATE这些东西，所以这个用法套不上的。

Key preserved table

What does mean by key preserved table?

According to its definition: A table is key-preserved if every key of the table can also be a key of the result of the join. So, a key-preserved table has its keys preserved through a join.

Let's illustrate this by an example.

 
create table a (a_id number primary key);
create table b (b_id number primary key, a_id number,
  foreign key (a_id) REFERENCES a(a_id) on delete cascade);

create or replace view c as (
  select a.a_id, b.b_id from a,b where a.a_id = b.a_id
)

insert into a values (1);
insert into b values (2,1);
insert into b values (3,1);

select * from c;

The result is

 
A_ID B_ID
---------
1    2
1    3

Clearly A's primary key A_ID cannot be used as a primary key in the view, so table A is not a key preserved table. On the other hand, table B's primary key B_ID can be used as a primary key in the view, so table B is a key preserved table for this view C.

So what happens when you

 
delete from c;

is all the rows in table B are deleted.

Rules for updating a join view

http://docs.oracle.com/cd/E11882_01/server.112/e25494/views.htm#ADMIN11782

What does EntityManager.clear() do?

It clears L1 cache. 

See an example

 
@Transactional
public void test(){
   Book book = bookDao.findById(1);
   System.out.println("Old Author: "+book.getAuthor());
   bookDao.updateBookAuthor(1, "New author");
   book = bookDao.findById(1);
   System.out.println("New Author: "+book.getAuthor());
}

The output is
Old Author: Old Author
New Author: Old Author

Line 1, JPA loads the Book entity from database and saves it into L1 cache.
Line 3 is a JPQL bulk update statement:

update Book b set b.author = :author where b.id = :id

It changes the entity's author and stores it into L2 cache, while the entity at L1 cache is unchanged.

Line 4, JPA loads the entity from L1 cache and therefore still shows the old author value.

Now check the changes that the clear() method brings.

@Transactional
public void test(){
   Book book = bookDao.findById(1);
   System.out.println("Old Author: "+book.getAuthor());
   bookDao.updateBookAuthor(1, "New author");
   entityManager.clear();
   book = bookDao.findById(1);
   System.out.println("New Author: "+book.getAuthor());
}

The output is
Old Author: Old Author
New Author: New Author

EntityManager.clear() clears the L1 cache so that at line 5, JPA has to load the entity from L2 cache.

So the clear() method doesn't clear any changes made to the entity. Namely, all the changes will still be committed to database. It does nothing more than just clearing the L1 cache. 

Find Nth to last element in a singly linked list

Use recursion

 public class FindNthToLast {  
        
      public static void main(String[] args){  
           LinkedListNode node 
              = LinkedListNode.createLinkedList(new int[]{1,2,3,4,5,6,7,8,9});  
           LinkedListNode.print(node);  
           System.out.println("\n=====================");  
           LinkedListNode found = find(node, 2);  
           System.out.println(found.data);  
      }  
        
      private static int count = 0;  
        
      public static LinkedListNode find(LinkedListNode head, int n){  
           if (head == null){  
                return null;  
           }  
           LinkedListNode found = find(head.next, n);  
           count++;  
           if (count==n){  
                return head;  
           }  
           return found;  
      }  
 }  

 public class LinkedListNode {  
      public int data;  
      public LinkedListNode next;  
        
      public LinkedListNode(int data) {  
           super();  
           this.data = data;  
      }  
        
      public static void print(LinkedListNode head){  
           LinkedListNode current = head;  
           while (current != null){  
                System.out.print(current.data+" ");  
                current = current.next;  
           }  
      }  
        
      public static LinkedListNode createLinkedList(int[] arr){  
           LinkedListNode head = new LinkedListNode(arr[0]);  
           LinkedListNode current = head;  
           for (int i=1; i<arr.length; i++){  
                LinkedListNode temp = new LinkedListNode(arr[i]);  
                current.next = temp;  
                current = temp;  
           }  
           return head;  
      }  
 }  

Coins

Given an infinite number of quarters (25 cents), dimes (10 cents), nickels (5 cents) and pennies (1 cent), write code to calculate the number of ways of representing n cents.

 /**  
  * numberOfWays(25, 3) = numberOfWays(25-1, 0) + numberOfWays(25-5, 1)  
  * + numberOfWays (25-10, 2) + numberOfWays (25-25, 3)  
  *   
  * numberOfWays(25-1, 0) = 1  
  * numberOfWays(25-5, 1) = numberOfWays(20, 1) = numberOfWays(20-1, 0)   
  * + numberOfWays(20-5, 1)   
  * ......   
  * @author zsunm  
  *  
  */  
 public class Coin {  
        
      private static final int[] COINS = {1, 5, 10, 25};  
        
      public static int numberOfWays(int n){  
           if (n == 0) return 0;  
           return numberOfWays(n, 3);  
      }  
        
      /**  
       * The number of ways to represent n cents by using the coins  
       * up to the specified coinIndex of the COINS array  
       * Example 1: numberOfWays(10, 2) means the number of ways  
       * to represent 10 cents using 1 cent, 5 cents, and 10 cents,   
       * because COINTS[2] = 10  
       *   
       * Example 2: numberOfWays(25, 3) means the number of ways  
       * to represent 25 cents using 1 cent, 5 cents, 10 cents, and 25 cents   
       * because COINTS[3] = 25  
       *   
       * @param n cents  
       * @param coinIndex the coin index of COINS array that can be used up to  
       * @return  
       */  
      private static int numberOfWays(int n, int coinIndex) {  
           if (n < 0){  
                return 0;  
           }  
           if (n == 0){  
                return 1;  
           }  
           int result = 0;  
           for (int i=0; i<=coinIndex; i++){  
                result += numberOfWays(n-COINS[i], i);  
           }  
           return result;  
      }  
   
      /**  
       * @param args  
       */  
      public static void main(String[] args) {  
           System.out.println(numberOfWays(25));  
      }  
   
 }  

Solve Eight Queens problem with 1D array

The following program prints out all solutions for N Queens problem. 

There are 92 solutions for 8 Queens.

 The solution uses a 1D array and does not use recursion.

 public class EightQueen {  
   
      private static final int N = 8;  
      //queens[2]=3, means the queen on row 2 is placed on column 3
      private static int[] queens = new int[N];  
      private static int index = 0;  
      private static int counter = 0;  
        
      public static void placeQueens(){  
           int previousColumn = -1;  
           boolean noMoreSolutions = false;  
           while (!noMoreSolutions){  
                while (index < N){  
                     int col = getColumnForNextRow(previousColumn);  
                     if (col != -1){  
                          queens[index++] = col;  
                          previousColumn = -1;  
                     }else if (index > 0){  
                          previousColumn = queens[--index];  
                     }else{  
                          noMoreSolutions = true;  
                          System.out.println("No More Solutions");  
                          break;  
                     }  
                }  
                if (!noMoreSolutions){  
                     print();  
                     System.out.println("Solution #"+ ++counter);  
                     //Let's find another solution  
                     previousColumn = queens[--index];  
                }  
           }  
      }  
        
      private static int getColumnForNextRow(int previousColumn) {  
           int startCol = previousColumn + 1;  
           for (int j=startCol; j < N; j++){  
                if (!canAttackExistingQueens(j)){  
                     return j;  
                }  
           }  
           return -1;  
      }  
        
      private static boolean canAttackExistingQueens(int col) {  
           for (int i=0; i<index; i++){  
                if (canAttackQueen(index, col, i, queens[i])){  
                     return true;  
                }  
           }  
           return false;  
      }  
   
      private static boolean canAttackQueen(int row1, int col1, int row2, int col2) {  
           return sameCol(col1, col2) || sameDiagonal(row1, col1, row2, col2);  
      }  
   
      private static boolean sameCol(int col1, int col2) {  
           return col1 == col2;  
      }  
   
      private static boolean sameDiagonal(int row1, int col1, int row2, int col2) {  
           return col1 - col2 == row1 - row2 || col1 - col2 == row2 - row1;  
      }  
        
      private static void print(){  
           for (int i=0; i<N; i++){  
                for (int j=0; j<N; j++){  
                     if (queens[i] == j){  
                          System.out.print("Q ");  
                     }else{  
                          System.out.print("* ");  
                     }  
                }  
                System.out.println();  
           }       
      }  
   
      /**  
       * @param args  
       */  
      public static void main(String[] args) {  
           placeQueens();  
      }  
 }