Summary of ZTE's written questions (II.) database part __ Database

1. The database holds only views ()
A operation
B the corresponding data
C definition
D limit

A view is an important mechanism that a relational database system provides to users to observe data in a database in a variety of ways. A view is a table that is exported from one or several basic tables (or views), unlike a basic table, which is a

Virtual table. The database holds only the definition of the view, not the corresponding data for the view, which is still stored in the original base table.

2. The following description of the database paradigm, which is wrong ()
A. If multiple data items are represented as a field with a large String, the first normal form is not satisfied
B. Meeting the database paradigm to make the database more efficient
C. If the higher paradigm is met, the lower-level paradigm must first be met
D. The second paradigm of the database requires that all data in the table not directly related to the primary key be split into other tables

Analysis:
A should be right, that's the first paradigm.
c should be right, this paradigm has a nature of what it's like to say this
There are B and D, the second normal form requires no partial dependence, seems to be right, but B is also like the feeling of the right, but may say a little ambiguous, to meet the database is to make the storage efficiency of the database is higher,

Less redundant data, if the efficiency of the implementation of the database, then the impact should be the underlying implementation of the data structure.
So I'm still inclined to choose b.

3. In order to speed up the database lookup, you need to add indexes to the data table, please ask the following description of the index, which is wrong ()
A. In the cluster index, the physical order of the rows in the table is the same as the logical order of the index key values
B. Using indexes can make data updates less efficient
C. In most database systems, each table can have only one cluster index
D. Consider this SQL statement: Select s.name, s.age from Student S where s.address= "Chengdu", if we build a composite index on the table Student (age,

Address), you can improve the efficiency of the above query.

The address for the D query has nothing to do with name and age

Clustering index
A clustered index is an index in which the logical order of key values determines the physical order of the corresponding rows in the table. The clustered index determines the physical order of the data in the table. Clustered index is similar to phone book, arranged by last name

Data. Because a clustered index prescribes the order in which data is physically stored in a table, a table can contain only one clustered index. However, the index can contain more than one column (combined index), just like a phone book by last name and

Names are organized as well. Clustered indexes are especially effective for columns that are often searched for range values. After you use the clustered index to locate the row that contains the first value, you can ensure that the rows that contain the following index values are physically

Adjacent. For example, if an application executes a query that frequently retrieves records in a range of dates, use a clustered index to quickly locate the row that contains the start date, and then retrieve all adjacent

Line until the end date is reached. This helps improve the performance of such queries. Similarly, if a column is often used to sort data retrieved from a table, you can aggregate the table on that column (

Physical sorting) to save costs by avoiding sorting each time the column is queried. Using a clustered index to find a specific row is also efficient when index values are unique. For example, use a unique employee ID column

emp_id the quickest way to find a specific employee is to create a clustered index or PRIMARY KEY constraint on the emp_id column.

Applicable situation
1, a column containing a large number of distinct values.
2, using between,>,>=,< or <= to return a range of values of the column
3. Columns that are continuously accessed
4. Queries that return large result sets
5. Columns frequently accessed by queries using a connection or a GROUP BY clause

4. What is a distributed database?
Distributed database systems usually use smaller computer systems, each computer can be placed in a single place, each computer has a full copy of the DBMS, and has its own local database

, many computers located in different locations connect to each other through the network to form a complete, global large database.
This method of organizing the database overcomes the weaknesses of the Physical Center database organization. First, the cost of data transfer is reduced because most of the access operations to the database are for local databases, not

is a database access to other locations; Secondly, the reliability of the system improves a lot because when the network fails, the operation of the local database is still allowed, and the failure of one location does not affect the other

The location of the processing work, only when access to the fault location of the data, to a certain extent, is affected; third, it is easy to expand the system, add a new local database, or expand a

An appropriate small computer is easy to implement. However, some features have to pay a higher price. For example, in order to provision activities in several locations, transaction management performance is more expensive than in a central database

