Common Database interview questions and database questions

Common Database interview questions and database questions

Recently, I have compiled some common database interview questions. I also want to review them and hope they will help you.

1. What is the role of a trigger?

A trigger is a special type of stored procedure. It is executed mainly through events. It can enhance constraints to maintain data integrity and consistency, and track operations in the database so that unauthorized updates and changes are not allowed. Cascade operations are supported. For example, a table trigger contains data operations on another table, which triggers the table trigger.

 

2. What is a stored procedure? What is used for calling?

A stored procedure is a pre-compiled SQL statement. The advantage is that it allows modular design, that is, it only needs to be created once and can be called multiple times in the program. If an operation requires multiple SQL statements, the stored procedure is faster than the SQL statement. You can use a command object to call a stored procedure.

 

3. What is the role of indexes? What are its advantages and disadvantages?

An index is a special query table. The database search engine can use it to accelerate data retrieval. It is similar to the directory of books in real life. You can find the desired data without querying the entire book. An index can be unique. You can create an index to specify a single column or multiple columns. The disadvantage is that it slows down data entry and increases the size of the database.

4. What is transaction continuity? A

A. Once A transaction is committed, changes to the database are permanent.
B. All operations included in the transaction are either done or not done.
C. operations and data used within a transaction are isolated from other concurrent transactions.
D. The transaction must change the database from one induced state to another.

Resolution: Transaction persistence (also called permanent) means that once the transaction is committed successfully, its data modification is persistent. The data update result has been transferred from memory to external memory. After that, even if a system failure occurs, the data update submitted by the firm will not be lost.

5. Which of the following options is the basic unit of DBMS, which is the set of operations that constitute a single logical unit C
A. Process
B. SQL
C. Transactions
D. File

6. What are the keywords of SQL statement performance analysis? A
A. EXPLAIN
B. LOAD
C. TOP
D. SUM

The explain command is the first recommended command for solving database performance. It can be used to solve most performance problems. The Explain command can be used to view the execution results of SQL statements, it can help you select better indexes and Optimize Query statements to write better optimization statements. Explain Syntax: explain select... From... [Where...] example: explain select * from news;

7. In relational databases, what is used to indicate the relationship between entities? B
A Property
B branch table
C-Network Structure
D Tree Structure

[Resolution] The Relationship Between Relational Model entities is represented by a two-dimensional table. Option C is the link between mesh model entities, option D is the link between hierarchical model entities, and option A describes entities.

8. What is the execution sequence of the SQL where condition? A
A. Before and after
B goes forward from the back
C. What is the other order?

9. There are four levels of transaction isolation in the database. Which of the following statements allow "non-repeated reads? (Interpretation) BC
A SERIALIZABLE
B READ COMMITTED
C READ UNCOMMITTED
D REPEATABLE READ

C: dirty reads, Phantom reads, and repeated reads are not allowed.
B: phantom reading occurs and repeated reading is not allowed.
D: phantom reading
A: No.
Read Uncommitted: data is not isolated. Other transactions can modify and delete the data used by the transaction.
Read Committed: Read of uncommitted data is not allowed. Only data Committed by other transactions can be accessed.
Repeatable Read: the data used in the transaction is locked, and other transactions cannot be modified or deleted.
Snapshot: Snapshot isolation
Serilizable: All data used by the transaction is locked. Other transactions cannot be added, modified, or deleted.

10. Which of the following statements about spring's PROPAGATION_REQUIRES_NEW transaction are true? B
A. Internal transaction rollback will cause external transaction rollback
B. The internal transaction is rolled back, and the external transaction can be committed.
C. The external transaction is rolled back, and the internal transaction page is rolled back.
D. After the external transaction is rolled back, the internal transaction can still be promoted.

11. In view, the constant operation is C.
A. Update the view
B. Query
C. define a new table in the view
D. define a new view on The View

12. Which of the following statements about SQL variable binding is false? C
A binding variable refers to using variables in SQL statements to change the value of variables to change the execution results of SQL statements.
B. bind variables to reduce SQL statement parsing and reduce Database Engine resource consumption on SQL statement parsing.
C binds variables, improves programming efficiency and reliability, and reduces the number of database accesses.
D. Bind a variable. The query optimizer will predict that the variable is more authentic than the literal variable.

13. Which of the following statements about SQL Server locks are true?
A locks can be divided into exclusive locks (exclusive locks), shared locks, and update locks.
The lock granularity supported by BSQL Server can be divided into rows, pages, keys, key ranges, indexes, tables, or databases.
CSQL Server uses the SET LOCK_TIMEOUT command to SET the lock timeout interval.
D locks are mainly used to provide the isolation required by the firm. Isolation ensures that transactions do not interfere with each other.

