Database Interview Collation

Source: Internet
Author: User

Concept

1. What is a view? And what are the usage scenarios for the view?

A 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.

    • Only exposes part of the field to the visitor, so build a virtual table, which is the view.
    • Query data from different tables, and respondents want to query in a unified way, so you can also set up a view, multiple table query results together, the query only need to directly from the view of the data, do not consider the data from the different tables caused by the differences
2, the role of triggers?

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.

3. What is a stored procedure? Using what to invoke?

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. Call: 1) You can invoke a stored procedure with a command object. 2) can be called by external programs, such as: Java programs.

4. What are the advantages and disadvantages of stored procedures?

Pros: 1 The stored procedure is pre-compiled, and the execution efficiency is high. 2) The code of the stored procedure is stored directly in the database and is directly called through the stored procedure name to reduce network traffic. 3) security is high, the execution of stored procedures requires a user with certain permissions. 4) Stored procedures can be reused to reduce the workload of database developers. Cons: Poor portability

5, the role of the index? And what are the pros and cons of it?

The index is a special query table, the database search 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.

6. What kind of field is suitable for indexing

A unique, non-empty, frequently queried field

7. What is a transaction? What is a lock?

A transaction is a grouping of SQL statements that are bound together as a logical unit of work, and if any one statement fails, the entire operation is failed, and later 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. Lock: In so the DBMS, the lock is the key to implement the transaction, the lock can guarantee the integrity and concurrency of the transaction. 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.

8. What is a view? What is a cursor?

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 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.

9. Advantages and disadvantages of view

Pros: 1) access to the database, because the view can have a selective selection of the part of the database. 2) Users can get results from complex queries through simple queries. 3) Maintain the independence of data and try to retrieve data from multiple tables. 4) Different views can be generated for the same data. Cons: Performance: When querying a view, you must turn the view's query into a query on the base table, and if the view is defined by a complex multi-table query, then the data cannot be changed

10, list several kinds of table connection way, what is the difference?

Internal connection, self-connect, outer connection (left, right, full), cross-connect

INNER JOIN: Only two elements of the table match to be displayed in the result set.

Outer connection: Left Outer connection: Driver table on left side, data of driver table is all displayed, matching table mismatch will not be displayed.

Right outer connection: the right side is the driver table, the data of the driver table is all displayed, the matching table mismatch will not be displayed.

Full outer joins: the unmatched data in the connected tables is all displayed.

Cross join: The Cartesian effect, the result of which is the product of the number of linked tables.

12. What is the difference between a primary key and a foreign key?

The primary key is unique in this table, it cannot be null, the foreign key can be duplicated, the foreign key is associated with the primary key of the other table, and a foreign key that does not exist in the corresponding table cannot be created.

13, in the database query statement speed is very slow, how to optimize?

1. Build an index

2. Reduce the association between tables

3. Optimize SQL, try to get SQL to locate data quickly, do not let SQL do the full table query, should go index, the data volume of the table in front

4. Simplify the query fields, no useless fields, have control over the returned results, and try to return a small amount of data

5. Try to use PreparedStatement to inquire, do not use statement

14. What is the three-paradigm of the database?

First paradigm: columns cannot be divided

Second Normal: Rows can be uniquely differentiated, primary key constraints

The third paradigm: there is no transitive dependency. And the three paradigms are one-level dependent, the second paradigm is based on the first paradigm, and the third paradigm establishes the first second paradigm

15. What is the difference between Union and UNION all?

The Union will filter out duplicate records after the table link is made, so the resulting set of results will be sorted after the table is connected, the duplicate records are deleted and the results returned . Most of the actual applications do not produce duplicate records, the most common being the process table and the History table Union.

UNION all simply merges two results and returns . Thus, if there are duplicate data in the two result sets returned, the returned result set will contain duplicate data . In terms of efficiency,union ALL is much faster than union , so if you can confirm that the combined two result sets do not contain duplicate data, then use UNION ALL.

16. What is the difference between varchar and varchar?

The length of the char is fixed, and the length of the varchar is changeable, for example, storing the string "abc" for char (20), which means that the characters you store will account for 20 bytes, contain 17 empty, and the same varchar2 (20) accounts for only 3 bytes and 20 is the maximum value. When you store characters that are less than 20 o'clock, store them at the actual length. The efficiency of char is higher than that of VARCHAR2. Currently varchar is synonymous with VARCHAR2, the industry standard varchar type can store an empty string, but Oracle cannot do so, although it retains the right to do so later. Oracle has developed a data type of VARCHAR2, which is not a standard varchar, and he will change the property of a varchar column in the database to store null values, and if you want to have backward compatibility capabilities, Oracle recommends using VARCHAR2 instead of varchar

17. How many types of Oracle statements

Oracle statements are divided into three categories: DDL, DML, DCL.

Data definition Language, which includes the CREATE statement: Some objects that can create databases and databases.

Drop statement: You can delete data tables, indexes, triggers, conditional constraints, and permissions for data tables.

Alter statement: Modifies the data table definition and properties.

Truncate statement: Deletes all records in the table, including all spatial allocation records.

Data manipulation language DML (manipulation Language), including: INSERT statement: Inserts a record into the data sheet.

Delete statement: Deletes one or more records from a data table, or deletes all records in the datasheet, but its operands are still records.

UPDATE statement: Used to modify the contents of a record in an existing table.

