oracle 测试

1。 D
You alter the database with this command:
 ALTER TABLE inventory
 MODIFY (price DEFAULT 0);
   which task was accomplished?
a). A new column was added to the table
b). A column constraint was added to the table
c). A column width was altered
d). A default value was added to a column
2。 C
Evaluate a data model with the entity of SALES, CAR and EMPLOYEE.
    one SALES may sale many CARs, one CAR could be sold, then trade in and
    then sold in another SALES. What is the relationship between CAR and SALES?
a). 1 to 1 relationship
b). 1 to many relationship
c). many to many relationship
3。C
Which command could re-enable a disabled primary key?
a). ALTER TABLE employee
    CREATE CONSTRAINT PRIMARY KEY;
b). ALTER TABLE employee
    ENABLE CONSTRAINT PRIMARY KEY;
c). ALTER TABLE employee
    ENABLE PRIMARY KEY;
d). ALTER TABLE employee
ENABLE PRIMARY KEY CASCADE;

4。A
After you create a view, where the view stored?
a). Data Dictionary 
b). SELECT statement
c). tables on which the view build on

5。A C E
Which tasks can be accomplished using WHERE clause (choose 3)?
A. compare two values
B. display unique data
C. restrict rows for display
D. column position
E. collect data greater than a specified value

6。B C E
Which three will return a DATE value?  (Choose three)
a). '01-MAR-97' - '02-FEB-96'
b). '01-MAR-97' - 7                       
c). '01-MAR-97' - 20/24                   
d). month_between('01-MAR-97','02-FEB-96')
e). '01-MAR-97' + 6

7。B
What happens if you manually create an index on a foreign key colum?
A. The unique key created on the primary key on the other table is overriden
B.The index will reduce the disk I/O for Select/Delete
C. You can not manually create a index on a foreign key colum

8。B
You query the database with this command:
SELECT id_no, quantity
FROM sales;
Which value is displayed when the quantity is null?
a) 0
b) nothing   
c) the keyword NULL
d) 100

9。A
When you create a table, there is a column, whose value have 2 digits on
the right of decimal point, but may vary greatly on the left, which datatype
should be used for this colume?
A number
B number(p,s)
C. VARCHAR2
D. DATE
E. You can not record such values in an Oracle table;

10。B
The employee table:
First_name Varchar2(25)
Last_name Varchar2(25)
Evaluate the following select statements:

 

1). SELECT CONCAT(first_name, last_name), length(concat(first_name, last_name)
FROM employee
WHERE upper(last_name) like '%L'
OR upper(last_name) like '%J'
OR upper(last_name) like '%K';
2). SELECT INITCAP(first_name || last_name), length(last_name)+length( first_name)
FROM employee
WHERE INITCAP(SUBSTR(last_name, 1, 1)) in ('J', 'K', 'L');

A. The two statements will retrieve the same data, but display differently.
B. The two statements will retrieve the different data.
C. Statement 2 will not execute.

11。A
Which privilege is concerned system security
A.DROP ANY TABLE
B. ALTER
C. SELECT
D.GRANT

12。C
Can you rename a column?
A. Rename Column command
B.Alter Table command
C. Can not rename a column, unless using a temporary table

13。B
To load a table with a huge amount of data, disable the Primary Key and
Not Null contraints, then re-enable them after the load. What happens?

A. Unique indexes automatically re-created;
B. Existing rows are validated;
C. Only subsequent insertions to the table are validated;

14。A B C
When will the WHERE clause be needed? (chose 3)
A. display those column with value greater than a specified value
B. compare 2 values
C. limit the selected rows
D.sort display

15。B
Which of following statement can retrieve all data dictionary
views that you can access?
A. SELECT * FROM user_views;
B. SELECT * FROM dictionary;
C. SELECT * FROM all_views;
D. SELECT * FROM user_objects;

16。A
In order for newly created user to get access to database, you must grant which
Privilege to the new user?
A. create session
B. create table
C. connect
D. resource

17。D
You run the following commands:
 CREATE ROLE payroll_dept;
 GRANT create table, create view, create session TO payroll_dept;
 GRANT payroll_dept to TOM;
 GRANT payroll_dept to JOHN;
   which tasks were accoumplished?
a). system privileges were granted to a role
b). object privileges were granted to users
c). a role was created, object privileges were granted to Tom and John
d). a role was created, system privileges were granted to Tom and John

