MSZBSQL interview question 2

Basics: 1. data abstraction: Physical abstraction, conceptual abstraction, view-level abstraction, internal mode, mode, external Mode 2. the SQL language includes data definition, data manipulation, and DataControl data definition: CreateTable, AlterTable, DropTable, CraeteDropIndex, and other data manipulation: Select, insert, u

Basics: 1. data abstraction: Physical abstraction, conceptual abstraction, view-level abstraction, internal mode, mode, external Mode 2. the SQL language includes Data definition, Data Manipulation, and Data Control Data definition: Create Table, Alter Table, Drop Table, Craete/Drop Index: select, insert, u

Basics:
1. Data abstraction: Physical abstraction, conceptual abstraction, view-level abstraction, internal mode, mode, external mode
2. the SQL language includes Data definition, Data Manipulation, and Data Control)
Data Definition: Create Table, Alter Table, Drop Table, Craete/Drop Index, etc.
Data manipulation: Select, insert, update, delete,
Data Control: grant, revoke
3. Common SQL commands:
Create table Student (
Id number primary key,
NAME VARCHAR2 (50) not null); // create a table
Create view view_name
Select * FROM Table_name; // create a view
Create unique index index_name ON TableName (col_name); // Create an INDEX
Insert into tablename {column1, column2 ,...} Values (exp1, exp2 ,...); // Insert
Insert into Viewname {column1, column2 ,...} Values (exp1, exp2 ,...); // Insert the view to the actual affected table
UPDATE tablename SET name = 'zang 3' condition; // UPDATE data
Delete from Tablename WHERE condition; // DELETE
GRANT (Select, delete ,...) ON (object) TO USER_NAME [with grant option]; // authorization
REVOKE (permission table) ON (object) FROM USER_NAME [with revoke option] // REVOKE permission
List the names of the staff and their leaders:
Select E. NAME, S. NAME FROM EMPLOYEE E S
Where e. SUPERName = S. Name
4. View:
5. integrity constraints: entity integrity, reference integrity, and user-defined integrity
6. What is the third paradigm:
1NF: Each attribute cannot be divided.
2NF: If the relational R is 1NF, and each non-primary attribute is completely dependent on the R key. For example, SLC (SID #, CourceID #, SNAME, Grade) is not 2NF;
3NF: if R is 2NF, and any non-key attribute of R is not passed, it depends on any candidate key.
7. ER (entity/contact) Model
8. Indexing
9. transactions are a series of database operations and basic logical units of database applications. Transaction nature: atomicity,
Atomicity. That is to say, the transaction is either completely executed or not executed.
Consistency or stringability. Transaction execution converts the database from one correct state to another.
Isolation. Before a transaction is correctly committed, it is not allowed to provide any changes to the data of the transaction to any other transaction,
Durability. After a transaction is correctly committed, its results will be permanently stored in the database. Even if there are other faults after the transaction is committed, the transaction processing results will be saved.
10. Lock: Shared lock and mutex lock
Two lock protocols: Phase 1: Lock phase 2: Unlock phase
11. deadlock and processing: transactions wait for data locks in a loop, and deadlocks occur.
Deadlock handling: deadlock prevention protocol and deadlock recovery mechanism
12. Stored Procedures: stored procedures are compiled SQL statements.
1. The stored procedure runs fast because the SQL statement has been pre-compiled.
2. Data security and integrity can be ensured. Through the stored procedure, users without permissions can indirectly access the database under control, thus ensuring data security. Through the stored procedure, related actions can be taken together to maintain the integrity of the database.
3. reduces network traffic. The storage process mainly runs on the server to reduce the pressure on the client.
4: stored procedures can accept parameters, output parameters, return one or more result sets, and return values. The error cause can be returned to the program.
5: stored procedures can include program streams, logic, and queries to databases. Data logic can be encapsulated and hidden by entities.
13. Trigger: when the trigger conditions are met, the system automatically executes the trigger body of the trigger.
Trigger time: There are before, after. trigger events: There are three types: insert, update, and delete. Trigger type: line trigger and statement trigger
14. What is the difference between inner join and outer join?
The internal connection ensures that all rows in the two tables must meet the connection conditions, while the external connection does not.
In the outer join, some columns that are not qualified are displayed. That is to say, only the rows of one table are restricted, but the rows of the other table are not restricted. Left join, right join, and full join

Classic query:

1. An SQL statement interview question about group
Table content:
Victory
Victory
Negative
Negative
Victory
Negative
Negative
How do I write an SQL statement to generate the following results?
Win-win
2 2
1 2
------------------------------------------
Create table # tmp (rq varchar (10), shengfu nchar (1 ))
Insert into # tmp values ('2017-2005 ', 'sheng ')
Insert into # tmp values ('2017-2005 ', 'sheng ')
Insert into # tmp values ('2017-2005 ', 'negative ')
Insert into # tmp values ('2017-2005 ', 'negative ')
Insert into # tmp values ('2017-05-10 ', 'sheng ')
Insert into # tmp values ('2017-05-10 ', 'negative ')
Insert into # tmp values ('2017-05-10 ', 'negative ')
1) select rq, sum (case when shengfu = 'WINS 'then 1 else 0 end) 'wins', sum (case when shengfu = 'ned' then 1 else 0 end) 'Negative 'from # tmp group by rq
2) select N. rq, N. wins, M. Negative from (
Select rq, win = count (*) from # tmp where shengfu = 'sheng' group by rq) N inner join
(Select rq, negative = count (*) from # tmp where shengfu = 'negative 'group by rq) M on N. rq = M. rq
3) select a. col001, a. a1 wins, B. b1 is negative from
(Select col001, count (col001) a1 from temp1 where col002 = 'sheng' group by col001),
(Select col001, count (col001) b1 from temp1 where col002 = 'negative 'group by col001) B
Where a. col001 = B. col001
2. Ask an SQL statement query question during the interview.
The table has three columns a B c, which are implemented using SQL statements: When Column A is greater than Column B, select column A; otherwise, select Column B, if column B is greater than column C, column B is selected; otherwise, column C is selected.
------------------------------------------
Select (case when a> B then a else B end ),
(Case when B> c then B esle c end)
From table_name
3. interview question: an SQL statement for date judgment?
Please retrieve all records whose date (SendTime field) is the current day In the tb_send table? (The SendTime field is of the datetime type, including the date and time)
------------------------------------------
Select * from tb where datediff (dd, SendTime, getdate () = 0
4. There is a table with three fields: Chinese, mathematics, and English. There are 3 records indicating 70 points in Chinese, 80 points in mathematics, and 58 points in English, please use an SQL statement to query these three records and display them according to the following conditions (and write your ideas ):
If the value is greater than or equal to 80, it indicates excellent. If the value is greater than or equal to 60, it indicates passing the test. If the value is less than 60, it indicates failing.
Display format:
Chinese, mathematics, and English
Pass excellent fail
------------------------------------------
Select
(Case when language> = 80 then 'excellent'
When language> = 60 then 'pass'
Else 'failed') as language,
(Case when mathematics> = 80 then 'excellent'
When mathematics> = 60 then 'pass'
Else 'failed') as mathematics,
(Case when English> = 80 then 'excellent'
When English> = 60 then 'pass'
Else 'failed') as English,
From table
5. In sqlserver2000, please use SQL to create a user temporary table and a system temporary table, which contains two fields: ID and IDValues. The types are both int type. What are the differences between them?
------------------------------------------
User temporary table: create table # xx (ID int, IDValues int)
Temporary system table: create table # xx (ID int, IDValues int)
Differences:
The user temporary table is only visible to the user's Session who created the table and invisible to other processes.
The temporary table is automatically deleted when the process for creating it disappears.
The global temporary table is visible to the entire SQL Server instance, but it is automatically deleted when all sessions accessing it disappear.
6. sqlserver2000 is a large database. Its storage capacity is limited only by the storage media. How does it implement this unlimited capacity mechanism.
------------------------------------------
All its data is stored in the data file (*. dbf), so as long as the file is large enough, the storage capacity of SQL Server can be expanded.
The SQL Server 2000 database has three types of files:
Main data files
The main data file is the starting point of the database and points to other parts of the file in the database. Each database has a primary data file. The recommended file extension for main data files is. mdf.
Secondary data file
The secondary data file contains all data files except the primary data file. Some databases may have no secondary data files, while some databases have multiple secondary data files. The recommended file extension for secondary data files is. ndf.
Log Files
The log file contains all the log information required to restore the database. Each database must have at least one log file, but more than one. The recommended file extension for log files is. ldf.
7. Use an SQL statement to obtain the result.
Retrieve the data in the format listed in table3 from table1 and table2. Note that the provided data and results are not accurate, but you can ask for advice as a format.
You can also use the stored procedure.
Table1
Month mon Department dep performance yj
-------------------------------
February 01 10
February 02 10
March January
March 02 8
March February
March
Table 2
Department dep Department name dname
--------------------------------
01 domestic business 1
02 domestic business department 2
03 domestic business department 3
04 International Business Department
Table3 (result)
Department dep, January, January
--------------------------------------
01 10 null
02 10 8 null
03 null 5 8
04 null 9
------------------------------------------
1)
Select a. Department name dname, B. Performance yj as 'August 1', c. Performance yj as 'August 1', and d. Performance yj as 'August'
From table1 a, table2 B, table2 c, table2 d
Where a. Department dep = B. Department dep and B. Month mon = 'August 1' and
A. Department dep = c. Department dep and c. Month mon = 'August 1' and
A. Department dep = d. Department dep and d. Month mon = 'August 1' and
2)
Select a. dep,
Sum (case when B. mon = 1 then B. yj else 0 end) as 'August 1 ',
Sum (case when B. mon = 2 then B. yj else 0 end) as 'August 1 ',
Sum (case when B. mon = 3 then B. yj else 0 end) as 'August 1 ',
Sum (case when B. mon = 4 then B. yj else 0 end) as 'August 1 ',
Sum (case when B. mon = 5 then B. yj else 0 end) as 'August 1 ',
Sum (case when B. mon = 6 then B. yj else 0 end) as 'August 1 ',
Sum (case when B. mon = 7 then B. yj else 0 end) as 'August 1 ',
Sum (case when B. mon = 8 then B. yj else 0 end) as 'August 1 ',
Sum (case when B. mon = 9 then B. yj else 0 end) as 'August 1 ',
Sum (case when B. mon = 10 then B. yj else 0 end) as 'August 1 ',
Sum (case when B. mon = 11 then B. yj else 0 end) as 'August 1 ',
Sum (case when B. mon = 12 then B. yj else 0 end) as 'August 1 ',
From table2 a left join table1 B on a. dep = B. dep
8. Interview Questions from Huawei
The Id in a table has multiple records. All the records of this id are displayed, and the total number of records is displayed.
------------------------------------------
Select id, Count (*) from tb group by id having count (*)> 1

