Several questions frequently asked in the database interview

Reprinted from HTTP://BLOG.SINA.COM.CN/S/BLOG_8DC50331010158U7.HTML1. What does a trigger do? A: A trigger is a special stored procedure that is executed primarily by triggering an event. It can enforce constraints to maintain the integrity and consistency of data, and can track operations within the database without permitting unauthorized updates and changes. Can be associated with a level operation. For example, a trigger on a table contains data operations on another table, and that action causes the table trigger to be triggered.
2. What is a stored procedure? Using what to invoke? A: A stored procedure is a precompiled SQL statement, and the advantage is that it allows for a modular design, meaning that it is created once and can be called more than once in the program. If an operation needs to execute multiple times of SQL, using a stored procedure is faster than simply SQL statement execution. You can invoke a stored procedure with a command object.
3. The role of the index? And what are the pros and cons of it? A: The index is a special query table, the database search engine can use it to speed up the retrieval of data. It is similar to the catalogue of real-life books, and you can find the data you want without having to query the entire contents of the book. Indexes can be unique, and creating an index allows you to specify a single column or multiple columns. The disadvantage is that it slows down data entry and also increases the size of the database.
3. What is a memory leak? A: Generally what we call a memory leak refers to a leak in heap memory. Heap memory is a program that is allocated to it from the heap, arbitrarily sized, and then shown to free memory after it is exhausted. When an application creates an object with the keyword new and so on, it allocates a piece of memory from the heap, the program calls free or the delete frees the memory after use, or the memory is not used, we say that the memory is compromised.
4. Maintain database integrity and consistency, do you prefer to use triggers or self-write business logic? Why? A: I do this, as far as possible to use constraints, such as check, primary key, foreign keys, non-empty fields to constrain, so that the most efficient and most convenient. The second is the use of triggers, which ensures that the data is intact and consistent regardless of the business system access to the database. The final consideration is self-writing business logic, but this is cumbersome, programming complex, inefficient.
5. What is a transaction? What is a lock? A: A transaction is a grouping of SQL statements that are bound together as a logical unit of work, and if any one statement fails then the entire operation fails, and the operation is rolled back to the pre-operation state, or there is a node on it. A transaction can be used to ensure that it is either executed or not executed. To consider a set of statements as transactions, you need to pass acid testing, that is, atomicity, consistency, isolation, and persistence. Locks: In the DBMS, locks are the key to implementing transactions, and locks guarantee the integrity and concurrency of transactions. Like a real-life lock, it enables certain data owners to be unable to use certain data or structures for a certain period of time. Of course, locks are also divided into levels.
6. What does a view mean? What is a cursor? A: A view is a virtual table that has the same functionality as a physical table. The view can be increased, changed, checked, manipulated, views are usually a subset of rows or columns that have a table or multiple tables. Changes to the view do not affect the base table. It makes it easier for us to get data, compared to multiple table queries. Cursor: is a query out of the result set as a unit to effectively handle. A cursor can be set to a specific row in that cell, retrieving one or more rows from the current row of the result set. You can make modifications to the current row of the result set. Cursors are generally not used, but cursors are important when you need to process the data individually.
7. For the management of business training information, the establishment of 3 tables: S (S#,SN,SD,SA) S#,sn,sd,sa respectively represents the student number, the student name, the affiliation unit, the student ages C (C#,CN) C#,CN respectively represents the course number, the course name SC (s#,c#,g) s#,c#, G respectively represents the student number, the selected course number, and the academic score (1) Use standard SQL nested statements to query the student number and name of the elective course named ' Tax base '? Answer: Select s#, SN from S where s# in (select s# from C,SC where C. c#=sc.c# and cn= ' tax base ') (2) Use standard SQL nested statements to query the student name and affiliation of the elective course number ' C2 '? A: Select Sn,sd from S,SC where s.s#=sc.s# and sc.c#= ' C2 ' (3 Use standard SQL nested statements to query for the name and affiliation of the student who does not take the course number ' C5 '? A: Select Sn,sd from the Where s# not in (select s# from SC where c#= ' C5 ') (4) query participants who have enrolled in the course Number A: Select number of participants =count (distinct s#) from SC (5) query elective courses more than 5 students study number and affiliation unit? A: Select Sn,sd from S where s# in (select s# from SC Grou P by s# have count (Distinct C #) >5)

Several questions frequently asked in the database interview