Term
| 16. Examine the structure of the BOOKS_TRANSACTIONS table: You want to display the member IDs, due date, and late fee as $2 for all transactions. Which SQL statement must you execute? a) select member_id as member_id, due_date as due_date, $2 as late_fee from books_transactions; b) select member_id 'member_id', due_date 'due_date', '$2 as late_fee' from books_transactions; c) select member_id as "member_id", due_date as "due_date", '$2' as "late_fee" from books_transactions; d) select member_id as "member_id", due_date as "due_date", $2 as "late_fee" from books_transactions; |
|
Definition
| c) select member_id as "member_id", due_date as "due_date", '$2' as "late_fee" from books_transactions; |
|
|
Term
17. View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables. 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; |
|
Definition
| B. DELETE orders WHERE order_total < 1000; |
|
|
Term
18. View the Exhibit and examine the data in ORDERS and ORDER_ITEMS tables.
You need to create a view that displays the ORDER ID, ORDER_DATE, and the total number of items in each order.
Which CREATE VIEW statement would create the view successfully?
A. CREATE OR REPLACE VIEW ord_vu (order_id, order_date) AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) "NO OF ITEMS"
FROM orders o JOIN order_items i ON (o.order_id = i.order_id)
GROUP BY o.order_id, o.order_date;
B. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON (o.order_id = i.order_id)
GROUP BY o.order_id, o.order_date;
C. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id, o.order_date;
D. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) ll’ NO OF ITEMS' FROM orders o JOIN order_items i
ON (o.order_id = i.order_id) GROUP BY o.order_id, o.order_date WITH CHECK OPTION; |
|
Definition
B. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON (o.order_id = i.order_id)
GROUP BY o.order_id, o.order_date; |
|
|
Term
19. Examine the structure of the members table:
NAME____________________NOT NULL____TYPE
-------------------------------- ------------- -----------------
MEMBER_ID_____________ NOT NULL_____VARCHAR2 (6)
FIRST_NAME____________________________VARCHAR2 (50)
LAST_NAME______________NOT NULL____ VARCHAR2 (50)
ADDRESS_______________________________VARCHAR2 (50)
CITY____________________________________VARCHAR2 (25)
STATE__________________________________VARCHAR2 (3)
You want to display details of all members who reside in states starting with the letter A followed by exactly one character.
Which SQL statement must you execute?
A. SELECT * FROM MEMBERS WHERE state LIKE '%A_*;
B. SELECT * FROM MEMBERS WHERE state LIKE 'A_*;
C. SELECT * FROM MEMBERS WHERE state LIKE 'A_%';
D. SELECT * FROM MEMBERS WHERE state LIKE 'A%'; |
|
Definition
| C. SELECT * FROM MEMBERS WHERE state LIKE 'A_%'; |
|
|
Term
20. You need to create a table for banking application with the following considerations:
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
3. Data type data without using the conversion functions
4. The max period of the credit provision in the application is 30 days
5. The interest has to be calculated for the number of days an individual has taken credit for.
Which data type would you use for such a column in the table?
A. INTERVAL YEAR TO MONTH
B. INTERVAL DAY TO SECOND
C. TIMESTAMP WITH LOCAL TIME ZONE
D. TIMESTAMP WITH TIME ZONE |
|
Definition
| B. INTERVAL DAY TO SECOND |
|
|
Term
21. Which three statements are true regarding the usage of the WITH clause in complex correlated Subqueries?
A. It can be used with the SELECT clause
B. The WITH clause can hold more than one query
C. If the query block name and the table name were the same, then the table name would take precedence
D. The query name in the WITH clause is visible to the other query blocks in the WITH clause as well as to the main query block |
|
Definition
A. It can be used with the SELECT clause
B. The WITH clause can hold more than one query
D. The query name in the WITH clause is visible to the other query blocks in the WITH clause as well as to the main query block |
|
|
Term
22. 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 > 20000 THEN
INTO large_orders
SELECT order_id, order_total, sales_rep_id, 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 first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses
C. They are evaluated by 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 |
|
Definition
| A. They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause. |
|
|
Term
23. View the Exhibit and examine the details of the PRODUCT_INFORMATION table.
Evaluate the following SQL statement:
SELECT TO_CHAR(list_price,’$9,999′)
FROM product_information;
Which two statements would be true regarding the output for this SQL statement? (Choose two.)
A. The LIST_PRICE column having value 1123.90 would be displayed as $1,124.
B. The LIST_PRICE column having value 1123.90 would be displayed as $1,123.
C. The LIST_PRICE column having value 11235.90 would be displayed as $1,123.
D. The LIST_PRICE column having value 11235.90 would be displayed as #######. |
|
Definition
A. The LIST_PRICE column having value 1123.90 would be displayed as $1,124.
D. The LIST_PRICE column having value 11235.90 would be displayed as #######. |
|
|
Term
24. Which three statements are true regarding single-row functions?
(Choose three.)
A. They can accept only one argument.
B. They can be nested up to only two levels.
C. They can return multiple values of more than one data type.
D. They can be used in SELECT, WHERE, and ORDER BY clauses.
E. They can modify the data type of the argument that is referenced.
F. They can accept a column name, expression, variable name, or a user-supplied constant as arguments. |
|
Definition
D. They can be used in SELECT, WHERE, and ORDER BY clauses.
E. They can modify the data type of the argument that is referenced.
F. They can accept a column name, expression, variable name, or a user-supplied constant as arguments. |
|
|
Term
25. Which three statements indicate the end of a transaction? (Choose three.)
A. after a COMMIT is issued
B. after a ROLLBACK is issued
C. after a SAVEPOINT is issued
D. after a SELECT statement is issued
E. after a CREATE statement is issued |
|
Definition
A. after a COMMIT is issued
B. after a ROLLBACK is issued
E. after a CREATE statement is issued |
|
|
Term
26. View the Exhibit and examine the description of the EMPLOYEES table.
You executed the following SQL statement:
SELECT first_name, department_id, salary
FROM employees
ORDER BY department_id, first_name, salary desc;
Which two statements are true regarding the output of the above query? (Choose two.)
A. The values in all the columns would be sorted in the descending order.
B. The values in the SALARY column would be sorted in descending order for all the employees having the same value in the DEPARTMENT_ID column.
C. The values in the FIRST_NAME column would be sorted in ascending order for all the
employees having the same value in the DEPARTMENT_ID column.
D. The values in the FIRST_NAME column would be sorted in the descending order for all the employees having the same value in the DEPARTMENT_ID column.
E. The values in the SALARY column would be sorted in descending order for all the employees having the same value in the DEPARTMENT_ID and FIRST_NAME column. |
|
Definition
C. The values in the FIRST_NAME column would be sorted in ascending order for all the
employees having the same value in the DEPARTMENT_ID column.
E. The values in the SALARY column would be sorted in descending order for all the employees having the same value in the DEPARTMENT_ID and FIRST_NAME column. |
|
|
Term
27. View the Exhibit and examine the data in the PRODUCTS table.
Which statement would add a column called PRICE, which cannot contain NULL?
A. ALTER TABLE products
ADD price NUMBER (8, 2) NOT NULL;
B. ALTER TABLE products
ADD price NUMBER (8, 2) DEFAULT NOT NULL;
C. ALTER TABLE products
ADD price NUMBER (8, 2) DEFAULT 0 NOT NULL;
D. ALTER TABLE products
ADD price NUMBER (8, 2) DEFAULT CONSTRAINT p_nn NOT NULL; |
|
Definition
C. ALTER TABLE products
ADD price NUMBER (8, 2) DEFAULT 0 NOT NULL; |
|
|
Term
28. Which statement correctly differentiates a system privilege from an object privilege?
A. System privileges can be granted only by the DBA whereas object privileges can be granted by DBAs or the owner of the object.
B. System privileges give the rights to only create user schemas whereas object privileges give rights to manipulate objects in a schema.
C. Users require system privileges to gain access to the database whereas they require object privileges to create objects in the database.
D. A system privilege is the right to perform specific activities in a database whereas an object privilege is a right to perform activities on a specific object in the database. |
|
Definition
| D. A system privilege is the right to perform specific activities in a database whereas an object privilege is a right to perform activities on a specific object in the database. |
|
|
Term
29. View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. There is only one customer with the cus_last_name column having value Roberts.
Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?
A.
INSERT INTO orders VALUES (1,’10-mar-2007′, ‘direct’, (SELECT customer_id FROM
customers WHERE cust_last_name=’Roberts’ AND credit_limit=600), 1000);
B.
INSERT INTO orders (order_id, order_date, order_mode, (SELECT customer_id FROM
customers WHERE cust_last_name=’Roberts’ AND credit_limit=600) .order_total)
VALUES (1, ’10-mar-2007′, ‘direct’, &&customer_id, 1000);
C.
INSERT INTO orders (order_id, order_date, order_mode, (SELECT customer_id FROM
customers WHERE cust_last_name=’Roberts’ AND credit _limit=600) .order_total)
VALUES (1, ’10-mar-2007′, ‘direct’, &customer_id, 1000);
D.
INSERT INTO (SELECT o.order_id, o.order_date, o.order_mode, c.customer_id,
o.order_total FROM orders o, customers c WHERE o.customer_id = c.customer_id AND
c.cust_last_name=’Roberts’ AND c. Credit_limit=600) VALUES (1,’10-mar-2007′,
‘direct’, (SELECT customer_id FROM customers WHERE cust_last_name=’Roberts’ AND
Credit_limit=600), 1000); |
|
Definition
A.
INSERT INTO orders VALUES (1,’10-mar-2007′, ‘direct’, (SELECT customer_id FROM
customers WHERE cust_last_name=’Roberts’ AND credit_limit=600), 1000); |
|
|
Term
30. A subquery is called a single-row subquery when ____.
A. The inner query returns a single value to the main query
B. The inner query uses an aggregate function and returns one or more values
C. There is only one inner query in the main query and the inner query returns one or more values
D. The inner query returns one or more values and the main query returns a single value as output |
|
Definition
| A. The inner query returns a single value to the main query |
|
|
Term
31. Which three statements are true regarding subqueries? (Choose three.)
A. The ORDER BY clause can be used in the subquery.
B. A subquery can be used in the FROM clause of a SELECT statement.
C. If the subquery returns NULL; the main query may still return result rows.
D. A subquery can be placed in a WHERE clause, 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. |
|
Definition
A. The ORDER BY clause can be used in the subquery.
B. A subquery can be used in the FROM clause of a SELECT statement.
C. If the subquery returns NULL; the main query may still return result rows. |
|
|
Term
32. View the Exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables.
Evaluate the following MERGE statement:
MERGE INTO orders_master o
USING monthly_orders m
ON (o.order_id = m.order_id)
WHEN MATCHED THEN
UPDATE SET o.order_total = m.order_total
DELETE WHERE (m.order_total IS NULL)
WHEN NOT MATCHED THEN
INSERT VALUES (m.order_id, m.order_total);
What would be the outcome of the above statement?
A. The ORDERS_MASTER table would contain the ORDER IDs 1 and 2.
B. The ORDERS_MASTER table would contain the ORDER IDs 1, 2 and 3.
C. The ORDERS_MASTER table would contain the ORDER IDs 1, 2 and 4.
D. The ORDERS MASTER table would contain the ORDER IDs 1, 2, 3 and 4. |
|
Definition
| C. The ORDERS_MASTER table would contain the ORDER IDs 1, 2 and 4. |
|
|
Term
33. 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,
ename VARCHAR2(15),
salary NUMBER(8,2),
mgr_no NUMBER(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. |
|
Definition
| C. It would remain disabled and has to be enabled manually using the ALTER TABLE command. |
|
|
Term
34. Evaluate the following SELECT statement and view the Exhibit to examine its output:
SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status FROM user_constraints
WHERE table_name = ORDERS
Which two statements are true about the output? (Choose two.)
A. In the second column, ‘C’ indicates a check constraint.
B. The STATUS column indicates whether the table is currently in use.
C. The R_CONSTRAINT_NAME column gives the alternative name for the constraint.
D. The column DELETE_RULE decides the state of the related rows in the child table when the corresponding row is deleted from the parent table. |
|
Definition
A. In the second column, ‘C’ indicates a check constraint.
D. The column DELETE_RULE decides the state of the related rows in the child table when the corresponding row is deleted from the parent table. |
|
|
Term
35. Which statements are true? (Choose all that apply.)
A. The data dictionary is created and maintained by the database administrator.
B. The data dictionary views can consist of joins of dictionary base tables and user-defined tables.
C. The usernames of all the users including the database administrators are stored in the data dictionary.
D. The USER_CONS_COLUMNS view should be queried to find the names of the columns to which a constraint applies.
E. Both USER_OBJECTS and CAT views provide the same information about all the objects that are owned by the user.
F. Views with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary |
|
Definition
C. The usernames of all the users including the database administrators are stored in the data dictionary.
D. The USER_CONS_COLUMNS view should be queried to find the names of the columns to which a constraint applies.
F. Views with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary |
|
|
Term
36. 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; |
|
Definition
A. ORDER BY 3;
C. ORDER BY DEPT_ID; |
|
|
Term
37. View the exhibits and examine the structures of the COSTS and PROMOTIONS tables.
Evaluate the following SQL statement:
SQL> SELECT prod_id FROM costs
WHERE promo_id IN (SELECT promo_id FROM promotions
WHERE promo_cost < ALL (SELECT MAX (promo_cost)
FROM promotions
GROUP BY (promo_end_date, promo_begin_date)));
What would be the outcome of the above SQL statement?
A. It displays prod IDs in the promo with the lowest cost.
B. It displays prod IDs in the promos with the lowest cost in the same time interval.
C. It displays prod IDs in the promos with the highest cost in the same time interval.
D. It displays prod IDs in the promos which cost less than the highest cost in the same time interval. |
|
Definition
| D. It displays prod IDs in the promos which cost less than the highest cost in the same time interval. |
|
|
Term
38. Examine the following query:
SQL> SELECT prod_id, amount_sold
FROM sales ORDER BY amount_sold FETCH FIRST 5 PERCENT ROWS ONLY;
What is the output of this query?
A. It displays 5 percent of the products with the highest amount sold.
B. It displays the first 5 percent of the rows from the SALES table.
C. It displays 5 percent of the products with the lowest amount sold.
D. It results in an error because the ORDER BY clause should be the last clause. |
|
Definition
| C. It displays 5 percent of the products with the lowest amount sold. |
|
|
Term
39. Which two statements are true regarding multiple-row subqueries? (Choose two.)
A. They can contain group functions.
B. They always contain a subquery within a subquery.
C. They use the < ALL operator to imply less than the maximum.
D. They can be used to retrieve multiple rows from a single table only.
E. They should not be used with the NOT IN operator in the main query if NULL is likely to be a part of the result of the subquery |
|
Definition
A. They can contain group functions.
E. They should not be used with the NOT IN operator in the main query if NULL is likely to be a part of the result of the subquery |
|
|
Term
40. Examine the structure of the MEMBERS table:
Name___________________Null?________Type
————————————————————————————————————————
MEMBER_ID______________NOT_NULL___VARCHAR2_(6)
FIRST_NAME___________________________VARCHAR2_(50)
LAST_NAME______________NOT_NULL___VARCHAR2_(50)
ADDRESS______________________________VARCHAR2_(50)
You execute the SQL statement:
SQL > SELECT member_id, ' ' , first_name, ' ' , last_name "ID FIRSTNAME LASTNAME" FROM members;
What is the outcome?
A. It fails because the alias name specified after the column names is invalid.
B. It fails because the space specified in single quotation marks after the first two column names is invalid.
C. It executes successfully and displays the column details in a single column with only the alias column heading.
D. It executes successfully and displays the column details in three separate columns and replaces only the last column heading with the alias. |
|
Definition
| D. It executes successfully and displays the column details in three separate columns and replaces only the last column heading with the alias. |
|
|
Term
41. 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; |
|
Definition
A. SELECT inv_no, NVL2 (inv_date, ’Pending’, ’Incomplete’)
FROM invoice;
C. SELECT inv_no, NVL2 (inv_date, sysdate-inv_date, sysdate)
FROM invoice; |
|
|
Term
42. View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables.
The CUSTOMERS table contains the current location of all currently active customers. The CUST_HISTORY table stores historical details relating to any changes in the location of all current as well as previous customers who are no longer active with the company. You need to find those customers who have never changed their address.
Which SET operator would you use to get the required output?
A. INTERSECT
B. UNION ALL
C. MINUS
D. UNION |
|
Definition
|
|
Term
43. 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 execute successfully to produce the required output.
B. The statement would not execute because inline views and outer joins cannot be used together.
C. The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.
D. The statement would not execute because the GROUP BY clause cannot be used in the inline view. |
|
Definition
| A. The statement would execute successfully to produce the required output. |
|
|
Term
44. Which two statements are true about Data Manipulation Language (DML) statements?
A. An INSERT INTO…VALUES.. statement can add multiple rows per execution to a table.
B. An UPDATE… SET… statement can modify multiple rows based on multiple conditions on a table.
C. A DELETE FROM….. statement can remove rows based on only a single condition on a table.
D. An INSERT INTO… VALUES….. statement can add a single row based on multiple conditions on a table.
E. A DELETE FROM….. statement can remove multiple rows based on multiple conditions on a table.
F. An UPDATE….SET…. statement can modify multiple rows based on only a single condition on a table. |
|
Definition
B. An UPDATE… SET… statement can modify multiple rows based on multiple conditions on a table.
E. A DELETE FROM….. statement can remove multiple rows based on multiple conditions on a table. |
|
|
Term
45. View the exhibit and examine the descriptions of the DEPT and LOCATIONS tables.
You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department.
Which SQL statement would you execute to accomplish the task?
A.
UPDATE dept d
SET city = ALL (SELECT city
FROM locations l
WHERE d.location_id = l.location_id);
B.
UPDATE dept d
SET city = (SELECT city
FROM locations l)
WHERE d.location_id = l.location_id;
C.
UPDATE dept d
SET city = ANY (SELECT city
FROM locations l)
D.
UPDATE dept d
SET city = (SELECT city
FROM locations l
WHERE d.location_id = l.location_id); |
|
Definition
D.
UPDATE dept d
SET city = (SELECT city
FROM locations l
WHERE d.location_id = l.location_id); |
|
|
Term
46. Which three statements are true regarding group functions? (Choose three.)
A. They can be used on columns or expressions.
B. They can be passed as an argument to another group function.
C. They can be used only with a SQL statement that has the GROUP BY clause.
D. They can be used on only one column in the SELECT clause of a SQL statement.
E. They can be used along with the single-row function in the SELECT clause of a SQL statement. |
|
Definition
A. They can be used on columns or expressions.
B. They can be passed as an argument to another group function.
E. They can be used along with the single-row function in the SELECT clause of a SQL statement. |
|
|
Term
47. View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES tables.
The PROD_ID column is the foreign key in the SALES tables, which references the PRODUCTS table. Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE)
AS
SELECT prod_id, cust_id, time_id
FROM sales;
Which statement is true regarding the above command?
A. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.
B. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.
C. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
D. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table. |
|
Definition
| A. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table. |
|
|
Term
48. 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 customers
WHERE SUBSTR (cust_name, INSTR (cust_name, ‘ ‘)+1)
LIKE INITCAP (‘MC%’);
B. SELECT SUBSTR (cust_name, INSTR (cust_name, ‘ ‘)+1)
FROM customers
WHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ‘ ‘)+1)) =’Mc’;
C. SELECT SUBSTR (cust_name, INSTR (cust_name, ‘ ‘)+1)
FROM customers
WHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ‘ ‘)+1))
LIKE ‘Mc%’;
D. SELECT SUBSTR (cust_name, INSTR (cust_name, ‘ ‘)+1)
FROM customers
WHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ‘ ‘)+1)) =
INITCAP ‘MC%’; |
|
Definition
C. SELECT SUBSTR (cust_name, INSTR (cust_name, ‘ ‘)+1)
FROM customers
WHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ‘ ‘)+1))
LIKE ‘Mc%’; |
|
|
Term
49. Evaluate the following query:
SQL> SELECT TRUNC (ROUND(156.00, -1),-1)
FROM DUAL;
What would be the outcome?
A. 150
B. 200
C. 160
D. 16
E. 100 |
|
Definition
|
|
Term
50. Which statement is true regarding the INTERSECT operator?
A. The names of columns in all SELECT statements must be identical.
B. It ignores NULL values.
C. Reversing the order of the intersected tables alters the result.
D. The number of columns and data types must be identical for all SELECT statements in the query. |
|
Definition
| D. The number of columns and data types must be identical for all SELECT statements in the query. |
|
|
Term
51. Which statement is true about an inner join specified in the WHERE clause of a query?
A. It must have primary-key and foreign-key constraints defined on the columns used in the join condition.
B. It requires the column names to be the same in all tables used for the join conditions.
C. It is applicable for equijoin and non equijoin conditions.
D. It is applicable for only equijoin conditions. |
|
Definition
| C. It is applicable for equijoin and non equijoin conditions. |
|
|
Term
52. Which statement is true about transactions?
A. A set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINT
forms a single transaction.
B. Each Data Definition Language (DDL) statement executed forms a single transaction.
C. A set of DDL statements executed in a sequence ending with a COMMIT forms a single transaction.
D. A combination of DDL and DML statements executed in a sequence ending with a COMMIT forms a single transaction. |
|
Definition
| B. Each Data Definition Language (DDL) statement executed forms a single transaction. |
|
|
Term
53. View the exhibit and examine the structure of the CUSTOMERS table.
Which two tasks would require subqueries or joins to be executed in a single statement?
A. Finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
B. Finding the average credit limit of male customers residing in ‘Tokyo’ or ‘Sydney’
C. Listing of customers who do not have a credit limit and were born before 1980
D. Finding the number of customers, in each city, who’s marital status is ‘married’.
E. Listing of those customers, whose credit limit is the same as the credit limit of customers residing in the city ‘Tokyo’. |
|
Definition
A. Finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
E. Listing of those customers, whose credit limit is the same as the credit limit of customers residing in the city ‘Tokyo’. |
|
|
Term
54. View the exhibit and examine the description of the DEPARTMENTS and EMPLOYEES tables.
The retrieve data for all the employees for their EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT NAME, the following SQL statement was written:
SELECT employee_id, first_name, department_name
FROM employees
NATURAL JOIN departments;
The desired output is not obtained after executing the above SQL statement. What could be the reason for this?
A. The table prefix is missing for the column names in the SELECT clause.
B. The NATURAL JOIN clause is missing the USING clause.
C. The DEPARTMENTS table is not used before the EMPLOYEES table in the FROM clause.
D. The EMPLOYEES and DEPARTMENTS tables have more than one column with the same column name and data type. |
|
Definition
| D. The EMPLOYEES and DEPARTMENTS tables have more than one column with the same column name and data type. |
|
|
Term
55. Which statements are correct regarding indexes? (Choose all that apply.)
A. A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.
B. Indexes should be created on columns that are frequently referenced as part of any expression.
C. When a table is dropped, the corresponding indexes are automatically dropped.
D. For each DML operation performed, the corresponding indexes are automatically updated. |
|
Definition
A. A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.
C. When a table is dropped, the corresponding indexes are automatically dropped.
D. For each DML operation performed, the corresponding indexes are automatically updated. |
|
|
Term
56. View the Exhibit for the structure of the STUDENT and FACULTY tables.
You need to display the faculty name followed by the number of students handled by the faculty at the base location. Examine the following two SQL statements:
Statement 1:
SELECT faculty_name, COUNT(student_id)
FROM student JOIN faculty
USING (faculty_id, location_id)
GROUP BY faculty_name;
Statement 2:
SELECT faculty_name, COUNT(student_id)
FROM student NATURAL JOIN faculty
GROUP BY faculty_name;
Which statement is true regarding the outcome?
A. Only statement 1 executes successfully and gives the required result.
B. Only statement 2 executes successfully and gives the required result.
C. Both statements 1 and 2 execute successfully and give different results.
D. Both statements 1 and 2 execute successfully and give the same required result. |
|
Definition
| A. Only statement 1 executes successfully and gives the required result. |
|
|
Term
57. Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS:
Create Table Department_Details
(Department_Id Number Primary Key,
Department_Name Varchar2(50),
Hod Varchar2(50));
Create Table Course_Details
(Course_Id Number Primary Key,
Course_Name Varchar2(50),
Department_Id Varchar2(50));
You want to generate a list of all department IDs along with any course IDs that may have been assigned to them.
Which SQL statement must you use?
A. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN
course_details c ON (d.department_id=c. department_id);
B. SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN
course_details c ON (d.department_id=c. department_id);
C. SELECT d.department_id, c.course_id FROM course_details c LEFT OUTER JOIN
department_details d ON (c.department_id=d. department_id);
D. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN
course_details c ON (c.department_id=d. department_id); |
|
Definition
B. SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN
course_details c ON (d.department_id=c. department_id); |
|
|
Term
58. Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.)
A. You can use column alias in the GROUP BY clause.
B. Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups.
C. The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause.
D. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.
E. 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 cause. |
|
Definition
D. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.
E. 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 cause. |
|
|
Term
59. You want to display the date for the first Monday of the next month and issue the following command:
select to_char(next_day(last_day(sysdate),'MON'),'DD "is the first Monday for" fmmonth RRRR') from dual;
What is the outcome?
A. In generates an error because rrrr should be replaced by rr in the format string.
B. It executes successfully but does not return the correct result.
C. It executes successfully and returns the correct result.
D. In generates an error because TO_CHAR should be replaced with TO_DATE.
E. In generates an error because fm and double quotation marks should not be used in the format string |
|
Definition
| C. It executes successfully and returns the correct result. |
|
|
Term
60. 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. |
|
Definition
C. The EXECUTION_DATETIME must be of DATE data type.
F. The COMPONENT_ID column must be of CHAR data type. |
|
|
Term
61. Which three tasks can be performed using SQL functions built into Oracle Database?
A. displaying a date in a non default 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 |
|
Definition
A. displaying a date in a non default format
B. finding the number of characters in an expression
C. substituting a character string in a text expression with a specified string |
|
|
Term
62. Which three statements are true about the ALTER TABLE….DROP COLUMN…. command?
A. A column can be dropped only if it does not contain any data.
B. A column can be dropped only if another column exists in the table.
C. A dropped column can be rolled back.
D. The column in a composite PRIMARY KEY with the CASCADE option can be dropped.
E. A parent key column in the table cannot be dropped. |
|
Definition
B. A column can be dropped only if another column exists in the table.
D. The column in a composite PRIMARY KEY with the CASCADE option can be dropped.
E. A parent key column in the table cannot be dropped. |
|
|
Term
63. View the exhibit and examine the ORDERS table.
Name__________Null?_______________Type
--------------------------------------------------------------
ORDER_ID______NOT_NULL___________NUMBER_(4)
ORDATE________DATE_______________DATE
CUSTOMER_ID______________________NUMBER_(3)
ORDER_TOTAL______________________NUMBER_(7,2)
The ORDERS table contains data and all orders have been assigned a customer ID. Which statement would add a NOT NULL constraint to the CUSTOMER_ID column?
A. ALTER TABLE orders
MODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
B. ALTER TABLE orders
ADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
C. ALTER TABLE orders
MODIFY customer_id CONSTRAINT orders_cust_nn NOT NULL (customer_id);
D. ALTER TABLE orders
ADD customer_id NUMBER (6) CONSTRAINT orders_cust_id_nn NOT NULL; |
|
Definition
C. ALTER TABLE orders
MODIFY customer_id CONSTRAINT orders_cust_nn NOT NULL (customer_id); |
|
|
Term
64. Evaluate the following SQL statement:
SELECT product_name || ‘it’s not available for order'
FROM product_information
WHERE product_status = ‘obsolete’;
You received the following error while executing the above query:
ERROR
ORA-01756: quoted string not properly terminated.
What would you do to execute the query successfully?
A. Use Quote (q) operator and delimiter to allow the use of single quotation mark in the literal character string.
B. Enclose the literal character string in the SELECT clause within the double quotation marks.
C. Do not enclose the character literal string in the SELECT clause within the single quotation marks.
D. Use escape character to negate the single quotation mark inside the literal character string in the SELECT clause. |
|
Definition
| A. Use Quote (q) operator and delimiter to allow the use of single quotation mark in the literal character string. |
|
|
Term
65. Which task can be performed by using a single Data Manipulation Language (DML) statement?
A. adding a column constraint when inserting a row into a table
B. adding a column with a default value when inserting a row into a table
C. removing all data only from one single column on which a unique constraint is defined
D. removing all data only from one single column on which a primary key constraint is defined. |
|
Definition
| C. removing all data only from one single column on which a unique constraint is defined |
|
|
Term
66. 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. |
|
Definition
B. COUNT (DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column.
D. COUNT (*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns. |
|
|
Term
67. Examine the structure of the MEMBERS table.
Name____________Null?_________Type
—————————————————————————————
MEMBER_ID_______NOT_NULL____VARCHAR2_(6)
FIRST_NAME___________________VARCHAR2_(50)
LAST_NAME_______NOT_NULL____VARCHAR2_(50)
ADDRESS______________________VARCHAR2_(50)
CITY_ ________________________VARCHAR2_(25)
STATE____________NOT_NULL____VARCHAR2_(3)
Which query can be used to display the last names and city names only for members from the states MO and MI?
A. SELECT last_name, city FROM members WHERE state =’MO’ AND state =’MI’;
B. SELECT last_name, city FROM members WHERE state LIKE ‘M%’;
C. SELECT last_name, city FROM members WHERE state IN (‘MO’, ‘MI’);
D. SELECT DISTINCT last_name, city FROM members WHERE state =’MO’ OR state =’MI’; |
|
Definition
| C. SELECT last_name, city FROM members WHERE state IN (‘MO’, ‘MI’); |
|
|
Term
68. 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. |
|
Definition
| A. In a character sort, the values are case-sensitive. |
|
|
Term
69. Sales data of a company is stored in two tables, SALES1 and SALES2, with some data being duplicated across the tables. You want to display the results from the SALES1 table, which are not present in the SALES2 table.
SALES1 table
Name_____________Null_________Type
-—————————————————————————
SALES_ID______________________NUMBER
STORE_ID______________________NUMBER
ITEMS_ID______________________NUMBER
QUANTITY______________________NUMBER
SALES_DATE____________________DATE
SALES2 table
Name_____________Null_________Type
-—————————————————————————
SALES_ID______________________NUMBER
STORE_ID______________________NUMBER
ITEMS_ID______________________NUMBER
QUANTITY______________________NUMBER
SALES_DATE____________________DATE
Which set operator generates the required output?
A. INTERSECT
B. UNION
C. PLUS
D. MINUS
E. SUBTRACT |
|
Definition
|
|
Term
70. Which normal form is a table in 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 |
|
Definition
|
|
Term
71. Examine the structure of the EMPLOYEES table.
Name_______________Null?____________Type
————————— ———————————————————————————
EMPLOYEE_ID________NOT NULL_______NUMBER(6)
FIRST_NAME________________________VARCHAR2(20)
LAST_NAME__________NOT NULL_______VARCHAR2(25)
EMAIL ______________NOT NULL_______VARCHAR2(25)
PHONE NUMBER______________________VARCHAR2(20)
HIRE_DATE__________NOT NULL________DATE
JOB_ID _____________NOT NULL_______VARCHAR2(10)
SALARY ____________________________NUMBER(8,2)
COMMISSION_PCT____________________NUMBER(2,2)
MANAGER_ID________________________NUMBER(6)
DEPARTMENT_ID_____________________NUMBER(4)
There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID.
You want to display the last names and manager IDs of employees who work for the same manager as the employee whose EMPLOYEE_ID is 123.Which query provide the correct output?
A.
SELECT e.last_name, m.manager_id
FROM employees e RIGHT OUTER JOIN employees m
on (e.manager_id = m.employee_id)
AND e.employee_id = 123;
B.
SELECT e.last_name, m.manager_id
FROM employees e RIGHT OUTER JOIN employees m
on (e.employee_id = m.manager_id)
WHERE e.employee_id = 123;
C.
SELECT e.last_name, e.manager_id
FROM employees e RIGHT OUTER JOIN employees m
on (e.employee_id = m.employee_id)
WHERE e.employee_id = 123;
D.
SELECT m.last_name, e.manager_id
FROM employees e LEFT OUTER JOIN employees m
on (e.manager_id = m.manager_id)
WHERE e.employee_id = 123; |
|
Definition
B.
SELECT e.last_name, m.manager_id
FROM employees e RIGHT OUTER JOIN employees m
on (e.employee_id = m.manager_id)
WHERE e.employee_id = 123; |
|
|
Term
72. REFER SIMILAR - QUESTION 39
Which three statements are true about multiple-row subqueries?
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. |
|
Definition
A. They can contain a subquery within a subquery.
B. They can return multiple columns as well as rows.
E. They can contain group functions and GROUP BY and HAVING clauses. |
|
|
Term
73. View the exhibit and examine the structure of the EMPLOYEES table.
You want to display all employees and their managers having 100 as the MANAGER_ID. You want the output in two columns: the first column would have the LAST_NAME of the managers and the second column would have
LAST_NAME of the employees.
Which SQL statement would you execute?
A.
SELECT m.last_name “Manager”, e.last_name “Employee”
FROM employees m JOIN employees e ON m.employee_id = e.manager_id
WHERE m.manager_id = 100;
B.
SELECT m.last_name “Manager”, e.last_name “Employee”
FROM employees m JOIN employees e
ON m.employee_id = e.manager_id
WHERE e.manager_id = 100;
C.
SELECT m.last_name “Manager”, e.last_name “Employee”
FROM employees m JOIN employees e
ON e.employee_id = m.manager_id
WHERE m.manager_id = 100;
D.
SELECT m.last_name “Manager”, e.last_name “Employee”
FROM employees m JOIN employees e
WHERE m.employee_id = e.manager_id and AND e.manager_id = 100 |
|
Definition
B.
SELECT m.last_name “Manager”, e.last_name “Employee”
FROM employees m JOIN employees e
ON m.employee_id = e.manager_id
WHERE e.manager_id = 100; |
|
|
Term
74. Examine the command:
ALTER TABLE books_transactions
ADD CONSTRAINT fk_book_id FOREIGN KEY (book_id)
REFERENCES books (book_id) ON DELETE CASCADE;
What does ON DELETE CASCADE imply?
A. When the BOOKS table is dropped, the BOOK_TRANSACTIONS table is dropped.
B. When the BOOKS table is dropped, all the rows in the BOOK_TRANSACTIONS table are deleted but the table structure is retained.
C. When a row in the BOOKS table is deleted, the rows in the BOOK_TRANSACTIONS table whose BOOK_ID matches that of the deleted row in the BOOKS table are also deleted.
D. When a value in the BOOKS.BOOK_ID column is deleted, the corresponding value is updated in the BOOKS_TRANSACTIONS.BOOK_ID column. |
|
Definition
| C. When a row in the BOOKS table is deleted, the rows in the BOOK_TRANSACTIONS table whose BOOK_ID matches that of the deleted row in the BOOKS table are also deleted. |
|
|
Term
75. The BOOKS_TRANSACTIONS table exists in your database.
SELECT * FROM books_transactions ORDER BY 3;
What is the outcome on execution?
A. The execution fails unless the numeral 3 in the ORDER BY clause is replaced by a column name.
B. Rows are displayed in the order that they are stored in the table only for the three rows with the lowest values in the key column.
C. Rows are displayed in the order that they are stored in the table only for the first three rows.
D. Rows are displayed sorted in ascending order of the values in the third column in the table. |
|
Definition
| D. Rows are displayed sorted in ascending order of the values in the third column in the table. |
|
|
Term
76. View the exhibit and examine the structure of the STORES table.
Name _____________________Null?_______________Type
—————–————–————–————–————–————–————–————–
STORE_ID_____________________________________NUMBER
NAME_________________________________________VARCHAR2(100)
ADDRESS______________________________________VARCHAR2(200)
CITY__________________________________________VARCHAR2(100)
COUNTRY______________________________________VARCHAR2(100)
START_DATE___________________________________DATE
END_DATE ____________________________________DATE
PROPERTY_PRICE_______________________________NUMBER
You want to display the NAME of the store along with the ADDRESS, START_DATE, PROPERTY_PRICE, and the projected property price, which is 115% of property price. The stores displayed must have START_DATE in the range of 36 months starting from 01-Jan-2000 and above.
Which SQL statement would get the desired output?
A.
SELECT name, concat (address| | ‘,’| |city| |’, ‘, country) AS full_address,
start_date, property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, ’01-JAN-2000′) <=36;
B.
SELECT name, concat (address| | ‘,’| |city| |’, ‘, country) AS full_address,
start_date, property_price, property_price*115/100
FROM stores
WHERE TO_NUMBER(start_date-TO_DATE(’01-JAN-2000′,’DD-MON-RRRR’)) <=36;
C.
SELECT name, address||’,’||city||’,’||country AS full_address,
start_date, property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, TO_DATE(’01-JAN-2000′,’DD-MON-RRRR’)) <=36;
D.
SELECT name, concat (address||’,’| |city| |’, ‘, country) AS full_address,
start_date, property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, TO_DATE(’01-JAN-2000′,’DD-MON-RRRR’)) <=36; |
|
Definition
D.
SELECT name, concat (address||’,’| |city| |’, ‘, country) AS full_address,
start_date, property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, TO_DATE(’01-JAN-2000′,’DD-MON-RRRR’)) <=36; |
|
|
Term
77. View the Exhibit and examine the details of the PRODUCT_INFORMATION table.
You have the requirement to display PRODUCT_NAME and LIST_PRICE 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, list_price
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 execute but the output would return no rows.
B. It would execute and the output would display the desired result.
C. It would not execute because the entire WHERE clause condition is not enclosed within the parentheses.
D. It would not execute because the same column has been used in both sides of the AND logical operator to form the condition. |
|
Definition
| A. It would execute but the output would return no rows. |
|
|
Term
78. Evaluate the following two queries:
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 only if there are null values in the CUST_CREDIT_LIMIT column.
B. There would be no change in performance.
C. Performance would degrade in query 2.
D. Performance would improve in query 2. |
|
Definition
| B. There would be no change in performance. |
|
|
Term
79. View the Exhibit and examine the structure of CUSTOMERS table. Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message “Not Available” displayed.
Which SQL statement would produce the required result?
A.
SELECT NVL (TO_CHAR(cust_credit_limit * .15), ‘Not Available’) “NEW CREDIT”
FROM customers;
B.
SELECT TO_CHAR (NVL(cust_credit_limit * .15), ‘Not Available’) “NEW CREDIT”
FROM customers;
C.
SELECT NVL(cust_credit_limit * .15), ‘Not Available’) “NEW CREDIT”
FROM customers;
D.
SELECT NVL(cust_credit_limit), ‘Not Available’) “NEW CREDIT”
FROM customers; |
|
Definition
A.
SELECT NVL (TO_CHAR(cust_credit_limit * .15), ‘Not Available’) “NEW CREDIT”
FROM customers; |
|
|
Term
80. You execute the following commands:
SQL > DEFINE hiredate = ’01-APR-2011′
SQL >SELECT employee_id, first_name, salary
FROM employees
WHERE hire_date > ‘&hiredate’
AND manager_id > &mgr_id;
For which substitution variables are you prompted for the input?
A. none, because no input required
B. both the substitution variables ”hiredate’ and ‘mgr_id’.
C. only hiredate’
D. only ‘mgr_id’ |
|
Definition
|
|
Term
81. Which two statements are true regarding roles? (Choose two.)
A. A role can be granted to itself.
B. A role can be granted to PUBLIC.
C. A user can be granted only one role at any point of time.
D. The REVOKE command can be used to remove privileges but not roles from other users.
E. Roles are named groups of related privileges that can be granted to users or other roles. |
|
Definition
B. A role can be granted to PUBLIC.
E. Roles are named groups of related privileges that can be granted to users or other roles. |
|
|
Term
82. 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
Identify three ORDER BY clauses either one of which can complete the query.
A. ORDER BY “Last name”
B. ORDER BY 2, cust_id
C. ORDER BY CUST_NO
D. ORDER BY 2, 1
E. ORDER BY “CUST_NO” |
|
Definition
A. ORDER BY “Last name”
B. ORDER BY 2, cust_id
D. ORDER BY 2, 1 |
|
|
Term
83. See the exhibit and examine the structure of the PROMOTIONS table.
Using the PROMOTIONS table you need to find out the average cost for promos in the range $0-2000 and $2000-5000 in category A.
You issue the following SQL Statement:
SELECT AVG (CASE
WHEN promo_cost BETWEEN 0 AND 2000 AND promo_category=’A’
THEN promo_cost
ELSE null END) “CAT_2000A”,
AVG (CASE
WHEN promo_cost BETWEEN 2001 and 5000 AND promo_category=’A’
THEN promo_cost
ELSE null END) “CAT_5000A”
FROM promotions;
What would be the outcome?
A. It generates error because multiple conditions cannot be specified for the WHEN clause.
B. It executes successfully and gives the required result.
C. It generates an error because CASE cannot be used with group functions.
D. It generates an error because NULL cannot be specified as a return value. |
|
Definition
| B. It executes successfully and gives the required result. |
|
|
Term
84. View the exhibit and examine the description of the PRODUCT_INFORMATION table. Which SQL statement would retrieve from the table the number of products having LIST_PRICE as NULL?
1. SELECT COUNT (list_price) FROM product_information WHERE list_price is NULL;
2. SELECT COUNT (list_price) FROM product_information WHERE list_price = NULL;
3. SELECT COUNT (NVL (list_price,0)) FROM product_information WHERE list_price is NULL;
4. SELECT COUNT (DISTINCT list_price) FROM product_information WHERE list_price is NULL; |
|
Definition
| 3. SELECT COUNT (NVL (list_price,0)) FROM product_information WHERE list_price is NULL; |
|
|
Term
| 85. Which three statements are true reading subqueries? A. A Main query can have many subqueries. B. A subquery can have more than one main query C. The subquery and main query must retrieve date from the same table. D. The subquery and main query can retrieve data from different tables. E. Only one column or expression can be compared between the subquery and main query. F. Multiple columns or expressions can be compared between the subquery and main query. |
|
Definition
| A. A Main query can have many subqueries. D. The subquery and main query can retrieve data from different tables. F. Multiple columns or expressions can be compared between the subquery and main query. |
|
|
Term
| 86. View the exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables. EMPLOYEES Table: Name__________________Null?____________Type ------------------------------------------------------------------------------------ EMPLOYEE_ID___________NOT_NULL________NUMBER_(6) FIRST_NAME____________________________VARCHAR2_(20) LAST_NAME_____________NOT_NULL________VARCHAR2_(25) HIRE_DATE_____________NOT_NULL________DATE JOB_ID_________________NOT_NULL________VARCHAR2_(10) SALARY_________________________________NUMBER_(10,_2) COMMISSION____________________________NUMBER_(6,_2) MANAGER_ID____________________________NUMBER_(6) DEPARTMENT_ID_________________________NUMBER_(4) DEPARTMENTS_Table: Name___________________Null?_____________Type ------------------------------------------------------------------------------------ DEPARTMENT_ID__________NOT_NULL________NUMBER_(4) DEPARTMENT_NAME_______NOT_NULL________VARCHAR2_(30) MANAGER_ID_____________________________NUMBER_(6) LOCATION_ID_____________________________NUMBER_(4) You want to update EMPLOYEES table as follows: • Update only those employees who work in Boston or Seattle (locations 2900 and 2700). • Set department_id for these employees to the department_id corresponding to London (location_id 2100). • Set the employees' salary in location_id 2100 to 1.1 times the average salary of their department. • Set the employees' commission in location_id 2100 to 1.5 times the average commission of their department. You issue the following command: SQL> UPDATE employees SET department_id = (SELECT department_id FROM departments WHERE location_id = 2100), (salary, commission) = (SELECT 1.1*AVG (salary), 1.5*AVG(commission) FROM employees, departments WHERE departments.location_id IN (2900, 2700, 2100)) WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 2900 OR location_id = 2700; What is outcome? A. It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an UPDATE statement. B. It generates an error because a subquery cannot have a join condition in a UPDATE statement. C. It executes successfully and gives the correct result. D. It executes successfully but does not give the correct result. |
|
Definition
| D. It executes successfully but does not give the correct result. |
|
|
Term
01. Evaluate the following ALTER TABLE statement:
ALTER TABLE orders SET UNUSED order_date;
Which statement is true?
a) The DESCRIBE command would still display the ORDER_DATE column.
b) ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.
c) The ORDER_DATE column should be empty for the ALTER TABLE command to execute successfully.
d) After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table.
|
|
Definition
d) After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table. |
|
|
Term
02. Examine the business rule: Each student can take up multiple projects and each project can have multiple students. You need to design an Entity Relationship Model (ERD) for optimal data storage and allow for generating reports in this format:
STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK
Which two statements are true in this scenario?
a) The ERD must have a 1: M relationship between the students and projects entitles.
b) The ERD must have a M:M relationship between the students and projects entities that must be resolved into 1:M relationships.
c) STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the projects entity.
d) PROJECT_ID must be the primary key in the projects entity and foreign key in the STUDENTS entity.
e) An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID; which is the foreign key linked to the STUDENTS and PROJECTS entities. |
|
Definition
b) The ERD must have a M:M relationship between the students and projects entities that must be resolved into 1:M relationships.
e) An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID; which is the foreign key linked to the STUDENTS and PROJECTS entities. |
|
|
Term
03. The first DROP operation is performed on PRODUCTS table using the following command:
DROP TABLE products PURGE;
Then you performed the FLASHBACK operation by using the following command:
FLASHBACK TABLE products TO BEFORE DROP;
Which statement describes the outcome of the FLASHBACK command?
a) It recovers only the table structure.
b) It recovers the table structure, data, and the indexes.
c) It recovers the table structure and data but not the related indexes.
d) It is not possible to recover the table structure, data, or the related indexes. |
|
Definition
d) It is not possible to recover the table structure, data, or the related indexes. |
|
|
Term
04. 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,2,1,3
b) 4,1,2,3
c) 2,4,1,3
d) 2,1,4,3
|
|
Definition
|
|
Term
05. The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues the following GRANT command:
GRANT ALL ON orders, order_items TO PUBLIC;
What correction needs to be done to the above statement?
a) PUBLIC should be replaced with specific usernames.
b) ALL should be replaced with a list of specific privileges.
c) WITH GRANT OPTION should be added to the statement.
d) Separate GRANT statements are required for ORDERS and ORDER_ITEMS tables. |
|
Definition
d) Separate GRANT statements are required for ORDERS and ORDER_ITEMS tables. |
|
|
Term
06. Which statement correctly grants a system privilege?
a) GRANT EXECUTE ON prod TO PUBLIC;
b) GRANT CREATE VIEW ON table 1 TO user;
c) GRANT CREATE TABLE TO user1, user2;
d) GRANT CREATE SESSION TO ALL; |
|
Definition
c) GRANT CREATE TABLE TO user1, user2; |
|
|
Term
07. Which statement is true regarding external tables?
a) The default REJECT LIMIT for external tables is UNLIMITED.
b) The data and metadata for an external table are stored outside the database.
c) ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.
d) The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the database from an external table. |
|
Definition
d) The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the database from an external table. |
|
|
Term
08. Which three statements are true regarding the data types?
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. |
|
Definition
a) Only one LONG column can be used per table.
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. |
|
|
Term
09. You issue 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 in the table are deleted but the table structure will remain
b) All data along with the table structure is deleted
c) All views and synonyms will remain but they are invalidated
d) The pending transaction in the session is committed
e) All indexes on the table will remain but they are invalidated |
|
Definition
b) All data along with the table structure is deleted
c) All views and synonyms will remain but they are invalidated
d) The pending transaction in the session is committed |
|
|
Term
10. You want to display 5 percent of the rows from the sales table for products with the lowest AMOUNT_SOLD and also want to include the rows that have the same AMOUNT_SOLD even if this causes the output to exceed 5 percent of the rows.
Which query will provide the required result?
a) SELECT prod_id, cust_id, amount_sold FROM sales
ORDER BY amount_sold
FETCH FIRST 5 PERCENT ROWS WITH TIES;
b) SELECT prod_id, cust_id, amount_sold FROM sales
ORDER BY amount_sold
FETCH FIRST 5 PERCENT ROWS ONLY WITH TIES;
c) SELECT prod_ id, cust_id, amount_sold FROM sales
ORDER BY amount_sold
FETCH FIRST 5 PERCENT ROWS WITH TIES ONLY;
d) SELECT prod_id, cust_id, amount_sold FROM sales
ORDER BY amount sold
FETCH FIRST 5 PERCENT ROWS ONLY; |
|
Definition
a) SELECT prod_id, cust_id, amount_sold FROM sales
ORDER BY amount_sold
FETCH FIRST 5 PERCENT ROWS WITH TIES; |
|
|
Term
11. Which three statements are true regarding constraints?
(Choose three.)
a) A constraint can be disabled even if the constraint column contains data.
b) All the constraints can be defined at the column level as well as the table level
c) A foreign key cannot contain NULL values.
d) A column with the UNIQUE constraint can contain NULL
e) A constraint is enforced only for the INSERT operation on a table.
f) You can have more than one column in a table as part of a primary key. |
|
Definition
a) A constraint can be disabled even if the constraint column contains data.
d) A column with the UNIQUE constraint can contain NULL
f) You can have more than one column in a table as part of a primary key. |
|
|
Term
12. Which two statements are true regarding the EXISTS operator used in the correlated subqueries?
a) It is used to test whether the values retrieved by the inner query exist in the result of the outer query.
b) The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.
c) It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.
d) The outer query stops evaluating the result set of the inner query when the first value is found. |
|
Definition
c) It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.
d) The outer query stops evaluating the result set of the inner query when the first value is found. |
|
|
Term
13. Which two tasks can be performed by using Oracle SQL statements?
A. changing the password for an existing database
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 |
|
Definition
A. changing the password for an existing database
C. querying data from tables across databases |
|
|
Term
14. Which two statements are true about sequences created in a single instance database? (Choose two.)
A. CURRVAL is used to refer to the last sequence number that has been generated
B. DELETE <sequencename> would remove a sequence from the database
C. The numbers generated by a sequence can be used only for one table
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. |
|
Definition
A. 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 |
|
|
Term
15. Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement? (Choose all that apply.)
A. The HAVING clause can be used with aggregate functions in subqueries.
B. The WHERE clause can be used to exclude rows after dividing them into groups.
C. The WHERE clause can be used to exclude rows before dividing them into groups.
D. The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.
E. The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table. |
|
Definition
A. The HAVING clause can be used with aggregate functions in subqueries.
C. The WHERE clause can be used to exclude rows before dividing them into groups. |
|
|
