JAVA 前端

1. css 写网页布局 2. JQuery

Spring MVC Architecture

EnergyHub

Spring Java Configuration Based AOP

Spring Java Configuration Based DI

J2EE Phone Interview 6

VERIZON

数据库考前复习大纲

Oracle Database 8种主要对象的考前练习

J2EE Phone Interview 5

Normal Form in Database Design

View Sequence Index and Synonyms

ER Diagram转化为关系映射的步骤

SQL注意细节

1. Cross Join 就是 Cartesian Product
    Natural Join 就是基于相同列名的Left Outer Join Right Outer Join 或者Inner Join

2. Count(列名) 会忽略NULL的情况， 它一定会返回一个0到无穷大之间的数，不会返回NULL.
    COUNT(*) 很特殊，它会记录NULL的情况。

3. JOIN 分为Equi-Join， Non-Equi-Join以及Inner Join 和Outer Join

    On Column Between A and B

4. SELECT TO_CHAR(SUM(ALL(SALARY)), '$9999.99') FROM EMPLOYEE;
    将数字转为字符格式。

5. Median AVG 会忽略NULL的情况。

6. Select Can only include Expressions in the select list that are defined at the same level of detail as each other.

7. When Group by is involved order by can only include group by columns.

8. Nest Aggregate Function can be up to 2 level deep.

9. Select Required
    from Required
    where Optional
    Group by Optional
    Having Optional
    Order By Optional

10. Having only take Group by Clause and Aggregate functions

11. Sub Query will return NULL if not found.

12. Without Group by sub query will return Single Value

13. Find all products with a price that’s greater than all of the products in the ‘Luxury’ category:              
SELECT * FROM PRODUCTS
    WHERE PRICE > ALL (SELECT PRICE FROM PRODUCTS
    WHERE CATEGORY = ‘Luxury’);

14. < > 表示不等于

15. A scalar subquery returns one row with one column.
      Scalar subquery expressions can ONLY be used in the following locations: SELECT AND WHERE

生产者消费者模型队列设计

Trips and Users

The Trips table holds all taxi trips. Each trip has a unique Id, while Client_Id and Driver_Id are both foreign keys to the Users_Id at the Users table. Status is an ENUM type of (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’).

+----+-----------+-----------+---------+--------------------+----------+
| Id | Client_Id | Driver_Id | City_Id |        Status      |Request_at|
+----+-----------+-----------+---------+--------------------+----------+
| 1  |     1     |    10     |    1    |     completed      |2013-10-01|
| 2  |     2     |    11     |    1    | cancelled_by_driver|2013-10-01|
| 3  |     3     |    12     |    6    |     completed      |2013-10-01|
| 4  |     4     |    13     |    6    | cancelled_by_client|2013-10-01|
| 5  |     1     |    10     |    1    |     completed      |2013-10-02|
| 6  |     2     |    11     |    6    |     completed      |2013-10-02|
| 7  |     3     |    12     |    6    |     completed      |2013-10-02|
| 8  |     2     |    12     |    12   |     completed      |2013-10-03|
| 9  |     3     |    10     |    12   |     completed      |2013-10-03| 
| 10 |     4     |    13     |    12   | cancelled_by_driver|2013-10-03|
+----+-----------+-----------+---------+--------------------+----------+

The Users table holds all users. Each user has an unique Users_Id, and Role is an ENUM type of (‘client’, ‘driver’, ‘partner’).

+----------+--------+--------+
| Users_Id | Banned |  Role  |
+----------+--------+--------+
|    1     |   No   | client |
|    2     |   Yes  | client |
|    3     |   No   | client |
|    4     |   No   | client |
|    10    |   No   | driver |
|    11    |   No   | driver |
|    12    |   No   | driver |
|    13    |   No   | driver |
+----------+--------+--------+

Write a SQL query to find the cancellation rate of requests made by unbanned clients between Oct 1, 2013 and Oct 3, 2013. For the above tables, your SQL query should return the following rows with the cancellation rate being rounded to two decimal places.

+------------+-------------------+
|     Day    | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 |       0.33        |
| 2013-10-02 |       0.00        |
| 2013-10-03 |       0.50        |
+------------+-------------------+

Department Top Three Salaries

The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id.

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
+----+-------+--------+--------------+

The Department table holds all departments of the company.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows.

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

Department Highest Salary

The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

The Department table holds all departments of the company.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

Rising Temperature

Given a Weather table, write a SQL query to find all dates' Ids with higher temperature compared to its previous (yesterday's) dates.

