View the Exhibit and examine the details of PRODUCT_INFORMATION table.
PRODUCT_NAME
CATEGORY_ID
SUPPLIER_ID
Inkjet C/8/HQ
12
102094
Inkjet C/4
12
102090
LaserPro 600/6/BW
12
102087
LaserPro 1200/8/BW
12
102099
Inkjet B/6
12
102096
Industrial 700/ID
12
102086
Industrial 600/DQ
12
102088
Compact 400/LQ
12
102087
Compact 400/DQ
12
102088
HD 12GB /R
13
102090
HD 10GB /I
13
102071
HD 12GB @7200 /SE
13
102057
HD 18.2GB @10000 /E
13
102078
HD 18.2GB @10000 /I
13
102050
HD 18GB /SE
13
102083
HD 6GB /I
13
102072
HD 8.2GB@5400
13
102093
You have the requirement to display PRODUCT_NAME from the table where the
CATEGORY_ID column has values 12 or 13, and the SUPPLIER_ID column has the value
102088. You executed the following SQL statement:
SELECT product_name
FROM product_information
WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088;
Which statement is true regarding the execution of the query?

A.

It would not execute because the same column has been used in both sides of the AND
logical operator to form the condition.

B.

It would not execute because the entire WHERE clause condition is not enclosed within
the parentheses.

C.

It would execute and the output would display the desired result.

D.

It would execute but the output would return no rows.

Which three statements are true regarding the WHERE and HAVING clauses in a SQL
statement? (Choose three.)

A.

WHERE and HAVING clauses cannot be used together in a SQL statement.

B.

The HAVING clause conditions can have aggregate functions.

C.

The HAVING clause conditions can use aliases for the columns.

D.

The WHERE clause is used to exclude rows before the grouping of data.

E.

The HAVING clause is used to exclude one or more aggregated results after grouping
data.

Examine the data in the CUST_NAME column of the CUSTOMERS table.
CUST_NAME
----------
Renske Ladwig
Jason Mallin
Samuel McCain
Allan MCEwen
Irene Mikilineni
Julia Nayer
You need to display customers' second names where the second name starts with "Mc" or
"MC".
Which query gives the required output?

A.

SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE
SUBSTR (cust_name, INSTR (cust_name, ' ')+1)LIKE INITCAP ('MC%');

B.

SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1)) ='Mc';

C.

SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE
INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1))LIKE 'Mc%';

D.

SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE
INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1)) =INITCAP 'MC%';

View the Exhibit and examine the structure in the DEPARTMENTS tables. (Choose two.)

Examine this SQL statement:
SELECT department_id "DEPT_ID", department_name, 'b' FROM
departments
WHERE departments_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 output?

A.

ORDER BY DEPT_NAME;

B.

ORDER BY DEPT_ID;

C.

ORDER BY 'b';

D.

ORDER BY 3;

Which three statements are true regarding subqueries? (Choose three.)

A.

The ORDER BY Clause can be used in a subquery.

B.

A subquery can be used in the FROM clause of a SELECT statement.

C.

If a subquery returns NULL, the main query may still return rows.

D.

A subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING
clause.

E.

Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of
a subquery.

Examine the structure of the MEMBERS table: (Choose the best answer.)

Examine the SQL statement:
SQL > SELECT city, last_name LNAME FROM MEMBERS ORDER BY 1, LNAME DESC;
What would be the result execution?

A.

It displays all cities in descending order, within which the last names are further sorted in
descending order.

B.

It fails because a column alias cannot be used in the ORDER BY clause.

C.

It fails because a column number and a column alias cannot be used together in the
ORDER BY clause.

D.

It displays all cities in ascending order, within which the last names are further sorted in
descending order.

Which two tasks can be performed by using Oracle SQL statements?

A.

changing the password for an existing database user

B.

connecting to a database instance

C.

querying data from tables across databases

D.

starting up a database instance

E.

executing operating system (OS) commands in a session

Examine these SQL statements that are executed in the given order:
CREATE TABLE emp
(emp_no NUMBER (2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,
ename VARCHAR 2 (15),
salary NUMBER (8, 2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp (emp_no));
ALTER TABLE emp
DISABLE CONSTRAINT emp_emp_no_pk CASCADE;
ALTER TABLE emp
ENABLE CONSTRAINT emp_emp_no_pk;
What will be the status of the foreign key EMP_MGR_FK?

A.

It will be enabled and immediate.

B.

It will be enabled and deferred.

C.

It will remain disabled and can be re-enabled manually.

D.

It will remain disabled and can be enabled only by dropping the foreign key constraint
and re-creating it.

Which two statements are true regarding the execution of the correlated subqueries?
(Choose two.)

A.

The nested query executes after the outer query returns the row.

B.

The nested query executes first and then the outer query executes.

C.

The outer query executes only once for the result returned by the inner query.

D.

Each row returned by the outer query is evaluated for the results returned by the inner
query.

You are designing the structure of a table in which two columns have the specifications:
COMPONENT_ID – must be able to contain a maximum of 12 alphanumeric characters
and uniquely identify the row
EXECUTION_DATETIME – contains Century, Year, Month, Day, Hour, Minute, Second to
the maximum precision and is used for calculations and comparisons between
components.
Which two options define the data types that satisfy these requirements most efficiently?

A.

The EXECUTION_DATETIME must be of INTERVAL DAY TO SECOND data type.

B.

The EXECUTION_DATETIME must be of TIMESTAMP data type.

C.

The EXECUTION_DATETIME must be of DATE data type.

D.

The COMPONENT_ID must be of ROWID data type.

E.

The COMPONENT_ID must be of VARCHAR2 data type.

F.

The COMPONENT_ID column must be of CHAR data type.

In which normal form is a table, if it has no multi-valued attributes and no partial
dependencies?

A.

second normal form

B.

first normal form

C.

third normal form

D.

fourth normal form

Which two statements are true regarding the COUNT function?

A.

A SELECT statement using the COUNT function with a DISTINCT keyword cannot have
a WHERE clause.

B.

COUNT (DISTINCT inv_amt) returns the number of rows excluding rows containing
duplicates and NULL values in the INV_AMT column.

C.

COUNT (cust_id) returns the number of rows including rows with duplicate customer IDs
and NULL value in the CUST_ID column.

D.

COUNT (*) returns the number of rows including duplicate rows and rows containing
NULL value in any of the columns.

E.

The COUNT function can be used only for CHAR, VARCHAR2, and NUMBER data
types.