1) You encounter the following statements:
SELECT e1.ename|| 'works for '||e2.ename "Employees and their Managers"
FROM emp e1, emp e2 WHERE e1.mgr=e2.empno;
What kind of join is this ?
A. Cartesian
B. Self
C. Outer
D. Equi
2) You have the statement "SELECT BirthDate, TO_CHAR(BirthDate, 'Month, ddth
"in, YyyY') Formatted". If the data is 11-NOV-46, what will Oracle return as the formatted
date ?
A. November ,11th in 1946
B. Nov 11, 1946
C. November 11, 1946
D. November , 01TH in 1946
E. 11-11-46
F. 11/11/46
3) ABC company is about to give each staff a $500 increase in monthly salary. You want to
"preview" the result from your database table without making any modification. How do you do that?
A. You cannot do this. Actual update will take place.
B. Give the arithmetic expression to invoke salary increment in the select clause
C. Give the arithmetic expression to invoke salary increment in the where clause
D. Give the arithmetic expression to invoke salary increment in the from clause
E. Give the arithmetic expression to invoke salary increment with an update clause
4)Create table student
(Student_id number(4) primary key,
Student_name varchar2(15),
Course varchar2(10) not null,
Age number(2) check (age between 18 and 65));
For which column(s) will an index be created automatically?
A. Student_id B. Student_name C. Course D. Age
5) What kind of join condition am I creating between the EMP and DEPT table in the
following query?
Select a.ename, b.job
From emp a, dept d;
A. Equijoin B. Outer Join C. Castesian product D. Self Join E. Non-Equijoin
6) What kind of join condition am I creating in the following query?
Select a.ename "Employee", a.job, b.ename, b.job
From emp a, emp b
where a.empno = b.empno;
A. Equijoin B. Outer Join C. Castesian product D. Self Join E. Non-Equijoin
7) Which type of PL/SQL statement would you use to increase the price values by 10
percent for items with more than 2,000 in stock and by 20 percent for items with fewer than 500 in stock?
A. An IF...THEN...ELSE statement B. A simple INSERT loop C. A simple UPDATE statement
D. A WHILE loop
8) For which of the following would you use the ALTER TABLE...MODIFY option?
A. Add a column to the table.B. Disable a table constraint.C. Drop a table column.
9) What is a characteristic of only PL/SQL?
A. Accepts input of variables. B. Allows shutdown of the database. C. Allows use of
exception handling routines based on error numbers. D. None of the above.
10) You query the database with this command:
SELECT atomic_weight FROM chart_n
WHERE (atomic_weight BETWEEN 1 AND 50 OR atomic_weight IN (25, 70, 95)) AND atomic_weight
BETWEEN (25 AND 75) Which of the following values could the statement retrieve ?
A. 51 B. 95 C. 30 D. 75
12) You query the database with this command:
SELECT isotope, group_id,mass_no,
DISTINCT(atomic_weight) FROM chart_n; What values are displayed?
A) Distinct combinations of isotope, group_id, mass_no, and atomic_weight.
B) isotope and distinct combinations of group_id, mass_no, and atomic_weight.
C) isotope, group_id, mass_no, and distinct values of atomic_weight.
D) No values will be displayed because the statement will fail.
13) Evaluate this procedure: CREATE OR REPLACE FUNCTION found_isotope (v_energy_line
IN BOOLEAN, v_proper_ratio IN BOOLEAN) RETURN NUMBER IS Ret_val NUMBER;
BEGIN
IF(v_energy_line AND v_proper_ratio) THEN ret_val:=1; ELSIF NOT (v_energy_line AND
v_proper_ratio) THEN ret_val:=2;
ELSIF (v_energy_line AND v_proper_ratio) IS NULL THEN
ret_val:=-1; END
IF; RETURN ret_val; END; If v_energy_line equals TRUE, and v_proper_ratio
equals NULL, which value is assigned to ret_val?
A) 1 B) 2 C) -1 D) None of the above
14) Evaluate this command: SELECT i.isotope, g.calibration FROM chart_n i,
gamma_calibrations g
WHERE i.energy = g.energy; What type of join is the command?
A. Equijoin B. Nonequijoin C. Self-join D. The statement is not a join query
15) Evaluate this command: ALTER TABLE customer DISABLE CONSTRAINT pk_customer
CASCADE; Which task would this command accomplish?
A. Delete only the primary key values.
B. Disable all dependent integrity constraints.
C. Disable only the primary key constraint.
D. Alter all dependent integrity constraint values.
16) Which of the following is a purpose of the user-defined constraint?
A. To enforce not-null restrictions B. To enforce referential integrity
C. To enforce business rules
D. To take action based on insertions, updates, or deletions in the base table
17) The PL/SQL executable section contains which type of statements ?
A. PL/SQL and SQL statements to manipulate data in the database
B. The procedure or function name and input/output variable definitions
C. The definition of program variables, constants, exceptions, and cursors
D. Statements to deal with error handling
18) Which of the following is executed automatically?
A. Anonymous PL/SQL block B. Function C. Procedure D. Trigger
19) Evaluate this command: CREATE FORCE VIEW isotope_groups AS SELECT element,
group_id,count(*) isotopes FROM chart_n WHERE atomic_weight>50 GROUP BY element,group_id
ORDER BY atomic_weight;
Which clause will cause an error?
A. AS SELECT isotope, group_id B. FROM chart_n C. WHERE atomic_weight>50
D. ORDER BY atomic_weight; -- cannot use order by in view, order by use when select view
20) You attempt to create a view with this command: CREATE VIEW parts_view AS SELECT
id_number, description, sum(quantity) FROM inventory WHERE id_number = 1234 GROUP
BY id_number; Which clause causes an error?
CREATE VIEW parts_view
AS SELECT id_number, description, sum(quantity)
FROM inventory
WHERE id_number = 1234'
GROUP BY id_number;
21) Evaluate this command:
CREATE TABLE purchase_items (id_number NUMBER(9), description
VARCHAR2(25)) AS SELECT id_number, description FROM inventory WHERE quantity < 10;
Why will this statement cause an error
A clause is missing.
A keyword is missing.
The WHERE clause cannot be used when you're creating a table.
The data types in the new table must not be defined.
22) In the executable section of a PL/SQL block, you include this statement:
Product.max_inventory1 :=30;
Which task will this accomplish?
A. A composite variable will be assigned a value.
B. A constant will be assigned a value.
C. An index identifier will be assigned a value.
D. A record will be assigned a value.
23) You have a table named CUSTOMER that was created with the following statement:
CREATE TABLE CUSTOMER (
CUST_ID NUMBER(5) PRIMARY KEY,
CUST_NAME VARCHAR2(50) NOT NULL,
CUST_TYPE CHAR(1) DEFAULT 'M' NOT NULL ,
LAST_CONTACT_DATE DATE,
RECORD_CREATION_DATE DATE NOT NULL);
Given this table definition, which of the following INSERT statements are valid? (choose all
correct answers)
A. INSERT INTO CUSTOMER VALUES (12345, 'Big Top Manufacturing', , NULL,
SYSDATE);
B. INSERT INTO CUSTOMER (CUST_ID, CUST_NAME, LAST_CONTACT,
RECORD_CREATED) VALUES (12345, 'Big Top Manufacturing', NULL, SYSDATE);
C. INSERT INTO CUSTOMER (CUST_ID, CUST_NAME, RECORD_CREATED)
VALUES (12345, 'Big Top Manufacturing', SYSDATE);
D. INSERT INTO CUSTOMER (CUST_ID, CUST_NAME, RECORD_CREATED)
VALUES (12346, 'Big Top Manufacturing', TO_CHAR(SYSDATE, 'fmMonth ddth, YYYY'));
24) You need to produce a report of all your products and their standard list prices. The
products are stored in the PRODUCT table, and the prices are stored in the PRICE table. Some products are new or discontinued and do not have a price in the PRICE table, but they should still be included on the report with a price of 0.00. Which of the following is the correct WHERE clause to complete this SQL command and meet these objectives?
SELECT PRODUCT.PRODNO,
PRODUCT.PRODNAME,
NVL(PRICE.PRICE_AMT, 0.00)
FROM PRODUCT, PRICE
A. WHERE PRODUCT.PRODNO(+) = PRICE.PRODNO
AND PRICE.COLUMN_LEVEL = 'LIST';
B. WHERE PRODUCT.PRODNO = PRICE.PRODNO(+)
AND PRICE.COLUMN_LEVEL = 'LIST';
C. WHERE PRODUCT.PRODNO = PRICE.PRODNO(+)
AND PRICE.COLUMN_LEVEL = 'LIST'(+);
D. WHERE PRODUCT.PRODNO(+) = PRICE.PRODNO
AND PRICE.COLUMN_LEVEL(+) = 'LIST';
25) The PRODUCT table contains one row for each product in the inventory. The PRICE table
contains
one row for each price level for each product. There is at least one price record for each
product, but
there may be as many as 100 price records for each product. The key for the PRODUCT
table is
PRODNO, and the foreign key for the PRICE table is PRODNO. If the query that follows
updates 100
records, how many rows in total will be returned by the subquery?
UPDATE PRICE
SET RECORD_STATUS =
(
SELECT RECORD_STATUS
FROM PRODUCT
WHERE PRODUCT.PRODNO = PRICE.PRODNO
)
WHERE PRICE_LEVEL = 'F16'
The subquery will return 100 rows.
The subquery will not have any matches.
The subquery will return 10,000 rows.
The subquery will return between 100 and 10,000 rows, depending on how many price
levels are
defined for each product.
26) Examine the queries below carefully. Which one will return only the first 100 rows in the
EMP table?
A. SELECT TOP 100 EMPNO, ENAME
FROM EMP
WHERE STATE = 'FL';
B. SELECT FIRST 100 EMPNO, ENAME
FROM EMP
WHERE STATE= 'FL';
C. SELECT EMPNO, ENAME
FROM EMP
WHERE ROWID <= 100;
D. SELECT EMPNO, ENAME
FROM EMP
WHERE ROWNUM <= 100;
27) You can use the PL/SQL block example to answer the following question:
DECLARE
CURSOR My_Employees IS
SELECT name, title FROM employee;
My_Name VARCHAR2(30);
My_Title VARCHAR2(30);
BEGIN
OPEN My_Employees;
LOOP
FETCH My_Employees INTO My_Name, My_Title;
EXIT WHEN My_Employees%NOTFOUND;
INSERT INTO MY_EMPS (MY_EMPNAME, MY_EMPTITLE)
VALUES (My_Name, My_Title);
END LOOP;
CLOSE My_Employees;
END;
If you were rewriting this block of PL/SQL, which of the following types
of loops would you use if you wanted to reduce the amount of code by
utilizing features of the loop that handle mundane aspects of processing
automatically?
A. loop ... exit when
B. while ... loop
C. loop ... loop … end
28) You issue the following statement:
SELECT DECODE(ACCTNO, 123456, 'CLOSED', 654321, 'SEIZED',
590395, 'TRANSFER','ACTIVE') FROM BANK_ACCT;
If the value for ACCTNO is 503952, what information will this statement display?
A. ACTIVE
B. TRANSFER
C. SEIZED
D. CLOSED
29 You are entering several dozen rows of data into the BANK_ACCT table.
Which of the following statements will enable you to execute the same statement again and
again,entering different values for variables at statement runtime?
A. insert into BANK_ACCT (ACCTNO, NAME) VALUES
(123456,’SMITH’);
B. insert into BANK_ACCT (ACCTNO, NAME) VALUES (VAR1, VAR2);
C. insert into BANK_ACCT (ACCTNO, NAME) VALUES (&VAR1,’&VAR2’);
D. insert into BANK_ACCT (select ACCTNO, NAME from EMP_BANK_ACCTS);
30) You execute the following SQL statement: select ADD_MONTHS
(‘28-APR-97’,120) from DUAL. What will Oracle return?
A. 28-APR-03
B. 28-APR-07
C. 28-APR-13
D. 28-APR-17
31) Evaluate this PL/SQL block:
DECLARE
v_lower NUMBER := 2;
v_upper NUMBER := 100;
v_count NUMBER := 1;
BEGIN
FOR I IN v_lower..v_lower LOOP
INSERT INTO test(results)
VALUES (v_count);
V_count := v_count + 1;
END LOOP;
END;
How many times the For Loop will loop ?
A. 0 B. 1 C. 2 D. 98 E. 100
32) Evaluate this PL/SQL block:
DECLARE
v_result BOOLEAN;
BEGIN
DELETE
FROM sale
WHERE salesperson_id IN (25,35,45);
v_result := SQL%ISOPEN;
COMMIT;
END;
A. 0 B. 3 C. TRUE D. NULL E. FALSE -- implicit cursor will automatically close.
33) Review this SQL Statement:
SELECT ename, emp_number, salary
FROM employee
WHERE dept_number = (SELECT dept_number
FROM department
WHERE location IN('CHICAGO','ATLANTA'))
Why may this statement return an error?
A. A multiple-row subquery returns one row. B. A multiple-column subquery returns one
column.
C. A single-row subquery returns more than one row. D. A multiple-row query uses a single-
row subquery.
E. A single-row quey uses a multiple-row subquery that returns only one row.
34) Review this SQL statement:
SELECT department "Departments", MAX(salary) "Top Salaries"
FROM employee
WHERE department IN (200,300,400)
GROUP BY Departments
HAVING MAX(salary) > 60000;
This statement fails when executed. Which change will correct the problem ?
A. Remove the group function from the HAVING clause.
B. Add the condition "MAX(salary) > 60000" to the WHERE clause.
C. Replace the column alias in the GROUP BY clause with the column name.
D. Add the group function(s) used in the SELCT list to the GROUP BY clause.
