A.  

28

B.  

0

C.  

14

D.  

42

A.  

It must be an inner join.

B.  

It can be an outer join.

C.  

The ON clause must be used.

D.  

It must be an equijoin.

E.  

The query must use two different aliases for the table.

F.  

The ON clause can be used.

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 COALESCE(100, 'A' ) FROM DUAL；

A.  

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

B.  

SELECT shape FROM bricks JOIN boxes ON weight > min_weight;

C.  

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

D.  

SELECT shape FROM bricks JOIN boxes ON weight < max_weight;

E.  

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

A.  

an attribute

B.  

a cardinality

C.  

label

D.  

an optionality

E.  

a unique identifier

F.  

an entity

A.  

It drops any triggers defined on the table.

B.  

It retains the indexes defined on the table.

C.  

It retains the integrity constraints defined on the table.

D.  

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

E.  

It always retains the space used by the removed rows.

F.  

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

A.  

It retains the indexes defined on the table.

B.  

It drops any triggers defined on the table.

C.  

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

D.  

It retains the integrity constraints defined on the table.

E.  

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

F.  

It always retains the space used by the removed rows

A.  

DELETE FROM emp80

WHERE department_ id = 90;

B.  

SELECT

FROM emp80

WHERE department_ id = 90;

C.  

SELECT

FROM emp80

WHERE department. id = 80;

D.  

UPDATE emp80

SET department. 1d =80;

WHERE department_ id =90;

A.  

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

B.  

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

C.  

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

D.  

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

E.  

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

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

A.  

1 and 4 give the same result.

B.  

2 returns the value 20.

C.  

2 and 3 give the same result.

D.  

3 returns an error.

E.  

1 and 4 give different results.

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;

A.  

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

B.  

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

C.  

A transaction interrupted by a system failure is automatically rolled back.

D.  

If the ROLLBACK statement is used without TO SAVEPOINT, then all savepoints in the transaction are deleted .

E.  

Data consistency is not guaranteed after a rollback.

A.  

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

B.  

SELECT LOWER (SUBSTR(‘Hello World’, 2)) FROM DUAL;

C.  

SELECT LOWER(TRIM(‘H’ FROM ‘Hello World’)) FROM DUAL;

D.  

SELECT SUBSTR(‘Hello world’, 2) FROM DUAL;

E.  

SELECT INITCAP(TRIM(‘H’ FROM ‘Hello World’)) FROM DUAL;

A.  

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

B.  

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

C.  

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

D.  

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

E.  

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

A.  

SELECT emp. *

FROM employees emp

WHERE manager_ id NOT IN (

SELECT mgr.employee_ id

FROM employees mgr

WHERE emp. department_ id < > mgr.department_ id

);

B.  

SELECT emp.*

FROM employees emp

WHERE NOT EXISTS (

SELECT NULL

FROM employees mgr

WHERE emp.manager id = mgr.employee_ id

AND emp.department_id<>mgr.department_id

);

C.  

SELECT emp.*

FROM employees emp

LEFT JOIN employees mgr

ON emp.manager_ id = mgr.employee_ id

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

D.  

SELECT emp. *

FROM employees emp

RIGHT JOIN employees mgr

ON emp.manager_ id = mgr. employee id

AND emp. department id <> mgr.department_ id

WHERE emp. employee_ id IS NOT NULL;

E.  

SELECT emp. *

FROM employees emp

JOIN employees mgr

ON emp. manager_ id = mgr. employee_ id

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

A.  

When the session logs out is successfully

B.  

When a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT

C.  

When a CREATE INDEX statement is executed successfully in same session

D.  

When a COMMIT statement is issued by the same user from another session in the same database instance

E.  

When a CREATE TABLE AS SELECT statement is executed unsuccessfully in the same session

F.  

When a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user, then issues a COMMIT

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

A.  

UPDATE can be granted only on tables and views.

B.  

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

C.  

REFERENCES can be granted only on tables and views.

D.  

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

E.  

SELECT can be granted only on tables and views.

F.  

