Q1. - (Topic 1)
Examine the structure and data of the CUSTJTRANS table:
CUSTJRANS
Name Null? Type
CUSTNO NOT NULL CHAR(2) TRANSDATE DATE TRANSAMT NUMBER(6.2) CUSTNO TRANSDATE TRANSAMT
11 01-JAN-07 1000
22 01-FEB-07 2000
33 01-MAR-07 3000
Dates are stored in the default date format dd-mon-rr in the CUSTJTRANS table. Which three SQL statements would execute successfully? (Choose three.)
A. SELECT transdate + '10' FROM custjrans;
B. SELECT * FROM custjrans WHERE transdate = '01-01-07':
C. SELECT transamt FROM custjrans WHERE custno > '11':
D. SELECT * FROM custjrans WHERE transdate='01-JANUARY-07':
E. SELECT custno - 'A' FROM custjrans WHERE transamt > 2000:
Answer: A,C,D
Q2. - (Topic 1)
Where can sub queries be used? (Choose all that apply)
A. field names in the SELECT statement
B. the FROM clause in the SELECT statement
C. the HAVING clause in the SELECT statement
D. the GROUP BY clause in the SELECT statement
E. the WHERE clause in only the SELECT statement
F. the WHERE clause in SELECT as well as all DML statements
Answer: A,B,C,F
Explanation:
SUBQUERIES can be used in the SELECT list and in the FROM, WHERE, and HAVING
clauses of a query.
A subquery can have any of the usual clauses for selection and projection. The following
are required clauses:
A SELECT list
A FROM clause
The following are optional clauses: WHERE GROUP BY HAVING
The subquery (or subqueries) within a statement must be executed before the parent query that calls it, in order that the results of the subquery can be passed to the parent.
Q3. - (Topic 2)
View the Exhibit and examine the data in the EMPLOYEES table:
You want to display all the employee names and their corresponding manager names.
Evaluate the following query:
SQL> SELECT e.employee_name "EMP NAME", m.employee_name "MGR NAME"
FROM employees e ______________ employees m
ON e.manager_id = m.employee_id;
Which JOIN option can be used in the blank in the above query to get the required output?
Exhibit:
A. only inner JOIN
B. only FULL OUTER JOIN
C. only LEFT OUTER JOIN
D. only RIGHT OUTER JOIN
Answer: C
Q4. - (Topic 1)
Which is the valid CREATE [TABLE statement?
A. CREATE TABLE emp9$# (emp_no NUMBER(4));
B. CREATE TABLE 9emp$# (emp_no NUMBER(4));
C. CREATE TABLE emp*123 (emp_no NUMBER(4));
D. CREATE TABLE emp9$# (emp_no NUMBER(4). date DATE);
Answer: A
Explanation:
Schema Object Naming Rules
Every database object has a name. In a SQL statement, you represent the name of an
object with a quoted identifier or a nonquoted identifier.
A quoted identifier begins and ends with double quotation marks ("). If you name a schema
object using a quoted identifier, then you must use the double quotation marks whenever
you refer to that object.
A nonquoted identifier is not surrounded by any punctuation.
The following list of rules applies to both quoted and nonquoted identifiers unless otherwise
indicated:
Names must be from 1 to 30 bytes long with these exceptions:
Names of databases are limited to 8 bytes.
Names of database links can be as long as 128 bytes.
If an identifier includes multiple parts separated by periods, then each attribute can be up to
30 bytes long.
Each period separator, as well as any surrounding double quotation marks, counts as one
byte. For example, suppose you identify a column like this:
"schema"."table"."column"
Nonquoted identifiers cannot be Oracle Database reserved words (ANSWER D). Quoted identifiers can be reserved words, although this is not recommended. Depending on the Oracle product you plan to use to access a database object, names might be further restricted by other product-specific reserved words. The Oracle SQL language contains other words that have special meanings. These words include datatypes, schema names, function names, the dummy system table DUAL, and keywords (the uppercase words in SQL statements, such as DIMENSION, SEGMENT, ALLOCATE, DISABLE, and so forth). These words are not reserved. However, Oracle uses them internally in specific ways. Therefore, if you use these words as names for objects and object parts, then your SQL statements may be more difficult to read and may lead to unpredictable results. In particular, do not use words beginning with SYS_ as schema object names, and do not use the names of SQL built-in functions for the names of schema objects or user-defined functions. You should use ASCII characters in database names, global database names, and database link names, because ASCII characters provide optimal compatibility across different platforms and operating systems. Nonquoted identifiers must begin with an alphabetic character (ANSWER B - begins with 9) from your database character set. Quoted identifiers can begin with any character. Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and "at" signs (@). Oracle strongly discourages you from using $ and # in nonquoted identifiers. Quoted identifiers can contain any characters and punctuations marks as well as spaces. However, neither quoted nor nonquoted identifiers can contain double quotation marks or the null character (\0). Within a namespace, no two objects can have the same name. Nonquoted identifiers are not case sensitive. Oracle interprets them as uppercase. Quoted identifiers are case sensitive. By enclosing names in double quotation marks, you can give the following names to different objects in the same namespace: employees "employees" "Employees" "EMPLOYEES"
Note that Oracle interprets the following names the same, so they cannot be used for different objects in the same namespace: employees EMPLOYEES "EMPLOYEES" Columns in the same table or view cannot have the same name. However, columns in different tables or views can have the same name. Procedures or functions contained in the same package can have the same name, if their arguments are not of the same number and datatypes. Creating multiple procedures or functions with the same name in the same package with different arguments is called overloading the procedure or function.
Q5. - (Topic 1)
View the Exhibit and examine the structure of the PRODUCTS table. You need to generate a report in the following format: CATEGORIES 5MP Digital Photo Camera's category is Photo Y Box's category is Electronics Envoy Ambassador's category is Hardware Which two queries would give the required output? (Choose two.)
A. SELECT prod_name || q'''s category is ' || prod_category CATEGORIES FROM products;
B. SELECT prod_name || q'['s ]'category is ' || prod_category CATEGORIES FROM products;
C. SELECT prod_name || q'\'s\' || ' category is ' || prod_category CATEGORIES FROM products;
D. SELECT prod_name || q'<'s >' || 'category is ' || prod_category CATEGORIES FROM products;
Answer: C,D
Explanation:
So, how are words that contain single quotation marks dealt with? There are essentially two mechanisms available. The most popular of these is to add an additional single quotation mark next to each naturally occurring single quotation mark in the character string Oracle offers a neat way to deal with this type of character literal in the form of the alternative quote (q) operator. Notice that the problem is that Oracle chose the single quote characters as the special pair of symbols that enclose or wrap any other character literal. These character-enclosing symbols could have been anything other than single quotation marks. Bearing this in mind, consider the alternative quote (q) operator. The q operator enables you to choose from a set of possible pairs of wrapping symbols for character literals as alternatives to the single quote symbols. The options are any single-byte or multibyte character or the four brackets: (round brackets), {curly braces}, [squarebrackets], or <angle brackets>. Using the q operator, the character delimiter can effectively be changed from a single quotation mark to any other character The syntax of the alternative quote operator is as follows: q'delimiter'character literal which may include the single quotes delimiter' where delimiter can be any character or bracket.
Alternative Quote (q) Operator
Specify your own quotation mark delimiter.
Select any delimiter.
Increase readability and usability.
SELECT department_name || q'[ Department's Manager Id: ]'
|| manager_id
AS "Department and Manager"
FROM departments;
Alternative Quote (q) Operator
Many SQL statements use character literals in expressions or conditions. If the literal itself
contains a single quotation mark, you can use the quote (q) operator and select your own
quotation mark delimiter.
You can choose any convenient delimiter, single-byte or multibyte, or any of the following
character pairs: [ ], { }, ( ), or < >. In the example shown, the string contains a single quotation mark, which is normally interpreted as a delimiter of a character string. By using the q operator, however, brackets [] are used as the quotation mark delimiters. The string between the brackets delimiters is interpreted as a literal character string.
Q6. - (Topic 2)
Which substitution variable would you use if you want to reuse the variable without prompting the user each time?
A. &
B. ACCEPT
C. PROMPT
D. &&
Answer: D
Explanation:
To reuse the variable without prompting the user each time you can use && substitution
variable.
Incorrect Answers
A:This substitution variable will prompt the user each time.
B:ACCEPT is command, not substitution variable. It used to define more accurate or
specific prompt or when you want more output to display as the values are defined.
C:PROMPT is part of the ACCEPT command, it is not a variable.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 165-173
Chapter 4: Sub queries
Q7. - (Topic 2)
Examine the structure of the PROMOS table:
You want to generate a report showing promo names and their duration (number of days).
If the PROMO_END_DATE has not been entered, the message 'ONGOING' should be displayed. Which queries give the correct output? (Choose all that apply.)
A. SELECT promo_name, TO_CHAR(NVL(promo_end_date -promo_start_date,'ONGOING')) FROM promos;
B. SELECT promo_name,COALESCE(TO_CHAR(promo_end_date -promo_start_date),'ONGOING') FROM promos;
C. SELECT promo_name, NVL(TO_CHAR(promo_end_date -promo_start_date),'ONGOING') FROM promos;
D. SELECT promo_name, DECODE(promo_end_date
-promo_start_date,NULL,'ONGOING',promo_end_date - promo_start_date) FROM
promos;
E. SELECT
promo_name,ecode(coalesce(promo_end_date,promo_start_date),null,'ONGOING',
promo_end_date - promo_start_date)
FROM promos;
Answer: B,C,D
Q8. - (Topic 1)
See the Exhibits and examine the structures of PRODUCTS, SALES and CUSTOMERS table:
You issue the following query:
Which statement is true regarding the outcome of this query?
A. It produces an error because the NATURAL join can be used only with two tables
B. It produces an error because a column used in the NATURAL join cannot have a qualifier
C. It produces an error because all columns used in the NATURAL join should have a qualifier
D. It executes successfully
Answer: B
Explanation:
Creating Joins with the USING Clause
Natural joins use all columns with matching names and data types to join the tables. The USING clause can be used to specify only those columns that should be used for an equijoin.
The Natural JOIN USING Clause
The format of the syntax for the natural JOIN USING clause is as follows: SELECT table1.column, table2.column FROM table1 JOIN table2 USING (join_column1, join_column2…); While the pure natural join contains the NATURAL keyword in its syntax, the JOIN…USING syntax does not. An error is raised if the keywords NATURAL and USING occur in the same join clause. The JOIN…USING clause allows one or more equijoin columns to be explicitly specified in brackets after the USING keyword. This avoids the shortcomings associated with the pure natural join. Many situations demand that tables be joined only on certain columns, and this format caters to this requirement.
Q9. - (Topic 2)
View the Exhibit and examine the structure of the ORDERS and CUSTOMERS tables.
Evaluate the following SQL command:
SQL> SELECT o.order_id, c.cust_name, o.order_total, c.credit_limit FROM orders o JOIN customers c USING (customer_id) WHERE o.order_total > c.credit_limit FOR UPDATE ORDER BY o.order_id;
Which two statements are true regarding the outcome of the above query? (Choose two.)
A. It locks all the rows that satisfy the condition in the statement.
B. It locks only the columns that satisfy the condition in both the tables.
C. The locks are released only when a COMMIT or ROLLBACK is issued.
D. The locks are released after a DML statement is executed on the locked rows.
Answer: A,C
Explanation:
FOR UPDATE Clause in a SELECT Statement
.
Locks the rows in the EMPLOYEES table where job_id is SA_REP.
.
Lock is released only when you issue a ROLLBACK or a COMMIT.
.
If the SELECT statement attempts to lock a row that is locked by another user, the database waits until the row is available, and then returns the results of the SELECTstatement SELECT employee_id, salary, commission_pct, job_id FROM employees WHERE job_id = 'SA_REP' FOR UPDATE ORDER BY employee_id;
Q10. - (Topic 2)
You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty.
Which statement accomplishes this task?
A. ALTER TABLE students ADD PRIMARY KEY student_id;
B. ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);
C. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;
D. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id); E. ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
Answer: D
Explanation:
ALTER TABLE table_name
ADD [CONSTRAINT constraint] type (coloumn);
Incorrect Answer:
Awrong syntax
Bwrong syntax
Cwrong syntax
Eno such MODIFY keyword
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-17