Select * from (select count (ID) as count from table group by ID) T where T. count> 1

Exam:

1. Are you familiar with databases SQL2005 and ORACLE?
SQL2005 is a database product of Microsoft. It is an RDBMS database, which is generally used in some medium-sized databases and cannot be used across platforms.
ORACLE is a data product of ORACLE. It supports massive data storage, distributed heat distribution, multi-user, cross-platform, and data security integrity control. It is an ORDBMS and is generally used in large companies.
2. Can I design a database? How to update Database Import and Export
The use of the POWERDISINE tool generally satisfies the third paradigm. Logical Import and Export of EXP and IMP Databases
3. How to show only duplicate data or not duplicate data
Show duplicate: select * from tablename groupby id having count (*)> 1
Duplicate: select * from tablename groupby id having count (*) = 1
4. What is database ing?
It is the process of maps the table and field of the database to the class name and attribute of the model layer.
5. What methods do you use to write paging data? Write a paging using SQL statements?
How do I use stored procedures to write pages?
Use TOP paging in SQLSERVER, ROWNUM in ORACLE, or the analysis function ROW_NUMBER
Use TOP:
Select top 20, n. * from tablename n minus select top 10, m. * fromtablename m
Use analysis functions:
Select * from
(Select n. *, row_number () over (order by columnname) num from tablename n)
Where num> = 10 and num <= 20;
When using the process, you only need to use two parameters for the paging range. In ORACLE, to encapsulate the process in the package, you must use a dynamic cursor variable to return the dataset.
6. Left and Right connections in ORACLE
Left join: left join right join: RIGHT JOIN
Select n. column, m. column from tablename1 n left join tablename2 m
Onn. columnname = m. columnname
Implement with WHERE:
Select n. column, m. column from tablename1 n, tablename2 m
Where n. columnname (+) = m. columnname
7. What are reflection, serialization, and deserialization? What are the transaction levels?
Reflection is a way to dynamically access DDL when the program is running. Serialization stores objects directly to files in binary, XML, and other ways. Deserialization is the process of extracting objects stored in files. There are three transaction levels: Page Level, application level, and database level.
8. How to test data testing?
In PLSQL, the process or function may pass through a dedicated test tool
9. When using transactions, if you call methods in the database access layer in the business logic layer, there are many classes in the access layer, and there are many methods in the class, each method must be implemented, so how?
Implementation of the Universal Data access layer
10. When will triggers be used?
A security management, B log management, and C complex business logic implementation
11. How to display tree control in the database? Use the parent ID and child ID to implement
12. How to optimize the database?
A. design the data structure adjustment. This part is completed before the development of the information system. Programmers need to consider whether to use the Partition Function of the ORACLE database and whether to create indexes for frequently accessed database tables.
B. Adjust the application structure design. This part is also completed before the development of information systems. In this step, programmers need to consider what kind of architecture the application uses. It uses the traditional Client/Server two-layer architecture, or use Browser/Web/Database's three-tier architecture. Different Application architectures require different database resources.
C. Adjust Database SQL statements. The execution of the application is ultimately attributed to the execution of SQL statements in the database. Therefore, the execution efficiency of SQL statements ultimately determines the performance of the ORACLE database. ORACLE recommends using ORACLE Optimizer and row-level manager to adjust and optimize SQL statements.
D. Adjust the server memory allocation. Memory Allocation is optimized during the operation of the information system. The database administrator can adjust the global zone of the database system (SGA) according to the database running status) the size of the data buffer, log buffer, and shared pool. You can also adjust the size of the global program area (PGA. Note that the larger the SGA area, the better. The higher the SGA area, the higher the memory occupied by the operating system during the Conference, resulting in Page Swap of virtual memory. This will reduce the system.
E. Adjust the hard disk I/O. This step is completed before the information system development. The database administrator can store data files in the same tablespace on different hard disks to achieve I/O load balancing between hard disks.
F. adjust operating system parameters. For example, for ORACLE databases running on UNIX operating systems, you can adjust the size of the UNIX data buffer pool and the memory size that each process can use.
13. What is the difference between EJB and java bean?
Java Bean is a reusable component and has no strict rules on JavaBean. Theoretically, any Java class can be a Bean. However, since Java Beans are created by containers (such as Tomcat), Java Beans should have a constructor without parameters. In addition, generally, Java Bean must implement the Serializable interface to realize Bean persistence. Java Bean is actually equivalent to the COM component in the local process in the Microsoft COM model. It cannot be accessed across processes. Enterprise Java Bean is equivalent to DCOM, which is a distributed component. It is based on Java remote method call (RMI) technology, so EJB can be remotely accessed (cross-process, cross-computer ). However, EJB must be deployed in containers such as Webspere and WebLogic. The EJB client never directly accesses the real EJB component but accesses it through its container.
The EJB container is the proxy of the EJB component. The EJB component is created and managed by the container. The customer accesses the real EJB component through the container.
14. Differences between forward and redirect
Forward is a server request resource. The server directly accesses the URL of the target address, reads the response content of that URL, and then sends the content to the browser, the browser does not know where the content sent by the server comes from, so its address bar is still the original address.
Redirect means that the server sends a status code based on logic to tell the browser to request the address again. Generally, the browser will re-request the address with all the parameters just requested, so the session and request parameters can be obtained.
15. Differences between HashMap and Hashtable.
HashMap is a lightweight Implementation of Hashtable (non-thread-safe implementation). They all complete the Map interface. The main difference is that HashMap allows null key values ), because of non-thread security, the efficiency may be higher than that of Hashtable.
HashMap allows null as the key or value of an entry, whereas Hashtable does not.
HashMap removes the contains method of Hashtable and changes it to containsvalue and containsKey. The contains method is easy to misunderstand.
Hashtable inherits from the Dictionary class, while HashMap is an implementation of the Map interface introduced by Java1.2.
The biggest difference is that the Hashtable method is Synchronize, but HashMap is not. When multiple threads access Hashtable, they do not need to implement synchronization for their own methods, hashMap must provide external synchronization for it.
The hash/rehash algorithms used by Hashtable and HashMap are roughly the same, so there is no big difference in performance.
16. What is the difference between VARCHAR and CHAR in the database type?
1. the length of char is fixed, while the length of varchar2 can be changed. For example, the storage string "abc", for char (20 ), it indicates that the characters you store will occupy 20 bytes (including 17 null characters), while the same VARCHAR2 (20) takes up only 3 bytes, and 20 is the maximum value, when the characters you store are less than 20 characters, they are stored according to the actual length. Char is of a fixed length, so it is much faster than varchar! However, it is a little troublesome for the program to process it. We need to use functions such as trim to remove spaces on both sides! 1. the length of char is fixed, while the length of varchar2 can be changed. For example, the storage string "abc", for char (20 ), it indicates that the characters you store will occupy 20 bytes (including 17 null characters), while the same VARCHAR2 (20) takes up only 3 bytes, and 20 is the maximum value, when the characters you store are less than 20 characters, they are stored according to the actual length. Char is of a fixed length, so it is much faster than varchar! However, it is a little troublesome for the program to process it. We need to use functions such as trim to remove spaces on both sides!
2. CHAR is more efficient than VARCHAR2.
3. Currently, VARCHAR is a synonym for varchar2. The industry standard VARCHAR type can store null strings, but oracle does not, although it reserves the right to do so in the future. Oracle has developed a data type VARCHAR2, which is not a standard VARCHAR. It changes the feature that can store NULL strings in the varchar column of the database to store NULL values. If you want backward compatibility, we recommend that you use VARCHAR2 instead of VARCHAR.
17. How to insert single quotes to database tables
It can be processed using ASCII codes. Other special characters such as & are also the same, such as: insert into t values ('I 'chr (39) 'M'); -- chr (39) represents the character 'or represents one or insert into t values (' I 'M ') using two single quotes; -- two ''can represent one'
18. How can I obtain the day of the week and use other date functions?
(1) to_char can be used to solve the problem, such as: select to_char (to_date ('2017-08-26 ', 'yyyy-mm-dd'), 'day') from dual;
(2) You can SET the date language before obtaining it, for example, alter session set NLS_DATE_LANGUAGE = 'American ';
(3) specify in the function, such as: select to_char (to_date ('2017-08-26 ', 'yyyy-mm-dd'), 'day ', 'nls _ DATE_LANGUAGE = American ') from dual;
(4) For more methods, see the to_char and to_date functions to obtain the complete time format select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss ') from dual; (5) Just introduce the usage of several other functions: SELECT to_char (last_day (SYSDATE), 'dd') for the number of days this month ') days FROM dual this year's days select add_months (trunc (sysdate, 'Year'), 12)-trunc (sysdate, 'Year ') from dual next Monday's date SELECT Next_day (SYSDATE, 'monday') FROM dual know the date of birth, how to find the age?
19. Calculate the date at the end of last month.
Today, July 22, April 23. What is the number at the end of last month? (March 31) select to_char (trunc (sysdate, 'mm')-1, 'yyyy-MM-DD ') from dual; select trunc (sysdate, 'mm')-1 from dual; select to_char () from dual;