High, and even offset many other advantages.

5. The transaction
A transaction is a sequence of operations that is "either done or not" and is an inseparable logical unit of work in the database environment. Transactions and programs are two different concepts, and generally a program can package

Includes multiple transactions. The following three articles are in the SQL statement:
1) Begin TRANSACTION: Transaction start
2 Commit: The transaction commits. This operation represents the successful end of a transaction, and he notifies the transaction manager that all update operations for that transaction can now be committed or persisted.
3) ROLLBACK: Transaction rollback. This operation indicates that the transaction ended unsuccessfully, and it notifies the transaction manager that it is failing, that the database may be in an inconsistent state, and that all update operations for that transaction must be rolled back

or withdrawn.

Transactions are atomic, consistent, isolated, and persistent. These 4 properties are also known as the ACID properties of transactions. (Atomicity, consistency, isolation, durability)
1 atomicity: The business is atomic, either doing it or not doing it.
2 Consistency: The result of a transaction execution must ensure that the database is changed from one consistency state to another.
3) Isolation: Transactions are isolated from each other. When multiple transactions are executed concurrently, the update operation of any transaction is not visible to other transactions until the entire process of its successful submission.
4 Persistence: Once the transaction is committed successfully, the update operation on the database will be permanently valid even if the database crashes.

Basic unit of recovery and concurrency control-transactions

Transaction serial execution: that is, there is only one transaction running at a time, and other things must wait until the end of the transaction before it can run.
Concurrent mode: In a multiprocessor system, each processor can run a transaction, multiple processors can run multiple transactions at the same time, to achieve a real parallel operation of multiple transactions, this parallel way called

Simultaneous concurrent mode.

6. The difference between a primary key and a unique index.
A primary key is a constraint, and a unique index is an index, which is essentially different.
A primary key must contain a unique index after it is created, and a uniqueness index is not necessarily the primary key.
The uniqueness index column allows null values, while primary key columns are not allowed to be null values.
When the primary key column is created, it defaults to a Non-null value + unique index.
A primary key can be referenced by another table as a foreign key, and a unique index cannot.
A table can only create one primary key, but multiple unique indexes may be created.
Primary keys are better suited for unique identities that are not easily changed, such as AutoIncrement columns, ID numbers, and so on.

7. How does a trigger work?
Triggers are executed primarily by triggering events, and the database automatically executes the SQL defined by the trigger when you perform such actions as update, INSERT, and DELETE on a table.

To ensure that the processing of the data must conform to the rules defined by these SQL statements.

8. The database of a table to operate, while the other table to operate, how to achieve?
The operation of manipulating multiple tables into a transaction is handled. You can also design a trigger that should be.

9. The age between "11~35岁" belongs to the database (integrity constraints).
Parsing: Integrity rules provide a means to ensure that when an authorized user modifies a database, it does not break the consistency of the data. Therefore, the integrity rule is to prevent accidental damage to the data. The end of the relational model

The integral rule is a kind of constraint condition to the relation. The integrity of a relationship is divided into three categories: entity integrity, referential integrity (also known as referential integrity), and user-defined integrity.
(1) Entity integrity. Primary key cannot be empty
(2) Referential integrity. You cannot delete a foreign key if it does not exist, or if it is being referenced.
(2) User-defined integrity. Conditional constraints, such as the above topic.

10, in a table, there are many fields, the value of each field has a lot of duplicates, let you write a SQL statement, the number of occurrences in each field is listed, and in front of this value plus a

1 (if more than one table is involved, please change the duplicate data in the other table)
For example, there are two fields of age and salary, the most of which are 18 and 3000, find out and change to-118 and-13000

If you add 1 to the string, select Concat ('-1 ', Sal) from the EMP; Oracle has this function, Oracle can directly use | | To connect a string, while MySQL

No, you can't.
I haven't figured out how else to write it, there's a reference.
Http://topic.csdn.net/t/20061227/11/5258862.html