[Pen face] Database operation interview

Database Theory

1. What does a trigger do? 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? The
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. What is the role of the index? And what are the pros and cons of it? The
Index is a special query table that the database's search engine can use 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.
4. What is a memory leak?
What we call a memory leak is a leak of 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.
5. Maintain database integrity and consistency, do you prefer to use triggers or self-write business logic? Why?
I do this, as far as possible constraints, such as check, primary key, foreign key, non-empty fields, and so on to constrain, this is 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.
6. What does a view mean? What is a cursor? The
view is a virtual table that has the same functionality as a physical table. You can add, change, check, manipulate, and attempt a view that is 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. A cursor is an efficient processing of a query-out result set as a unit. 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. What are the types of table space management methods?
How to manage data dictionaries
Local File management methods
8. Say the composition of the index?
Indexed columns, ROWID
What is the difference between 9.DELETE and truncate?
1, truncate on various tables whether large or small are very fast. If there is a rollback command delete will be revoked, and truncate will not be revoked.
2. Truncate is a DDL language, and as with all other DDL languages, he will be implicitly committed and cannot use the rollback command with truncate.
3. Truncate will reset the high-level line and all indexes. When a full table and index are fully browsed, the table after the truncate operation is much faster than the table after the delete operation.
4. Truncate cannot trigger any delete triggers.
5. You cannot grant anyone permission to empty another's table.
6. When the table is emptied, the index of the table and table is reset to the initial size, and delete cannot.
7. Cannot empty the parent table.
10. What is a unique index?
A unique index ensures that the indexed column does not contain duplicate values. In the case of a multiple-column unique index, the index ensures that each value combination in the indexed column is unique. For example, if you create a unique index full_name on a combination of last_name, first_name, and middle_initial columns, no two people in that table can have the same full name.
Both clustered and nonclustered indexes can be unique. Therefore, as long as the data in the column is unique, you can create a unique clustered index and multiple unique nonclustered indexes on the same table.
Specifying a unique index makes sense only when uniqueness is a feature of the data itself. If uniqueness must be enforced to ensure data integrity, you should create a unique or PRIMARY KEY constraint on the column instead of creating a unique index. For example, if you intend to frequently query the Social Security Number (SSN) column in the Employee table (the primary key is emp_id) and want to ensure that the social Security number is unique, create a unique constraint on the SSN column. If a user enters the same social security number for more than one employee, an error is displayed.
11.SQL inside in relatively fast or exists relatively fast?
exists is faster because exists returns a Boolean and in returns a value.
What is the difference between 12.rowid and rownum?
rowID is a concept within a database that represents a row of tables for fast access to a row of data
RowNum is a function of the result set, such as a select * from Student where rownum = 2 is the second row of the result set.
13.delete, what is the difference between truncate and drop?
The delete command deletes all or part of a table's data rows, and after the delete is executed, the user needs to commit (Commmit) or rollback (rollback) transaction to perform the delete or undo the deletion. The delete command triggers all the delete triggers on the table.
Truncate deletes all the data in the table, the operation cannot be rolled back, it does not trigger the trigger on the table, truncate is faster and takes up less space than the delete.

The drop command deletes the table from the database, all data rows, indexes, and permissions are also deleted, and all DML triggers are not triggered, and the command cannot be rolled back.

14. Database design principles, several major paradigms.

15. What kinds of encoding methods are used in the database?

SQL operations

1. There is a database table peope, the table has field name,age,address three attributes (note: no primary key). Now if there are duplicate data in the table, please delete the duplicate and leave only one of them. The definition of repetition is that the name,age,address value of the two records is the same.
The key is how to filter out repetitive data, and then what I think about it is this:
SELECT * from people GROUP by Name,age,address have COUNT (DISTINCT name) =1

This allows you to filter out duplicate data, insert the data into a temporary table, delete all the data from the original table, and then plug the data back into the temporary table.

CREATE Temporary TABLE tmp_table SELECT * from people GROUP by Name,age,address have COUNT (DISTINCT name) =1
Delete from people
INSERT INTO people select * from tmp_table

2. A set of exercises

1. Inquire about the employee who entered the 2nd day of the month.

2. Query the manager's information for last_name ' Chen '.

3. Query the department ID of the average salary above 8000 and its average salary.

4. Inquire about employee with lowest salary: last_name, salary

5. Check the department information for the lowest average wage

6. Search for departmental information with the lowest average wage and the average salary of the department

7. Query job information with the highest average wage

8. What are the departments that inquire about the average salary above the company's average salary?

9. Check out the details of all the managers in your company.

10. What is the minimum wage for the lowest unit of the highest wage in each sector?

11. For more information on the manager of the department with the highest average wage: last_name, department_id, email, salary

12. Check the information of the employee who has the highest wage for all employees of the company for 1999 years.

13. Return employee numbers, names, job_id, and salary of employees in other departments who are lower than job_id for all wages in the ' It_prog ' department

3.sql queries the database for the last 10 records and sorts them in descending order.
SELECT TOP from table name ORDER by row sequence DESC; The SQL execution order is sorted by your requirements before the contents of the query are returned. For example, there is a column called auto-growth of ID, there are 100 data in the table, the column is worth 1, 2, 3, 4 ... 9, 99, 100. Then the query plus desc you get 91 to 100, that is the last 10, if you add ASC will be 1 to 10, that is, the front of the few.

Records if there is a succession of words must be sorted according to a certain number of fields you turn to the first 10 records, the DESC and ASC interchange (the default is ASC) Oracle's notation Slect * FROM (SELECT * from tab order BY Col desc) where rownum <= 10
Agree

The last 10 descending is the same as the first 10 ascending, and if you want to sort, then use the temporary table as they say.
Select top * FROM table 1 ORDER by field1 to table #tempselect * from #temp ORDER BY field1 DESC//Query results put temporary table
SELECT * Top from table1 ORDER by field1 ASC to TABL Temp//re-query from temp table
SELECT * from temp ORDER BY field1 DESC

Database Optimization

1. Assume that there are 100 million users, each user at least 100 people, how to design a database (relational database) to meet the following requirements:
1, find a who is concerned about
2, find out who is concerned about a
3, find A and b who have a common interest

2. Should the database commit operation be implemented in a large transaction where there is only one commit for a large transaction?
Using a large transaction to implement bulk database operations, although fallback and recall is very convenient, but it is not conducive to bulk data operations when the exception of the processing and time requirements. If the bulk data processing runs slowly or fails at the last step, the transaction fallback time will be very long, and re-run the entire transaction from start to finish, may not meet the limited bulk of the time window requirements
In the case of an Oracle database, large transaction operations need to be set to a large enough undo tablespace, with an insert operation greater than the size of the data, and a update,delete operation greater than twice times the size of the data. There is also a need to balance the consumption of other operations.
If batch operations affect the integrity of the business, there is no need to do it in batches, and it is not recommended to deliberately batch the operation, only to ensure that there is enough undo table space.

[Pen face] Database operation interview