1. You must permanently remove all data from the INVOICE table, but will need the table structure in the future. What single command should be issued?
Ans:B
Ex:Truncate will delete data permanently .But the structure will remain same.
Delete command will delete the data,but if issuing of "ROLLBACK" Command will retrieve the back
A) Number(8,2)
a. DROP TABLE invoice
b. TRUNCATE TABLE invoice
c. DELETE
FROM invoice
d. TRNCATE TABLE invoice
KEEP STRUCTURE;
Ans:B
Ex:Truncate will delete data permanently .But the structure will remain same.
Delete command will delete the data,but if issuing of "ROLLBACK" Command will retrieve the back
2. Examine the following command:
CREATE TABLE pay
(employee-id Number(9)
CONSTRAINT pay-pk PRIMARY KEY,
2000-pay NUMBER(8,2)
manager-name VARCHAR2(25)
CONSTRAINT mgr-name-nn. NOT NULL,
pay-96 NUMBER(8,2));
Which two lines of this command will return an error?
a. First
b. Second
c. Third
d. Fourth
e. Fifth
f. Sixth
g. Seventh
Ex:while defining the structure the table we can't give like ' mgr-name' for the field,need to give mgr_name
Ex:while defining the structure the table we can't give like ' mgr-name' for the field,need to give mgr_name
3. You must store currency data. All data will have two digits to the fight of the decimal point (ie, 25.xx). The numbers to the left of the decimal point will vary. Which data type is most appropriate?
a. NUMBER
b. NUMBER(T)
c. LANG
d. LANGRA
A) Number(8,2)
4. Examine the WORKER table below:
NAME NULL TYPE
WORKER ID NOT NULL NUMBER(3)
NAME NOT NULL VARCHAR2(25)
PHONE NOT NULL VARCHAR2(9)
ADDRESS VARCHAR2(50)
POSITION UPDATE
There are hundreds of records in the EMPLOYEE TABLE. You need to modify the phone column to hold only number values. Which statement will modify the data type appropriately?
a. ALTER TABLE worker
MODIFY phone NUMBER(9)
b. ALTER WORKER table
MODIFY COLUMN phone NUMBER(9)
c. A VARCHAR2 data type cannot be changed to a NUMBER data type for a column.
d. The data type of a column cannot be modified if there is data in the column.
5. What should be done after each fetch statement in a PL/SQL block?
a. Open the cursor.
b. Close the cursor.
c. Initialize the loop.
d. Test for rows using the cursor attribute.(Self Explanatory)
6. Examine this executable section of a PL/SQL block:
BEGIN
FOR worker_record IN pay_cursor LOOP
Worker_id_table(worker_id):=
Worker_record.last_name;
END LOOP;
CLOSE salary_cursor;
END;
Why does this section cause an error?
a. The cursor needs to be open.
b. No fetch statements were used.
c. Terminating conditions are missing.
d. The cursor does not need to be explicitly closed.(Self Explanatory)
7. Your company will be granting workers a $150 salary increase. You need to evaluate results of the increase from the worker table prior to the actual modification. You do not want to store in the results in the current database. Which of the following is untrue?
a. A column must be added to the worker table.
b. An arithmetic expression must be added that invokes the salary increment in the SET section of the upgrade clause.
c. An arithmetic expression must be added that invokes the salary increment in the SELECT clause of the SELECT statement.
d. An arithmetic expression must be added that invokes the salary increment in the UPDATE clause of the SELECT statement.
8. What command should be used to execute a script file named QUERYCOLOR.SQL from the SQL Plus environment?
a. START QUERYCOLOR
b. EXECUTE QUERYCOLOR
c. RUN QUERYCOLOR
d. GET QUERYCOLOR
9. You need to test if the current fetch within a PL/SQL loop was successful. Which cursor attribute is needed to accomplish this task?
a. SQL%ISOPEN
b. SQLROWCOUNT
c. SQL%FOUND
d. A cursor attribute cannot be used within a PL/SQL loop.
10. You have been assigned the task of updating worker salaries. If a salary is less than 1000, it must be incremented by 10%. The SQL Plus substitution variable will be used to accept a worker number. Which PL/SQL block successfully updates salaries?
a. DECLARE
V_sal worker.sal%type;
BEGIN
SELECT sal
INTO v_sal
FROM worker
WHERE workerno=p_workerno;
IF v_sal<1000-then>
UPDATE worker
INTO sal:=sal*1.1
WHERE workerno=p_workerno;
END IF;
END;
b. DECLARE
V_sal worker.sal%type;
BEGIN
SELECT sal
INTO v_sal
FROM worker
WHERE worker=;p_wokerno;
IF ‘v_sal<1000 data-blogger-escaped-nbsp="" data-blogger-escaped-p="">sal:="sal*1.;
END IF;
END;
c. DECLARE
V_sal worker.sal%type;
BEGIN
SELECT sal
INTO v_sal
FROM worker
WHERE workerno=p_workerno;
IF ‘v_sal<1000 data-blogger-escaped-comment-1000--="" data-blogger-escaped-d-then="">
UPDATE emp
sal:=sal*1.1
WHERE workerno=p_workerno;
END IF;
END;
d. DECLARE
V_sal worker.sal%type;
BEGIN
SELECT sal
INTO v_sal
FROM worker
WHERE workernop_workerno;
IF ‘v_sal<1000 data-blogger-escaped--then="" data-blogger-escaped-comment-1000--="">
UPDATE worker
SET sal:=sal*1.1
WHERE workerno=p_workerno;
END IF;
END;
11. Examine the following:
SET SERVER OUTPUT ON
X NUMBER;
V_sal NUMBER;
V_found VARCHAR2(10):= ‘TRUE’;
BEGIN
X:=1;
V_sal:=1000;
DECLARE
V_found VARCHAR2(10);
Y NUMBER
BEGIN
IF(v_sal>500)
THEN v_found:= ‘YES’;
END IF;
DBMS_OUTPUT.PUT_LINE(‘VALUE OF v_found is’||v_found);
DBMS_OUTPUT.PUT_LINE(‘VALUE OF v_sal is’||v_sal);
Y:=20;
END;
DBMS_OUTPUT.PUT_LIN(‘VALUE OF v_found is’||v_found);
DBMS_OUTPUT.PUT_LINE(‘VALUE OF Y is’||to_char(Y));
END;
SET SERVER OUT PUT OFF;
Why will this procedure produce an error when executed?
a. The value of v_found cannot be YES.
b. The variable v_found is declared in more than one location.
c. The variable Y is declared in the inner block and referred to in the outer block.
d. The variable v_sal is declared in the outer block and referred to in the inner block.
12. The worker table contains the following columns:
First_name VARCHAR2(25)
Last_name VARCHAR2(25)
Evaluate these two SQL statements:
SELECT CONTACT (first_name,last_name),
LENGTH(CONTACT(first_name,last_name))
FROM worker
WHERE UPPER(last_name)like ‘%J’
OR UPPER (last_name)like ‘%K’
OR UPPER (last_name)like ‘%L’;
SELECT INITCAP(first_name)||
INITCAP(last_name),
LENGTH(last_name)+LENGTH(first_name)
FROM worker
WHERE INTICAP(substr(last_name,1,1))In
( ‘J’, ‘K’, ‘L’);
How will results differ between the two SQL statements?
a. Different data will be retrieved from the database.
b. The same data will be retrieved, but it will be displayed differently.
c. The first statement will be executed; the second will not.
d. The second statement will be executed; the first will not.
13. In the declarative section of a PL/SQL block, you create-but do not initialize-a number variable. When the block executes, what will be the initial value of the variable?
a. 0
b. Null
c. The value will depend on the scale and precision of the variable.
d. The block will not execute because the variable was not initialized.
14. The worker table contains the following columns:
LAST_NAME VARCHAR2(25) FIRST_NAME VARCHAR2(25) DEPT_ID NUMBER(9)
You need to display the names of workers that are not assigned to the department. Evaluate the following:
SELECT last_name, first_name
FROM worker
WHERE dept_id is NULL
Which of the following changes should be made to achieve the desired result?
a. Create an outer join.
b. Change the column in the WHERE condition.
c. Change the operator in the WHERE condition.
d. Add a second condition to the WHERE condition.
15. Which of the following statements regarding SQL is true?
a. Null values are displayed last in ascending sequences.
b. Data values are displayed in descending order by default.
c. You cannot specify a column alias in an ORDER BY clause.
d. Query results cannot be sorted by a column that is not included in the SELECT list.
16. You are a user of the PROD database, which has over 1200 tables. What data dictionary view must you query to determine the number of tables you can access?
a. ALL_OBJECTS
b. DBA_TABLES
c. DBA_SEGMENTS
d. USER_OBJECTS
17. Evaluate the two following commands:
SELECT DISTINCT object-type
FROM user-objects;
SELECT object-type
ROM all-objects;
How will the results of the two commands differ?
a. The first statement will display the distinct object types in the database; the second statement will display the object types in the database.
b. The first statement will display the distinct object types owned by the users; the second statement will display all object types in the database.
c. The first statement will display distinct object types owned by the user; the second statement will display all the object types that the user can access.
d. The first statement will display the distinct object types that the user can access; the second statement will display all the object types that the user owns.
18. Which of the following privileges is related to system-level security?
a. Drop any table
b. DELETE
c. ALTER
d. INDEX
19. Evaluate the following:
CREATE ROLE supervisor;
CREATE ROLE clerk;
CREATE ROLE janitor;
CREATE USER alex IDENTIFIED BY green;
GRANT janitor TO clerk;
GRANT clerk TO supervisor;
GRANT janitor to alex;
/
How many rows will user alex have access to?
a. 0
b. 1
c. 2
d. 3
20. Mike forgot his password. Which of the following commands will set a password for user, mike?
a. ATER USER mike PASSWORD BY green. The command must be issued by Mike.
b. ALTER USER mike IDENTIFIED BY green. The command must be issued by the DBA.
c. ALTER USER mike IDENTIFIED BY green. The command must be issued by Mike.
d. CHANGE password to green WHERE “user=mike”; The command must be issued by the DBA.
21. You are updating the worker table. Julie has been granted the same privileges as you. You ask her to check your work before you issue a COMMIT command. What can she do on the workers table?
a. She can access the table and verify your changes.
b. She cannot access the table.
c. She can access the table but cannot see your changes. She can make changes for you.
d. She can access the table, but cannot see your changes or make changes to the roles you are changing.
22. Examine the following:
Name Null? Type
PUPIL-ID NOT NULL NUMBER(3)
NAME NOT NULL VARCHAR2(25)
ADDRESS VARCHAR2(50)
GRADUATION DATE
Which of these statements inserts a new row into the PUPIL table?
a. INSERT INTO pupil.
VALUES(121, ‘Benson’);
b. INSERT INTO pupil.
VALUES(121, ’50 NE Oak St.’, ‘20-MAR-01’, ‘Benson’);
c. INSERT INTO test.
VALUES(121, ’Benson’, ’50 NE Oak St.’, ‘20-MAR-01’);
d. INSERT INTO pupil.(pupil-id,address,name,graduation)
VALUES(121, ’50 NE Oak St.’, ‘Benson’, ‘20-MAR-01’);
23. Examine the following:
NAME NULL TYPE
PUPIL_ID NOT NULL NUMBER(3)
NAME NOT NULL VARCHAR2(25)
ADDRESS VARCHAR2(50)
GRADUATION DATE
The GRADUATION column is a foreign key column to the table. Examine the data in the GRADE DATA table:
Graduation 11-May-2001
13-Jan-2001
19-Dec-2001
25-Jun-2000
Which of the following statements will produce the error….”ORA-02291 integrity constraint (sys_c23) violated. Parent key not found.?
a. UPDATE pupil
SET pupil-id=999
Graduation=’11-MAY-2001’
WHERE stud-id=101;’
b. UPDATE pupil
SET name=’Benson’,
Graduation=’11-MAY-2001’
WHERE pupil-id=101;
c. UPDATE pupil
SET name=’Benson’,
Graduation=’15-AUG-200’
WHERE pupil-id=101*
d. UPDATE pupil
SET stud-id=NULL,
Address=’50 NE Oak St’
WHERE graduation=’18-APR-2001’
24. Within SQL Plus, you issue the following:
Delete from dept_id=807
You receive an integrated constraint error indicating the child record was found. What should be done to make the statement execute?
a. Delete the child record first.
b. Add a fourth keyword to the command.
c. Add the constraints cascade option to the command.
d. The statement cannot be executed.
25. The view WORKER-VIEW is created based on the WORKER table as follows:
CREATE OF REPLACE VIEW worker-view
AS
SELECT deptno,Sum(sal)TOT-SAL,COUNT(+)NOT-WORKER
FROM worker
GROUP BY deptno;
What happens when the following command is issued?
UPDATE worker-view
SET lot-sal=25000
WHERE deptno=8;
a. The base table cannot be updated through this view.
b. The TOT_SAL column in the WORKER table is updated to 25,000 for department 8.
c. The TOT_SAL column in the WORKER view is updated to 25,000 for department 8.
d. The SAL column in the WORKER table is updated to 25,000 for employees in department 8.
