DB-common SQL Accumulation

1. Find the number of repeated records for the specified field in the table
Answer:Select [FIeld1], [FIeld2], [FIeld3], [...], Count (*) from [tablename] Where [condition] group by [FIeld1], [FIeld2], [FIeld3], [...] Having count (*)> 1
2. TableThe latest X of every Y (ID is the master key and auto-increment)
Answer: select   ID, Y, X from Tablename t where ( Not   Exists (Select 1   From tablename T2 where (T2.y = T.y) And (T2.x > T. X Or T2.x = T. X And T2.id > T. ID )))
3. Table: Table1 (FID, fclass, FSCORE). The most efficient and simple SQL statement is used to list the highest scores of each class. Two fields are displayed: class and score.
Answer: Select fclass, max (FSCORE) from Table1 group by fclass
4. There is a table in table 1 with two fields FID, fno, and the word is not empty. Write an SQL statement to list records with multiple fno records corresponding to one FID in the table.

• example
  class:
  large
  101a1001
  102a1002
  102a1003
  103a1004
  large a1005
  large a1006
  105a1007
  105a1007
  105a1007
  result:
  102a1002
  102a1003
  2017a1005
  2017a1006
  

• answer: select T2. * From Table1 T1, table1 T2 where t1.fid = t2.fid and t1.fno <> t2.fno;

5. empinfo
(
Fempno varchar2 (10) Not null PK,
Fempname varchar2 (20) not null,
Fage number not null,
Fsalary number not null
);
Assume that there are about 10 million data records. Write an SQL statement that you think is the most efficient. Use one SQL statement to calculate the following four types of users:
Fsalary> 9999 and Fage> 35
Fsalary> 9999 and Fage <35
Fsalary <9999 and Fage> 35
Fsalary <9999 and Fage <35
The number of employees;
Answer
Select sum (case when fsalary> 9999 and Fage> 35 then 1 else 0end) as "fsalary> 9999_fage> 35 ",
Sum (case when fsalary> 9999 and Fage <35 then 1 else 0 end) as "fsalary> 9999_fage <35 ",
Sum (case when fsalary <9999 and Fage> 35 Then 1 Else 0 End) as "fsalary <9999_fage> 35 ",
Sum (case when fsalary <9999 and Fage <35 Then 1 Else 0 End) as "fsalary <9999_fage <35"
From empinfo;

