Database common face Questions __ Database

In the process of preparing a database interview, first on the Internet a sea search, to find the history of interview questions, and then a bone a bone after the gnawing, now basically these problems (or called practice) are no problem. The difficulties encountered are: Pl/sql majority, T-SQL too little, so need to filter, modify the answer, and even some in T-SQL has not been supported.

The next step in the database T-SQL Classic Tutorial in the review, basically to the database even if the end of a total of 1 months of time (10.1 is two weeks last year, download is three weeks), the study is OK.

The following is the whole content, a large excerpt, or copied, I wrote the source; some really forget, please forgive me: to share knowledge, presumably is also the author of the Honganji bar.

1. Three Paradigms

That is: Property unique, record unique, table unique

First normal form (1NF): The fields in the database table are single attributes and cannot be divided. This single attribute is composed of basic types, including integer, real, character, logical, date, etc.

Second Normal form (2NF): Non-critical fields do not exist in database tables partial function dependencies on any candidate key fields (partial function dependencies refer to the existence of certain fields in the combination keyword that determine non-critical fields), i.e. all non-critical fields are entirely dependent on any set of candidate keywords.
Third normal form (3NF): On the basis of the second normal form, if no non-critical field exists in the data table, it conforms to the third normal form if the transfer function dependency of any candidate key field is not present. The so-called transfer function dependency means that if there is a "a→b→c" decision relationship, the C transfer function relies on A. Therefore, the database table that satisfies the third normal form should not have the following dependencies: key field → non-critical field x→ non-critical field Y

2. Some common sense: http://www.enet.com.cn/article/2007/0802/ a20070802755140.shtml

² Briefly describe some of the database objects used in SQL Server 2000

Tables, views, user-defined functions, stored procedures, triggers, and so on.

² NULL What does it mean

This value of NULL represents unknown (unknown): It does not represent "" (an empty string). Suppose you have ansi_nulls in your SQL Server database, of course, by default, and any comparison of NULL values will produce a null value. You can't compare any value to a unknown value and logically expect an answer. You must use the IS null operator .

Use ISNULL(var,value) for null judgment: var = value when Var is null, and return value

² What is an index? What type of index is in SQL Server 2000?

An index is a data structure that is used to quickly access data in a database table or view. In SQL Server, they have two forms: clustered Indexes and nonclustered indexes . The clustered index holds the data at the leaf level of the index. This means that regardless of which (or which) fields are in the clustered index, the fields are stored sequentially in the table, in the same physical and logical order. Because of this sort, each table will have only one clustered index. A nonclustered index has a row identifier at the leaf level of the index. It allows multiple nonclustered indexes per table.

² What is a primary key? What is a foreign key?

A primary key is a field (one or more) in a table that is used only to define rows in a table; the values in the primary key are always unique. A foreign key is a constraint used to establish a relationship between two tables. This relationship typically involves a field in a table with a primary key field and another table (possibly the same table). Then these connected fields are foreign keys.

² What is a trigger? What are the different types of triggers for SQL Server 2000?

There are instead-of and after two kinds of triggers. A trigger is a specialized type of stored procedure that is bundled into a table or view. instead-of triggers are stored procedures that override data manipulation language (DML) statements to execute statements on a table. For example, if I have a instead-of-update trigger for TableA, and an UPDATE statement is executed on the table, the code in the Instead-of-update trigger executes, not the UPDATE statement I execute.

After triggers are not executed until the DML statements have been used in the database. These types of triggers are very useful for monitoring data that occurs in a database table.

² How do you make sure that a TableB table with the name Fld1 field has only those values in the Fld1 field that are in the Fld1 field of the table named TableA?

The first answer (and the answer that you want to hear) is to use a foreign key restriction. Foreign key restrictions are used to maintain referential integrity integrity. It is used to ensure that the fields in the table only hold values that have been defined in another field in a different (or identical) table. This field is the candidate key (usually the primary key for another table).
Another answer is a trigger. Triggers can be used to ensure that the same effect is achieved in another way, but it is very difficult to set up and maintain, and performance is generally bad. For this reason, Microsoft recommends that developers use foreign key restrictions rather than triggers to maintain referential integrity.

² What are the performance considerations for an input online transaction processing table (OLTP) with too many indexes?

The more indexes you have on a table, the more time the database engine will take to update, insert, or delete data, because indexes must be maintained when data manipulation occurs.

² What can you use to make sure that the fields in the table only accept values in a specific range?

Check limit, which is defined in the database table to restrict the value entered for the column.
Triggers can also be used to limit the values that fields in a database table can accept, but this approach requires triggers to be defined in a table, which may affect performance in some cases.

² The return parameter is always returned by the stored procedure, which is used to indicate whether the stored procedure succeeded or failed. The return parameter is always an int data type.

The output parameter explicitly requires the developer to specify that it can return other types of data, such as character and numeric values. (The data type that can be used as an output parameter has some limitations.) You can use multiple output parameters in a stored procedure, and you can use only one return parameter.