ALTER can be granted only on tables and sequences.

A.  

SELECT last_ name, salary

FROM employees

FETCH FIRST 3 ROWS ONLY

ORDER BY salary;

B.  

SELECT last name, salary

FROM employees

ORDER BY salary

FETCE FIRST 3 RONS ONLY;

C.  

SELECT last_ name, salary

FBOM employees

WEERE

ORDER BY SELECT

ROINUM <= 3

salary FROM

employees);

D.  

SELECT last_ name, salary

FROM

(SELECT” FROM employees ORDER BY salary)

WHERE ROWNUM <=3

E.  

SELECT last_ name, salary

FROM employees

WHERE ROWNUM <=3

ORDER BY salary

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

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

A.  

You must have SELECT privileges on a table to view it in ALL TABLES.

B.  

You must have SELECT privileges on a table to view it in DBA TABLES.

C.  

USER TABLES displays all tables owned by the current user.

D.  

All TABLES displays all tables owned by the current user.

E.  

You must have SELECT privileges on a table to view it in USER TABLES.

F.  

All users can query DBA TABLES successfully.

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

A.  

using single-row functions in the WHERE clause requires a subquery

B.  

using single-row functions in the HAVING clause requires a subquery

C.  

using aggregate functions in the WHERE clause requires a subquery

D.  

using aggregate functions in the HAVING clause requires a subquery

A.  

The date is in the time zone of DBTIMEZONE.

B.  

The value varies depending on the setting of SESSIONTIMEZONE.

C.  

It returns the same date as CURRENT_DATE.

D.  

The time is in the time zone of DBTIMEZONE.

E.  

It returns a value of data type TIMESTAMP

F.  

It always returns the same value as SYSTIMESTAMP

A.  

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

B.  

A TRUNCATE command issued in a session causes all rows In a GLOBAL TEMPORARY TABLE for the issuing session to be deleted.

C.  

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

D.  

A GLOBAL TEMPORARY TABLE's definition is available to multiple sessions.

E.  

Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted.

F.  

GLOBAL TEMPORARY TABLE space allocation occurs at session start.

A.  

64

B.  

6

C.  

3

D.  

12

A.  

quantity

B.  

quantity, unit_price

C.  

total_paid

D.  

product_id

E.  

quantity * unit_price

A.  

It displays values for variables created by the DEFINE command.

B.  

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

C.  

It can be used only in SQL*plus.

D.  

It displays values for variables prefixed with &&.

E.  

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

A.  

All remaining employee names will appear in an ascending order

B.  

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

C.  

All remaining employee names will appear in ascending order

D.  

All remaining employee names will appear in descending order

E.  

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

F.  

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

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.

A.  

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

B.  

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

C.  

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

D.  

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

E.  

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

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');

A.  

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

B.  

Andrew will be unable to see the changes you have made

C.  

Andrew will be able to see the changes you habe made

D.  

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

E.  

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

A.  

SELECT.

FROM bricks b

RIGHT JOIN colors c

ON b. color _rgb_ hex_ value = c. rgb hex_ value;

B.  

SELECT

EROM colors C

LEFT JOIN bricks 上

USING (rgb _ hex_ value) ;

C.  

SELECT

FROM bricks b

FULL JOIN colors C

ON b. color rgb _ hex_ value = c. rgb _hex_ value;

D.  

SELECT *

EROM bricks | b

JOIN colors C

ON b. color_ rgb_ hex_ value =c. rgb _hex value;

E.  

SELECT

EROM colors C

LEET JOIN bricks b

ON b. color_ rgb_ hex value = c. rgb. hex.

value

WHERE b. brick_ id > 0;

A.  

You cannot correlate them with a table in the parent statement

B.  

You can use them as a default value for a column.

C.  

.You must enclose them in parentheses.

D.  

They can return at most one row.

E.  

They can return two columns.

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;

A.  

It can be used from SQL Developer.

B.  

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

C.  

It can be used only from SQL*Plus.

D.  

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

E.  

It displays all constraints that are defined for each column.

F.  

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

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;

A.  

