Interview Title: Database Oracle database not seen 6

Oracle Database Face Test SummaryApril 27, 2017 22:41:44Hits: 9271

1. SQL Statement Classification

DQL (data query Language) SELECT

DML (Data manipulation language) Insert, delete, update

DDL (data definition Language) Create, DROP, alter

DCL (Data Control Language) Grant: Granting Permissions to users, revoke: Recovering permissions from the user

TPL (TCL, Transaction Control Language): Commit, rollback

2. How Oracle is Paged

Oracle uses RowNum for paging

To split a page statement:

A. Top-level SQL, querying all data to be paged

B. Second-tier SQL, which determines the upper bound of the displayed data through rownum pseudo-columns, and adds rownum pseudo-column values to the queried data

C. Outermost SQL, setting the lower limit for displaying data

Select*from

(Select A.*,rownum r from

(select*from table name where condition by column) a

whererownum<= pages * count) b where r> (pages-1) * Number of bars

3, truncate and delete differences

1) Truncate and delete can delete data entities, truncate operation is not logged to rollback log, and data cannot be recovered

2) Truncate is Data definition language (DDL), delete is data manipulation language (DML)

3) Truncate cannot operate on view, delete operation does not free table space memory

4. Talk about functions that are frequently used in Oracle

length, lower lowercase, upper uppercase, to_date conversion date, To_char conversion character, To_number conversion number LTrim go to left space, RTrim to the right space, substr intercept string, add_ Month added or lost,

5. What are the primary key types?

Compound, integer, character type

6, how to create an index, the principles of index use, what are the pros and cons

Create index index name on table name (column name)

Principle:

Recommended index column establish NOT NULL constraint

Tables that are often connected to other tables and indexed on the connection columns

Advantages and Disadvantages

Creating indexes can greatly speed up retrieval and enhance table-to-table connections, but creating indexes is space-intensive

7. Use an Oracle pseudo-column to delete one of the duplicate data in the table

Delete fromtable t where t.rowid!= (select Max (t1.rowid) from table T1 wheret.name=t1.name)

8. How to display duplicate data only

SELECT * fromtable GROUP BY ID has count (*) >1

9. What is a database mapping

Is the process of mapping tables and fields in a database to model-level class names and properties

10. How to design Database

First analyze the project to see how many entities (rectangles) are in the project, add attributes (ellipses) for each entity, clarify the relationship between entities (Rhombus), draw a e-r diagram, convert the E-r diagram into a table

11, how to achieve the optimization of the database

1) Adjust the design of the database structure: consider whether to use the partitioning function, whether to build indexes, etc.

2) Adjust the SQL statement for the database

3) Adjust the server's memory allocation

4) Adjust the hard disk I/O

5) Adjust operating system parameters

12. What are the relationship operations of relational databases?

Select (query some rows), project (query some columns), link (get data from multiple table joins), add, delete, modify

13, do you know which Philippine relational database, and relational database is the difference?

14. The difference between SQL statements and Sql*plus commands

1) SQL is the standard operating language for relational databases, and Sql*plus is a client-side tool that executes some of the commands of the tool itself, in addition to executing standard SQL.

2) The SQL statement cannot be abbreviated, and the Sql*plus command can be abbreviated

15. How to display the structure of a table in an Oracle database

New command window, enter DESC table name

16. Naming rules for database objects in Oracle

1) must start with a letter and be between 1-30 characters in length

2) contains letters, numbers, _, $, #

3) object names that are owned by the same Oracle server cannot be duplicated

4) The name cannot be a reserved word for Oracle

5) Case insensitivity

17. What are the three main paradigms of the database?

1) First paradigm: Atomic parts, requiring that the values of each column no longer be split

2) Second paradigm: A table describes only one entity (if there is redundant data in the column, it is not satisfied)

3) Third paradigm: All columns are directly related to the primary key

18. What is the characteristic (ACID) of a transaction?

1) atomicity (Atomic): Each operation in a transaction is either done or not, and any failure of an operation will result in the failure of the entire transaction.

2) Consistency (consistent): The system state is the same after the end of the transaction

3) Isolation (Isolated): Concurrently executed transactions cannot see each other's middle state

4) Persistence (durable): After a transaction is complete, even if a catastrophic failure occurs, log and synchronous backups can be used to reconstruct the data after the failure occurs

19. What is the difference between MySQL database and Oracle database?

1) Application: MySQL is a database of small and medium-sized applications, generally used for personal projects or small and medium-sized websites and forums. Oracle is a large database, typically in a sizable enterprise application.

2) Auto-growth data type aspect: MySQL has an automatically growing data type. Oracle does not have an automatically growing data type and needs to create a self-increment sequence

3) Group BY usage: the group by in MySQL is free to use in the SELECT statement, but if there is a set of functions in the query statement in Oracle, the other column names must be the columns in the GROUP BY clause that are processed by the SET function or the error

4) quotation marks: in MySQL, wrap strings in double quotes, only single quotes wrap strings can be used in Oracle

Interview Title: Database Oracle database not seen 6