² What are related subqueries? How do I use these queries?

A related subquery is a special type of query that contains subqueries. A subquery contained in a query can actually request the value of an external query, thus creating a loop-like condition.

11. A column allows NULL values, but wants to ensure that all non-null (non-null) values are unique
SQL Server does not implement an internal mechanism for non-null value uniqueness, so you need to customize the trigger:

Create trigger Mytrigger on T1 for INSERT, update as

BEGIN

IF (select Max (CNT) from (select COUNT (I.C1) as CNT

From T1, inserted I where T1.C1=I.C1 Group by I.C1) x) > 1

ROLLBACK TRAN

End

3. A column of the largest or smallest of the first few, or greater than or less than a certain value (maximum or average) of data

http://www.blogjava.net/looline/archive/2006/12/08/86367.html

* * The HAVING clause sets the condition for the GROUP BY clause in the same way that the WHERE clause interacts with the SELECT statement. The WHERE clause search condition is applied before the group operation, and the having search condition is applied after the group operation . The having syntax is similar to the where syntax, but the having can contain aggregate functions. A HAVING clause can refer to any item that appears in the select list.

² Displays all fields of the last record in the database (ID is self-increasing)

SELECT Top 1 * FROM table_name ORDER BY ID DESC-or

SELECT * FROM table_name WHERE id= (select MAX (ID) from table_name)

² Displays all fields in the last 10 records in the database (ID is DESC do descending ASC for Ascending)

SELECT Top * FROM table_name ORDER BY ID DESC

² To update the State column from the first 10 authors of table authors

UPDATE s SET s.saleprice = s.saleprice+2

From (SELECT top * FROM Sales order by Saleid) as T1, sales s

WHERE S.saleid=t1.saleid

--or

UPDATE s SET s.saleprice = s.saleprice+2 from sales s

WHERE S.saleid in (SELECT top Saleid from Sales order by Saleid)

² Find the top three employees with the highest income in the company

Select Top 3 * The T1 ORDER by a desc-or

Select Top 3 *, row_number () over (order by a DESC) as No from T1

(According to the analysis, the order in the execution plan: Sort by) + Top 3, then where and so on)

² Find the highest (low) three-> five employees in the company

Select Top 3 A from T1 where a in (select Top 5 A from T1 an ASC) ORDER by a DESC

--The disadvantage: to participate in the sort of must be index, or unique, and selected only a single column

--so use the following method

Select Top (10-3+1) * "From" (select top * to customers order with zip ASC) T ORDER by zip DESC

² Take out 31st to 40th Records in table A (SQL Server, with an automatically growing ID as the primary key, note that the ID may not be contiguous.) )

--Top 10 can omit

Select Top * from a WHERE ID not in (select Top ID from a)

² shows the department name of the employee with an average wage greater than 3000 yuan (with SQL statement)

Note full outer join,left joins, right Join,inner join differences and connections

SELECT Dept_name

From T_dept

WHERE ID in (SELECT dept_idfrom t_salary

GROUP by DEPT_ID--Group of departments (i.e.: same department, same operation)

Having avg (Salary) >3000)

² Find those employees whose wages are higher than the average wage in their department

Select Last_Name, dept_id, salary from S_emp a
Where salary> (select AVG (Salary) from s_emp where dept_id=a.dept_id)

² Find those employees whose salaries are higher than those of the manager in their department.

Select ID, last_name, salary, manager_id from S_emp a
where salary> (select salary from s_emp where id=a.manager_id)

² There are two tables, respectively, as follows :
Table A (varchar) name,int GRADE)

Data: Zhangshan, LISI, WANGWU 84
Table B (varchar) Name,int age)
Data: Zhangshan, LISI, Wangwu, Wutian 26
1 Write the SQL statement to get the following query results:
NAME GRADE Age
Zhangshan 80 26
LISI 60 24
WANGWU 84 26
Wutian NULL 26

A: SELECT * from ' right ' join B on a.name = B.name
2 Write the SQL statement according to the first name (name) in the same age group to get the average score of people of different ages, and write the results.

A: AVG (grade) Group by name, age
  4.      Horizontal table is erected
please write  SQl  Statement to achieve the results of the topic requirements: Write a  sql to complete the left table into the right table.

Table structure	Requirements results
ProductID &nbs P Sale_year   SALES 001          2001         & nbsp;   002          2001             15 003          2002              003          2003	ProductID   2001  2002     2003   & nbsp;001        10      002        15& nbsp     003               12

² The number of columns in the cross table is determined

Select Name,

SUM (case subject when ' math ' then source else 0 end) as ' mathematics ',

SUM (case subject when ' English ' then source else 0 end) as ' English ',

SUM (case subject when ' language ' then source else 0 end) as ' language '

From Test GROUP by name

 

² The number of columns in the crosstab table is indeterminate.

DECLARE @sql varchar (8000)

