Oracle 1z0-071 Oracle Database 12c SQL Exam Practice Test

WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name,’ ’) + 1)) LIKE UPPER('MC%')

WHERE SUBSTR(cust_name, INSTR(cust_name,’ ’) + 1) LIKE 'Mc%’ OR 'MC%’

WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,’ ’) + 1)) IN (‘MC%’,’Mc%’)

WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,’ ') + 1)) LIKE ‘Mc%’

WHERE SUBSTR(cust_name, INSTR(cust_name,’ ‘) + 1) LIKE ‘Mc%’

SELECT *

FROM employees e

JOIN department d

WHERE e.department_id=d.department_id

AND d.department_id=90;

SELECT *

FROM employees e

JOIN departments d

ON e.department_id=d.department_id

WHERE d.department_id=90;

SELECT *

FROM employees e

JOIN departments d

ON e.department_id=d.department_id

AND d.department_id=90;

SELECT *

FROM employees e

JOIN departments d

ON d.departments_id=90

WHERE e.department_id=d.department_id;

You can add an ON clause with a join condition.

You can add a WHERE clause with filtering criteria.

It returns the number of rows in BRICKS plus the number of rows in COLORS.

You can add a USING clause with a join condition.

It returnsthe same rows as SELECT * FROM bricks CROSS JOIN colors.

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;

Drop pseudo columns from a table.

Restrict all DML statements on a table.

Drop all columns simultaneously from a table.

Lock a set of rows in a table CE Rename a table.

Rename a table

Enable or disable constraints on a table.

The WHERE clause can be used to exclude rows after dividing them into groups

WHERE and HAVING clauses can be used in the same statement only if applied to different table columns.

The HAVING clause can be used with aggregating functions in subqueries.

Aggregating functions and columns used in HAVING clauses must be specified in these SELECT list of a query.

The WHERE clause can be used to exclude rows before dividing them into groups.

WHERE (borrowed_date = SYSDATE AND transaction_type = 'RM') OR member_id IN ('A101','A102');

WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' OR member_id IN('A101','A102');

WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' OR member_id IN('A101','A102');

WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' AND (member_id = 'A101' OR member_id = 'A102'));

WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' AND member_id = 'A101' OR member_id = 'A102');

The first column in the last SELECT of the compound query

The first NUMBER column in the first SELECT of the compound query

The first VARCHAR2 column in the first SELECT of the compound query

The first column in the first SELECT of the compound query

The first NUMBER or VARCHAR2 column in the last SELECTof the compound query

A sequence's unallocated cached values are lost if the instance shuts down.

Two or more tables cannot have keys generated from the same sequence.

A sequence number that was allocated can be rolled back if a transaction fails.

A sequence can issue duplicate values.

Sequences can always have gaps.

A sequence can only be dropped by a DBA.

INSERT INTO rate_list VALUES (-.9) inserts the value as -.9.

INSERT INTO rate_list VALUES (0.999) produces an error.

INSERT INTO rate_list VALUES (-10) produces an error.

INSERT INTO rate_list VALUES (87654. 556) inserts the value as 87654.6.

INSERT INTO rate_list VALUES (0.551) inserts the value as .55.

INSERT INTO rate_list VALUES (-99.99) inserts the value as 99.99.

SELECT TO_ CHAR(TO_ DATE(‘29-10-2019’) +INTERVAL ‘2’; MONTH + INTERVAL ‘5’; DAY -

INTERVAL ‘86410’ SECOND, ‘ DD-MON-YYYY’) AS "date"

FROM DUAL;

SELECT TO_ CHAR(TO_ DATE(‘29-10-2019’) + INTERVAL ‘3’ MONTH + INTERVAL ‘7’ DAY -

INTERVAL ‘360’ SECOND, ‘ DD-MON-YYYY’) AS "date"

FROM DUAL;

SELECT To CHAR(TO _DATE(‘29-10-2019’) + INTERVAL ‘2’ NONTH + INTERVAL ‘5’ DAY

INEERVAL ‘120’ SECOND, ‘ DD-MON-YYY) AS "date"

FROM DUAL;