+---------+------------+------------------+
| Id(INT) | Date(DATE) | Temperature(INT) |
+---------+------------+------------------+
|       1 | 2015-01-01 |               10 |
|       2 | 2015-01-02 |               25 |
|       3 | 2015-01-03 |               20 |
|       4 | 2015-01-04 |               30 |
+---------+------------+------------------+

For example, return the following Ids for the above Weather table:

+----+
| Id |
+----+
|  2 |
|  4 |
+----+

Delete Duplicate Emails

Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id.

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
Id is the primary key column for this table.

For example, after running your query, the above Person table should have the following rows:

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

Customers Who Never Order

Suppose that a website contains two tables, the Customers table and the Orders table. Write a SQL query to find all customers who never order anything.

Table: Customers.

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Table: Orders.

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

Using the above tables as example, return the following:

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

Duplicate Emails

Write a SQL query to find all duplicate emails in a table named Person.

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

For example, your query should return the following for the above table:

+---------+
| Email   |
+---------+
| a@b.com |
+---------+

Note: All emails are in lowercase.

思路：

1.

2.

3.

Employees Earning More Than Their Managers

The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+

Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.

+----------+
| Employee |
+----------+
| Joe      |
+----------+

Consecutive Numbers

Write a SQL query to find all numbers that appear at least three times consecutively.

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.

思路：

1.

2.

3.

Rank Scores

Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no "holes" between ranks.

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

For example, given the above Scores table, your query should generate the following report (order by highest score):

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

Nth Highest Salary

Write a SQL query to get the nth highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.

思路：

1.

Second Highest Salary

Write a SQL query to get the second highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

For example, given the above Employee table, the second highest salary is 200. If there is no second highest salary, then the query should return null.

思路：

1.

Combine Two Tables

Table: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId is the primary key column for this table.

Table: Address

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId is the primary key column for this table.

Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:

FirstName, LastName, City, State

Implement Stack using Queues

Implement the following operations of a stack using queues.

• push(x) -- Push element x onto stack.
• pop() -- Removes the element on top of the stack.
• top() -- Get the top element.
• empty() -- Return whether the stack is empty.

Notes:

• You must use only standard operations of a queue -- which means only push to back, peek/pop from front, size, and is empty operations are valid.
• Depending on your language, queue may not be supported natively. You may simulate a queue by using a list or deque (double-ended queue), as long as you use only standard operations of a queue.
• You may assume that all operations are valid (for example, no pop or top operations will be called on an empty stack).

Update (2015-06-11):
The class name of the Java function had been updated to MyStack instead of Stack.

思路：

1.

2.

3.

Lowest Common Ancestor of a Binary Tree

Given a binary tree, find the lowest common ancestor (LCA) of two given nodes in the tree.

According to the definition of LCA on Wikipedia: “The lowest common ancestor is defined between two nodes v and w as the lowest node in T that has both v and w as descendants (where we allow a node to be a descendant of itself).”

        _______3______
       /              \
    ___5__          ___1__
   /      \        /      \
   6      _2       0       8
         /  \
         7   4

For example, the lowest common ancestor (LCA) of nodes 5 and 1 is 3. Another example is LCA of nodes 5 and 4 is 5, since a node can be a descendant of itself according to the LCA definition.

思路：

1.

2.

3.

Maximum Depth of Binary Tree

Given a binary tree, find its maximum depth.

The maximum depth is the number of nodes along the longest path from the root node down to the farthest leaf node.

思路：

1. 树为空返回0

2. 递归求解比较左右子树深度， 返回较大的值加1

Minimum Depth of Binary Tree

Given a binary tree, find its minimum depth.

The minimum depth is the number of nodes along the shortest path from the root node down to the nearest leaf node.

思路：

1. 递归求解

2. Level Order Traversal
3. 特别注意，左子树为空，需要计算右子树的最小高度。
4. 特别注意，右子树为空，需要计算左子树的最小高度。

Validate Binary Search Tree

Given a binary tree, determine if it is a valid binary search tree (BST).

Assume a BST is defined as follows:

• The left subtree of a node contains only nodes with keys less than the node's key.
• The right subtree of a node contains only nodes with keys greater than the node's key.
• Both the left and right subtrees must also be binary search trees.

思路：

1. 注意对于每个节点要分别检查左子树的每个右节点和右子树的每个左节点。

Implement strStr()

Implement strStr().

Returns the index of the first occurrence of needle in haystack, or -1 if needle is not part of haystack.

Update (2014-11-02):
The signature of the function had been updated to return the index instead of the pointer. If you still see your function signature returns a char * or String, please click the reload button  to reset your code definition.

思路：

1. 暴力解法，复杂度O(M*N).

Valid Palindrome

