1. There are three divisions within your company and each division has at least one worker bonus program and at least one worker. Bonus values do not exceed 500; not all employees receive bonuses. Evaluate the following block:
DECLARE
V_bonus worker.bonus%TYPE:=270;
BEGIN
UPDATE worker
SET bonus=bonus+v_bonus
WHERE division_id IN (10,20,30);
COMMIT;
END;
What is the result of the statement?
a. All employees will be given a bonus of 270.
b. A subset of 270 employees will be given a bonus of 270.
c. All employees will be given a 270 increase in bonus.
d. A subset of employees will be given a 270 increase in bonus.
Ans:d. A subset of employees will be given a 270 increase in bonus.
Ans:d. A subset of employees will be given a 270 increase in bonus.
2. You have been given update privileges on the last_name column of the worker table. Which data dictionary view would you query to display the column? The privileges were granted on the schema that owns the worker table.
a. ALL_TABLES
b. TABLE_PRIVILEGES
c. ALL_COL_PRIVS_RECD
d. This cannot be retrieved from a single view.
c. ALL_COL_PRIVS_RECD
3. Which of the following ALTER commands reinstates a disabled primary constraint?
a. ALTER TABLE FRUIT
ENABLE PRIMARY KEY(ID)
b. ALTER TABLE FRUIT
Enable PRIMARY KEY(id)CASCADE;
c. ALTER TABLE FRUIT
ENALBE CONSTRAINT fruit_id_pk;
d. ALTER TABLE FRUIT
ADD CONSTRAINT fruit_id_pk PRIMARY KEY(id);
ans:c. ALTER TABLE FRUIT
ans:c. ALTER TABLE FRUIT
ENALBE CONSTRAINT fruit_id_pk;
4. You have been assigned the task of making major updates to the worker table. You disable the primary key constraint on the workid column and the check constraint on the job column. What happens when you try to enable the constraint after the update is completed?
a. Existing rows that don’t conform with the constraints are automatically deleted.
b. Indexes on both columns with the primary key constraint and the check constraints are automatically recreated.
c. All existing column values are verified to conform with the constraints and an error message is narrated if any existing values are not confirmed.
d. The constraints must be recreated once they are disabled.
c. All existing column values are verified to conform with the
constraints and an error message is narrated if any existing values are not
confirmed.
5. Which of the following is a valid table name?
a. #_9
b. 24_bottles
c. colors-1999
d. Slipper_#66*
Ans :d. Slipper_#66*
6. Examine the structure of the pupil table:
NAME NULL? TYPE
PUP_ID NOT NULL NUMBER(3)
NAME NOT NULL VARCHAR2(25)
PHONE NOT NULL VARCHAR2(9)
ADDRESS VARCHAR2(50)
GRADUATION DATE
There are over two hundred records in the pupil table. You want to change the name of the graduation column to grad_date. Which of the following is true?
a. You can use the ALTER TABLE command with the MODIFY COLUMN clause to modify the column.
b. You can use the ALTER TABLE command with the RENAME COLUMN clause to rename the column.
c. You can use the ALTER TABLE command with the MODIFY clause to rename the column.
d. You cannot rename the column.
Ans:d. You cannot rename the column.
7. Examine the automobile table:
AUTOMOBILE
Column name ID MODEL STYLE Color LOT_NO
Key type PK FK
Nulls/Unique NN, UU NN NN NN NN
FK table LOT
FK column LOT_NO
Data type NUM CHAR CHAR CHAR NUM
Length 9 20 20 20 3
Which SELECT statement will display the style, color, and lot number for all cars based on model?
a. SELECT style,color,lot_no
FROM automobile
WHERE model=UPPER(‘%model’);
b. SELECT style,color,lot_no
FROM automobile
WHERE UPPER ‘model’=(:model);
c. SELECT style,color,lot_no
FROM automobile
WHERE UPPER ‘model’=UPPER(:model);
d. SELECT style,color,lot_no
FROM automobile
WHERE model=’model’;
ANS a. SELECT style,color,lot_no
FROM automobile
WHERE model=UPPER(:model)
8. Examine the following DECLARE statement:
DECLARE
CURSOR work_cursor(p_divisionno NUMBER, p_job VARCHAR2)
IS
SELECT WORKNO, WNAME
FROM WORK
WHERE WORKNO=p_divisionno
AND JOB=p_job
BEGIN
. . .
Which statement opens the cursor successfully?
a. OPEN work_cursor.
b. OPEN work_cursor(‘clerk;,10);
c. OPEN work_cursor(10, ‘manager’);
d. OPEN work_cursor(p_divisionno,p_job);
Ans: a. OPEN work_cursor.
9. As DBA, you use the CREATE USER command to create an account for user, Davis. Davis must create tables and packages in his own schema. What command must be executed next to grant him these privileges?
a. GRANT CREATE TABLE, CREATE PACKAGE
TO davis;
b. GRANT CREATE CONNECT, CREATE TABLE, CREATE PROCEDURE
TO davis;
c. GRANT CREATE TABLE, CREATE PROCEDURE
TO davis;
d. GRANT CREATE SESSION,CREATE TABLE, CREATE PROCEDURE
TO davis;
Ans: d. GRANT CREATE SESSION,CREATE TABLE, CREATE PROCEDURE
TO davis;
10. The WORK table has columns designated for the birth date and hire date of all workers. Both columns are defined with the DATE data type. You want to insert a row with the details of employee Wallace, who was born in 1952 and hired in 2001. Which of the following statements will insert the values into the table in the correct century?
a. INSERT INTO WORK(workno,wname,birthdate,hiredate)
VALUES(WORKNO_SEQ.NEXTVAL, ‘Wallace’, ‘10-nov-52’, ’13-jan-01’)
b. INSERT INTO WORK(workno,wname,birthdate,hiredate)
VALUES(WORKNO_SEQ.NEXTVAL, ‘Wallace’,
TO_DATE(‘10-nov-52’, ‘DD-MON-YY’),
TO_DATE(‘13-jan-01’, ‘DD-MON-YY’));
c. INSERT INTO WORK(workno,wname,birthdate,hiredate)
VALUES(WORKNO_SEQ.NEXTVAL, ‘Wallace’,
TO_DATE(‘10-nov-52’, ‘DD-MON-RR’),
TO_DATE(‘13-jan-01’, ‘DD-MON-RR’));
d. d. INSERT INTO WORK(workno,wname,birthdate,hiredate)
VALUES(WORKNO_SEQ.NEXTVAL, ‘Wallace’,
TO_DATE(‘10-nov-52’, ‘DD-MON-YYYY’),
TO_DATE(‘13-jan-01’, ‘DD-MON-RR’));
Ans: c. INSERT INTO WORK(workno,wname,birthdate,hiredate)
VALUES(WORKNO_SEQ.NEXTVAL, ‘Wallace’,
TO_DATE(‘10-nov-52’,
‘DD-MON-RR’),
TO_DATE(‘13-jan-01’,
‘DD-MON-RR’));
