Jacky self-answer-database

1. What is the difference between exists and in?

• The exists is used to check whether a subquery returns at least one row of data, and the subquery does not actually return any data, but instead returns a value of TRUE or false, while the in subquery returns a specific data value compared to the specified field
• The problem of the efficiency of exists and in, usually using exists is higher than in efficiency, because in does not walk the index, but depends on the actual use of practical

• In the case of large appearance and small inner table, exists is suitable for small appearance and large inner table. (The landlord has not figured out the principle)

2. Explain the SQL Union and UNION ALL operators?

• The Union and Union all are merged with the result set of two or more table queries, and the union drops the duplicate records, and unional does not remove the duplicate records. Union and UNION all use the same column data for two result sets, same data type, same order

3, a table has 3 fields, gender, name, age, with a SQL query out how many men have, how many women?

• Select Sex,count (*) from table GROUP by sex

or select sum (case when sex = ' male ' then 1 else 0 end) Number of males, sum (case when sex = ' women ' then 1 ELSE 0 end) Number of women from Table

4, an employee table, there is a two fields, respectively, is the employee name and salary, assuming >=1000 on the level, less than 1000 on the 2 level, with a SQL query out all the staff level?

• SELECT name,
• Case when wages < Then ' 2 '
• When wages >= ' 1 '
• ELSE NULL END wage level,
• From Table_a

Reference: http://www.cnblogs.com/prefect/p/5746624.html

Jacky self-answer-database