Interview question: Database not seen 8

The difference between stored procedures and functions

A stored procedure is a collection of user-defined sets of SQL statements that involve a task for a particular table or other object, a user can call a stored procedure, and a function is usually a method defined by a database that takes parameters and returns a value of some type and does not involve a particular user table.

.

The benefits of the L view? Establish a basic syntax structure for a view?

Advantages of the View:

1. The view provides a degree of logical independence for the reconfiguration of the database. The logical independence of the data is when the database is re-constructed, such as the database is enlarged (new fields, new relationships, etc.), and the user and user programs are not affected.

2. Simplified user perspective. The view mechanism allows the user to focus on the data he cares about. If the data is not directly from the base table, you can define the view, which makes the data structure in the user's eyes simple and straightforward, and can greatly simplify the user's data query operations, especially the view of connecting several tables together, the connection operation from table to table is hidden from the user.

3. The view mechanism enables different users to view the same data in different ways.

4. The view mechanism provides automatic security protection for confidential data. Confidential data can be separated from the public Data view (the base table), that is, different views are defined for different users, and the fields of confidential data are not included in the user view. In this way, such data cannot be accessed by the user through the view, thereby automatically providing protection of the confidential data.

Basic syntax structure of the view:

CREATE VIEW view_name [(column] [,... N])] as select_statement

Where View_name is the name of the view to be built, and the AS clause is followed by the query statement that establishes the view. This statement has the following limitations: You cannot include the order BY, compute, and COMPUTE by clauses, cannot contain the INTO keyword, and cannot involve temporary tables.

.

l What is a transaction?

A transaction is a series of operations performed as a logical unit, and a logical work cell must have four properties, called ACID (atomicity, consistency, isolation, and persistence) properties, in order to be a transaction:

1, atomicity: The transaction must be an atomic unit of work, for its data modification, either all execute, or all do not execute.

2. Consistency: When a transaction is complete, all data must be kept in a consistent state. In a related database, all rules must be applied to transaction modifications to maintain the integrity of all data. At the end of the transaction, all internal data structures, such as B-tree indexes or doubly linked lists, must be correct.

3. Isolation: Modifications made by concurrent transactions must be isolated from modifications made by any other concurrent transaction. The state in which the data is located when the transaction is viewing the data, either when another concurrent transaction modifies its state or after another transaction modifies it, and the transaction does not view the data in the middle state. This is called serializable because it is able to reload the starting data and replay a series of transactions so that the state at the end of the data is the same state as the original transaction execution.

4, Persistence: After the transaction is completed, it has a permanent effect on the system. This modification will persist even if a system failure occurs.

.

What is the function of the cursor? How do I know that the cursor is at the end?

Cursors are used to locate the rows of the result set by judging the global variable "@ @FETCH_STATUS" to determine whether it is final. Usually this variable does not equal 0 to indicate an error or to the last.

.

L trigger is divided into pre-trigger and post-trigger, what is the difference between the two triggers. What is the difference between a statement-level trigger and a row-level trigger.

An advance trigger runs before the triggering event occurs, and afterwards the trigger runs after the triggering event. Usually the pre-trigger can get the event before and the new field value.

Statement-level triggers can be executed before or after the statement executes, while row-level firings are triggered once for each row affected by the trigger.

.

L Database design: Please design an Enterprise Invoicing system database table structure, the need to achieve the basic management of invoicing, need to record information including: Supplier information, commodity information, inventory information, customer information, warehousing information, warehouse information, etc.: Warehousing as incoming goods, out of the library as sales, and enterprises have multiple warehouses Please draw a table description table structure (need to describe the field name of each field, field type, field meaning description);

In the database design, you should:

1. Ensure uniqueness of each supplier, commodity category, customer, warehouse, etc.;

2. Ensure the referential integrity between supplier information, commodity information, warehouse information, customer information and access to the library information;

3. If new suppliers, new products, new customers, new warehouses to join, to ensure the automatic generation of their unique identity;

4. Database design should support the following reporting requirements: (no specific instructions, do not write implementation statements, but in the database design, ensure that these reports can be implemented with up to one SQL statement):

ü Daily: Daily stock, sales of goods and a list of products, including the corresponding supplier information, warehouse information, daily Enterprise gross profit statement (sales and purchase price difference);

ü Monthly Report: The sales of goods from each supplier each month;

ü Real-time reporting: Inventory the current list of warehouse items, including product name, number, inventory time, etc. write the SQL statement that implements this requirement.

Database application:

Please write a series of SQL statements describing the complete commodity transfer operation, that is, a certain kind of commodity, from a warehouse to another warehouse in the process, invoicing system needs to execute a series of SQL statements, and guarantee the execution integrity of this series of SQL statements. (Baidu)

.

L Design a table in SQL Server to hold an organization chart of a tree structure (assuming that only the name in the structure diagram needs to be saved). What do you think if you want to query all the positions in a position and use a stored procedure to implement it?

.

L 2, assuming the following two tables: * Primary key

Cus_a

id*
Name
Address

...
...
...

Cus_b

id*
Name
Address

...
...
...

The structure of table cus_a and table Cus_b is exactly the same, and there are records with the same ID in table cus_a and table Cus_b, and records with different IDs. It is now required to insert the records that exist only in table cus_a and not in table cus_b into the Cus_b table, and to update the records of the same ID in table Cus_b with the records in table Cus_a, write out the stored procedure that completes this function.

Create or procedure test is

Declare ID1 Cusa.id%type;

Begin

Select ID into id1 from cusa A,cusb b where a.id=b.id;

.

L A company is developing a file management system that requires a tree-like file directory that is fully consistent with the Windows file system in a relational database. At least what tables are required to implement this directory structure? Please describe in detail the purpose and structure of these tables (if necessary, a diagram can be used to describe them). A pseudo-code (or natural language) describes an algorithm for traversing all files in a tree-like structure.

.

L Select the record that num repeats in table t

SELECT * from T where num in (

Select num from T GROUP by Num have (COUNT (num) >1))

.

L Extract records from rows fifth through seventh in the database

Select Top 3 userid from T where UserID not in (

Select Top 4 UserID from the T Order by UserID) Order by UserID

.

L A table T has two fields: ID, Name,id is the primary key. To query all name with two or more IDs.

Select NAME from T GROUP by NAME has COUNT (ID) >=2

Interview question: Database not seen 8