Oracle__7. Oracle 1Z0-051 Exam - Insert_Update_Delete Statements

Question 1

What happens after these 2 statements? CREATE TABLE orders (order_id NUMBER(10) NOT NULL, customer_id Number(8), order_date DATE DEFAULT SYSDATE); INSERT INTO TABLE (order_id, customer_id, order_date) VALUES (1,null,’05-AUG-2010’)

Answer 1

The record is inserted with a null for the customer_id

Question 2

What happens after these 2 statements? CREATE TABLE orders (order_id NUMBER(10) NOT NULL, customer_id Number(8), order_date DATE DEFAULT SYSDATE); INSERT INTO TABLE (order_id, customer_id, order_date) VALUES (1,12,null)

Answer 2

The record is inserted with a null for the customer_id and a default date of today’s date

Question 3

What happens after these 2 statements? CREATE TABLE orders (order_id NUMBER(10) NOT NULL, customer_id Number(8), order_date DATE DEFAULT SYSDATE); INSERT INTO TABLE (order_id, customer_id, order_date) VALUES (null,12,null)

Answer 3

The record is rejected but the is a null for Order_id

Question 4

Are all fields required in an INSERT INTO statement?

Answer 4

NoBut all fields declared as NOT NULL must be populated in an INSERT statement

Question 5

Are all columns that have NOT NULL constraints and defaults values required in an INSERT INTO statement?

Answer 5

No. All fields that have defaults declared will automatically be populated on an insert into statement.

Question 6

What is the syntax for an insert statement if more than one record?

Answer 6

INSERT ALL INTO (field1, field2…) VALUES (exp1, exp2, …) INTO (field1, field2…) VALUES (exp1, exp2, …) INTO (field1, field2…) VALUES (exp1, exp2, …)SELECT * FROM dual;

Question 7

What is the syntax of an INSERT statement where the field name list can be eliminated?

Answer 7

INSERT INTO table2 SELECT * FROM table1All the field names and data types must match and be in the same order between the 2 tables

Question 8

What reason would a values clause be used in an insert statement?

Answer 8

to insert records using expressions or hard coded values that are not retrieved from other tables?

Question 9

Is a constraint only enforced by an INSERT operation on the table?

Answer 9

NoA constraint is also enforced by an UPDATE operation on a table

Question 10

The default format for a date column is DD-MON-RR, so on an insert can you insert 10-JANUARY-2014?

Answer 10

Yes.Months are implicitly convertedYears are implicitly converted

Question 11

What will happen with this insert statement? Insert into table (id, statename) values (&id, ‘&statename’);

Answer 11

The user will be prompted for the ID then the statename.

Question 12

What will happen with this insert statement? insert into table(id, statename) values (&id, ‘&&statename’)

Answer 12

The user will be prompred for the ID and the statename only the first time. The first statename will be remembered to the user will not be prompted for the statename the subsequent inserts.

Question 13

What can be used in place of the VALUES clause in an INSERT statement?

Answer 13

a select statement

Question 14

Is there anything incorrect with this INSERT statement? UPDATE employees SET job = ‘IT’, commission = null where employee = 114;

Answer 14

Everything is syntactically correct.

Question 15

The MERGE command the same as performing what 2 other commands?

Answer 15

UPDATE and INSERTThe Merge command will perform an UPDATE and then an INSERT

Question 16

Can an update be performed on a view that does not have all the table columns defined with NOT NULL?

Answer 16

No.All NOT NULL columns must be in the view before the view will allow any updates to the table

Question 17

What is the maximum number of tables in an Update that can be changed?

Answer 17

Only 1 table

Question 18

Will this statement produce an error? INSERT INTO (SELECT department_id, department_name, location_id FROM departments WHERE location_id < 2000) VALUES (9999, ‘Entertainment’, 2500);

Answer 18

This statement is validThe records will be inserted even though the 2500 value fails the WHERE clause

Question 19

Will this statement produce an error? INSERT INTO (SELECT department_id, department_name, location_id FROM departments WHERE location_id < 2000 WITH CHECK OPTION) VALUES (9999, ‘Entertainment’, 2500);

Answer 19

Yes, because the 2500 value fails the where clause because of the WITH CHECK OPTION

Question 20

How many rows can be inserted with an INSERT with a VALUES clause?

Answer 20

Only 1 row

Question 21

What is the difference in the return between these 2 statements? DELETE FROM employees WHERE job_id = ‘SA_REP’ AND commission_pct < .2; DELETE FROM (SELECT * FROM employees) WHERE job_id = ‘SA_REP’ AND commission_pct < .2;

Answer 21

There is a subquery in the second statement, but the results are the same. Both will delete the same records

Question 22

Will the following statement produce and error? UPDATE (SELECT prod_id, cust_id, quantity_sold, time_id FROM sales) SET time_id = ‘22-MAR-2007’ WHERE cust_id = (SELECT cust_id FROM customers WHERE cust_last_name = ‘Roberts’ AND credit_limit = 600)

Answer 22

No. It would execute and restrict modificationsto only the columns specified in the SELECTstatement.The Values statement cannot have a direct subquery, but a field can have a subquery.

Question 23

What is this INSERT statement limiting? INSERT INTO (SELECT * FROM employees WHERE department_id in (20,50) WITH CHECK OPTION) VALUES (emp_id.NEXTVAL, ‘&ename’,’&jobid’,2000,Null,&did);

Answer 23

Limiting the user input to department IDs of 20 and 50 because of the WITH CHECK OPTION

Question 24

Will this statement execute without errors? DELETE * FROM tables where name = ‘Carrey’:

Answer 24

No.The * is not allowed

Question 25

Will this statement execute without errors? DELETE FROM employees WHERE employee_id = (SELECT employee_d from employees);

Answer 25

NoThe subquery returns more than one row.

Question 26

Will this statement execute without errors? UPDATE employee SET firstname = ‘John’ AND lastname = ‘Smith’ WHERE employee_id = 180;

Answer 26

No.Replace the AND with a comma and it will be correct.

Question 27

Will this statement execute without errors? INSERT INTO employees(name, hire_date) VALUES (‘John’,01-jan-11)

Answer 27

YesQuotes are not needed is the date format matches the default DD-MON-RR

Question 28

Will this statement cause and error? DELETE table1;

Answer 28

No.The FROM is not necessary.

Question 29

What is the difference between these two statements? INSERT INTO (SELECT depart_id, depart_name, loc_id FROM departments WHERE loc_id < 2000) VALUES (9999, ‘Entertainment’, 2500); INSERT INTO (SELECT depart_id, depart_name, loc_id FROM departments WHERE loc_id < 2000 WITH CHECK OPTION) VALUES (9999, ‘Entertainment’, 2500);

Answer 29

Neither will insert a record because the 2500 is greater than 2000.The first statement will be inserted, but the WITH CHECK OPTION block the insert and gives a syntax error.