Simulado Prova 1Z0-071: Oracle Database OCA 12c

Este test está cronometrado.

Tienes 1 hora 21 minutos para completar 67 preguntas de este test.

Iniciar Test

Por favor espera - en proceso…

Pregunta 1 de 67

See the Exhibit and Examine the structure of the CUSTOMERS table:
<IMAGEM>
Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.
Which SQL statement would produce the required result?

Selecciona una de las siguientes respuestas posibles:

SELECT NVL(cust_credit_limit,'Not Available')*.15 "NEW CREDIT" FROM customers;
SELECT NVL(cust_credit_limit*.15,'Not Available') "NEW CREDIT" FROM customers;
SELECT TO_CHAR(NVL(cust_credit_limit*.15,'Not Available')) "NEW CREDIT" FROM customers;
SELECT NVL(TO_CHAR(cust_credit_limit*.15),'Not Available') "NEW CREDIT" FROM customers;

Explicación

Pregunta 2 de 67

Examine thestructure of the BOOKS_TRANSACTIONS table: You want to display the member IDs, due date, and late fee as $2 for all transactions.
<IMAGEM>
Which SQL statement must you execute?

Selecciona una de las siguientes respuestas posibles:

SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE FROM BOOKS_TRANSACTIONS;
SELECT member_id 'MEMBER_ID', due_date 'DUE_DATE', '$2 AS LATE_FEE' FROM BOOKS_TRANSACTIONS;
SELECT member_id AS "MEMBER_ID", due_date AS "DUE_DATE", '$2' AS " LATE_FEE" FROM BOOKS_TRANSACTIONS;
SELECT member_id AS "MEMBER_ID", due_date AS "DUE_DATE", $2 AS "LATE_FEE" FROM BOOKS_TRANSACTIONS;

Explicación

Pregunta 3 de 67

Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.)

Selecciona una o más de las siguientes respuestas posibles:

You can use column alias in the GROUP BY clause.
Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups.
The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause.
Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.
If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY clause.

Explicación

Pregunta 4 de 67

Which three statements are true regarding the data types?

Selecciona una o más de las siguientes respuestas posibles:

Only one LONG column can be used per table.
ATIMESTAMP data type column stores only time values with fractional seconds.
The BLOB data type column is used to store binary data in an operating system file.
The minimum column width that can be specified for a varchar2 data type column is one.
The value for a CHAR data type column is blank-padded to the maximum defined column width.

Explicación

Pregunta 5 de 67

Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS:

CREATE TABLE DEPARTMENT_DETAILS
(DEPARTMENT_ID NUMBER PRIMARY KEY,
DEPARTMENT_NAME VARCHAR2(50),
HOD VARCHAR2(50));

CREATE TABLE COURSE_DETAILS
(COURSE_ID NUMBER PRIMARY KEY,
COURSE_NAME VARCHAR2(50),
DEPARTMENT_ID NUMBER REFERENCES DEPARTMENT_DETAILS(DEPARTMENT_ID));

You want to generate a list of all department IDs along with any course IDs that may have been assigned to them.

Which SQL statement must you use?

Selecciona una de las siguientes respuestas posibles:

SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (d.department_id=c.deparment_id);
SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department_id=c.deparment_id);
SELECT d.department_id, c.course_id FROM course_details c LEFT OUTER JOIN department_details d ON (c.department_id=d.deparment_id);
SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (c.department_id=d.deparment_id);

Explicación

Pregunta 6 de 67

Which statements are true? (Choose all that apply.)

Selecciona una o más de las siguientes respuestas posibles:

The data dictionary is created and maintained by the database administrator.
The data dictionary views can consist of joins of dictionary base tables and user-defined tables.
The usernames of all the users including the database administrators are stored in the data dictionary.
The USER_CONS_COLUMNS view should be queried to find the names of the columns to which a constraint applies.
Both USER_OBJECTS and CAT views provide the same information about all the objects that are owned by the user.
Views with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary.

Explicación

Pregunta 7 de 67

Which two statements are true regarding the COUNT function? (Choose two.)

Selecciona una o más de las siguientes respuestas posibles:

COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns
COUNT(cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column
COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column
A SELECT statement using COUNT function with a DISTINCT keyword cannot have a WHERE clause
The COUNT function can be used only for CHAR, VARCHAR2 and NUMBER data types

Explicación

Pregunta 8 de 67

Which statement is true regarding the INTERSECT operator?

Selecciona una de las siguientes respuestas posibles:

It ignores NULL values
The number of columns and data types must be identical for all SELECT statements in the query
The names of columns in all SELECT statements must be identical
Reversing the order of the intersected tables the result

Explicación

Pregunta 9 de 67

The Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables.
<IMAGEM>
The CUSTOMERS table contains the current location of all currently active customers. The CUST_HISTORY
table stores historical details relating to any changes in the location of all current as well as previous
customers who are no longer active with the company.
You need to find those customers who have never changed their address.
Which SET operator would you use to get the required output?

Selecciona una de las siguientes respuestas posibles:

INTERSECT
UNION ALL
MINUS
UNION

Explicación

Pregunta 10 de 67

Evaluate the following SQL statements that are issued in the given order:
CREATE TABLE emp (emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY, enameVARCHAR2(15),
salary NUMBER(8,2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp);
ALTER TABLE emp
DISABLE CONSTRAINT emp_emp_no_pk CASCADE;
ALTER TABLE emp
ENABLE CONSTRAINT emp_emp_no_pk;
What would be the status of the foreign key EMP_MGR_FK?
Select One.

Selecciona una o más de las siguientes respuestas posibles:

It would be automatically enabled and deferred.
It would be automatically enabled and immediate.
It would remain disabled and has to be enabled manually using the ALTER TABLE command.
It would remain disabled and can be enabled only by dropping the foreign key constraint and recreating it.

Explicación

Pregunta 11 de 67

Which statements are correct regarding indexes? (Choose all that apply.)

Selecciona una o más de las siguientes respuestas posibles:

When a table is dropped, the corresponding indexes are automatically dropped.
For each DML operation performed, the corresponding indexes are automatically updated.
Indexes should be created on columns that are frequently referenced as part of an expression.
A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.

Explicación

Pregunta 12 de 67

View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables. ORDER__ID is the primary key in the ORDERS table. It is also the foreign key in the ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option.
<IMAGEM>
Which DELETE statement would execute successfully?

Selecciona una de las siguientes respuestas posibles:

DELETE order_id FROM orders WHERE order_total < 1000;
DELETE orders WHERE order_total < 1000;
DELETE FROM orders WHERE (SELECT order_id FROM order_items);
DELETE orders o, order_items i WHERE o.order id = i.order id;

Explicación

Pregunta 13 de 67

Which two statements are true regarding constraints? (Choose two.)

Selecciona una o más de las siguientes respuestas posibles:

A foreign key cannot contain NULL values.
A column with the UNIQUE constraint can contain NULL.
A constraint is enforced only for the INSERT operation on a table.
A constraint can be disabled even if the constraint column contains data.
All the constraints can be defined at the column level as well as the table level.

Explicación

Pregunta 14 de 67

The first DROP operation is performed on PRODUCTS table using the following command: DROP TABLE products PURGE;
Then you performed the FLASHBACK operation by using the following command:
FLASHBACK TABLE products TO BEFORE DROP;
Which statement describes the outcome of the FLASHBACK command?

Selecciona una de las siguientes respuestas posibles:

It recovers only the table structure.
It recovers the table structure, data, and the indexes.
It recovers the table structure and data but not the related indexes.
It is not possible to recover the table structure, data, or the related indexes.

Explicación

Pregunta 15 de 67

Examine the structure of the members table:
<IMAGEM>
You want to display details of all members who reside in states starting with the letter A followed by exactly one character. Which SQL statement must you execute?

Selecciona una de las siguientes respuestas posibles:

SELECT * FROM MEMBERS WHERE state LIKE '%A_* ;
SELECT * FROM MEMBERS WHERE state LIKE 'A_*;
SELECT * FROM MEMBERS WHERE state LIKE 'A_%';
SELECT * FROM MEMBERS WHERE state LIKE 'A%';

Explicación

Pregunta 16 de 67

Examine the following query:
<IMAGEM>
What is the output of this query?

Selecciona una de las siguientes respuestas posibles:

It displays 5 percent of the products with the highest amount sold.
It displays the first 5 percent of the rows from the SALES table.
It displays 5 percent of the products with the lowest amount sold.
It results in an error because the ORDER BY clause should be the last clause.

Explicación

Pregunta 17 de 67

Which of the following is NOT an aggregate function?

Selecciona una de las siguientes respuestas posibles:

SUM
MAX
LENGTH
COUNT
AVG

Explicación

Pregunta 18 de 67

You have a script that has the following query:

SELECT e1.Lname||' works for '||e2.Lname
"Workers and Their Supervisors"
FROM employees e1, employees e2
WHERE e1.ManagerId = e2.EmployeeId
AND e1.Lname LIKE 'T%'
ORDER BY e1.Lname;

What type of join is used in the script?

Selecciona una de las siguientes respuestas posibles:

Semijoin
Antijoin
Outer join
Self join

Explicación

Pregunta 19 de 67

Which privilege is an object privilege?

Selecciona una de las siguientes respuestas posibles:

CREATE INDEX
DROP USER
CREATE SESSION
SELECT ANY TABLE

Explicación

Pregunta 20 de 67

Which of the following objects are NOT contained in a schema? (Choose all that apply.)

Selecciona una o más de las siguientes respuestas posibles:

Roles
View
Table
Users
Index
Synonym
Tablespaces

Explicación

Pregunta 21 de 67

Examine the data in the product table.
<IMAGEM>
Evaluate this SELECT statement:

SELECT description, cost
FROM product
ORDER BY cost, quantity;

Which statements are true? (Choose all that apply.)

Selecciona una o más de las siguientes respuestas posibles:

The product_id value for the first record displayed is 220.
The product_id values for the last two rows displayed are 140 and 126.
The description value for the first two rows displayed is C 2pk-battery.
The description value for the first two rows displayed is AA 2pk-battery.
No row with a product_id of 220is displayed.

Explicación

Pregunta 22 de 67

Which three function descriptions are TRUE? (Choose three.)

Selecciona una o más de las siguientes respuestas posibles:

The SYSDATE function returns the local machine date and time.
The NVL single-row function can be used on VARCHAR2 columns.
The LENGTH character function returns the number of characters in an expression.
The ROUND number function returns a number rounded to the specified column value.
The TRUNC date function returns a date with the time portion of the day truncated to the specified format unit.
The SUBSTR character function replaces a portion of a string, beginning at a defined character position for a defined length.

Explicación

Pregunta 23 de 67

In which two of the following clauses of the SELECT statement can you use a subquery? (Choose two.)

Selecciona una o más de las siguientes respuestas posibles:

ORDER BY
FROM
WHERE
GROUP BY

Explicación

Pregunta 24 de 67

The EMPLOYEE table contains these columns:

EMP_ID NUMBER(9)
FNAME VARCHAR2(25)
LNAME VARCHAR(30)
SALARY NUMBER(7,2)
BONUS NUMBER(5,2)
DEPT_ID NUMBER(9)

You need to calculate the average bonus for all the employees in each department. The average should be calculated based on all the rows in the table, even if some employees do not receive a bonus.

Which group function should you use to calculate this value?

Selecciona una de las siguientes respuestas posibles:

AVG
SUM
MAX
MEAN
COUNT
AVERAGE

Explicación

Pregunta 25 de 67

Which two statements regarding the valid use of single-row and multiple-row subqueries are true? (Choose two.)

Selecciona una o más de las siguientes respuestas posibles:

Single-row subqueries can only be used in a WHERE clause.
Multiple-row subqueries can be used with the LIKE operator.
Single-row operators can only be used with single-row subqueries.
Single- and multiple-row subqueries can be used with the BETWEEN operator.
Multiple-row subqueries can be used with both single-row and multiple-row operators.
Multiple-row subqueries can be used in a WHERE clause and the INTO portion of an INSERT statement.

Explicación

Pregunta 26 de 67

Which set operator would you use to display the employee IDs of employees hired after January 10, 2007 in the employee table and employee IDs of employees who have held more than one position in the emp_hist table, eliminating any duplicate IDs?

Selecciona una de las siguientes respuestas posibles:

UNION
UNION ALL
INTERSECT
MINUS

Explicación

Pregunta 27 de 67

You issue the following command to drop the PRODUCTS table:
SQL > DROP TABLE products;
Which three statements are true about the implication of this command?

Selecciona una o más de las siguientes respuestas posibles:

All data along with the table structure is deleted.
A pending transaction in the session is committed.
All indexes on the table remain but they are invalidated.
All views and synonyms on the table remain but they are invalidated.
All data in the table is deleted but the table structure remains.

Explicación

Pregunta 28 de 67

You execute the following commands:
SQL > DEFINE hiredate = '01-APR-2011'
SQL >SELECT employee_id, first_name, salary
FROM employees
WHERE hire_date > '&hiredate'
AND manager_id > &mgr_id;
For which substitution variables are you prompted for the input?

Selecciona una de las siguientes respuestas posibles:

none, because no input required
both the substitution variables ''hiredate' and 'mgr_id'.
only 'hiredate'
only 'mgr_id'

Explicación

Pregunta 29 de 67

View the exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.
<IMAGEM>
You want to update EMPLOYEES table as follows:
Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
Set department_id for these employees to the department_id corresponding to London (location_id 2100).
Set the employees' salary in location_id 2100 to 1.1 times the average salary of their department.
Set the employees' commission in location_id 2100 to 1.5 times the average commission of their department.
You issue the following command:
SQL> UPDATE employees
SET department_id =
(SELECT department_id
FROM departments
WHERE location_id = 2100),
(salary, commission) =
(SELECT 1.1AVG(salary), 1.5AVG(commission)
FROM employees, departments
WHERE departments.location_id IN(2900, 2700, 2100))
WHERE department_id IN
(SELECT department_id
FROM departments
WHERE location_id = 2900
OR location_id = 2700;
What is outcome?

Selecciona una de las siguientes respuestas posibles:

It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an UPDATE statement.
It generates an error because a subquery cannot have a join condition in a UPDATE statement.
It executes successfully and gives the correct result.
It executes successfully but does not give the correct result.

Explicación

Pregunta 30 de 67

Evaluate the following two queries:
SQL> SELECT cust_last_name, cust_city
FROM customers
WHERE cust_credit_limit IN (1000, 2000, 3000);
SQL> SELECT cust_last_name, cust_city
FROM customers
WHERE cust_credit_limit = 1000 or cust_credit_limit = 2000 or
cust_credit_limit = 3000
Which statement is true regarding the above two queries?

Selecciona una de las siguientes respuestas posibles:

Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column.
There would be no change in performance.
Performance would degrade in query 2.
Performance would improve in query 2.

Explicación

Pregunta 31 de 67

Examine the business rule:
Each student can take up multiple projects and each project can have multiple students.
You need to design an Entity Relationship Model (ERD) for optimal data storage and allow for generating reports in this format:
STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK
Which two statements are true in this scenario?

Selecciona una o más de las siguientes respuestas posibles:

The ERD must have a 1:M relationship between the STUDENTS and PROJECTS entities.
The ERD must have a M:M relationship between the STUDENTS and PROJECTS entities that must be resolved into 1:M relationships.
STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity.
PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity.
An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the STUDENTS and
PROJECTS entities.

Explicación

Pregunta 32 de 67

View the Exhibit and examine the details of PRODUCT_INFORMATION table.
<IMAGEM>
You have the requirement to display PRODUCT_NAME from the table where the CATEGORY_ID column has values 12 or 13, and the SUPPLIER_ID column has the
value 102088. You executed the following SQL statement:
SELECT product_name
FROM product_information
WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088;
Which statement is true regarding the execution of the query?

Selecciona una de las siguientes respuestas posibles:

It would not execute because the same column has been used in both sides of the AND logical operator to form the condition.
It would not execute because the entire WHERE clause condition is not enclosed within the parentheses.
It would execute and the output would display the desired result.
It would execute but the output would return no rows.

Explicación

Pregunta 33 de 67

Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)

Selecciona una o más de las siguientes respuestas posibles:

The outer query stops evaluating the result set of the inner query when the first value is found.
It is used to test whether the values retrieved by the inner query exist in the result of the outer query.
It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.
The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.

Explicación

Pregunta 34 de 67

View the exhibit and examine the structure of the STORES table.
<IMAGEM>
You want to display the NAME of the store along with the ADDRESS, START_DATE, PROPERTY_PRICE, and the projected property price, which is 115% of property
price.
The stores displayed must have START_DATE in the range of 36 months starting from 01-Jan-2000 and above.
Which SQL statement would get the desired output?

Selecciona una de las siguientes respuestas posibles:

SELECT name, concat (address| | ','| |city| |', ', country) AS full_address,
start_date,
property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, '01-JAN-2000') <=36;
SELECT name, concat (address| | ','| |city| |', ', country) AS full_address,
start_date,
property_price, property_price*115/100
FROM stores
WHERE TO_NUMBER(start_date-TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;
SELECT name, address||','||city||','||country AS full_address,
start_date,
property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;
SELECT name, concat (address||','| |city| |', ', country) AS full_address,
start_date,
property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;

Explicación

Pregunta 35 de 67

The BOOKS_TRANSACTIONS table exists in your database.
SQL>SELECT * FROM books_transactions ORDER BY 3;
What is the outcome on execution?

Selecciona una de las siguientes respuestas posibles:

The execution fails unless the numeral 3 in the ORDER BY clause is replaced by a column name.
Rows are displayed in the order that they are stored in the table only for the three rows with the lowest values in the key column.
Rows are displayed in the order that they are stored in the table only for the first three rows.
Rows are displayed sorted in ascending order of the values in the third column in the table.

Explicación

Pregunta 36 de 67

Examine the command:
SQL> ALTER TABLE books_transactions
ADD CONSTRAINT fk_book_id FOREIGN KEY (book_id)
REFERENCES books (book_id) ON DELETE CASCADE;
What does ON DELETE CASCADE imply?

Selecciona una de las siguientes respuestas posibles:

When the BOOKS table is dropped, the BOOK_TRANSACTIONS table is dropped.
When the BOOKS table is dropped, all the rows in the BOOK_TRANSACTIONS table are deleted but the table structure is retained.
When a row in the BOOKS table is deleted, the rows in the BOOK_TRANSACTIONS table whose BOOK_ID matches that of the deleted row in the BOOKS table are
also deleted.
When a value in the BOOKS.BOOK_ID column is deleted, the corresponding value is updated in the BOOKS_TRANSACTIONS.BOOK_ID column.

Explicación

Pregunta 37 de 67

View the exhibit and examine the structure of the EMPLOYEES table.
<IMAGEM>
You want to display all employees and their managers having 100 as the MANAGER_ID. You want the output in two columns: the first column would have the
LAST_NAME of the managers and the second column would have LAST_NAME of the employees.
Which SQL statement would you execute?

Selecciona una de las siguientes respuestas posibles:

SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON m.employee_id = e.manager_id
WHERE m.manager_id = 100;
SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON m.employee_id = e.manager_id
WHERE e.manager_id = 100;
SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON e.employee_id = m.manager_id
WHERE m.manager_id = 100;
SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
WHERE m.employee_id = e.manager_id and AND e.manager_id = 100;

Explicación

Pregunta 38 de 67

Which three statements are true about multiple-row subqueries?

Selecciona una o más de las siguientes respuestas posibles:

They can contain a subquery within a subquery.
They can return multiple columns as well as rows.
They cannot contain a subquery within a subquery.
They can return only one column but multiple rows.
They can contain group functions and GROUP BY and HAVING clauses.
They can contain group functions and the GROUP BY clause, but not the HAVING clause.

Explicación

Pregunta 39 de 67

Examine the structure of the EMPLOYEES table.
<IMAGEM>
There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID.
You want to display the last names and manager IDs of employees who work for the same manager as the employee whose EMPLOYEE_ID is 123.
Which query provides the correct output?

Selecciona una de las siguientes respuestas posibles:

SELECT e.last_name, m.manager_id
FROM employees e RIGHT OUTER JOIN employees m
on (e.manager_id = m.employee_id)
AND e.employee_id = 123;
SELECT e.last_name, m.manager_id
FROM employees e RIGHT OUTER JOIN employees m
on (e.employee_id = m.manager_id)
WHERE e.employee_id = 123;
. SELECT e.last_name, e.manager_id
FROM employees e RIGHT OUTER JOIN employees m
on (e.employee_id = m.employee_id)
WHERE e.employee_id = 123;
SELECT m.last_name, e.manager_id
FROM employees e LEFT OUTER JOIN employees m
on (e.manager_id = m.manager_id)
WHERE e.employee_id = 123;

Explicación

Pregunta 40 de 67

Which normal form is a table in if it has no multi-valued attributes and no partial
dependencies?

Selecciona una de las siguientes respuestas posibles:

second normal form
first normal form
third normal form
fourth normal form

Explicación

Pregunta 41 de 67

Sales data of a company is stored in two tables, SALES1 and SALES2, with some data being duplicated across the tables. You want to display the results from the SALES1 table, which are not present in the SALES2 table.

Which set operator generates the required output?

Selecciona una de las siguientes respuestas posibles:

INTERSECT
UNION
PLUS
MINUS
SUBTRACT

Explicación

Pregunta 42 de 67

Evaluate the following ALTER TABLE statement:
ALTER TABLE orders
SET UNUSED (order_date);
Which statement is true?

Selecciona una de las siguientes respuestas posibles:

After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table.
he ORDER_DATE column should be empty for the ALTER TABLE command to execute succsessfully.
ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.
The DESCRIBE command would still display the ORDER_DATE column.

Explicación

Pregunta 43 de 67

Which three statements are true regarding subqueries?

Selecciona una o más de las siguientes respuestas posibles:

Multiple columns or expressions can be compared between the main query and subquery.
Subqueries can contain ORDER BY but not the GROUP BY clause.
Main query and subquery can get data from different tables.
Subqueries can contain GROUP BY and ORDER BY clauses.
Main query and subquery must get data from the same tables.
Only one column or expression can be compared between the main query and subquery.

Explicación

Pregunta 44 de 67

Which statement is true regarding the default behavior of the ORDER BY clause?

Selecciona una de las siguientes respuestas posibles:

In a character sort, the values are case-sensitive.
NULL values are not considered at all by the sort operation.
Only those columns that are specified in the SELECT list can be used in the ORDER BY clause.
Numeric values are displayed from the maximum to the minimum value if they have decimal positions.

Explicación

Pregunta 45 de 67

Examine the structure of the MEMBERS table.
<IMAGEM>
Which query can be used to display the last names and city names only for members from the states MO and MI?

Selecciona una de las siguientes respuestas posibles:

SELECT last_name, city FROM members WHERE state ='MO' AND state ='MI';
SELECT last_name, city FROM members WHERE state LIKE 'M%';
SELECT last_name, city FROM members WHERE state IN ('MO', 'MI');
SELECT DISTINCT last_name, city FROM members WHERE state ='MO' OR state ='MI';

Explicación

Pregunta 46 de 67

Which task can be performed by using a single Data Manipulation Language (DML) statement?

Selecciona una de las siguientes respuestas posibles:

adding a column constraint when inserting a row into a table
adding a column with a default value when inserting a row into a table
removing all data only from one single column on which a unique constraint is defined
removing all data only from one single column on which a primary key constraint is defined

Explicación

Pregunta 47 de 67

View the exhibit and examine the ORDERS table
<IMAGEM>
The ORDERS table contains data and all orders have been assigned a customer ID. Which statement would add a NOT NULL constraint to the CUSTOMER_ID
column?

Selecciona una de las siguientes respuestas posibles:

ALTER TABLE orders
MODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
ALTER TABLE orders
ADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
ALTER TABLE orders
MODIFY customer_id CONSTRAINT orders_cust_nn NOT NULL (customer_id);
ALTER TABLE orders
ADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;

Explicación

Pregunta 48 de 67

Examine the structure of the INVOICE table.
<IMAGEM>
Which two SQL statements would execute successfully?

Selecciona una o más de las siguientes respuestas posibles:

SELECT inv_no, NVL2(inv_date, 'Pending', 'Incomplete')
FROM invoice;
SELECT inv_no, NVL2(inv_amt, inv_date, 'Not Available')
FROM invoice;
SELECT inv_no, NVL2(inv_date, sysdate-inv_date, sysdate)
FROM invoice;
SELECT inv_no, NVL2(inv_amt, inv_amt*.25, 'Not Available')
FROM invoice;

Explicación

Pregunta 49 de 67

Which three statements are true about the ALTER TABLE....DROP COLUMN.... command?

Selecciona una o más de las siguientes respuestas posibles:

A column can be dropped only if it does not contain any data.
A column can be dropped only if another column exists in the table.
A dropped column can be rolled back.
The column in a composite PRIMARY KEY with the CASCADE option can be dropped.
A parent key column in the table cannot be dropped.

Explicación

Pregunta 50 de 67

View the exhibit and examine the description of the PRODUCT_INFORMATION table.
<IMAGEM>
Which SQL statement would retrieve from the table the number of products having LIST_PRICE as NULL?

Selecciona una de las siguientes respuestas posibles:

SELECT COUNT (DISTINCT list_price)
FROM product_information
WHERE list_price is NULL
SELECT COUNT (NVL(list_price, 0))
FROM product_information
WHERE list_price is NULL
SELECT COUNT (list_price)
FROM product_information
WHERE list_price i= NULL
SELECT COUNT (list_price)
FROM product_information
WHERE list_price is NULL

Explicación

Pregunta 51 de 67

Which three tasks can be performed using SQL functions built into Oracle Database?

Selecciona una o más de las siguientes respuestas posibles:

displaying a date in a nondefault format
finding the number of characters in an expression
substituting a character string in a text expression with a specified string
combining more than two columns or expressions into a single column in the output

Explicación

Pregunta 52 de 67

The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues the following GRANT command:
GRANT ALL
ON orders, order_items
TO PUBLIC;
What correction needs to be done to the above statement?

Selecciona una de las siguientes respuestas posibles:

PUBLIC should be replaced with specific usernames.
ALL should be replaced with a list of specific privileges.
WITH GRANT OPTION should be added to the statement.
Separate GRANT statements are required for ORDERS and ORDER_ITEMS tables.

Explicación

Pregunta 53 de 67

You are designing the structure of a table in which two columns have the specifications:
COMPONENT_ID – must be able to contain a maximum of 12 alphanumeric characters and uniquely identify the row
EXECUTION_DATETIME – contains Century, Year, Month, Day, Hour, Minute, Second to the maximum precision and is used for calculations and comparisons
between components.
Which two options define the data types that satisfy these requirements most efficiently?

Selecciona una o más de las siguientes respuestas posibles:

The EXECUTION_DATETIME must be of INTERVAL DAY TO SECOND data type.
The EXECUTION_DATETIME must be of TIMESTAMP data type.
The EXECUTION_DATETIME must be of DATE data type.
The COMPONENT_ID must be of ROWID data type.
The COMPONENT_ID must be of VARCHAR2 data type.
The COMPONENT_ID column must be of CHAR data type.

Explicación

Pregunta 54 de 67

You want to display the date for the first Monday of the next month and issue the following command:
SQL>SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE), 'MON'),
'dd "is the first Monday for" fmmonth rrrr')
FROM DUAL;
What is the outcome?

