1z0-071 Oracle Database 12c SQL Questions and Answers

Questions 4

Examine the description of the CUSTOMERS table:

Which three statements will do an implicit conversion?

Options:

SELECT * FROM customers WHERE insert_date=DATE’2019-01-01’;

SELECT * FROM customers WHERE customer_id=’0001’;

SELECT * FROM customers WHERE TO_DATE(insert_date)=DATE’2019-01-01’;

SELECT * FROM customers WHERE insert_date’01-JAN-19’;

SELECT * FROM customers WHERE customer_id=0001;

SELECT * FROM customers WHERE TO_CHAR(customer_id)=’0001’;

Buy Now

Questions 5

Which two statements cause changes to the data dictionary?

Options:

DELETE FROM scott. emp;

GRANT UPDATE ON scott. emp TO fin manager;

AITER SESSION set NLs. _DATE FORMAT = 'DD/MM/YYYY';

TRUNCATE TABLE emp:

SELECT * FROM user_ tab._ privs;

Buy Now

Questions 6

which three statements are true about indexes and their administration in an Oracle database?

Options:

The same table column can be part of a unique and non-unique index

A DESCENDING INDEX IS A type of function-based index

A DROP INDEX statement always prevents updates to the table during the drop operation

AN INVISIBLE INDEX is not maintained when DML is performed on its underlying table.

AN INDEX CAN BE CREATED AS part of a CREATE TABLE statement

IF a query filters on an indexed column then it will always be used during execution of query

Buy Now

Questions 7

Examine this list of requirements for a sequence:

1. Name:EMP_SEQ

2. First value returned:1

3. Duplicates are never permitted.

4. Provide values to be inserted into the EMPLOYEES.EMPLOYEE_ID COLUMN.

5. Reduce the chances of gaps in the values.

Which two statements will satisfy these requirements?

Options:

CREATE SEQUENCE emp_seq START WITH 1 INCRENENT BY 1 NOCACHE;

CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CYCLE;

CREATE SEQUENCE emp_seq NOCACHE;

CREATE SEQUENCE emp_seq START WITH 1 CACHE;

CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CACHE;

CREATE SEQUENCE emp_seq;

Buy Now

Questions 8

For each employee in department 90 you want to display:

1. their last name

2. the number of complete weeks they have been employed

The output must be sorted by the number of weeks, starting with the longest serving employee

first.Which statement will accomplish this?

Options:

SELECT last_name, TRUNC( (SYSDATE - hire_ date) 1 7) AS tenure

FROM employees

WHERE department_ id = 90

ORDER BY tenure ;

SELECT last_name, ROUND( (SYSDATE - hire_ date) 1 7) AS tenure

FROM employees

WHERE department_ id = 90

ORDER BY tenure ;

SELECT last_name, ROUND( (SYSDATE - hire_ date) 17) AS tenure

FROM employees

WHERE department_ id = 90

ORDER BY tenure DESC;

SELECT last_name, TRUNC ( (SYSDATE - - hire_ date) 1 7) AS tenure

FROM employees

WHERE department_id = 90

ORDER BY tenure DESC;

Buy Now

Questions 9

Which two statements are true regarding the UNION ALL operators?

Options:

NULLS are not ignored during duplicate checking.

Duplicates are eliminated automatically by the UNION ALL operator

The names of columns selected in each SELECT statement must be identical.

The number of columns selected in each SELECT statement must be identical

The output is sorted by the UNION ALL operator.

Buy Now

Questions 10

Which two statements are true about the SET VERIFY ON command?

Options:

It displays values for variables created by the DEFINE command.

It can be used in SQL Developer and SQL*Plus.

It can be used only in SQL*plus.

It displays values for variables prefixed with &&.

It displays values for variables used only in the WHERE clause of a query.

Buy Now

Questions 11

Which two are true about the USING clause when joining tables?

Options:

All column names in a USING clause must be qualified with a table name or table alias.

It can never be used with onatural join.

It is used to specify an equijoin of columns that have the same name in both tables.

It can never be used with a full outer join.

It is used to specify an explicit join condition involving operators.

Buy Now

Questions 12

Which two queries only return CUBE?

Options:

SELECT shape FROM bricks JOIN boxes ON weight >= min_weight AND weight < max_weight;

SELECT shape FROM bricks JOIN boxes ON weight > min_weight;

SELECT shape FROM bricks JOIN boxes ON weight BETWEEN min_weight AND max_weight;

SELECT shape FROM bricks JOIN boxes ON weight < max_weight;

SELECT shape FROM bricks JOIN boxes ON NOT (weight > max_weight);

Buy Now

Questions 13

Which statement is true about TRUNCATE and DELETE?

Options:

For large tables TRUNCATE is faster than DELETE.

For tables with multiple indexes and triggers is faster than TRUNCATE.

You can never TRUNCATE a table if foreign key constraints will be violated.

You can never tows from a table if foreign key constraints will be violated.

Buy Now

Questions 14

Examine this statement:

Which two statements are true?

Options:

All remaining employee names will appear in an ascending order

The names of employees remaining the maximum salary will appear first in an ascending order

All remaining employee names will appear in ascending order

All remaining employee names will appear in descending order

The names of employees maximum salary will appear fist to descending order

The names of employees maximum salary will appear fist to ascending order

Buy Now

Questions 15

Which three statements are true about the DESCRIBE command?

Options:

It can be used from SQL Developer.

It can be used to display the structure of an existing view.

It can be used only from SQL*Plus.

It displays the NOT NULL constraint for any columns that have that constraint.

It displays all constraints that are defined for each column.

It displays the PRIMARY KEY constraint for any column or columns that have that constraint.

Buy Now

Questions 16

Examine the description of the SALES1 table:

SALES2 is a table with the same description as SALES1,

Some sales data is duplicated In both tables.

You want to display the rows from the SALES1 table which are not present in the SALIES2 table.

Which set operator generates the required output?

Options:

SUBTRACT

INTERSECT

UNION ALL

MINUS

UNION

Buy Now

Questions 17

Examine the description of the EMPLOYEES table:

Which statement will execute successfully, returning distinct employees with non-null first names?

Options:

SELECT DISTINCT * FROM employees WHERE first_ name IS NOT NULL;

SELECT first_ name, DISTNCT last_ name FROM employees WHERE first_ name IS NOT NULL;

SELECT Distinct * FROM employees WHERE first_ name < > NULL;

SELECT first_ name, DISTINCT last_ name FROM employees WHERE first_ name < > NULL;

Buy Now

Questions 18

Which two statements are true about the results of using the INTERSECT operator in compound queries?

Options:

Reversing the order of the intersected tables can sometimes affect the output.

Column names in each SELECT in the compound query can be different.

INTERSECT returns rows common to both sides of the compound query.

The number of columns in each SELECT in the compound query can be different.

INTERSECT ignores NULLs

