Summer Special 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 324 questions

Oracle Database 12c SQL Questions and Answers

Question 1

Which two statements are true about selecting related rows from two tables based on entity relationship diagram (ERD)?

Options:

A.

Relating data from a table with data from the same table is implemented with a self join.

B.

An inner join relates rows within the same table.

C.

Rows from unrelated tables cannot be joined.

D.

Implementing a relationship between two tables might require joining additional tables.

E.

Every relationship between the two tables must be implemented in a Join condition.

Question 2

In the PROMOTIONS table, the PROMO_ BEGIN_DATE column is of data type and the default date format is DD-MON-RR

Which two statements are true about expressions using PROMO_ BEGIN_DATE in a query?

Options:

A.

TONUMBER (PROMO BEGIN_DATE) - 5 will return a number

B.

PROMO_ BEGIN_DATE - 5 will return a date

C.

PROMO_ BEGIN_DATE - SYSDATE will return a number

D.

PROMO_ BEGIN_DATE - SYSDATE will return an error

E.

TODATE(PROMO BEGIN_DATE *5) will return a date

Question 3

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 4

Which two are true about granting privilege on objects?

Options:

A.

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

B.

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

C.

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

D.

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

E.

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

Question 5

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 6

Which two statements about INVISIBLE indexes are true?

Options:

A.

an INVISIBLE Index consumes no storage

B.

You can only create one INVISIBLE index on the same column list

C.

The query optimizer never considers INVISIBLE Indexes when determining execution plans

D.

You use AlTER INDEX to make an INVISIBLE Index VISIBLE

E.

All INSERT, UPDATE, and DELETE statements maintain entries in the index

Question 7

Which three are true about multiple INSERT statements?

Options:

A.

They can be performed only by using a subquery.

B.

They can be performed on relational tables.

C.

They can be performed on views.

D.

They can be performed on remote tables.

E.

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

F.

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

Question 8

Which two statements execute successfully?

Options:

A.

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

=AMERICAN' ) FROM DUAL;

B.

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

AMERICAN')

FROM DUAL;

C.

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

FROM DUAL;

D.

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

FROM DUAL

E.

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

FROM DUAL

Question 9

Which two statements are true about an Oracle database?

Options:

A.

A table can have multiple primary keys.

B.

A table can have multiple foreign keys.

C.

A NUMBER column without data has a zero value.

D.

A column definition can specify multiple data types.

E.

A VARCHAR2 column without data has a NULL value.

Question 10

You own table DEPARTMENTS, referenced by views, indexes, and synonyms.

Examine this command which executes successfully:

DROP TABLE departments PURGE;

Which three statements are true?

Options:

A.

Neither can it be rolled back nor can the DEPARTMENTS table be recovered.

B.

It will remove all views that are based on the DEPARTMENTS table.

C.

It will delete all rows from the DEPARTMENTS table, but retain the empty table.

D.

It will remove the DE PARTMENTS table from the database.

E.

It will remove all synonyms for the DEPARTMENTS table.

F.

It will drop all indexes on the DEPARTMENTS table.

Question 11

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 12

Which two are true about transactions in the Oracle Database?

Options:

A.

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

B.

A DDL statement issued by a session with an uncommitted transation automaticall commits that transaction.

C.

An uncommitted transaction is automatically committed when the user exits SQL*PLUS

D.

DML statements always start new transactions.

E.

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

Question 13

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 14

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 15

The STORES table has a column START_DATE of data type DATE, containing the datethe row was inserted.

You only want to display details of rows where START_DATEis within the last 25 months.which WHERE clause can be used?

Options:

A.

WHERE TO_NUMBER(start_date - SYSDATE)<=25

B.

WHERE ADD_MONTHS (start date , 25)<= SYSDATE

C.

WHERE MONTHS_BETWEEN(SYSDATE, start_date)<=25

D.

WHERE MONTHS_BETWEEN (start_date, SYSDATE)<=25

Question 16

Examine the description of the CUSTONERS table:

Question # 16

CUSTNO is the PRIMARY KEY.

You must determine if any customers' details have been entered more than once using a different CUSTNO, by listing all duplicate names.

Which two methods can you use to get the required result?

Options:

A.

LEFT OUTER JOIN with self join

B.

PULL OUTER JOIN with self join

C.

subquery

D.

RIGHT OUTER JOIN with self join

E.

self Join

Question 17

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 18

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

Options:

A.

SELECT last_name, salary

FROM employees

WHERE ROWNUM<=3

B.

SELECT last_name,salary

FROM employees

ORDER BY salary

FETCH FIRST 3 ROWS ONLY;

C.

SELECT last_name,salary

FROM employees

WHERE ROWNUM<=3

ORDER BY (SELECT salary FROM employees);

D.

SELECT last_name,salary

FROM (SELECT * FROM employees ORDER BY salary)

E.

SELECT last_name,salary

FROM employees

FETCH FIRST 3 ROWS ONLY

ORDER BY salary;

Question 19

Which two are true about queries using set operators (UNION, UNION ALL, INTERSECT and MINUS)?

Options:

A.

There must be an equal number of columns in each SELECT list.

B.

The name of each column in the first SELECT list must match the name of the corresponding column in each subsequent SELECT list.

C.

Each SELECT statement in the query can have an ORDER BY clause.

D.

None of the set operators can be used when selecting CLOB columns.

E.

The FOR UPDATE clause cannot be specified.

Question 20

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

Options:

A.

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

B.

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

C.

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

D.

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

E.

INTERSECT ignores NULLs

Question 21

Table EMPLOYEES contains columns including EMPLOYEE_ID, JOB_ID and SALARY.

Only the EMPLOYEES_ID column is indexed.

Rows exist for employees 100 and 200.

Examine this statement:

UPDATE employees

SET (job_id, salary) =

(SELECT job_id, salary

FROM employees

WHERE employee_id = 200)

WHERE employee id=100;

Which two statements are true?

Options:

A.

Employees 100 and 200 will have the same SALARY as before the update command.

B.

Employee 100 will have SALARY set to the same value as the SALARY of employee 200.

C.

Employee 100 will have JOB_ID set to the same value as the JOB_ID of employee 200.

D.

Employees 100 and 200 will have the same JOB ID as before the update command.

E.

Employee 200 will have SALARY set to the same value as the SALARY of employee 100.

F.

Employee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100

Question 22

Examine the data in the EMPLOYEES table:

Question # 22

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 23

Which two statements are true regarding the UNION ALL operators?

Options:

A.

NULLS are not ignored during duplicate checking.

B.

Duplicates are eliminated automatically by the UNION ALL operator

C.

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

D.

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

E.

The output is sorted by the UNION ALL operator.

Question 24

Which three statements are true about inner and outer joins?

Options:

A.

A full outer join returns matched and unmatched rows.

B.

A full outer join must use Oracle syntax.

C.

Outer joins can be used when there are multiple join conditions on two tables.

D.

Outer joins can only be used between two tables per query.

E.

An inner join returns matched rows.

F.

A left or right outer join returns only unmatched rows.

Question 25

Which two statements are true about the DUAL table?

Options:

A.

It can display multiple rows and columns.

B.

It can be accessed only by the SYS user.

C.

It can be accessed by any user who has the SELECT privilege in any schema

D.

It can display multiple rows but only a single column.

E.

It consists of a single row and single column of VARCHAR2 data type.

F.

It can be used to display only constants or pseudo columns.

Question 26

Examine the description of the CUSTOMERS table:

Which three statements will do an implicit conversion?

Options:

A.

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

B.

SELECT * FROM customers WHERE customer_id=’0001’;

C.

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

D.

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

E.

SELECT * FROM customers WHERE customer_id=0001;

F.

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

Question 27

Examine the description of the BOOKS table:

Question # 27

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:

A.

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

B.

The second ROLLBACK command does nothing.

C.

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

D.

The second ROLLBACK command replays the delete.

E.

The second ROLLBACK command undoes the insert.

Question 28

Evaluate these commands which execute successfully CREATE SEQUENCE ord_seq

INCREMENT BY 1

START WITH 1

MAXVALUE 100000

CYCLE

CACHE 5000;

Create table ord_items(

ord_no number(4) default ord_seq.nextval not null,

Item_no number(3),

Qty number(3),

Expiry_date date,

Constraint it_pk primary key(ord_no,item_no),

Constraint ord_fk foreign key (ord_no) references orders(ord_no));

Which two statements are true about the ORD_ITEMS table and the ORD_SEQ sequence?

Options:

A.

Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ.

B.

Column ORD_NO gets the next number from sequence ORD_SEQ whenever a row is inserted into ORD_ITEMS and no explicit value is given for ORD_NO.

C.

Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times

D.

IF sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS.

E.

Sequence ORD_SEQ is guaranteed not to generate duplicate numbers.

Question 29

Examine the BRICKS table:

Question # 29

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:

A.

1

B.

16

C.

10

D.

6

E.

4

F.

0

Question 30

Which two are true about the NVL, NVL2, and COALESCE functions?

Options:

A.

The first expression in NVL2 is never returned.

B.

NVL2 can have any number of expressions in the list.

C.

COALESCE stops evaluating the list of expressions when it finds the first null value.

D.

COALESCE stops evaluating the list of expressions when it finds the first non-null value.

E.

NVL must have expressions of the same data type.

F.

NVL can have any number of expressions in the list.

Question 31

Which two statements are true about Oracle databases and SQL?

Options:

A.

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

B.

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

C.

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

D.

A query can access only tables within the same schema.

E.

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

Question 32

What is true about non-equijoin statement performance?

Options:

A.

The between condition always performs less well than using the >= and <= conditions.

B.

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

C.

The join syntax used makes no difference to performance.

D.

The between condition always performs better than using the >= and <= conditions.

E.

Table aliases can improve performance.

Question 33

Which two statements are true about Entity Relationships?

Options:

A.

A Relationship can be mandatory for both entities

B.

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

C.

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

D.

A table name can be specified just once when selecting data from a table having a selfreferencing relationship

E.

A one-to-many relationship in one direction is a one-to-one relationship in the other direction

Question 34

Which two statements are true about substitution variables?

Options:

A.

A substitution variable used to prompt for a column name must be endorsed in single quotation marks.

B.

A substitution variable used to prompt for a column name must be endorsed in double quotation marks.

C.

A substitution variable prefixed with & always prompts only once for a value in a session.

D.

A substitution variable can be used with any clause in a SELECT statement.

E.

A substitution variable can be used only in a SELECT statement.

F.

A substitution variable prefixed with 6 prompts only once for a value in a session unless is set to undefined in the session.

Question 35

Which two statements are true about INTERVAL data types

Options:

A.

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

B.

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

C.

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

D.

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

E.

INTERVAL DAY TO SECOND columns support fractions of seconds.

F.

INTERVAL YEAR TO MONTH columns support yearly intervals.

Question 36

Which two are true about external tables that use the ORACLE _DATAPUMP access driver?

Options:

A.

Creating an external table creates a directory object.

B.

When creating an external table, data can be selected only from a table whose rows are stored in database blocks.

C.

When creating an external table, data can be selected from another external table or from a table whose rows are stored in database blocks.

D.

Creating an external table creates a dump file that can be used by an external table in the same or a different database.

E.

Creating an external table creates a dump file that can be used only by an external table in the same database.

Question 37

Examine this statement which executes successfully:

Which statement will violate the CHECK constraint?

Options:

A.

UPDATE emp80

SET department_id=90

WHERE department_id=80;

B.

DELETE FROM emp80

WHERE department_id=90;

C.

SELECT *

FROM emp80

WHERE department_id=80;

D.

SELECT *

FROM emp80

WHERE department_id=90;

Question 38

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:

A.

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

B.

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

C.

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

D.

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

E.

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

Question 39

Which statement will return a comma-separated list of employee names in alphabetical order for each department in the EMP table?

Options:

A.

SELECT deptno,LISTAGG(ename, ' , ') WITHIN GROUP AS employee_list FROM emp GROUP BY deptno;

B.

SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP AS employee_list FROM emp GROUP BY deptno ORDER BY ename;

C.

SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (GROUP BY deptno) AS employee_list FROM emp ORDER BY ename;

D.

SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS employee_list FROM emp GROUP BY deptno;

Question 40

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

Options:

A.

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

B.

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

C.

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

D.

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

E.

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

Question 41

Which two statements are true about conditional INSERT ALL?

Options:

A.

Each row returned by the subquery can be inserted into only a single target table.

B.

It cannot have an ELSE clause.

C.

The total number of rows inserted is always equal to the number of rows returned by the subquery

D.

A single WHEN condition can be used for multiple INTO clauses.

E.

Each WHEN condition is tested for each row returned by the subquery.

Question 42

Examine these two queries and their output:

SELECT deptno, dname FROM dept;

Question # 42

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

Question # 42

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 43

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 44

Which three are true about system and object privileges

Options:

A.

WITH GRANT OPTION can be used when granting an object privilege to both users and roles

B.

WITH GRANT OPTION cannot be used when granting an object privilege to PUBLIC

C.

Revoking a system privilege that was granted with the WITH ADMIN OPTION has a cascading effect.

D.

Revoking an object privilege that was granted with the WITH GRANT OPTION clause has a cascading effect

E.

Adding a primary key constraint to an existing table in another schema requires a system privilege

F.

Adding a foreign key constraint pointing to a table in another schema requires the REFERENCEs object privilege

Question 45

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

Options:

A.

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

B.

It can never be used with onatural join.

C.

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

D.

It can never be used with a full outer join.

E.

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

Question 46

Which three are true about the CREATE TABLE command?

Options:

A.

It can include the CREATE...INDEX statement for creating an index to enforce the primary key constraint.

B.

The owner of the table should have space quota available on the tablespace where the table is defined.

C.

It implicitly executes a commit.

D.

It implicitly rolls back any pending transactions.

E.

A user must have the CREATE ANY TABLE privilege to create tables.

F.

The owner of the table must have the UNLIMITED TABLESPACE system privilege.

Question 47

Examine the description of the PRODUCTS table:

Question # 47

Which three queries use valid expressions?

Options:

A.

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

B.

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

C.

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

D.

SPLECT product_id, expiry_date * 2 FROM products;

E.

SELEGT product_id, unit_price, unit_price + surcharge FROM products;

F.

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

Question 48

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 49

Which two queries execute successfully?

Options:

A.

SELECT NULLIF(100, 100) FROM DUAL;

B.

SELECT COALESCE(100, NULL, 200) FROM DUAL;

C.

SELECT NULLIF(100, 'A') FROM DUAL;

D.

SELECT NULLIF(NULL, 100) FROM DUAL;

E.

SELECT CO ALESCE(100, 'A' ) FROM DUAL;

Question 50

Examine the data in the INVOICES table:

Question # 50

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:

A.

SELECT currency_ code FROM currencies

MINUS

SELECT currency_ code FROM invoices;

B.

SELECT * FROM currencies

WHERE NOT EXISTS (

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

C.

SELECT currency_ code FROM currencies

INTERSECT

SELECT currency_ code FROM invoices;

D.

SELECT * FROM currencies

MINUS

SELECT * FROM invoices;

Question 51

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 52

Which two statements are true regarding a SAVEPOINT?

Options:

A.

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

B.

Only one SAVEPOINT may be issued in a transaction.

C.

A SAVEPOINT does not issue a COMMIT

D.

Rolling back to a SAVEPOINT can undo a TRUNCATE statement.

E.

Rolling back to a SAVEPOINT can undo a DELETE statement

Question 53

Examine the data in the EMP table:

Question # 53

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:

A.

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

B.

An allas name must not contain space characters.

C.

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

D.

An alias name must always be specified in quotes.

Question 54

In which three situations does a new transaction always start?

Options:

A.

When issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session

B.

When issuing a CREATE INDEX statement after a CREATE TABLE statement completed unsuccessfully in the same session

C.

When issuing a TRUNCATE statement after a SELECT statement was issued in the same session

D.

When issuing a CREATE TABLE statement after a SELECT statement was issued in the same session

E.

When issuing the first Data Manipulation Language (OML) statement after a COMMIT or ROLLBACK statement was issued in the same session

F.

When issuing a DML statement after a DML statement filed in the same session.

Question 55

Which three statements are true about GLOBAL TEMPORARY TABLES?

Options:

A.

A GLOBAL TEMPORARY TABLE cannot have PUBLIC SYNONYM.

B.

A GLOBAL TEMPORARY TABLE can have multiple indexes

C.

A GLOBAL TEMPORARY TABLE can be referenced in the defining query of a view.

D.

Data Manipulation Language (DML) on GLOBAL TEMPORARY TABLES generates no REDO.

E.

A GLOBAL TEMPORARY TABLE can have only one index.

F.

A trigger can be created on a GLOBAL TEMPORARY TABLE

Question 56

Examine these statements and results:

SQL> SELECT COUNT(*) FROM emp

COUNT(*)

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

14

sQL> CREATE GLOBAL TEMPORARY TABLE t emp As SELECT * FROM emp;

Table created

SQL> INSERT INTo temp SELECT * FROM emp;

14 rows created

SQL> COMMIT:

Commit complete*

SQL> INSERT INTo temp SELECT * EROM emp;

14. rows created

SQL> SELECT COUNT(*) FROM t emp

How many rows are retrieved by the last query?

Options:

A.

28

B.

0

C.

14

D.

42

Question 57

Which three items does a direction of a relationship contain?

Options:

A.

an attribute

B.

a cardinality

C.

label

D.

an optionality

E.

a unique identifier

F.

an entity

Question 58

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 59

Examine this query:

SELECT INTERVAL '100' MONTH DURATION FROM DUAL;

What will be the output?

Options:

A.

DURATION

+08-04

B.

DUFATION

+100

C.

DURATION

+08

D.

an error

Question 60

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:

A.

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

B.

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.

C.

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.

D.

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

Question 61

Which two are true about using constraints?

Options:

A.

A FOREIGN KEY column in a child table and the referenced PRIMARY KEY column in the parenttable must have the same names.

B.

A table can have multiple PRIMARY KEY and multiple FOREIGN KEY constraints.

C.

A table can have only one PRIMARY KEY and one FOREIGN KEY constraint.

D.

PRIMARY KEY and FOREIGNY constraints can be specified at the column and at the table level

E.

A table can have only one PRIMARY KEY but may have multiple FOREIGN KEY constraints.

F.

NOT NULL can be specified at the column and at the table level.

Question 62

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:

Question # 62

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

Options:

A.

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

B.

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

C.

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

D.

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

Question 63

Examine the description of the EMPLOYEES table:

Question # 63

Examine this query:

Question # 63

Which line produces an error?

Options:

A.

Line 7

B.

Line 8

C.

Line 3

D.

Line 5

Question 64

You want to write a query that prompts for two column names and the WHERE condition each time It is executed in a session but only prompts for the table name the first time it is executed. The variables used in your

query are never undefined in your session . Which query can be used?

Options:

A.

SELECT &col1, &col2

FROM &&table

WHERE &condition;

B.

SELECT &col1, &col2

FROM “&table”

WHERE &condition;

C.

SELECT &&col1,&&col2

FROM &table

WHERE &&condition= &&cond;

D.

SELECT'&co11','&&co12'

FROM &table

WHERE'&&condition' ='&cond';

E.

SELECT&&col1, &&col2

FROM &table

WHERE &&condition;

Question 65

Examine the description of the ENPLYEES table:

Question # 65

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 66

Examine this partial query:

SELECT ch.channel_type, t.month, co.country_code, SUM(s.amount_sold) SALES

FROM sales s, times t, channels ch, countries co

WHERE s.time_ id = t.time id

AND s.country_ id = co. country id

AND s. channel id = ch.channel id

AND ch.channel type IN ('Direct Sales', 'Internet')

AND t.month IN ('2000-09', '2000-10')

AND co.country code IN ('GB', 'US')

Examine this output:

Question # 66

Which GROUP BY clause must be added so the query returns the results shown?

Options:

A.

GROUP BY ch.channel_type, t.month, co.country code;

B.

GROUP BY ch.channel_type,ROLLUP (t month, co. country_ code) ;

C.

GROUP BY CUBE (ch. channel_ type, t .month, co. country code);

D.

GROUP BYch. channel_ type, t.month,ROLIUP (co. country_ code) ;

Question 67

The EMPLOYEES table contains columns EMP_ID of data type NUMBER and HIRE_DATE of data type DATE

You want to display the date of the first Monday after the completion of six months since hiring.

The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first day of the week Which query can be used?

Options:

A.

SELECT emp_id,NEXT_DAY(ADD_MONTHS(hite_date,6),'MONDAY') FROM employees;

B.

SELECT emp_id,ADD_MONTHS(hire_date,6), NEXT_DAY('MONDAY') FROM employees;

C.

SELECT emp_id,NEXT_DAY(MONTHS_BETWEEN(hire_date,SYSDATE),6) FROM employees;

D.

SELECT emp_id,NEXT_DAY(ADD_MONTHS(hire_date,6),1) FROM employees;

Question 68

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

Options:

A.

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

B.

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

C.

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

D.

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

E.

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

Question 69

Examine the description of the employees table:

Question # 69

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?

A)

Question # 69

B)

Question # 69

C)

Question # 69

D)

Question # 69

Options:

A.

Option A

B.

Option B

C.

Option C

D.

Option D

Question 70

Which two statements are true about a self join?

Options:

A.

The join key column must have an index.

B.

It can be a left outer join.

C.

It must be a full outer join.

D.

It can be an inner join.

E.

It must be an equijoin.

Question 71

Examine this data in the EMPLOYERS table:

Question # 71

Which statement will execute successfully?

Options:

A.

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

B.

SELECT dept_id, LENGTH (last_name), SUM (salary) FROM employees GROUP BY dept_id

C.

SELECT dept_id, STDDEV (last_name), SUM (salary) FROM employees GROUP BY dept_id

D.

SELECT dept_id, INSTR (last_name,'A'), SUM (salary) FROM employees GROUP BY dept_id

Question 72

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:

A.

SELECT product id, quantity on hand, supplier id

FROM product information

NATURAL JOIN inventories AND quantity .on hand < 5;

B.

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;

C.

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;

D.

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;

Question 73

Examine the description or the CUSTOMERS table:

Question # 73

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_AMONT FROM customers

WHERE cust_income_level <> NULL AND due_amount <> NULL;

C.

SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMONT FROM customers

WHERE cust_income_level IS NOT NULL AND cust_credit_limit IS NOT NULL;

D.

SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMONT FROM customers

WHERE cust_income_level IS NOT NULL AND due_amount IS NOT NULL;

E.

SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMONT FROM customers

WHERE cust_income_level != NULL AND due_amount != NULL;

Question 74

Examine the description of the PRODUCT_STATUS table:

Question # 74

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

Which two queries will execute successfully?

Options:

A.

SELECT prod_id "CURRENT AVAILABILITY" || q'('s not available)' FROM product_status WHERE status = ’OUT OF STOCK';

B.

SELECT prod_id || q's not available'' FROM product_status WHERE status=’OUT OF STOCK’;

C.

SELECT prod_id || q'('s not available)’ "CURRENT AVAILABILITY" FROM product_status WHERE status = 'OUT OF STOCK';

D.

SELECT prod_id || q'('s not available)' FROM product_status WHERE status = ’OUT OF STOCK’;

E.

SELECT prod_id || q’(’s not available)' 'CURRENT AVAILABILITY' FROM product_status WHERE status = 'OUT OF STOCK';

F.

SELECT prod_id || q"'s not available" FROM product_status WHERE status = 'OUT OF STOCK';

Question 75

You issued this command: DROP TABLE hr. employees;

Which three statements are true?

Options:

A.

ALL constraints defined on HR, EMPLOYEES are dropped.

B.

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

C.

Synonyms for HR EMPLOYEES are dropped.

D.

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

E.

All indexes defined on HR, EMPLOYEES are dropped.

F.

Views referencing HR, EMPLOYEES are dropped.

Question 76

Choose two

Examine the description of the PRODUCT DETALS table:

Question # 76

Options:

A.

PRODUCT_ID can be assigned the PEIMARY KEY constraint.

B.

EXPIRY_DATE cannot be used in arithmetic expressions.

C.

EXPIRY_DATE contains the SYSDATE by default if no date is assigned to it

D.

PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it

E.

PRODUCT_PRICE contains the value zero by default if no value is assigned to it.

F.

PRODUCT_NAME cannot contain duplicate values.

Question 77

Which three statements are true?

Options:

A.

A customer can exist in many countries.

B.

The statement will fail if a row already exists in the SALES table for product 23.

C.

The statement will fail because subquery may not be I contained in a values clause.

D.

The SALES table has five foreign keys.

E.

The statement will execute successfully and a new row will be inserted into the SALES table.

F.

A product can have a different unit price at different times.

Question 78

Which three statements are true about single row functions?

Options:

A.

They can be used only in the where clause of a select statement.

B.

They can accept only one argument.

C.

They return a single result row per table.

D.

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

E.

They can be nested to any level.

F.

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

Question 79

Which two are true about granting privilege on objects?

Options:

A.

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

B.

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

C.

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

D.

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

E.

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

Question 80

Which two are true about the WITH GRANT OPTION clause?

Options:

A.

The grantee can grant the object privilege to any user in the database, with of without including this option.

B.

The grantee must have the GRANT ANY OBJECT PRIVILEGE system prvilege to use this option.

C.

It can be used when granting privileges to roles.

D.

It can be used for system and object privileges.

E.

It cannot be used to pass on privileges to PUBLIC by the grantee.

F.

It can be used to pass on privileges to other users by the grantee.

Question 81

In the PROMOTIONS table, the PROMO_BEGTN_DATE column is of data type DATE and the default date format is DD-MON-RR.

Which two statements are true about expressions using PROMO_BEGIN_DATE contained in a query?

Options:

A.

TO_NUMBER(PROMO_BEGIN_DATE)-5 will return number

B.

TO_DATE(PROMO_BEGIN_DATE * 5) will return a date

C.

PROMO_BEGIN_DATE-SYSDATE will return a number.

D.

PROMO_BEGIN_DATE-5 will return a date.

E.

PROMO_BEGIN_DATE-SYSDATE will return an error.

Question 82

Which two are true about queries using set operators such as UNION?

Options:

A.

An expression in the first SELECT list must have a column alias for the expression

B.

CHAR columns of different lengths used with a set operator retum a vAacsua mhtoe e equals the longest CHAR value.

C.

Queries using set operators do not perform implicit conversion across data type groups (e.g. character, numeric)

D.

In a query containing multiple set operators INTERSECT always takes precedence over UNION and UNION ALL

E.

All set operators are valid on columns all data types.

Question 83

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

Options:

A.

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

B.

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

C.

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

D.

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

Question 84

Examine the description of the PRODCTS table which contains data:

Question # 84

Which two are true?

Options:

A.

The PROD ID column can be renamed.

B.

The PROD_ ID column data type can be changed to VARCHAR2 (2).

C.

The EXPIRY DATE column data type can be changed to TIME STAMP.

D.

The EXPIRY DATE column cannot be dropped.

E.

The PROD NAME column cannot have a DEFAULT clause added to it.

Question 85

Examine the description products table:

Question # 85

Examine the description of the new_projects table;

Question # 85

Which two queries execute successfully?

A)

Question # 85

B)

Question # 85

C)

Question # 85

D)

Question # 85

E)

Question # 85

Options:

A.

Option A

B.

Option B

C.

Option C

D.

Option D

E.

Option E

Question 86

Examine this SQL statement:

SELECT cust_id, cust_last_name "Last Name

FROM customers

WHERE countryid=10

UNION

SELECT custid CUSTNO, cust_last_name

FROM customers

WHERE countryid=30

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

Options:

A.

ORDER BY“CUST NO"

B.

ORDER BY 2, cust_id

C.

ORDERBY2, 1

D.

ORDER BY "Last Name"

E.

ORDER BY CUSTNO

Question 87

Examine this partial statement:

SELECT ename, sal,comm FROM emp

Now examine this output:

Question # 87

WHICH ORDER BY clause will generate the displayed output?

Options:

A.

ORDER BY NVL(enam,0) DESC, ename

B.

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

C.

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

D.

ORDER BY comm DESC NULLS LAST, ename

Question 88

Which three are true about dropping columns from a table?

Options:

A.

A column can be removed only if it contains no data.

B.

A column drop is implicitly committed

C.

A column that is referenced by another column in any other table cannot be dropped.

D.

A column must be set as unused before it is dropped from a table.

E.

A primary key column cannot be dropped.

F.

Multiple columns can be dropped simultaneously using the ALTER TABLE command.

Question 89

Examine the description of the EMPLOYEES table:

Question # 89

Which statement will fail?

Options:

A.

SELECT department_id, COUNT (*)

FROM employees

HAVING department_ id <> 90 AND COUNT(*) >= 3

GROUP BY department_id;

B.

SELECT department_id, COUNT (*)

FROM employees

WHERE department_ id <> 90 AND COUNT(*) >= 3

GROUP BY department_id;

C.

SELECT department_id, COUNT(*)

FROM employees

WHERE department_id <> 90 HAVING COUNT(*) >= 3

GROUP BY department_id;

D.

SELECT department_id, COUNT(*)

FROM employees

WHERE department_id <> 90 GROUP BY department_id

HAVING COUNT(*) >= 3;

Question 90

Examine this partial command:

Question # 90

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 91

Which two statements are true about Oracle synonyms?

Options:

A.

A synonym can have a synonym.

B.

A synonym has an object number.

C.

Any user can create a public synonym.

D.

All private synonym names must be unique in the database.

E.

A synonym can be created on an object in a package.

Question 92

Which two tasks require subqueries?

Options:

A.

Display the total number of products supplied by supplier 102 which have a product status of obsolete.

B.

Display suppliers whose PROD_LIST_PRICE is less than 1000.

C.

Display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE.

D.

Display the minimum PROD_LIST_PRICE for each product status.

E.

Display products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products, and whose status is orderable.

Question 93

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

Options:

A.

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

B.

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

C.

A system privilege is required.

D.

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

E.

A primary key constraint is manadatory.

Question 94

The ORDERS table has a column ORDER_DATE of date type DATE The default display format for a date is DD-MON-RR

Which two WHERE conditions demonstrate the correct usage of conversion functions?

Options:

A.

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

B.

WHERE TO_CHAR(order_date,'MON DD YYYY') ='JAN 20 2019';

C.

WHERE order_date> TO_DATE('JUL 10 2018','MON DD YYYY');

D.

WHERE order_date IN (TO_DATE ('Oct 21 2018','MON DD YYYY'), TO_CHAR('Nov 21 2018','MON DD YYYY'));

E.

WHERE order_date> TO_DATE(ADD_MONTHS(SYSDATE,6),'MON DD YYYY');

Question 95

Which two statements are true regarding non equijoins?

Options:

A.

The ON clause can be used.

B.

The USING clause can be used.

C.

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

D.

Table aliases must be used.

E.

The Oracle join syntax can be used.

Question 96

The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2.

The table has two rows whose COST_LAST_MANE values are Anderson and Ausson.

Which query produces output for CUST_LAST_SAME containing Oder for the first row and Aus for the second?

Options:

A.

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

B.

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

C.

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

D.

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

Question 97

Examine the data in the EMPLOYEES table:

Question # 97

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

Options:

A.

SELECT last name,

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

annual comp

FROM employees

;

B.

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

FROM employees ;

C.

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

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

FROM employees;

D.

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

annual_ comp

FROM employees;

Page: 1 / 32
Total 324 questions