• Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off
Reading...
Front

Card Range To Study

through

image

Play button

image

Play button

image

Progress

1/1974

Click to flip

Use LEFT and RIGHT arrow keys to navigate between flashcards;

Use UP and DOWN arrow keys to flip the card;

H to show hint;

A reads text to speech;

1974 Cards in this Set

  • Front
  • Back
TO_DSINTERVAL()
TO_DSINTERVAL()

TO_DSINTERVAL converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to an INTERVAL DAY TO SECOND type.

TO_DSINTERVAL accepts argument in one of the two formats:
■ SQL interval format compatible with the SQL standard (ISO/IEC 9075:2003)
■ ISO duration format compatible with the ISO 8601:2004 standard
It enables users to reuse the same query block in a SELECT statement if it occurs more than once in a complex query - Correct or Incorrect regarding the Benefits of using the WITH clause ?
Correct
View the Exhibit and examine the data in the EMPLOYEES tables.

Evaluate the following SQL statement:

SELECT employee_id, department_id
FROM employees
WHERE department_id= 50 ORDER BY department_id
UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id= 90
UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id= 10;

What would be the outcome of the above SQL statement?

A. The statement would execute successfully and display all the rows in the ascending order of
DEPARTMENT_ID.

B. The statement would execute successfully but it will ignore the ORDER BY clause and display
the rows in random order.

C. The statement would not execute because the positional notation instead of the column name
should be used with the ORDER BY clause.

D. The statement would not execute because the ORDER BY clause should appear only at the
end of the SQL statement, that is, in the last SELECT statement.
D
Which character classes defines only uppercase letters?
[:upper:]
If the list of ORDER BY expressions uses the “by position” form, then all expressions in the ORDER BY must use the “by position” form - Correct or Incorrect of ORDER BY clause ?
Incorrect
Evaluate this SELECT statement that includes a subquery:

SELECT last_name, first_name, phone_num
FROM prospect
WHERE postal_code IN (SELECT postal_code
FROM sales WHERE salesperson_id = 25);

Which two statements are true about the given subquery? (Choose two.)

The query on the prospect table executes before the query on the sales table.

THe results of the query on the sales table are returned to the query on the prospect table.

The results of the query on the prospect table are returned to the query on the sales table.

The query on the sales table executes once for the execution of the query on the prospect table.

The query on the sales and the query on the prospect table must both return a value or an error occurs
The results of the query on the sales table are returned to the query on the prospect table.

The query on the sales table executes once for the execution of the query on the prospect table.
Multitable Insert use a subquery to insert values - Correct or Incorrect ?
Correct
Which statement would you use to create a nonunique index on a table?
the CREATE INDEX statement
You work as a Database Administrator for Dolliver Inc. The company uses Oracle as its database. You have used the TO_DATE conversion function to subtract two date values.

The following query is used to accomplish the task:

SELECT TO_DATE('02-AUG-2007') - TO_DATE('25-JUL-2007') FROM DUAL;

Which of the following statements about the above mentioned SQL query is true?

A: Oracle will display an error message, as two date values cannot be subtracted.

B: Oracle will display 8 as the output.

C: Oracle will display 02-AUG-2007 as the output.

D: Oracle will display 25-JUL-2007 as the output
B: Oracle will display 8 as the output
You work as a Database Designer for Dolliver Inc. The company uses Oracle 11g as its database. The database contains a table named Employees. You issue the following set
of queries:

Q1: SELECT End_date, COUNT(*)
FROM EMPLOYEES;

Q2: SELECT End_date, Start_date, COUNT(*)
FROM EMPLOYEES GROUP BY End_date;

Q3: SELECT TO_CHAR (End_date, 'YYYY') "Year", COUNT(*) "Number of
employees" FROM EMPLOYEES
GROUP BY TO_CHAR(End_date, 'YYYY')
ORDER BY COUNT(*) DESC;

Q4: SELECT Start_date, End_date COUNT(*)
FROM EMPLOYEES GROUP BY Start_date
WHERE AVG(LENGTH(Emp_last_name)) > 8;

Which of the following queries will not generate an ORA error?

A: Q2
B: Q4
C: Q3
D: Q1
C: Q3
Pacific Standard Time is abbreviated PST. PST is an example of which format model element?
TZD
The CUBE operation can only be performed with:
GROUP BY
It returns 1 for those rows that have NULL values for the regular grouped rows - Correct or Incorrect about GROUPING FUNCTION ?
Incorrect
In which clauses of a SELECT statement can a scalar subquery NOT be used?
GROUP BY
Review this SQL statement: SELECT TRUNC(ROUND(ABS(-1.7),2)) FROM DUAL; What will be the result of the SQL statement?
1
Indexes should be created on columns that are frequently referenced as part of an expression - Correct or Incorrect about INDEXES ?
Incorrect
Which keywords can be used with the CREATE SEQUENCE statement?
CYCLE / MAXVALUE / INCREMENT
Evaluating MOD(20,3) returns what value?
2
Which database roles provides the privileges required to perform full and incremental database exports?
EXP_FULL_DATABASE
You use Synonyms to shorten lengthy table names - Correct or Incorrect about Synonyms ?
Correct
GREATEST()
GREATEST returns the greatest of the list of one or more expressions. Oracle Database uses the first expr to determine the return type. If the first expr is numeric, then Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type before the comparison, and returns
that data type. If the first expr is not numeric, then each expr after the first is implicitly converted to the data type of the first expr before the comparison.

Character comparison is based on the numerical codes of the characters in the database character set and is performed on whole strings treated as one sequence of bytes, rather than character by character. If the value returned by this function is
character data, then its data type is VARCHAR2 if the first expr is a character data type and NVARCHAR2 if the first expr is a national character data type.
WITH clause cannot be specified in any top-level SELECT statement and in most types of subqueries - Correct or Incorrect ?
Incorrect
Which database objects stores table column data and row reference information?
Indexes
Single Row Functions can modify the data type of the argument that is referenced. - True or False ?
1
Which database objects can be used to generate the Primary key value?
Sequence
Which is not supported by Oracle as an internal data type?
STRING
The DESCRIBE command would still display the sales_date column - Correct or Incorrect about ALTER TABLE … SET UNUSED ?
Incorrect
The child and parent tables must be on different database - Correct or Incorrect about FOREIGN KEY constraint ?
Incorrect
The USING clause is optional - Correct or Incorrect about the USING clause of MERGE statement ?
Incorrect
The actual data never gets stored inside the database - Correct or Incorrect about EXTERNAL tables ?
Correct
Consider the following query executed on a products table:

SELECT * FROM products GROUP BY CUBE(product_quantity, product_cost);

Which of the following statements is TRUE about the given query?

There is only one group.

There are two groups of rows.

There are three groups of rows.

There are four groups of rows.
There are four groups of rows.
Consider the following statement executed for an employee records database:

CREATE TABLE employee_info
(
emp_id NUMBER(5),
dept_id NUMBER(5),
designation VARCHAR(30),
salary NUMBER(5),
CONSTRAINT emp_pk PRIMARY KEY (emp_id))
ORGANIZATION INDEX
INCLUDING dept_id
OVERFLOW TABLESPACE employee_tablespace;

Which of the following statements are FALSE about the preceding statement? (Choose all that apply.)

The employee_info table is an index-organized table.

The employee_info table does not have an index.

The index block contains only the primary key emp_id.

The rows are sorted based on the emp_id and the dept_id columns.
The employee_info table does not have an index.

The index block contains only the primary key emp_id.

The rows are sorted based on the emp_id and the dept_id columns
LPAD()
LPAD returns expr1, left-padded to length n characters with the sequence of characters in expr2. This function is useful for formatting the output of a query.

Both expr1 and expr2 can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 data type if expr1 is a character data type, NVARCHAR2 if expr1 is a national character data type, and a LOB if expr1 is a LOB data type. The string returned is in the same character set as
expr1. The argument n must be a NUMBER integer or a value that can be implicitly
converted to a NUMBER integer.

If you do not specify expr2, then the default is a single blank. If expr1 is longer than n, then this function returns the portion of expr1 that fits in n.

The argument n is the total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multibyte character sets, the display length of a
character string can differ from the number of characters in the string.

The following example left-pads a string with the asterisk (*) and period (.) characters:

SELECT LPAD('Page 1',15,'*.') "LPAD example"
FROM DUAL;

LPAD example
---------------
*.*.*.*.*Page 1
Your business team has decided not to use the "SAL" column of the table T1 anymore
and asks you to drop it immediately. However, the table T1 is heavily accessed during business hours by users.

Which of the following options shows the correct method of dropping the "SAL" column from table T1?

A: Run the following statement during business hours:

ALTER TABLE T1 SET UNUSED (SAL);

B: Run the following statement after business hours:

ALTER TABLE T1 DROP COLUMN SAL;

C: Run the following statement during business hours:

ALTER TABLE T1 DROP COLUMN SAL;

D: Run the following statement during business hours:

ALTER TABLE T1 SET UNUSED (SAL);

and run the below statement after business hours:

ALTER TABLE T1 DROP UNUSED COLUMNS;
D: Run the following statement during business hours:
ALTER TABLE T1 SET UNUSED (SAL);

and run the below statement after business hours:
ALTER TABLE T1 DROP UNUSED COLUMNS;
You need to define a regular expression pattern that specifies a string of one or more alphabetic characters. Which patterns will do this?
'[[:alpha:]]+'
You work as a Database Administrator for Gadgets Inc. Two days ago, there was an
error in the accounting department. The error amounted to a few of the generated bills.
Now, you receive a call from a customer of Customer Id 6721 complaining about a bill
that was delivered to him. The complainant said that the total amount in the bill was overestimated by $400.

What will you do to confirm that the customer was actually affected by the error?

A: You will use a Flashback query as follows:

SELECT order_id, customer_id, order_total from orders as of timestamp (sysdate - 2)
where customer_id = 6721;

B: You will use a Flashback query as follows:

SELECT order_id, customer_id, order_total from orders as of timestamp (systimestamp - interval '2' minute) where customer_id = 6721;

C: You will use a Flashback query as follows:

SELECT order_id, customer_id, order_total from orders as of timestamp (to_timestamp ('2-sep-06 18:16:45. 845993', 'DD-Mon-RR HH24:MI:SS.FF'))
where customer_id = 6721;

D: You will use a Flashback query requiring undo data, which is now not present in the undo tablespace:

SELECT order_id, customer_id, order_total from orders as of timestamp (systimestamp - interval '2' month) where customer_id = 6721;
A: You will use a Flashback query as follows:
SELECT order_id, customer_id, order_total from orders
as of timestamp (sysdate - 2) where customer_id = 6721;
How do group functions handle NULL values?
Group functions ignore NULL values.
Susan maintains the database for a library. She needs to find the number of times the Word 'Database' appears in each book's title.

Which of the following functions should you use in this scenario?

REGEXP_INSTR
REGEXP_SUBSTR
REGEXP_COUNT
REGEXP_LIKE
REGEXP_COUNT
Which clauses is used to specify the target for the MERGE statement?
INTO
You work as a Database Administrator for Dolliver Inc. The company uses Oracle 11g as its database. The database contains a table named Employee. You want to alias a column named Emp_dependents to Employee Family Size and also retrieve the column.
The case of the alias should not change.

Which of the following SQL statements will you
use to accomplish the task?

Each correct answer represents a complete solution. Choose two.

A: SELECT EMP_dependents AS "Employee Family Size" FROM Employee;

B: SELECT EMP_dependents AS Employee Family Size FROM Employee;

C: SELECT EMP_dependents "Employee Family Size" FROM Employee;

D: SELECT EMP_dependents AS 'Employee Family Size' FROM Employee;
A: SELECT EMP_dependents AS "Employee Family Size" FROM Employee;

C: SELECT EMP_dependents "Employee Family Size" FROM Employee;
Which comparison operator is used when matching character patterns?
the LIKE operator
There are two tables A and B, which are to be joined to retrieve data from both of them. However, there is one condition that all the rows retrieved from A and B should be displayed in the result set even if they do not meet the join condition.

What kind of join will you perform to get the desired result set?

A: Left outer join
B: Cartesian product
C: Right outer join
D: Full outer join
D: Full outer join
INTERVAL '540' DAY(3) - INTERVAL '480' HOUR - INTERVAL '15 12:30' DAY TO MINUTE + INTERVAL '12:30' HOUR TO MINUTE; Which represent the values of the given expression?
INTERVAL '505' DAY(3) / INTERVAL '12120' HOUR(5)
It returns 1 for those rows that have subtotals computed by ROLLUP or CUBE - Correct or Incorrect about GROUPING FUNCTION ?
Correct
It finds all the DEFAULT values in the superaggregates for the groups specified in the GROUP BY clause - Correct or Incorrect regarding CUBE operator ?
Incorrect
A self join can never return a cartesian product - Correct or Incorrect about Self-Joins ?
Incorrect
What value is returned after executing the following statement? SELECT INSTR('How_long_is_a_piece_of_string?','_',5,3) FROM DUAL;
14
It can be used only with the SELECT clause - Correct or Incorrect regarding the usage of WITH clause in complex correlated queries ?
Correct
Which character function substitutes one string for another?
REPLACE. The syntax of the REPLACE function is REPLACE(column|expression, search_string [,'replacement_string'])
GRANT UPDATE ON inventory TO joe WITH GRANT OPTION; Which task was accomplished?
Only an object privilege was granted to user Joe.
Can subqueries contain group functions?
Yes
Indexes can be used to precompute complex values without using a trigger - Correct or Incorrect about Indexes ?
Correct
SYSDATE()
SYSDATE()

SYSDATE returns the current date and time set for the operating system on which the
database server resides. The data type of the returned value is DATE, and the format
returned depends on the value of the NLS_DATE_FORMAT initialization parameter. The
function requires no arguments. In distributed SQL statements, this function returns the date and time set for the operating system of your local database. You cannot use this function in the condition of a CHECK constraint.
The number generated by a sequence can be used only for one table - Correct or Incorrect about Sequences created in a single instance database ?
Incorrect
Single Row Functions cannot be nested - True or False ?
FALSE
Which commands is used to revoke system level privileges that were previously granted with the grant command?
Revoke
What are the general kinds of methods that can be declared in a type definition?
Static Methods, Member Methods, Constructor Methods
All of the following are DBA views that are used to describe all relational tables in the database except for which one? - USER_TABLES , DBA_TABLES, ALL_TABLES, DBA_TAB_COLUMNS
DBA_TAB_COLUMNS
What is the term for a subquery in which two or more values from the main query are matched to two or more values of the subquery?
a multicolumn subquery
It is not possible to define a foreign key constraint in a CREATE TABLE statement that contains an AS subquery clause - Correct or Incorrect about FOREIGN KEY constraint ?
Correct
Which can be used with the flashback query to obtain historic data that existed during the specified time frame?
Undo retention period
In order to avail the maximum benefit from the flash recovery area in Oracle 10g, you want to maximize its space. parameters. Which parameter / parameters will you configure to accomplish the task?
DB_RECOVERY_FILE_DEST_SIZE
It removes all the rows in the table and allows ROLLBACK - Correct or Incorrect about DELETE FROM statements ?
Correct
Which of the following set operators returns all rows selected by either query, including all duplicates?
UNION ALL
A Cartesian product generates a large number of rows, and the result of a Cartesian product is rarely used. This can be considered a disadvantage of a Cartesian product.

Which of the following are the reasons for using a Cartesian product?

Each correct answer represents a complete solution. Choose two.

A: It is used to simulate a reasonable amount of data.

B: It is used to retrieve the desired rows from a table.

C: It is used to retrieve all rows from a table.

D: It is used to join two tables
A: It is used to simulate a reasonable amount of data

D: It is used to join two tables
SESSIONTIMEZONE()
SESSIONTIMEZONE()

SESSIONTIMEZONE returns the time zone of the current session. The return type is a time zone offset (a character type in the format '[+|]TZH:TZM') or a time zone region name, depending on how the user specified the session time zone value in the most recent ALTER SESSION statement.

The following example returns the time zone of the current session:

SELECT SESSIONTIMEZONE FROM DUAL;
SESSION
-------
-08:00
Evaluate the following SQL statement:

DROP TABLE Order;
CREATE TABLE Order
(OrdNo NUMBER(3) PRIMARY KEY,
OrdName VARCHAR2(10)
OrderType VARCHAR2(10));
DROP TABLE Order;

FLASHBACK TABLE Order TO BEFORE DROP;

Which of the following statements is true regarding the above Flashback operation?

A: It will recover only the first Order table.

B: It will recover both the tables but the names would be changed to the ones assigned
in the RECYCLE BIN.

C: It will recover only the second Order table.

D: It does not recover any of the tables because Flashback is not possible in this case
C: It will recover only the second Order table
You work as a Database Designer for Dolliver Inc. The company uses Oracle 11g as its
database. The database contains a table named Job_recruitment. The table contains columns such as Joining_date, Leaving_date, Tot_salary, and Tot_incentive. The Joining_date and Leaving_date columns are of DATE data type. You perform the following arithmetic operation on these columns:

Joining_date - Leaving_date

What will the above arithmetic operation return?

A: It will return the result in VARCHAR2 data type.
B: It will return the result in NUMBER data type.
C: It will return the result in DATE data type.
D: It will return an ORA error.
B: It will return the result in NUMBER data type.
The ORDER BY clause specifies one or more terms by which the retrieved rows are sorted.These terms can only be column names - Correct or Incorrect of ORDER BY clause ?
Incorrect
When a correlated query is processed the following steps are taken.

1. The inner query returns qualified rows only.
2. The final result set is displayed.
3. A row is fetched by the outer parent query.
4. The row is processed by the inner sub-query along with other column values.
5. The row values are passed on to the inner query.
6. The outer query processes the next row from the table in similar manner till no rows are left to be processed

Which of the following is the correct order in which these steps are followed?

A: 6, 3, 5, 4, 1, 2
B: 3, 5, 4, 1, 6, 2
C: 5, 3, 1, 6, 4, 2
D: 3, 5, 1, 4, 6, 2
B: 3, 5, 4, 1, 6, 2
In which of the following is the SELECT query of a subquery always enclosed?
Parentheses
View the Exhibit and examine the data in the CUST_DET table.

You executed the following multitable INSERT statement:

INSERT FIRST
WHEN credit_limit >= 5000 THEN
INTO cust_1 VALUES(cust_id, credit_limit, grade, gender) WHEN grade = THE INTO cust_2 VALUES(cust_id, credit_limit, grade, gender)
WHEN gender = THE INTO cust_3 VALUES(cust_id, credit_limit, grade, gender)
INTO cust_4 VALUES(cust_id, credit_limit, grade, gender)
ELSE
INTO cust_5 VALUES(cust_id, credit_limit, grade, gender)
SELECT * FROM cust_det;

The row will be inserted in________.

A. CUST_1 table only because CREDIT_LIMIT condition is satisfied

B. CUST_1 and CUST_2 tables because CREDIT_LIMIT and GRADE conditions are satisfied

C. CUST_1 ,CUST_2 and CUST_5 tables because CREDIT_LIMIT and GRADE conditions are satisfied but GENDER condition is not satisfied

D. CUST 1, CUST 2 and CUST 4 tables because CREDIT LIMIT and GRADE conditions are
satisfied for CUST 1 and CUST 2, and CUST 4 has no condition on it
A
Which comparison operator allows you to compare a value to a range of values?
the BETWEEN... AND operator
View the Exhibit and examine the data in the DEPARTMENTS tables.

Evaluate the following SQL statement:

SELECT department_id "DEPT_ID", department_name , 'b' FROM departments
WHERE department_id=90
UNION
SELECT department_id, department_name DEPT_NAME, 'a' FROM departments
WHERE department_id=10

Which two ORDER BY clauses can be used to sort the output of the above statement? (Choose
two.)

A. ORDER BY 3;
B. ORDER BY 'b';
C. ORDER BY DEPT_ID;
D. ORDER BY DEPT NAME
A,C
List the values that would match the following regular expression: ['abc']{3,5}
abcabcabc or abcabcabcabc or abcabcabcabcabc
The ROLLUP operator delivers aggregates and super aggregates for expressions within a GROUP BY statement - Correct or Incorrect about ROLLUP operator ?
Correct
It returns 0 for those rows that have subtotals computed by ROLLUP or CUBE - Correct or Incorrect about GROUPING FUNCTION ?
Incorrect
Given in the table below is the list of meta character syntaxes and their description in
random order. Identify the option that correctly matches the meta character syntaxes
with their descriptions.

S.No Meta Character
Syntax
1 ^a
2 [^ ]
3 |
4 \

Description
a Is used to ensure that a character is not in the list.
b Matches the pattern at the beginning of the string.
c Treats the subsequent meta character as a literal.
d Matches one of the character, such as the OR operator.

A: 1-b, 2-c, 3-b, 4-d
B: 1-b, 2-a, 3-d, 4-c
C: 1-a, 2-d, 3-a, 4-b
D: 1-d, 2-c, 3-a, 4-b
B: 1-b, 2-a, 3-d, 4-c
Table aliases should be used to self join the table - Correct or Incorrect about Self-Joins ?
Correct
A WITH clause can occur inside another WITH clause - Correct or Incorrect about the WITH clause ?
Incorrect
If you want to calculate the number of days between the two given dates, which date function should you use?
MONTHS_BETWEEN
Which of the following functions is used when a non-NULL value needs to be returned in place of a NULL value?
NVL2
Which operators returns all distinct rows selected by the first query but not the second?
MINUS
Which attributes of the CREATE SEQUENCE statement is used to specify the number of values of the sequence the database has preallocated and kept in memory for faster access?
CACHE
TO_CHAR may convert numbers to character items - Correct or Incorrect ?
Correct
NVL2()
NVL2()

NVL2 lets you determine the value returned by a query based on whether a specified expression is null or not null. If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3.

The argument expr1 can have any data type. The arguments expr2 and expr3 can have any data types except LONG.

If the data types of expr2 and expr3 are different, then Oracle Database implicitly converts one to the other. If they cannot be converted implicitly, then the database returns an error. If expr2 is character or numeric data, then the implicit conversion is implemented as follows:

■ If expr2 is character data, then Oracle Database converts expr3 to the data type of expr2 before returning a value unless expr3 is a null constant. In that case, a data type conversion is not necessary, and the database returns VARCHAR2 in the character set of expr2.
■ If expr2 is numeric data, then Oracle Database determines which argument has the highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type.
EMP is a private synonym for the OE.EMPLOYEES table. DROP SYNONYM EMP; Which statement is true regarding the above SQL statement?
Only the synonym would be dropped
The DBA_TAB_PRIVS data dictionary view allows a user account to see object privileges it has granted to other user accounts - Correct or Incorrect ?
Correct
For a user to create a table successfully in a database, which of the following holds true?
Grant the user CREATE TABLE Privilege, Allocate quota on a tablespace to that user.
The OR REPLACE option is used to change the definition of an existing view without dropping and recreating it - Correct or Incorrect about a VIEW ?
Correct
Which data types are variable length?
BLOB, LONG, NUMBER, RAW, VARCHAR2
In which of the following SQL clauses is the sub-query usually placed while writing a multiple-column sub-query?
FROM, WHERE
Tasks Performed By - LPAD --
Pads the character value right-justified to a total width of n character positions.
Rows are either inserted or updated in the target table during a single pass of the source table - Correct or Incorrect about MERGE statement ?
Correct
It returns versions of rows only within a transaction - Correct or Incorrect about FLASHBACK Version Query?
Incorrect
What does ACID stand for?
Atomicity, Consistency, Isolation, and Durability
How does the USER_TABLES dictionary view differ from the ALL_TABLES data dictionary view?
USER_TABLES will display only the tables owned by the user
CONCAT()
CONCAT returns char1 concatenated with char2. Both char1 and char2 can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is in the same character set as char1. Its data type depends on the data types of the arguments.