The Database Control Language DCL (Data Control Language) includes: Grant statement: Allows the creator of an object to give certain permissions to a user or to a group or all users (public).

Revoke statement: You can revoke access rights for a user or group or all users

18. Difference between order by and GROUP by

Order by sort query, ASC ascending, desc descending

The group by group query, having only used for GROUP BY clauses, is in groups, having conditional clauses can be directly followed by function expressions. Query statements that use the GROUP BY clause need to use aggregate functions.

19. MySQL Database Several basic index types: Normal index, unique index, primary key index, full-text index

    • Indexing speeds up Database retrieval
    • Indexes reduce the speed of maintenance tasks such as INSERT, delete, modify, etc.
    • Unique indexes ensure uniqueness of each row of data
    • By using an index, you can improve the performance of your system by using an optimized stealth device during the query
    • Indexes need to occupy physical and data space

20. What is the optimistic lock and pessimistic lock of the database?

The task of concurrency control in a database management system (DBMS) is to ensure that multiple transactions concurrently access the same data in the database without disrupting the isolation and uniformity of the transaction and the consistency of the database.

Optimistic concurrency control (optimistic lock) and pessimistic concurrency control (pessimistic lock) are the main techniques used in concurrency control.

    • Pessimistic lock: Assumes concurrency conflicts and masks all operations that may violate data integrity
    • Optimistic Lock: Assume that there will be no concurrency conflicts and only check for violation of data integrity when committing the operation.

21, Super key, candidate key, primary key, foreign key is what?

Hyper-Key: A property set that uniquely identifies a tuple in a relationship is called a hyper-key of a relational pattern. A property can be used as a super-key, and multiple properties can be combined together as a super-key. The super-key contains the candidate and primary keys .

Candidate Key: Is the least -key, that is, a super-key without redundant elements.

Primary key: A combination of data columns or properties in a database table that uniquely and fully identifies the stored data object. A data column can have only one primary key, and the value of the primary key cannot be missing, that is, it cannot be a null value (NULL).

FOREIGN key: The foreign key of this table is called by the primary key of another table that exists in one table.

SQL statements

1, 1. Create a database. Requires SQL statements to create a database that meets the following requirements:
(1) The database name is school;
(2) The main data file: The logical name is Schooldata, the file name is "D:\School\SchoolData.mdf", the file initial size is 50MB, the maximum size of the file is unrestricted, the file growth rate is 20%.

(3) Log file: The logical name is Schoollog, the file name is "D:\School\SchoolLog.ldf," The file initial size is 10MB, the maximum file size is 50MB, the file growth rate is 1MB;

Create DatabaseSchool on Primary(Name='Schooldata', filename='D:\School\SchoolData.mdf', size=50MB, MaxSize=Unlimited, filegrowth= -%)
Log on(Name='Schoollog', filename='D:\School\SchoolLog.ldf', size=10MB, MaxSize=50MB, FileGrowth=1MB)

2. Create data types and tables to increase constraints.

(1) Table tblstudent (student table):

Create Tabletblstudent (Stuidbigint Primary Key, Stunamenvarchar(Ten) not NULL, Stusexnchar(1) not NULL, Stubirthdatetime, Stunumnvarchar( -)Unique, departedint, Citynvarchar(Ten)default 'Hangzhou',//Set Default valuesconstraintCk_tblstudent_stusexCheck(Stusexinch('male','female') ),//Establish ConstraintsconstraintCk_tblstudent_stubirthCheck(Stubirth<getdate())    )     

2) Table Tblscore (score table):

Create TableTblscope (Stuidbigint, Mathint, 中文版int, Computerint, Summaryint,    /*establishing foreign KEY constraints*/    constraintStuid_fkForeign Key(STUID)Referencestblstudent (stuid),constraintCk_tblscope_mathCHECK(Mathbetween 0  and  - orMath is NULL),    constraintCk_tblscope_englishCHECK(中文版between 0  and  - or中文版 is NULL),    constraintCk_tblscope_computerCHECK(Computerbetween 0  and  - orComputer is NULL)    )    

3. Use the SQL statement to insert the data from the following table:

/*BULK INSERT, when a new record is added, you can omit the field name, but each field must have a value*/    Insert  intoTblscopeValues(1001, -, the, -,NULL),(1002, $, -, -,NULL);<Strong>  </Strong>  

4. Find the number and name of the student with the highest total
/*First step: Find the highest score from the Tblscope table*/    Select  Max(Math+中文版+Computer) fromTblscope/*Step Two: Find the student ID from the Tblscope table with the highest score*/    SelectStuid fromTblscopeGroup  byStuid having     sum(Math+中文版+Computer)=(Select  Max(Math+中文版+Computer) fromtblscope)/*The third step: according to the highest score to check out students stuid again to inquire about student information*/    SelectStuid,stuname fromTblstudentwhereStuid=    (SelectStuid fromTblscopeGroup  byStuid having     sum(Math+中文版+Computer)=(Select  Max(Math+中文版+Computer) fromTblscope))

5. Statistics on the number of boys and girls
Select  as ' Sex ',count(*as'fromGroup   by

Query Result:


Excerpt from: https://www.2cto.com/database/201710/688377.html

Https://www.cnblogs.com/remember-forget/p/6140112.html

http://blog.csdn.net/beauxie/article/details/52946590#

Database Interview Collation

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.