6. The fields in Table A are as follows:
Month person income
Monthly staff income
You must use an SQL statement (note one) to obtain the total revenue of all people (regardless of personnel) for each month, last month, and next month.
The output of the requirement list is
Month: When the monthly income is higher than the monthly income
Months person income
200807 mantisxf 5000
200806 mantisxf2 3500
200806 mantisxf3 3000
200805 mantisxf1 2000
200805 mantisxf6 2200
200804 mantisxf7 1800
200803 8 mantisxf 4000
200802 9 mantisxf 4200
200802 10 mantisxf 3300
200801 11 mantisxf 4600
200809 11 mantisxf 6800
Answer:
Select (select month from a where month = to_char (sysdate, 'mm') month,
(Select sum (income) from a where month = to_char (sysdate, 'mm') when monthly income,
(Select sum (income) from a where to_number (month) = to_number (extract (month from sysdate)-1) monthly income,
(Select sum (income) from a where to_number (month) = to_number (extract (month from sysdate) + 1) Monthly Income
200801 4600 0 7500
200802 7500 4600 4000
200803 4000 7500 1800
200804 1800 4000 4200
200805 4200 1800 6500
200806 6500 4200 5000
200807 5000 6500 0
200809 6800 0 0

7.Table B
C1 C2
2005-01-01
2005-01-01 3
2005-01-02 5
Required data
2005-01-01 4
2005-01-02 5
Total 9
Try an SQL statement.
Answer:
Select TT. C1 as 'date', sum (TT. C2) as 'Total'
From B TT
Group by TT. C1
Union
Select 'Total' as 'date', sum (TT. C2) as 'Total'
From B TT

8. Concepts and understanding of database 1, 2, and 3 paradigms.
When designing relational databases, you must follow certain rules. In particular, the database design paradigm 1nf (first paradigm), 2nf (second paradigm), and 3nf (third paradigm ).

• 1nf: The first paradigm emphasizes the atomicity of the column, that is, the column cannot be further divided into several other columns.
  name class score
  If the score has a mathematical score, an English score, or a Chinese score, the score does not meet the first paradigm, because the score column can score again.
• second Paradigm (2nf): Based on 1nf, the table must have a primary key; second, columns not included in the primary key must be completely dependent on the primary key, rather than only a portion of the primary key.
  orderid productid unitprice discount quantity productname
  the primary key is composed of orderid and productid. The discount (discount) and quantity (Quantity) depend entirely on the primary key (oderid, productid), while unitprice and productname only depend on productid. Therefore, the orderdetail table does not conform to 2nf.
  
• 3nf: Based on 2nf, non-primary key columns must directly depend on the primary key, the transfer dependency cannot exist. That is, it cannot exist: non-primary key column A depends on non-primary key column B, and non-primary key column B depends on the primary key.
  orderid orderdate customerid customername customeraddr customercity
  non-primary key columns such as orderdate, customerid, customername, customeraddr, and customercity depend entirely on the primary key (orderid), so 2nf is met. However, the problem is that customername, customeraddr, and customercity depend directly on customerid (non-primary key column) instead of directly relying on the primary key. It depends on the primary key through transmission, so it does not conform to 3nf.

9. Briefly describe the concept and restrictions of the Oracle row trigger change table. What are the restrictions on these two tables in the row trigger.

• Mutating table: The table being modified by the DML statement
  The table that needs to be updated as a delete cascade reference results of integrity restrictions also changes
  Restriction: for the session itself, the table in change cannot be read.
• Restricted table constraining table: The table for which read operations are performed on the reference integrity restriction.
  Restriction: if the restriction column is being changed, an error is triggered when reading or modifying the restriction column, but modifying other columns is allowed.
  

10. How many temporary Oracle tables are available? What are the main differences between a temporary table and a common table? What are the main reasons for using a temporary table?

• Session-specific temporary table
  Session-level temporary table data exists throughout the session until the session ends.
  
• Temporary tables specific to transactions
  The temporary table data at the transaction level disappears after the tranaction ends. That is, the temporary table data in the commit/rollback or end session is cleared.
• Temporary tables are saved in memory. Normal tables store data for a long time and can be read and written at any time.
• Temporary tables are mainly used to improve query efficiency.

11. How to implement the global variables that can be accessed by multiple process functions or triggers executed in a session, and achieve inter-session isolation?
Oracle DatabaseProgramThe variables in the package can be directly referenced in this package, but cannot be directly referenced outside the package. The access to the package variables can be implemented by matching the corresponding Stored Procedure <used to store data> and function <used to read data> for each variable.

12. both AA and BB tables have 20 fields and a large number of records. The X fields of AA and BB tables (not empty) have indexes, use SQL to list the values of X in the AA table that does not exist in the BB table. Write the fastest statement and explain the cause.
Select AA. X from AA where not exists (select 'x' from BB where AA. x = BB. X );
Both the X indexes in AA and BB are used.

13. Briefly describe the main structure and application of SGA?
SGA is a group of shared memory buffers allocated by Oracle for an instance. It contains the data and control information of the instance. SGA is automatically allocated when the instance is started, and is withdrawn when the instance is closed. All database data operations must be performed through SGA.
The memory in SGA can be divided into the following areas based on the storage information:

• Buffer cache: stores copies of database blocks in the database. It is composed of a group of buffer blocks, which are shared by all user processes linked to the instance. The number of buffer blocks is determined by the initialization parameter db_block_buffers. The size of the buffer block is determined by the initialization parameter db_block_size. Large data blocks increase the query speed. It is operated by dbwr.
• Log buffer redo log Buffer: stores changes to data operations. They are stored in the log buffer as a log entry. When you need to recover the database, log entries are used to reconstruct or roll back the changes made to the database. The size of the log buffer is determined by the initialization parameter log_buffer. A large log buffer can reduce the number of log file I/O times. The background process lgwr writes the information in the log buffer to the log file on the disk. You can start the arch background process to archive the log information.
• Shared Pool: Contains SQL statement information for processing. It includes the shared SQL area and data dictionary storage area. The shared SQL area contains the information used to execute specific SQL statements. The data dictionary area is used to store data dictionaries, which are shared by all user processes.

 

14. What is a partition table? Briefly describe the differences between range partitions and list partitions. What are the main advantages of partition tables?
A partition table is called a partition table.
Range partition: Each partition is specified by a partition key value range. (For a table that uses a date column as the partition key, the "January 2005" partition contains the partition key value from "January 1, 2005"
To the line of "January 31, 2005 ).
List partition: Each partition is specified by a list of partition key values (for a table with a region column as the partition key, the "North America" partition may contain values "Canada", "United States", and "Mexico ").
By improving manageability, performance, and availability, the partitioning function brings great benefits to various applications. In general, partitions can greatly improve the performance of some queries and maintenance operations. In addition, partitions can greatly simplify common management tasks. Through partitioning, database designers and administrators can solve some of the challenges posed by cutting-edge applications. Partitioning is a key tool for building a gigabit data system or an ultra-high availability system.

15. background: A certain data runs in archivelog, and RMAN is used for full backup and cold backup of the database. All archived logs exist. All the control files are damaged, and all other files are intact, I would like to explain how to restore the database. One or two methods are provided.

 

• Use cold backup to directly copy all cold backup files to the original directory.
• Use archive logs
  Start database nomount
  Create a control file and specify the location of the data file and redo log file.
  Use the recover database using backup controlfile until cancel command to reply to the database. Archive logs can be used at this time.
  Aleter database open resetlogs;
  Back up database and control files again

16. Use RMAN to write a backup statement: Backup Incremental backup of table space TSB with Level 2.

17. There is a table a (x number (20) and Y number (20) that inserts 10 million consecutive records starting from 1 into the table with the fastest and most efficient SQL statement.
Alter table a nologging;
Insert/* + append */A select/* + parallel (T, 8) */rownum from dual t connect by level <= 10000000; commit;
18. Query Optimization

• optimize database queries to avoid full table scans, first, you should consider creating an index on the columns involved in where and order by
• avoid null values in the WHERE clause, otherwise, the engine will discard the index and perform a full table scan.
  select ID from t where num is null
  
• avoid using the WHERE clause whenever possible! = Or <> operator. Otherwise, the engine will discard the index for full table scanning
• try to avoid using or in the WHERE clause for join conditions, otherwise, the engine will discard the index and perform a full table scan
• use in and not in with caution, otherwise, the entire table is scanned.
• if you use a parameter in the WHERE clause, this will also cause a full table scan
• avoid expression operations on fields in the WHERE clause, this will cause the engine to stop using the index for full table scanning
• do not perform function operations on fields in the WHERE clause, this will cause the engine to stop using the index for full table scanning

19. Four features of database transactions
Atomicity
Consistency
Separation
Durability

 

 

 

 

 

 

 

To be read:Http://www.cnblogs.com/tylerdonet/archive/2011/10/10/2205859.html
Rollup usage in SQL: http://blog.csdn.net/dyufei/article/details/4801283
From: http://www.cnblogs.com/qiangqiang/archive/2010/10/15/1852252.html