Common Database interview questions and database questions

Common Database interview questions and database questions
1. Foreign key candidates for primary key superkeys

Primary key:

A combination of data columns or attributes that are unique and fully identified for the stored data objects in the database table.A data column can have only one primary key.And the value of the primary key cannot be missing, that is, it cannot be Null ).

Superkeys:

The attribute set that uniquely identifies a tuples in a link is called the superkey of The Link mode. An attribute can be used as a super key, and multiple attributes can be combined as a super key.The superkeys include candidate keys and primary keys.

Candidate Key:

YesMinimum superkeyThat is, the superkeys without redundant elements.

Foreign key:

ThePrimary Key of another tableThe foreign key of the table.

2. Four features and meanings of database transactions

The four basic elements of the correct execution of Database Transaction transanction. ACID, Atomicity, consistency, Isolation, and Durability ).
Atomicity: All the operations in the entire transaction are either completed or not completed, and it is impossible to stop at a stage in the middle. When a transaction encounters an error during execution, it will be rolled back to the state before the start of the transaction, just as this transaction has never been executed.
Consistency: The integrity constraints of the database are not damaged before and after the transaction starts.
Isolation: The isolation status executes transactions, making them seem to be the only operations performed by the system within a given time. If two transactions run at the same time and perform the same functions, the isolation of the transaction will ensure that each transaction is considered to be only the transaction in the system. This type of attribute is sometimes called serialization. To prevent confusion between transaction operations, you must perform serialization or serialization requests so that only one request is used for the same data at the same time.
Durability: After the transaction is completed, the changes made by the firm to the database will be permanently stored in the database and will not be rolled back.

3. What is the role of a view? Can a view be changed?

A view is a virtual table. Unlike a table that contains data, a view only contains queries to Dynamically Retrieve data during use, and does not contain any columns or data. Using a view can simplify complex SQL operations, hide specific details, and protect data. After creating a view, you can use it in the same way as a table.
The view cannot be indexed or associated with triggers or default values. If the view itself contains order by, the view will be overwritten again by order.
Create view XXX as XXXXXXXXXXXXXX;
For some views, such as the Distinct Union function, which does not use the join subquery grouping function, you can update the view, and update the base table. However, the view is mainly used to simplify retrieval, data protection is not used for update, and most views cannot be updated.

 

More about views: http://www.cnblogs.com/zzwlovegfj/archive/2012/06/23/2559596.html

4. Differences between drop, delete and truncate: drop directly deletes the table truncate, deletes the table data, and then deletes the table data starting from 1 when the table is inserted. You can add the where clause.

(1) The DELETE statement deletes a row from the table each time, and saves the DELETE operation of the row as a transaction record in the log for rollback. Truncate table deletes all data from the TABLE at a time and does not store the delete operation records in logs. Deleting rows cannot be recovered. In addition, table-related deletion triggers are not activated during the deletion process. Fast execution speed.

(2) space occupied by tables and indexes. When a table is TRUNCATE, the space occupied by the table and index will be restored to the initial size, and the DELETE operation will not reduce the space occupied by the table or index. The drop statement releases all the space occupied by the table.

(3) In general, drop> truncate> delete

(4) application scope. TRUNCATE can only be set to TABLE. DELETE can be set to table or view.

(5) TRUNCATE and DELETE only DELETE data, while DROP deletes the entire table (structure and data ).

(6) truncate and delete without where: delete data only, but not the table structure (Definition) drop statement will delete the constraints (constrain) on which the table structure is dependent ), trigger index; the stored procedure/function dependent on the table will be retained, but its status will change to: invalid.

(7) When the delete statement is DML (data maintain Language), this operation will be placed in the rollback segment and take effect after the transaction is committed. If there is a corresponding tigger, it will be triggered during execution.

(8) truncate and drop are DLL (data define language). The operation takes effect immediately. The original data is not stored in rollback segment and cannot be rolled back.

(9) exercise caution when using drop and truncate without backup. To delete some data rows, use delete and use where to restrict the impact scope. The rollback segment must be large enough. To delete a table, use drop. If you want to retain the table and delete the table data, use truncate if it is unrelated to transactions. If it is related to the transaction or the instructor wants to trigger the trigger, delete is used.

(10) Truncate table names are fast and efficient because:
The truncate table function is the same as the DELETE statement without the WHERE clause: both DELETE all rows in the table. However, truncate table is faster than DELETE and uses less system and transaction log resources. The DELETE statement deletes a row at a time and records one row in the transaction log. Truncate table deletes data by releasing the data pages used to store TABLE data, and only records the release of pages in transaction logs.

(11) truncate table deletes all rows in the TABLE, but the TABLE structure and its columns, constraints, and indexes remain unchanged. The Count value used by the new row ID is reset to the seed of the column. To retain the ID Count value, use DELETE instead. To delete TABLE definitions and data, use the drop table statement.

(12) for tables referenced by the foreign key constraint, the truncate table cannot be used, but the DELETE statement without the WHERE clause should be used. Because the truncate table is not recorded in the log, it cannot activate the trigger.

5. indexing principles and types