SELECT-TO_CHAR(TO _DATE(‘29-10-2019’+ INTERVAL ‘2’ MONTH+INTERVAL ‘6’ DAYINTERVAL

‘120’ SECOND, ‘DD-MON-YY’) AS "daTe"

FROM DUAL;

SELECT-TO_CHAR(TO _DATE(‘29-10-2019’+ INTERVAL ‘2’ MONTH+INTERVAL ‘4’ DAYINTERVAL

‘120’ SECOND, ‘DD-MON-YY’) AS "daTe"

FROM DUAL;

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

The WITH CHECK clause prevents certain rows from being updated or inserted.

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.

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

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

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

It processes NULLS in the selected columns.

INTERSECT is of lower precedence than UNION or UNION ALL.

It ignores NULLS.

Multiple INTERSECT operators are not possible in the same SQL statement.

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

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.

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

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

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.

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.

the DEFAULT DIRECTORY clause

the REJECT LIMIT clause

the LOCATION clause

the ACCESS PARAMETERS clause

the access driver TYPE clause

Insert statements can always be done on a table through a view.

The WITH CHECK clause has no effect when deleting rows from the underlying table through the view.

Views cannot be used to query rows from an underlying table if the table has a PRIPOARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view.

Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword.

Views cannot be used to add on modify rows in an underlying table if the defining query of the view contains aggregating functions.

Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.

INTERVAL ‘12:00’

INTERVAL’0,5’DAY

INTERVAL’12’ HOUR

INTERVAL’720’MINUTE

INTERVAL’0 12’DAY TO HOUR

INTERVAL’11:60’HOUR TO MINUTE

ORDERBY 2, 1

ORDER BY "CUST_NO"

ORDER BY 2,cust_id

ORDER BY CUST_NO

ORDER BY "Last Name"

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.

WHERE UPPER(cust_last_name) IN ('A%', 'B%') AND cust_credit_limit < 1000:

WHERE (INITCAP(cust_last_name) LIKE ‘A%' OR ITITCAP(cust_last_name) LIKE ‘B%') AND cust_credit_limit < 1000

WHERE UPPER(cust_last_name) BETWEEN UPPER('A%' AND 'B%’) AND ROUND(cust_credit_limit) < 1000;

WHERE (UPPER(cust_last_name) LIKE 'A%’ OR UPPER(cust_last_name) LIKE ‘B%’) AND ROUND(cust_credit_limit) < 1000;

WHERE (UPPER(cust_last_name) like INITCAP ('A') OR UPPER(cust_last_name) like INITCAP('B')) AND ROUND(cust_credit_limit) < ROUND(1000) ;

SELECT product_id,(unit_price * 0.15 / (4.75 + 552.25)) FROM products;

SELECT product_id,(expiry_date - delivery_date) * 2 FROM products;

SELECT product_id,unit_price || 5 "Discount" , unit_price + surcharge - discount FROM products;

SELECT product_id, expiry_date * 2 from products;

SELECT product_id,unit_price,5 "Discount", unit_price + surcharge-discount FROM products;

SELECT product_id, unit_price, unit_price + surcharge FROM products;

SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT

FROM customers

WHERE cust income_level !=NULL

AND cust credit_level !=NULL;

SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT

FROM customers

WHERE cust income_level IS NOT NULL

AND due_amount IS NOT NULL;

SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT

FROM customers

WHERE cust income_level <> NULL

AND due_amount <> NULL;

SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT

FROM customers

WHERE cust_income_level IS NOT NULL

AND cust_credit_limit IS NOT NULL;

SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT

FROM customers

WHERE cust income_level !=NULL

AND due_amount !=NULL;

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

Statement 2 returns only one row of output.

Both the statement given the same output.

Both statements will return NULL if either UNIT PRICE or QUANTITY contains NULL,

Statement 2 may return multiple rows of output.

Statement 1 returns only one row of output.

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;

It guarantees atomicity, consistency, isolation, and durability (ACID) features

It best supports relational databases

It is used to define encapsulation and polymorphism for a relational table

It requires that data be contained in hierarchical data storage

It is the only language that can be used for both relational and object-oriented databases

It provides independence for logical data structures being manipulated from the underlying physical data storage

3

2

1

5 01-MAR-2019

4 01-FEB-2019

3 01-JAN-2019

The code for pen is 10.

There is no row containing fountain pen.

There is no row containing pencil.

The code for pen is 1.

The code for fountain pen is 3

There is no row containing pen

SALES1 has PRIMARY KEY and UNIQUE constraints on any selected columns which had those constraints in the SALES table.

SALES1 created with 55, 000 rows

SALES1 created with no rows.

SALES1 created with 1 row.

SALES1 has NOT NULL constraints on any I selected columns which had those constraints I in the SALES table.

SELECT last _ NAME (monthly_ salary + monthly _commission _ pct) * 12 AS annual_ comp FROM employees;

select last _ name, (monthly_ salary * 12) + (monthly_ salary * 12 *monthly_ commission_ pct) AS annual_ camp FROM employees

SELECT last _ name, (monthly_ salary * 12) + (monthly_ salary * 12 * NVL (monthly_ commission _pct, 0)) AS annual _comp

SELECT last _ name, (monthly _ salary * 12) + (monthly_ commission _ pct * 12) AS FROM employees:

CROSS

FULL OUTER

LEFT OUTER

RIGHT OUTER

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

SELECT NVL2(cust_credit_limit * .15,'Not Available') FROM customers;

SELECT NVL(cust_credit_limit * .15,'Not Available') FROM customers;

SLECT NVL(TO_CHAR(cust_credit_limit * .15),'Not available') from customers;

SELECT NVL2(cust_credit_limit,TO_CHAR(cust_credit_limit * .15),'NOT Available') FROM customers;

Execute the SET VERIFY ON command before executing the query.

Execute the SET VERIFY OFF command before executing the query.

Store the query in a script and pass the substitution value to the script when executing it.

Replace 's1' with &1'in the query:

Use the UNDEFINE command before executing the query.

Use the DEFINE command before executing the query

ORDER BY 1,2.

ORDER BY last_name DESC,city ASC

CORADER BY 1, LNAME DESC

WHERE city=’%AN%;

WHERE city LIKE ’%AN%;

WHERE city IN (’%AN%’)

CREATE GLOBAL SYNONYM emp FOR hcm.employee_records;

CREATE SYNONYM emp FOR hcm.employee_records;

CREATE SYNONYM PUBLIC.emp FOR hcm.employee_records;

CREATE SYNONYM SYS.emp FOR hcm.employee_records;

CREATE PUBLIC SYNONYM emp FOR hcm. employee_records;

DELETE can be granted on tables, views, and sequences.

REFERENCES can be grantrd only on tables.

INSERT can be granted only on tables and sequences.

SELECT can be granted on tables, views, and sequences.

ALTER can be granted only on tables and sequences.

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;

sorted by DEPARTMENT_NAME

sorted by DEPARTMENT_NAME and AVGSAL

sorted by DEPARTMENT_NAME and MAXSAL

sorted by AVGSAL

Sorted by MAXSAL

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.

Truncate a table in another schema.

Access flat files via a database, which are stored in an operating system directory.

Log in to a database.

Query any table in a database.

Use the WITH GRANT OPTION clause.

Execute a procedure in another schema.

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.

RIGHT OUTER JOIN with seif join

FULL OUTER JOIN with seif join

SUBQUERY

seif join

LEFT OUTER JOIN with seif join

SELECT promo_cost, | pxomo_category FROM promotions ORDER BY 1;

SELECT promo_category, DISTINCT promo_cost PROM promotions ORDER BY 2:

SELECT DISTINCT promo_category ||'has’|| promo_cost AS COSTS FROM promotions ORDER BY 1;

SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

SELECT DISTINCT promo_cost ||' in' II DISTINCT promo_category FROM promotions ORDER BY 1;

64

6

3

12

CONCAT: can be used to combine any number of values

FLOOR: returns the smallest integer greater than or equal to a specified number

CEIL: can be used for positive and negative numbers

TRUNC: can be used with NUMBER and DATE values

MOD: returns the quotient of a division operation

SELECT TO_DATE(TO_CHAR(SYSDATE,'MM/DD/YYYY'),'MM/DD/YYYY') FROM DUAL;

SELECT TO_CHAR (ADD_MONTHS (SYSDATE,6)) FROM DUAL;

SELECT TO_DATE (SYSDATE, 'RRRR-MM-DD') FROM DUAL;

SELECT TO_DATE (ADD_MONTHS(SYSDATE,6), 'dd-mon-yyyy') FROM DUAL;

SELECT TO_CHAR (SYSDATE, 'MM/DD/YYYY') FROM DUAL;

SELECT TO_CHAR (ADD_MONTHS (SYSDATE, 6), 'dd-mon-yyyy') FROM DUAL;