SELECT customer_id AS "CUSTOMER-ID", transaction_date AS DATE, amount+100 "DUES" from transactions;

B.  

SELECT customer_id AS 'CUSTOMER-ID',transaction_date AS DATE, amount+100 'DUES' from transactions;

C.  

SELECT customer_id CUSTID, transaction_date TRANS_DATE，amount+100 DUES FROM transactions;

D.  

SELECT customer_id AS "CUSTOMER-ID", transaction_date AS "DATE", amount+100 DUES FROM transactions;

E.  

SELECT customer id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount+100 "DUES AMOUNT" FROM transactions;

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.

A.  

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

B.  

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

C.  

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

D.  

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

E.  

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

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.

A.  

AIl conditions evaluated using DECODE can also be evaluated using CASE.

B.  

All conditions evaluated using CASE can also be evaluated using DECODE.

C.  

CASE is a function and DECODE is not.

D.  

DECODE is a function and CASE is not.

E.  

Neither CASE nor DECODE is a function.

F.  

Both CASE and DECODE are functions.

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.

A.  

It will return rows from both select statements after eliminating duplicate rows.

B.  

It will return rows common to both select statements.

C.  

It will return rows both select statements including duplicate rows.

D.  

It will return rows that are not common to both select statements.

A.  

An uncommitted transaction commits automatically if the user exits SQL*Plus

B.  

Data Manipulation Language (DML) statements always start a new transaction.

C.  

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

D.  

A Data Definition Language (DDL) statement does a commit automatically only for the data dictionary updates caused by the DDL

E.  

A session can always see uncommitted updates made by itself.

F.  

If a session has an uncommitted transaction, then a DDL statement issue a COMMIT before starting a new transaction.

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.

A.  

They can contain HAVING clauses.

B.  

Two or more values are always returned from the subquery.

C.  

They cannot contain subquery.

D.  

They can return multiple columns.

E.  

They can contain GROUP BY clauses.

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;

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.

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.

A.  

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

B.  

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

C.  

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

D.  

It executes successfully with an implicit data type conversion

A.  

It executes successfully but does not return any result.

B.  

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

C.  

It generates an error.

D.  

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

A.  

DELETE FROM orders WHERE order_total<1000;

B.  

DELETE * FROM orders WHERE order_total<1000;

C.  

DELETE orders WHERE order_total<1000;

D.  

DELETE FROM orders;

E.  

DELETE order_id FROM orders WHERE order_total<1000;

A.  

CONCAT: can be used to combine any number of values

B.  

MOD: returns the quotient of a division operation

C.  

CEIL: can be used for positive and negative numbers

D.  

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

E.  

TRUNC: can be used with NUMBER and DATE values

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' AND member_id='A101' OR member_id ='A102'));

D.  

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

E.  

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

A.  

Line 7

B.  

Line 8

C.  

Line 3

D.  

Line 5

A.  

SELECT COALESCR('DATE', SYSDATE) FROM DUAL;

B.  

SELECT NVL('DATE',SYSDATE) FROM DUAL;

C.  

SELECT COALESCE(O,SYSDATE) TRCH DUAL;

D.  

SELECT NVL('DATE',200) FROM (SELECT NULL AS “DATE” FROM DUAL);

E.  

SELECT COALESCE('DATE',SYSDATE) FROM (SELECT NULL AS “DATE” FROM DUAL) ;

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.

A.  

ORDER BY

B.  

WHERE

C.  

GROUP BY

D.  

SELECT

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.

A.  

Use the DEFINE command before executing the query.

B.  

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

C.  

Replace'&1' with'&&1' in the query.

D.  

Execute the SET VERIFY OFF command before executing the query.

E.  

Use the UNDEFINE command before executing the query.

F.  

Execute the SET VERIFY ON command before executing the query.

A.  

An ORDER BY clause always sorts NULL values last.

B.  

An ORDER BY clause can perform a binary sort

C.  

An ORDER BY clause can perform a linguistic sort

D.  

By default an ORDERBY clause sorts rows in ascending order