Set @sql = ' Select Name, '

Select @sql = @sql + ' sum (case subject when ' +subject+ ' "

Then source else 0 end) as ' +subject+ ', '

From (select distinct subject from test) as a

Select @sql = Left (@sql, Len (@sql)-1) + ' from test group by name '

EXEC (@sql)

5. SQL Server deletes duplicate data records

Http://www.cnblogs.com/luohoufu/archive/2008/06/05/1214286.html

There are two duplicates of the record, one is a completely duplicate record, that is, all the fields are duplicate records, the second is some key fields duplicate records, such as the Name field repeats, and other fields do not necessarily repeat.

² Write out the SQL statement (or SQL statement Group) and query all Id_no duplicate records.

Select dept_id from Salary

GROUP BY DEPT_ID has count (dept_id) > 1

² For the first repetition, easier to solve, using

SELECT DISTINCT * FROM tablename
You can get a result set without duplicate records.
If the table needs to delete duplicate records (1 of duplicate records are retained), you can delete them in the following ways
SELECT DISTINCT * into #Tmp tablename
TRUNCATE TABLE TableName
SELECT * INTO TableName from #Tmp
drop table #Tmp
This duplication occurs because the table is poorly designed and can be resolved by adding a unique index column .

² This type of repetition typically requires that the first record in a duplicate record be preserved , as follows:

Suppose there is a duplicate field of name,address that requires a unique result set for both fields
Select Identity (int,1,1) as Autoid, * into #Tmp from TableName
Select min (autoid) as autoid into #Tmp2 from #Tmp Group by name,autoid
SELECT * from #Tmp where autoid in (select Autoid from #tmp2)
The last select gets the name,address result set (but one more autoid field, which can be written in the SELECT clause to omit this column)

² Some key fields are duplicated and there is an ID in the record. (* * * this more practical * * * *)

The first method can remove all duplicates at once. (Only records with the lowest ID in duplicate are retained).
Delete from table where ID isn't in (select min(ID) from table group by name) The second method deletes only one record in duplicate with the largest ID at a time.

Delete from table where ID in (SELECT MAX (ID) from table group by name has count (*) >1)

² Using SQL programs to delete

Declare @max integer, @id integer
Declare cur_rows cursor Local for select main field, COUNT (*) from table name Group by main field having count (*) > 1
Open Cur_rows
Fetch cur_rows into @id, @max
While @ @fetch_status =0
Begin
Select @max = @max-1
SET ROWCOUNT @max
Delete from table name where main field = @id
Fetch cur_rows into @id, @max
End
Close Cur_rows
SET ROWCOUNT 0

² itself also to come to the method:

SELECT * FROM User1 where ID is (select top 1 id from user1 where name = user1.name)-Two name equality is more important, otherwise it is wrong. But group by is much better

Delete it and write it

Delete from User1 where ID isn't in (select top 1 IDs from User1 where Name=user1.name)

Or

Delete from user where id "not in" (SELECT Max (ID) from user where name=user.name)

Delete from user where ID. (SELECT max (ID) from user group by name has count (*) > 1)

Other methods

A: Preserves the largest (or smallest) row of IDs, deleting other rows

--Method 1

Delete [user] from [user] t

INNER JOIN (select Name,max (ID) as ID from [user] group by name) a

On t.name = A.name and t.id <> a.id

--Method 2

Delete [user] from [user] t

where exists (select * from [user] WHERE name = T.name and ID > T.id)

B: Delete all duplicate name lines without leaving a line

Delete [user] from [user] t

INNER JOIN

(select ID from [user] a where exists (select * from [user] WHERE name = A.name GROUP BY name has count (*) > 1)) as B

On t.id = b.ID

6. Some highly difficult SQL

Http://www.teecool.com/post/2007072807.html

² If the structure and data of the table :

Table 1:usertable
USERID USERNAME
1 user1
2 NULL
3 User3
4 NULL
5 User5
6 User6

Table 2:usergrade;
USERID USERNAME GRADE
1 User1 90
2 NULL 80
7 User7 80
8 User8 90

then execute the statement select COUNT (*) from Usergrade where username is not in (select username from usertable);

Select COUNT (*) from Usergrade G where does exists (select null from Usertable t where T.userid=g.userid and T.USERNAME=G.U Sername);

The result is: statement 1 (0) Statement 2 (3) a:0 b:1 c:2 d:3 e:null--- don't understand

why the first result is zero. Because: the parentheses inside got all the names of Usertable, two of which are null, in which the meaning of this keyword is or

analogy, so through the above example we know that not in fact produces an indeterminate result, so the return to us is without any result.

I did my own experiment, and I tried this problem, and I took the 2,4 out of the table and I got 2.

A:select ' true ' where 3 in (1, 2, 3, NULL)
B:select ' true ' where 3 isn't in (1, 2, NULL)

Select ' true ' where 3 = 1 or 3 = 2 or 3 = 3 or 3 = NULL

add a not and then become