To organize the Oracle pen questions of large companies with reference answers

NVL (name,0) empty then take 0. SUSTRB (manth,1,4) first started to take 4 digits. Decode (je,1,1,2), je 1, displays 1, or 2.

Select Classno, AVG (score) from student group by Classno has avg (score) = (select Max (avg (score)) from student GROUP by CLASSNO);

CREATE TABLE Space Neuspace, the data file named neudata.dbf, stored in the D:data directory, file size 200MB, set to automatic growth, increment 5MB, file maximum of 500MB. (8 points)

A: Create tablespace neuspace datafile ' d:dataneudata.dbf ' size 200m auto extend on next 5m maxsize 500m;

2. Assuming that the table space Neuspace has exhausted 500MB space, is required to add a data file, stored in the E:appdata directory, file name Appneudata, the size of 500MB, does not automatically grow. (5 points)

Answer: Alter tablespace neuspace add datafile ' e:appdataappneudata.dbf ' size 500m;

3. Log in as a system administrator, create an account Tom, set Tom's default Tablespace to Neuspace. Assign the Connect and resource system roles for Tom to get basic system privileges. Then assign Tom a SELECT permission to the table EMP for user Scott and update permissions on the salary, Mgr property. (8 points)

A: Create user Tom identified by Jack default Tablespace neuspace;

Grant Connect, resource to Tom;

Grant Select, update (Salary, MGR) on scott.emp to Tom;

4. CREATE table class and student as per the following requirements. (15 points)

Attribute type (length) default value constraint meaning

Classno value (2) No primary key class number

CNAME variable length character (10) Nothing but empty class name

Attribute type (length) default value constraint meaning

Stuno Value (8) No primary key number

Sname variable length character (12) nothing but empty name

Sex characters (2) male without sex

Birthday date with no birthdays

Email variable length character (20) no unique email

Score Value (5, 2) No check score

Classno value (2) No foreign key, Classno primary key class number associated to table class

Answer: CREATE TABLE Class

(Classno number (2) Constraint CLASS_CLASSNO_PK primary key,

CNAME VARCHAR2 (a) not null);

CREATE TABLE Student

(Stuno number (8) Constraint STUDENT_STUNO_PK primary key,

Sname VARCHAR2 (a) NOT NULL,

Sex char (2) Default ' male ',

Birthday date,

email varchar2 (constraint) Student_email_uk unique,

Score number (5,2) constraint student_score_ck check (score>=0 and score<=100),

Classno number (2) constraint STUDENT_CLASSNO_FK references class (CLASSNO)

);

5. Create an index on the Sname property of table student Student_sname_idx (5 points)

A: Create INDEX student_sname_idx on student (sname);

6. Create sequence stuseq, require initial value is 20050001, increment is 1, maximum is 20059999. (6 points)

A: Create sequence stuseq increment by 1-start with 20050001 maxvalue 20059999 NoCache;

7. Insert the following 2 lines into the table student. (5 points)

Stunosnamesexbirthdayemailscoreclassno

From Stuseq Tom man 1979-2-3 14:30:25tom@163.net89.501

From Stuseq value Jerry default empty 2

