Free 1Z0-071 Exam Dumps Preview

SELECT REPLACE (TRIM(TRALING'son' FROM cust_last_name),'An','O') FROM customers;

SELECT INITCAP (REPLACE(TRIM('son' FROM cust_last_name),'An','O')) FROM customers;

SELECT REPLACE (SUBSTR(cust_last_name,-3),'An','O') FROM customers;

INSERT INTO orders
VALUES (1,'09-mar-2007', `online','', 1000);

INSERT INTO orders
(order_id,order_date,order_mode,
(customer_id,order_total)
VALUES (1,TO_DATE(NULL), `online', 101, NULL);

INSERT INTO orders
(order_id,order_date,order_mode,order_total)
VALUES (1,'10-mar-2007',`online',1000);

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

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

Date Manipulation Language (DML) can always be used on views.

Inserting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error.

PROMPT Enter table name &x -
SELECT employee_id FROM &x WHERE last_name = `King';

DEFINE x = `employees'
PROMPT Enter table name &x -
SELECT employee_id FROM &x WHERE last_name = `King';

PROMPT Enter table name &x -
SELECT employee_id FROM &&x WHERE last_name = `King';

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

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

SELECT * FROM employees WHERE COALESCE(salary, commission) >= 20000;

WHERE order_date > TO_DATE(ADD_MONTH(SYSDATE, 6), `MON DD YYYY')

WHERE order_date IN (TO_DATE(`Oct 21 2018', `Mon DD YYYY'), TO_CHAR(`Nov 21 2018', `Mon
DD YYYY'))

WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE, 6), `MON DD YYYY')

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

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

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

It fails unless the expression is modified to TO-CHAR(SUNBSTR(SYSDATE,1,5)

It fails unless the expression is modified to SUBSTR (TO_CHAR(SYSDATE),1,5)

It fails unless the expression is modified to SUBSTR (TO_CHAR(TRUNC(SYSDATE)),1,5)

Option B

Option C

Option D

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

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

SELECT TO_CHAR (`11-oct-2007', `fmDdspth "of" Month, Year')

FROM DUAL

SELECT TO_CHAR (TO_DATE (`11-oct-2007'), `fmDdspth of month, year')
FROM DUAL

SELECT TO_DATE (TO_CHAR ('11-oct-2007'), `fmDdspth "of" Month, Year'))
FROM DUAL

Synonyms can be created for roles.

Synonyms cannot be created for sequences.

Synonyms cannot be created for synonyms.

Users must have the DBA role to create public synonyms.

A scalar subquery expression that returns zero rows evaluates to zoro

They cannot be used in the values clause of an insert statement*

They can be used as default values for columns in a create table statement.

Table aliases must be used.

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

The USING clause can be used.

It displays values for variables created by the DEFINE command.

It can be used only in SQL*plus.

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

LEFT OUTER JOIN with self join

PULL OUTER JOIN with self join

RIGHT OUTER JOIN with self join

SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id HAVING hire_date > '01-
JAN-19';

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

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

Remove the GROUP BY clause from the subquery and place it in the main query

Replace = with the >ANY operator

Replace = with the >ALL operator

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

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

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

INTERVAL YEAR TO MONTH columns only support monthly intervals within a single year.

The YEAR field in an INTERVAL YEAR TO MONTH column must be a positive value.

INTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years.

The value in an INTERVAL DAY TO SECOND column can be copied into an INTERVAL YEAR TO
MONTH column.

DELETE <sequencename> would remove a sequence from the database.

The numbers generated by an explicitly defined sequence can only be used to insert data in one table.

When a database instance shuts down abnormally, sequence numbers that have been cached but not used are available again when the instance is restarted.

It must be an equijoin.

It must be a full outer join.

The join key column must have an index.

first normal form

third normal form

fourth normal form

They can be performed on remote tables.

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

They can be performed on views.

The subquery is executed before the DELETE statement is executed.

The subquery is not a correlated subquery.

All existing rows in the EMPLOYEES table are deleted.

CONCAT joins two or more character strings together.

FLOOR returns the largest positive integer less than or equal to a specified number.

INSTR finds the offset within a character string, starting from position 0.

INSTR finds the offset within a string of a single character only.

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

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

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

DML statements always start new transactions.

DDL statements automatically commit only data dictionary updates caused by executing the DDL.

A session can see uncommitted updates made by the same user in a different session.

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.

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;

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

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

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

You can use a column alias in the GROUP BY clause.

Using the WHERE clause after the GROUP BY clause excludes rows after creating groups.

The GROUP BY clause is mandatory if you are using an aggregating function in the SELECT clause.

ALTER VIEW emp_view ADD (employee.manager_id);

ALTER VIEW emp_view MODIFY (
SELECT employee_id, employee_name, department_name, manager_id
FROM employees e, departments d - WHERE e.department_id = d.department_id);

ALTER VIEW emp_view ADD (SELECT manager_id FROM employees);

64

3

12

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

The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause 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.

1, 2 and 3

2 and 3

1 and 2

The ROLLBACK statement does not release locks resulting from table updates.

Data Control L anguage (DCL) statements, such as GRANT and REVOKE, can be rolled back.

Data consistency is not guaranteed after a rollback.

Option A

Option B

Option C

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

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

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

Sequence ORD_SEQ is guaranteed not to generate duplicate numbers.

Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times.explicit value is given for ORD_NO.

If sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows

inserted into ORD_ITEMS.

During parsing, a SQL statement containing literals in the WHERE clause that has been executed by

any session and Which is cached in memory, is always reused for the current execution.

During row source generation, rows that satisfy the query are retrieved from the database and stored in memory.

During optimization, execution plans are formulated based on the statistics gathered by the database instance, and the lowest cost plan is selected for execution.

Option A

Option B

Option C

The structure of the TEST table is removed.

All the constraints on the TEST table are dropped.

Removed rows can not be recovered using the ROLLBACK command.

INTERVAL '0.5' DAY

INTERVAL '11:60' HOUR TO MINUTE

INTERVAL '12:00' HOUR TO SECOND

GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted select on the table.

A DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back.

GLOBAL TEMPORARY TABLE space allocation occurs at session start.

A GLOBAL TEMPORARY TABLE can have only one index.

DML on GLOBAL TEMPORARY TABLES generates no REDO.

A GLOBAL TEMPORARY TABLE cannot have a PUBLIC SYNONYM.

TO_NUMBER(PROMO_BEGIN_DATE) ?5 will return a number.

PROMO_BEGIN_DATE ?SYSDATE will return an error.

TO_DATE(PROMO_BEGIN_DATE * 5) will return a date.

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.

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

column positions must be used in the order by clause.

Each select statement in the compound query must have its own order by clause.

Each select statement in the compound query can have its own order by clause.

The foreign key constraint on DEPT_ID must be defined at the table level instead of the column level.

The primary key constraint on EMP_ID must have a name.

The word CONSTRAINT in the foreign key constraint on DEPT_ID must be changed to FOREIGN
KEY.

The default length for a CHAR column is always one character.

A VARCHAR2 blank-pads column values only in the data stored is non-numeric and contains no special characters.

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

SELECT INITCAP(TRIM(`H' FROM `Hello World')) FROM DUAL;

SELECT SUBSTR(`Hello World', 2) FROM DUAL;

SELECT LOWER(SUBSTR(`Hello World', 2, 1)) FROM DUAL;

A one-to-one relationship is always a self-referencing relationship.

A many-to-many relationship can be implemented only by using foreign keys.

A table name can be specified just once when selecting data from a table having a self-referencing relationship.

Using aggregate functions in the HAVING clause requires a subquery.

Using single-row functions in the WHERE clause requires a subquery.

Using single-row functions in the HAVING clause requires a subquery.

Option A

Option C

Option D

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 + 20 FROM employees;

SELECT prod_id "CURRENT AVAILABILITY" || 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 * FROM customers WHERE insert_date=DATE'2019-01-01';

SELECT * FROM customers WHERE customer_id=0001;

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

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

It drops any triggers defined on the table.

It always retains the space used by the removed rows.

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

NULLS are not included in the sort operation.

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 clause.

SYSDATE can be used in expressions only if the default date format is DD-MON-RR.

CURRENT_TIMESTAMP returns the same date as CURRENT_DATE.

SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server.

SYSDATE can be queried only from the DUAL table.

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 USING (product id) AND quantity .on hand < 5;

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.