Which two statements are true regarding views? (Choose two.)
A. A subquery that defines a view cannot include the GROUP BY clause.
B. A view that is created with the subquery having the DISTINCT keyword can be updated.
C. A view that is created with the subquery having the pseudo column ROWNUM keyword cannot
be updated.
D. A data manipulation language (DML) operation can be performed on a view that is created
with the subquery having all the NOT NULL columns of a table.
Answer: C, D
Which three statements are true regarding subqueries? (Choose three.)
A. Subqueries can contain GROUP BY and ORDER BY clauses.
B. Main query and subquery can get data from different tables.
C. Main query and subquery must get data from the same tables.
D. Subqueries can contain ORDER BY but not the GROUP BY clause.
E. Only one column or expression can be compared between the main query and subquery.
F. Multiple columns or expressions can be compared between the main query and subquery.
Answer: A, B, F
Which three statements/commands would cause a transaction to end? (Choose three.)
A. COMMIT
B. SELECT
C. CREATE
D. ROLLBACK
E. SAVEPOINT
Answer: A, C, D
Which two statements are true regarding views? (Choose two.)
A. A simple view in which column aliases have been used cannot be updated.
B. Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword.
C. Rows added through a view are deleted from the table automatically when the view is
dropped.
D. The OR REPLACE option is used to change the definition of an existing view without dropping
and re-creating it.
E. The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns
displayed through the view.
Answer: B, D
Examine the structure of the INVOICE table.
Name Null? Type
---------- ------------- ---------------
INV_NO NOT NULL NUMBER(3)
INV_DATE DATE
INV_AMT NUMBER(10,2)
Which two SQL statements would execute successfully? (Choose two.)
A. SELECT inv_no,NVL2(inv_date,'Pending','Incomplete')
FROM invoice;
B. SELECT inv_no,NVL2(inv_amt,inv_date,'Not Available')
FROM invoice;
C. SELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate)
FROM invoice;
D. SELECT inv_no,NVL2(inv_amt,inv_amt*.25,'Not Available')
FROM invoice;
Answer: A, C
Which statement is true regarding synonyms?
A. Synonyms can be created only for a table.
B. Synonyms are used to reference only those tables that are owned by another user.
C. A public synonym and a private synonym can exist with the same name for the same table.
D. The DROP SYNONYM statement removes the synonym, and the table on which the synonym
has been created becomes invalid.
Answer: C
You want to create an ORD_DETAIL table to store details for an order placed having the
following business requirement:
1) The order ID will be unique and cannot have null values.
2) The order date cannot have null values and the default should be the current date.
3) The order amount should not be less than 50.
4) The order status will have values either shipped or not shipped.
5) The order payment mode should be cheque, credit card, or cash on delivery (COD).
Which is the valid DDL statement for creating the ORD_DETAIL table?
A. CREATE TABLE ord_details
(ord_id NUMBER(2) CONSTRAINT ord_id_nn NOT NULL,
ord_date DATE DEFAULT SYSDATE NOT NULL,
ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min
CHECK (ord_amount > 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN ('Shipped', 'Not Shipped')),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN ('Cheque', 'Credit Card',
'Cash On Delivery')));
B. CREATE TABLE ord_details
(ord_id NUMBER(2) CONSTRAINT ord_id_uk UNIQUE NOT NULL,
ord_date DATE DEFAULT SYSDATE NOT NULL,
ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min
CHECK (ord_amount > 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN ('Shipped', 'Not Shipped')),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN ('Cheque', 'Credit Card',
'Cash On Delivery')));
C. CREATE TABLE ord_details
(ord_id NUMBER(2) CONSTRAINT ord_id_pk PRIMARY KEY,
ord_date DATE DEFAULT SYSDATE NOT NULL,
ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min
CHECK (ord_amount >= 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk ord_status VARCHAR2(15)
CONSTRAINT
ord_status_chk
CHECK (ord_status IN ('Shipped', 'Not Shipped')),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk ord_pay_mode VARCHAR2(15)
CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN ('Cheque', 'Credit Card',
'Cash On Delivery')));
D. CREATE TABLE ord_details
(ord_id NUMBER(2),
ord_date DATE NOT NULL DEFAULT SYSDATE,
ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min
CHECK (ord_amount >= 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN ('Shipped', 'Not Shipped')),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN ('Cheque', 'Credit Card',
'Cash On Delivery')));
Answer: C
Evaluate the following two queries:
SQL> SELECT cust_last_name, cust_city
FROM customers
WHERE cust_credit_limit IN (1000, 2000, 3000);
SQL> SELECT cust_last_name, cust_city
FROM customers
WHERE cust_credit_limit = 1000 OR cust_credit_limit = 2000 OR
cust_credit_limit = 3000;
Which statement is true regarding the above two queries?
A. Performance would improve in query 2.
B. Performance would degrade in query 2.
C. There would be no change in performance.
D. Performance would improve in query 2 only if there are null values in the
CUST_CREDIT_LIMIT column.
Answer: C
When does a transaction complete? (Choose all that apply.)
A. when a DELETE statement is executed
B. when a ROLLBACK command is executed
C. when a PL/SQL anonymous block is executed
D. when a data definition language (DDL) statement is executed
E. when a TRUNCATE statement is executed after the pending transaction
Answer: B, D, E
Which two statements are true regarding single row functions? (Choose two.)
A. They accept only a single argument.
B. They can be nested only to two levels.
C. Arguments can only be column values or constants.
D. They always return a single result row for every row of a queried table.
E. They can return a data type value different from the one that is referenced.
Answer: D, E
Which statement is true regarding subqueries?
A. The LIKE operator cannot be used with single-row subqueries.
B. The NOT IN operator is equivalent to IS NULL with single-row subqueries.
C. =ANY and =ALL operators have the same functionality in multiple-row subqueries.
D. The NOT operator can be used with IN, ANY, and ALL operators in multiple-row subqueries.
Answer: D
Which three SQL statements would display the value 1890.55 as $1,890.55?
(Choose three.)
A. SELECT TO_CHAR(1890.55,'$0G000D00')
FROM DUAL;
B. SELECT TO_CHAR(1890.55,'$9,999V99')
FROM DUAL;
C. SELECT TO_CHAR(1890.55,'$99,999D99')
FROM DUAL;
D. SELECT TO_CHAR(1890.55,'$99G999D00')
FROM DUAL;
E. SELECT TO_CHAR(1890.55,'$99G999D99')
FROM DUAL;
Answer: A, D, E
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_no NUMBER(3),
ord_date DATE DEFAULT SYSDATE NOT NULL);
C. CREATE TABLE ord_details
(ord_no NUMBER(2) ,
item_no NUMBER(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_no NUMBER(3),
ord_date DATE DEFAULT SYSDATE NOT NULL,
CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));
Answer: D
Evaluate the following SQL statement:
SQL> SELECT cust_id, cust_last_name "Last Name"
FROM customers
WHERE country_id = 10
UNION
SELECT cust_id CUST_NO, cust_last_name
FROM customers
WHERE country_id = 30;
Which ORDER BY clauses are valid for the above query? (Choose all that apply.)
A. ORDER BY 2,1
B. ORDER BY CUST_NO
C. ORDER BY 2,cust_id
D. ORDER BY "CUST_NO"
E. ORDER BY "Last Name"
Answer: A, C, E
Which is the valid CREATE TABLE statement?
A. CREATE TABLE emp9$# (emp_no NUMBER(4));
B. CREATE TABLE 9emp$# (emp_no NUMBER(4));
C. CREATE TABLE emp*123 (emp_no NUMBER(4));
D. CREATE TABLE emp9$# (emp_no NUMBER(4), date DATE);
Answer: A
The SQL statements executed in a user session are as follows:
SQL> CREATE TABLE product
(pcode NUMBER(2),
pname VARCHAR2(10));
SQL> INSERT INTO product VALUES (1, 'pen');
SQL> INSERT INTO product VALUES (2,'pencil');
SQL> SAVEPOINT a;
SQL> UPDATE product SET pcode = 10 WHERE pcode = 1;
SQL> SAVEPOINT b;
SQL> DELETE FROM product WHERE pcode = 2;
SQL> COMMIT; SQL> DELETE FROM product WHERE pcode=10;
Which two statements describe the consequences of issuing the ROLLBACK TO SAVE POINT a
command in the session? (Choose two.)
A. The rollback generates an error.
B. No SQL statements are rolled back.
C. Only the DELETE statements are rolled back.
D. Only the second DELETE statement is rolled back.
E. Both the DELETE statements and the UPDATE statement are rolled back.
Answer: A, B
Evaluate the following command:
CREATE TABLE employees
(employee_id NUMBER(2) PRIMARY KEY,
last_name VARCHAR2(25) NOT NULL,
department_id NUMBER(2)NOT NULL,
job_id VARCHAR2(8),
salary NUMBER(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 two statements are true regarding the above view? (Choose two.)
A. It allows you to insert rows into the EMPLOYEES table.
B. It allows you to delete details of the existing sales staff from the EMPLOYEES table.
C. It allows you to update job IDs of the existing sales staff to any other job ID in the
EMPLOYEES table.
D. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used
in multitable INSERT statements.
Answer: B, D
The following data exists in the PRODUCTS table:
PROD_ID PROD_LIST_PRICE
------------------------------------------------
123456 152525.99
You issue the following query:
SQL> SELECT RPAD((ROUND(prod_list_price)), 10,'*')
FROM products
WHERE prod_id = 123456;
What would be the outcome?
A. 152526****
B. **152525.99
C. 152525**
D. an error message
Answer: A
Examine the structure and data of the CUST_TRANS table:
CUST_TRANS
Name Null? Type
-------- ------- ---------
CUSTNO NOT NULL CHAR(2)
TRANSDATE DATE
TRANSAMT NUMBER(6,2)
CUSTNO TRANSDATE TRANSAMT
------------ ---------------- ---------------
11 01-JAN-07 1000
22 01-FEB-07 2000
33 01-MAR-07 3000
Dates are stored in the default date format dd-mon-rr in the CUST_TRANS table.
Which three SQL statements would execute success fully? (Choose three.)
A. SELECT transdate + '10' FROM cust_trans;
B. SELECT * FROM cust_trans WHERE transdate = '01-01-07';
C. SELECT transamt FROM cust_trans WHERE custno > '11';
D. SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07';
E. SELECT custno + 'A' FROM cust_trans WHERE transamt > 2000;
Answer: A, C, D
Which three tasks can be performed using SQL functions built into Oracle Database? (Choose
three.)
A. displaying a date in a nondefault format
B. finding the number of characters in an expression
C. substituting a character string in a text expression with a specified string
D. combining more than two columns or expressions into a single column in the output
Answer: A, B, C
Which two statements are true regarding subqueries? (Choose two.)
A. A subquery can retrieve zero or more rows.
B. Only two subqueries can be placed at one level.
C. A subquery can be used only in SQL query statements.
D. A subquery can appear on either side of a comparison operator.
E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT
statement.
Answer: A, D
You need to calculate the number of days from 1st January 2007 till date .
Dates are stored in the default format of dd-mon-rr.
Which two SQL statements would give the required output? (Choose two.)
A. SELECT SYSDATE - '01-JAN-2007' FROM DUAL;
B. SELECT SYSDATE - TO_DATE('01/JANUARY/2007') FROM DUAL;
C. SELECT SYSDATE - TO_DATE('01-JANUARY-2007') FROM DUAL;
D. SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') - '01-JAN-2007' FROM DUAL;
E. SELECT TO_DATE(SYSDATE, 'DD/MONTH/YYYY') - '01/JANUARY/2007' FROM DUAL;
Answer: B, C
You need to generate a list of all customer last names with their credit limits from the
CUSTOMERS table. Those customers who do not have a credit limit should appear last in the
list. Which two queries would achieve the required result? (Choose two.)
A. SELECT cust_last_name, cust_credit_limit
FROM customers
ORDER BY cust_credit_limit DESC;
B. SELECT cust_last_name, cust_credit_limit
FROM customers
ORDER BY cust_credit_limit;
C. SELECT cust_last_name, cust_credit_limit
FROM customers
ORDER BY cust_credit_limit NULLS LAST;
D. SELECT cust_last_name, cust_credit_limit
FROM customers
ORDER BY cust_last_name, cust_credit_limit NULLS LAST;
Answer: B, C
Examine the structure and data in the PRICE_LIST table:
Name Null? Type
-------------- ---------- ----------------
PROD_ID NOT NULL NUMBER(3)
PROD_PRICE VARCHAR2(10)
PROD_ID PROD_PRICE
------------- -------------------
100 $234.55
101 $6,509.75
102 $1,234
`in the same format as the PROD_PRICE.
Which SQL statement would give the required result?
A. SELECT TO_CHAR(prod_price* .25,'$99,999.99') FROM PRICE_LIST;
B. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25,'$99,999.00') FROM PRICE_LIST;
C. SELECT TO_CHAR(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM
PRICE_LIST;
D. SELECT TO_NUMBER(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM
PRICE_LIST;
Answer: C
Which statement is true regarding the COALESCE function?
A. It can have a maximum of five expressions in a list.
B. It returns the highest NOT NULL value in the list for all rows.
C. It requires that all expressions in the list must be of the same data type.
D. It requires that at least one of the expressions in the list must have a NOT NULL value.
Answer: C
Evaluate the following CREATE TABLE commands:
CREATE TABLE orders
(ord_no NUMBER(2) CONSTRAINT ord_pk PRIMARY KEY,
ord_date DATE,
cust_id NUMBER(4));
CREATE TABLE ord_items
(ord_no NUMBER(2),
item_no NUMBER(3),
qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200),
expiry_date date CHECK (expiry_date > SYSDATE),
CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no),
CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no));
The above command fails when executed. What could be the reason?
A. SYSDATE cannot be used with the CHECK constraint.
B. The BETWEEN clause cannot be used for the CHECK constraint.
C. The CHECK constraint cannot be placed on columns having the DATE data type.
D. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is
also the FOREIGN KEY.
Answer: A
You created an ORDERS table with the following description:
Name Null Type
----------------- ------------- --------------
ORD_ID NOT NULL NUMBER(2)
CUST_ID NOT NULL NUMBER(3)
ORD_DATE NOT NULL DATE
ORD_AMOUNT NOT NULL NUMBER (10,2)
You inserted some rows in the table. After some time, you want to alter the table by creating the
PRIMARY KEY constraint on the ORD_ID column.
Which statement is true in this scenario?
A. You cannot have two constraints on one column.
B. You cannot add a primary key constraint if data exists in the column.
C. The primary key constraint can be created only at the time of table creation.
D. You can add the primary key constraint even if data exists, provided that there are no duplicate
values.
Answer: D
You need to display the date 11-oct-2007 in words as 'Eleventh of October, Two Thousand
Seven'. Which SQL statement would give the required result?
A. SELECT TO_CHAR('11-oct-2007', 'fmDdspth "of" Month, Year')
FROM DUAL;
B. SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdspth of month, year')
FROM DUAL;
C. SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdthsp "of" Month, Year')
FROM DUAL;
D. SELECT TO_DATE(TO_CHAR('11-oct-2007','fmDdspth ''of'' Month, Year'))
FROM DUAL;
Answer: C
You need to create a table for a banking application. One of the columns in the table has the
following requirements:
1) You want a column in the table to store the duration of the credit period.
2) The data in the column should be stored in a format such that it can be easily added and
subtracted with DATE data type without using conversion functions.
3) The maximum period of the credit provision in the application is 30 days.
4) The interest has to be calculated for the number of days an individual has taken a credit for.
Which data type would you use for such a column in the table?
A. DATE
B. NUMBER
C. TIMESTAMP
D. INTERVAL DAY TO SECOND
E. INTERVAL YEAR TO MONTH
Answer: D
Which statements are correct regarding indexes? (Choose all that apply.)
A. When a table is dropped, the corresponding indexes are automatically dropped.
B. A FOREIGN KEY constraint on a column in a table automatically creates a nonunique index.
C. A nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a
unique index.
D. For each data manipulation language (DML) operation performed, the corresponding indexes
are automatically updated.
Answer: A, C, D
Which two statements are true about sequences created in a single instance database? (Choose
two.)
A. The numbers generated by a sequence can be used only for one table.
B. DELETE
C. CURRVAL is used to refer to the last sequence number that has been generated.
D. When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit
by using the ALTER SEQUENCE statement.
E. When a database instance shuts down abnormally, the sequence numbers that have been
cached but not used would be available once again when the database instance is restarted.
Answer: C, D
Which two statements are true regarding single row functions? (Choose two.)
A. They accept only a single argument.
B. They can be nested only to two levels.
C. Arguments can only be column values or constants.
D. They always return a single result row for every row of a queried table.
E. They can return a data type value different from the one that is referenced.
Answer: D, E
Which two statements are true regarding constraints? (Choose two.)
A. A foreign key cannot contain NULL values.
B. A column with the UNIQUE constraint can contain NULL values.
C. A constraint is enforced only for the INSERT operation on a table.
D. A constraint can be disabled even if the constraint column contains data.
E. All constraints can be defined at the column level as well as the table level.
Answer: B, D
Evaluate the following query:
SQL> SELECT promo_name || q'{'s start date was }' || promo_begin_date
AS "Promotion Launches"
FROM promotions;
What would be the outcome of the above query?
A. It produces an error because flower braces have been used.
B. It produces an error because the data types are not matching.
C. It executes successfully and introduces an 's at the end of each promo_name in the output.
D. It executes successfully and displays the literal " {'s start date was } " for each row in the
output.
Answer: C
Evaluate the following query:
SELECT INTERVAL '300' MONTH,
INTERVAL '54-2' YEAR TO MONTH,
INTERVAL '11:12:10.1234567' HOUR TO SECOND
FROM dual;
What is the correct output of the above query?
A. +25-00 , +54-02, +00 11:12:10.123457
B. +00-300, +54-02, +00 11:12:10.123457
C. +25-00 , +00-650, +00 11:12:10.123457
D. +00-300 , +00-650, +00 11:12:10.123457
Answer: A
You issued the following command to drop the PRODUCTS table:
SQL> DROP TABLE products;
What is the implication of this command? (Choose all that apply.)
A. All data along with the table structure is deleted.
B. The pending transaction in the session is committed.
C. All indexes on the table will remain but they are invalidated.
D. All views and synonyms will remain but they are invalidated.
E. All data in the table are deleted but the table structure will remain.
Answer: A, B, D
Evaluate the following SQL commands:
SQL>CREATE SEQUENCE ord_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCYCLE;
SQL>CREATE TABLE ord_items
(ord_no NUMBER(4)DEFAULT ord_seq.NEXTVAL NOT NULL,
item_no NUMBER(3), qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200),
expiry_date date CHECK (expiry_date > SYSDATE),
CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no),
CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no));
The command to create a table fails. Identify the reason for the SQL statement failure? (Choose
all that apply.)
A. You cannot use SYSDATE in the condition of a CHECK constraint.
B. You cannot use the BETWEEN clause in the condition of a CHECK constraint.
C. You cannot use the NEXTVAL sequence value as a DEFAULT value for a column.
D. You cannot use ORD_NO and ITEM_NO columns as a composite primary key because
ORD_NO is also the FOREIGN KEY.
Answer: A, C
Which arithmetic operations can be performed on a column by using a SQL function that is built
into Oracle database? (Choose three.)
A. addition
B. subtraction
C. raising to a power
D. finding the quotient
E. finding the lowest value
Answer: A, C, E
You need to create a table with the following column specifications:
1. Employee ID (numeric data type) for each employee
2. Employee Name (character data type) that stores the employee name
3. Hire date, which stores the date of joining the organization for each employee
4. Status (character data type), that contains the value 'ACTIVE' if no data is entered
5. Resume (character large object [CLOB] data type), which contains the resume submitted by
the employee
Which is the correct syntax to create this table?
A. CREATE TABLE EMP_1
(emp_id NUMBER(4),
emp_name VARCHAR2(25),
start_date DATE,
e_status VARCHAR2(10) DEFAULT 'ACTIVE',
resume CLOB(200));
B. CREATE TABLE 1_EMP
(emp_id NUMBER(4),
emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10) DEFAULT 'ACTIVE',
resume CLOB);
C. CREATE TABLE EMP_1
(emp_id NUMBER(4),
emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10) DEFAULT "ACTIVE",
resume CLOB);
D. CREATE TABLE EMP_1
(emp_id NUMBER,
emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10) DEFAULT 'ACTIVE',
resume CLOB);
Answer: D
Which statement is true regarding the default behavior of the ORDER BY clause?
A. In a character sort, the values are case-sensitive.
B. NULL values are not considered at all by the sort operation.
C. Only those columns that are specified in the SELECT list can be used in the ORDER BY
clause.
D. Numeric values are displayed from the maximum to the minimum value if they have decimal
positions.
Answer: A
You need to extract details of those products in the SALES table where the PROD_ID column
contains the string '_D123'. Which WHERE clause could be used in the SELECT statement to get
the required output?
A. WHERE prod_id LIKE '%_D123%' ESCAPE '_'
B. WHERE prod_id LIKE '%\_D123%' ESCAPE '\'
C. WHERE prod_id LIKE '%_D123%' ESCAPE '%_'
D. WHERE prod_id LIKE '%\_D123%' ESCAPE '\_'
Answer: B
Evaluate the following DELETE statement:
DELETE FROM sales;
There are no other uncommitted transactions on the SALES table.
Which statement is true about the DELETE statement?
A. It would not remove the rows if the table has a primary key.
B. It removes all the rows as well as the structure of the table.
C. It removes all the rows in the table and deleted rows can be rolled back.
D. It removes all the rows in the table and deleted rows cannot be rolled back.
Answer: C
Which three statements are true about multiple-row subqueries? (Choose three.)
A. They can contain a subquery within a subquery.
B. They can return multiple columns as well as rows.
C. They cannot contain a subquery within a subquery.
D. They can return only one column but multiple rows.
E. They can contain group functions and GROUP BY and HAVING clauses.
F. They can contain group functions and the GROUP BY clause, but not the HAVING clause.
Answer: A, B, E
Examine the description of the EMP_DETAILS table given below:
NAME NULL TYPE
----------------- ----------------- ---------------------------
EMP_ID NOT NULL NUMBER
EMP_NAME NOT NULL VARCHAR2 (40)
EMP_IMAGE LONG
Which two statements are true regarding SQL statements that can be executed on the
EMP_DETAIL table? (Choose two.)
A. An EMP_IMAGE column can be included in the GROUP BY clause.
B. An EMP_IMAGE column cannot be included in the ORDER BY clause.
C. You cannot add a new column to the table with LONG as the data type.
D. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column.
Answer: B, C
Which two statements are true regarding the ORDER BY clause? (Choose two.)
A. It is executed first in the query execution.
B. It must be the last clause in the SELECT statement.
C. It cannot be used in a SELECT statement containing a HAVING clause.
D. You cannot specify a column name followed by an expression in this clause.
E. You can specify a combination of numeric positions and column names in this clause.
Answer: B, E
Evaluate the following SQL statement:
SQL> SELECT promo_id, promo_category
FROM promotions
WHERE promo_category = 'Internet' ORDER BY 2 DESC
UNION
SELECT promo_id, promo_category
FROM promotions
WHERE promo_category = 'TV'
UNION
SELECT promo_id, promo_category
FROM promotions
WHERE promo_category ='Radio';
Which statement is true regarding the outcome of the above query?
A. It executes successfully and displays rows in the descending order of PROMO_CATEGORY.
B. It produces an error because positional notation cannot be used in the ORDER BY clause with
SET operators.
C. It executes successfully but ignores the ORDER BY clause because it is not located at the end
of the compound statement.
D. It produces an error because the ORDER BY clause should appear only at the end of a
compound query-that is, with the last SELECT statement.
Answer: D
The CUSTOMERS table has the following structure:
Name Null? Type
------- ------------- -------------
CUST_ID NOT NULL NUMBER
CUST_FIRST_NAME NOT NULL VARCHAR2(20)
CUST_LAST_NAME NOT NULL VARCHAR2(30)
CUST_INCOME_LEVEL VARCHAR2(30)
CUST_CREDIT_LIMIT NUMBER
You need to write a query that does the following tasks:
1. Display the first name and tax amount of the customers. Tax is 5% of their credit limit.
2. Only those customers whose income level has a value should be considered.
3. Customers whose tax amount is null should not be considered.
Which statement accomplishes all the required tasks?
A. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT
FROM customers
WHERE cust_income_level IS NOT NULL AND
tax_amount IS NOT NULL;
B. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT
FROM customers
WHERE cust_income_level IS NOT NULL AND
cust_credit_limit IS NOT NULL;
C. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT
FROM customers
WHERE cust_income_level <> NULL AND
tax_amount <> NULL;
D. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT
FROM customers
WHERE (cust_income_level,tax_amount) IS NOT NULL;
Answer: B
Which two statements are true regarding the USING clause in table joins?
(Choose two.)
A. It can be used to join a maximum of three tables.
B. It can be used to restrict the number of columns used in a NATURAL join.
C. It can be used to access data from tables through equijoins as well as nonequijoins.
D. It can be used to join tables that have columns with the same name and compatible data types.
Answer: B, D
Which statement is true regarding the INTERSECT operator?
A. It ignores NULL values.
B. Reversing the order of the intersected tables alters the result.
C. The names of columns in all SELECT statements must be identical.
D. The number of columns and data types must be identical for all SELECT statements in the
query.
Answer: D
Which three statements are true regarding the data types in Oracle Database 0g/11g? (Choose
three.)
A. Only one LONG column can be used per table.
B. A TIMESTAMP data type column stores only time values with fractional seconds.
C. The BLOB data type column is used to store binary data in an operating system file.
D. The minimum column width that can be specified for a VARCHAR2 data type column is one.
E. The value for a CHAR data type column is blank-padded to the maximum defined column
width.
Answer: A, D, E
Examine the structure of the PROGRAMS table:
Name Null? Type
---------- ------------- ---------------
PROG_ID NOT NULL NUMBER(3)
PROG_COST NUMBER(8,2)
START_DATE NOT NULL DATE
END_DATE DATE
Which two SQL statements would execute successfully? (Choose two.)
A. SELECT NVL(ADD_MONTHS(END_DATE,1),SYSDATE) FROM programs;
B. SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE)) FROM programs;
C. SELECT NVL(MONTHS_BETWEEN(start_date,end_date),'Ongoing') FROM programs;
D. SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),'Ongoing') FROM
programs;
Answer: A, D
You are currently located in Singapore and have connected to a remote database in Chicago.
You issue the following command:
SQL> SELECT ROUND(SYSDATE-promo_begin_date,0)
FROM promotions
WHERE (SYSDATE-promo_begin_date)/365 > 2;
PROMOTIONS is the public synonym for the public database link for the PROMOTIONS table.
What is the outcome?
A. an error because the ROUND function specified is invalid
B. an error because the WHERE condition specified is invalid
C. number of days since the promo started based on the current Chicago date and time
D. number of days since the promo started based on the current Singapore date and time
Answer: C
You need to display the first names of all customers from the CUSTOMERS table that contain the
character 'e' and have the character 'a' in the second last position. Which query would give the
required output?
A. SELECT cus t_first_name
FROM customers
WHERE INSTR(cust_first_name, 'e')<>0 AND
SUBSTR(cust_first_name, -2, 1)='a';
B. SELECT cus t_first_name
FROM customers
WHERE INSTR(cust_first_name, 'e')<>'' AND
SUBSTR(cust_first_name, -2, 1)='a';
C. SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, 'e')IS NOT NULL AND
SUBSTR(cust_first_name, 1,-2)='a';
D. SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, 'e')<>0 AND
SUBSTR(cust_first_name, LENGTH(cust_first_name),-2)='a';
Answer: A
Evaluate the following query:
SQL> SELECT TRUNC(ROUND(156.00,-1),-1)
FROM DUAL;
What would be the outcome?
A. 16
B. 100
C. 160
D. 200
E. 150
Answer: C
Which two statements are true regarding the COUNT function? (Choose two.)
A. The COUNT function can be used only for CHAR, VARCHAR2, and NUMBER data types.
B. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL
value in any of the columns.
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(DISTINCT inv_amt)returns the number of rows excluding rows containing duplicates
and NULL values in the INV_AMT column.
E. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a
WHERE clause.
Answer: B, D
Which two statements are true regarding the USING and ON clauses in table joins? (Choose
two.)
A. Both USING and ON clauses can be used for equijoins and nonequijoins.
B. A maximum of one pair of columns can be joined between two tables using the ON clause.
C. The ON clause can be used to join tables on columns that have different names but
compatible data types.
D. The WHERE clause can be used to apply additional conditions in SELECT statements
containing the ON or the USING clause.
Answer: C, D
Where can subqueries be used? (Choose all that apply.)
A. field names in the SELECT statement
B. the FROM clause in the SELECT statement
C. the HAVING clause in the SELECT statement
D. the GROUP BY clause in the SELECT statement
E. the WHERE clause in only the SELECT statement
F. the WHERE clause in SELECT as well as all DML statements
Answer: A, B, C, F
Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit
amount in each income level. The report should NOT show any repeated credit amounts in each
income level. Which query would give the required result?
A. SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50
AS "50% Credit Limit" FROM customers;
B. SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50
AS "50% Credit Limit"
FROM customers;
C. SELECT DISTINCT cust_income_level || ' ' || cust_credit_limit * 0.50
AS "50% Credit Limit"
FROM customers;
D. SELECT cust_income_level ||' '|| cust_credit_limit * 0.50 AS "50% Credit Limit"
FROM customers;
Answer: C
Which statement is true regarding the UNION operator?
A. By default, the output is not sorted.
B. NULL values are not ignored during duplicate checking.
C. Names of all columns must be identical across all SELECT statements.
D. The number of columns selected in all SELECT statements need not be the same.
Answer: B
Which two statements are true regarding working with dates? (Choose two.)
A. The default internal storage of dates is in the numeric format.
B. The default internal storage of dates is in the character format.
C. The RR date format automatically calculates the century from the SYSDATE function and does
not allow the user to enter the century.
D. The RR date format automatically calculates the century from the SYSDATE function but
allows the user to enter the century if required.
Answer: A, D
The ORDERS table belongs to the user OE. OE has granted the SELECT privilege on the
ORDERS table to the user HR. Which statement would create a synonym ORD so that HR can
execute the following query successfully?
SELECT * FROM ord;
A. CREATE SYNONYM ord FOR orders; This command is issued by OE.
B. CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by OE.
C. CREATE SYNONYM ord FOR oe.orders; This command is issued by the database
administrator.
D. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database
administrator.
Answer: D
Evaluate the following SQL statement:
SQL> SELECT cust_id, cust_last_name
FROM customers
WHERE cust_credit_limit IN
(select cust_credit_limit
FROM customers
WHERE cust_city ='Singapore');
Which statement is true regarding the above query if one of the values generated by the
subquery is NULL?
A. It produces an error.
B. It executes but returns no rows.
C. It generates output for NULL as well as the other values produced by the subquery.
D. It ignores the NULL value and generates output for the other values produced by the
subquery.
Answer: C
The PART_CODE column in the SPARES table contains the following list of values:
PART_CODE
-----------------
A%_WQ123
A%BWQ123
AB_WQ123
Evaluate the following query:
SQL> SELECT part_code
FROM spares
WHERE part_code LIKE '%\%_WQ12%' ESCAPE '\';
Which statement is true regarding the outcome of the above query?
A. It produces an error.
B. It displays all values.
C. It displays only the values A%_WQ123 and AB_WQ123.
D. It displays only the values A%_WQ123 and A%BWQ123.
E. It displays only the values A%BWQ123 and AB_WQ123.
Answer: D
Oracle Database: SQL Fundamentals -- 1Z0-051
