Weekend Sale Limited Time Flat 70% Discount offer - Ends in 0d 00h 00m 00s - Coupon code: 70spcl

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

Page: 1 / 32
Total 318 questions

Oracle Database 12c SQL Questions and Answers

Question 1

Examine the data in the CUST_NAME column of the CUSTOMERS table:

CUST_NAME

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

Renske Ladwig

Jason Mallin

Samuel McCain

Allan MCEwen

Irene Mikkilineni

Julia Nayer

You want to display the CUST_NAME values where the last name starts with Mc or MC.

Which two WHERE clauses give the required result?

Options:

A.

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

B.

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

C.

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

D.

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

E.

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

Question 2

Which statement falls to execute successfully?

Options:

A.

SELECT *

FROM employees e

JOIN department d

WHERE e.department_id=d.department_id

AND d.department_id=90;

B.

SELECT *

FROM employees e

JOIN departments d

ON e.department_id=d.department_id

WHERE d.department_id=90;

C.

SELECT *

FROM employees e

JOIN departments d

ON e.department_id=d.department_id

AND d.department_id=90;

D.

SELECT *

FROM employees e

JOIN departments d

ON d.departments_id=90

WHERE e.department_id=d.department_id;

Question 3

SELECT *

FROM bricks,colors;

Which two statements are true?

Options:

A.

You can add an ON clause with a join condition.

B.

You can add a WHERE clause with filtering criteria.

C.

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

D.

You can add a USING clause with a join condition.

E.

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

Question 4

Examine the data in the COLORS table:

Question # 4

Examine the data in the BRICKS table:

Question # 4

Which two queries return all the rows from COLORS?

Options:

A.

Option A4

B.

Option B4

C.

Option C4

D.

Option D4

E.

Option E4

Question 5

Examine the description of the EMPLOYEES table:

Question # 5

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

Options:

A.

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

B.

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

C.

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

D.

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

Question 6

Which three actions can you perform by using the ALTER TABLE command?

Options:

A.

Drop pseudo columns from a table.

B.

Restrict all DML statements on a table.

C.

Drop all columns simultaneously from a table.

D.

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

E.

Rename a table

F.

Enable or disable constraints on a table.

Question 7

Which two statements are true about the WHERE and HAVING clauses in a SELECT statement?

Options:

A.

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

B.

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

C.

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

D.

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

E.

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

Question 8

Examine the description of the BOOKS_TRANSACTIONS table:

Question # 8

Examine this partial SQL statement:

SELECT * FROM books_transactions

Which two WHERE conditions give the same result?

Options:

A.

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

B.

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

C.

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

D.

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

E.

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

Question 9

Which is the default column or columns for sorting output from compound queries using SET operators such as INTERSECT in a SQL statement?

Options:

A.

The first column in the last SELECT of the compound query

B.

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

C.

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

D.

The first column in the first SELECT of the compound query

E.

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

Question 10

Which three statements are true about sequences in a single instance Oracle database?

Options:

A.

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

B.

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

C.

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

D.

A sequence can issue duplicate values.

E.

Sequences can always have gaps.

F.

A sequence can only be dropped by a DBA.

Question 11

You create a table by using this command:

CREATE TABLE rate_list (rate NUMBER(6,2));

Which two are true about executing statements?

Options:

A.

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

B.

INSERT INTO rate_list VALUES (0.999) produces an error.

C.

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

D.

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

E.

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

F.

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

Question 12

In your session, the NLS._DAE_FORMAT is DD- MM- YYYY.There are 86400 seconds in a day.Examine

this result:

DATE

02-JAN-2020

Which statement returns this?

Options:

A.

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

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

FROM DUAL;

B.

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

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

FROM DUAL;

C.

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

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

FROM DUAL;

D.

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

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

FROM DUAL;

E.

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

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

FROM DUAL;

Question 13

Which three statements are true about views in an Oracle database?

Options:

A.

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

B.

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

C.

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

D.

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

E.

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

F.

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

G.

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

Question 14

Which statement is true about the INTERSECT operator used in compound queries?

Options:

A.

It processes NULLS in the selected columns.

B.

INTERSECT is of lower precedence than UNION or UNION ALL.

C.

It ignores NULLS.

D.

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

Question 15

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

Options:

A.

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

B.

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.

C.

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

D.

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

