Free Oracle 1z0-882 Exam Dumps Questions & Answers

A complex query consists of eight populated tables that are all connected via INNER JOIN operands as shown:

You modify the query and replace the SELECT operand with SELECT STRAIGHT JOIN.
What is the effect of adding STRAIGHT JOINs to the query?

• A.The optimizer ignores all terms in the WHERE clause until all JOINs have been completed.
• B.The optimizer processes only the JOINs in the sequence listed in the query.
• C.The optimizer will only JOIN the tables by using their PRIMARY KEYS or UNIQUE constraints.
• D.The optimizer will only JOIN the tables in sequence from smallest to largest.

The contents of the parent and child tables are:

The child table has the parent_id column that has a foreign key constraint to the id column of the parent table with
ON DELETE CASCADE clause.
Consider the command WHERE id =1;
What is the effect of the above command?

• A.It deletes one row from the parent table but does not affect the child table.
• B.It deletes one row from the parent table and sets the parent _id column to NULL in the child.
• C.It does not delete anything from any table but returns an error.
• D.It deletes one row from the parent table and two rows from the child table.

You attempt to create two new tables:
CREATE TABLE 'warehouse' (
'id' int (11) NOT NULL AUTO_INCREMENT,
'name' varchar (20) NOT NULL,
'phone' varchar (20) NOT NULL,
PRIMARY KEY (' id)
) ENGINE=MyISAM
CREATE TABLE 'warehouseitem' (
'warehouse_id' bigint (11) NOT NULL,
'item_id' int (11) NOT NULL,
'count' int(11) NOT NULL DEFAULT '0',
KEY "warehouse_id' ('warehouse-id) ,
FOREIGN KEY (warehouse_id) REFFERENCES warehouse (id)
) ENGINE= InnoDB
You get this error :
ERROR 1215 ( HYooo): cannot add foreign key constraint
Which two changes are required to permit these statements to execute without any error?

• A.The foreign key clause must be reversed: FOREIGN KEY warehouse(1)REFERENCES (warehouse-id).
• B.The warehouse_id' column must be renamed 'id' to match the definition on the 'warehouse' table.
• C.The data types of the 'warehouse'.'id' and ' warehouseitem.warehouse_is columns must match.
• D.The 'warehouseitem' table must be managed by the MySAm storage engine.
• E.The 'warehouse-table must be managed by the InnoDB storage engine.
• F.A UNIQUE key must be defined for the columns ('item_id','warehouse_id').

Inspect the query:
Mysql> SELECT count (emp_no) FROM titles WHERE title = 'senior staff';

How can this query be optimized?

• A.The query needs an index that includes the title column.
• B.The query cannot be optimized as count () must read all rows.
• C.The query need an index on the emp-no column.
• D.The query cannot be optimized as an index is already used.

Using the query:
SELECT Code FROM country WHERE Name = 'united states'
Which statement executed in the mysql client would create a prepared statement?

• A.PREPARE STATEMENT countrycode As 'SELECT code FROM country WHERE Name =?.;
• B.PREPARE countrycode As 'SELECT code FROM country WHERE Name =?.;
• C.PREPARE countrycode FROM 'SELECT code FROM country WHERE Name =?.;
• D.PREPARE STATEMENT countrycode FROM 'SELECT code FROM counyry WHERE Name =?.;