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. .
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.
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.
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.
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.
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;.
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.
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.
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.
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;
.
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.
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.
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.
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.
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.
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;.
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;
.
|