E.

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.

F.

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

G.

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

Question 16

Examine this partial command:

Question # 16

Which two clauses are required for this command to execute successfully?

Options:

A.

the DEFAULT DIRECTORY clause

B.

the REJECT LIMIT clause

C.

the LOCATION clause

D.

the ACCESS PARAMETERS clause

E.

the access driver TYPE clause

Question 17

Which three statements are true about performing Data Manipulation Language (DML) operations on a view In an Oracle Database?

Options:

A.

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

B.

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

C.

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.

D.

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

E.

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

F.

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.

Question 18

Examine this incomplete query:

SELECT DATA’2019-01-01’+

FROM DUAL;

Which three clauses can replaceti add 22 hours to the date?

Options:

A.

INTERVAL ‘12:00’

B.

INTERVAL’0,5’DAY

C.

INTERVAL’12’ HOUR

D.

INTERVAL’720’MINUTE

E.

INTERVAL’0 12’DAY TO HOUR

F.

INTERVAL’11:60’HOUR TO MINUTE

Question 19

Examine this SQL statement:

SELECT cust_id, cus_last_name "Last Name"

FROM customers

WHERE country_id = 10

UNION

SELECT cust_id CUST_NO, cust_last_name

FROM customers

WHERE country_id = 30

Identify three ORDER BY clauses, any one of which can complete the query successfully.

Options:

A.

ORDERBY 2, 1

B.

ORDER BY "CUST_NO"

C.

ORDER BY 2,cust_id

D.

ORDER BY CUST_NO

E.

ORDER BY "Last Name"

Question 20

Which three are true about subqueries?

Options:

A.

A subquery can be used in a WHERE clause.

B.

A subquery can be used in a HAVING clause.

C.

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

D.

E.

A subquery cannot be used in a FROM clause.

F.

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

G.

A subquery cannot be used in the select list.

Question 21

Examine the description of the CUSTOMERS table:

Question # 21

You need to display last names and credit limits of all customers whose last name starts with A or B In lower or upper case, and whose credit limit is below 1000.

Examine this partial query:

SELECT cust_last_nare, cust_credit_limit FROM customers

Which two WHERE conditions give the required result?

Options:

A.

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

B.

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

C.

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

D.

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

E.

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

Question 22

Which three queries use valid expressions?

Options:

A.

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

B.

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

C.

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

D.

SELECT product_id, expiry_date * 2 from products;

E.

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

F.

SELECT product_id, unit_price, unit_price + surcharge FROM products;

Question 23

Examine the description or the BOOKS_TRANSACTIONS table:

Question # 23

FOR customers whose income level has a value, you want to display the first name and due amount as 5% of their credit limit. Customers whose due amount is null should not be displayed.

Which query should be used?

Options:

A.

SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT

FROM customers

WHERE cust income_level !=NULL

AND cust credit_level !=NULL;

B.

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;

C.

SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT

FROM customers

WHERE cust income_level <> NULL

AND due_amount <> NULL;

D.

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;

E.

SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT

FROM customers

WHERE cust income_level !=NULL

AND due_amount !=NULL;

Question 24

Examine the description of the ENPLYEES table:

Question # 24

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

Options:

A.

SELECT ”

FROM employees

WHERE salary > ANY

SELECT AVG (salary)

EROM employees

GROUP BY department_ id);

B.

SELECT

FROM employees

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

C.

SELECT”

FROM employees e1

WHERE salary >!

SELECT AVG (salary)

FROM employees e2

WHERE e1. Department _id = e2, department_ id

D.

SELECT.

FROM

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

FROM employees e

WHERE salary > avg_ sal;

E.

SELECT”

FROM employees

WHERE salary >

( SELECT AVG

(salary) FROM

employees

GROUP BY department _ id

Question 25

Table ORDER_ITEMS contains columns ORDER_ID, UNIT_PRICE and QUANTITY, of data type NUMBER

Statement 1:

SELECT MAX (unit price*quantity) "Maximum Order FROM order items;

Statement 2:

SELECT MAX (unit price*quantity "Maximum order" FROM order items GROUP BY order id;

Which two statements are true?

Options:

A.

Statement 2 returns only one row of output.

B.

Both the statement given the same output.

C.

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

D.

Statement 2 may return multiple rows of output.

E.

Statement 1 returns only one row of output.

Question 26

Which statement will execute successfully?

Options:

A.

SELECT 1, 2 FROM DUAL

UNION

SELECT 3, 4 FROM DUAL

ORDER BY 1, 2;

B.

SELECT 3 FROM DUAL

UNION

SELECT 4 FROM DUAL

ORDER BY 3 ;

C.

SELECT 1, 2 FROM DUAL

UNION

SELECT 3, 4 FROM DUAL

ORDER BY 3, 4;

D.

SELECT 1 FROM DUAL

UNION

SELECT 2 FROM DUAL

ORDER BY 1, 2;

Question 27

Which three statements are true about Structured Query Language (SQL)?

Options:

A.

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

B.

It best supports relational databases

C.

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

D.

It requires that data be contained in hierarchical data storage

E.

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

F.

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

Question 28

Examine the data in the ORDERS table:

Question # 28

Examine the data in the INVOICES table:

Question # 28

Examine this query:

SELECT order_ id, order_ date FROM orders

INTERSECT

SELECT order_ 1d, order_ date FROM invoices;

Which two rows will it return?

Options:

A.

3

B.

2

C.

1

D.

5 01-MAR-2019

E.

4 01-FEB-2019

F.

3 01-JAN-2019

Question 29

Examine these statements executed in a single Oracle session:

CREATE TABLE product (pcode NUMBER(2),pname VARCHAR2(20));

INSERT INTO product VALUES(1,'pen');

INSERT INTO product VALUES (2,'pencil');

INSERT INTO product VALUES(3,'fountain pen');

SAVEPOINT a;

UPDATE product SET pcode=10 WHERE pcode =1;

COMMIT;

DELETE FROM product WHERE pcode =2;

SAVEPOINT b;

UPDATE product SET pcode=30 WHERE pcode =3;

SAVEPOINT c;

DELETE FROM product WHERE pcode =10;

ROLLBACK TO SAVEPOINT b;

COMMIT;

Which three statements are true?

Options:

A.

The code for pen is 10.

B.

There is no row containing fountain pen.

C.

There is no row containing pencil.

D.

The code for pen is 1.

E.

The code for fountain pen is 3

F.

There is no row containing pen

Question 30

Examine the description of the sales table.

The sales table has 55,000 rows.

Examine this statements:

Which two statements are true?

Options:

A.

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

B.

SALES1 created with 55, 000 rows

C.

SALES1 created with no rows.

D.

SALES1 created with 1 row.

E.

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

Question 31

Examine the data in the EMPLOYEES table:

Question # 31

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

Options:

A.

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

B.

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

C.

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

D.

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

Question 32

Examine this Statement which returns the name of each employee and their manager,

SELECT e.last name AS emp,,m.last_name AS mgr

FROM employees e JOIN managers m

ON e.manager_ id = m. employee_ id ORDER BY emp;

You want to extend the query to include employees with no manager. What must you add before JOIN to do this?

Options:

A.

CROSS

B.

FULL OUTER

C.

LEFT OUTER

D.

RIGHT OUTER

Question 33

The CUSTOMERS table has a CUST_CREDT_LIMIT column of data type number.

Which two queries execute successfully?

Options:

A.

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

B.

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

C.

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

D.

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

E.

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

Question 34

Examine this query:

SELECT employee_id, first_name, salary

FROM employees

WHERE hiredate > 61*

Which two methods should yours to prevent prompting for hire date value when this queries executed?

Options:

A.

Execute the SET VERIFY ON command before executing the query.

B.

Execute the SET VERIFY OFF command before executing the query.

C.

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

D.

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

E.

Use the UNDEFINE command before executing the query.

F.

Use the DEFINE command before executing the query

Question 35

Examine the description of the MEMBERS table;

SELECT city,last_name LNAME FROM members …

You want to display all cities that contain the string AN. The cities must be returned in ascending order, with the last names further sorted in descending order.

Which two clauses must you add to the query?

Options:

A.

ORDER BY 1,2.

B.

ORDER BY last_name DESC,city ASC

C.

CORADER BY 1, LNAME DESC

D.

WHERE city=’%AN%;

E.

WHERE city LIKE ’%AN%;

F.

WHERE city IN (’%AN%’)

Question 36

You have the privileges to create any type of synonym.

Which stalement will create a synonym called EMP for the HCM.EMPLOYEE_RECORDS table that is accesible to all users?

Options:

A.

CREATE GLOBAL SYNONYM emp FOR hcm.employee_records;

B.

CREATE SYNONYM emp FOR hcm.employee_records;

C.

CREATE SYNONYM PUBLIC.emp FOR hcm.employee_records;

D.

CREATE SYNONYM SYS.emp FOR hcm.employee_records;

E.

CREATE PUBLIC SYNONYM emp FOR hcm. employee_records;

Question 37

Which two are true about granting object privileges on tables, views, and sequences?

Options:

A.

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

B.

REFERENCES can be grantrd only on tables.

C.

INSERT can be granted only on tables and sequences.

D.

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

E.

ALTER can be granted only on tables and sequences.

Question 38

Question # 38

Which two queries will result in an error?

Options:

A.

SELECT FIRST_NAME LAST_NAME FROM EMPLOYEES;

B.

SELECT FIRST_NAME,LAST_NAME FROM EMPLOYEES;

C.

SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY

FROM EMPLOYEES

WHERE ANNUAL_SALARY > 100000

ORDER BY 12 * SALARY ;

D.

SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY

FROM EMPLOYEES

WHERE 12 * SALARY > 100000

ORDER BY ANNUAL_SALARY;

E.

SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY

FROM EMPLOYEES

WHERE 12 * SALARY > 100000

ORDER BY 12 * SALARY;

F.

SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY

FROM EMPLOYEES

WHERE ANNUAL_SALARY > 100000

ORDER BY ANNUAL_SALARY;

Question 39

Examine this statement,which executes successfully:

In which order are the rows displayed?

Options:

A.

sorted by DEPARTMENT_NAME

B.

sorted by DEPARTMENT_NAME and AVGSAL

C.

sorted by DEPARTMENT_NAME and MAXSAL

D.

sorted by AVGSAL

E.

Sorted by MAXSAL

Question 40

Which statement is true regarding the SESSION_PRIVS dictionary view?

Options:

A.

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

B.

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

C.

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

D.

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

Question 41

Which three actions can you perform only with system privileges?

Options:

A.

Truncate a table in another schema.

B.

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

C.

Log in to a database.

D.

Query any table in a database.

E.

Use the WITH GRANT OPTION clause.

F.

Execute a procedure in another schema.

Question 42

Which statement is true about TRUNCATE and DELETE?

Options:

A.

For large tables TRUNCATE is faster than DELETE.

B.

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

C.

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

D.

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

Question 43

Examine the description of the CUSTONERS table

Question # 43

CUSTON is the PRIMARY KEY.

You must derermine if any customers’derails have entered more than once using a different

costno,by listing duplicate name

Which two methode can you use to get the requlred resuit?

Options:

A.

RIGHT OUTER JOIN with seif join

B.

FULL OUTER JOIN with seif join

C.

SUBQUERY

D.

seif join

E.

LEFT OUTER JOIN with seif join

Question 44

Examine the description of the PROMTIONS table:

Question # 44

You want to display the unique promotion costs in each promotion category.

Which two queries can be used?

Options:

A.

SELECT promo_cost, | pxomo_category FROM promotions ORDER BY 1;

B.

SELECT promo_category, DISTINCT promo_cost PROM promotions ORDER BY 2:

C.

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

D.

SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

E.

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

Question 45

Examine these two queries and their output:

SELECT deptno, dname FROM dept;

Question # 45

SELECT ename, job, deptno FROM emp ORDER BY deptno;

Question # 45

Now examine this query:

SELECT ename, dname

FROM emp CROSS JOIN dept WHERE job = 'MANAGER'

AND dept.deptno IN (10, 20) ;

Options:

A.

64

B.

6

C.

3

D.

12

Question 46

Which two statements are true about single row functions?

Options:

A.

CONCAT: can be used to combine any number of values

B.

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

C.

CEIL: can be used for positive and negative numbers

D.

TRUNC: can be used with NUMBER and DATE values

E.

MOD: returns the quotient of a division operation

Question 47

In your session NLS_ DATE_ FORMAT is set to DD–MON_RR.

Which two queries display the year as four digits?

Options:

A.

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

B.

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

C.

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

D.

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

E.

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

F.

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

Page: 1 / 32
Total 318 questions