Selecciona una de las siguientes respuestas posibles:

In generates an error because rrrr should be replaced by rr in the format string.
It executes successfully but does not return the correct result.
It executes successfully and returns the correct result.
In generates an error because TO_CHAR should be replaced with TO_DATE.
In generates an error because fm and double quotation marks should not be used in the format string.

Explicación

Pregunta 55 de 67

Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.)

Selecciona una o más de las siguientes respuestas posibles:

You can use column alias in the GROUP BY clause.
Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups.
The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause.
Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.
If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY cause.

Explicación

Pregunta 56 de 67

View the exhibit for the structure of the STUDENT and FACULTY tables.
<IMAGEM>
You need to display the faculty name followed by the number of students handled by the faculty at the base location.
Examine the following two SQL statements:
Statement 1
SQL>SELECT faculty_name, COUNT(student_id)
FROM student JOIN faculty
USING (faculty_id, location_id)
GROUP BY faculty_name;
Statement 2
SQL>SELECT faculty_name, COUNT(student_id)
FROM student NATURAL JOIN faculty
GROUP BY faculty_name;
Which statement is true regarding the outcome?

Selecciona una de las siguientes respuestas posibles:

Only statement 2 executes successfully and gives the required result.
Only statement 1 executes successfully and gives the required result.
Both statements 1 and 2 execute successfully and give different results.
Both statements 1 and 2 execute successfully and give the same required result.

