Oracle__7. Oracle 1Z0-051 Exam - Insert_Update_Delete Statements Flashcards
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’)
The record is inserted with a null for the customer_id
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)
The record is inserted with a null for the customer_id and a default date of today’s date
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)
The record is rejected but the is a null for Order_id
Are all fields required in an INSERT INTO statement?
NoBut all fields declared as NOT NULL must be populated in an INSERT statement
Are all columns that have NOT NULL constraints and defaults values required in an INSERT INTO statement?
No. All fields that have defaults declared will automatically be populated on an insert into statement.
What is the syntax for an insert statement if more than one record?
INSERT ALL INTO (field1, field2…) VALUES (exp1, exp2, …) INTO (field1, field2…) VALUES (exp1, exp2, …) INTO (field1, field2…) VALUES (exp1, exp2, …)SELECT * FROM dual;
What is the syntax of an INSERT statement where the field name list can be eliminated?
INSERT INTO table2 SELECT * FROM table1All the field names and data types must match and be in the same order between the 2 tables
What reason would a values clause be used in an insert statement?
to insert records using expressions or hard coded values that are not retrieved from other tables?
Is a constraint only enforced by an INSERT operation on the table?
NoA constraint is also enforced by an UPDATE operation on a table
The default format for a date column is DD-MON-RR, so on an insert can you insert 10-JANUARY-2014?
Yes.Months are implicitly convertedYears are implicitly converted
What will happen with this insert statement? Insert into table (id, statename) values (&id, ‘&statename’);
The user will be prompted for the ID then the statename.
What will happen with this insert statement? insert into table(id, statename) values (&id, ‘&&statename’)
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.
What can be used in place of the VALUES clause in an INSERT statement?
a select statement
Is there anything incorrect with this INSERT statement? UPDATE employees SET job = ‘IT’, commission = null where employee = 114;
Everything is syntactically correct.
The MERGE command the same as performing what 2 other commands?
UPDATE and INSERTThe Merge command will perform an UPDATE and then an INSERT
Can an update be performed on a view that does not have all the table columns defined with NOT NULL?
No.All NOT NULL columns must be in the view before the view will allow any updates to the table
What is the maximum number of tables in an Update that can be changed?
Only 1 table
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);
This statement is validThe records will be inserted even though the 2500 value fails the WHERE clause
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);
Yes, because the 2500 value fails the where clause because of the WITH CHECK OPTION
How many rows can be inserted with an INSERT with a VALUES clause?
Only 1 row
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;
There is a subquery in the second statement, but the results are the same. Both will delete the same records
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)
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.
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);
Limiting the user input to department IDs of 20 and 50 because of the WITH CHECK OPTION
Will this statement execute without errors? DELETE * FROM tables where name = ‘Carrey’:
No.The * is not allowed
Will this statement execute without errors? DELETE FROM employees WHERE employee_id = (SELECT employee_d from employees);
NoThe subquery returns more than one row.
Will this statement execute without errors? UPDATE employee SET firstname = ‘John’ AND lastname = ‘Smith’ WHERE employee_id = 180;
No.Replace the AND with a comma and it will be correct.
Will this statement execute without errors? INSERT INTO employees(name, hire_date) VALUES (‘John’,01-jan-11)
YesQuotes are not needed is the date format matches the default DD-MON-RR
Will this statement cause and error? DELETE table1;
No.The FROM is not necessary.
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);
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.