E.  

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

A.  

CREATE TABLE bookings (

bk_id NUMBER(4) NOT NULL PRIMARY KEY,

start_date DATE NOT NULL,

end_date DATE DEFAULT SYSDATE);

B.  

CREATE TABLE bookings (

bk_id NUMBER(4) NOT NULL DEFAULT book_seq.CURRVAL,

start_date DATE NOT NULL,

end_date DATE DEFAULT SYSDATE);

C.  

CREATE TABLE bookings (

bk_id NUMBER(4) DEFAULT book_seq.CURRVAL,

start_date DATE DEFAULT SYSDATE,

end_date DATE DEFAULT start date);

D.  

CREATE TABLE bookings ( bk_id NUMBER(4),

start_date DATE DEFAULT SYSDATE,

end_date DATE DEFAULT (end_date >= start_date));

E.  

CREATE TABLE bookings (

bk_id NUMBER(4) DEFAULT book_seq.NEXTVAL PRIMARY KEY,

start_date DATE DEFAULT SYSDATE,

end_date DATE DEFAULT SYSDATE NOT NULL);

A.  

They can be created only by a user with the DBA role,but can be accessed by all users who can create a session.

B.  

Backup and recovery operations are available for these tables.

C.  

If the ON COMMIT clause is session-specific,the table is dropped when the session is terminated.

D.  

Their data is always stored in the default temporary tablespace of the user who created them.

E.  

Indexes can be created on them.

F.  

If the ON COMMIT clause Is transaction-specific, all rows in the table are deleted alter each COMMIT or ROLLBACK.

A.  

It must be an inner join.

B.  

It must be an equijoin.

C.  

The query must use two different aliases for the table.

D.  

The on clause can be used.

E.  

The on clause must be used.

F.  

It can be an outer join.

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;

A.  

CREATE SEQUENCE emp_seq START WITH 1 INCRENENT BY 1 NOCACHE;

B.  

CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CYCLE;

C.  

CREATE SEQUENCE emp_seq NOCACHE;

D.  

CREATE SEQUENCE emp_seq START WITH 1 CACHE;

E.  

CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CACHE;

F.  

CREATE SEQUENCE emp_seq;

A.  

SELECT * FROM customers WHERE TO_ CHAR (customer_ id) = '0001';

B.  

SELECT * FROM customers WHERE customer id = '0001';

C.  

SELECT * FROM customers WHERE customer_ id = 0001;

D.  

SELECT FROM customers WHERE insert date = '01-JAN-19';

E.  

SELECT. FROM customers WHERE insert_ date = DATE *2019-01-01';

F.  

SELECT. FRON customers WE TO DATE (Insert _ date) = DATE ‘2019-01-01’;

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

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

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.

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;

A.  

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

B.  

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

C.  

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

D.  

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

E.  

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

F.  

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

A.  

MERGE INTO employees e

USING new employees ne

WHERE e.employee_id = ne.employee_ id

WHEN MATCHED THEN

UPDATE SET e.name = ne.name, e.job_id = ne.job_id,e.salary =ne. salary

WHEN NOT MATCHED THEN

INSERT VALUES (ne. employee_id,ne.name, ne.job_id,ne.salary) ;

B.  

MERGE INTO employees e

USING new_employees n

ON (e.employee_id = ne.employee_id)

WHEN MATCHED THEN

UPDATE SET e.name = ne.name, e.job id = ne.job_id,e.salary =ne. salary

WHEN NOT MATCHED THEN

INSERT VALUES (ne. employee_id,ne.name,ne.job_id,ne.salary);

C.  

MERGE INTO employees e

USING new employees ne

ON (e.employee_id = ne.employee_id)

WHEN FOUND THEN

UPDATE SET e.name =ne.name, e.job_id=ne.job_id, e.salary =ne.salary

WHEN NOT FOUND THEN

INSERT VALUES (ne.employee_id,ne.name,ne.job_id,ne.salary) ;

D.  

MERGE INTO employees e

USING new_employees n

WHERE e.employee_id = ne.employee_id

WHEN FOUND THEN

UPDATE SET e.name=ne.name,e.job_id =ne.job_id, e.salary=ne.salary

WHEN NOT FOUND THEN

INSERT VALUES (ne.employee_ id,ne.name,ne.job id,ne.salary) ;

A.  

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

B.  

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

C.  

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

D.  

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

E.  

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

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;

A.  

The primary key constraint will be enabled and DEFERRED.

B.  

The primary key constraint will be enabled and IMMEDIATE.

C.  

The foreign key constraint will be disabled.

D.  

The foreign key constraint will be enabled and DEFERRE

D.  

E.  

The foreign key constraint will be enabled and IMMEDIAT

E.  

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

A.  

0 rows

B.  

an error

C.  

8 rows

D.  

6 rows

E.  

1 row

F.  

3 rows

A.  

ALTER

B.  

REFERENCES

C.  

UPDATE

D.  

SELECT

E.  

INDEX

F.  

INSERT

G.  

DELETE

A.  

A scalar subquery expression that returns zero rows evaluates to zoro

B.  

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

C.  

They can be nested.

D.  

A scalar subquery expression that returns zero rows evaluates to null.

E.  

They cannot be used in group by clauses.

F.  

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

A.  

SECECT last_namo, (menthy_salary + monthly_commission_pct) * 12 AS annual_comp

FROM employees;

B.  

SELCECT last_namo, (monthly_salary * 12) + (monthly_commission_pct * 12) AS annual_comp

FROM employees

C.  

SELCECT last_namo, (monthly_salary * 12) + (menthy_salary * 12 * NVL

(monthly_commission_pct, 0)) AS annual_comp FROM employees

D.  

SELCECT last_namo, (monthly_salary * 12) + (menthy_salary * 12 * monthly_commission_pct)

AS annual_comp FROM employees

A.  

CREATE GLOBAL SYNONYM emp FOR hcm.employee_records;

B.  

CREATE SYNONYM emp FOR hcm.employee_records;

C.  

CREATE SYNONYM PUBLI

C.  

D.  

CREATE SYNONYM SYS.emp FOR hcm.employee_records;

E.  

CREATE PUBLIC SYNONYM emp FOR hcm. employee_records;

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

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.

A.  

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

FROM employees

WHERE department_ id = 90

ORDER BY tenure ;

B.  

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

FROM employees

WHERE department_ id = 90

ORDER BY tenure ;

C.  

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

FROM employees

WHERE department_ id = 90

ORDER BY tenure DESC;

D.  

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

FROM employees

WHERE department_id = 90

ORDER BY tenure DESC;

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

A.  

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

B.  

A SEQUENCE can have a synonym.

C.  

A synonym can be available to all users .

D.  

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

E.  

Any user can drop a PUBLIC synonym.

A.  

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

B.  

They can be nested to any level.

C.  

They return a single result row per table.

D.  

They can accept only one argument.

E.  

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

F.  

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

A.  

It executes successfully and the row is inserted with a null PLBLISHER_ID.

B.  

It executes successfully only if NULL is explicitly specified in the INSERT statement.

C.  

It executes successfully only NULL PUBLISHER_ID column name is added to the columns list in the INSERT statement.

D.  

It executes successfully onlyif NULL PUBLISHER ID column name is added to the columns list and NULL is explicitly specified In the INSERT statement.

A.  

CURRENT DATE

B.  

CURRENT_ TIMESTAMP

C.  

SYSDATE

D.  

LOCALTIMESTAMP

A.  

They must be placed on the left side of the comparison operator or condition.

B.  

They must return a row to prevent errors in the SQL statement.

C.  

A SQL statement may have multiple single row subquery blocks.

D.  

They can be used in the HAVING clause.

E.  

They must be placed on the right side of the comparison operator or condition.

F.  

They can be used in the clause.

A.  

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

B.  

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

C.  

NULLS are not included in the sort operation.

D.  

Column aliases can be used In the ORDER BY cause.

E.  

Ina character sort, the values are case-sensitive.