Buy Now

Questions 19

Examine these statements which execute successfully:

ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MON-YYYY HH24 MI: SS’

ALTER SESSION SET TIME_ ZONE = ‘-5:00’;

SELECT DBTIMEZONE, SYSDATE FROM DUAL

Examine the result:

If LOCALTIMESTAMP was selected at the same time what would it return?

Options:

11-JUL-2019 6,00,00,00000000 AM – 05:00

11-JUL-2019 11,00,00,00000000 AM

11-JUL-2019 6,00,00,000000 AM

11-JUL-2019 11,00,00,000000AM -05:00

Buy Now

Questions 20

Examine the data in the EMPLOYEES table:

Which statement will compute the total annual compensation for each employee?

Options:

SELECT last name,

(monthly salary*12) + (monthly_commission_pct * 12) AS

annual comp

FROM employees

;

SELECT last_ name (monthly_ salary+ monthly_ commission _ pct) *12 AS annual_

FROM employees ;

SELECT last name, (monthly_ salary *12) + (monthly_ salary * 12 * NVL

(monthly commission pct,0) ) As annual _ comp

FROM employees;

SELECT last_ name, monthly_ salary*12) + (monthly_ salary * 12 * Monthly commission _Pct) AS

annual_ comp

FROM employees;

Buy Now

Questions 21

Which two queries return the string Hello! we're ready?

Options:

SELECT q'! Hello! We're ready! 'FROM DUAL;

SELECT "Hello! We're ready "FROM |DUAL;

SELECT q'[Hello! We're ready]'FROM DUAL;

SELECT 'Hello! we\ re ready' ESCAPE'N'FROMDUAL:

SELECT 'Hello! We're ready' FROM DUAL;

Buy Now

Questions 22

Examine this SQL statement

DELETE FROM employees e

WHERE EXISTS

(SELECT' dummy'

FROM emp history

WHERE employee_ id= e. employee id);

Which two are true?

Options:

The subquery is not a correlated subquery.

The subquery is executed before the DELETE statement is executed.

All existing rows in the EMPLOYEES table are deleted,

The DELETE statement executes successfully even if the subquery selects multiple rows.

The subquery is executed for every row in the EMPLOYEES table.

Buy Now

Questions 23

You need to allow user ANDREW to:

1. Modify the TITLE and ADDRESS columns of your CUSTOMERS table.

2. GRANT tha permission to other users.

Which statement will do this?

Options:

GRANT UPDATE (title, address) ON customers TO andrew WITH ADMIN OPTION;

GRANT UPDATE ON customers. title, customers.address TO andrew WITH GRANT OPTION;

GRANT UPDATE ON customers.title, customers.address TO andrew WITH ADMIN OPTION;

GRANT UPDATE (title, address) ON customers TO andrew;

GRANT UPDATE ON customers. title, customers.address TO andrew;

GRANT UPDATE (title, address) ON customers TO andrew WITH GRANT OPTION:

Buy Now

Questions 24

Which two statements will return the names of the three employees with the lowest salaries?

Options:

SELECT last_name, salary

FROM employees

WHERE ROWNUM<=3

SELECT last_name,salary

FROM employees

ORDER BY salary

FETCH FIRST 3 ROWS ONLY;

SELECT last_name,salary

FROM employees

WHERE ROWNUM<=3

ORDER BY (SELECT salary FROM employees);

SELECT last_name,salary

FROM (SELECT * FROM employees ORDER BY salary)

SELECT last_name,salary

FROM employees

FETCH FIRST 3 ROWS ONLY

ORDER BY salary;

Buy Now

Questions 25

Which three statements are true about the Oracle join and ANSI Join syntax?

Options:

The Oracle join syntax only supports right outer joins,

The Oracle join syntax supports creation of a Cartesian product of two tables.

The SQL:1999 compliant ANSI join syntax supports natural joins.

The Oracle join syntax supports natural joins.

The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.

The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables.

The Oracle join syntax performs less well than the SQL:1999 compliant ANSI Join Answer.

Buy Now

Questions 26

Which three actions can you perform on an existing table containing date?

Options:

Add a new column as the table's first column.

Define a default value that is automatically inserted into a column containing nulls.

Add a new NOT NULL Column with a DEFAULT value.

Change a DATE Column containing data to a NUMBER data type.

Increase the width of a numeric column.

Change the default value of a column.

Buy Now

Questions 27

Which two are true about granting privilege on objects?

Options:

An object privilege can be granted to a role only by the owner of that object

An object privilege can be granted to other users only by the owner of that object

The owner of an object acquires all object privilege on that object by default

A table owner must grant the REFERENCES privilege to allow other users to create FOREIGN KEY constraints using that table

The WITH GRANT OPTION clause can be used only by DBA users

Buy Now

Questions 28

Examine the description of the BRICKS table;

Examine the description of the BRICKS_STAGE table;

Which two queries execute successfully?

Options:

SELECT shape,color,weight from bricks

MINUS

SELECT * FROM bricks_stage;

SELECT shape,color FROM bricks

MINUS

SELECT WEIGHT,color FROM bricks_stage;

select * from bricks

MINUS

select * from bricks_stage;

SELECT shape,color FROM bricks

MINUS

SELECT color,shape FROM bricks_stage;

SELECT brick_id,shape FROM bricks

MINUS

SELECT WEIGHT,COLOR from bricks_stage;

Buy Now

Questions 29

Examine the description of the EMPLOYEES table:

Which two queries return rows for employees whose manager works in a different department?

Options:

SELECT emp. *

FROM employees emp

WHERE manager_ id NOT IN (

SELECT mgr.employee_ id

FROM employees mgr

WHERE emp. department_ id < > mgr.department_ id

);

SELECT emp.*

FROM employees emp

WHERE NOT EXISTS (

SELECT NULL

FROM employees mgr

WHERE emp.manager id = mgr.employee_ id

AND emp.department_id<>mgr.department_id

);

SELECT emp.*

FROM employees emp

LEFT JOIN employees mgr

ON emp.manager_ id = mgr.employee_ id

AND emp. department id < > mgr. department_ id;

SELECT emp. *

FROM employees emp

RIGHT JOIN employees mgr

ON emp.manager_ id = mgr. employee id

AND emp. department id <> mgr.department_ id

WHERE emp. employee_ id IS NOT NULL;

SELECT emp. *

FROM employees emp

JOIN employees mgr

ON emp. manager_ id = mgr. employee_ id

AND emp. department_ id<> mgr.department_ id;

Buy Now

Questions 30

View the Exhibits and examine the structure of the COSTS and PROMOTIONS tables.

You want to display PROD IDS whose promotion cost is less than the highest cost PROD ID in a pro

motion time interval.

Examine this SQL statement:

SELECT prod id

FROM costs

WHERE promo id IN