Explicación

Pregunta 57 de 67

Which two tasks can be performed by using Oracle SQL statements?

Selecciona una o más de las siguientes respuestas posibles:

changing the password for an existing database user.
connecting to a database instance
querying data from tables across databases
starting up a database instance
executing operating system (OS) commands in a session

Explicación

Pregunta 58 de 67

Which statement correctly grants a system privilege?

Selecciona una de las siguientes respuestas posibles:

GRANT CREATE VIEW
ON table1 TO
user1;
GRANT ALTER TABLE
TO PUBLIC;
GRANT CREATE TABLE
TO user1, user2;
GRANT CREATE SESSION
TO ALL;

Explicación

Pregunta 59 de 67

View the exhibit and examine the structure of ORDERS and CUSTOMERS tables.
<IMAGEM>
Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?
Assume there exists only one row with CUST_LAST_NAME as Roberts and CREDIT_LIMIT as 600.

Selecciona una de las siguientes respuestas posibles:

INSERT INTO (SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_total
FROM orders o, customers c
WHERE o.customer_id = c.customer_id AND c.cust_last_name='Roberts' AND c.credit_limit=600)
VALUES (1,'10-mar-2007', 'direct', (SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
INSERT INTO orders (order_id, order_date, order_mode,
(SELECT customer id
FROM customers
WHERE cust_last_name='Roberts' AND credit_limit=600), order_total);
VALUES (1,'10-mar-2007', 'direct', &customer_id, 1000);
INSERT INTO orders
VALUES (1,'10-mar-2007', 'direct',
(SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
INSERT INTO orders (order_id, order_date, order_mode,
(SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND credit_limit=600), order_total);
VALUES (1,'10-mar-2007', 'direct', &customer_id, 1000);

Explicación

Pregunta 60 de 67

View the exhibit and examine the description of the DEPARTMENTS and EMPLOYEES tables.
<IMAGEM>
The retrieve data for all the employees for their EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT NAME, the following SQL statement was written:
SELECT employee_id, first_name, department_name
FROM employees
NATURAL JOIN departments;
The desired output is not obtained after executing the above SQL statement. What could be the reason for this?

Selecciona una de las siguientes respuestas posibles:

The table prefix is missing for the column names in the SELECT clause.
The NATURAL JOIN clause is missing the USING clause.
The DEPARTMENTS table is not used before the EMPLOYEES table in the FROM clause.
The EMPLOYEES and DEPARTMENTS tables have more than one column with the same column name and data type.

Explicación

Pregunta 61 de 67

Which two statements are true about sequences created in a single instance database? (Choose two.)

Selecciona una o más de las siguientes respuestas posibles:

When the MAXVALUE limit for the sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement.
DELETE <sequencename> would remove a sequence from the database.
The numbers generated by a sequence can be used only for one table.
CURRVAL is used to refer to the last sequence number that has been generated.
When a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted.

Explicación

Pregunta 62 de 67

View the exhibit and examine the structure of the CUSTOMERS table.
<IMAGEM>
Which two tasks would require subqueries or joins to be executed in a single statement?

Selecciona una o más de las siguientes respuestas posibles:

finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
listing of customers who do not have a credit limit and were born before 1980
finding the number of customers, in each city, who’s marital status is 'married'.
listing of those customers, whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'.

Explicación

Pregunta 63 de 67

Which statement is true about transactions?

Selecciona una de las siguientes respuestas posibles:

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.
A combination of DDL and DML statements executed in a sequence ending with a COMMIT forms a single transaction.

Explicación

Pregunta 64 de 67

View the exhibit and examine the structure in ORDERS and ORDER_ITEMS tables.
<IMAGEM>
You need to create a view that displays the ORDER_ID, ORDER_DATE, and the total number of items in each order.
Which CREATE VIEW statement would create the views successfully?

Selecciona una de las siguientes respuestas posibles:

CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id, o.order_date;
CREATE OR REPLACE VIEW ord_vu (order_id, order_date)
AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)
"NO OF ITEMS"
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id, o.order_date;
. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)
"NO OF ITEMS"
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id, o.order_date;
CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) ||
"NO OF ITEMS"
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
WHITH CHECK OPTION;

Explicación

Pregunta 65 de 67

Which statement is true about an inner join specified in the WHERE clause of a query?

Selecciona una de las siguientes respuestas posibles:

It must have primary-key and foreign-key constraints defined on the columns used in the join condition.
It requires the column names to be the same in all tables used for the join conditions.
It is applicable for equijoin and nonequijoin conditions.
It is applicable for only equijoin conditions.

Explicación

Pregunta 66 de 67

Which statement is true regarding the INTERSECT operator?

Selecciona una de las siguientes respuestas posibles:

The names of columns in all SELECT statements must be identical.
It ignores NULL values.
Reversing the order of the intersected tables alters the result.
The number of columns and data types must be identical for all SELECT statements in the query.

Explicación

Pregunta 67 de 67

Examine the following query:
SQL> SELECT prod_id, amount_sold
FROM sales
ORDER BY amount_sold
FETCH FIRST 5 PERCENT ROWS ONLY;
What is the output of this query?

Selecciona una de las siguientes respuestas posibles:

It displays 5 percent of the products with the highest amount sold.
It displays the first 5 percent of the rows from the SALES table.
It displays 5 percent of the products with the lowest amount sold.
It results in an error because the ORDER BY clause should be the last clause.

Explicación

Revisar respuesta

["https://cdn.goconqr.com/uploads/media/image/11586276/desktop_2cb5d1e8-e49b-40f8-accc-62d854e0faea.png","https://cdn.goconqr.com/uploads/media/image/11619366/desktop_23771751-ff5a-4749-bc60-b346b93975dd.png","https://cdn.goconqr.com/uploads/media/image/11598838/desktop_e3fee04f-7f54-49be-b406-d58fc4d49a75.png","https://cdn.goconqr.com/uploads/media/image/11598982/desktop_4001e64d-03ab-4a23-ad31-20183e0a240b.png","https://cdn.goconqr.com/uploads/media/image/11599078/desktop_a194fe47-b149-42a7-813d-28d35eee47b5.png","https://cdn.goconqr.com/uploads/media/image/11599125/desktop_1bb69956-68df-48ab-8dc5-0313f2ee0875.png","https://cdn.goconqr.com/uploads/media/image/11599446/desktop_172cfa12-f6dc-49f1-acb6-9955671c54f9.png","https://cdn.goconqr.com/uploads/media/image/11624620/desktop_90eee86b-543e-4b97-a7ae-884164e24b3c.png","https://cdn.goconqr.com/uploads/media/image/11627381/desktop_d1564cfe-83cd-4f97-bba8-5204c4ea8348.png","https://cdn.goconqr.com/uploads/media/image/11678393/desktop_7cfb88b9-005d-45cd-840d-dd759be813fc.png","https://cdn.goconqr.com/uploads/media/image/11678575/desktop_b293ff47-2da3-445f-b642-fc4b8eb198c4.png","https://cdn.goconqr.com/uploads/media/image/11708547/desktop_3d8364a4-ba40-4691-b74d-8d3616864e98.png","https://cdn.goconqr.com/uploads/media/image/11744992/desktop_ec0df42e-8502-4b30-8508-bef5a5108cfa.png","https://cdn.goconqr.com/uploads/media/image/11745105/desktop_5e8eee81-e001-4b27-8ba0-3c049a80c2a7.png","https://cdn.goconqr.com/uploads/media/image/11620136/desktop_36c9c76d-239d-41f9-b47a-8053da8fa5a1.png","https://cdn.goconqr.com/uploads/media/image/11776770/desktop_628115bd-cf11-4b34-abac-e8e40bf7d0f7.png","https://cdn.goconqr.com/uploads/media/image/11598838/desktop_e3fee04f-7f54-49be-b406-d58fc4d49a75.png","https://cdn.goconqr.com/uploads/media/image/11786021/desktop_c8899a21-d760-438b-af83-a6406bbf7448.png","https://cdn.goconqr.com/uploads/media/image/11796042/desktop_1451eb01-cdd3-46bc-ac1a-197970752462.png","https://cdn.goconqr.com/uploads/media/image/11796270/desktop_b50ffd2d-b535-4387-a3a3-b3ca81661dc4.png","https://cdn.goconqr.com/uploads/media/image/11796342/desktop_7ab98854-6203-47d0-a6b3-67d1e9ce02d2.png"]

	Creado por Jean Krüger hace casi 7 años

Test sobre Simulado Prova 1Z0-071: Oracle Database OCA 12c, creado por Jean Krüger el 12/09/2017.

Simulado Prova 1Z0-071: Oracle Database OCA 12c

Pregunta 1 de 67

Selecciona una de las siguientes respuestas posibles:

Explicación

Pregunta 2 de 67

Examine thestructure of the BOOKS_TRANSACTIONS table: You want to display the member IDs, due date, and late fee as $2 for all transactions. <IMAGEM> Which SQL statement must you execute?

Selecciona una de las siguientes respuestas posibles:

Explicación

Pregunta 3 de 67

Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.)

Selecciona una o más de las siguientes respuestas posibles:

Explicación

Pregunta 4 de 67

Which three statements are true regarding the data types?

Selecciona una o más de las siguientes respuestas posibles:

Explicación

Pregunta 5 de 67

Selecciona una de las siguientes respuestas posibles:

Explicación

Pregunta 6 de 67

Which statements are true? (Choose all that apply.)

Selecciona una o más de las siguientes respuestas posibles:

Explicación

Pregunta 7 de 67

Which two statements are true regarding the COUNT function? (Choose two.)

Selecciona una o más de las siguientes respuestas posibles:

Explicación

Pregunta 8 de 67

Which statement is true regarding the INTERSECT operator?

Selecciona una de las siguientes respuestas posibles:

Explicación

Pregunta 9 de 67

Selecciona una de las siguientes respuestas posibles:

Explicación

Pregunta 10 de 67

Selecciona una o más de las siguientes respuestas posibles:

Explicación

Pregunta 11 de 67

Which statements are correct regarding indexes? (Choose all that apply.)

Selecciona una o más de las siguientes respuestas posibles:

Explicación

Pregunta 12 de 67

View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables. ORDER__ID is the primary key in the ORDERS table. It is also the foreign key in the ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option. <IMAGEM> Which DELETE statement would execute successfully?