18。C
Table  Dept has two columns: id( PK) , name
Table Emp has four columns: id, lastname, firstname, dept_id
Create Index emp_dept_id_idx
on emp(dept_id);
what this command accomplished?
a. Store an index in Emp table
b. Increase the chance of full table scans
c. May reduce the amount of I/O for select statments
d. May reduce the amount of I/O for insert statments
e. override UK index created when the FK relationship was defined
19。C
Evaluate the following statement:
 SELECT count(*)
 FROM employee
 WHERE salary >
  (SELECT AVG(salary)
   FROM employee
   WHERE department_id =
   (SELECT department_id
    FROM employee
    WHERE last_name = 'SMITH'));
    This statement will display:
a). number of employees whose salary is more than the average salary of all employees
b). number of departments with average salary higher than the average salary of department where Mr. SMITH is working in
c). number of employees whose salary is more than the average salary of department where Mr. SMITH is working in

20。C
How to make the following SQL execute?
SELECT ename, sal
FROM employee
WHERE sal > AVG(sal)
A. add one line
B. modify SELECT clause
C. use subquery in WHERE clause
D. move the function to the SELECT statement and add GROUP BY clause
E. move the function to the SELECT statement and add GROUP BY clause and HAVING clause

21。B
select i.id, i.name
from employee m, employee i
where i.id = m.manager_id;

select i.id, i.name
from employee i, employee m
where m.id = i.manager_id;
Two statement  will show  the  result  is
a) same
b) different
c) error

22。D
You issued a statement:
CREATE PUBLIC SYNONYM emp
FOR ed.employee
Which task has been accomplished?
A. The object now can be accessed by all users;
B. All users were given object privilege to the table;
C. The need to qualify the object name with its schema was elimilated only for you;
D. The need to qualify the object name with its schema was elimilated for all users.

23。C
SELECT empno, ename, deptno, salary
FROM emp
WHERE deptno = (select deptno
from emp
where ename = 'SMITH'
or salary > 3000);
The problem of  this statement is
A. can not use subquery in where clause
B. a single row subquery with a multiple comparison operator
C. a multiple row subquery with a single comparison operator

24。C
Which SOL statement would you use to display the names of teachers that do
not have an E-mail address?
A. SELECT last_name, first_name
   FROM teacher
   WHERE email = NULL;
B. SELECT last_name, first_name
   FROM teacher
   WHERE email != NULL;

C. SELECT last_name, first_name
   FROM teacher
   WHERE email IS NULL;
D. SELECT last_name, first_name
   FROM teacher
   WHERE email IS NOT NULL;

25。C
Try to look through the information about employee Smith, but not
sure the case stored in the database. which statement is correct
A. select lastname, firstname
from emp
where lastname='simth';

B. select lastname, firstname
from emp
where Upper(lastname)='simth';

C. select lastname, firstname
from emp
where Lower(lastname)='simth';

26。B

You create a table with this statement:
    1. CREATE TABLE alpha_3000
    2.  (alpha_id& NUMBER(9)
    3.    CONSTRAINT alpha_3000_id_pk PRIMARY KEY,
    4.   name$  VARCHAR2(25),
    5.   title#  VARCHAR2(25),
    6.   comment VARCHAR2(25)
    7.    CONSTRAINT alpha_3000_idname_nn NOT NULL);
   Which two lines of column name are invalid?
a) 2, 4
b) 2, 6  
c) 4, 5
d) 4, 6

27。B
Evaluate this command:
 SELECT  count(*)
 FROM inventory
 WHERE price > 5
 GROUP BY manufacturer_id
 HAVING count(*) > 10
 ORDER BY order_date;
   Which clause specifies which group will be displayed?
a) WHERE price > 5
b) GROUP BY manufacturer_id
c) HAVING count(*) > 10  
d) ORDER BY order_date

28。B
how to remove all rows from a table while keeping all table definition,
suppose you don't care if the data can be rollbacked after all?
A. DELETE * from emp;
B. TRUNCATE TABLE emp;
C. REMOVE * FROM emp;
D. DROP TABLE emp;
29。B
Dictionary table User_constraints only displays constraint names
A.true
B. false

