Claim:
SQL stored procedure
purpose:
To take out each code 'announcement date', the closing price of 10 trading days before and after, if less than 10 trading days, the price of the day will be replaced by "-95". The results are deposited in the "result" (new generation) table. The table "result" structure is code char (6), the trading date DateTime, the closing price Numbric5 (9, 4).
example:
The date of publication of the code 000001 is 2003-4-24, judges in the trading calendar 2003-4-24, the trading day, such as the trading day, 5 trading days before 2003-4-24 (note is the trading day , From time to time, can pass cum_days
The field to determine the number of days); such as 2003-4-24 Do not take a recent trading day in the transaction calendar, as the reference date, then take the 5 trading date before and after the reference date.
relationship:
1. The trading date in the "Trading Calendar" table is identical to the trading date in the "Price" table. That is, every grid date in the "Trading Calendar" table can find the corresponding trading date in the "price" table.
2. Table The code in the "Date of Announcement" is equivalent to the code in the "price" table.
Below is a database education, the data is watching it.
/ ****** Object: Database Test Script date: 2005-2-28 8:34:37 ****** /
If exists (select name from master.dbo.sysdatabases where name = n'test ')
DROP DATABASE [TEST]
Go
Create Database [TEST] ON (Name = n'test_data ', filename = n'c: / program files / microsoft sql server / mssql / data / test_data.mdf', size = 5, FileGrowth = 10%) log on (Name = N'test_log ', filename = n'c: / program files / microsoft SQL Server / MSSQL / DATA / TEST_LOG.LDF', SIZE = 4, FileGRowth = 10%)
Go
Exec sp_dboption n'test ', n'autoclose', n'true '
Go
EXEC SP_DBOPTION N'TEST ', N'BULKCOPY', N'FALSE '
Go
EXEC SP_DBOPTION N'TEST ', N'TRUNC. LOG', N'TRUE '
Go
EXEC SP_DBOPTION N'TEST ', N'TORN Page Detection', N'true '
Go
EXEC SP_DBOPTION N'TEST ', N'READ ONLY', N'FALSE '
Go
Exec sp_dboption n'test ', n'dbo us', n'false'
Go
Exec sp_dboption n'test ', n'single', n'false'go
Exec sp_dboption n'test ', N'Autoshrink', N'true '
Go
EXEC SP_DBOPTION N'TEST ', N'SI Null Default', N'False '
Go
EXEC SP_DBOPTION N'TEST ', N'Recursive Triggers', N'FALSE'
Go
Exec sp_dboption n'test ', n'ansi nulls', n'false'
Go
EXEC SP_DBOPTION N'TEST ', N'CONCAT NULL YIELDS NULL', N'FALSE '
Go
EXEC SP_DBOPTION N'TEST ', N'CURSOR Close on Commit', N'False '
Go
Exec sp_dboption n'test ', N'Default to Local Cursor', N'FALSE '
Go
EXEC SP_DBOPTION N'TEST ', N'QUOTED IDENTIFIER', N'FALSE '
Go
EXEC SP_DBOPTION N'TEST ', N'SI WARNINGS', N'FALSE '
Go
EXEC SP_DBOPTION N'TEST ', N'AUTO CREATE STATISTISTIS', N'TRUE '
Go
EXEC SP_DBOPTION N'TEST ', N'AUTO UPDATE STATISTICS', N'TRUE '
Go
Use [test]
Go
/ ****** Object: table [dbo]. [Transaction calendar $] script date: 2005-2-28 8:34:40 ****** /
IF exists (Select * from dbo.sysObjects where id = Object_id (n '[dbo). [Trading calendar $]') And ObjectProperty (id, n'susertable ') = 1)
Drop Table [DBO]. [Transaction Calendar $]
Go
/ ****** Object: table [dbo]. [Price] Script date: 2005-2-28 8:34:40 ****** /
if EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[dbo]. [price]') And ObjectProperty (ID, n'susertable ') = 1)
DROP TABLE [DBO]. [Price]
Go
/ ****** Object: table [dbo]. [Announcement date $] script date: 2005-2-28 8:34:40 ****** / if EXISTS (SELECT * from DBO.SYSOBJECTS WHERE ID = Object_ID (N '[DBO]. [Announcement Date $]') And ObjectProperty (ID, N'susertable ') = 1)
Drop Table [DBO]. [Announcement Date $]
Go
/ ****** Object: table [dbo]. [Result] Script date: 2005-2-28 8:34:40 ****** /
if EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[dbo]. [Results]') And ObjectProperty (ID, n'susertable ') = 1)
Drop Table [DBO]. [Result]
Go
/ ****** Object: User DBO Script Date: 2005-2-28 8:34:38 ****** /
/ ****** Object: User Guest Script Date: 2005-2-28 8:34:38 ****** /
If not exists (select * from dbo.sysusers where name = n'Guest 'and uid <16382 and hasdbaccess = 1)
EXEC SP_GRANTDBACCESS N'GUEST '
Go
/ ****** Object: table [dbo]. [Transaction calendar $] script date: 2005-2-28 8:34:41 ***** /
Create Table [DBO]. [Trading Calendar $] (
[trade_date] [smalldatetime] null,
[cum_days] [float] NULL
) On [primary]
Go
/ ****** Object: Table [DBO]. [Price] Script Date: 2005-2-28 8:34:42 ****** /
Create Table [DBO]. [Price] (
[Stock code] [NVARCHAR] (255) NULL,
[Trading Time] [SmallDateTime] NULL,
[Closed price] [float] null
) On [primary]
Go
/ ****** Object: table [dbo]. [Announcement date $] script date: 2005-2-28 8:34:42 ****** /
CREATE TABLE [DBO]. [Announcement Date $]
[Code] [NVARCHAR] (255) NULL,
[Announcement Date] [SmallDateTime] NULL
) On [primary]
Go
/ ****** Object: table [dbo]. [Result] Script date: 2005-2-28 8:34:42 ****** /
CREATE TABLE [DBO]. [Result] (
[Stock code] [NVARCHAR] (255) NULL,
[Trading time] NULL, [Closed price] [float] null
) On [primary]
Go
============================================================================================================================================================================================================= ================================
This may be the article you have been looking forward to, while paying attention to this technical problem, please be sure to read questions and answers about individuals in the interview. The answer here is not very comprehensive, these issues can be explained by multiple angles. Maybe you don't have to give a completely detailed answer during the interview process, just need to make the interview examinations to understand your familiarity of Oracle concept. .
1. Explain the different points of the cold backup and hot spare and their own advantages
Solution: The hot backup for the archive mode, the database is still backup when the database is still working. The cold backup refers to the backup after the database is closed, and it is suitable for databases of all modes. The advantage of the thermal spare is that the database can still be used when backup and can restore the database to any point in time. The advantage of the cold backup is that its backup and recovery operation is quite simple, and the database performance is slightly better than the archive mode due to the database of the cold backup. (Because you don't have to write Archive Log to your hard drive)
2. You must use the backup to recover the database, but you don't control the file, how to solve the problem?
Solution: Reconstruction Control File, recover the database with the Recover command with the Backup Control File clause.
3. How to convert init.ora to SPFILE?
Solution: Use the Create SPFile from Pfile command.
4. Explain the difference between Data Block, Extent and Segment (herein, English terms)
Solution: Data Block is the smallest logical storage unit in the database. When the object of the database requires more physical storage space, consecutive Data Block constitutes an extent. A database object has all ExtensS called the object's segment.
5. Give two methods of checking table structure
Answer: 1.Describe command
2.DBMS_METADATA.GET_DDL package
6. How to view the error of the database engine
Solution: Alert log.
7. Compare Truncate and Delete Commands
Solution: Both can be used to delete all records in the table. The difference is that Truncate is DDL operation, it moves HWK, does not require rollback segment. And DELETE is DML operation, which requires Rollback Segment and spends longer.
8. Reasons for using indexes
Answer: Quick access to Data Block
9. Given the two tables in Star Schema and their respective data
Solution: Fact Tables and Dimension Tables. Fact Table contains a lot of major information and DIMENSION TABLES is stored for Fact Table Some attributes Description Description 10. What indexes need to establish on Fact Table?
Answer: Bitmap Index)
11. Give two related constraints?
Answer: Primary key and foreign key
12. How to rebuild a master table without affecting the child table
Solution: The foreign key of the sub-table is enforced, reconstructed the master table, and the foreign key is activated.
13. Interpret the difference between archiving and non-archive mode and their respective advantages and disadvantages
Answer: Archiving mode means you can back up all database transactions and return to any point in time. In contrast, the non-invasive mode cannot be recovered to any time point. However, non-archiving patterns can bring a little increase in database performance.
14. How to build a backup control file?
Answer: Alter Database Backup Control File to TRACE.
15. How many states have been introduced by the database normal startup?
Solution: Startup Nomount - Database instance start
STARTUP MOUNT - Database Database
STARTUP OPEN - Database Open
16. Which Column can be used to distinguish between V $ view and GV $ view?
Answer: INST_ID indicates a specific INSTANCE in the cluster environment.
17. How to generate an Explain Plan?
Solution: Run UTLXPLAN.SQL. Create a Plan table
Using Explain Plan Set Statement_ID = 'TST1' INTO PLAN_TABLE for a specific SQL statement
Run UTLXPLP.SQL or UTLXPLS.SQL to view Explain Plan
18. How to increase the hit rate of buffer cache?
Solution: When the database is busy, the Buffer Cache Advisory tool is applied, query V $ db_cache_advice. If necessary, you can use the ALTER System Set DB_Cache_SIZE command.
19. What is the right method of ORA-01555?
Solution: The specific error message is Snapshot Too Old Within Rollback SEG, you can usually solve the problem by increasing Rollback Seg. Of course, you need to look at the specific SQL text that causes the error.
20. Interpret $ Oracle_Home and $ ORACLE_BASE?
Solution: Oracle_base is the root of Oracle, Oracle_Home is the directory of Oracle products.