Selecciona una de las siguientes respuestas posibles:

Explicación

Pregunta 13 de 67

Which two statements are true regarding constraints? (Choose two.)

Selecciona una o más de las siguientes respuestas posibles:

Explicación

Pregunta 14 de 67

The first DROP operation is performed on PRODUCTS table using the following command: DROP TABLE products PURGE; Then you performed the FLASHBACK operation by using the following command: FLASHBACK TABLE products TO BEFORE DROP; Which statement describes the outcome of the FLASHBACK command?

Selecciona una de las siguientes respuestas posibles:

Explicación

Pregunta 15 de 67

Examine the structure of the members table: <IMAGEM> You want to display details of all members who reside in states starting with the letter A followed by exactly one character. Which SQL statement must you execute?

Selecciona una de las siguientes respuestas posibles:

Explicación

Pregunta 16 de 67

Examine the following query: <IMAGEM> What is the output of this query?

Selecciona una de las siguientes respuestas posibles:

Explicación

Pregunta 17 de 67

Which of the following is NOT an aggregate function?

Selecciona una de las siguientes respuestas posibles:

Explicación

Pregunta 18 de 67

You have a script that has the following query: SELECT e1.Lname||' works for '||e2.Lname "Workers and Their Supervisors" FROM employees e1, employees e2 WHERE e1.ManagerId = e2.EmployeeId AND e1.Lname LIKE 'T%' ORDER BY e1.Lname; What type of join is used in the script?

