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