<Analysis>:
(1) concept:
A database is a shared resource used by multiple users. When multiple users access data concurrently, multiple transactions can access the same data simultaneously in the database. If concurrent operations are not controlled, incorrect data may be read and stored, compromising Database Consistency.
Locking is a very important technology for implementing database concurrency control. Before a transaction operates on a data object, it first sends a request to the system to lock it. After the lock, the transaction has certain control over the data object. Before the transaction releases the lock, other transactions cannot update the data object.
(2) Lock classification:
Share (S) Lock: multiple transactions can block one shared page. No transaction can modify this page. Generally, this page is read and the S lock is released immediately.
Lock (X): Only one transaction is allowed to block this page. any other transaction must wait until the X lock is released to access this page. The X lock cannot be released until the transaction ends.
Update (U) Lock: The update lock is used to lock resources that may be modified during the initialization phase of the modification operation, so as to avoid deadlocks caused by the use of shared locks. When a shared lock is used, the data modification operation is divided into two steps. First, a shared lock is obtained, data is read, the shared lock is upgraded to an exclusive lock, and then the modification is performed. In this way, if two or more transactions apply for a shared lock for one transaction at the same time, these transactions must upgrade the shared lock to an exclusive lock when modifying data. At this time, these transactions will not release the shared lock, but will wait for the other party to release, resulting in a deadlock. If you apply for an update lock before data modification and upgrade it to the exclusive lock when data is modified, the deadlock can be avoided.
(3) Lock granularity:
In SQL Server, the lock is granular, that is, different resources can be locked. Locking in a small granularity of resources (such as rows) can increase the concurrency of the system, but requires a large amount of system overhead, which will also affect the system performance, because the lock granularity is small, the number of locks that an operation may produce will increase. Locking at a large granularity (such as a table) is quite expensive for concurrency, locking the entire table limits access to any part of the table by other transactions, but requires a low overhead. Because there are fewer locks to be maintained, it is a mutually restrictive relationship here.
The granularity locked in SQL Server includes rows, pages, extended disk areas, tables, databases, and other resources.

14. Transaction logs are used to save C
A program running process
Execution result of program B
C. Data Update operations
D. query data

Records Database transactions to maximize data consistency and security
Redo log file: includes changes made to the database. In this case, you can enable data recovery in case of a fault. A database requires at least two redo log files.
Archive log file: an offline copy of the redo log file, which may be necessary to recover from a media failure.

15. What are the four basic elements of correct execution of database transactions? C
A isolation
B persistence
C mandatory
D. Consistency

16. To delete data in table a, use truncate table. What is the running result? A
The constraints in Table A still exist.
Table B A has been deleted.
Half of the data in Table C is deleted. When you execute the statement again, the remaining half of the data rows are deleted.
D. The data in table A that does not meet the check constraints is deleted, while the data that meets the check constraints is retained.

The statement for deleting a TABLE is: drop table table_name;
DELETE and TRUNCATE tables are statements used to DELETE data in a TABLE. They differ in the following:
1. truncate table is faster than DELETE;
2. truncate table is used to DELETE all rows of the TABLE, while DELETE is used to DELETE one or more rows of the TABLE (unless DELETE does not contain the WHERE statement );
3. If any row violates the constraints (mainly foreign key constraints) during deletion, the truncate table will still be deleted, but the TABLE's structure, columns, constraints, and indexes remain unchanged, however, DELETE directly returns an error;
4. For tables restricted by foreign keys, the truncate table cannot be used, but the DELETE statement without the WHERE statement should be used.

5. If you want to retain the ID Count value, use DELETE because truncate table resets the Count value searched for the new row flag column to the seed of the column.

17. Which of the following statements about data redundancy is false? () C
In database A, data copies exist, that is, data redundancy.
B. classified storage can effectively reduce data redundancy, But it increases the complexity of data search.
C. In the database design stage, do your best to avoid data redundancy. It is best to avoid data redundancy.
D. data redundancy is usually caused by database design.

18. Which of the following statements about the transaction isolation level are true? ABCD
A Uncommitted Read (Read Uncommitted): Dirty Read is allowed, that is, data that may be Read from other sessions that have not committed transaction modifications.
Read Committed: only submitted data can be Read.
C Repeated Read: queries in the same transaction are consistent at the start time of the transaction.
D. Serializable: Fully Serializable read. Each read operation requires a table-level shared lock, which blocks read/write operations.

Http://blog.csdn.net/u011102153/article/details/48184339