SQL ORACLE Managing Tables using DML statements1

An Oracle Database session has an uncommitted transaction in progress which updated 5000 rows in a table. In which three situations does the transaction complete thereby committing the updates? (Choose three.). When a CREATE TABLE AS SELECT statement is issued in the same session but fails with a syntax error. when a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user then issues a COMMIT. when the session logs out successfully. when a CREATE INDEX statement is executed successfully in the same session. when a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT. when a COMMIT statement is issued by the same user from another session in the same database instance.

No user-defined locks are used in your database. Which three are true about Transaction Control Language (TCL)? (Choose three.). ROLLBACK without the TO SAVEPOINT clause undoes all the transaction’s changes, releases its locks, and erases all its savepoints. ROLLBACK without the TO SAVEPOINT clause undoes all the transaction’s changes but does not release its locks. ROLLBACK without the TO SAVEPOINT clause undoes all the transaction’s changes but does not erase its savepoints. ROLLBACK TO SAVEPOINT undoes the transaction’s changes made since the named savepoint and then ends the transaction. COMMIT ends the transaction and makes all its changes permanent. COMMIT erases all the transaction’s savepoints and releases its locks.

Which statement is true about transactions?. A combination of DDL and DML statements executed in a sequence ending with a COMMIT forms a transaction. A set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINT forms a single transaction. Each Data Definition Language (DDL) statement executed forms a single transaction. A set of DDL statements executed in a sequence ending with a COMMIT forms a single transaction.

Which two are true about using the FOR UPDATE clause in a SELECT statement? (Choose two.). It can be used with SET operators (UNION, INTERSECT etc.). It cannot be used with the DISTINCT keyword. If the NOWAIT clause is added, the statement will automatically acquire locks from their owning transactions and not wait. The statement skips rows locked by other transactions. It can be used with joins.

Which statement is true about TRUNCATE and DELETE?. For tables with multiple indexes and triggers DELETE is faster than TRUNCATE. For large tables TRUNCATE is faster than DELETE. You can never TRUNCATE a table if foreign key constraints will be violated. You can never DELETE rows from a table if foreign key constraints will be violated.

Which three statements are true about performing Data Manipulation Language (DML) operations on a view with no INSTEAD OF triggers defined? (Choose three.). Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view. The WITH CHECK clause has no effect when deleting rows from the underlying table through the view. Delete statements can always be done on a table through a view. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view. Insert statements can always be done on a table through a view.

Which three statements are true about Data Manipulation Language (DML)?. Delete statements can remove multiple rows based on multiple conditions. Insert statements can insert nulls explicitly into a column. insert into. . .select. . .from statements automatically commit. DML statements require a primary key be defined on a table. update statements can have different subqueries to specify the values for each updated column.

Which two are true about savepoints? (Choose two.). After issuing a savepoint, you can roll back to the savepoint name within the current transaction. They make uncommitted updates visible to sessions owned by other users. You can commit updates done between two savepoints without committing other updates in the current transaction. A ROLLBACK TO SAVEPOINT command issued before the start of a transaction results in an error. They make uncommitted updates visible to other sessions owned by the same user. After issuing a savepoint, you cannot roll back the complete transaction.

Which two are true about rollbacks?. The ROLLBACK statement does not release locks resulting from table updates. Data Control L anguage (DCL) statements, such as GRANT and REVOKE, can be rolled back. A transaction interrupted by a system failure is automatically rolled back. If the ROLLBACK statement is used without TO SAVEPOINT, then all savepoints in the transaction are deleted . Data consistency is not guaranteed after a rollback.

Examine the description of the BOOKS table: The table has 100 rows. Examine this sequence of statements issued in a new session: INSERT INTO books VALUES (‘ADV112’, ‘Adventures of Tom Sawyer’, NULL, NULL); SAVEPOINT a; DELETE FROM books; ROLLBACK TO SAVEPOINT a; ROLLBACK; Which two statements are true? (Choose two.). The second ROLLBACK command replays the delete. The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row. The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed. The second ROLLBACK command undoes the insert. The second ROLLBACK command does nothing.

Which two statements are true about Oracle databases and SQL? (Choose two.). Updates performed by a database user can be rolled back by another user by using the ROLLBACK command. A query can access only tables within the same schema. The database guarantees read consistency at select level on user-created tables. A user can be the owner of multiple schemas in the same database. When you execute an update statement, the database instance locks each updated row.

Examine these statements executed in a single Oracle session: CREATE TABLE product (pcode NUMBER(2), pname VARCHAR2(20)); INSERT INTO product VALUES (1, ‘pen’); INSERT INTO product VALUES (2, ‘pencil’); INSERT INTO product VALUES (3, ‘fountain pen’); SAVEPOINT a; UPDATE product SET pcode = 10 WHERE pcode = 1; COMMIT; DELETE FROM product WHERE pcode = 2; SAVEPOINT b; UPDATE product SET pcode = 30 WHERE pcode = 3; SAVEPOINT c; DELETE FROM product WHERE pcode = 10; ROLLBACK TO SAVEPOINT b; COMMIT; Which three statements are true? (Choose three.). There is no row containing pencil. The code for pen is 10. There is no row containing fountain pen. The code for pen is 1. The code for fountain pen is 3. There is no row containing pen.