Given a string, determine if it is a palindrome, considering only alphanumeric characters and ignoring cases.

For example,
"A man, a plan, a canal: Panama" is a palindrome.
"race a car" is not a palindrome.

Note:
Have you consider that the string might be empty? This is a good question to ask during an interview.

For the purpose of this problem, we define empty string as valid palindrome.

思路：

1. 必须把字符串统一为小写，而且要忽略除数字和字符外的所有符号。

2. 利用 String.toCharArray() 转换字符串为Character数组。

3. 双指针check头尾，直到双指针相遇前停止，不相等则返回false。

4. 结束check则返回true。

Reverse Words in a String

Given an input string, reverse the string word by word.

For example,
Given s = "the sky is blue",
return "blue is sky the".

Update (2015-02-12):
For C programmers: Try to solve it in-place in O(1) space.

思路：

1. 利用String的split函数，生成包含多个String的数组，注意其中会有空字符串。

2. 从后往前，利用StringBuilder建立反向字符串，注意检查不是空字符串，每次添加空格。

3. 如果StringBuilder为空返回空字符串，否则返回substring(0, sb.length()-1)

4. 输入字符串为空是唯一的特殊情况。

ICE

Talk with Craig

For an onsite interview, they want to know what they have and what you are looking for so that they can make a decision on hiring you for a long term.

Behavior Question

Below are several behavior questions you might be asked. You don’t have to tell every details. For each questions, list 2 or 3 bulletin would be good.

Why you leave?

I like my previous job. But unfortunately, they are going for different directions. The company is doing a shift on out sourcing most of their project. And as a result, they are shrinking their software development team. That is why I am leaving.

What are you looking for in the next couple of years?

For what I am looking for, firstly, I want to continuously developing my Java skills. That is what I am good at and also love to do. I am planning to be a J2EE Architect in the future.

Secondly, I want to do software development in a large scale environment such that there would be different kinds of projects going around. For typical technical company, there is usually only one product. When time is flying, your ability will be limited to just that specific kind of project. However, in XXX I can be involved in numerous applications for different groups. That is exciting for me.

Thirdly, when I am developing software I’d like to hear feedback from the users. It would be great to talk to the users directly, know what they want and deliver exactly the features they want. User’s satisfaction with my product would make me happy. In financial industry, usually the user will be the colleagues in other departments. You can talk to them directly and find out whether they like your contribution or not.

Technical Question

The technical questions can be expected by researching the job description and your resume. For an onsite interview you need to show what you have on the table and let the interviewer decide how much you matching this role. If you are 85% matching, you probably will get the offer. If you only match 50%, you are going to lose.

You are not able to answer correct all the questions. You have to know that. Based on this, you need to know how to handle this situation. When you don’t know the answer, say you don’t know, then tell them a story in your last job when you need to learn new stuffs to handle the position. It’s Okay to not know something but you need to let them know you are willing to learn new stuff and based on history you learn it fast and well.

Do you have any questions?

For this question, it is time for you to get to know better about the group and the situation of the project. Interview is like a date. You are not a man who want a one night stand. You are going to work with the team for long term. So you need to know the situation before you enter in. And of course, you should know that you are able to handle this role. When the interviewer asks this question, it’s time for you get more information about the team. That information can be used for consideration when you decide which offer to take.

What kind of project you guys are doing? What technologies are you guys using?

This question is to know if you are interested in the project and also if you need to learn new stuff when taking this role.

Where are you in the process of project? Will that be released soon? How often do you guys release?

These questions are to know how much pressure you are going to take when you take the role. Most people expect an easy to hard mode rather than hard mode all the time.

How much are you expecting for this role?

When you are asked this question, it means they are considering to give you an offer. You should not hide the current salary you make. Because they will know that eventually. When negotiating, DO NOT give them a number. Tell them the current salary. Tell them for the next job you are looking for various things, not only money. Tell them you are interviewing with some other companies, DO NOT tell them the name.

Longest Valid Parentheses

Given a string containing just the characters '(' and ')', find the length of the longest valid (well-formed) parentheses substring.

For "(()", the longest valid parentheses substring is "()", which has length = 2.

Another example is ")()())", where the longest valid parentheses substring is "()()", which has length = 4.

思路:

1. 使用一个和字符串等长的数组，初始化为0。

2. 使用堆栈存放左括号，如果为 '(' 则压入 '(' 所在位置index。

3. 如果是 ')' 且堆栈不为空，则在数组中设置栈顶左括号的位置和当前右括号的位置的值为1. 弹出栈顶。
4. 此时数组中只有0和1，合法的括号，一定是连续的1. 此题变为最大子序列和。为1计算，为0清零。