30。D
Create role Inventory
Create role Clerk
Create role Manager
Create user Scott identified by tigger
Grant clerk to Manager
Grant Inventory to Clerk
Grant Manager to Scott
How many role Scott can access to ?
A. ZERO
B. ONE
C. TWO
D. THREE

31。B
Could you update following view?
create or replace view dept_view
as select dept_no, sum(salary) total_sal, count(*)
from emp
group by dept_no
A. Yes
B. No

32。B
WHICH ONE CAN RE-ENABLE A DISABLE PRIMARY KEY?
a). ALTER TABLE ADD CONSTRAIN PRIMARY
KEY(EMPLOYEE_ID);
b). ALTER TABLE ENABLE CONSTRAINT EMPLOYEE_ID_PK;
c). ALTER TABLE ENABLE PRIMARY KEY (ID);
d). ALTER TABLE ENABLE PRIMARY KEY (ID) CASCADE;

33。B
select * from emp where id NOT IN ( select manager_id from .....),
which operator is the same as NOT IN.
a. !=
b. !=ALL
C. NOT LIKE
d. !=ANY

34。A C
You create a table with this statement:
    1. CREATE TABLE alpha_3000
    2.  (id NUMBER(9),
    3.    CONSTRAINT alpha_3000_id_pk PRIMARY KEY,
    4.   3000_name  VARCHAR2(25),
    5.   title  VARCHAR2(25),
    6.   idname  VARCHAR2(25)
    7.    CONSTRAINT alpha_3000_idname_nn NOT NULL);
   Which two lines of the statement will return an error?
a) 2
b) 3  
c) 4 
c) 7

35。A B C
what can be used in where clause? choose 3
a. column name
b. arithmetic expresion
c. comparision
d. alias
e. position
36。D
Who is the owner of the database role?
a). system
b). user who created the role
c). internal
d). nobody

37。A
To create a Sequence, which start with 100, max should be 99999, no
reused number and increase by 1.  Chose the correct one.

A) CREATE SEQUENCE dept_deptno
INCREMENT BY 1
START WITH 100
MAXVALUE 99999
NOCYCLE;

B)CREATE SEQUENCE dept_deptno
START WITH 100
MAXVALUE 99999
NOCYCLE;

C)CREATE SEQUENCE dept_deptno
INCREMENT BY 1
MINVALUE 100
MAXVALUE 99999

D)CREATE SEQUENCE dept_deptno
INCREMENT BY 1
MINVALUE 100
MAXVALUE 99999
NOCYCLE;

38。D
How to pass the parameters to the following SQL script file with
    a file name called my_prog.sql?
 SELECT *
 FROM employee
 WHERE employee_id = &1
 OR (last_name = '&2'
     AND first_name = &3);
a) @my_prog 7906, 'SMITH', JOHN
b) @my_prog 7906, SMITH, 'JOHN'
c) @my_prog 7906 'SMITH' 'JOHN'
d) @my_prog 7906 'SMITH' '''JOHN'''

39。C
Which ALTER statement would you use to add a primary key constraint
   on the manufacturer_id column of the inventory table?
a). ALTER TABLE inventory
    MODIFY manufacturer_id CONSTRAINT PRIMARY KEY;
b). ALTER TABLE inventory
    MODIFY CONSTRAINT PRIMARY KEY manufacturer_id;
c). ALTER TABLE inventory
    ADD CONSTRAINT pk_inventory PRIMARY KEY (manufacturer_id); 
d). ALTER TABLE inventory
    ADD CONSTRAINT manufacturer_id PRIMARY KEY;

40。B
What values will be returned for the following statement
 SELECT name
 FROM customer
 WHERE name like '%x%'
      OR (name like '%s%'
     AND name like '%t%'
     AND name like '%p%');
    the data in name column are
a). john
b). stop 
c). steve
d). roadmap
e). temporary

41。C
Which operator can not be used in nonequel join.
a. IN.
b. Like.
C. Equal operator
d. between...and..

42。E
which is a correct name for a table
A. number
B. 1996_invoice
C. invoice-1996
D. invoice@1996
E. catch_$
43。B
Each employee will has a $100 salary increment. You need to evaluate
the results from the employee table prior to the actual modification.
If you do not want to store the results in the database, which statement
is valid?
A. Give the arithmetic expression that involves the salary increase
in the set clause of the update statement.
B. Give the arithmetic expression that involves the salary increase
in the select clause of select statement.。