Q1. - (Topic 2)
View the exhibit and examine the description for the SALES and CHANNELS tables.
You issued the following SQL statement to insert a row in the SALES table:
INSERT INTO sales VALUES (23, 2300, SYSDATE, (SELECT channel_id FROM channels WHERE channel_desc='Direct Sales'), 12, 1, 500);
Which statement is true regarding the execution of the above statement?
A. The statement will execute and the new row will be inserted in the SALES table.
B. The statement will fail because subquery cannot be used in the VALUES clause.
C. The statement will fail because the VALUES clause is not required with subquery.
D. The statement will fail because subquery in the VALUES clause is not enclosed with in single quotation marks.
Answer: A
Q2. - (Topic 2)
View the Exhibit and examine the structure of the CUSTOMERS table.
Which two tasks would require subqueries or joins to be executed in a single statement? (Choose two.)
A. listing of customers who do not have a credit limit and were born before 1980
B. finding the number of customers, in each city, whose marital status is 'married'
C. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
D. listing of those customers whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'
E. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
Answer: D,E
Explanation:
Describe the Types of Problems That the Subqueries Can Solve There are many situations where you will need the result of one query as the input for another. Use of a Subquery Result Set for Comparison Purposes Which employees have a salary that is less than the average salary? This could be answered by two statements, or by a single statement with a subquery. The following example uses two statements: select avg(salary) from employees; select last_name from employees where salary < result_of_previous_query ;
Alternatively, this example uses one statement with a subquery:
select last_name from employees where salary < (select avg(salary)from employees);
In this example, the subquery is used to substitute a value into the WHERE clause of the
parent query: it is returning a single value, used for comparison with the rows retrieved by
the parent query.
The subquery could return a set of rows. For example, you could use the following to find
all departments that do actually have one or more employees assigned to them:
select department_name from departments where department_id in
(select distinct(department_id) from employees);
Q3. - (Topic 1)
Which is a valid CREATE TABLE statement?
A. CREATE TABLE EMP9$# AS (empid number(2));
B. CREATE TABLE EMP*123 AS (empid number(2));
C. CREATE TABLE PACKAGE AS (packid number(2));
D. CREATE TABLE 1EMP_TEST AS (empid number(2));
Answer: A
Explanation: Table names and column names must begin with a letter and be 1-30
characters long. Characters A-Z,a-z, 0-9, _, $ and # (legal characters but their use is
discouraged).
Incorrect Answer:
BNon alphanumeric character such as “*” is discourage in Oracle table name.
DTable name must begin with a letter.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 9-4
Q4. - (Topic 1)
Examine the structure and data in the PRIC E_LIST table: Name Null? Type
PROD_D NOT NULL NUMBER(3)
PROD_PRICE VARCHAR2(10)
PROD_ID PROD PRICE
100 $234.55
101 $6,509.75
102 $1,234
in the same format as the PROD_PRICE. Which SQL statement would give the required result?
A. SELECT TO_CHAR(prod_price* .25.'$99.999.99') FROM PRICEJLIST:
B. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25.'$99.999.00') FROM PRICE_LIST;
C. SELECT TO_CRAR(TO_NUMBER(prod_price.'S99.999.99')* .25.'$99.999.00') FROM PRICE_LIST:
D. SELECT TO_NUMBER(TO_NUMBER(prod_price.,$99.999.99')* .25/$99.999.00') FROM PRICE_LIST:
Answer: C
Q5. - (Topic 2)
Evaluate the SQL statement
DROP TABLE DEPT:
Which four statements are true of the SQL statement? (Choose four)
A. You cannot roll back this statement.
B. All pending transactions are committed.
C. All views based on the DEPT table are deleted.
D. All indexes based on the DEPT table are dropped.
E. All data in the table is deleted, and the table structure is also deleted.
F. All data in the table is deleted, but the structure of the table is retained.
G. All synonyms based on the DEPT table are deleted.
Answer: A,B,D,E
Explanation:
You cannot roll back DROP TABLE statement. All pending transactions related on this
table are committed. If the table is dropped, Oracle automatically drops any index, trigger
and constraint associated with the table as well. All data in the table is deleted, and the
table structure is also deleted.
Incorrect Answers
C:All views based on the DEPT table become invalid, but they are not deleted.
F:All data in the table is deleted, and the table structure is also deleted. Command
TRUNCATE deletes all data in the table, but does not delete the structure of the table.
G:All synonyms based on the DEPT table are not deleted after dropping the table.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 225 Chapter 5: Creating Oracle Database Objects
Q6. - (Topic 1)
Which two statements are true regarding the COUNT function? (Choose two.)
A. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns
B. COUNT(cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column
C. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column
D. A SELECT statement using COUNT function with a DISTINCT keyword cannot have a WHERE clause
E. The COUNT function can be used only for CHAR, VARCHAR2 and NUMBER data types
Answer: A,C
Explanation:
Using the COUNT Function
The COUNT function has three formats:
COUNT(*)
COUNT(expr)
COUNT(DISTINCT expr)
COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT
statement, including duplicate rows and rows containing null values in any of the columns.
If a WHERE clause is included in the SELECT statement, COUNT(*) returns the number of
rows that satisfy the condition in the WHERE clause.
In contrast,
COUNT(expr) returns the number of non-null values that are in the column identified by
expr.
COUNT(DISTINCT expr) returns the number of unique, non-null values that are in the
column identified by expr.
Q7. - (Topic 2)
Examine the following SQL commands:
Which statement is true regarding the execution of the above SQL commands?
A. Both commands execute successfully.
B. The first CREATE TABLE command generates an error because the NULL constraint is not valid.
C. The second CREATE TABLE command generates an error because the CHECK constraint is not valid.
D. The first CREATE TABLE command generates an error because CHECK and PRIMARY KEY constraints cannot be used for the same column.
E. The first CREATE TABLE command generates an error because the column PROD_ID cannot be used in the PRIMARY KEY and FOREIGN KEY constraints.
Answer: B
Explanation:
Defining Constraints The slide gives the syntax for defining constraints when creating a table. You can create
constraints at either the column level or table level. Constraints defined at the column level
are included when the column is defined. Table-level constraints are defined at the end of
the table definition and must refer to the column or columns on which the constraint
pertains in a set of parentheses. It is mainly the syntax that differentiates the two;
otherwise, functionally, a columnlevel constraint is the same as a table-level constraint.
NOT NULL constraints must be defined at the column level.
Constraints that apply to more than one column must be defined at the table level.
Q8. - (Topic 1)
Which one is a system privilege?
A. SELECT
B. DELETE
C. EXECUTE
D. ALTER TABLE
E. CREATE TABLE
Answer: E
Q9. - (Topic 1)
Evaluate these two SQL statements:
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC;
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC;
What is true about them?
A. The two statements produce identical results.
B. The second statement returns a syntax error.
C. There is no need to specify DESC because the results are sorted in descending order by default.
D. The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement.
Answer: A
Explanation: Explanation: the two statement produce identical results as ORDER BY 2 will take the second column as sorting column.
Incorrect Answer: Bthere is no syntax error Cresult are sorted in ascending order by default DORDER BY 2 will take the second column as sorting column. Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-22
Q10. - (Topic 1)
The ORDERS TABLE belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR.
Which statement would create a synonym ORD so that HR can execute the following query successfully?
SELECT * FROM ord;
A. CREATE SYNONYM ord FOR orders; This command is issued by OE.
B. CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by OE.
C. CREATE SYNONYM ord FOR oe.orders; This command is issued by the database administrator.
D. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database administrator.
Answer: D
Explanation:
Creating a Synonym for an Object To refer to a table that is owned by another user, you need to prefix the table name with the name of the user who created it, followed by a period. Creating a synonym eliminates the need to qualify the object name with the schema and provides you with an alternative name for a table, view, sequence, procedure, or other objects. This method can be especially useful with lengthy object names, such as views. In the syntax: PUBLIC Creates a synonym that is accessible to all users synonym Is the name of the synonym to be created object Identifies the object for which the synonym is created Guidelines The object cannot be contained in a package. A private synonym name must be distinct from all other objects that are owned by the same user. If you try to execute the following command (alternative B, issued by OE):