A: INSERT into student values (Stuseq.nextval, ' Tom ', ' Man ', To_date (' 1979-2-3

14:30:25 ', ' yyyy-mm-dd fmhh24:mi:ss '), ' tom@163.net ', 89.50, 1;

INSERT into student (Stuno, sname, Classno) VALUES (Stuseq.nextval, ' Jerry ', 2);

8. Revise the data of table student to add 10 points to all class students. (4 points)

Answer: Update student set score=score+10 where classno=1;

9. Delete the data from the table student and delete all 3 classes with a record date of birth less than May 12, 1981. (4 points)

Answer: Delete from student where classno=3 and birthday > ' December-May-81 ';

10. Complete the following SQL statement. (40 points)

(1) Sorted by class ascending order, the results are sorted in descending order, all records of student table are queried.

A: SELECT * from Student ORDER by Classno, score desc;

(2) Check the records of boys in the student table with a score greater than 85.50 and a date of birth greater than 1982-10-31 days.

A: SELECT * from student where classno=2 and score>85.50 and Birthday < ' 3 January-October -82 ' and sex= ' men ';

(3) The student record of all three classes in the Student table is blank.

A: SELECT * from student where classno=3 and score are null;

(4) Table student and class joint query, ask all students to learn the number, name, Grade, class name. (using Oracle and SQL 992 formats)

Answer: Select S.stuno, S.sname, S.score, c.cname from student S, Class C where s.classno=c.classno;

(5) According to the class number grouped statistics of the number of each class, the highest score, the lowest points, the average score, and in descending order by average.

A: Select Classno, COUNT (*), Max (score), Min (score), AVG (score) from student group by Classno ORDER by AVG (score) desc;

(6) To check the records of all the students in a class with the average score above the class.

A: SELECT * from student where classno=1 and score > (select AVG (score) from student where classno=1);

(7) Statistics on the number of students in class two are greater than the average score of all classes.

A: SELECT COUNT (*) from student where classno=2 and score > All (select AVG (socre) from student group by Classno);

(8) The highest average number of classes and points.

A: Select Classno, AVG (score) from student group by Classno has avg (score) = (select Max (avg (score)) from student group by Classno);

(9) Check the student's number, name, Grade and class number of the top 10 students in the record.

A: Select Stuno, sname, score, Classno from (SELECT * to student ORDER BY score Desc) where rownum<=10;

(10) Create a view stuvu that contains the Stuno, sname, score, Classno four properties of all classes of students in the student table and the WITH CHECK option limit.

Answer: CREATE VIEW Stuvu

As

Select Stuno, Sname,score,classno from student where classno=1 with CHECK option;

1. Compare size

Select decode (sign (variable 1-variable 2),-1, variable 1, variable 2) from dual; – Take a smaller value

The sign () function returns 0, 1, and 1, depending on whether a value is 0, positive, or negative.

For example:

Variable 1=10, variable 2=20

Then sign (variable 1-variable 2) returns the -1,decode decoding result as "Variable 1", which achieves the goal of taking a smaller value.

2, table, view structure transformation

There is an existing sales table sale, the table structure is:

Month char (6) – Month

Sell Number (10,2) – Monthly sales Amount

The existing data is:

200001 1000

200002 1100

200003 1200

200004 1300

200005 1400

200006 1500

200007 1600

200101 1100

200202 1200

200301 1300

Data that you want to translate into the following structure:

Year char (4) – Years

Month1 Number (10,2) – January Sales Amount

Month2 Number (10,2) – February Sales Amount

Month3 Number (10,2) – March Sales Amount

Month4 Number (10,2) – April Sales Amount

Month5 Number (10,2) – May Sales Amount

Month6 Number (10,2) – June Sales Amount

Month7 Number (10,2) – July Sales Amount

Month8 Number (10,2) – August Sales Amount

Month9 Number (10,2) – September Sales Amount

Month10 Number (10,2) – October Sales Amount

Month11 Number (10,2) – November Sales Amount

Month12 Number (10,2) – December Sales Amount

The SQL statement for structural transformation is:

Create or Replace view

V_sale (YEAR,MONTH1,MONTH2,MONTH3,MONTH4,MONTH5,MONTH6,MONTH7,MONTH8,MONTH9,MONTH10,MONTH11,MONTH12)

As

Select

SUBSTRB (month,1,4),

SUM (Decode (SUBSTRB (month,5,2), ' 01′,sell,0 '),

SUM (Decode (SUBSTRB (month,5,2), ' 02′,sell,0 '),

SUM (Decode (SUBSTRB (month,5,2), ' 03′,sell,0 '),

SUM (Decode (SUBSTRB (month,5,2), ' 04′,sell,0 '),

SUM (Decode (SUBSTRB (month,5,2), ' 05′,sell,0 '),

SUM (Decode (SUBSTRB (month,5,2), ' 06′,sell,0 '),

SUM (Decode (SUBSTRB (month,5,2), ' 07′,sell,0 '),

SUM (Decode (SUBSTRB (month,5,2), ' 08′,sell,0 '),

SUM (Decode (SUBSTRB (month,5,2), ' 09′,sell,0 '),

SUM (Decode (SUBSTRB (month,5,2), ' 10′,sell,0 '),

SUM (Decode (SUBSTRB (month,5,2), ' 11′,sell,0 '),

SUM (Decode (SUBSTRB) (month,5,2), ' 12′,sell,0 ')

From sale

Group by SUBSTRB (month,1,4);

79, the use of case statements?

Oracle usage is simple:

SELECT last_name, job_id, salary

Case job_id

When ' It_prog ' THEN 1.10*salary

When ' St_clerk ' THEN 1.15*salary

When ' Sa_rep ' THEN 1.20*salary

ELSE salary End "Revised_salary"

From Employees

80, the difference between truncate and delete?

1, truncate on all kinds of tables, whether large or small are very fast. If a rollback command delete is revoked, the truncate is not revoked.

2. Truncate is a DDL language and delete is a DML statement, and, like all other DDL languages, he will be implicitly committed and cannot use rollback commands against truncate.

3, TRUNCATE will reset the high-level line and all indexes. When you fully browse the entire table and index, the table after the truncate operation is much faster than the table after the delete operation.

4, TRUNCATE cannot trigger the trigger, delete triggers the trigger.

5, can not give anyone the right to empty other people's table.

6, when the table is emptied after the table and table index is reset to the initial size, and delete is not.

7, can not empty the parent table.

81, how to expand the table space and write with the statement?

Two ways to extend:

(a) Increased number of data files

Alter tablespace tablespace_name add datafile ' XXMB

b Extended Data File size

ALTER DATABASE datafile ' Resize NEWMB

82. How to manage the table space area? which is the recommended use now?

A) dictionary management methods

extent management Dictionary; default method

(b) Local management methods

Extent management Local[autoallocate/uniform XXMB];

83, with what function to get the date? and the date of the month, day, year

To_char (Sysdate, ' year '): Tow Thsound Six to_char (sysdate, ' yyyy '): 2006

To_char (sysdate, ' month '): August to_char (sysdate, ' mm '): 08

To_char (sysdate, ' Day '): Week 4 to_char (sysdate, ' DD '): 22

84, the application of the partition table?

A A partitioned table has one or more partitions, each partition by using a range partition, a hash partition, or a combination of partition's rows

b each partition in the partitioned table is a segment and can be located in a separate table space

C is useful for large table partitions that can be queried or manipulated with several processes at once

85, talk about the use and principle of indexing?

An index is a list of keywords for several data rows that, when queried for data, can be quickly positioned through the keywords in the index to the block of data to which the record is to be accessed, greatly reducing the number of I/O to the data block, and thus significantly improving performance.

86, the application of stored procedures, how to both input and output?

Create procedure Pro_name

(xxxx in/out type;

yyyy in/out/inout type;

) Is/as

ZZZZ type;

Begin

Sqlpro;

exception

exceptionxxxxx;

Commit

End

87. What are the anomalies that occur frequently?

Common predefined exceptions

cursor_already_open-ora-06511 SQLCODE =-6511 cursor already open

dup_val_on_index-ora-00001 SQLCODE =-1 violation of uniqueness constraint

invalid_cursor-ora-01001 SQLCODE =-1001 Illegal cursor operation

invalid_number-ora-01722 SQLCODE =-1722 character failed to convert to number

login_denied-ora-01017 SQLCODE =-1017

no_data_found-ora-01403 SQLCODE = +100 No data found

not_logged_on-ora-01012 SQLCODE =-1012 not connected to database

program_error-ora-06501 SQLCODE =-6501 Internal Error

storage_error-ora-06500 SQLCODE =-6500

timeout_on_resource-ora-00051 SQLCODE =-51

too_many_rows-ora-01422 SQLCODE =-1422 return multiple rows

transaction_backed_out-ora-00061 SQLCODE =-61

value_error-ora-06502 SQLCODE =-6502 numeric conversion error

Access_into_null attempted to assign a value to a property of a null object

zero_divide-ora-01476 SQLCODE =-1476 by 0 apart

others-any other error handling

88, how to use the exception?

There are three types of exceptions in Oracle. Predefined exceptions non-predefined exception user-defined exceptions the second, predefined exception is associated with a specific Oracle error. and is associated with PRAGM Exception_init (Exception_name,error_number). But what's the use? For example: Declare Dup_primary_key exception; pragma exception_init (dup_primary_key,-1); Begin INSERT into itemfile values (' i201′, ' washer ', ' spares ', 100,50,250,12,30); Exception when Dup_primary_key then Dbms_output.put_line (' Duplicate item number-primary key conflict '); End

The first way to use: exception

When exception name then

Exception handling code;

The third usage: if condition then

Raise_application_error (-20000 "" ""-20999, hint information);

End If;

89, the optimized strategy generally includes:

• Memory optimization

• Operating system optimization

• Optimization of data storage

• Network optimization and other methods

Specific to the different database involves adjusting different database configuration files, different operating system parameters, network parameters, and so on, different databases are different