Oracle 1z1-061 : Oracle Database 12c: SQL Fundamentals

There is a simple view SCOTT.DEPT_VIEW on the table SCOTT.DEPT.
This insert fails with an error:
SQL> insert into dept_view values('SUPPORT','OXFORD'); insert into dept_view values('SUPPORT','OXFORD')
* ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."DEPT"."DEPTNO")
What might be the problem?

• A. The view was created as WITH CHECK OPTION.
• B. The INSERT violates a constraint on the view.
• C. The INSERT violates a constraint on the detail table.
• D. The view was created as WITH READ ONLY.

Which three statements are true regarding sub queries? (Choose three.)

• A. Multiple columns or expressions can be compared between the main query and sub query
• B. Main query and sub query can get data from different tables
• C. Main query and sub query must get data from the same tables
• D. Sub queries can contain GROUP BY and ORDER BY clauses
• E. Only one column or expression can be compared between the main query and subqeury
• F. Sub queries can contain ORDER BY but not the GROUP BY clause

EMPLOYEES and DEPARTMENTS data:
EMPLOYEES

DEPARTMENTS

On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID managers and refers to the EMPLOYEE_ID.
On the DEPARTMENTS table DEPARTMENT_ID is the primary key.
Evaluate this UPDATE statement.
UPDATE employees
SET mgr_id
. (SELECT mgr_id
. FROM. employees
. WHERE dept_id
. (SELECT department_id
. FROM departments
. WHERE department_name = 'Administration')),
. Salary = (SELECT salary
. . FROM employees
. . WHERE emp_name = 'Smith')
WHERE job_id = 'IT_ADMIN';
What happens when the statement is executed?

• A. The statement fails because there is no 'Administration' department in the DEPARTMENTS table.
• B. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 4000 for the employees with ID 103 and 105.
• C. The statement fails because there is more than one row matching the employee name Smith.
• D. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 3000 for the employees with ID 103 and 105.
• E. The statement executes successfully, leaves the manager ID as the existing value, and changes the salary to 4000 for the employees with ID 103 and 105.
• F. The statement fails because there is more than one row matching the IT_ADMIN job ID in the EMPLOYEES table.

Evaluate the SQL statement:
TRUNCATE TABLE DEPT;
Which three are true about the SQL statement? (Choose three.)

• A. It releases the storage space used by the table.
• B. You can NOT roll back the deletion of rows after the statement executes.
• C. It does not release the storage space used by the table.
• D. You can roll back the deletion of rows after the statement executes.
• E. An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will display an error.
• F. You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT table

The CUSTOMERS table has these columns:

A promotional sale is being advertised to the customers in France.
Which WHERE clause identifies customers that are located in France?

• A. WHERE lower(country_address) LIKE %france%
• B. WHERE lower(country_address) = '%france%'
• C. WHERE lower(country_address) IS 'france'
• D. WHERE lower(country_address) = 'france'
• E. WHERE lower(country_address) = "france"