Selecciona una de las siguientes respuestas posibles:

Explicación

Pregunta 19 de 67

Which privilege is an object privilege?

Selecciona una de las siguientes respuestas posibles:

Explicación

Pregunta 20 de 67

Which of the following objects are NOT contained in a schema? (Choose all that apply.)

Selecciona una o más de las siguientes respuestas posibles:

Explicación

Pregunta 21 de 67

Examine the data in the product table. <IMAGEM> Evaluate this SELECT statement: SELECT description, cost FROM product ORDER BY cost, quantity; Which statements are true? (Choose all that apply.)

Examine thestructure of the BOOKS_TRANSACTIONS table: You want to display the member IDs, due date, and late fee as $2 for all transactions.
<IMAGEM>
Which SQL statement must you execute?

View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables. ORDER__ID is the primary key in the ORDERS table. It is also the foreign key in the ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option.
<IMAGEM>
Which DELETE statement would execute successfully?

The first DROP operation is performed on PRODUCTS table using the following command: DROP TABLE products PURGE;
Then you performed the FLASHBACK operation by using the following command:
FLASHBACK TABLE products TO BEFORE DROP;
Which statement describes the outcome of the FLASHBACK command?

Examine the structure of the members table:
<IMAGEM>
You want to display details of all members who reside in states starting with the letter A followed by exactly one character. Which SQL statement must you execute?

Examine the following query:
<IMAGEM>
What is the output of this query?

You have a script that has the following query:

SELECT e1.Lname||' works for '||e2.Lname
"Workers and Their Supervisors"
FROM employees e1, employees e2
WHERE e1.ManagerId = e2.EmployeeId
AND e1.Lname LIKE 'T%'
ORDER BY e1.Lname;

What type of join is used in the script?

Examine the data in the product table.
<IMAGEM>
Evaluate this SELECT statement:

SELECT description, cost
FROM product
ORDER BY cost, quantity;

Which statements are true? (Choose all that apply.)

You issue the following command to drop the PRODUCTS table:
SQL > DROP TABLE products;
Which three statements are true about the implication of this command?

You execute the following commands:
SQL > DEFINE hiredate = '01-APR-2011'
SQL >SELECT employee_id, first_name, salary
FROM employees
WHERE hire_date > '&hiredate'
AND manager_id > &mgr_id;
For which substitution variables are you prompted for the input?

The BOOKS_TRANSACTIONS table exists in your database.
SQL>SELECT * FROM books_transactions ORDER BY 3;
What is the outcome on execution?

Examine the command:
SQL> ALTER TABLE books_transactions
ADD CONSTRAINT fk_book_id FOREIGN KEY (book_id)
REFERENCES books (book_id) ON DELETE CASCADE;
What does ON DELETE CASCADE imply?