Which two statements are true regarding a SAVEPOINT? (Choose two.). Rolling back to a SAVEPOINT can undo a CREATE INDEX statement. Rolling back to a SAVEPOINT can undo a TRUNCATE statement. Only one SAVEPOINT may be issued in a transaction. A SAVEPOINT does not issue a COMMIT. Rolling back to a SAVEPOINT can undo a DELETE statement.

You execute these commands: CREATE TABLE customers (customer_id INTEGER, customer_name VARCHAR2 (20)) ; INSERT INTO customers VALUES (1, ‘Customer 1’); SAVEPOINT post_insert; INSERT INTO customers VALUES (2, ‘Customer 2’); <TODO> SELECT COUNT (*) FROM customers; Which two, used independently, can replace so the query returns 1?. ROLLBACK;. ROLLBACK TO SAVEPOINT post_insert;. ROLLBACK TO post_insert;. COMMIT;. COMMIT TO SAVEPOINT post_insert;.

Examine the description of the EMPLOYEES table: Which two statements will insert a row into the EMPLOYEES table? (Choose two.). INSERT INTO employees VALUES (101, ‘John’, ‘Smith’, 12000, SYSDATE);. INSERT INTO employees VALUES (101, ‘John’, ‘Smith’, 10, 12000, SYSDATE);. INSERT INTO employees (employee_id, salary, first_name, hiredate, last_name) VALUES (101, 12100, ‘John’, SYSDATE, ‘Smith’);. iNSERT INTO employees (employee_id, first_name, last_name, salary, hiresate) VALUES ( (SELECT 101, ‘John’, ‘Smith’. 12000, SYSDATE FROM dual) );. INSERT INTO employees SELECT 101, ‘John’, ‘Smith’, 12000, (SELECT SYSDATE FROM dual), 10 FROM dual;. INSERT INTO employees VALUES (101, ‘John’, ‘ ‘, 12000, SYSDATE, 10);.

View the Exhibit and examine the description of the tables. You execute this SQL statement: NSERT INTO sales VALUES ( 23, 2300, SYSDATE, (SELECT channel_id FROM channels WHERE channel _desc = ‘Direct Sales’), 12, 1, 500) ; Which three statements are true? (Choose three.). The statement will execute successfully and a new row will be inserted into the SALES table. A product can have a different unit price at different times. The statement will fail if a row already exists in the SALES table for product 23. The statement will fail because a subquery may not be contained in a VALUES clause. A customer can exist in many countries. The SALES table has five foreign keys.

Examine the SQL statement used to create the TRANSACTION table. SQL > CREATE TABLE transaction (trn_id char(2) primary key, Start_date date DEFAULT SYSDATE, End_date date NOT NULL); The value ‘A1’ does not exist for trn_id in this table. Which SQL statement successfully inserts a row into the table with the default value for START_DATE?. INSERT INTO transaction VALUES (‘A1’, DEFAULT, TO_DATE(DEFAULT +10)). INSERT INTO transaction VALUES (‘A1’, DEFAULT, TO_DATE(‘SYSDATE+10’)). INSERT INTO transaction (trn_id, end _date) VALUES (‘A1’, ‘10-DEC-2014’). INSERT INTO transaction (trn_id, start_date, end _date) VALUES (‘A1’, , ‘10-DEC-2014’).

You create a table by using this command: CREATE TABLE rate_list (rate NUMBER(6,2)); Which two are true about executing statements? (Choose two.). INSERT INTO rate_list VALUES (0.999) produces an error. INSERT INTO rate_list VALUES (-.9) inserts the value as -.9. INSERT INTO rate_list VALUES (87654.556) inserts the value as 87654.6. INSERT INTO rate_list VALUES (-10) produces an error. INSERT INTO rate_list VALUES (-99.99) inserts the value as 99.99. INSERT INTO rate_list VALUES (0.551) inserts the value as .55.