(SELECT promo id

FROM promotions

WHERE promo_cost < ALL

(SELECT MAX (promo cost)

FROM promotions

GROUP BY (promo_end date-promo_begin_date)) );

What will be the result?

Options:

It executes successfully but does not give the required result.

It gives an error because the ALL keyword is not valid.

It gives an error because the GROUP BY clause is not valid

It executes successfully and gives the required result.

Buy Now

Questions 31

Examine the description of the EMPLOYEES table:

Which two statements will run successfully?

Options:

SELECT 'The first_name is '' || first_name || '' FROM employees ;

SELECT 'The first_name is '''||first_name ||'''' FROM employees ;

SELECT 'The first_name is ''' ||first_name||''' FROM employees ;

SELECT 'The first_name is '|| first_name|| '' FROM employees;

SELECT 'The first_name is \'' || first_name || '\'' FROM employees;

Buy Now

Questions 32

Which two statements are true about views?

Options:

Views can be indexed.

Theethi CHEcK clause prevents certalin rows from being updated or inserted in the underying table through the view.

Tables in the defining query of a view must always exist in order to create the view.

Views can be updated without the need to re-grant privileges on the view.

The wITH CHECK clause prevents certain rows from being displayed when querying the view.

Buy Now

Questions 33

Which three are true about privileges?

Options:

Schema owners can grant object privileges on objects in their schema to any other user or role.

A combination of object and system privileges can be granted to a role.

All types of schema objects have associated object privileges .

Only users with the DBA role can create roles .

Object privileges granted on a table automatically apply to all synonyms for that table.

Only users with the GRANT ANY PRIVILEGE privilege can grant and revoke system privileges from other users.

Buy Now

Questions 34

Which two true about a sql statement using SET operations such as UNION?

Options:

The data type of each column returned by the second query must be implicitly convertible to the data type of the corresponding column returned by the first query

The data type of each column retuned by the second query must exactly match the data type of the corresponding column returned by the first query

The number, but not names, of columns must be identical for all SELECT statements in the query

The data type group of each column returned by the second query must match the data type group of the corresponding column returned by the first query

The names and number of columns must be identical for all SELECT statements in the query.

Buy Now

Questions 35

You execute these commands successfully:

CREATE GLOBAL TEMPORARY TABLE invoices _ gtt

( customer id INTEGER,

invoice_ total NUMBER (10, 2)

) ON COMMIT PRESERVE ROWS;

INSERT INTO invoices_ gtt VALUES (1， 100);

COMMIT;

Which two are true?

Options:

You can add a foreign key to the table.

When you terminate your session, the row will be deleted.

To drop the table in this session, you must first truncate it.

You can add a column to the table in this session.

Other sessions can view the committed row.

Buy Now

Questions 36

You execute this query:

SELECT TO CHAR (NEXT_DAY(LAST_DAY(SYSDATE),’MON’ ),’ dd“Monday for” fmMonth rrr’) FROM DUAL;

What is the result?

Options:

It executes successfully but does not return any result.

It returns the date for the first Monday of the next month.

It generates an error.

It returns the date for the last Monday of the current month.

Buy Now

Questions 37

You and your colleague Andrew have these privileges on the EMPLOYEE_RECORDS table:

1. SELECT

2. INSERT

3. UPDATE

4. DELETE

You connect to the database instance an perform an update to some of the rows in

EMPLOYEE_RECORDS, but don’t commit yet.

Andrew connects to the database instance and queries the table

No othet user are accessing the table

Which two statements ate true at this point?

Options:

Andrew will be able to modify any rows in the table that have not been modified by your transaction

Andrew will be unable to see the changes you have made

Andrew will be able to see the changes you habe made

Andrew will be unable to perform any INSERT, UPDATE of DELETE on the teble

Andrew will be able to SELECT from the table, but be unable to modify any existing rows.

Buy Now

Questions 38

Which two are true about creating tables in an Oracle database?

Options:

A create table statement can specify the maximum number of rows the table will contain.

The same table name can be used for tables in different schemas.

A system privilege is required.

Creating an external table will automatically create a file using the specified directory and file name.

A primary key constraint is manadatory.

Buy Now

Questions 39

Examine the description of the EMPLOYEES table:

Which query is valid?

Options:

SELECT dept_id, join date, SUM(salary) FROM employees GROUP BY dept_id,join_date;

SELECT dept_id, MAX (AVG(salary)) FROM employees GROUP BY dept_id;

SELECT dept_id, AVG(NAX(salary)) FROM employees GROUP BY dept_id;

SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id;

Buy Now

Questions 40

Examine the description of the PRODUCT_ STATUS table:

The STATUS column contains the values IN STOCK or OUT OF STocK for each row.

Which two queries will execute successfully?

Options:

SELECT prod_id ||q’(‘ s not available)’ ‘CURRENT AVAILABILITY’ FROM

product_ status WHERE status = ‘OUT OF STOCK’

SELECT prod_id ||q”‘ s not available” FROM

product_ status WHERE status = ‘OUT OF STOCK’;

SELECT PROD_ID||q’(‘s not available)’ FROM

product_ status WHERE status = ‘OUT OF STOCK’;

SELECT PROD_ID||q’(‘s not available)’ “CURRENT AVAILABILITY”

FROM product_ status WHERE status = ‘OUT OF STOCK’;

SELECT prod_id q’s not available” from product_ status WHERE status = ‘OUT OF STOCK’;

SELECT prod_id “CURRENT AVAILABILITY”||q’ (‘s not available)’ from product_ status WHERE status

= ‘OUT OF STOCK’;

Buy Now

Questions 41

Which statement is true regarding the SESSION_PRIVS dictionary view?

Options:

It contains the object privileges granted to other users by the current user session.

It contains the system privileges granted to other users by the current User session.

It contains the current system privileges available in the user session.

It contains the current object privileges available in the user session.

Buy Now

Questions 42

Which three statements are true regarding indexes?

Options:

A SELECT statement can access one or more indices without accessing any tables.

A table belonging to one user can have an index that belongs to a different user,

When a table is dropped and is moved to the RECYCLE BIN, all Indexes built on that table are permanently dropped.

A UNIQUE index can be altered to be non-unique.

An update to a table can result in no updates to any of the table's indexes.

An update to a table can result in updates to any or all of the table's indexes.

Buy Now

Questions 43

The ORDERS table has a primary key constraint on the ORDER_ID column.

The ORDER_ITEMS table has a foreign key constraint on the ORDER_ID column, referencing the primary key of the ORDERS table.

The constraint is defined with on DELETE CASCADE.

There are rows in the ORDERS table with an ORDER_TOTAL less than 1000.

Which three DELETE statements execute successfully?

Options:

DELETE FROM orders WHERE order_total<1000;

DELETE * FROM orders WHERE order_total<1000;

DELETE orders WHERE order_total<1000;

DELETE FROM orders;

DELETE order_id FROM orders WHERE order_total<1000;

Buy Now

Questions 44

Which three are true about subqueries?

Options:

A subquery can be used in a WHERE clause.

A subquery can be used in a HAVING clause.

=ANY can only evaluate the argument against a subcjuery if it returns two or more values.

A subquery cannot be used in a FROM clause.

< any returns true if the argument is less than the lowest value returned by the subquery.

A subquery cannot be used in the select list.

Buy Now

Questions 45

You execute these commands:

CREATE TABLE customers (customer id INTEGER, customer name VARCHAR2 (20));

INSERT INTO customers VALUES (1，‘Custmoer1 ‘);

SAVEPOINT post insert;

INSERT INTO customers VALUES (2, ’Customer2 ‘);

SELECTCOUNT (*) FROM customers;

Which two, used independently, can replace so the query retums 1?

Options:

ROLLBACK;

COMMIT;

ROLIBACK TO SAVEPOINT post_ insert;

CONOIT TO SAVEPOINT post_ insert;

ROLLEBACK TO post_ insert;

Buy Now

Questions 46

Which two queries will result in an error?

Options:

SELECT FIRST_NAME LAST_NAME FROM EMPLOYEES;

SELECT FIRST_NAME,LAST_NAME FROM EMPLOYEES;

SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY

FROM EMPLOYEES

WHERE ANNUAL_SALARY > 100000

ORDER BY 12 * SALARY ;

SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY

FROM EMPLOYEES

WHERE 12 * SALARY > 100000

ORDER BY ANNUAL_SALARY;

SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY

FROM EMPLOYEES

WHERE 12 * SALARY > 100000

ORDER BY 12 * SALARY;

SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY

FROM EMPLOYEES

WHERE ANNUAL_SALARY > 100000

ORDER BY ANNUAL_SALARY;

Buy Now

Questions 47

Examine the description of the ORDER_ITEMS table:

Examine this incomplete query:

SELECT DISTINCT quantity * unit_price total_paid FROM order_items ORDER BY ;

Which two can replace so the query completes successfully?

Options:

quantity

quantity, unit_price

total_paid

product_id

quantity * unit_price

Buy Now

Questions 48

View the Exhibit and examine the structure of the ORDERS table.

The columns ORDER_MODE and ORDER TOTAL have the default values'direct “and respectively.

Which two INSERT statements are valid? (Choose two.)

Options:

INSERT INTO (SELECT order_id, order date, customer_id FROM orders) VALUES (1, ‘09-mar-2007“，101);

INSERT INTO orders (order_id, order_date, order mode,customer_id, order_total) VALUES (1, TO_DATE (NULL),‘online‘,101, NULL) ;

INSERT INTO orders VALUES (1, ‘09-mar-2007’, ‘online’,’ ’,1000);

INSERT INTO orders (order id, order_date, order mode, order_total)VALUES (1，‘10-mar-2007’,’online’, 1000)

INSERT INTO orders VALUES(‘09-mar-2007’，DEFAULT,101, DEFALLT);

Buy Now

Questions 49

Which two statements execute successfully?

Options:

SELECT TO_ DATE('2019-DEC-25 15:30'， 'YYYY-MON-DD HH24:MI', 'NLS_ DATE_ LANGUAGE

=AMERICAN' ) FROM DUAL;

SELECT TO_CHAR('2019-DEC-25 15:30"， YY-MON-D HH24:M2', 'NLS_DATE LANGUAGE =

AMERICAN')

FROM DUAL;

SELECT TO _DATE (TO_ CHAR (‘2019-DEC-25 03:30’, ‘YYYY-MON-DD HH12:MI’))

FROM DUAL;

SELECT TO _ CHAR (TO_ DATE (‘2019-DEC-25 03:30’,’YYYY-MON-DD HH12:MI’))

FROM DUAL

SELECT TO _ CHAR (‘2019-DEC-25 15:30’.’YYYY-MON-DD HH24:MI’)

FROM DUAL

Buy Now

Questions 50

Examine the description of the PRODUCTS table:

Which two statements execute without errors?

Options:

MERGE INTO new_prices n

USING (SELECT * FROM products) p

WHEN MATCHED THEN

UPDATE SET n.price= p.cost* 01

WHEN NOT MATCHED THEN

INSERT(n.prod_id, n.price) VALUES(p.prod_id, cost*.01)

WHERE(p.cost<200);

MERGE INTO new_prices n

USING (SELECT * FROM products WHERE cost>150) p

ON (n.prod_id= p.prod_id)

WHEN MATCHED THEN

UPDATE SET n.price= p.cost*.01

DELETE WHERE (p.cost<200);

MERGE INTO new_prices n

USING products p

ON (p.prod_id =n.prod_id)

WHEN NOT MATCHED THEN

INSERT (n.prod _id, n.price) VALUES (p.prod_id, cost*.01)

WHERE (p.cost<200);

MERGE INTO new_prices n

USING (SELECT * FROM products WHERE cost>150) p

ON (n.prod_id= p.prod_id)

WHEN MATCHED THEN

DELETE WHERE (p.cost<200)

Buy Now

Questions 51

Which two are true about self joins?

Options:

They are always equijoins.

They require the NOT EXISTS operator in the join condition.

They have no join condition.

They can use INNER JOIN and LEFT JOIN.

They require table aliases.

They require the EXISTS opnrator in the join condition.

Buy Now

Questions 52

Which two are true about constraints?

Options:

Constraints are enforced only during INSERT operations.

A column with a foreign key constraint can never contain a null value.

All constraints can be defined at the table or column level.

A constraint can be disabled even if the constrained column contains data.

A column with a UNIQUE constraint can contain a NULL value.

Buy Now

Questions 53

A session's NLS_DATE_FORMAT is set to DD Mon YYYY .

Which two queries return the value 1 Jan 2019?

Options:

SELECT to_date(' 2019-01-01 ', 'YYYY -MM-DD' ) FROM DUAL;

SELECT DATE '2019-01-01' FROM DUAL ;

SELECT TO_CHAR('2019-01-01') FROM DUAL; 2019-01-01

SELECT '2019-01-01' FROM DUAL ; 2019-01-01

SELECT TO_ DATE('2019-01-01') FROM DUAL;

Buy Now

Questions 54

Examine the data in the INVOICES table:

Examine the data in the CURRENCIES table:

CURRENCY_CODE

-------------

JPY

GPB

CAD

EUR

USD

Which query returns the currencies in CURRENCIES that are not present in INVOICES?

Options:

SELECT currency_ code FROM currencies

MINUS

SELECT currency_ code FROM invoices;

SELECT * FROM currencies

WHERE NOT EXISTS (

SELECT NULL FROM invoices WHERE currency_ code = currency_ code);

SELECT currency_ code FROM currencies

INTERSECT

SELECT currency_ code FROM invoices;

SELECT * FROM currencies

MINUS

SELECT * FROM invoices;

Buy Now

Questions 55

Examine this statement:

CREATE TABTE orders

(sarial_no NUMBER UNIQUE,

order_id NUMBER PRIMARY KEY ,

order_date DATE NOT NULL,

status VARCHAR2 (10) CHECK (status IN ('CREDIT', 'CASH')),

product_id NUMBER REFERENCES products (product_id),

order_ total NUMBER);

On which two columns of the table will an index be created automatically?

Options:

SERIAL_NO

ORDER_DATE

PRODUCT_ ID

ORDER TOTAL

ORDER_ ID

STATUS

Buy Now

Questions 56

Examine the description of the EMPLOYEES table：

Which two queries return the highest salary in the table?

Options:

SELECT department_id, MAX(salary)

FROM employees

GROUP BY department_id;

SELECT MAX (salary)

FROM employees;

SELECT MAX (salary)

FROM employees

GROUP BY department_id;

SELECT MAX (salary)

FROM employees

GROUP BY department_id

HAVING MAX (salary) = MAX (MAX (salary));

SELECT MAX (MAX (salary))

FROM employees

GROUP BY department_id;

Buy Now

Questions 57

Examine the description of the BOOKS table:

The table has 100 rows.

Examine this sequence of statements issued in a new session;

INSERT INTO BOOKS VALUES (‘ADV112’ , ‘Adventures of Tom Sawyer’, NULL, NULL);

SAVEPOINT a;

DELETE from books;

ROLLBACK TO SAVEPOINT a;

ROLLBACK;

Which two statements are true?

Options:

The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed.

The second ROLLBACK command does nothing.

The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row.

The second ROLLBACK command replays the delete.

The second ROLLBACK command undoes the insert.

Buy Now

Questions 58

You issued this command: DROP TABLE hr. employees;

Which three statements are true?

Options:

ALL constraints defined on HR, EMPLOYEES are dropped.

The HR. EMPLOYEES table may be moved to the recycle bin.

Synonyms for HR EMPLOYEES are dropped.

Sequences used to populate columns in the HR. EMPLOYEES table are dropped.

All indexes defined on HR, EMPLOYEES are dropped.

Views referencing HR, EMPLOYEES are dropped.

Buy Now

Questions 59

Which two statements are true about Oracle databases and SQL?

Options:

Updates performed by a database user can be rolled back by another user by using the ROLLBACK command.

The database guarantees read consistency at select level on user-created tablers.

When you execute an UPDATE statement, the database instance locks each updated row.

A query can access only tables within the same schema.

A user can be the owner of multiple schemas In the same database.

Buy Now

Questions 60

Viev the Exhibit and examine the structure of the PRODUCT INFORMATION and INVENTORIEStables.

You have a requirement from the supplies department to give a list containing PRODUCT _ID,SUPPLIER ID, and QUANTITY_ON HAND for all the products where in QUANTITY ON HAND is lessthan five.

Which two SQL statements can accomplish the task? (Choose two)

Options:

SELECT product id, quantity on hand, supplier id

FROM product information

NATURAL JOIN inventories AND quantity .on hand < 5;

SELECT i. product id, i. quantity .on hand, pi. supplier_id

FROM product_information pi JOIN inventories i

ON (pi. product. id=i. product id) AND quantity on hand < 5;

SELECT i. product_id, i. quantity_on hand, pi. supplier id

FROM product information pi JOIN inventories i USING (product id) AND quantity .on hand < 5;

SELECT i.product id, i. quantity on hand, pi. supplier id

FROM product information pi JOIN inventories i

ON (pi.product id=i. product id)WHERE quantity on hand < 5;

Buy Now

Questions 61

Which three statements are true about single-row functions?

Options:

The data type returned can be different from the data type of the argument.

They can be nested to any level.

They return a single result row per table.

They can accept only one argument.

The argument can be a column name, variable, literal or an expression.

They can be used only in the WHERE clause of a SELECT statement.

Buy Now

Questions 62

Which three statements are true about Oracle synonyms?

Options:

A synonym cannot be created for a PL /SQL package.

A SEQUENCE can have a synonym.

A synonym can be available to all users .

A synonym created by one user can refer to an object belonging to another user.

Any user can drop a PUBLIC synonym.

Buy Now

Questions 63

Examine this business rule:

Each student can work on multiple projects and earth project can have multiple students.

You must decide an Entity Relationship (ER) model for optional data storage and allow generating reports in this format:

STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK Which two statements are true?

Options:

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.

The ER must have a many-to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into 1-to-many relationships.

PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity.

The ER must have a 1-to-many relationship between the STUDENTS and PROJECTS entities.

STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity.

Buy Now

Questions 64

Examine the description of the EMPLOYEES table:

NLS_DATE_FORMAT is set to DD-MON-YY.

Which query requires explicit data type conversion?

Options:

SELECT salary + 120.50 FROM employees;

SELECT SUBSTR(join date, 1, 2)- 10 FROM employees;

SELECT join date 11.’11 salary FROM employees;

SELECT join date FROM employees where join date > *10-02-2018*;

SELECT join date + 20 FROM employees;

Buy Now

Questions 65

Examine these statements:

CREATE TABLE alter_test (c1 VARCHAR2(10), c2 NUMBER(10));

INSERT INTO alter_test VALUES ('123'， 123);

COMMIT;

Which is true ahout modifyIng the columns in AITER_TEST?

Options:

c1 can be changed to NUMBER(10) and c2 can be changed to VARCHAN2 (10).

c2 can be changed to NUMBER(5) but c1 cannot be changed to VARCHAN2 (5).

c2 can be changed to VARCHAR2(10) but c1 cannot be changed to NUMBER (10).

c1 can be changed to NUMBER(10) but c2 cannot be changed to VARCHAN2 (10).

c1 can be changed to VARCHAR2(5) and c2 can be changed to NUMBER (12,2).

Buy Now

Questions 66

You execute this command:

TRUNCATE TABLE dept;

Which two are true?

Options:

It drops any triggers defined on the table.

It retains the indexes defined on the table.

It retains the integrity constraints defined on the table.

A ROLLBACK statement can be used to retrieve the deleted data.

It always retains the space used by the removed rows.

A FLASHBACK TABLE statement can be used to retrieve the deleted data.

Buy Now

Questions 67

Examine the description of the employees table:

Examine these requirements:

1- Display the last name, date of hire and the number of years of service for each employee.

2. If the employee has been employed 5 or more years but less than 10, display -5+ years of service".

3. If the employee has been employed 10 or more years but less than 15, display "10+ years of

service".

4. If the employee has been employed 15 or more years, display "15-*- years of service".

5. If none of these conditions matches, display "<5 years of service".

6. Sort the results by the hire_date column.

Which statement satisfies all the requirements?

Options:

Option A

Option B

Option C

Option D

Buy Now

Answer:

Explanation:

Option D is the correct SQL statement that satisfies all the requirements mentioned. The CASE statement correctly compares the hire_date to date intervals subtracted from the current date (SYSDATE) to determine the number of years of service. This CASE statement is also appropriately ordered to ensure that the first condition matched is the one returned, preventing overlapping of the conditions.

Here is how Option D works according to the requirements:

It selects the last_name and hire_date from the employees table.

The CASE statement is used to calculate the number of years of service and to display the appropriate message according to the intervals defined.

The ORDER BY hire_date clause ensures the results are sorted by the date of hire.

In Option D, the intervals are defined in the correct order, ensuring that the first true condition is the one that is used:

The first WHEN checks if the hire date is 15 or more years ago.

The second WHEN checks if it is 10 or more years ago.

The third WHEN checks if it is 5 or more years ago.

The ELSE clause covers any hire dates less than 5 years ago.

The correct syntax for the intervals is SYSDATE - hire_date >= TO_YMINTERVAL('15-0') and similarly for the 10 and 5 years intervals.

Options A, B, and C are incorrect because they have various issues, such as incorrect ordering of the CASE statement's conditions, which could lead to incorrect results due to overlapping intervals, or the use of the TO_YMINTERVAL function that may not properly cover the intended date ranges.

References:

Oracle Documentation on CASE Expressions: SQL Language Reference - CASE Expression

Oracle Documentation on TO_YMINTERVAL Function: SQL Language Reference - TO_YMINTERVAL

Oracle Documentation on ORDER BY Clause: SQL Language Reference - ORDER BY Clause

Therefore, Option D is the statement that fulfills all the requirements for displaying the number of years of service based on the employee's hire date and ordered by the hire date.

Questions 68

Examine the data in the COLORS table:

Examine the data in the BRICKS table:

Which two queries return all the rows from COLORS?

Options:

Buy Now

Answer:

A, D

Explanation:

In SQL, to return all rows from one table in a join regardless of whether they match rows in the other table, a LEFT JOIN is used when we want all rows from the left table, or a RIGHT JOIN when we want all rows from the right table. Here’s an explanation for each query provided in the context of the COLORS and BRICKS tables:

A. This query uses a LEFT JOIN and will return all the rows from the COLORS table (c) because it is on the left side of the join. Additionally, it joins the BRICKS table (b) on the condition that the color_rgb_hex_value matches. The condition in the WHERE clause (b.brick_id > 0) will not exclude any COLORS rows because it only filters the rows from the right table (BRICKS) after the join.

B. This query uses a RIGHT JOIN, and while it does return all rows from the COLORS table, it doesn't place it on the left side of the join, which is not what is typically expected from a RIGHT JOIN to return all rows from the right side table.

C. This query is a plain JOIN (also known as an inner join), which only returns rows that have matching values in both tables, so it will not return all the rows from the COLORS table if there is no match in the BRICKS table.

D. This query uses a FULL JOIN, which returns all rows when there is a match in one of the tables. Hence, it will return all the rows from both the COLORS and BRICKS tables. While this does return all rows from the COLORS table, it also includes all rows from the BRICKS table, which may not be desired if the requirement was only to return all rows from COLORS.

E. This query is similar to option A and uses the USING clause to specify the join condition. The USING clause is used when both tables have a column with the same name and is meant to simplify the syntax. However, in this case, the columns do not have the same name in both tables (RGB_HEX_VALUE in COLORS and COLOR_RGB_HEX_VALUE in BRICKS), so this query will not execute successfully.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Joins"

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "LEFT OUTER JOIN"

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "RIGHT OUTER JOIN"

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "FULL OUTER JOIN"

Based on the explanations above, the correct answers that return all rows from the COLORS table are A and D. However, D includes all rows from BRICKS as well, which may not be the intended requirement if the question specifies only rows from COLORS should be returned. Thus, A is the most accurate answer to the question as it adheres to the standard expectation of a LEFT JOIN.

Questions 69

You need to calculate the number of days from 1st January 2019 until today.

Dates are stored in the default format of DD-MON-RR.

Which two queries give the required output?

Options:

SELECT SYSDATE-TO_DATE ('01-JANUARY-2019') FROM DUAL;

SELECT TO_DATE (SYSDATE, 'DD/MONTH/YYYY')-'01/JANUARY/2019' FROM DUAL;

SELECT ROUND (SYSDATE-TO_DATE ('01/JANUARY/2019')) FROM DUAL;

SELECT TO_CHAR (SYSDATE, 'DD-MON-YYYY')-'01-JAN-2019' FROM DUAL;

SELECT ROUND (SYSDATE- '01-JAN-2019') FROM DUAL:

Buy Now

Questions 70

Examine the description of EMPLOYEES table:

Which three queries return all rows for which SALARY+COMMISSION is greate than 20000?

Options:

SELECT * FROM employees WHERE salary+NULLF(commission,0)>=20000;

SELECT * FROM employees WHERE salary+NVL2(commission,commission,0)>=20000;

SELECT * FROM employees WHERE NVL2(salary)+commission,salary+commission,

SELECT * FROM employees WHERE salary+NVL(commission,0)>=20000;

SELECT * FROM employees WHERE NVL(salary+commission,0)>=20000;

SELECT * FROM employees WHERE NVL(salary+commission,0)>==20000;

Buy Now

Questions 71

Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data?

Options:

Only the primary key can be defined at the column and table level.

The foreign key columns and parent table primary key columns must have the same names.

It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted.

A table can have only one primary key but multiple foreign keys.

Primary key and foreign key constraints can be defined at both the column and table level.

A table can have only one primary key and one foreign key.

It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted

Buy Now

Questions 72

Which two are true about the data dictionary?

Options:

Base tables in the data dictionary have the prefix DBA_.

All user actions are recorded in the data dictionary.

The data dictionary is constantly updated to reflect changes to database objects, permissions, and data.

All users have permissions to access all information in the data dictionary by default

The SYS user owns all base tables and user-accessible views in the data dictionary.

Buy Now

Questions 73

Examine the description of the ENPLYEES table:

Which two queries return all rows for employees whose salary is greater than the average salary in their department?

Options:

SELECT ”

FROM employees

WHERE salary > ANY

SELECT AVG (salary)

EROM employees

GROUP BY department_ id);

SELECT

FROM employees

WHERE salary > AVG (salary) OVER (PARTITION BY department _ id);

SELECT”

FROM employees e1

WHERE salary >!

SELECT AVG (salary)

FROM employees e2

WHERE e1. Department _id = e2, department_ id

SELECT.

FROM

SELECT e.", AVG (salary) OVER (PARTITION BY department id) avg_ sal

FROM employees e

WHERE salary > avg_ sal;

SELECT”

FROM employees

WHERE salary >

( SELECT AVG

(salary) FROM

employees

GROUP BY department _ id

Buy Now

Questions 74

Which two statements are true regarding a SAVEPOINT?

Options:

Rolling back to a SAVEPOINT can undo a CREATE INDEX statement.

Only one SAVEPOINT may be issued in a transaction.

A SAVEPOINT does not issue a COMMIT

Rolling back to a SAVEPOINT can undo a TRUNCATE statement.

Rolling back to a SAVEPOINT can undo a DELETE statement

Buy Now

Questions 75

Examine the description of the EMPLOYEES table:

The session time zone is the same as the database server

Which two statements will list only the employees who have been working with the company for more than five years?

Options:

SELECT employee_ name FROM employees WHERE (SYSDATE – hire_ data) / 365>5

SELECT employee_ name FROM employees WHERE (SYSTIMESTAMP – hire_ data) / 365>

SELECT employee_ name FROM employees WHERE (CUARENT_ DATE – hire_ data / 365>5

SELECT employee_ name FROM employees WHERE (SYSNAYW – hire_ data / 12> 3

SELECT employee_ name FROM employees WHERE (CUNACV_ DATE – hire_ data / 12> 3

Buy Now

Questions 76

Which four statements are true about constraints on Oracle tables?

Options:

A Column can have only one CHECK Constraint.

A NOT NULL Constraint can be defined at the table level.

A UNIQUE constraint permits NULLS.

A PRIMARY KEY Constraint can be added after a table has been created and populated.

A CHECK Constraint can refer to values in other rows.

A UNIQUE Constraint can use a pre-existing index on the constrained column or columns.

A FOREIGN KEY Column can contain NULLS.

Buy Now

Questions 77

Examine this SQL statement:

Which two are true?

Options:

The subquery is executed before the UPDATE statement is executed.

All existing rows in the ORDERS table are updated.

The subquery is executed for every updated row in the ORDERS table.

The UPDATE statement executes successfully even if the subquery selects multiple rows.

The subquery is not a correlated subquery.

Buy Now

Answer:

A, C

Explanation:

The provided SQL statement is an update statement that involves a subquery which is correlated to the main query.

A. The subquery is executed before the UPDATE statement is executed. (Incorrect)

This statement is not accurate in the context of correlated subqueries. A correlated subquery is one where the subquery depends on values from the outer query. In this case, the subquery is executed once for each row that is potentially updated by the outer UPDATE statement because it references a column from the outer query (o.customer_id).

B. All existing rows in the ORDERS table are updated. (Incorrect)

Without a WHERE clause in the outer UPDATE statement, this would typically be true. However, the correctness of this statement depends on the actual data and presence of matching customer_id values in both tables. If there are rows in the ORDERS table with customer_id values that do not exist in the CUSTOMERS table, those rows will not be updated.

C. The subquery is executed for every updated row in the ORDERS table. (Correct)

Because the subquery is correlated (references o.customer_id from the outer query), it must be executed for each row to be updated in the ORDERS table to get the corresponding cust_last_name from the CUSTOMERS table.

[Reference: The behavior of correlated subqueries is detailed in the Oracle Database SQL Language Reference, which explains that a correlated subquery is evaluated once for each row processed by the parent statement (Oracle Database SQL Language Reference, 12c Release 1 (12.1))., D. The UPDATE statement executes successfully even if the subquery selects multiple rows. (Incorrect), The subquery inside the SET clause must return exactly one value for each row to be updated. If the subquery returns more than one row for any outer row, the UPDATE statement will result in an error (specifically, an "ORA-01427: single-row subquery returns more than one row" error)., E. The subquery is not a correlated subquery. (Incorrect), This is incorrect because the subquery references the o.customer_id column from the ORDERS table, which makes it a correlated subquery., The correct answers are A and C. The subquery is a correlated subquery because it references the ORDERS table's customer_id in its WHERE clause. It is executed for each row to be updated since it depends on values from the outer query (the o.customer_id). It's important to note that although the statement A is marked incorrect based on the typical behavior of correlated subqueries, in some cases, Oracle's optimizer may unnest the subquery and execute it beforehand if it determines that it's more efficient and the result is the same. However, this doesn't change the nature of the query being a correlated subquery., , ]

Questions 78

Which three are true about multiple INSERT statements?

Options:

They can be performed only by using a subquery.

They can be performed on relational tables.

They can be performed on views.

They can be performed on remote tables.

They can be performed on external tables using SQL*Loader.

They can insert each computed row into more than one table.

Buy Now

Questions 79

Which two are true about the MERGE statement?

Options:

The WHEN NOT MATCHED clause can be used to specify the deletions to be performed.

The WHEN NOT MATCHED clause can be used to specify the inserts to be performed.

The WHEN MATCHED clause can be used to specify the inserts to be performed.

The WHEN NOT MATCHED clause can be used to specify the updates to be performed.

The WHEN MATCHED clause can be used to specify the updates to be performed.

Buy Now

Questions 80

Examine the data in the EMP table:

You execute this query:

SELECT deptno AS "Department", AVG(sal) AS AverageSalary, MAX(sal) AS "Max Salary"

FROM emp

WHERE sal >= 12000

GROUP BY "Department "

ORDER BY AverageSalary;

Why does an error occur?

Options:

An alias name must not be used in an ORDER BY clause.

An allas name must not contain space characters.

An alias name must not be used in a GROUP BY clause.

An alias name must always be specified in quotes.

Buy Now

Questions 81

Which statement will execute successfully?

Options:

SELECT 1, 2 FROM DUAL

UNION

SELECT 3, 4 FROM DUAL

ORDER BY 1, 2;

SELECT 3 FROM DUAL

UNION

SELECT 4 FROM DUAL

ORDER BY 3 ;

SELECT 1, 2 FROM DUAL

UNION

SELECT 3, 4 FROM DUAL

ORDER BY 3, 4;

SELECT 1 FROM DUAL

UNION

SELECT 2 FROM DUAL

ORDER BY 1, 2;

Buy Now

Questions 82

Which two statements are true about the ORDER BY clause?

Options:

Numeric values are displayed in descending order if they have decimal positions.

Only columns that are specified in the SELECT list can be used in the ORDER BY cause.

NULLS are not included in the sort operation.

Column aliases can be used In the ORDER BY cause.

Ina character sort, the values are case-sensitive.

Buy Now

Questions 83

Which three statements are true about an ORDER BY clause?

Options:

An ORDER BY clause always sorts NULL values last.

An ORDER BY clause can perform a binary sort

An ORDER BY clause can perform a linguistic sort

By default an ORDERBY clause sorts rows in ascending order

An ORDR BY clause will always precede a HAVI NG clause if both are used in the same top-level

Buy Now

Questions 84

Examine the BRICKS table:

You write this query:

SELECT

FROM bricks b1 CROSS JOIN bricks b2

WHERE b1. Weight < b2. Weight:

How many rows will the query return?

Options:

Buy Now

Questions 85

Which is true about the & and && prefixes with substitution variables?

Options:

& can prefix a substitution variable name only in queries. DML

An & prefix to an undefined substitution variable, which is referenced twice in the same query, will prompt for a value twice .

The && prefix will not prompt for a value even if the substitution variable is not previously defined in the session.

An && prefix to an undefined substitution variable, which is referenced multiple times in multiple queries, will prompt for a value once per query.

Both & and && can prefix a substitution variable name in queries and DML statements.

Buy Now

Questions 86

Examine this business rule:

Each student can work on multiple projects and each project can have multiple students.

You must design an Entity Relationship(ER) model for optimal data storage and allow for generating reports in this format:

Which two statements are true?

Options:

An associative table must be created with a composite key of STUDENT_ID and PROJRCT_ID, which is the foreign key linked to the STUDENTS and PROJECTS entities.

PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity.

The ER must have a 1-to-many relationship between the STUDENTS and PROJECTS entities.

The ER must have a many to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into 1-to-many relationships.

STUDENT ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity.

Buy Now

Questions 87

The PROD_ID column is the foreign key in the SALES table.Which references the PRODUCTS table.

Similarly,the CUST_ID and TIME_ID columns are Also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.

Evaluate the following CREATE TABLE command:

CREATE TABLE new_sales(prod_id, I cust_id, order_date DEFAULT SYSDATE)

AS SELECT I prod_id,cust_id,time_id FROM sales.

Which statement is true regarding the above command?

Options:

The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.

The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.

The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause I do not match.

The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table

Buy Now

Questions 88

Which two statements are true about truncate and delete?

Options:

the result of a delete can be undone by issuing a rollback

delete can use a where clause to determine which row(s) should be removed.

TRUNCATE can use a where clause to determine which row(s) should be removed.

truncate leavers any indexes on the table in an UNUSABLE STATE.

the result of a truncate can be undone by issuing a ROLLBACK.

Buy Now

Questions 89

which is true about the round,truncate and mod functions>?

Options:

ROUND(MOD(25,3),-1) IS INVALID

ROUND(MOD(25,3),-1) AND TRUNC(MOD(25,3),-1) ARE BOTH VALID AND GIVE THE SAME RESULT.

ROUND(MOD(25,3),-1) AND TRUNC(MOD(25,3),-1) ARE BOTH VALID AND GIVE THE DIFFERENT RESULTS.

TRUNC(MOD(25,3),-1) IS INVALID.

Buy Now

Questions 90

Which two statements are true about CURRENT_TIMEITAMP?

Options:

The date is in the time zone of DBTIMEZONE.

The value varies depending on the setting of SESSIONTIMEZONE.

It returns the same date as CURRENT_DATE.

The time is in the time zone of DBTIMEZONE.

It returns a value of data type TIMESTAMP

It always returns the same value as SYSTIMESTAMP

Buy Now

Questions 91

Which three statements are true?

Options:

The COMMISSION column can contain negative values .

The MANAGER column is a foreign key referencing the EMPNO column.

The SALARY column must have a value .

An index is created automatically in the MANAGER column.

The DEPTNO column in the EMP table can contain the value 1.

The DEPTNO column in the EMP table can contain NULLS .

The DNAME column has a unique constraint.

Buy Now

Questions 92

Which two statements are true regarding non equijoins?

Options:

The ON clause can be used.

The USING clause can be used.

The SQL:1999 compliant ANSI join syntax must be used.

Table aliases must be used.

The Oracle join syntax can be used.

Buy Now

Questions 93

Which two are true about multiple table INSERT statements?

Options:

They always use subqueries.

They can transform a row from a source table into multiple rows in a target table.

The conditional INSERT FIRST statement always inserts a row into a single table.

The conditional INSERT ALL statement inserts rows into a single table by aggregating source rows.

The unconditional INSERT ALL statement must have the same number of columns in both the source and target tables.

Buy Now

Questions 94

Whith three statements are true about built in data types?

Options:

A VARCHAR2 blank pads column values only if the data stored is non numeric and contains no special characlers

A BFILE stores unstructured binary data in operating systerm files

A CHAR column definition does not require the length to be specified

The default length for a CHAR column is always one character

A VARCHAR2 column definition does not require the length to be specified

A BLOB stores unstructured binary data within the database

Buy Now

Questions 95

Examine the ORDER _ITEms table:

Which two queries return rows where QUANTITY is a multiple of ten?

Options:

SELECT * FROM order_ items WHERE quantity = TRUNC (quantity, -1);

SELECT * FROM order_ items WHERE MOD (quantity, 10) = 0;

SELECT” FROM order_ items WHERE FLOOR (quantity / 10) = TRUNC (quantity / 10);

SELECT FROM order_ items WHERE quantity / 10 = TRUNC (quantity);

SELECT” FROM order_ _items WHERE quantity = ROUND (quantity, 1);

Buy Now

Questions 96

The SALES table has columns PROD_ID and QUANTITY_SOLD of data type NUMBER. Which two queries execute successfully?

Options:

SELECT COUNT(prod_id) FROM sales WHERE quantity_sold>55000 GROUP BY prod_id;

SELECT prod_id FROM sales WHERE quantity_sold> 55000 GROUP BY prod_id HAVING COUNT(*)> 10;

SELECT COUNT(prod_id) FROM sales GROUP BY prod_id WHERE quantity_sold> 55000;

SELECT prod_id FROM sales WHERE quantity_sold> 55000 AND COUNT(*)> 10 GROUP BY COUNT(*)> 10;

SELECT prod_id FROM sales WHERE quantity_sold> 55000 AND COUNT(*)> 10 GROUP BY prod_id HAVING COUNT(*)> 10;

Buy Now

Questions 97

Examine this partial statement:

SELECT ename, sal,comm FROM emp

Now examine this output:

WHICH ORDER BY clause will generate the displayed output?

Options:

ORDER BY NVL(enam,0) DESC, ename

ORDER BY NVL(comm,0) ASC NULLS FIRST, ename

ORDER BY NVL(comm,0) ASC NULLS LAST, ename

ORDER BY comm DESC NULLS LAST, ename

Buy Now

Exam Code: 1z0-071

Exam Name: Oracle Database 12c SQL

Last Update: Jun 15, 2025

Questions: 326

1z0-071 PDF

$29.75 ~~$84.99~~

Add to Cart

1z0-071 Testing Engine

$35 ~~$99.99~~

Add to Cart

1z0-071 PDF + Testing Engine

$47.25 ~~$134.99~~

Add to Cart

Summer Special Limited Time 65% Discount Offer - Ends in 0d 00h 00m 00s - Coupon code: geek65

clapgeek logo

1z0-071 Oracle Database 12c SQL Questions and Answers

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Options:

Answer:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options: