- Home
- Database
- Oracle 12 C
- OCA Oracle 12 C SQl Fundamentals(1Z0-061)
96.
The following table describes the DEPARTMENTS table:
Which of the following INSERT statements will raise an exception?
You execute the following SQL code:
CREATE TABLE STATE_NEW AS SELECT * FROM STATE;
How many constraints will there be in the new table?
Which of the following INSERT statements will raise an exception? CREATE TABLE STATE_NEW AS SELECT * FROM STATE;
How many constraints will there be in the new table?
- A.INSERT INTO departments (dept_id, dept_name, location_id) VALUES(280,'Security',1700);
- B.INSERT INTO departments VALUES(280,'Security',1700);
- C.INSERT INTO departments VALUES(280,'Corporate Giving',266,1700);
- D.None of these statements will raise an exception.
- Answer & Explanation
- Report
Answer : [B]
Explanation :
Explanation :
| Option B will raise an exception because there are not enough column values for the implicit column list (all columns). |
97.
Refer to the DEPARTMENTS table structure in question 13. Two SQL statements are
shown here. Choose the option that best describes the SQL statements.
1.INSERT INTO departments (dept_id, dept_name, mgr_id) VALUES(280,'Security',1700);
2.INSERT INTO departments (dept_id, dept_name, mgr_id, location_id) VALUES(280,'Security',1700, NULL);
1.INSERT INTO departments (dept_id, dept_name, mgr_id) VALUES(280,'Security',1700);
2.INSERT INTO departments (dept_id, dept_name, mgr_id, location_id) VALUES(280,'Security',1700, NULL);
- A.Statements 1 and 2 insert the same values to all columns in the table.
- B.Statements 1 and 2 insert different values to at least one column in the table.
- C.The location_id column must be included in the column list of statement 1.
- D.A NULL value cannot be inserted explicitly in statement 2.
- Answer & Explanation
- Report
Answer : [B]
Explanation :
Explanation :
| Because the location_id column is defined with a default value of 99, statement 1 will insert 99 for location_id . In statement 2, a NULL is explicitly inserted into the location_id column; Oracle will not replace the NULL with the default value defined. |
98.
The SALES table contains the following data:
How many rows will be inserted into the NEW_CHANNEL_SALES table with the following
SQL statement?
- A.0
- B.12,000
- C.24,000
- D.36,000
- Answer & Explanation
- Report
Answer : [B]
Explanation :
Explanation :
| The FIRST clause tells Oracle to execute only the first WHEN clause that evaluates to TRUE for each row. Because no rows have a channel_id of C, no rows would be inserted into the catalog_sales table; 24,000 rows have a channel_id of I, so control would pass to the second WHEN clause 24,000 times, and the internet_sales table would get 24,000 rows. Because the second WHEN clause evaluates to TRUE and the INSERT FIRST option is specified, these rows would not make it to the third WHEN clause and would not be inserted into the new_channel_sales table. Had the INSERT ALL option been used, these 24,000 rows would also be inserted into the new_channel_sales table; 12,000 rows have a channel_id of T, so control would pass all the way to the third WHEN clause for these rows, and 12,000 rows would be inserted into new_channel_sales. |
99.
In the following SQL code, how many rows will be counted in the last statement?
- A.121
- B.0
- C.124
- D.143
- Answer & Explanation
- Report
Answer : [C]
Explanation :
Explanation :
| The TRUNCATE statement is DDL and performs an implicit commit. After the TRUNCATE statement is performed on the employees table, there will be 124 rows in the emp table. The one row that is inserted will be removed when the ROLLBACK statement is executed. |
100.
Which of the following options best describes the following SQL statement?
1. UPDATE countries
2. CNT_NAME = UPPER(CNT_NAME)
3. WHERE country_code BETWEEN 1 and 99;
1. UPDATE countries
2. CNT_NAME = UPPER(CNT_NAME)
3. WHERE country_code BETWEEN 1 and 99;
- A.The statement is missing the keyword SET , but the statement will work just fine because SET is an optional keyword.
- B.The BETWEEN operator cannot be used in the WHERE clause used in an UPDATE state- ment.
- C.The function UPPER(CNT_NAME) should be changed to UPPER('CNT_NAME') .
- D.The statement is missing keyword SET ; therefore, the statement will fail.
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
| You must have the SET keyword in an UPDATE statement. The BETWEEN operator and any other valid operators are allowed in the WHERE clause. |
|
|
||||||||||||||||||||||||||||