Examine the command to create the BOOKS table SQL> create table books(book id CHAR(6) PRIMARY KEY, title VARCHAR2(100) NOT NULL, publisher_id VARCHAR2(4), author_id VARCHAR2 (50)); The BOOK ID value 101 does not exist in the table. Examine the SQL statement. insert into books (book id title, author_id values (‘101’,’LEARNING SQL’,’Tim Jones’). It executes successfully and the row is inserted with a null PLBLISHER_ID. It executes successfully only if NULL is explicitly specified in the INSERT statement. It executes successfully only NULL PUBLISHER_ID column name is added to the columns list in the INSERT statement. It executes successfully onlyif NULL PUBLISHER ID column name is added to the columns list and NULL is explicitly specified In the INSERT statement.

Which two are true about multitable INSERT statements?. The conditional INSERT FIRST statement always inserts a row into a single table. The unconditional INSERT ALL statement must have the same number of columns in both the source and target tables. They can transform a row from a source table into multiple rows in a target table. The conditional INSERT ALL statement inserts rows into a singe table by aggregating source rows. They always use subqueries.

Which three are true about multitable INSERT statements? (Choose three.). They can be performed on views. They can insert each computed row into more than one table. They can be performed on relational tables. They can be performed only by using a subquery. They can be performed on external tables using SQL* Loader. They can be performed on remote tables.

Which two statements are true about conditional INSERT ALL? (Choose two.). Each row returned by the subquery can be inserted into only a single target table. A single WHEN condition can be used for multiple INTO clauses. Each WHEN condition is tested for each row returned by the subquery. It cannot have an ELSE clause. The total number of rows inserted is always equal to the number of rows returned by the subquery.

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 > 200000 AND order_total THEN INTO large_orders SELECT order_id, order_total, customer_id FROM orders; Evaluate the following statement. Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?. Each row is evaluated by the first WheN clause and if the condition is true, then the row would be evaluated by the subsequent when clauses. The INSERT statement will return an error because the ELSE clause is missing. All rows are evaluated by all the three WHEN clauses. Each row is evaluated by the first WHEN clause and if the condition is false then the row would be evaluated by the subsequent when clauses.

Examine this statement which executes successfully: INSERT ALL WHEN SAL > 20000 THEN INTO special_sal VALUES (EMP_ID, SAL) ELSE INTO sal history VALUES (EMP_ID, HIREDATE, SAL) INTO mgr_history VALUES (EMP_ID, MGR, SAL) SELECT employee_id EMP_ID, hire_date HIREDATE, salary SAI, manager _id MGR FROM employees WHERE employee_id < 125; Which is true?. Only if the salary is 20000 or less and the employee id is 125 or higher, insert EMPLOYEE_ID, MANAGER_ID, and SALARY into the MGR_HISTORY table. Regardless of salary and employee id, insert EMPLOYEE_ID, MANAGER_ID, and SALARY into the MGR_HISTORY table. Regardless of salary, only if the employee id is less than 125, insert EMPLOYEE_ID, MANAGER_ID, and SALARY into the MGR_HISTORY table. Only if the salary is 20000 or less and the employee id is less than 125, insert EMPLOYEE_ID, MANAGER_ID, and SALARY into the MGR_HISTORY table.

Which two are true about the MERGE statement? (Choose two.). The WHEN NOT MATCHED clause can be used to specify the deletions to be performed. The WHEN NOT MATCHED clause can be used to specify the updates to be performed. The WHEN NOT MATCHED clause can be used to specify the inserts to be performed. The WHEN WATCHED clause can be used to specify the inserts to be performed. The WHEN WATCHED clause can be used to specify the updates to be performed.

Which three are true about the MERGE statement?. It can merge rows only from tables. It can use views to produce source rows. It can combine rows from multiple tables conditionally to insert into a single table. It can use subqueries to produce source rows. It can update the same row of the target table multiple times. It can update, insert, or delete rows conditionally in multiple tables.

Examine this description of the PRODUCTS table: NAME Null? Type ------------------ ------------- ------------------ PROD_ID NOT NULL NUMBER(2) QTY NUMBER(5,2) COST NUMBER(8,2) You successfully execute this command: CREATE TABLE new_prices (prod_id NUMBER(2), price NUMBER(8,2)) Which two statements execute without errors? (Choose two.). MERGE INTO new_prices n USING (SELECT * FROM products WHERE cost > 150) p ON (n.prod_id = p.prod_id) WHEN MATCHED THEN DELETE WHERE (p.cost < 200) WHEN NOT MATCHED THEN INSERT (n.prod_id, n.price) VALUES (p.prod_id, p.cost*.01);. MERGE INTO new_prices n USING (SELECT * FROM products) p WHEN MATCHED THEN UPDATE SET n.price = p.cost*.01 WHEN NOT MATCHED THEN INSERT (n.prod_id, n.price) VALUES (p.prod_id, cost*. 01) WHERE (p.cost < 200);. MERGE INTO new_prices n USING (SELECT * FROM products WHERE cost > 150) p ON (n.prod_id = p.prod_id) WHEN MATCHED THEN UPDATE SET n.price = p.cost*.01 DELETE WHERE (p.cost < 200);. MERGE INTO new_prices n USING products p ON (p.prod_id = n.prod_id) WHEN NOT MATCHED THEN INSERT (n.prod_id, n.price) VALUES (p.prod_id, cost*.01) WHERE (p.cost < 200);.