In concatenations of two different data types, Oracle Database returns the data type that results in a lossless conversion. Therefore, if one of the arguments is a LOB, then the returned value is a LOB. If one of the arguments is a national data type, then the returned value is a national data type. For example:

■ CONCAT(CLOB, NCLOB) returns NCLOB
■ CONCAT(NCLOB, NCHAR) returns NCLOB
■ CONCAT(NCLOB, CHAR) returns NCLOB
■ CONCAT(NCHAR, CLOB) returns NCLOB

This function is equivalent to the concatenation operator (||).

This example uses nesting to concatenate three character strings:

SELECT CONCAT(CONCAT(last_name, '''s job category is '), job_id) "Job" FROM employees
WHERE employee_id = 152;

Job
------------------------------------------------------
Hall's job category is SA_REP
View the Exhibit and examine the description of the EMPLOYEES table.

You want to display the EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT_ID for all the
employees who work in the same department and have the same manager as that of the
employee having EMPLOYEE_ID 104.

To accomplish the task, you execute the following SQL statement:

SELECT employee_id, first_name, department_id
FROM employees
WHERE (manager_id, department_id) =(SELECT department_id, manager_id FROM employees
WHERE employee_id = 104)
AND employee_id <> 104;

When you execute the statement it does not produce the desired output. What is the reason for
this?

A. The WHERE clause condition in the main query is using the = comparison operator, instead of EXISTS.

B. The WHERE clause condition in the main query is using the = comparison operator, instead of the IN operator.

C. The WHERE clause condition in the main query is using the = comparison operator, instead of the = ANY operator.

D. The columns in the WHERE clause condition of the main query and the columns selected in the
subquery should be in the same order.
D
Given below are two tables: one contains the date time data type and the other one contains a list of values stored in them in a random order:

Data Type
-------------
1 INTERVAL YEAR TO MONTH
2 TIMESTAMP WITH LOCAL TIME ZONE
3 TIMESTAMP WITH TIME ZONE
4 INTERVAL DAY TO SECOND

Example
-----------
a '2008-06-15 8:00:00 -8:00'
b '+06 03:30 18.000000'
c '18-JAN-03 12.00.00.000000 AM'
d '+04-00'

Identify the option that correctly matches the data types with the values given in the example.

A: 1-b, 2-c, 3-d, 4-c
B: 1-d, 2-c, 3-a, 4-b
C: 1-b, 2-a, 3-c, 4-d
D: 1-d, 2-a, 3-b, 4-c
B: 1-d, 2-c, 3-a, 4-b
You work as a Database Administrator for Gadgets Inc. The company uses Oracle as its
database. One of the policies of the company is to provide incentives to its deserving employees. Now, you want to retrieve the salaries of those employees who are eligible for incentives. You also want to sort the data by the employee's first name in ascending order.

Which of the following clauses will you use to sort the data?

A: HAVING
B: DISTINCT
C: GROUP BY
D: ORDER BY
D: ORDER BY
Which features is used to retrieve metadata and historical data for a specific time interval?
Oracle Flashback Version Query
ROWIDTOCHAR()
ROWIDTOCHAR()

ROWIDTOCHAR converts a rowid value to VARCHAR2 data type. The result of this
conversion is always 18 characters long.
Can you sort by a column that is NOT included in the SELECT list?
Yes, you can include a column in the ORDER BY clause even if it is not included in the SELECT list.
You are the DBA in a retail store selling different products. You execute the following query to display records from the orders_info table:

SELECT * FROM orders_info
GROUP BY GROUPING SETS product_id,(order_id, order_date), ROLLUP(order_id, cost,
delivery_status);

How many groups are created in total as a result of the given query?

3
4
5
6
6
The HAVING clause is used to exclude one or more aggregated results after grouping data. - Correct or Incorrect ?
Correct
Group Functions can be passed as an argument to another group function - Correct or Incorrect ?
Correct
GROUP BY GROUPING SETS is an alternative to which of the following?
GROUP BY CUBE
View the Exhibit and examine the descriptions of the EMPLOYEES and DEPARTMENTS tables.

The following SQL statement was executed:

SELECT e.department_id, e.job_id, d.location_id, sum(e.salary) total, GROUPING(e. department_id) GRP_DEPT, GROUPING(e.job_id) GRPJOB,
GROUPING(d. location_id) GRP_LOC
FROM employees e JOIN departments d
ON e.department_id = d.department_id
GROUP BY ROLLUP (e.department_id, e.job_id, d.location_id);

View the Exhibit2 and examine the output of the command.

Which two statements are true regarding the output? (Choose two.)

A. The value 1 in GRP_LOC means that the LOCATION_ID column is taken into account to
generate the subtotal.

B. The value 1 in GRPJOB and GRP_LOC means that JOB_ID and LOCATION_ID columns are not taken into account to generate the subtotal.

C. The value 1 in GRPJOB and GRP_LOC means that the NULL value in JOBJD and
LOCATIONJD columns are taken into account to generate the subtotal.

D. The value 0 in GRP_DEPT, GRPJOB, and GRP_LOC means that DEPARTMENT_ID, JOB_ID,and LOCATION_ID columns are taken into account to generate the subtotal
B,D
You are the database administrator in a corporate organization. Records of all employees are stored in the employees table, while records of all retired employees are stored in the retired_employees table. You need to remove the rows from the employees table for all the employees who have retired before 1990.

Which of the following statements should you use to achieve the desired results?

DELETE FROM retired_employees
WHERE retire_year<1990;

DELETE FROM retired_employees
WHERE emp_id IN (SELECT emp_id FROM employees);

DELETE FROM employees
WHERE emp_id IN (SELECT emp_id FROM retired_employees WHERE retire_year<1990);

DELETE FROM employees
WHERE emp_id NOT IN (SELECT emp_id FROM retired_employees WHERE retire_year<1990);
DELETE FROM employees
WHERE emp_id IN (SELECT emp_id FROM retired_employees WHERE retire_year<1990);
A self-join is:
A SELECT statement that specifies one table twice in the FROM clause / A SELECT statement that joins a table to itself by connecting a column in the table to a different column in the same table
A NOT NULL constraint is stored in the data dictionary as a UNIQUE constraint - Correct or Incorrect about NOT NULL constraints ?
Incorrect
What is the maximum number of decimal positions you can specify for a timestamp datatype?
9
It is used to identify if the NULL value in an expression is a stored NULL value or created by ROLLUP of CUBE - Correct or Incorrect about GROUPING function ?
Correct
Which expression will add five days to the current date?
SYSDATE + 5
What is another name for an outer query?
the main query
TO_DATE may convert character items to date items - Correct or Incorrect ?
Correct
View the exhibit and examine the data in the Sales_Master and Monthly_Sales tables:

Sales_Master
Sale_ID Sale_Total
1 1000
2 3000
3 5000
4
Monthly_Sales
Sale_ID Sale_Total
2 35000
3

Based on the above two tables, evaluate the following MERGE statement and identify
the outcome of the following SQL statement:

MERGE INTO Sales_Master s
USING Monthly_Sales m
ON (s.Sale_ID=m.Sale_ID)
WHEN MATCHED THEN
UPDATE SET s.Sale_Total=m.Sale_Total
DELETE WHERE(m.Sale_Total IS NULL)
WHEN NOT MATCHED THEN
INSERT VALUES (m.Sale_ID, m.Sale_Total);

A: The Sales_Master table would contain the Sale_IDs 1, 2, 3, and 4.
B: The Sales_Master table would contain the Sale_IDs 1, 2, and 3.
C: The Sales_Master table would contain the Sale_IDs 1, 2, and 4.
D: The Sales_Master table would contain the Sale_IDs 1 and 2.
C: The Sales_Master table would contain the Sale_IDs 1, 2, and 4.
Which statement will user Barbara use to create a private synonym when referencing the EMPLOYEE table existing in user Chan's schema?
CREATE SYNONYM emp FOR chan.employee;
What can be granted to a role?
System privileges / Object privileges / Roles
Which should you use along with the view to change the definition of an existing view without dropping and recreating?
OR REPLACE
A column with the UNIQUE constraint may contain NULL - True or False ?
1
Which operations can a user perform once he is granted the system level privilege?
Perform DBA activities / Alter session related parameters / Connect to the database
Can you use CURRVAL or NEXTVAL As the DEFAULT value of a column in the CREATE TABLE or ALTER TABLE command ?
No
What is the significance of DUAL ?
DUAL is a dummy table used to view results returned by functions.
What is the relationship between schemas and tablespaces?
No relationship
An external table does not describe how data is stored in the external source - Correct or Incorrect about EXTERNAL tables ?
Correct
TRUNCATE is better if all the rows are to be deleted from a table - Correct or Incorrect ?
Correct
When you’re looking for a particular bit of data and you’re not sure where in the data dictionary it might be, a good starting point is:
SELECT * FROM DICTIONARY;
Which statements is used to remove entire row of data from a specified table or view?
DELETE statement
You are the database administrator of a large department store. You need to store and maintain data about the different products available.

Assume that productid, orderid, and customerid are all datatype varchar2(8).

You execute the following query:

SQL> SELECT productid, to_char(NULL), to_char(NULL) FROM products
UNION ALL
(SELECT productid, orderid, to_char(NULL) FROM orders
UNION
SELECT productid, orderid, customerid FROM customers) ORDER BY 3;

Which of the following statements are TRUE about the output of this query? (Choose all that apply).

The output contains rows sorted by the customerid column in ascending order.

The output contains rows sorted by the customerid column in descending order.

An error occurs because the ORDER BY clause uses a column that does not exist in the first SELECT statement.

An ORDER BY clause appears at the end of the last query when using SET operators.
The output contains rows sorted by the customerid column in ascending order.

An ORDER BY clause appears at the end of the last query when using SET operators.
Evaluate this CREATE TABLE statement (line numbers are for reference only):

1. CREATE TABLE order*1 (
2. order# NUMBER(9),
3. cust_id NUMBER(9),
4. date_1 DATE DEFAULT SYSDATE);

Which line of this statement will cause an error?
1
2
3
4
1
GROUPING()
GROUPING distinguishes superaggregate rows from regular grouped rows. GROUP BY
extensions such as ROLLUP and CUBE produce superaggregate rows where the set of all values is represented by null. Using the GROUPING function, you can distinguish a null representing the set of all values in a superaggregate row from a null in a regular row.

The expr in the GROUPING function must match one of the expressions in the GROUP BY clause. The function returns a value of 1 if the value of expr in the row is a null representing the set of all values. Otherwise, it returns zero. The data type of the value returned by the GROUPING function is Oracle NUMBER
What is the default sort order when using an ORDER BY clause?
ascending
James works as a Database Developer for Bluewell Inc. The company has a SQL Server
database named Department. The Department database has a table named Employee
that contains information of all the employees who work in their respective departments.

There are six departments in the company: Marketing, Production, Sales, Accounts,
Purchase, and Finance. An employee can work in more than one department. James wants to retrieve the Emp_ID, FirstName, and LastName columns of those employees who work in either Dallas or Houston.

Which of the following SELECT statements can James use to accomplish the task?

Each correct answer represents a complete solution. Choose all that apply.

A: SELECT ID, FirstName, LastName
WHERE UPPER(City) IN 'DALLAS' OR 'HOUSTON' FROM Employee

B: SELECT * FROM Employee
WHERE UPPER(City) LIKE 'DALLAS' OR 'HOUSTON'

C: SELECT ID, FirstName, LastName
FROM Employee
WHERE UPPER(City) LIKE 'DALLAS' OR UPPER(City) LIKE 'HOUSTON'

D: SELECT ID, FirstName, LastName
FROM Employee
WHERE UPPER(City) IN ('DALLAS', 'HOUSTON')
C: SELECT ID, FirstName, LastName
FROM Employee
WHERE UPPER(City) LIKE 'DALLAS' OR UPPER(City) LIKE 'HOUSTON'

D: SELECT ID, FirstName, LastName
FROM Employee
WHERE UPPER(City) IN ('DALLAS', 'HOUSTON')
Group Funhctions can be used only with the SQL statement that has the GROUP BY clause - Correct or Incorrect ?
Incorrect
Which group function would you use to determine the number of rows that meet a certain condition?
COUNT(*)
Consider the statements given below:

SQL> CREATE ROLE R1;
Role created

SQL> GRANT INSERT ON EMP TO R1;
Grant succeeded

SQL> GRANT UPDATE ON EMP TO R1;
Grant succeeded

SQL> GRANT DELETE ON EMP TO R1;
Grant succeeded

SQL> GRANT R1 TO SCOTT;
Grant succeeded

SQL> GRANT R1 TO BLAKE;
Grant succeeded

If you want to revoke only INSERT and DELETE on EMP from SCOTT, how will you achieve
this task?

A: REVOKE R1 FROM SCOTT;
GRANT UPDATE ON EMP TO SCOTT;

B: REVOKE INSERT ON EMP FROM R1;
REVOKE DELETE ON EMP FROM R1;

C: REVOKE R1 FROM SCOTT;

D: REVOKE INSERT ON EMP FROM SCOTT;
REVOKE DELETE ON EMP FROM SCOTT;
A: REVOKE R1 FROM SCOTT;
GRANT UPDATE ON EMP TO SCOTT;
Which meta characters enables you to match one or more occurrences of an expression?
+
Which of the following regular expression functions search a given pattern in the source
string and returns the row(s) that satisf(ies)y the specified condition?

Each correct answer represents a complete solution. Choose all that apply

A: REGEXP_SUBSTR
B: REGEXP_REPLACE
C: REGEXP_LIKE
D: REGEXP_INSTR
A: REGEXP_SUBSTR
C: REGEXP_LIKE
The ID column in the CUSTOMER table that corresponds to the CUSTOMER_ID column of the CURR_ORDER table contains null values for rows that need to be displayed. Which type of join should you use to display the data?
outer join
View the Exhibit and examine DEPARTMENTS and the LOCATIONS tables.

Evaluate the following SOL statement:

SELECT location_id, city FROM locations
I WHERE NOT EXISTS (SELECT location_id
FROM departments WHERE location_id <>

A. The statement would execute and would return the desired results.

B. The statement would not execute because the = comparison operator is missing in the WHERE
clause of the outer query.

C. The statement would execute but it will return zero rows because the WHERE clause in the
inner query should have the = operator instead of <>.

D. The statement would not execute because the WHERE clause in the outer query is missing the
column name for comparison with the inner query result
C
What is the default number of decimal positions stored in a timestamp datatype?
6
It calculates super aggregates - Correct or Incorrect about ROLLUP operation ?
Correct
They help in retrieving data from a database table whose rows have a hierarchical relationship - Correct or Incorrect about Hierarchial Queries ?
Correct
Oracle allows up to 255 levels of subqueries in the WHERE clause - True or False in regard to subquery ?
1
TO_CHAR may convert date items to character items - Correct or Incorrect ?
Correct
A subquery used in an INTO clause of a SELECT statement must return only one column, but can return multiple rows - True or False regarding Subquery ?
1
The DBA_TAB_PRIVS data dictionary view allows a user account to see object privileges granted by other user accounts to itself - Correct or Incorrect ?
Correct
The DROP SYNONYM statement removes the synonym, and the status of the table on which the synonym has been created becomes invalid - Correct or Incorrect about SYNONYMS ?
Incorrect
Which specifies the rows returned by a hierarchical query?
LEVEL
How can a subquery make reference to a table in the main query?
Through the use of a table alias
GRANT CREATE TABLE, SELECT ON oe.orders TO emp, - Correct or Incorrect ?
Incorrect - because system privileges and object privileges cannot be granted together in a single GRANT statement
The DROP SYNONYM statement removes the synonym, and the status of the table on which the synonym has been created becomes invalid - Correct or Incorrect about SYNONYMS ?
Incorrect
Which SQL statements will display the time zone offset for US/Alaska time zone?
SELECT TZ_OFFSET ('US/Alaska') FROM DUAL;
Which is a quick way of selecting all columns?
Asterisk (*)
Which option for a column in a CREATE TABLE statement indicates the value that the column should be given if no value is explicitly specified on an INSERT statement?
the DEFAULT option
If a table or the base table of a view contains one or more domain index columns,then this statement executes the appropriate indextype update routine - Correct or Incorrect about UPDATE statements ?
Correct
Only two columns may be compared between the parent and the subquery - Correct or Incorrect about the Multiple Column Subquery ?
Incorrect
You are tasked with querying the data dictionary view that lists only those sequences to which you currently have privileges, but don’t necessarily own. To do this, you log in to your own user account and query the data dictionary view called:
ALL_SEQUENCES
What happens when the = operator is used with a multiple-row subquery?
An ORA-01427: single-row subquery returns more than one row error occurs
The SET clause is used to update multiple columns of a table separated by commas - Correct or Incorrect about UPDATE statement ?
Correct
Which keys automatically have indexes?
Primary and unique keys
You work as a Database Administrator for Gadgets Inc. The company uses Oracle as its
database. You want to retrieve the total number of employees whose last_name ends with a letter s.

Which of the following queries will you use to accomplish the task?

Each correct answer represents a complete solution. Choose two.

A: SELECT COUNT (*) FROM employee WHERE SUBSTR (last_name, 1) = 's';

B: SELECT COUNT(*) FROM employee WHERE SUBSTR (last_name, -1) = 's';

C: SELECT COUNT(*) FROM employee WHERE last_name LIKE '%s';

D: SELECT COUNT(*) FROM employee WHERE last_name IN (n, s);
B: SELECT COUNT(*) FROM employee WHERE SUBSTR (last_name, -1) = 's';

C: SELECT COUNT(*) FROM employee WHERE last_name LIKE '%s';
Given below is a list of functions and their purpose in random order.

Function Purpose
1)NVL a) Used for evaluating NOT NULL and NULL values

2)NULLIF b) Used to return the first non- null values in a list of expressions

3)COALESCE c) Used
to compare two expressions. If both are same, it returns NULL; otherwise, it returns only the first expression.

4)NVL2 d) Used to convert NULL values to actual values

Identify the correct combination of functions and their usage.

A. 1-a,2-c,3-b,4-d
B. 1-d,2-c,3-b,4-a
C. 1-b,2-c,3-d,4-a
D. 1-d,2-b,3-c,4-a
B
TO_YMINTERVAL()
TO_YMINTERVAL()

TO_YMINTERVAL converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to an INTERVAL YEAR TO MONTH type.
TO_YMINTERVAL accepts argument in one of the two formats:

■ SQL interval format compatible with the SQL standard (ISO/IEC 9075:2003)
■ ISO duration format compatible with the ISO 8601:2004 standard

The following example calculates for each employee in the sample hr.employees table a date one year two months after the hire date:

SELECT hire_date, hire_date + TO_YMINTERVAL('01-02') "14 months" FROM employees;

HIRE_DATE 14 months
--------- ---------
17-JUN-03 17-AUG-04
21-SEP-05 21-NOV-06
13-JAN-01 13-MAR-02
20-MAY-08 20-JUL-09
21-MAY-07 21-JUL-08
The personnel table contains these columns:

id NUMBER(9)
last_name VARCHAR2(25)
first_name VARCHAR2(25)
manager_id NUMBER(9)
dept_id NUMBER(9)

Evaluate this SQL statement:

SELECT p.dept_id, p.first_name|| ' ' ||p.last_name employee,c.first_name|| ' ' ||c.last_name coworker
FROM personnel p, personnel c WHERE p.dept_id = c.dept_id AND p.id <> c.id;

Which result will the statement provide?

It will display each employee's department number, name, and manager's name.

It will display each employee's department number, name, and all coworkers in the same department.

It will display each department, the manager in each department, and all the employees in each department.

It will return a syntax error.
It will display each employee's department number, name, and all coworkers in the same department.
They can be used along with the single-row function in the SELECT clause of a SQL statement - Correct or Incorrect ?
Correct
Which CREATE TABLE statement is valid?

A. CREATE TABLE ord_details
(ord_no NUMBER(2) PRIMARY KEY,
item_no NUMBER(3) PRIMARY KEY,
ord_date date NOT NULL);

B. CREATE TABLE ord_details
(ord_no NUMBER(2) UNIQUE, NOT NULL,
item_noNUMBER(3),
ord_date date DEFAULT SYSDATE NOT NULL);

C. CREATE TABLE ord_details
(ord_no NUMBER(2) ,
item_noNUMBER(3),
ord_date date DEFAULT NOT NULL,
CONSTRAINT ord_uq UNIQUE (ord_no),
CONSTRAINT ord_pk PRIMARY KEY (ord_no));

D. CREATE TABLE ord_details
(ord_no NUMBER(2),
item_noNUMBER(3),
ord_date date DEFAULT SYSDATE NOT NULL,
CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));
D
Which of the following syntaxes is used to specify that multiple levels of grouping should be computed at once?
ROLLUP
The service table contains these columns:

id NUMBER Primary Key
service-date DATE
technician_id NUMBER
description VARCHAR2(50)

Which SELECT statement could you use to display the number of times each technician performed a service between January 1, 2008 and June 30, 2008?

SELECT COUNT(*)
FROM service
WHERE service_date BETWEEN '01-JAN-2008' AND '30-JUN-2008' GROUP BY service_date;

SELECT COUNT(service_date)
FROM service
WHERE service_date BETWEEN '01-JAN-2008' AND '30-JUN-2008' GROUP BY service_date;

SELECT technician_id, service_date, COUNT(*)
FROM service
WHERE service_date BETWEEN '01-JAN-2008' AND '30-JUN-2008' ORDER BY technician_id, service_date;

SELECT technician_id, COUNT(technician_id)
FROM service
WHERE service_date BETWEEN '01-JAN-2008' AND '30-JUN-2008' GROUP BY technician_id;
SELECT technician_id, COUNT(technician_id)
FROM service
WHERE service_date BETWEEN '01-JAN-2008' AND '30-JUN-2008' GROUP BY technician_id;
Evaluate the following expression using meta character for regular expression: '[^Ale|ax.r$]' Which matches would be returned by this expression?
Alexender / Alaxendar
The po_detail table was created using this
CREATE TABLE statement:

CREATE TABLE po_detail
(po_num NUMBER NOT NULL,
po_line_id NUMBER NOT NULL,
product_id NUMBER NOT NULL,
quantity NUMBER(3) NOT NULL,
unit_price NUMBER (5,2) DEFAULT 0,
PRIMARY KEY( po_num, po_line_id),
FOREIGN KEY (po_num) REFERENCES PO_HEADER(po_num),
FOREIGN KEY (product_id) REFERENCES product(product_id),
CHECK (unit_price BETWEEN 0 and 9999.99))
TABLESPACE USERS;

Which two INSERT statements will execute successfully? (Choose two.)

INSERT INTO po_detail (po_num, po_line_id, product_id, unit_price, quantity)
VALUES ('10056','1','3','400','1052.40');

INSERT INTO po_detail
VALUES (10055,1,2,30,DEFAULT);

INSERT INTO po_detail (po_num, po_line_id, product_id, quantity, unit_price)
VALUES (10052,2,3, 200, NULL);

INSERT INTO po_detail VALUES (10056,1,3,400,52.40);

INSERT INTO po_detail VALUES (10055,1,2,NULL,NULL);
INSERT INTO po_detail
VALUES (10055,1,2,30,DEFAULT);

INSERT INTO po_detail VALUES (10056,1,3,400,52.40);
The parent query can reference the columns specified in the subquery - Correct or Incorrect about Correlated Subquery ?
Incorrect
You work as a Database Administrator for Dolliver Inc. The company uses Oracle as its database.

The database contains a table named Employee.

You issue the following SQL statement:

SELECT emp_name, SUM(salary)
FROM employee
WHERE SUM(salary) >6000
GROUP BY emp_name;

Which of the following lines will generate an error message?

A: Line 3
B: Line 2
C: Line 1
D: Line 4
A: Line 3
It is read only - Correct or Incorrect about Oracle Data Dictionary ?
Correct
Where You can use regular expression function ?
Anywhere you can use a SQL function of a comparable datatype
It is used to identify the NULL value in the aggregate functions - Correct or Incorrect about GROUPING function ?
Incorrect
There can be only root row in the hierarchy - Correct or Incorrect about Hierarchial Queries ?
Incorrect
Which clause can you include in a CREATE VIEW statement to ensure that DML operations that would change the result of the view are not allowed?
WITH CHECK OPTION
If you wish to display a numeric value with dollar signs and commas, which is the best approach to take?
The TO_CHAR function with a format model
Which construct can be used to return data based on an unknown condition?
a subquery
If the sub query returns 0 rows, then the value returned by the sub query expression is NULL - True or False ?
1
A B-tree reduces the query execution time - Correct or Incorrect about B-Tree Index ?
Correct
If you query the USER_CONSTRAINTS data dictionary view for checking the constraints on T1 table, what will be the value of the CONSTRAINT_TYPE column for the NOT NULL constraint T1_NN?
C
Which keywords is used in the syntax of the system privilege to indicate that a user can perform the privilege on any objects owned by any other user except for SYS?
ANY
Which statements should be issued to make a visible index invisible?
ALTER INDEX index INVISIBLE;
Which of the following conversion functions is used to convert raw to a character value containing its hexadecimal equivalent?
RAWTOHEX
Which system privilege may not be granted to a role?
ALTER / EXECUTE / REFERENCES
Which of the following statements can be said of the SELECT statement’s WHERE clause?
It specifies which rows are to be returned from the table, It is optional
The ORDER BY clause can be used in the subquery -True or False regarding Subqueries ?
1
Which is used to view all the columns of an existing table?
The USER_TAB_COLUMNS system view
The WITH query_name clause allows a user to assign a name to a subquery block - Correct or Incorrect ?
Correct
Advantages of using Flashback Data Archive for historical data tracking ---
Application Transparent / Seamless Access / Security / Minimal performance overhead / Storage Optimized / Centralized Management
Which statement would you use to define a default value for an existing column?
ALTER TABLE. You would also use the ALTER TABLE statement to add, modify, or drop columns.
Which statements is used to access and manipulate data in existing tables?
Data manipulation language
The difference between dropping a column from a table with DROP and setting a column to be UNUSED is:
The UNUSED column and its data are retained within the table’s storage allocation and counts against the total limit on the number of columns the table is allowed to have.
How do you insure that values entered in the salary column of the employee table must always be greater than $30,000?
Use a check constraint indicating salary > 30000
SYSTIMESTAMP()
SYSTIMESTAMP()

SYSTIMESTAMP returns the system date, including fractional seconds and time zone,
of the system on which the database resides. The return type is TIMESTAMP WITH TIME ZONE.
SIGN()
SIGN()

SIGN returns the sign of n. This function takes as an argument any numeric data type,or any nonnumeric data type that can be implicitly converted to NUMBER, and returns
NUMBER.

For value of NUMBER type, the sign is:
■ -1 if n<0
■ 0 if n=0
■ 1 if n>0

For binary floating-point numbers (BINARY_FLOAT and BINARY_DOUBLE), this
function returns the sign bit of the number. The sign bit is:
■ -1 if n<0
■ +1 if n>=0 or n=NaN

The following example indicates that the argument of the function (-15) is <0:

SELECT SIGN(-15) "Sign" FROM DUAL;

Sign
----------
-1
You are the DBA for a new shopping portal still in its beta stage and which does not yet include any client-side validation. While ordering products, some users in San Francisco have entered their city codes incorrectly as 'SanFr'. The correct value for city code for those living in San Francisco is 'SF'. You need to find the rows with the incorrect city code of 'SanFr' and then change them to the correct city code of 'SF'.

Which of the following statements should you use to achieve the desired results?

UPDATE users_info
SET city_code='SF'
WHERE NOT REGEXP_LIKE(city_code, 'SF');

UPDATE users_info
SET city_code='SF'
WHERE REGEXP_LIKE(city_code, 'SF');

UPDATE users_info
SET city_code='SF'
WHERE NOT REGEXP_LIKE(city_code, 'SanFr');

UPDATE users_info
SET city_code='SF'
WHERE REGEXP_LIKE(city_code, 'SanFr');
UPDATE users_info
SET city_code='SF'
WHERE REGEXP_LIKE(city_code, 'SanFr');
Which part of the query given below will be executed and processed first?

SELECT * FROM SUPPLIER_GOOD
UNION
SELECT * FROM SUPPLIER_TEST
MINUS
SELECT * FROM SUPPLIER;

A: Both operators are executed together
B: UNION operator
C: MINUS operator
D: Last compound query is executed first
B: UNION operator
When the MIN group function is used with a DATE column, which date is displayed?
the earliest date
You maintain the database for a resort hotel. The details of the hotel rooms need to be maintained in a database.
For this, you execute the following statements:

CREATE TABLE roomdetails
(
roomnum NUMBER(5),
occupancy NUMBER(5),
roomtype VARCHAR(30)
);

INSERT INTO roomdetails VALUES (344, 1, 'Single');

INSERT INTO roomdetails VALUES (196, NULL,'Single');

ALTER TABLE roomdetails ADD CONSTRAINT un_rnum UNIQUE(roomnum) DISABLE NOVALIDATE;

ALTER TABLE roomdetails MODIFY roomtype NOT NULL ENABLE;

INSERT INTO roomdetails VALUES (231, NULL, 'Single');

INSERT INTO roomdetails VALUES (196,2 , 'Double');

INSERT INTO roomdetails VALUES (11, 2, NULL);

Based on the given statements, how many rows are inserted in the roomdetails table after the un_rnum and NOT NULL constraints are added?

0
1
2
3
2
A CHECK constraint cannot be defined on a view - Correct or Incorrect ?
Correct
You work as a Database Administrator for Dolliver Inc. The company uses Oracle as its database.

You have issued the CREATE TABLE command to create a table named Employee.

You use the following code-line to accomplish the task:

CREATE TABLE @EMP
(EMP_ID NUMBER, EMP_NAME VARCHAR2 (12), EMP_ADD VARCHAR (30),
EMP_DEP NUMBER, SALARY NUMBER);

The desired table is not created and an error message is returned.

Which of the following is the cause of the error message?

A: The table name starts with a special character.

B: The command used to create the table is inappropriate.

C: The data types used for every columns are inappropriate.

D: The statement ends with a semi-colon (;).
A: The table name starts with a special character
The WITH clause improves the performance of the queries - Correct or Incorrect about the WITH clause ?
Correct
Evaluate the following command:

CREATE TABLE employees
(employee_id NUMBER(2) PRIMARY KEY,
last_name VARCHAR2(25) NOT NULL,
department_idNUMBER(2), job_id VARCHAR2(8),
salaryNUMBER(10,2));

You issue the following command to create a view that displays the IDs and last names of the
sales staff in the organization:

CREATE OR REPLACE VIEW sales_staff_vu AS
SELECT employee_id, last_name job_id
FROM employees
WHERE job_id LIKE 'SA_%' WITH CHECK OPTION;

Which statements are true regarding the above view? (Choose all that apply.)

A. It allows you to insert details of all new staff into the EMPLOYEES table.

B. It allows you to delete the details of the existing sales staff from the EMPLOYEES table.

C. It allows you to update the job ids of the existing sales staff to any other job id in the
EMPLOYEES table.

D. It allows you to insert the IDs, last names and job ids of the sales staff from the view if it is used
in multitable INSERT statements
B,D
Which may not be used within a parameter of the REGEXP_LIKE function?
‘%’ as a wildcard operator
It can only be used with the ROLLUP and CUBE operators specified in the GROUP BY clause - Correct or Incorrect about GROUPING function ?
Correct
When the MAX group function is used with a NUMBER column, which number is displayed?
the largest number
Examine the structures of the po_header and po_detail tables:

po_header
--------------------
po_num NUMBER NOT NULL
po_date DATE DEFAULT SYSDATE
po_total NUMBER(9,2)
supplier_id NUMBER(9)
po_terms VARCHAR2(25)

po_detail
------------------
po_num NUMBER NOT NULL
po_line_id NUMBER NOT NULL
product_id NUMBER NOT NULL
quantity NUMBER(3) NOT NULL,
unit_price NUMBER (5,2) DEFAULT 0

The primary key of the po_header table is po_num. The primary key of the po_detail table is the combination of po_num and po_line_id. A FOREIGN KEY constraint is defined on the po_num column of the po_detail table that references the po_header table.

You want to update the purchase order total amount for a given purchase order. The po_total column in the po_header table should equal the sum of the extended amounts of the corresponding po_detail records. You want the user to be prompted for the purchase order number when the query is executed. When a purchase order is updated, the po_date column should be reset to the current date.

Which UPDATE statement should you execute?

UPDATE po_header
SET po_total = (SELECT SUM(ext)
FROM (SELECT po_num, quantity * unit_price ext
FROM po_detail
WHERE po_num = &&ponum)),
SET po_date = sysdate
WHERE po_num = &&ponum;

UPDATE po_header
SET po_total = (SELECT SUM(quantity * unit_price) FROM (SELECT po_num)
FROM po_detail
WHERE po_num = &&ponum)),
po_date = DEFAULT
WHERE po_num = &&ponum;

UPDATE po_header
SET po_total = (SELECT SUM(ext)
FROM (SELECT po_num, quantity * unit_price ext
FROM po_detail WHERE po_num = &&ponum)),

UPDATE po_header
SET po_date = sysdate
WHERE po_num = &&ponum;

UPDATE po_header
SET po_total = (SELECT SUM(ext)
FROM (SELECT po_num, quantity * unit_price ext
FROM po_detail WHERE po_num = &&ponum)),
po_date = DEFAULT
WHERE po_num = &&ponum;

UPDATE po_header
SET po_total = (SELECT po_num, SUM(ext)
FROM (SELECT po_num, quantity * unit_price ext
FROM po_detail WHERE po_num = &&ponum)),
po_date = DEFAULT
WHERE po_num = &&ponum;

UPDATE po_header
SET po_total = (SELECT SUM(ext)
FROM (SELECT po_num, quantity * unit_price ext
FROM po_detail WHERE po_num = &&ponum)),
po_date = NULL WHERE po_num = &&ponum;
UPDATE po_header
SET po_total = (SELECT SUM(ext)
FROM (SELECT po_num, quantity * unit_price ext
FROM po_detail WHERE po_num = &&ponum)),
po_date = DEFAULT WHERE po_num = &&ponum;
Which clauses actually links the rows of a table in a hierarchical tree structure?
CONNECT BY PRIOR <condition>
Which option can you include with the CREATE VIEW statement to prevent DML through the view?
The WITH READ ONLY option of the CREATE VIEW statement
What is the purpose of the START WITH <column name> IS NULL clause in a hierarchical query?
Hierarchical queries that should begin at the very top of the hierarchy will often have a NULL value for <column name> since that column often will be the name of the parent, and the top row of the query does not have a parent.
Multiple-row subqueries always contain a subquery within a subquery - True or False ?
1
Which comparison operator be used with multiple-row subqueries?
ALL, ANY, IN, NOT IN, >=ALL
A date value may be converted to a character string using the TO_CHAR function - True or False ?
1
TIMESTAMP WITH TIME ZONE -- stores value of ---
2008-06-15 8:00:00 -8:00'
Oracle has system privileges that allow local access rights - Correct or Incorrect about Privileges ?
Incorrect
Sequence numbers are generated independently of tables - Correct or Incorrect about Sequences ?
Correct
It contains the current system privileges available in the user session- Correct or Incorrect about the SESSION_PRIVS dictionary view ?
Correct
Which is used to fetch rows from a SELECT statement.
Cursor
How many levels can subqueries be nested in a FROM clause?
unlimited
It is not possible to delete rows with the help of a view if the DISTINCT clause is present in the view definition - Correct or Incorrect about a VIEW ?
Correct
Constraint - Is it a Database Object ? - Yes or No
Yes
The datatypes of the columns being compared must match - Correct or Incorrect about Multiple Column subquery ?
Correct
Which of the following clauses is used in a query block to represent a set of data that is being repeatedly used in a complex query?
WITH
Which statement would you use to add a PRIMARY KEY constraint to a table?
ALTER TABLE ADD CONSTRAINT
Which commands can be rolled back?
DELETE / INSERT / MERGE / UPDATE
Which operator combines two character strings yielding one combined character string?
|| (the concatenation operator)
Which of the following retention periods is valid if the command at SQL prompt is as follows: ALTER db_flashback_retention_target = 4320;
3 days
What is the benefit to setting a column that you no longer want in your table to UNUSED, rather than just dropping the column?
Improved performance
SUM()
SUM()

SUM returns the sum of values of expr. You can use it as an aggregate or analytic function.
This function takes as an argument any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type. The function returns the same data type as the numeric data type of the argument.
Which system privilege allows a user to connect to the database?
CREATE SESSION
SELECT product_name, list_price, min_price, list_price - min_price Difference FROM
product_information

Which options when used with the above SQL statement can produce the sorted output in
ascending order of the price difference between LIST_PRICE and MIN_PRICE? (Choose all that
apply.)

A. ORDER BY 4
B. ORDER BY MIN_PRICE
C. ORDER BY DIFFERENCE
D. ORDER BY LIST_PRICE
E. ORDER BY LIST_PRICE - MIN_PRICE
A,C,E
What will be the output of the following query?

SELECT ABS(FLOOR(-268651.894)) FROM DUAL;

A: 268651
B: -268652
C: 268652
D: Oracle Error
C: 268652
You work as a Database Administrator for Dolliver Inc. The company uses Oracle as its database.

You use the CREATE TABLE command to create a table named Employee.

The syntax to create the table is given below:

1.CREATE TABLE Employee;
2.(Emp_ID NUMBER(8)
3.CONSTRAINT Emp_pk PRIMARY KEY,
4. 2007_Emp_Amt NUMBER(8,2),
5. Emp_Type VARCHAR2(24)
6. CONSTRAINT Emp_Cons NOT NULL,
7. Emp_Amt_07 NUMBER(8,4));

The syntax fails to create the table. Which of the following lines are incorrect?

Each correct answer represents a complete solution. Choose all that apply.

A: Line 7
B: Line 4
C: Line 1
D: Line 3
E: Line 2
F: Line 6
G: Line 5
B: Line 4
C: Line 1
You are tasked to identify the position of a given pattern within a larger string. Which functions will you be sure to use?
REGEXP_INSTR
NUMTODSINTERVAL()
NUMTODSINTERVAL()

NUMTODSINTERVAL converts n to an INTERVAL DAY TO SECOND literal. The argument n can be any NUMBER value or an expression that can be implicitly converted to a NUMBER value. The argument interval_unit can be of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type. The value for interval_unit specifies the unit of n and must resolve to one of the following string values:
■ 'DAY'
■ 'HOUR'
■ 'MINUTE'
■ 'SECOND'

interval_unit is case insensitive. Leading and trailing values within the parentheses are ignored. By default, the precision of the return is 9.
A scalar subquery may not be used in which of the following clauses and/or SQL statements?
The GROUP BY clause of a SELECT statement
Evaluate the following SQL statement:

SELECT product_name || 'it's not present in the stock'
FROM product_information
WHERE product_status = 'obsolete';

On executing the above query, you received the following error message:

ERROR
ORA-01756: quoted string not properly terminated

What should you do to execute the query successfully?

A: Enclose the character literal string in the SELECT clause within the double quotation
marks.

B: Use Quotes (q) operator and delimiter to allow the use of the single quotation mark in the literal character string.

C: Use escape character to negate the single quotation mark inside the literal character
string in the SELECT clause.

D: Do not enclose the character literal strings in the SELECT clause within the single quotation marks.
B: Use Quotes (q) operator and delimiter to allow the use of the single quotation mark in the literal character string.
Which characters anchors the expression to the start of a line?
^
When a GROUP BY clause and no ORDER BY clause is used, in what order does the Oracle server implicitly sort the results?
in ascending order by the first grouping column
You work as a Database Administrator for Gadgets Inc. The company uses Oracle as its
database. You created a sequence by using the following syntax:

SELECT sequence_name, min_value, max_value, increment_by, last_number
FROM user_sequences;

Which of the following statements represents the significance of the last_number column?

A: The last_number column displays the next available sequence number if NOMINVALUE is specified.

B: The last_number column displays the next available sequence number if NOCYCLE is
specified.

C: The last_number column displays the next available sequence number if NOCACHE
is specified.

D: The last_number column displays the next available sequence number if NOMAXVALUE is specified.
C: The last_number column displays the next available sequence number if NOCACHE is specified.
What is the function of the NOCYCLE keyword in a hierarchical query?
It prevents the query from aborting at run time due to an infinite loop
Which should a user do to perform an unconditional multitable insert?
Specify ALL followed by multiple insert_into_clauses.
Which statement removes a sequence from the data dictionary?
DROP SEQUENCE sequence_name;
Which of the following queries will provide details of all the constraints and column names on which they are created for the EMP table owned by SCOTT user?

A: SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, COLUMN_NAME FROM DBA_CONSTRAINTS
WHERE OWNER='SCOTT' AND TABLE_NAME='EMP';

B: SELECT OBJECT_NAME, OBJECT_TYPE, TABLE_NAME, COLUMN_NAME FROM DBA_OBJECTS WHERE OWNER='SCOTT'
AND TABLE_NAME='EMP' AND OBJECT_TYPE='CONSTRAINT';

C: SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, COLUMN_NAME FROM DBA_CONS_COLUMNS
WHERE OWNER='SCOTT' AND TABLE_NAME='EMP';

D: SELECT A.CONSTRAINT_NAME, A.CONSTRAINT_TYPE, A.TABLE_NAME,
B.COLUMN_NAME FROM DBA_CONSTRAINTS A, DBA_CONS_COLUMNS B WHERE A.OWNER='SCOTT' AND A.OWNER=B.OWNER
AND .CONSTRAINT_NAME=B.CONSTRAINT_NAME AND A.TABLE_NAME='EMP';
D: SELECT A.CONSTRAINT_NAME, A.CONSTRAINT_TYPE, A.TABLE_NAME,
B.COLUMN_NAME FROM DBA_CONSTRAINTS A, DBA_CONS_COLUMNS B WHERE A.OWNER='SCOTT'
AND A.OWNER=B.OWNER AND A.CONSTRAINT_NAME=B.CONSTRAINT_NAME
AND A.TABLE_NAME='EMP';
Which is a function that is used to convert a timestamp value and a time zone to a TIMESTAMP WITH TIME ZONE value?
FROM_TZ
Outer join conditions CANNOT use which logical operator?
the IN logical operator
Multiple-row subqueries can be used to retrieve multiple rows from a single table only - True or False ?
1
What are the distinguishing characteristics of a scalar subquery?
A scalar subquery returns one row & One column
The NVL single-row function can be used on VARCHAR2 columns - True or false ?
1
You are tasked to work with a view. The view’s underlying table has been altered. What information can the data dictionary provide at this point?
The status of the view so that you can determine if the view requires recompilation / The current state of the table / The query that was used to create the view. / The names of columns in the underlying table.
Synonyms can be created for tables but not views - Correct or Incorrect about SYNONYMS ?
Incorrect
Roles can be granted to other roles - Correct or Incorrect about ROLES ?
Correct
Where could subqueries be used in a SELECT statement ?
SELECT_LIST, FROM, WHERE, GROUP BY, HAVING
For which database objects can you create a synonym?
Sequences / Another synonym / User defined object types / Views
How many PRIMARY KEY constraints can be defined on a table?
only one
DBTIMEZONE()
DBTIMEZONE returns the value of the database time zone. The return type is a time zone offset (a character type in the format '[+|-]TZH:TZM') or a time zone region name, depending on how the user specified the database time zone value in the most recent CREATE DATABASE or ALTER DATABASE statement.

The following example assumes that the database time zone is set to UTC time zone:

SELECT DBTIMEZONE FROM DUAL;

DBTIME
------
+00:00
With which clauses can a user use the WITH clause?
SELECT
Assuming you have the appropriate object privileges, how should you access a table owned by another user in a FROM clause?
prefix the table name with the owner/schema name, as in ownername.tablename
When testing for NULL values in a SQL statement, which comparison operator should you use?
the IS NULL comparison operator
What would be the most common reason for an error message if you attempted to drop a primary key constraint on a table?
The reason for the error would be that the column that had the primary key constraint on it was also acting as the parent in a parent / child relationship.
Unique constraints can be defined in either a CREATE TABLE statement or an ALTER TABLE statement - True or False ?
1
A constraint is enforced only for the INSERT operation on a table - Correct or Incorrect regarding Constraints ?
Incorrect
Which can be identified by a password?
Users / Roles
Which data dictionary views store information about all the objects in the data dictionary?
DBA_*
Which statement creates a marker in the current transaction to allow you to rollback only a portion of the changes within the transaction?
SAVEPOINT. The syntax is SAVEPOINT name;
Consider the following expression:

INTERVAL '540' DAY(3) - INTERVAL '480' HOUR - INTERVAL '15 12:30' DAY TO MINUTE +
INTERVAL '12:30' HOUR TO MINUTE

Which of the following represent the values of the given expression? (Choose two.)

INTERVAL '12120' HOUR(5)

INTERVAL '505' DAY(3)

INTERVAL '576-1' DAY TO HOUR

INTERVAL '504 23:00' DAY TO MINUTE
INTERVAL '12120' HOUR(5)

INTERVAL '505' DAY(3)
Which of the following options should you use to insert data into the Name column only
of the T1 table?

A: INSERT INTO T1(NAME) VALUES('ADAM KINGER');

B: INSERT INTO T1 VALUES(NULL);

C: INSERT INTO T1 VALUES(NULL,'ADAM KINGER');

D: INSERT INTO T1 VALUES('ADAM KINGER',NULL);
A: INSERT INTO T1(NAME) VALUES('ADAM KINGER');

C: INSERT INTO T1 VALUES(NULL,'ADAM KINGER');
RTRIM()
RTRIM()

RTRIM removes from the right end of char all of the characters that appear in set.

This function is useful for formatting the output of a query.

If you do not specify set, then it defaults to a single blank. If char is a character literal, then you must enclose it in single quotation marks. RTRIM works similarly to LTRIM.

Both char and set can be any of the data types CHAR, VARCHAR2, NCHAR,NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 data type if char is a character data type, NVARCHAR2 if expr1 is a national character data type, and a LOB if char is a LOB data type

The following example trims all the right-most occurrences of less than sign (<),greater than sign (>) , and equal sign (=) from a string:

SELECT RTRIM('<=====>BROWNING<=====>', '<>=') "RTRIM Example" FROM DUAL;

RTRIM Example
---------------
<=====>BROWNING
If the subquery returns no rows, then the value is considered to be NULL - Correct or Incorrect about Scaler subqueries ?
Correct
You work as a Database Administrator for Amtech Inc. The company uses Oracle as its
database. The database contains a table named Employee. You want to retrieve all records except the record of an employee whose emp_id is equal to 6 in the Employee table.

Which of the following SQL queries will you use to accomplish the task?

Each correct answer represents a complete solution. Choose two

A: SELECT * FROM employee
WHERE emp_id IS 6;

B: SELECT * FROM employee
WHERE emp_id <>6;

C: SELECT *FROM employee
WHERE emp_id !=6;

D: SELECT * FROM employee
WHERE emp_id NOT EQUAL 6;
B: SELECT * FROM employee
WHERE emp_id <>6;

C: SELECT *FROM employee
WHERE emp_id !=6;
Which SQL commands can be used to define a CHECK constraint?
ALTER TABLE
Which clause of a SELECT statement does Oracle server evaluate last?
the ORDER BY clause
You work as a Database Developer for Gadgets Inc. The company uses Oracle as its
database. The database contains a table named Employee.

You issue the following query against the
Employee table:

SELECT Department_id, COUNT(last_name)
FROM Employee;

Which of the following statements is true about the above mentioned query?

A: It will retrieve the department IDs of those employees whose count of the last name
exists.

B: It will retrieve the department IDs of those employees whose last name column exists and has a NULL value.

C: It will retrieve the department IDs of all employees.

D: It will return an error message.
D: It will return an error message.
You cannot specify a table collection expression when performing a multitable insert - Correct or Incorrect ?
Correct
What should you do to eliminate the need for all database users to qualify an object name with its schema name?
Create a public synonym using CREATE PUBLIC SYNONYM syn_name FOR obj_name;
The employee table contains these columns:

emp_id NUMBER(9)
fname VARCHAR2(25)
lname VARCHAR(30)
salary NUMBER(7,2)
bonus NUMBER(5,2)
dept_id NUMBER(9)

You need to calculate the average bonus for all the employees in each department. The average should be calculated based on all the rows in the table even if some employees do not receive a bonus.

Which group functions should you use to calculate this value? (Choose two.)

AVG
SUM
MEAN
NVL
COUNT
AVERAGE
AVG
NVL
When using the LIKE operator, which option must you include to use the percent (%) and underscore (_) characters as literal values?
The ESCAPE option identifies a special escape character that when preceding these characters causes them to be interpreted literally
Which functions is used to format hierarchical data?
LPAD
You work as a Database Administrator for uCertify Inc. The company uses Oracle as its database. The database has a table named Employee that holds information of all the employees of the company. You have moved the table to some other location for maintenance purpose. A user has fired a SELECT query against the table. What will happen in such a scenario?

A: The SELECT query fired against the table will drop the table.

B: An error message will be generated.

C: The query will succeed with reduced performance.

D: The consequence depends on the SKIP_UNUSABLE_INDEXES parameter.
D: The consequence depends on the SKIP_UNUSABLE_INDEXES parameter
It displays the grand total last - Correct or Incorrect about CUBE operator ?
Incorrect
It produces higher-level subtotals, moving from left to right through the list of grouping columns specified in the GROUP BY clause - Correct or Incorrect reharding ROLLUP operator ?
Incorrect
Multiple-Row subqueries use the <ALL> operator to imply less than the maximum - True or False ?
1
A date value may be converted to a character value using the TO_DATE function - True or False ?
1
Single-row Functions can operate on multiple rows of a dataset at a time - True or False ?
1
A user can be granted only one role at any point of time - Correct or Incorrect about ROLES ?
Incorrect
What is the name for a SELECT statement that is embedded in another SELECT statement?
a subquery
Which restricts a user from removing a row from a view?
A GROUP BY clause / Group functions, such as SUM, MIN, MAX, AVG. / The DISTINCT keyword
TO_BLOB()
TO_BLOB()

TO_BLOB converts LONG RAW and RAW values to BLOB values.

From within a PL/SQL package, you can use TO_BLOB to convert RAW and BLOB
values to BLOB.
When the view already exists, using the OR REPLACE option requires the regranting of the object privileges previously granted on the view - Correct or Incorrect concerning the creation of a view ?
Incorrect
Which statement removes all rows from a table while releasing all or most of the storage space used by the table?
the TRUNCATE TABLE statement
Parentheses can be used in an equation to do which of the following?
Override the rules of operator precedence.
The DESCRIBE, or DESC, command, can be used to do which of the following?
Show a table’s columns and the datatypes of those columns
Which files are backed up in the flash recovery area when the BACKUP RECOVERY AREA and BACKUP RECOVERY FILES commands are in use?
Data files
DESCRIBE is which type of command ?
SQL*Plus command
A composite unique key cannot have more than 32 columns - Correct or Incorrect about UNIQUE constraints ?
Correct
If a scalar sub-query evaluates to 0 row, i.e. the sub-query result set is no rows selected, then what value will the sub-query return to the outer query?
NULL
ALTER TABLE products DROP COLUMN discount ADD COLUMN (servicetax NUMBER(10); - Correct or Incorrect ?
Incorrect
You work as a Database Administrator for Dolliver Inc. The company uses Oracle as its database. You want to view all the object privileges granted to a database user on some specific columns of a table.

Which of the following data dictionary views will you use to accomplish the task?

Each correct answer represents a complete solution. Choose two.

A: ALL_COL_PRIVS_RECD
B: DBA_IND_COLUMNS
C: USER_OBJECTS
D: USER_COL_PRIVS_RECD
E: USER_SOURCE
A: ALL_COL_PRIVS_RECD
D: USER_COL_PRIVS_RECD
UPPER()
UPPER()


UPPER returns char, with all letters uppercase. char can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is the same data type as char. The database sets the case of the characters based on the binary mapping defined for the underlying character set.

The following example returns each employee's last name in uppercase:

SELECT UPPER(last_name) "Uppercase"
FROM employees;
View the Exhibit and examine the description of the EMPLOYEES table.

Evaluate the following SQL statement:

SELECT first_name, employee_id, NEXr_DAY(ADD_MONTHS(hire_date, 6), 1) "Review" FROM
employees;

The query was written to retrieve the FIRST_NAME, EMPLOYEE_ID, and review date for employees.

The review date is the first Monday after the completion of six months of the hiring. The
NLS_TERRITORY parameter is set to AMERICA in the session.

Which statement is true regarding this query?

A. The query would execute to give the desired output.

B. The query would not execute because date functions cannot be nested.

C. The query would execute but the output would give review dates that are Sundays.

D. The query would not execute because the NEXT_DAY function accepts a string as argument
C
You are the DBA in the service of the British Royal Museum. You need to update the database of all citizens who have been knighted in the past. You want to build in a validation on the data to find all these people.

Which of the following metacharacter combinations would you use?

'Sir .'
'Sir +'
'Sir ?1'
'Sir EG*1'
'Sir .'
Which group function calculates a standard deviation for a group of values?
STDDEV
Which SQL statement will you use to display a message in the following format?

Outer1 Inner1 Inner2

Here, Outer1, Inner1, and Inner2 are the first, second, and third elements of the
message, respectively.

A: SELECT CONCAT('Outer1' || 'Inner1' || 'Inner2') FROM DUAL;

B: SELECT CONCAT('Outer1', 'Inner1', 'Inner2') FROM DUAL;

C: SELECT CONCAT("Outer1", CONCAT("Inner1", "Inner2")) FROM DUAL;

D: SELECT CONCAT('Outer1', CONCAT(' Inner1', 'Inner2')) FROM DUAL;
D: SELECT CONCAT('Outer1', CONCAT(' Inner1', 'Inner2')) FROM DUAL;
Which should you use if you want minimal redo information to be generated during the table creation?
NOLOGGING
You work as a Database Administrator for Dolliver Inc. The company's database has a table named EMP that contains the COMM column. This column stores the value of commission received by employees. Some of the values for this column are unknown.

Which of the following queries will return a list of those employees whose commission
values are unknown?

A: SELECT * FROM EMP WHERE COMM=NULL;
B: SELECT * FROM EMP WHERE COMM='';
C: SELECT * FROM EMP WHERE COMM IS NULL;
D: SELECT * FROM EMP WHERE COMM=0;
C: SELECT * FROM EMP WHERE COMM IS NULL;
A user can use the WITH clause only with the SELECT clause - Correct or Incorrect regarding the WITH clause ?
Correct
INTERVAL YEAR TO MONTH -- stores value of ---
'+04-00'
Evaluate the following SQL statements that are issued in the given order:

CREATE TABLE emp
(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,
enameVARCHAR2(15),
salaryNUMBER(8,2),
mgr_noNUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp);

ALTER TABLE emp
DISABLE CONSTRAINT emp_emp_no_pk CASCADE;

ALTER TABLE emp
ENABLE CONSTRAINT emp_emp_no_pk;

What would be the status of the foreign key EMP_MGR_FK?

A. It would be automatically enabled and deferred.

B. It would be automatically enabled and immediate.

C. It would remain disabled and has to be enabled manually using the ALTER TABLE command.

D. It would remain disabled and can be enabled only by dropping the foreign key constraint and
re-creating it.
C
If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY clause - Correct or Incorrect ?
Correct
It returns 0 for those rows that have NULL values as the data in those rows - Correct or Incorrect about GROUPING FUNCTION ?
Correct
Which clause of the SELECT statement constrains columns that use the GROUPING functions?
the HAVING clause
Which type of join joins a table to itself?
a self join
Which SQL statements will display the current time, in hours, minutes, and seconds, as determined by the operating system on which the database server resides?
SELECT TO_CHAR(SYSDATE, ‘HH:MI:SS’) FROM DUAL;
Which function could be used to return a date without the time portion?
The TRUNC date function returns a date with the time portion of the day truncated to the specified format unit. If no 'fmt' (format model) is provided, the date is truncated to the nearest day.
Indexes provide fast access to table data - Correct or Incorrect about Indexes ?
Correct
TO_DATE()
TO_DATE()

TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of DATE data type.

The fmt is a datetime model format specifying the format of char. If you omit fmt, then char must be in the default date format. The default date format is determined implicitly by the NLS_TERRITORY initialization parameter or can be set explicitly by
the NLS_DATE_FORMAT parameter. If fmt is J, for Julian, then char must be an integer.

The 'nlsparam' argument specifies the language of the text string that is being converted to a date. This argument can have this form:

'NLS_DATE_LANGUAGE = language'

Do not use the TO_DATE function with a DATE value for the char argument. The first two digits of the returned DATE value can differ from the original char, depending on fmt or the default date format.

This function does not support CLOB data directly. However, CLOBs can be passed in
as arguments through implicit data conversion

The following example converts a character string into a date:

SELECT TO_DATE(
'January 15, 1989, 11:00 A.M.',
'Month dd, YYYY, HH:MI A.M.',
'NLS_DATE_LANGUAGE = American')
FROM DUAL;

TO_DATE('
---------
15-JAN-89
Which types of indexes is created only for columns having PRIMARY KEY or UNIQUE constraint?
Unique
Which statement do you use to assign a privilege to a user or role?
the GRANT statement
A foreign key cannot contain NULL values - True or False ?
1
When a table is created with a statement such as the following: create table newtab as select * from tab; will there be any constraints on the new table?
Check and not null constraints will be copied but not unique or primary key.
What is the advantage of writing a multitable INSERT command that will insert rows from table_a into either tablex, tabley, or tablez based upon some condition, as opposed to executing three separate INSERT statements to insert rows one table at a time?
Performance. The multitable INSERT needs to make only one pass of the data in table_a to get the job done
A composite foreign key cannot have more than 32 columns - Correct or Incorrect about FOREIGN KEY constraint ?
Correct
Which will return the same result as the SQL statement given ? SELECT TO_CHAR (SYSDATE, 'YYYY') FROM DUAL;
SELECT EXTRACT (YEAR FROM SYSDATE) FROM DUAL;
When a commit occurs, what happens to existing savepoints?
All savepoints are erased when a commit occurs
The purpose of the CREATE DIRECTORY statement is to create a named object in the database:
That points to a directory you choose somewhere within the Oracle server’s file system
For existing rows in a table, UPDATE can remove values from any column by changing its value to NULL.
Correct
SELECT * FROM USER_CATALOG; What might be displayed by this statement?
names of all the views that you own
REGEXP_COUNT()
REGEXP_COUNT()

REGEXP_COUNT complements the functionality of the REGEXP_INSTR function by returning the number of times a pattern occurs in a source string. The function evaluates strings using characters as defined by the input character set. It returns an integer indicating the number of occurrences of pattern. If no match is found, then
the function returns 0.

■ source_char is a character expression that serves as the search value. It is commonly a character column and can be of any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

■ pattern is the regular expression. It is usually a text literal and can be of any of the data types CHAR, VARCHAR2, NCHAR, or NVARCHAR2. It can contain up to 512 bytes. If the data type of pattern is different from the data type of source_
char, then Oracle Database converts pattern to the data type of source_char.

REGEXP_COUNT ignores subexpression parentheses in pattern. For example, the
pattern '(123(45))' is equivalent to '12345'.

■ position is a positive integer indicating the character of source_char where Oracle should begin the search. The default is 1, meaning that Oracle begins the search at the first character of source_char. After finding the first occurrence of
pattern, the database searches for a second occurrence beginning with the first character following the first occurrence.

■ match_param is a text literal that lets you change the default matching behavior of the function. You can specify one or more of the following values for match_param:

- 'i' specifies case-insensitive matching.
- 'c' specifies case-sensitive matching.
- 'n' allows the period (.), which is the match-any-character character, to match the newline character. If you omit this parameter, then the period does not match the newline character.
- 'm' treats the source string as multiple lines. Oracle interprets the caret (^) and dollar sign ($) as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string.

If you omit this parameter, then Oracle treats the source string as a single line.
- 'x' ignores whitespace characters. By default, whitespace characters match themselves.

If you specify multiple contradictory values, then Oracle uses the last value.

For example, if you specify 'ic', then Oracle uses case-sensitive matching. If you specify a character other than those shown above, then Oracle returns an error.

If you omit match_param, then:
- The default case sensitivity is determined by the value of the NLS_SORT parameter.
- A period (.) does not match the newline character.
- The source string is treated as a single line.

The following example shows that subexpressions parentheses in pattern are ignored:

SELECT REGEXP_COUNT('123123123123123', '(12)3', 1, 'i') REGEXP_COUNT FROM DUAL;

REGEXP_COUNT
------------
5

In the following example, the function begins to evaluate the source string at the third
character, so skips over the first occurrence of pattern:

SELECT REGEXP_COUNT('123123123123', '123', 3, 'i') COUNT FROM DUAL;

COUNT
----------
3
EMP is a private synonym for the OE.EMPLOYEES table.

The user OE issues the following command:

DROP SYNONYM EMP;

Which statement is true regarding the above SQL statement?

A: The synonym would be dropped and the packages referring to the synonym would be dropped.

B: It is not possible to drop the private synonym.

C: Only the synonym would be dropped.

D: The synonym would be dropped and the corresponding table would become invalid
C: Only the synonym would be dropped
You work as a Database Designer for Dolliver Inc. The company uses Oracle 11g as its
database. The database contains a table named Employees. You want to extract those rows where Emp_dept_id is equal to 100. Another objective is to produce a single string literal output from the CONCAT function of the format Emp_f_name Emp_l_name
earns Emp_salary.

Which of the following SQL statements can you use to accomplish the task?

Each correct answer represents a complete solution. Choose two

A: SELECT CONCAT (Emp_f_name,CONCAT(' ', CONCAT(Emp_l_name, CONCAT(' earns
',Emp_salary)))) FROM EMPLOYEES
WHERE Emp_dept_id = 100;

B: SELECT CONCAT ('Emp_f_name',CONCAT(' ', CONCAT('Emp_l_name', CONCAT('
earns ','Emp_salary')))) FROM EMPLOYEES
WHERE Emp_dept_id = 100;

C: SELECT Emp_f_name|| " "||Emp_l_name|| " earns "||Emp_salary FROM EMPLOYEES
WHERE Emp_dept_id = 100;

D: SELECT Emp_f_name|| ' '||Emp_l_name|| ' earns '||Emp_salary FROM EMPLOYEES
WHERE Emp_dept_id = 100;
A: SELECT CONCAT (Emp_f_name,CONCAT(' ', CONCAT(Emp_l_name, CONCAT(' earns
',Emp_salary)))) FROM EMPLOYEES
WHERE Emp_dept_id = 100;

D: SELECT Emp_f_name|| ' '||Emp_l_name|| ' earns '||Emp_salary FROM EMPLOYEES
WHERE Emp_dept_id = 100;
You are asked to update the word "Ave" to "Avenue", "St" to "Street" and "Dr" to "Drive"

Assuming that you have to begin with "Ave", which of the following options should you use to achieve the desired result?

A: UPDATE EMP_ADDRESS SET ADDRESS1 = REGEXP_REPLACE (ADDRESS1,'Ave','Avenue')
WHERE REGEXP_LIKE(ADDRESS1,'Ave*');

B: UPDATE EMP_ADDRESS SET ADDRESS1 = REGEXP_REPLACE ADDRESS1,'Ave*','Avenue')
WHERE REGEXP_LIKE(ADDRESS1,'Ave*');

C: UPDATE EMP_ADDRESS SET ADDRESS1 = REGEXP_REPLACE ADDRESS1,'Ave$','Avenue')
WHERE REGEXP_LIKE(ADDRESS1,'Ave$');

D: UPDATE EMP_ADDRESS SET ADDRESS1 = REGEXP_REPLACE ADDRESS1,'Ave*','Avenue')
WHERE REGEXP_LIKE(ADDRESS1,'Ave$');
C: UPDATE EMP_ADDRESS SET ADDRESS1 = REGEXP_REPLACE (ADDRESS1,'Ave$','Avenue')
WHERE REGEXP_LIKE(ADDRESS1,'Ave$');
Which is a DML statement that is used to update or insert rows conditionally into a table?
MERGE
Evaluate the CREATE TABLE statement:

CREATE TABLE products
(product_id NUMBER(6) CONSTRAINT prod_id_pk PRIMARY KEY,
product_name VARCHAR2(15));

Which statement is true regarding the PROD_ID_PK constraint?

A. It would be created only if a unique index is manually created first.

B. It would be created and would use an automatically created unique index.

C. It would be created and would use an automatically created nonunique index.

D. It would be created and remains in a disabled state because no index is specified in the
command
B
Which value will the NOT logical condition return when the condition following it is false?
TRUEE
The product table contains these columns:

product_id NUMBER PK
name VARCHAR2(30)
list_price NUMBER(7,2)
cost NUMBER(7,2)

You logged on to the database using SQL*Plus to update the product table. After your session began, you issued these statements:

INSERT INTO product VALUES(4,'Ceiling Fan',59.99,32.45);

INSERT INTO product VALUES(5,'Ceiling Fan',69.99,37.20);

SAVEPOINT A;

UPDATE product SET cost = 0;

SAVEPOINT B;

DELETE FROM product WHERE INITCAP(name) = 'CEILING FAN';

ALTER TABLE product ADD qoh NUMBER DEFAULT 10;

ROLLBACK TO B;

UPDATE product SET name = 'CEILING FAN KIT' WHERE product_id = 4;

Then, you exit the SQL*Plus session.

Assuming that the default behavior of SQL*Plus has not been modified, which of the statements you issued were committed?

only the INSERT statements

only the INSERT statements and the first UPDATE statement

the INSERT statements, the first UPDATE statement, and the DELETE statement

all of the DML operations

none of the DML operations
all of the DML operations
Group functions can operate on multiple rows at a time - Correct or Incorrect ?
Correct
The purpose of the GROUPING function is to:
Differentiate between regular rows and superaggregate rows.
Group Functions can be used on columns or expressions - Correct or Incorrect ?
Correct
It produces 2n possible superaggregates combinations if the n columns and expressions are specified in the GROUP BY clause - Correct or Incorrect regarding CUBE operator ?
Correct
Evaluate the following statement:

INSERT ALL
WHEN order_total < 10000 THEN INTO small_orders
WHEN order_total > 10000 AND order_total < 20000 THEN INTO medium_orders
WHEN order_total > 2000000 THEN INTO large_orders
SELECT order_id, order_total, customer_id FROM orders;

Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?

A. They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.

B. They are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.

C. They are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.

D. The INSERT statement would give an error because the ELSE clause is not present for support
in case none of the WHEN clauses are true.
A
In which situations does a Cartesian product occur?
When data is retrieved from two or more tables and there is no common relation specified in the WHERE clause.
Which columns of the USER_OBJECTS data dictionary view provides you with the last timestamp when TRUNCATE is executed on a table?
LAST_DDL_TIME
The difference between an INNER and an OUTER join is:
The INNER join displays rows that match in all joined tables; the OUTER join shows data that doesn’t necessarily match
Which function returns the number of characters in a column or character string?
LENGTH. The syntax of the LENGTH function is LENGTH(column|expression)
What value is returned after executing the following statement? SELECT NVL2(NULLIF('CODA','SID'),'SPANIEL','TERRIER') FROM DUAL;
SPANIEL
TO_TIMESTAMP()
TO_TIMESTAMP()

TO_TIMESTAMP converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type
to a value of TIMESTAMP data type.

The optional fmt specifies the format of char. If you omit fmt, then char must be in the default format of the TIMESTAMP data type, which is determined by the NLS_TIMESTAMP_FORMAT initialization parameter. The optional 'nlsparam' argument has the same purpose in this function as in the TO_CHAR function for date conversion.

This function does not support CLOB data directly. However, CLOBs can be passed in
as arguments through implicit data conversion.
You created a view that contains groups of data, does NOT allow DML operations, and does NOT contain a subquery. Which type of view did you create?
complex
A user can be granted only one role at any point of time - Correct or Incorrect about ROLES ?
Incorrect
Which privileges allows a user to perform system level activities?
System privilege
If you run DESCRIBE EMP on the SQL prompt, what will the Foreign Key column show up in the NULL? column?
Nothing
In which of the following ways can SQL SELECT retrieve data?
Joining, Projection, Selection
Tasks Performed By - TRIM --
Used to remove heading or trailing or both characters from the character string.
The V$FLASHBACK_DATABASE_LOG view is used to display information about the flashback data. Which of the following columns of this view is used to specify the lowest system change number in the flashback data?
OLDEST_FLASHBACK_SCN
SQL can set permissions on tables, procedures, and views - Correct or Incorrect about SQL ?
Correct
Information about the unused column does not appear in the output of the describe table command - Correct or Incorrect about UNUSED column ?
Correct
Evaluate this statement: DELETE FROM workorder; What does this statement accomplish?
deletes all the rows from the WORKORDER table
Which actions will cause the contents of the data dictionary to be changed in some way?
Create a new table. / Modify the datatype of an existing column. / Execute a valid COMMENT statement
Which set operator returns only the results of the first query that are not in the second query?
MINUS
You want to insert a row and then update it. What sequence of steps should you follow?
INSERT, UPDATE, COMMIT
Which of the following SQL statements will return an ORA error?

Each correct answer represents a complete solution. Choose two.

A: SELECT MOD('13.3',2) FROM DUAL;
B: SELECT MOD('13.3.3',2) FROM DUAL;
C: SELECT MOD('$13.3',2) FROM DUAL;
D: SELECT MOD('13',2) FROM DUAL;
B: SELECT MOD('13.3.3',2) FROM DUAL;

C: SELECT MOD('$13.3',2) FROM DUAL;
The details of the order ID, order date, order total, and customer ID are obtained from the
ORDERS table. If the order value is more than 30000, the details have to be added to the
LARGEjDRDERS table. The order ID, order date, and order total should be added to the
ORDERJHISTORY table, and order ID and customer ID should be added to the CUSTJHISTORY table.

Which multitable INSERT statement would you use?

A. Pivoting INSERT
B. Unconditional INSERT
C. Conditional ALL INSERT
D. Conditional FIRST INSERT
C
Which SQL statement would retrieve from the table the number of products having LIST_PRICE as NULL?

A. SELECT COUNT(list_price)
FROM product_information
WHERE list_price IS NULL;

B. SELECT COUNT(list_price)
FROM product_information
WHERE list_price = NULL;

C. SELECT COUNT(NVL(list_price, 0))
FROM product_information
WHERE list_price IS NULL;

D. SELECT COUNT(DISTINCT list_price) FROM product_information
WHERE list_price IS NULL;
C
You query the database with this SQL statement:

SELECT CONCAT(LOWER(SUBSTR(description, 1, 3)), subject_id) "Subject Description" FROM subject;

In which order are the functions evaluated?

CONCAT, LOWER, SUBSTR
SUBSTR, LOWER, CONCAT
LOWER, SUBSTR, CONCAT
All three will be evaluated simultaneously
SUBSTR, LOWER, CONCAT
It is possible for the WITH clause to hold more than one query - Correct or Incorrect regarding the WITH clause ?
Correct
As the DBA for your organization, you are required to populate the junior_employees and
senior_employees tables with the data from the employees table. The junior_employees table should contain data about the employees who have been working at your company for less than five years. The senior_employees table should contain data about employees who have been working at your company for five or more years. You need to ensure that managers and team leaders are not included in either of these two
tables.

Which of the following INSERT statements should you use to achieve the desired results?

INSERT
WHEN years<5
INTO junior_employees
WHEN years>=5
INTO senior_employees
SELECT * FROM employees
WHERE designation!='Manager' AND designation!='Team Leader';

INSERT
INTO junior_employees
INTO senior_employees
SELECT * FROM employees
WHERE designation!='Manager' AND designation!='Team Leader';

INSERT INTO junior_employees, senior_employees
SELECT * FROM employees
WHERE designation!='Manager' AND designation!='Team Leader';

INSERT
WHEN years<5 AND designation!='Manager' AND designation!='Team Leader'
INTO junior_employees
WHEN years>=5 AND designation!='Manager' AND designation!='Team Leader'
INTO senior_employees;
INSERT
WHEN years<5
INTO junior_employees
WHEN years>=5
INTO senior_employees
SELECT * FROM employees
WHERE designation!='Manager' AND designation!='Team Leader';
SQL GROUP BY is used to divide a database table into groups based on group columns - Correct or Incorrect ?
Correct
To how many levels can group functions be nested?
two
The child rows of a parent row are determined by a given condition - Correct or Incorrect about Hierarchial Queries ?
Correct
Evaluate this SQL statement:

SELECT supplier_id, AVG(cost)
FROM product
WHERE AVG(list_price) > 60.00
GROUP BY supplier_id
ORDER BY AVG(cost) DESC;

Which clause will cause an error?

SELECT

WHERE

GROUP BY

ORDER BY
WHERE
Can a multitable INSERT insert rows into multiple tables and views in a single pass?
No. A multitable INSERT cannot insert rows into views
Outer join conditions CANNOT be linked to another condition by which operator?
the OR operator
You want to display data from the EMP table where the result set needs to be sorted by
DEPTNO first in ascending order and then by SAL in descending order.

Which of the following queries will produce the required result set?

Each correct answer represents a complete solution. Choose all that apply

A: SELECT * FROM EMP ORDER BY DEPTNO, SAL DESC;

B: SELECT * FROM EMP ORDER BY DEPTNO, SAL;

C: SELECT * FROM EMP ORDER BY DEPTNO DESC, SAL;

D: SELECT * FROM EMP ORDER BY DEPTNO ASC, SAL DESC;
A: SELECT * FROM EMP ORDER BY DEPTNO, SAL DESC;

D: SELECT * FROM EMP ORDER BY DEPTNO ASC, SAL DESC;
Which database objects is a subset of data from one or more tables and stored in the database as a query?
Views
A table alias:
Exists only for the SQL statement that declared it. / Can be used to clear up ambiguity in the query.
Which two constructs can be used to emulate an IF-THEN-ELSE condition within a SELECT statement?
the DECODE function and the CASE expression
Which clause of the SELECT statement is used to exclude entire branches from the output of a hierarchical query?
CONNECT BY
If SYSDATE returns 12-JUL-2009, what is returned by the following statement? SELECT TO_CHAR(TO_DATE(TO_CHAR(SYSDATE,'DD'),'DD'),'YEAR') FROM DUAL;
TWO THOUSAND NINE
create sequence seq1 start with 1; After selecting from it a few times, you want to reinitialize it to reissue the numbers already generated. How can you do this?
You must drop and re-create the sequence
How do you grant UPDATE privileges on specific columns in a table?
Include a column list in the GRANT statement. This syntax is only valid with the UPDATE, REFERENCES, and INSERT privileges.
Which object privileges CANNOT be granted to roles?
REFERENCES
ADD_MONTHS()
ADD_MONTHS returns the date date plus integer months. A month is defined by the
session parameter NLS_CALENDAR.
The date argument can be a datetime value or any value that can be implicitly converted to DATE.
The integer argument can be an integer or any value that can be implicitly converted to an integer.
The return type is always DATE, regardless of the data type of date. If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as date.
When using the FLASHBACK VERSION QUERY to track changes in table data, which of the following columns will give you the transaction number of the transaction that executed and changed data in that table?
versions_xid
SQL can create stored procedures in a database - Correct or Incorrect about SQL ?
Correct
Tasks Performed By - TRUNC --
Used to truncate a column, expression, or value to n decimal places.
Constraints can be created at the same time as the table or after the table is created - Correct or Incorrect about Constraints ?
Correct
Which commands can clear the formatting done for columns of a table?
CLEAR COLUMNS
A CONSTRAINT is assigned to which of the following?
TABLE
Consider this statement: insert into regions (region_id,region_name) values ((select max(region_id)+1 from regions), 'Great Britain'); What will the result be?
The statement will execute without error.
Which data dictionary view could you query to display the names of tables you have access to?
ALL_OBJECTS
Which types of operators - Character, Set, Arithmatic, Comparison - is used for the concatenation (||) operator?
Character
If within a transaction, a single DML statement fails, what is rolled back?
only the work of the offending DML statement
Which access drivers is used to unload data from a table in the database and insert it into an external table?
ORACLE_DATAPUMP
View the Exhibit and examine the structure of the ORDERS and ORDER_ITEMS tables.

In the ORDERS table, ORDER_ID is the PRIMARY KEY and ORDER_DATE has the DEFAULT value as SYSDATE.

Evaluate the following statement:

UPDATE orders
SET order_date=DEFAULT
WHERE order_id IN (SELECT order_id FROM order_items WHERE qty IS NULL);

What would be the outcome of the above statement?

A. The UPDATE statement would not work because the main query and the subquery use
different tables.
B. The UPDATE statement would not work because the DEFAULT value can be used only in
INSERT statements.
C. The UPDATE statement would change all ORDER_DATE values to SYSDATE provided the
current ORDER_DATE is NOT NULL and QTY is NULL
D. The UPDATE statement would change all the ORDER_DATE values to SYSDATE irrespective
of what the current ORDER_DATE value is for all orders where QTY is NULL
D
Evaluate the following statements:

CREATE TABLE digits
(id NUMBER(2),
descriptionVARCHAR2(15));

INSERT INTO digits VALUES (1,'ONE);

UPDATE digits SET description ='TWO'WHERE id=1;

INSERT INTO digits VALUES (2 .'TWO');

COMMIT;

DELETE FROM digits;

SELECT description FROM digits
VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE;

What would be the outcome of the above query?

A. It would not display any values.
B. It would display the value TWO once.
C. It would display the value TWO twice.
D. It would display the values ONE, TWO, and TWO.
C
You work as a Database Administrator for TechSoft Inc. You want to create a simple
database view on which other privileged users should not be able to perform DML operations.

Which of the following options will you choose?

Each correct answer represents a complete solution. Choose all that apply.

A: Create the view with the WITH CHECK OPTION option.

B: Create the view as a Complex View.

C: Grant only SELECT access on the view to users.

D: Create the view with the OR REPLACE option.

E: Create the view with the WITH READ ONLY option
B: Create the view as a Complex View.
C: Grant only SELECT access on the view to users.
E: Create the view with the WITH READ ONLY option
You work as a Database Developer for Dolliver Inc. The company uses Oracle as its database. The database contains tables named Product and Sales. You have created a CHECK constraint on the Product table by using the following syntax:

CREATE TABLE product
(p_id NUMBER(4),
p_name VARCHAR2(20),
CONSTRAINT check_p_id
CHECK(p_id BETWEEN 1 and 100));

In the given scenario, which of the following statements about the CHECK constraint
are true?

Each correct answer represents a complete solution. Choose all that apply.

A: The check_p_id cannot be defined on a view.

B: The check_p_id cannot refer to columns of the Sales table.

C: The check_p_id can be defined at the row level of the Product table.

D: The check_p_id cannot include a subquery.
A: The check_p_id cannot be defined on a view.

B: The check_p_id cannot refer to columns of the Sales table

D: The check_p_id cannot include a subquery.
The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause - Correct or Incorrect ?
Incorrect
Group Functions can be used along with the single-row function in the SELECT clause of a SQL statement - Correct or Incorrect ?
Correct
John works as a DBA for the railways. Data for all trains and passengers is centrally managed in the
railway_records database. This database has train_info and passenger_info tables that contain data about various local trains and passengers for all the stations. The RailwayRecords database is unavailable for a few days after a server crash. Officers at the local stations store data in a Notepad file when the database in unavailable. When the server is functional again, John needs to collate the data from all the Notepad files and
populate the railway_records database.

He executes the following statements:

USE DATABASE railway_records;
CREATE TABLE missing_railway_info
(
train_no NUMBER(10),
origin_point VARCHAR2,
destination_point VARCHAR2
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
ACCESS PARAMETER
(
RECORDS DELIMITED BY NEWLINE
LOGFILE 'train.log'
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
( train_no,
origin_point,
destination_point )
)
LOCATION ('station1.txt', 'station2.txt', 'station3.txt')
PARALLEL 3;

Which of the following are TRUE about the given statements? (Choose all that apply.)

Data can be loaded from the external table into the railway_records database.

Data in the missing_railway_info table can be retrieved in parallel.

Data is loaded from the station1.txt file only.

Data is loaded from the station1.txt and the station2.txt files only.
Data can be loaded from the external table into the railway_records database.

Data in the missing_railway_info table can be retrieved in parallel.
A CHECK constraint can only contain reference of the table in which it is created and not of columns belonging to other tables - Correct or Incorrect ?
Correct
Which type of outer join includes all matched rows and all unmatched rows in the second table listed?
a right outer join
You work as a Database Developer for Gadgets Inc. The company uses Oracle as its database.

You create a view for a table named Employee using the following syntax:

CREATE VIEW emp1
AS SELECT employee_id, last name, salary
FROM employee
WHERE dept_id = 60;

Which of the following statements about a view are true?

Each correct answer represents a complete solution. Choose two

A: It is a virtual table.

B: It provides a brief description of a database.

C: It helps to manage permissions and other administrative tasks on a table.

D: It is used to speed up the data retrieval process.
A: It is a virtual table.

C: It helps to manage permissions and other administrative tasks on a table
It is used to test whether the values retrieved by the outer query exist in the result of the inner query - Correct or Incorrect regarding EXIST operator used in Correlated subquery ?
Correct
It is totally and automatically maintained by Oracle - Correct or Incorrect about Oracle Data Dictionary ?
Correct
If a column is used frequently in the search criteria of a WHERE clause, what additional attribute would that column need to have in order to consider building an index on it?

a column that is a small number of characters or digits

a column that is updated frequently

a column containing a wide range of values

a column with a small number of null values
a column containing a wide range of values
What is produced when a join condition is not specified in a multiple-table query?
a Cartesian product or cross join
Which of the following join conditions is generally associated with a hierarchical query?
Self-join
To insert data through a simple view, must you include all NOT_NULL columns that do not have a default value assigned in the view definition?
Yes
If SYSDATE returns 12-JUL-2009, what is returned by the following statement? SELECT TO_CHAR(SYSDATE, 'fmDDth MONTH') FROM DUAL;
12TH JULY
Which element would be used in a format model to return the spelled-out year?
YEAR or SYEAR
Where does Oracle store the granted privileges?
Data dictionary
revoke privileges on object from user; - Correct or Incorrect for revoking privileges on a table?
Correct
REFERENCES is a system privilege - Correct or Incorrect about System Privilege ?
Incorrect
One sequence can be used for multiple tables in the same schema - Correct or Incorrect about a Sequence ?
Correct
Tasks Performed By -- INSTR --
Used to return the numeric value for position of a named character from the character string.
You need to add a NOT NULL constraint to the QUANTITY column in the PO_DETAIL table. Which statement should you use to complete this task?
ALTER TABLE po_detail MODIFY (quantity NOT NULL);
Constraints can be created before a table is created - True or False ?
1
Which statement type would be used to remove transactions more than one year old from the TRX table?
DML
A subquery can be placed in a WHERE clause, GROUP BY clause, or a HAVING clause - True or False regarding Subqueries ?
1
Which represents the correct pair-wise comparison in which the rows in the subquery are evaluated in the main query of the multiple-column subquery?
Column-to-column comparison and row-to-row comparison
Which data dictionary view displays only the data dictionary views accessible to the user?
DICTIONARY
If a compound query contains both a MINUS and an INTERSECT operator, which will be applied first?
The precedence is determined by the order in which they are specified
If you issue this command: update employees set salary=salary * 1.1; what will be the result?
Every row will have SALARY incremented by 10 percent, unless SALARY was NULL
Which indexes store rowids associated with a key value as a bitmap?
Bitmap indexes
More than one base table can be updated through a view - Correct or Incorrect about UPDATE statements ?
Incorrect
In which of the following situations will you use the ROLLUP feature for expressions or columns within a GROUP BY clause?

Each correct answer represents a complete solution. Choose all that apply.

A: To find the groups forming the sub totals in a row.

B: To speed up and simplify the maintenance and population of summary tables.

C: For subtotaling along a hierarchical dimension such as geography or time.

D: To create grouping of expressions or columns specified within a GROUP BY clause in one direction from left to right for calculating the sub totals.
B: To speed up and simplify the maintenance and population of summary tables.

C: For subtotaling along a hierarchical dimension such as geography or time.

D: To create grouping of expressions or columns specified within a GROUP BY clause in one direction from left to right for calculating the sub totals.
Which of the following should be put together (separated by commas) to define
concatenated groupings?

Each correct answer represents a complete solution. Choose all that apply.

A: AVG
B: ROLLUPs
C: GROUPING SETS
D: CUBEs
B: ROLLUPs
C: GROUPING SETS
D: CUBEs
Samantha works as a Database Administrator for Dolliver Inc. The company uses an Oracle database. David is an employee in the company. He has created a few database objects in his schema. Now, Samantha wants to restrict David from accessing the database. She also wants to ensure that the objects owned by David remain intact in the database.

What will she do to accomplish this?

Each correct answer represents a complete solution. Choose two.

A: Lock David's user account.

B: Revoke the RESTRICTED SESSION privilege from David's user account.

C: Drop David's user account from the database.

D: Revoke the CREATE SESSION privilege from David's user account
A: Lock David's user account.

D: Revoke the CREATE SESSION privilege from David's user account
The following are the steps for a correlated subquery, listed in random order:

1. The WHERE clause of the outer query is evaluated.
2. The candidate row is fetched from the table specified in the outer query.
3. The procedure is repeated for the subsequent rows of the table, till all the rows are processed.
4. Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query.

Identify the option that contains the steps in the correct sequence in which the Oracle server evaluates a correlated subquery.

A: 4-1-2-3
B: 2-4-1-3
C: 4-2-1-3
D: 2-1-4-3
B: 2-4-1-3
Which is the default value of ORDER BY clause to sort the result set?
ASC
Evaluate the SQL statements:

CREATE TABLE new_order
(orderno NUMBER(4),
booking_dateTIMESTAMP WITH LOCAL TIME ZONE);

The database is located in San Francisco where the time zone is -8:00.

The user is located in New York where the time zone is -5:00.

A New York user inserts the following record:

INSERT INTO new_order
VALUES(1, TIMESTAMP ?007-05-10 6:00:00 -5:00?);

Which statement is true?

A. When the New York user selects the row, booking_date is displayed as 007-05-10
3.00.00.000000'

B. When the New York user selects the row, booking_date is displayed as 2007-05-10
6.00.00.000000 -5:00'.

C. When the San Francisco user selects the row, booking_date is displayed as 007-05-10 3.00.00.000000'

D. When theSan Francisco user selects the row, booking_date is displayed as 007-05-10 3.00.00.000000 -8:00'
C
Which regular expression functions search a given pattern in the source string and returns the row(s) that satisf(ies)y the specified condition?
REGEXP_LIKE / REGEXP_SUBSTR
You have been assigned a task to transform a non-relational database into relational
database tables. This is required, as the source data is not in relational format.

Which of the following options should you use to accomplish the given task?

A: Conditional INSERT FIRST
B: Conditional INSERT ALL
C: Unconditional INSERT ALL
D: Pivoting INSERT
D: Pivoting INSERT
A Correlated sub-query is evaluated once for every row returned by the outer query - Correct or Incorrect regarding Correlated subquery ?
Correct
Which data dictionary views contains comments on columns of all tables and views?
DBA_COL_COMMENTS
You work as a Database Developer for Dolliver Inc. The company uses Oracle 11g as its
database. The company implements its incentive policy for only those employees who have completed six months of work or are contributing exceptionally well to the company. The company's database contains a table named Employee. You want to retrieve the records of all those employees who are not eligible for the incentive policy.

Which of the following SQL commands can you use to accomplish the task?

Each correct answer represents a complete solution. Choose two.

A: SELECT * FROM EMPLOYEE WHERE Emp_incentive ISNULL;

B: SELECT * FROM EMPLOYEE WHERE Emp_incentive IS NULL;

C: SELECT * FROM EMPLOYEE WHERE NOT(Emp_incentive IS NOT NULL);

D: SELECT * FROM EMPLOYEE WHERE Emp_incentive = NULL;
B: SELECT * FROM EMPLOYEE WHERE Emp_incentive IS NULL;

C: SELECT * FROM EMPLOYEE WHERE NOT(Emp_incentive IS NOT NULL);
GROUP BY cannot be used without the ORDER BY clause - Correct or Incorrect ?
Incorrect
Which is a join of a table to itself?
Self join
An error occurs when a row is both the parent and the child - Correct or Incorrect about Hierarchial Queries ?
Correct
Which statement can be used to alter a view?
CREATE OR REPLACE VIEW
What value is returned after executing the following statement? SELECT NVL(SUBSTR('AM I NULL',10),'YES I AM') FROM DUAL;
YES I AM
If today is March 12, 2012, under what conditions will the statement SELECT to_date('12-Mar-2012') sysdate FROM dual; produce the number 0 for its output?
When it was executed at exactly midnight.
Which function can be used to determine the number of months between two dates?
MONTHS_BETWEEN. The syntax of the MONTHS_BETWEEN function is MONTHS_BETWEEN(date1, date2)
They can contain group functions - True Or False regarding Multiple-Row subquery ?
1
GRANT CREATE TABLE TO user1, user2 - Correct or Incorrect ?
Correct
REGEXP_INSTR()
REGEXP_INSTR()

REGEXP_INSTR extends the functionality of the INSTR function by letting you search a string for a regular expression pattern. The function evaluates strings using characters as defined by the input character set. It returns an integer indicating the
beginning or ending position of the matched substring, depending on the value of the return_option argument. If no match is found, then the function returns 0.

■ source_char is a character expression that serves as the search value. It is commonly a character column and can be of any of the data types CHAR,VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

■ pattern is the regular expression. It is usually a text literal and can be of any of the data types CHAR, VARCHAR2, NCHAR, or NVARCHAR2. It can contain up to 512 bytes. If the data type of pattern is different from the data type of source_
char, then Oracle Database converts pattern to the data type of source_char.

■ position is a positive integer indicating the character of source_char where Oracle should begin the search. The default is 1, meaning that Oracle begins the search at the first character of source_char.

■ occurrence is a positive integer indicating which occurrence of pattern in source_char Oracle should search for. The default is 1, meaning that Oracle searches for the first occurrence of pattern. If occurrence is greater than 1,
then the database searches for the second occurrence beginning with the first
character following the first occurrence of pattern, and so forth. This behavior is different from the INSTR function, which begins its search for the second occurrence at the second character of the first occurrence.

■ return_option lets you specify what Oracle should return in relation to the occurrence:
- If you specify 0, then Oracle returns the position of the first character of the occurrence. This is the default.
- If you specify 1, then Oracle returns the position of the character following the occurrence.

■ match_parameter is a text literal that lets you change the default matching behavior of the function. The behavior of this parameter is the same for this function as for REGEXP_COUNT. Refer to REGEXP_COUNT on page 5-158 for
detailed information.

■ For a pattern with subexpressions, subexpr is an integer from 0 to 9 indicating which subexpression in pattern is the target of the function. The subexpr is a fragment of pattern enclosed in parentheses. Subexpressions can be nested.

Subexpressions are numbered in order in which their left parentheses appear in pattern.

For example, consider the following expression:
0123(((abc)(de)f)ghi)45(678)

This expression has five subexpressions in the following order: "abcdefghi" followed by "abcdef", "abc", "de" and "678".

If subexpr is zero, then the position of the entire substring that matches the pattern is returned. If subexpr is greater than zero, then the position of the substring fragment that corresponds to subexpression number subexpr in the
matched substring is returned. If pattern does not have at least subexpr subexpressions, the function returns zero. A null subexpr value returns NULL.
The default value for subexpr is zero.

The following example examines the string, looking for occurrences of one or more non-blank characters. Oracle begins searching at the first character in the string and returns the starting position (default) of the sixth occurrence of one or more non-blank characters.

SELECT REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA', '[^ ]+', 1, 6) "REGEXP_INSTR" FROM DUAL;

REGEXP_INSTR
------------
37

The following example examines the string, looking for occurrences of words beginning with s, r, or p, regardless of case, followed by any six alphabetic characters. Oracle begins searching at the third character in the string and returns the position in
the string of the character following the second occurrence of a seven-letter word beginning with s, r, or p, regardless of case.

SELECT REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA', '[s|r|p][[:alpha:]]{6}', 3, 2, 1, 'i') "REGEXP_INSTR" FROM DUAL;

REGEXP_INSTR
------------
28
Using the FLASHBACK feature of Oracle, which of the following options will retrieve data from the EMP table for a particular time without actually doing a restore of the EMP table?
SELECT * FROM EMP AS OF TIMESTAMP <timestamp>; / DBMS_FLASHBACK.ENABLE_AT_TIME (<timestamp>); SELECT * FROM EMP;
A constraint name must be provided when using the WITH CHECK OPTION clause or the statement will fail - Correct or Incorrect concerning the creation of a view ?
Incorrect
Roles are owned by the SYS user - Correct or Incorrect about ROLES ?
Incorrect
Which are the types of SQL statement?
DDL, DML, DCL
Which object methods provide access to the data of an object?
Member Methods
The WITH clause may be processed as an inline view or resolved as a temporary table - Correct or Incorrect ?
Correct
Which of the following SQL keyword is used to sort the result set of a query?
ORDER BY
Single Row Functions can accept only one argument - True or False ?
1
Which of the following utilities offers statement tracing and instant feedback on any successful SELECT, INSERT, UPDATE or DELETE statement?
Autotrace
An external table named ContractEmployees provides data for the Employees table in a database. DROP TABLE ContractEmployees; Which statements is TRUE about the DROP TABLE statement?
The DROP TABLE statement removes only the metadata of the external table from the database
You have been assigned a task to transform a non-relational database into relational database tables. This is required, as the source data is not in relational format. Which options should you use to accomplish the given task?
Pivoting INSERT
They impose rules to be followed when data/rows are being added, modified and removed from a table - Correct or Incorrect about CONSTRAINTS ?
Correct
Which of the following will be the result of executing the following SQL statement?

SELECT TRUNC(SYSDATE,'YEAR') FROM DUAL;

Assume that the value of SYSDATE is equal to 30-DEC-2008.

A: 30-DEC-2007
B: 01-JAN-2007
C: 31-DEC-2008
D: 01-JAN-2008
D: 01-JAN-2008
You have written the following CREATE SEQUENCE statement:

CREATE SEQUENCE s1
START WITH 100
INCREMENT BY 10
MAXVALUE 500
CYCLE
NOCACHE;

The sequence s1 has generated numbers up to the maximum limit of 500. You issue the following SQL statement:

SELECT s1.nextval FROM dual;

Which of the following is the correct output displayed by the SELECT statement?

A: 10
B: 1
C: 100
D: 500
B: 1
The details of the order ID, order date, order total, and customer ID are obtained from the
ORDERS table. If the order value is more than 30000, the details have to be added to the
LARGE_DRDERS table. The order ID, order date, and order total should be added to the
ORDER_HISTORY table, and order ID and customer ID should be added to the CUST_HISTORY table.

Which multitable INSERT statement would you use?

A. Pivoting INSERT
B. Unconditional INSERT
C. Conditional ALL INSERT
D. Conditional FIRST INSERT
C
Which of the following commands will specify a DEFAULT value for the HIREDATE column in the EMP table?

Each correct answer represents a complete solution. Choose all that apply.

A: CREATE TABLE EMP
(
EMPNO NUMBER(5),
HIREDATE DATE,
DEFAULT HIREDATE SYSDATE
);

B: CREATE TABLE EMP
(
EMPNO NUMBER(5),
HIREDATE DATE DEFAULT SYSDATE
);

C: ALTER TABLE EMP MODIFY HIREDATE DEFAULT SYSDATE;

D: ALTER TABLE EMP MODIFY HIREDATE DEFAULT=SYSDATE;
B: CREATE TABLE EMP
(
EMPNO NUMBER(5),
HIREDATE DATE DEFAULT SYSDATE
);

C: ALTER TABLE EMP MODIFY HIREDATE DEFAULT SYSDATE
SELECT REGEXP_REPLACE('Charles Dickens','([[:alpha:]]+) ([[:alpha:]]+)','2') FROM DUAL; What will this SQL statement return when executed?
Dickens
View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.

You executed the following query to display PRODUCT_NAME and the number of times the
product has been ordered:

SELECT p.product_name, i.item_cnt
FROM (SELECT product_id, COUNT (*) item_cnt
FROM order_items GROUP BY product_id) i RIGHT OUTER JOIN products p
ON i.product_id = p.product_id;

What would happen when the above statement is executed?

A. The statement would not execute because the WHERE clause is not allowed with LEFT
OUTER JOIN.

B. The statement would execute successfully to produce the required output.

C. The statement would not execute because inline views and outer joins cannot be used
together.

D. The statement would not execute because the ITEM_CNT alias cannot be displayed in the
outer query.

E. The statement would not execute because the GROUP BY clause cannot be used in the inline
view.
A
Which SQL operations support the usage of sub-queries for viewing and manipulating table data?
SELECT, INSERT, UPDATE, CREATE TABLE
EMP is a table with all the details of employees and EMP_TERM is a new table with
employee number and name of the employees that have been terminated from the company.

Which of the following options are the alternative ways to write the following SQL query?

SELECT A.EMPNO, A.ENAME, A.JOB, A.HIREDATE, A.SAL, A.MGR, A.DEPTNO
FROM EMP A WHERE (A.EMPNO, A.ENAME) IN (SELECT B.EMPNO, B.ENAME FROM
EMP_TERM B);

Each correct answer represents a complete solution. Choose two.

A: SELECT A.EMPNO, A.ENAME, A.JOB, A.HIREDATE, A.SAL, A.MGR, A.DEPTNO
FROM EMP A WHERE A.EMPNO IN (SELECT
B.EMPNO FROM EMP_TERM B) AND A.ENAME IN (SELECT C.ENAME FROM EMP_TERM C);

B: SELECT A.EMPNO, A.ENAME, A.JOB, A.HIREDATE, A.SAL, A.MGR, A.DEPTNO
FROM EMP A WHERE (A.ENAME, A.EMPNO) IN (SELECT B.EMPNO, B.ENAME FROM EMP_TERM B);

C: SELECT A.EMPNO, A.ENAME, A.JOB, A.HIREDATE, A.SAL, A.MGR, A.DEPTNO
FROM EMP A WHERE A.EMPNO = (SELECT B.EMPNO FROM EMP_TERM B)
AND A.ENAME = (SELECT C.ENAME FROM EMP_TERM C);

D: SELECT A.EMPNO, A.ENAME, A.JOB, A.HIREDATE, A.SAL, A.MGR, A.DEPTNO
FROM EMP A WHERE (A.EMPNO, A.ENAME) IN (SELECT * FROM EMP_TERM B);
A: SELECT A.EMPNO, A.ENAME, A.JOB, A.HIREDATE,
A.SAL, A.MGR, A.DEPTNO FROM EMP A
WHERE A.EMPNO IN (SELECT B.EMPNO FROM EMP_TERM B) AND A.ENAME IN (SELECT C.ENAME FROM EMP_TERM C);

D: SELECT A.EMPNO, A.ENAME, A.JOB, A.HIREDATE,
A.SAL, A.MGR, A.DEPTNO FROM EMP A
WHERE (A.EMPNO, A.ENAME) IN (SELECT * FROM EMP_TERM B);
Group Functions are also known as aggregate functions - Correct or Incorrect ?
Correct
Which of the following statements is true of Boolean operators?
OR is evaluated after AND / NOT is evaluated first
Which of the following precedence orders represents the correct precedence of the
operators?

A: + (binary), - (unary), *, /, +, -, ||, =, !=, <, >, <=, >=, IS NULL, LIKE, IN,BETWEEN, NOT, AND, OR

B: +, -, *, /, +, -, ||, =, !=, <, >, <=, >=, LIKE, IS NULL BETWEEN, IN, NOT, AND,OR

C: + (unary), - (binary), +, -, *, /, ||, =, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN,IN, NOT, OR, AND

D: + (unary), - (unary), *, /,+, -, ||, =, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN,IN, NOT, AND, OR
D: + (unary), - (unary), *, /,+, -, ||, =, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN,IN, NOT, AND, OR
The relationship between the parent rows and their child rows is established automatically - Correct or Incorrect about Hierarchial Queries ?
Incorrect
How do you create an inline view?
include a subquery as a data source in a FROM clause
SELECT TO_DATE('02-AUG-2007') - TO_DATE('25-JUL-2007') FROM DUAL; Which statements about the above mentioned SQL query is true?
Oracle will display 8 as the output.
The GROUPING SETS operation combines the equivalent of several GROUP BY clauses with the functionality of which of the following?
UNION ALL
When using the TO_CHAR function to format dates, which format model element will display the numeric day number of the month?
DD or dd
The USER_CONS_COLUMNS view should be queried to find the names of the columns to which a constraint applies - True or False regarding Data Dictionary Views ?
TRUE
What value is returned after executing the following statement? SELECT REPLACE('How_long_is_a_piece_of_string?','_','') FROM DUAL;
Howlongisapieceofstring?
They are not based/dependent on a particular database object - Correct or Incorrect about System Privileges ?
Correct
create sequence seq1 maxvalue 50; If the current value is already 50, when you attempt to select SEQ1.NEXTVAL what will happen?
There will be an error
Which object level privileges can you grant on a database table?
INSERT / REFERENCES / ALTER / INDEX
SELECT sequence_name, min_value, max_value, increment_by, last_number FROM user_sequences; Which of the following statements represents the significance of the last_number column?
The last_number column displays the next available sequence number if NOCACHE is specified.
A table is which of the following?
A schema object
What is the difference between a nested and a correlated subquery?
A nested subquery executes the inner query first
The minimum column width that can be specified for a Varchar2 data type column is one - True or False about Oracle Data Types ?
1
What must you do to create a column alias containing spaces?
Enclose the column alias in double quotes (")
CREATE TABLE LETTERS (LETTER_ID NUMBER(7), POSTAGE NUMBER(7)); Which of the following will change POSTAGE to be an UNUSED column?
ALTER TABLE LETTERS SET UNUSED COLUMN POSTAGE;
If a view was created with the WITH CHECK OPTION, then the view can be updated only if the resulting data satisfies the view's defining query - Correct or Incorrect about UPDATE statement ?
Correct
They prevent invalid data to be entered into tables - Correct or Incorrect about CONSTRAINTS ?
Correct
The CREATE, ALTER, DROP, RENAME, and TRUNCATE statements are examples of which category of SQL statement?
DDL
You work as a Database Technical support for Dolliver Inc. The company uses Oracle 11g as its database. The database has a table named Employee. One of your clients wants to know which function he should use to show the average age of employees as 42.4 instead of 42.39 in the Age column of the table. Which of the following functions will you suggest him to use?

A: TRUNC(42.39,1)
B: CEIL(42.39)
C: ROUND(42.39,1)
D: MOD(42.39,1)
C: ROUND(42.39,1)
You work as a Database Administrator for Education Inc. You have installed the
enterprise edition of Oracle 11g to check the functionality of regular expressions. You
create a table named Student that holds information of all students. You want to retrieve rows of those students whose enrollment number does not include a digit. For this purpose, you used the caret (^) metacharacter.

Which of the following is the correct usage of a metacharacter?

A: ^[[:digit]]
B: [[:digit:]^]
C: [[:digit:^]]
D: [^[:digit:]]
D: [^[:digit:]]
Which of the following queries is qualified as a correlated sub-query?

A: SELECT X.DEPTNO, X.ENAME, X.SAL
FROM EMP X
WHERE X.SAL > (SELECT AVG(Y.SAL)
FROM EMP Y
WHERE X.DEPTNO = Y.DEPTNO)
ORDER BY X.DEPTNO;

B: SELECT X.DEPTNO, X.ENAME, X.SAL
FROM EMP X
WHERE X.SAL > (SELECT AVG(Y.SAL)
FROM EMP Y)
ORDER BY X.DEPTNO;

C: SELECT X.DEPTNO, X.ENAME, X.SAL
FROM EMP X
WHERE X.SAL > (SELECT AVG(Y.SAL)
FROM EMP Y
WHERE Y.DEPTNO = 10)
ORDER BY X.DEPTNO;

D: SELECT X.DEPTNO, X.ENAME, X.SAL
FROM EMP X
WHERE X.SAL > (SELECT AVG(Y.SAL)
FROM EMP Y)
ORDER BY Y.DEPTNO;
A: SELECT X.DEPTNO, X.ENAME, X.SAL
FROM EMP X
WHERE X.SAL > (SELECT AVG(Y.SAL)
FROM EMP Y
WHERE X.DEPTNO = Y.DEPTNO)
ORDER BY X.DEPTNO;
ABS()
ABS returns the absolute value of n.
This function takes as an argument any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type. The function returns the same data type as the numeric data type of the argument.

The following example returns the absolute value of -15:

SELECT ABS(-15) "Absolute"
FROM DUAL;
Absolute
----------
15
A Correlated sub-query cannot return multiple columns - Correct or Incorrect regarding Correlated Subquery ?
Incorrect
You work as a Database Administrator for Gentech Inc. The company uses an Oracle
database. Andrew joins the company as an Assistant Database Administrator. You are required to grant him the privilege that allows him to create a database.

Which of the following privileges will you grant him?

A: DBA
B: SYSDBA
C: SYSOPER
D: DEFAULT
B: SYSDBA
Group Function can be used only with the SQL statement that has the GROUP BY clause - Correct or Incorrect ?
Incorrect
Which is NOT a way to sequence Oracle output rows?
Oracle external sort
You work as a Database Designer for Dolliver Inc. The company uses Oracle 11g as its
database. The database contains a table named Employees. You want to retrieve Emp_id, Emp_name, and Emp_join_date of all those employees who have worked for more than 24 months since their joining date till 01-JAN-2008.

Which of the following SQL queries will you use to accomplish the task?

A: SELECT Emp_id, Emp_name, Emp_join_date
FROM Employees WHERE MONTHS_BETWEEN (Emp_join_date, '01-JAN-2008') >24;

B: SELECT Emp_id, Emp_name, Emp_join_date
FROM Employees WHERE Emp_join_date BETWEEN Emp_join_date AND '01-JAN-
2008';

C: SELECT Emp_id, Emp_name, Emp_join_date
FROM Employees WHERE MONTHS_BETWEEN ('01-JAN-2008', Emp_join_date) >24;

D: SELECT Emp_id, Emp_name, Emp_join_date
FROM Employees WHERE Emp_join_date IN (Emp_join_date, '01-JAN-2008');
C: SELECT Emp_id, Emp_name, Emp_join_date
FROM Employees WHERE MONTHS_BETWEEN ('01-JAN-2008', Emp_join_date) >24;
Rows in the target table can be deleted using the MERGE statement - Correct or Incorrect ?
Correct
Evaluate the CREATE TABLE statement:

CREATE TABLE employee
(emp_id Number (6) CONSTRAINT emp_id_pk PRIMARY KEY,
emp_name VARCHAR2(15));

Which of the following statements is true regarding the emp_id_pk constraint?

A: It would not be created and will display an error message.

B: It would be created and remain in the disabled state because no index is specified in the command.

C: It would be created only if a unique index is manually created first.

D: It would be created and would use an automatically created unique index
D: It would be created and would use an automatically created unique index.
Which pseudocolumn can be used with a sequence to display the current value of that sequence?
CURRVAL
Which of the following SQL datatypes cannot store fractional seconds?
DATE
It is used to display group data for all possible combinations of expressions - Correct or Incorrect about CUBE operator ?
Correct
Which date function returns the ending date of the month containing a given date?
LAST_DAY. The syntax of the LAST_DAY function is LAST_DAY(date)
Which clause in a SQL expression will enable a single statement to calculate all possible combinations of aggregations?
GROUP BY CUBE
A subquery can be used in a CREATE VIEW statement, regardless of the number of rows it returns - True or False regarding Subquery ?
1
Built-In SQl Functions Are available for use within a SELECT statement’s WHERE clause, as well as the SELECT statement’s expression list - True or False ?
1
An index that is based on more than one column is:
A composite index
Built-In SQl Functions Are available for use from the UPDATE statement - True or False ?
1
It contains the object privileges granted to other users by the current user session - Correct or Incorrect about the SESSION_PRIVS dictionary view ?
Incorrect
Which index is built on columns with low cardinality and is used for data warehouse applications?
Bitmap index
REGEXP_REPLACE()
REGEXP_REPLACE()

REGEXP_REPLACE extends the functionality of the REPLACE function by letting you search a string for a regular expression pattern. By default, the function returns source_char with every occurrence of the regular expression pattern replaced with replace_string. The string returned is in the same character set as source_char.
The function returns VARCHAR2 if the first argument is not a LOB and returns CLOB if
the first argument is a LOB.

■ source_char is a character expression that serves as the search value. It is commonly a character column and can be of any of the data types CHAR,VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB.

■ pattern is the regular expression. It is usually a text literal and can be of any of the data types CHAR, VARCHAR2, NCHAR, or NVARCHAR2. It can contain up to 512 bytes. If the data type of pattern is different from the data type of source_
char, then Oracle Database converts pattern to the data type of source_char.

■ replace_string can be of any of the data types CHAR, VARCHAR2, NCHAR,NVARCHAR2, CLOB, or NCLOB. If replace_string is a CLOB or NCLOB, then Oracle truncates replace_string to 32K. The replace_string can contain up to 500 backreferences to subexpressions in the form \n, where n is a number from 1 to 9. If n is the backslash character in replace_string, then you must precede it with the escape character (\\).

■ position is a positive integer indicating the character of source_char where Oracle should begin the search. The default is 1, meaning that Oracle begins the search at the first character of source_char.

■ occurrence is a nonnegative integer indicating the occurrence of the replace operation:
- If you specify 0, then Oracle replaces all occurrences of the match.
- If you specify a positive integer n, then Oracle replaces the nth occurrence.

If occurrence is greater than 1, then the database searches for the second occurrence beginning with the first character following the first occurrence of pattern, and so forth. This behavior is different from the INSTR function, which
begins its search for the second occurrence at the second character of the first occurrence.

■ match_parameter is a text literal that lets you change the default matching behavior of the function. The behavior of this parameter is the same for this function as for REGEXP_COUNT.

The following example examines phone_number, looking for the pattern xxx.xxx.xxxx. Oracle reformats this pattern with (xxx) xxx-xxxx.

SELECT REGEXP_REPLACE(phone_number,
'([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
'(\1) \2-\3') "REGEXP_REPLACE"
FROM employees
ORDER BY "REGEXP_REPLACE";

REGEXP_REPLACE

(515) 123-4444
(515) 123-4567
(515) 123-4568
(515) 123-4569
(515) 123-5555

The following example examines country_name. Oracle puts a space after each non-null character in the string.

SELECT REGEXP_REPLACE(country_name, '(.)', '\1 ') "REGEXP_REPLACE"
FROM countries;

REGEXP_REPLACE
------------------------------------------------------------------
A r g e n t i n a
A u s t r a l i a
B e l g i u m
B r a z i l
C a n a d a

The following example examines the string, looking for two or more spaces. Oracle replaces each occurrence of two or more spaces with a single space.

SELECT REGEXP_REPLACE('500 Oracle Parkway, Redwood Shores, CA',
'( ){2,}', ' ') "REGEXP_REPLACE"
FROM DUAL;

REGEXP_REPLACE
--------------------------------------
500 Oracle Parkway, Redwood Shores, CA
Can you Use CURRVAL or NEXTVAL In the SELECT query of a view ?
No
Consider the following SQL statement:

ALTER TABLE Product
SET UNUSED (prod_name);

Which statements are true regarding the effect of the above statement?

Each correct answer represents a complete solution. Choose all that apply

A: Any view created on the Product table that includes the prod_name column would have to be dropped and recreated.
B: Any synonym existing on the Product table would have to be recreated.
C: The above statement will drop the prod_name along with the Product table.
D: Any constraint defined on the prod_name column would be removed by the above
command.
A: Any view created on the Product table that includes the prod_name column would have to be dropped and recreated.

D: Any constraint defined on the prod_name column would be removed by the above command.
Rules enforced at the table level concerning acceptable data which can be entered into a table are called what?
constraints
What character should separate columns in the SELECT list?
commas
A table can have up to 10,000 columns - True or False ?
1
It removes only the rows that have a NULL value in them - Correct or Incorrect about DELETE FROM statements ?
Incorrect
Which tasks can a user perform with the help of the flashback technology?
Recover tables or rows to a previous point in time / Automatically track and archive transactional data changes / Perform queries that return past data. / Roll back a transaction and its dependent transactions while the database remains online.
What would you use in an INSERT statement instead of a VALUES clause to copy rows from one table to another?
a subquery
Which mandatory clauses should be added to successfully create an external table called Emp?
DEFAULT DIRECTORY
You work as a Database Designer for Dolliver Inc. The company uses Oracle 11g as its
database. The database contains a table named Employees. The marketing members had gone on a recruitment drive for the company, one that turned out to be highly successful. Now the team has asked you to write a SQL statement that will retrieve the days of the week on which more than 10 staff members were hired, as well as the
exact number of employees hired on these days. This other condition must be specified
previously in the SQL statement.

Which of the following SQL statements will you use to accomplish the task?

A: SELECT TO_CHAR(Emp_hire_date, 'day') Emp_hire_day, COUNT(*) FROM EMPLOYEES
GROUP BY TO_CHAR(Emp_hire_date, 'day')
HAVING COUNT(*) >10;

B: SELECT TO_CHAR(Emp_hire_date, 'day') Emp_hire_day, COUNT(*) FROM EMPLOYEES
HAVING COUNT(*) >=10;

C: SELECT TO_CHAR(Emp_hire_date, 'day') Emp_hire_day, COUNT(*) FROM EMPLOYEES
GROUP BY TO_CHAR(Emp_hire_date, 'day')
WHERE COUNT > =10;

D: SELECT TO_CHAR(Emp_hire_date, 'day') Emp_hire_day, COUNT(*) FROM EMPLOYEES
GROUP BY TO_CHAR(Emp_hire_date, 'day')
HAVING COUNT(*) >=10;
A: SELECT TO_CHAR(Emp_hire_date, 'day') Emp_hire_day, COUNT(*) FROM EMPLOYEES
GROUP BY TO_CHAR(Emp_hire_date, 'day')
HAVING COUNT(*) >10;
You disabled the PRIMARY KEY constraint on the id column in the inventory table and updated all the values in the inventory table. You need to enable the constraint and verify that the new id column values do not violate the constraint. If any of the id column values do not conform to the constraint, an error message should be returned.

Evaluate this statement:

ALTER TABLE inventory
ENABLE CONSTRAINT inventory_id_pk;

Which statement is true?

The statement will achieve the desired results.

The statement will execute, but will not enable the PRIMARY KEY constraint.

The statement will execute, but will not verify that values in the ID column do not violate the constraint.

The statement will return a syntax error.
The statement will achieve the desired results
CONVERT()
CONVERT converts a character string from one character set to another.

■ The char argument is the value to be converted. It can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
■ The dest_char_set argument is the name of the character set to which char is converted.
■ The source_char_set argument is the name of the character set in which char is stored in the database. The default value is the database character set.

The return value for CHAR and VARCHAR2 is VARCHAR2. For NCHAR and NVARCHAR2,
it is NVARCHAR2. For CLOB, it is CLOB, and for NCLOB, it is NCLOB.

Both the destination and source character set arguments can be either literals or columns containing the name of the character set.
You work as a Database Developer for TechSoft Inc. The company uses Oracle as its database. The database has a table named NEW_CUST that stores information of all new customers of the company. You want to load the information of new customers from the NEW_CUST table into two tables called CUST and SPECIAL_CUST.

If a new customer has a credit limit greater than 15,000, the details should get loaded/inserted into the SPECIAL_CUST table, otherwise the details should be loaded/inserted into the CUST table.

Which of the following should be used to load the
data efficiently?

A: Multitable INSERT statement
B: MERGE Command
C: Internal LOBS
D: External table
E: INSERT using WITH CHECK OPTION
A: Multitable INSERT statement
What does the format of my_string have to be in order for the WHERE clause in the following query to evaluate to TRUE? SELECT * FROM dual WHERE REGEXP_LIKE(my_string,'^[a-z]+$');
one or more all alphabetic lowercase characters
What are the two operators that are functionally equivalent to the <> operator?
!= and ^=
Which SQL statement will you use to display a message in the following format?

Outer1 Inner1 Inner2

Here, Outer1, Inner1, and Inner2 are the first, second, and third elements of the
message, respectively.

A: SELECT CONCAT('Outer1' || 'Inner1' || 'Inner2') FROM DUAL;

B: SELECT CONCAT("Outer1", CONCAT("Inner1", "Inner2")) FROM DUAL;

C: SELECT CONCAT('Outer1', CONCAT(' Inner1', 'Inner2')) FROM DUAL;

D: SELECT CONCAT('Outer1', 'Inner1', 'Inner2') FROM DUAL;
C: SELECT CONCAT('Outer1', CONCAT(' Inner1', 'Inner2')) FROM DUAL;
Sequences cannot be used in the subquery of the multitable insert statement - Correct or Incorrect ?
Correct
Which statement correctly grants a system privilege?

A. GRANT EXECUTE
ON prod TO PUBLIC;

B. GRANT CREATE VIEW
ON tablel TO used;

C. GRANT CREATE TABLE
TO used ,user2;

D. GRANT CREATE SESSION
TO ALL;
C
In which clause of a SELECT statement can a column alias NOT be used?
A column alias CANNOT be used in a WHERE clause.
You work as a Database Administrator for Gadgets Inc. The company uses Oracle as its
database. The database contains a table named Employees. The table consists of columns, namely Emp_id, Emp_name, Emp_dept, Emp_cnt, Emp_desig, and Emp_add.

Now, you want to view the table schema.

Which of the following commands or packages will you use to accomplish the task?

A: COMMIT
B: DBMS_STATS
C: DESC
D: DBMS_SYSTEM
E: ROLLBACK
C: DESC
You want to retrieve the address of all those employees who provide technical support and have joined after the year 2007. Which queries will you use to accomplish the task?
SELECT emp_add FROM employee WHERE job_desig = 'tech_support' AND join_date > '31-dec-2007';
SELECT Brand, Series, COUNT(*) FROM CompProducts GROUP BY Brand, CUBE(Series, Type); Which statements is TRUE about the given query?
The number of computers of every series of every brand is displayed / Grand total is not displayed by this query
It calculates a grand total of all the groups - Correct or Incorrect about ROLLUP operation ?
Correct
Within which statements can a view be created by embedding a subquery?
CREATE VIEW
If A and B are joined by multiple join conditions, then you must use the (+) operator in all of these conditions - Correct or Incorrect about using Oracle Join Operator ( + ) ?
Correct
Single-Row Functions execute once for each record processed - True or False ?
1
Fill in the blank with a numeric function to get 15 as the output --- SELECT __________ (-15) "Absolute" FROM DUAL;
ABS
Roles are named groups of related privileges that can be granted to users or other roles - Correct or Incorrect about ROLES ?
Correct
They can be used to retrieve multiple rows from a single table only - True or False regarding Multiple-row Subquery ?
1
It does not have any usage of SQL group functions or grouping of data - Correct or Incorrect about Simple View ?
Correct
MIN()
MIN returns minimum value of expr. You can use it as an aggregate or analytic function.

The following statement returns the earliest hire date in the hr.employees table:

SELECT MIN(hire_date) "Earliest"
FROM employees;

Earliest
---------
13-JAN-01
A view is like a logical table - Correct or Incorrect about VIEWS ?
Correct
Which clause of the ALTER TABLE statement would you use to add a NOT NULL constraint to an existing table?
the MODIFY clause
All the constraints can be defined at the column level as well as the table level - True or False ?
1
Which types of constraint require an index?
PRIMARY KEY, UNIQUE
Can You Use CURRVAL or NEXTVAL In the WHERE clause of a SELECT statement ?
No
It retrieves all versions including the deleted as well as subsequently reinserted versions of the rows - Correct or Incorrect about FLASHBACK Version Query?
Correct
Which two categories of statements cause an autocommit to occur?
DCL and DDL statements
An external table is a read-write source - Correct or Incorrect about EXTERNAL tables ?
Incorrect
A co-related sub query can be used in UPDATE statements to update data from other tables - Correct or Incorrect about UPDATE statements ?
Correct
In the EMP table, the primary key is Emp_id; and in the DEPT table the composite primary key is (Dept_id, Emp_id).

Which of the following are valid create index statements?

Each correct answer represents a complete solution. Choose two.

A: CREATE INDEX Emp_idx
ON DEPT(Emp_id);

B: CREATE INDEX Emp_idx
ON EMP(Emp_id);

C: CREATE INDEX Emp_idx
ON EMP, DEPT(Emp_id, Dept_id, Dept_name);

D: CREATE INDEX Emp_idx
ON DEPT(Dept_id);
A: CREATE INDEX Emp_idx
ON DEPT(Emp_id);

D: CREATE INDEX Emp_idx
ON DEPT(Dept_id);
CURRENT_DATE()
CURRENT_DATE returns the current date in the session time zone, in a value in the Gregorian calendar of data type DATE.

The following example illustrates that CURRENT_DATE is sensitive to the session time
zone:

ALTER SESSION SET TIME_ZONE = '-5:0';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;

SESSIONTIMEZONE CURRENT_DATE
--------------- --------------------
-05:00 29-MAY-2000 13:14:03

ALTER SESSION SET TIME_ZONE = '-8:0';
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;

SESSIONTIMEZONE CURRENT_DATE
--------------- --------------------
-08:00 29-MAY-2000 10:14:33
Consider the following two SQL statements:

ALTER TABLE EMPLOYEE
DROP COLUMN LAST_NAME;

Which of the following statements are true regarding the above two SQL statements?

Each correct answer represents a complete solution. Choose all that apply.

A: The Last_Name column would not be dropped.

B: The Last_Name column can be dropped even if it is a part of a composite PRIMARY KEY provided the cascade option is used.

C: The Last_Name column would be dropped provided at least one or more columns remain in the table.

D: The Last_Name column can be rolled back provided the SET_UNUSED option is added to the above SQL Statement.
B: The Last_Name column can be dropped even if it is a part of a composite PRIMARY KEY provided the cascade option is used.

C: The Last_Name column would be dropped provided at least one or more columns remain in the table.
For which of the following purposes you cannot use scalar subqueries?

Each correct answer represents a complete solution. Choose all that apply.

A: WHEN condition of triggers

B: START WITH and CONNECT BY clauses

C: GROUP BY and HAVING clauses

D: CHECK constraints on columns
A: WHEN condition of triggers

B: START WITH and CONNECT BY clauses

C: GROUP BY and HAVING clauses

D: CHECK constraints on columns
Which is a comparison operator that is used to compare a value with a NULL value?
IS NOT NULL
You work as a Database Designer for Dolliver Inc. The company uses Oracle 11g as its database. The database contains a table named Employees. You want to retrieve Emp_id, Emp_name, and Emp_join_date of all those employees who have worked for more than 24 months since their joining date till 01-JAN-2008.

Which of the following SQL queries will you use to accomplish the task?

A: SELECT Emp_id, Emp_name, Emp_join_date
FROM Employees WHERE MONTHS_BETWEEN ('01-JAN-2008', Emp_join_date) >24;

B: SELECT Emp_id, Emp_name, Emp_join_date
FROM Employees WHERE MONTHS_BETWEEN (Emp_join_date, '01-JAN-2008') >24;

C: SELECT Emp_id, Emp_name, Emp_join_date
FROM Employees WHERE Emp_join_date BETWEEN Emp_join_date AND '01-JAN-
2008';

D: SELECT Emp_id, Emp_name, Emp_join_date
FROM Employees WHERE Emp_join_date IN (Emp_join_date, '01-JAN-2008');
A: SELECT Emp_id, Emp_name, Emp_join_date
FROM Employees WHERE MONTHS_BETWEEN ('01-JAN-2008', Emp_join_date) >24;
The sum of all the INTO columns cannot exceed 999 in case of Multi table Insert -- Correct or Incorrect ?
Correct
Evaluate the following expression using meta character for regular expression:

'[AAle|ax.r$]'

Which two matches would be returned by this expression? (Choose two.)
A. Alex
B. Alax
C. Alxer
D. Alaxendar
E. Alexender
D,E
Which is used to restrict the rows returned by a query?
WHERE clause
View the Exhibit and examine the structure of the EMP table.

You executed the following command to add a primary key to the EMP table:

ALTER TABLE emp
ADD CONSTRAINT emp_id_pk PRIMARY KEY (emp_id) USING INDEX emp_id_idx;

Which statement is true regarding the effect of the command?

A. The PRIMARY KEY is created along with a new index.

B. The PRIMARY KEY is created and it would use an existing unique index.

C. The PRIMARY KEY would be created in a disabled state because it is using an existing index.

D. The statement produces an error because the USING clause is permitted only in the CREATE
TABLE command.
B
The HAVING clause conditions can have aggregate functions - Correct or Incorrect ?
Correct
What are the possible values that the GROUPING function can return?
0 / 1
You work as a Database Designer for Dolliver Inc. The company uses Oracle 11g as its database. The database contains a table named Employee.

The structure of the table is given below :

Emp_id Emp_name Emp_address Emp_contact Emp_commission

You want to fetch the names of those employees who do not earn any commission.

Therefore, you issue the following query by using SQL Developer:

SELECT Emp_name FROM EMPLOYEE
WHERE Emp_commission = NULL;

What will be the result of executing the above SQL statement?

A: It will return the rows that have non-null values in the Emp_commission column.

B: It will display an error message mentioning inappropriate use of operator.

C: It will retrieve the names of those employees whose commission is NULL.

D: It will not return any rows and will also not display any error message
D: It will not return any rows and will also not display any error message
What is the name of the function which allows you to determine the exact GROUP BY level when using any of the GROUPING functions?
GROUPING_ID
Which types of join is used to join two tables and involves a join condition using the OUTER JOIN operator with one or more columns of either of the two tables.
Outer join
What value is returned after executing the following statement? Take note that 01-JAN-2009 occurs on a Thursday. SELECT NEXT_DAY('01-JAN-2009','wed') FROM DUAL;
7-Jan-09
Views with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary - Correct or Incorrect about VIEWS ?
Correct
Assuming SYSDATE=30-DEC-2007, what value is returned after executing the following statement? SELECT TRUNC(SYSDATE,'YEAR') FROM DUAL;
1-Jan-07
Which comparison operator compares a value to every value returned by a subquery?
ALL
DML operations are allowed on the view - Correct or Incorrect about Simple View ?
Correct
What type of conversion is performed by the following statement? SELECT LENGTH(3.14285) FROM DUAL;
Implicit conversion
ROWIDTONCHAR()
ROWIDTONCHAR()

ROWIDTONCHAR converts a rowid value to NVARCHAR2 data type. The result of this
conversion is always in the national character set and is 18 characters long.
A view contains no data in it - Correct or Incorrect about VIEWS ?
Correct
It is not possible to revoke the privileges that are granted with system privileges - Correct or Incorrect about System privileges ?
Correct
What are distinguishing characteristics of heap tables?
A heap can store variable length rows. / Rows in a heap are in random order
Which SQL statements is used to create a backup copy of all the rows present in a table?
CREATE TABLE...AS SELECT statement
What are the clauses that a basic SELECT statement should always include?
FROM, SELECT
SELECT NVL(1234) FROM DUAL; - Correct or Incorrect ?
Incorrect
Which statement ends the current transaction by disregarding all pending changes?
ROLLBACK
No index can be defined on an external table - Correct or Incorrect ?
Correct
It can be used to add rows to a table by setting values to all of the columns - Correct or Incorrect about UPDATE statements ?
Incorrect
Which of the following table is used to store new data in the first available slot?
Heap table
Which data dictionary view would you query to display objects you can access either by privileges explicitly granted to you or by privileges granted to PUBLIC?
ALL_COL_PRIVS_RECD
RAWTONHEX()
RAWTONHEX()

RAWTONHEX converts raw to a character value containing its hexadecimal representation.

RAWTONHEX (raw) is equivalent to TO_NCHAR(RAWTOHEX(raw)). The value returned is always in the national character set.
The inventory table contains these columns:

id_number NUMBER PK
category VARCHAR2(10)
location NUMBER
description VARCHAR2(30)
price NUMBER(7,2)
quantity NUMBER

You want to return the total of the extended amounts for each item category and location, including only those inventory items that have a price greater than $100.00. The extended amount of each item equals the quantity multiplied by the price.

Which SQL statement will return the desired result?

SELECT category, SUM(price * quantity) TOTAL, location FROM inventory WHERE price > 100.00;

SELECT category, location, SUM(price)
FROM inventory
WHERE price > 100.00 GROUP BY category, location;

SELECT category, SUM(price * quantity) TOTAL, location FROM inventory
WHERE price > 100.00 GROUP BY category;

SELECT category, SUM(price * quantity) TOTAL, location FROM inventory
WHERE price > 100.00 GROUP BY category, location;
SELECT category, SUM(price * quantity) TOTAL, location
FROM inventory WHERE price > 100.00
GROUP BY category, location;
Evaluate the following SQL statements executed in the given order:

ALTER TABLE cust
ADD CONSTRAINT cust_id_pk PRIMARY KEY(cust_id) DEFERRABLE INITIALLY DEFERRED;

INSERT INTO cust VALUES (1,'RAJ1); --row 1

INSERT INTO cust VALUES (1,'SAM); --row 2

COMMIT;

SET CONSTRAINT cust_id_pk IMMEDIATE;

INSERT INTO cust VALUES (1,'LATA'); --row 3

INSERT INTO cust VALUES (2,'KING'); --row 4

COMMIT;

Which rows would be made permanent in the CUST table?

A. row 4 only
B. rows 2 and 4
C. rows 3 and 4
D. rows 1 and 4
C
What is the problem with the sub-query in the below exhibit?

SQL> INSERT INTO SAMPLE (SELECT * FROM EMP);
INSERT INTO SAMPLE (SELECT * FROM EMP)
*
ERROR at line 1:
ORA-00913: too many values

A: SAMPLE.EMPNO is not set to NOT NULL as EMP.EMPNO.

B: SELECT * is not allowed in a sub-query, column names have to be specified

C: The number of columns that the sub-query returns is inconsistent with the number of columns in the outer query.

D: There is a data type mismatch in the above queries.
C: The number of columns that the sub-query returns is inconsistent with the number of columns in the outer query.
The column names for the rows to be inserted must match the column names in the subquery - Correct or Incorrect about Multi table Insert ?
Incorrect
What type of Multi Table INSERT does the given SQL depict?

INSERT ALL
INTO T1 VALUES (<value list>, ...)
INTO T2 VALUES (<value list>, ...)
INTO T3 VALUES (<value list>, ...)
INTO T4 VALUES (<value list>, ...)
INTO T5 VALUES (<value list>, ...)
INTO T6 VALUES (<value list>, ...)
INTO T7 VALUES (<value list>, ...)
SELECT <column list>, ...
FROM T8
WHERE <condition>;

A: Unconditional INSERT ALL
B: Conditional INSERT FIRST
C: Conditional INSERT ALL
D: Conditional INSERT ELSE
A: Unconditional INSERT ALL
When using a LIKE condition to perform pattern matching, what does an underscore represent?
any single character
Evaluate the following INSERT statement:

INSERT ALL
WHEN order_total < 10000 THEN
INTO small_orders
WHEN order_total > 10000 AND order_total < 50000 THEN
INTO medium_orders
WHEN order_total > 50000 THEN
INTO large_orders
SELECT order_id, order_total, customer_id
FROM Orders;

Which of the following statements is true regarding the evaluation of rows returned by
the sub query in the INSERT statement?

A: They are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.

B: They are evaluated by all the three WHEN clauses regardless of the results of the
evaluation of any other WHEN clause.

C: The INSERT statement would give an error message.

D: They are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.
B: They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.
Which metacharacter, when used in a SQL regular expression, represents a match to any character in the database character set?
the period (.)
Can a group function be included in a HAVING clause?
Yes
User SCOTT in the PROD database has a table called EMP. He wants to give select
access on this table to all the users in the database.

Which of the following commands should you use to accomplish this task?

A: GRANT ALL ON EMP TO PUBLIC;

B: GRANT ALL ON EMP TO ALL;

C: GRANT SELECT ON EMP TO ALL;

D: GRANT SELECT ON EMP TO PUBLIC
D: GRANT SELECT ON EMP TO PUBLIC;
In which specific area of database administration are the keywords CUBE, ROLLUP, and GROUPING SETS are most often used?
Data Warehousing
A WHERE condition containing the (+) operator cannot be combined with another condition using the OR logical operator - Correct or Incorrect about using Oracle Join Operator ( + ) ?
Correct
View the exhibit and examine the details of the Employee table:

EMP_ID LAST_NAME JOB_ID MANAGER_ID
201 Peter MK_MAN 200
101 Morris AD_VP 200
231 David AD_VP 200
121 Fillipps ST_MAN 200
211 Harriston ST_MAN 200
102 Garten SA_MAN 201
122 Darill AS_ASST 201

You want to generate a hierarchical report for all the employees who report to the employee whose EMP_ID is 200.

Which of the following SQL clauses would you require to accomplish the task?

Each correct answer represents a complete
solution. Choose all that apply.

A: START WITH
B: GROUP BY
C: CONNECT BY
D: ORDER BY
E: WHERE
A: START WITH
C: CONNECT BY
E: WHERE
External tables are read only, so no DML operation can be performed on them - Correct or Incorrect ?
Correct
The USER_OBJECTS view can provide information about the tables and views created by the permanent users only - Correct or Incorrect about VIEWS ?
Incorrect
It is used to set the order for the groups to be used for calculating the grand totals and subtotals - Correct or Incorrect about GROUPING function ?
Incorrect
Which group functions can return character and date results?
MIN
Which of the following operators return TRUE if both component conditions are TRUE and return FALSE if either is FALSE; otherwise returns UNKNOWN?
AND
A character value may be converted to a date value using the TO_DATE function - True or False ?
TRUE
LENGTH()
The LENGTH functions return the length of char. LENGTH calculates length using characters as defined by the input character set. LENGTHB uses bytes instead of characters. LENGTHC uses Unicode complete characters. LENGTH2 uses UCS2 code points. LENGTH4 uses UCS4 code points.

char can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or
NCLOB. The exceptions are LENGTHC, LENGTH2, and LENGTH4, which do not allow
char to be a CLOB or NCLOB. The return value is of data type NUMBER. If char has data type CHAR, then the length includes all trailing blanks. If char is null, then this function returns null.

The LENGTHB function is supported for single-byte LOBs only. It cannot be used with CLOB and NCLOB data in a multibyte character set.

The following example uses the LENGTH function using a single-byte database character set:

SELECT LENGTH('CANDIDE') "Length in characters" FROM DUAL;

Length in characters
--------------------
7
You have created an index with this statement: create index ename_i on employees(last_name,first_name); How can you adjust the index to include the employees’ birthdays, which is a date type column called DOB?
You must drop the index and re-create it.
System privileges confer on the grantee a group of roles, objects, and other privileges - Correct or Incorrect about System Privileges ?
Incorrect
Can any user who owns an object grant object privileges for that object?
Yes
A constraint can be disabled even if the constraint column contains data - True or False ?
1
The query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block - Correct or Incorrect regarding the usage of WITH clause in complex correlated queries ?
Correct
Tasks Performed By - DECODE --
Used to translate an expression after comparing it with each search value.
The database object that stores lookup information to speed up querying in tables is:
INDEX
Marking a column as unused and then using the alter table name drop unused column statement is useful because it allows the DBA to take away column access quickly and immediately - Correct or Incorrect about UNUSED column ?
Correct
Which commands will terminate a transaction?
COMMIT / ROLLBACK / TRUNCATE
It is not possible to specify NULL or NOT NULL in a view constraint - Correct or Incorrect about NOT NULL constraint ?
Correct
The WHERE clause can be used to have your update affects a specific set of rows - Correct or Incorrect about UPDATE statement ?
Correct
The current date is July 19, 2011. You need to store this date value:

19-OCT-99

Using that date value as written, and assuming it is to be INSERTed into a table using a corresponding format mask, which statement below describing that format mask is true?

Both the YY and RR date formats will interpret the year as 1999.

Both the YY and RR date formats will interpret the year as 2099.

The RR date format will interpret the year as 2099, and the YY date format will interpret the year as 1999.

The RR date format will interpret the year as 1999, and the YY date format will interpret the year as 2099.
The RR date format will interpret the year as 1999, and the YY date format will interpret the year as 2099.
View the Exhibit and examine the description of the ORDERS table.

The orders in the ORDERS table are placed through sales representatives only. You are given the task to get the SALES_REP_ID from the ORDERS table of those sales representatives who have successfully referred more than 10 customers.

Which statement would achieve this purpose?

A. SELECT sales_rep_id, COUNT(customer_id) "Total"
FROM orders "
HAVING COUNT(customer_id) > 10;

B. SELECT sales_rep_id, COUNT(customer_id) "Total"
FROM orders "
WHERE COUNT(customer_id) > 10
GROUP BY sales_rep_id;

C. SELECT sales_rep_id, COUNT(customer_id) "Total"
FROM orders "
GROUP BY sales_rep_id
HAVING total > 10;

D. SELECT sales_rep_id, COUNT(customer_id) "Total"
FROM orders "
GROUP BY sales_rep_id
HAVING COUNT(customer_id) > 10;
D
View the Exhibit and examine the details of the EMPLOYEES table.

Evaluate the following SQL statement:

SELECT phone_number,
REGEXP_REPLACE(phone_number,'([[: digit: ]]{3})\.([[: digit: ]]{3})\.([[: digit: ]]{4})', ,(\1)\2-\3')
"PHONE NUMBER" FROM employees;

The query was written to format the PHONE_NUMBER for the employees. Which option would be the correct format in the output?

A. xxx-xxx-xxxx
B. (xxx) xxxxxxx
C. (xxx) xxx-xxxx
D. xxx-(xxx)-xxxx
C
TRUNC (number)
TRUNC (number)

The TRUNC (number) function returns n1 truncated to n2 decimal places. If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point.

This function takes as an argument any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type. If you omit n2, then the function returns the same data type as the numeric data type of the argument. If you include n2, then the function returns NUMBER.

The following examples truncate numbers:

SELECT TRUNC(15.79,1) "Truncate" FROM DUAL;
Truncate
----------
15.7

SELECT TRUNC(15.79,-1) "Truncate" FROM DUAL;
Truncate
----------
10
Which comparison operator identifies values that are equal to or larger than a particular value?
>= (greater than or equal to)
You work as a Database Developer for TechSoft Inc. The company uses Oracle as its Database. The database has a table named NEW_CUST that stores information of all new customers of the company. You want to load the information of new customers from the NEW_CUST table into two tables called CUST and SPECIAL_CUST.

If a new customer has a credit limit greater than 15,000, the details should get loaded/inserted into the SPECIAL_CUST table, otherwise the details should be loaded/inserted into the CUST table.

Which of the following should be used to load the
data efficiently?

A: Multitable INSERT statement
B: SQL* Loader
C: Merge command
D: External table
A: Multitable INSERT statement
Group Functions can be used along with the single-row function in the SELECT clause of a SQL statement - Correct or Incorrect ?
Correct
Which SELECT statement clause would you use to override the implicit sorting of a GROUP BY clause?
an ORDER BY clause
Which of the following SQL queries will generate an output of 5#6#7#8#9#?

Each correct answer represents a complete solution. Choose two.

A: SELECT SUBSTR('1#2#3#4#5#6#7#8#9#',9) FROM DUAL;

B: SELECT INSTR('1#2#3#4#5#6#7#8#9#',5,8) FROM DUAL;

C: SELECT INSTR('1#2#3#4#5#6#7#8#9#',5,12) FROM DUAL;

D: SELECT SUBSTR
('1#2#3#4#5#6#7#8#9#',9,10) FROM DUAL;
A: SELECT SUBSTR('1#2#3#4#5#6#7#8#9#',9) FROM DUAL;

D: SELECT SUBSTR('1#2#3#4#5#6#7#8#9#',9,10) FROM DUAL;
SELECT REGEXP_REPLACE(WebReference, 'ht{2}ps:/{2}','ht{2}p:/{2} w{3}.') FROM PublishedBooks; Which statements is TRUE about the given query?
https:// is replaced with http://www.
Updates are not allowed through a view if the view definition includes columns that are defined by what?
expressions
You work as a Database Developer for Gadgets Inc. The company uses Oracle as its database. The database has a table named Accounts. You want to retrieve rows from Accounts table.

For this purpose, you issue the following SQL statement:

SELECT * FROM Accounts WHERE Salary < = 5000 OR Salary > = 10000

The statement retrieves the rows having salary less than or equal to Rs. 5000 or greater than or equal to Rs. 10,000.

Which of the following characters will you use to
terminate and execute the above statement?

Each correct answer represents a complete solution. Choose two

A: A semicolon (;)
B: A colon (:)
C: A forward slash (/)
D: A backslash (\)
A: A semicolon (;)
C: A forward slash (/)
Dynamic performance views are continuously updated while a database is open and in use - correct or Incorrect about VIEWS ?
Correct
You work as a Database Developer for Dolliver Inc. The company uses Oracle as its
database. The database contains a table named Employee. Details of the last_name column in the table are given below:

Mixfeary
Iftikaar
Mirgous
Lirentz
Suzaine
Ginny

You issue the following SQL statement on the Employee table to restrict the result set:

SELECT last_name
FROM Employee
WHERE last_name LIKE '_i%';

What will be the result set of the above SQL statement?

A: Mixfeary, Mirgous, Lirentz
B: Mixfeary, Iftikaar, Lirentz, Ginny
C: Mixfeary, Ginny
D: Mixfeary, Mirgous, Lirentz, Ginny
D: Mixfeary, Mirgous, Lirentz, Ginny
It is used to find the groups forming the subtotal in a row - Correct or Incorrect about GROUPING function ?
Correct
What keyword must be included in the equality condition in a hierarchical query, on one side or the other of the equal sign?
PRIOR
A WHERE clause condition that uses < ALL will return all records where the value is ______.
a value less than the minimum returned by the subquery
The SYSDATE function returns the host machine date and time - True or False ?
1
A view can fetch data from multiple tables - Correct or Incorrect about VIEWS ?
Correct
Which three functions allow you to perform explicit datatype conversions?
TO_CHAR, TO_DATE, and TO_NUMBER
Which of the following statements will grant the role OMBUDSMAN to user JOSHUA in such a way that JOSHUA may grant the role to another user?
GRANT OMBUDSMAN TO JOSHUA WITH ADMIN OPTION;
Synonyms are used to shorten the data of the table - Correct or Incorrect about SYNONYMS ?
Incorrect
Which system privileges or roles should be granted to a new database user to connect to the database?
CREATE SESSION / CONNECT
Can you Use CURRVAL or NEXTVAL in a A SELECT statement with the DISTINCT operator ?
No
Tasks Performed By -- COUNT --
Used to count the number of records present in a column of a table.
Which schema objects supports the INDEX privilege?
Table
Which datatype includes a time zone displacement value that allows Oracle to return data to the client in the user's local session time zone?
TIMESTAMP WITH LOCAL TIME ZONE
User JOHN updates some rows and asks user ROOPESH to log in and check the changes before he commits them. Which statements is true?
ROOPESH will not be able to see the changes
To satisfy a NOT NULL constraint, every row in the table must contain a value for the column - Correct or Incorrect about NOT NULL constraint ?
Correct
Which is a collection of DML statements that form a logical unit of work?
Transaction
A column with the UNIQUE constraint can contain NULL - Correct or Incorrect regarding Constraints ?
Correct
Which commands is used to grant system level privileges?
Grant
View the Exhibit and examine the structure of the ORDERS table.

The columns ORDER_MODE
and ORDER_TOTAL have the default values 'direct' and 0 respectively.

Which two INSERT statements are valid? (Choose two.)

A. INSERT INTO orders
VALUES (1, O9-mar-2007', 'online',",1000);

B. INSERT INTO orders
(order_id ,order_date ,order_mode,
customer_id ,order_total)
VALUES(1 ,TO_DATE(NULL), 'online', 101, NULL);

C. INSERT INTO
(SELECT order_id ,order_date .customer_id
FROM orders)
VALUES (1,O9-mar-2007', 101);

D. INSERT INTO orders
VALUES (1,09-mar-2007', DEFAULT, 101, DEFAULT);

E. INSERT INTO orders
(order_id ,order_date ,order_mode .order_total)
VALUES (1 ,'10-mar-2007','online',1000);
C,D
You have been asked to produce a report of all instructors, including the classes taught by each instructor. All instructors must be included on the report, even if they are not currently assigned to teach classes
.
Which two SELECT statements could you use? (Choose two.)

SELECT i.last_name, i.first_name, c.class_name
FROM instructor i, class c;

SELECT i.last_name, i.first_name, c.class_name
FROM class c LEFT OUTER JOIN instructor i
ON (i.instructor_id = c.instructor_id)
ORDER BY i.instructor_id;

SELECT i.last_name, i.first_name, c.class_name
FROM instructor i, class c
WHERE i.instructor_id = c.instructor_id (+)
ORDER BY i.instructor_id;

SELECT i.last_name, i.first_name, c.class_name
FROM instructor i LEFT OUTER JOIN class c
ON (i.instructor_id = c.instructor_id)
ORDER BY i.instructor_id;

SELECT i.last_name, i.first_name, c.class_name
FROM instructor i, class c
WHERE i.instructor_id (+) = c.instructor_id
ORDER BY i.instructor_id;

SELECT i.last_name, i.first_name, c.class_name
FROM instructor i NATURAL JOIN class c
ON (i.instructor_id = c.instructor_id);
SELECT i.last_name, i.first_name, c.class_name
FROM instructor i, class c
WHERE i.instructor_id = c.instructor_id (+)
ORDER BY i.instructor_id;

SELECT i.last_name, i.first_name, c.class_name
FROM instructor i LEFT OUTER JOIN class c
ON (i.instructor_id = c.instructor_id)
ORDER BY i.instructor_id;
HEXTORAW()
HEXTORAW converts char containing hexadecimal digits in the CHAR, VARCHAR2,
NCHAR, or NVARCHAR2 data type to a raw value.

This function does not support CLOB data directly. However, CLOBs can be passed in as arguments through implicit data conversion.
You work as a Database Administrator for Dolliver Inc. The company uses an Oracle database. The database contains a table named Employee that keeps the record of all employees in the company.

You issue the following SELECT statement to select some details of an employee and also set the filter by using the WHERE clause:

SELECT Emp_ID, Emp_Name, Dept_ID
FROM Employee
WHERE Salary > AVG (Salary)
ORDER BY Emp_Name;

Which of the following statements is true about the SELECT statement?

A: The SELECT statement will return only the employee names.

B: The SELECT statement will return only the employee IDs.

C: The SELECT statement will return an error.

D: The SELECT statement will return only the department IDs.
C: The SELECT statement will return an error.
GROUP BY is used to divide a database table into groups based on group columns - Correct or Incorrect ?
Correct
Evaluate this SQL script:

CREATE USER hr IDENTIFIED BY hr01;
CREATE ROLE hr_director;
GRANT hr_director TO hr;
GRANT SELECT ON teacher TO hr_director;
DROP USER hr;

After the conclusion of this script, how many users are granted the hr_director role, and how many privileges are granted to the hr_director role?

one user and one privilege
one user and no privileges
no users and one privilege
no users and no privileges
no users and one privilege
Which clauses can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns?
GROUP BY
You want to retrieve the average income of the departments from the Department table if the lowest income among the departments is $50,000.

Which of the following SQL statements will you use to accomplish the task?

Each correct answer represents a complete solution. Choose two

A: SELECT AVG (CASE WHEN d.dept_income > 50000 THEN d.dept_income ELSE
50000; END CASE;) "Average department income" FROM department d;

B: CASE WHEN d.dept_income > 50000 THEN d.dept_income ELSE 50000; END CASE;
"Average salary" FROM department d;

C: SELECT AVG (CASE WHEN d.dept_income > 50000 THEN d.dept_income ELSE
50000; END CASE;) FROM department d;

D: CASE WHEN d.dept_income > 50000 THEN d.dept_income ELSE 50000; END CASE;
FROM department d;
A: SELECT AVG (CASE WHEN d.dept_income > 50000 THEN d.dept_income ELSE
50000; END CASE;) "Average department income" FROM department d;

C: SELECT AVG (CASE WHEN d.dept_income > 50000 THEN d.dept_income ELSE
50000; END CASE;) FROM department d;
Which meta characters functions will you use to get only those rows that match a given regular expression?
REGEXP_LIKE
Consider the following statement:

CREATE TABLE reservations
(
id NUMBER,
cust_id NUMBER,
origin NVARCHAR(30),
destination NVARCHAR(30),
travel_date DATETIME CONSTRAINT date_chk CHECK
(REGEXP_LIKE(travel_date, '\A\d{4}?-\w{3, 9}?-\d{2}?\Z')) );

Which of the following statements are TRUE about the given constraint? (Choose two.)

14-Oct-2009 can be inserted as the travel_date into the reservations table

54-OCTOBER-2009 can be inserted as the travel_date into the reservations table

2009-Oct-14 can be inserted as the travel_date into the reservations table

2009-OCTOBER-09 can be inserted as the travel_date into the reservations table
2009-Oct-14 can be inserted as the travel_date into the reservations table

2009-OCTOBER-09 can be inserted as the travel_date into the reservations table
If the subquery returns no rows, then the value is considered to be zero - Correct or Incorrect about Scaler subquery ?
Incorrect
The database administrator creates and maintains the data dictionary view - Correct or Incorrect about VIEWS ?
Incorrect
You must display the order number, line item number, product identification number, and quantity of each item where the quantity ranges from 10 through 100. The order numbers must be in the range of 1500 through 1575.

The results must be sorted by order number from lowest to highest and then further sorted by quantity from highest to lowest.

Which statement should you use to display the desired results?

SELECT order_id, line_item_id, product_id, quantity FROM line_item
WHERE quantity BETWEEN 9 AND 101
AND order_id BETWEEN 1500 AND 1575
ORDER BY order_id DESC, quantity DESC;

SELECT order_id, line_item_id, product_id, quantity FROM line_item
WHERE (quantity > 10 AND quantity < 100)
AND order_id BETWEEN 1500 AND 1575
ORDER BY order_id ASC, quantity;

SELECT order_id, line_item_id, product_id, quantity FROM line_item
WHERE (quantity > 9 OR quantity < 101)
AND order_id BETWEEN 1500 AND 1575
ORDER BY order_id, quantity;

SELECT order_id, line_item_id, product_id, quantity FROM line_item
WHERE quantity BETWEEN 10 AND 100
AND order_id BETWEEN 1500 AND 1575
ORDER BY order_id, quantity DESC;
SELECT order_id, line_item_id, product_id, quantity
FROM line_item WHERE quantity BETWEEN 10 AND 100
AND order_id BETWEEN 1500 AND 1575 ORDER BY order_id, quantity DESC;
The datatype of the value returned by the GROUPING function is Oracle CHARACTER - Correct or Incorrect about GROUPING function ?
Incorrect
Can a row be both the child and parent of a given row?
No
Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword - Correct or Incorrect about VIEWS ?
Correct
A number value may be converted to a date value using the TO_DATE function - True or False ?
1
Which function compares two expressions, returning NULL if the expressions are equal and returning the first expression if the expressions are not equal?
the NULLIF function
REVOKE REFERENCES ON inventory FROM joe CASCADE CONSTRAINTS; Which two tasks were accomplished by executing this statement?
All the FOREIGN KEY constraints on the INVENTORY table created by user Joe were removed / The ability to create a FOREIGN KEY constraint on the INVENTORY table was revoked from user Joe
What is a subquery called when a nested subquery references a column from a table referred to a parent statement any number of levels above the subquery?
Correlated Subquery
A public synonym and a private synonym can exist with the same name for the same table - Correct or Incorrect about Synonyms ?
Correct
User SCOTT in the PROD database has a table called EMP. He wants to give select access on this table to all the users in the database. Which commands should you use to accomplish this task?
GRANT SELECT ON EMP TO PUBLIC;
A public synonym and a private synonym can exist with the same name for the same table - Correct or Incorrect about SYNONYMS ?
Correct
Which functions are conversion functions?
COMPOSE, CHARTOROWID
Which are the system level privileges?
SYSOPER / SELECT ANY TABLE / SYSDBA
To create another name for an object, and make that name available to the entire database, you would create which of the following?
PUBLIC SYNONYM
In a SELECT statement, subqueries are often used in which clause to return values for an unknown conditional value?
a WHERE clause
What datatype would be used to store binary data in an external file?
BFILE
Which clauses helps a user to use a query block repeatedly in the main query?
WITH
Which of the following queries will provide information about the number of employees working under each manager and classify them based on their designation for each manager?
SELECT MGR, JOB, COUNT (*) AS TOTAL FROM EMP GROUP BY ROLLUP (MGR, JOB);
What are the two ways in which a user can use the INSERT statement to insert data into a table, partition, or view?
Conventional INSERT and Direct-path INSERT
A foreign key cannot contain NULL values - Correct or Incorrect regarding Constraints ?
Incorrect
Which flashback features is used to retrieve metadata and historical data for a specific time interval?
FLASHBACK VERSION QUERY
The EMP table contains all the details of the employees and the EMP_BONUS_2009
table contains the new salary for employees including their performance bonuses for
the year 2009.

Which of the following queries should you use to update the SAL column of the EMP table with the new SAL values from the EMP_BONUS_2009 table?

A: UPDATE EMP A
SET A.SAL= (SELECT B.SAL FROM EMP_BONUS_2009 B);

B: UPDATE EMP A
SET B.SAL= (SELECT A.SAL FROM EMP_BONUS_2009 B
WHERE A.EMPNO=B.EMPNO);

C: UPDATE EMP
SET SAL= (SELECT B.SAL FROM EMP_BONUS_2009 B
WHERE EMPNO=B.EMPNO);

D: UPDATE EMP A
SET A.SAL= (SELECT B.SAL FROM EMP_BONUS_2009 B
WHERE A.EMPNO=B.EMPNO);
D: UPDATE EMP A
SET A.SAL= (SELECT B.SAL FROM EMP_BONUS_2009 B WHERE A.EMPNO=B.EMPNO);
COUNT()
COUNT returns the number of rows returned by the query. You can use it as an aggregate or analytic function.

If you specify expr, then COUNT returns the number of rows where expr is not null.
You can count either all rows, or only distinct values of expr.

If you specify the asterisk (*), then this function returns all rows, including duplicates and nulls. COUNT never returns null
You work as a Database Administrator for Doliiver Inc. The company uses Oracle as its database. You want to find out the difference between two date values.

For this purpose you use the following SQL query:

SELECT MONTHS_BETWEEN('15-JAN-2008', '25-MAY-2008') FROM DUAL;

What will be the result of the above SQL query?

A: -4.3225806
B: 4.3225806
C: 4
D: -4
A: -4.3225806
Evaluate the following CREATE TABLE command:

CREATE TABLE order_item (order_id NUMBER(3),
Item_id NUMBER(2),
qtyNUMBER(4),
CONSTRAINT ord_itm_id_pk
PRIMARY KEY (order_id jtem_id)
USING INDEX
(CREATE INDEX ord_itm_idx
ON order_item(order_id item_id)));

Which statement is true regarding the above SOL statement?

A. It would execute successfully and only ORD_ITM_IDX index would be created.

B. It would give an error because the USING INDEX clause cannot be used on a composite
primary key.

C. It would execute successfully and two indexes ORD_ITM_IDX and ORD_ITM_ID_PK would be
created.

D. It would give an error because the USING INDEX clause is not permitted in the CREATE
TABLE command.
A
The WHERE clause is used to exclude rows before the grouping of data - Correct or Incorrect ?
Correct
Your Manager has asked you to display the Product_Name and List_Price from the table
where the Category_ID column has values 10 or 11, and the Supplier_ID column has the value 21894.

You execute the following SQL Statement:

SELECT Product_Name, List_Price
FROM PRODUCT_DETAILS
WHERE (Category_ID=10 OR Category_ID=11) AND Supplier_ID =21894;

Which of the following statements is true regarding the execution of the above query?

A: It would not execute because the entire WHERE clause condition is not enclosed
within the parenthesis.

B: It would generate an error message.

C: It would execute, and the output would display the desired result.

D: It would not execute because the same column has been used in both sides of the AND logical operator to form the condition
C: It would execute, and the output would display the desired result.
Is it possible for the main query to contain a greater than sign in its WHERE clause if the subquery returns only one value?
Yes
You issued this statement:

GRANT UPDATE
ON inventory
TO joe
WITH GRANT OPTION;

Which task was accomplished?

Only a system privilege was granted to user joe.

Only an object privilege was granted to user joe.

User joe was granted all privileges on the inventory object.

Both an object privilege and a system privilege were granted to user joe.
Only an object privilege was granted to user joe.
A multiple-column subquery can also be a scalar subquery - Correct or Incorrect about the Multiple column Subquery ?
Incorrect
ORDER__ID is the primary key in the ORDERS table. It is also the foreign key in the
ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option.

Which DELETE statement
would execute successfully?

A. DELETE order_id
FROM orders
WHERE order_total < 1000;

B. DELETE orders
WHERE order_total < 1000;

C. DELETE
FROM orders
WHERE (SELECT order_id
FROM order_items);

D. DELETE orders o, order_items i
WHERE o.order id = i.order id;
B
Which characters anchors the expression to the end of a line?
$
It works on the column list from right to left - Correct or Incorrect about ROLLUP operation ?
Correct
INTERVAL DAY TO SECOND -- stores a value of --
+06 03:30 18.000000'
Evaluate the following CREATE TABLE command:

CREATE TABLE order_item
(order_id NUMBER(3),
item_idNUMBER(2),
qtyNUMBER(4),
CONSTRAINT ord_itm_id_pk
PRIMARY KEY (order_id item_id)
USING INDEX
(CREATE INDEX ord_itm_idx
ON order_item(order_id,item_id)));

Which statement is true regarding the above SOL statement?

A. It would execute successfully and only ORD_ITM_IDX index would be created.

B. It would give an error because the USING INDEX clause cannot be used on a composite
primary key.

C. It would execute successfully and two indexes ORD_ITM_IDX and ORD_ITM_ID_PK would be
created.

D. It would give an error because the USING INDEX clause is not permitted in the CREATE
TABLE command.
A
Which is the maximum number of columns that can be defined in a view?
1000
What two keywords in a hierarchical query establishes the relationship between a parent and a child?
CONNECT BY
subquery must be enclosed in the parenthesis - True or False ?
1
A subquery can be used in the VALUES clause of an INSERT statement, if it returns more than one row - True or False regarding subquery ?
1
A B-tree index is used for columns with low cardinality - Correct or Incorrect about B-Tree Index ?
Incorrect
The TRUNC date function returns a date with the time portion of the day truncated to the specified format unit - True or False ?
1
The revoke command can be used to remove any privilege - Correct or Incorrect about Privileges ?
Correct
Which attributes indicates that the sequence continues to generate values after reaching either its maximum or minimum value?
CYCLE
GRANT emp, create table TO SCOTT; - Correct or Incorrect ?
Correct
A database object that is defined by a SELECT statement but contains no data is a:
VIEW
Which precedence orders represents the correct precedence of the operators?
+ (unary), - (unary), *, /,+, -, ||, =, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN,IN, NOT, AND, OR
Which statement do you use to remove previously granted privileges?
the REVOKE statement
A ______ value must either be null or match an existing value in the primary key column of the parent table.
foreign key
Subqueries cannot contain ORDER BY clause - Correct or Incorrect ?
Incorrect
Unique indexes are automatically created on columns that have which two types of constraints?
UNIQUE and PRIMARY KEY
Which DML statement updates rows conditionally, based on whether a row already exists or not?
the MERGE statement
What type of index would be most appropriate to use in a data warehousing application on a column where the cardinality may be poor?
a bitmap index
Indexes can be created in any tablespace - Correct or Incorrect about Indexes in Oracle ?
Correct
The REVOKE command can be used to remove privileges but not roles from other users - Correct or Incorrect about ROLES ?
Incorrect
DROP TABLE Order; CREATE TABLE Order; DROP TABLE Order; FLASHBACK TABLE Order TO BEFORE DROP; Which statements is true regarding the above Flashback operation?
It will recover only the second Order table
REGEXP_SUBSTR()
REGEXP_SUBSTR()

REGEXP_SUBSTR extends the functionality of the SUBSTR function by letting you search a string for a regular expression pattern. It is also similar to REGEXP_INSTR, but instead of returning the position of the substring, it returns the substring itself. This function is useful if you need the contents of a match string but not its position in
the source string. The function returns the string as VARCHAR2 or CLOB data in the same character set as source_char.

■ source_char is a character expression that serves as the search value. It is commonly a character column and can be of any of the data types CHAR,VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

■ pattern is the regular expression. It is usually a text literal and can be of any of the data types CHAR, VARCHAR2, NCHAR, or NVARCHAR2. It can contain up to 512 bytes. If the data type of pattern is different from the data type of source_
char, then Oracle Database converts pattern to the data type of source_char.

■ position is a positive integer indicating the character of source_char where Oracle should begin the search. The default is 1, meaning that Oracle begins the search at the first character of source_char.

■ occurrence is a positive integer indicating which occurrence of pattern in source_char Oracle should search for. The default is 1, meaning that Oracle searches for the first occurrence of pattern.
If occurrence is greater than 1, then the database searches for the second occurrence beginning with the first character following the first occurrence of pattern, and so forth. This behavior is different from the SUBSTR function, which begins its search for the second occurrence at the second character of the first occurrence.

■ match_parameter is a text literal that lets you change the default matching behavior of the function. The behavior of this parameter is the same for this function as for REGEXP_COUNT.

■ For a pattern with subexpressions, subexpr is a nonnegative integer from 0 to 9 indicating which subexpression in pattern is to be returned by the function. This parameter has the same semantics that it has for the REGEXP_INSTR function.

The following example examines the string, looking for the first substring bounded by commas. Oracle Database searches for a comma followed by one or more occurrences of non-comma characters followed by a comma. Oracle returns the substring,
including the leading and trailing commas.

SELECT REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA',
',[^,]+,') "REGEXPR_SUBSTR"
FROM DUAL;

REGEXPR_SUBSTR
-----------------
, Redwood Shores,

The following example examines the string, looking for http:// followed by a substring of one or more alphanumeric characters and optionally, a period (.). Oracle searches for a minimum of three and a maximum of four occurrences of this substring
between http:// and either a slash (/) or the end of the string.

SELECT REGEXP_SUBSTR('http://www.example.com/products',
'http://([[:alnum:]]+\.?){3,4}/?') "REGEXP_SUBSTR"
FROM DUAL;

REGEXP_SUBSTR
----------------------
http://www.example.com/
You work as a Database Developer for Biochem Inc. The company uses Oracle as its
database. You create a table named medicine that holds details of all available medicines. The Med_id column is the Primary key of the table.

Which of the following does the Primary key enforce?

A: Table integrity
B: