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

64

6

3

12

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

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

SELECT * FROM employees WHERE NVL2(salary)+commission,salary+commission,

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

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

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

A Relationship can be mandatory for both entities

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

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

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

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

ORDERBY 2, 1

ORDER BY "CUST_NO"

ORDER BY 2,cust_id

ORDER BY CUST_NO

ORDER BY "Last Name"

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

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

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

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

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

Create a directory object for an external table.

Read data from an external table and load it into a table in the database.

Query data from an external table.

Create a directory object for a flat file.

Execute DML statements on an external table.

Read data from a table in the database and insert it into an external table.

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

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

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

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

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

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

providing graphical capabilities

providing variable definition capabilities.

providing database transaction control

processing sets of data

providing update capabilities for data in external files

SELECT SUBSTR(join date, 1, 2) - 10 FROM employees;

SELECT join_ date + '20' EROM employees;

SELECT join_ date丨丨‘’丨丨 salary FROM employees;

SELECT join _ date FROM employees WHERE join date > *10-02-2018';

SELECT salary + '120.50' FROM employees;

SELECT.

FROM bricks b

RIGHT JOIN colors c

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

SELECT

EROM colors C

LEFT JOIN bricks 上

USING (rgb _ hex_ value) ;

SELECT

FROM bricks b

FULL JOIN colors C

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

SELECT *

EROM bricks | b

JOIN colors C

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

SELECT

EROM colors C

LEET JOIN bricks b

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

value

WHERE b. brick_ id > 0;

intersect ignores nulls.

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

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

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

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

SELECT product id, quantity on hand, supplier id

FROM product information

NATURAL JOIN inventories AND quantity .on hand < 5;

SELECT i. product id, i. quantity .on hand, pi. supplier_id

FROM product_information pi JOIN inventories i

ON (pi. product. id=i. product id) AND quantity on hand < 5;

SELECT i. product_id, i. quantity_on hand, pi. supplier id

FROM product information pi JOIN inventories i USING (product id) AND quantity .on hand < 5;

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;

It executes successfully but does not return any result.

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

It generates an error.

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

ALTER

REFERENCES

UPDATE

SELECT

INDEX

INSERT

DELETE

SELECT salary + 120.50 FROM employees;

SELECT SUBSTR(join date, 1, 2)- 10 FROM employees;

SELECT join date 11.’11 salary FROM employees;

SELECT join date FROM employees where join date > *10-02-2018*;

SELECT join date + 20 FROM employees;

delete statements can remove multiple rows based on multiple conditions.

insert statements can insert nulls explicitly into a column.

insert into. . .select. . .from statements automatically commit.

DML statements require a primary key be defined on a table.

update statements can have different subqueries to specify the values for each updated column.

A table can have multiple primary keys.

A table can have multiple foreign keys.

A NUMBER column without data has a zero value.

A column definition can specify multiple data types.

A VARCHAR2 column without data has a NULL value.

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

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

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

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

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

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

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

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

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

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

SELECT department_id, COUNT (*)

FROM employees

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

GROUP BY department_id;

SELECT department_id, COUNT (*)

FROM employees

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

GROUP BY department_id;

SELECT department_id, COUNT(*)

FROM employees

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

GROUP BY department_id;

SELECT department_id, COUNT(*)

FROM employees

WHERE department_id <> 90 GROUP BY department_id

HAVING COUNT(*) >= 3;

SELECT * FROM TABLE (123) ;

SELECT * FROM '123';

SELECT * FROM "123";

SELECT * FROM V'123V';

You cannot correlate them with a table in the parent statement

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

.You must enclose them in parentheses.

They can return at most one row.

They can return two columns.

It retains the indexes defined on the table.

It drops any triggers defined on the table.

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

It retains the integrity constraints defined on the table.

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

It always retains the space used by the removed rows

A query can display data from the DEPARTMENT_ID column.

The storage space occupied by the DEPARTMENT_ID column is released only after a COMMIT is issued.

The DEPARTMENT_ID column is set to null for all tows in the table

A new column with the name DEPARTMENT_ID can be added to the EMPLOYEES table.

No updates can be made to the data in the DEPARTMENT_ID column.

The DEPARTMENT_ID column can be recovered from the recycle bin

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.

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

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

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

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

The subquery is executed for every row in the EMPLOYEES table.

The subquery is not a correlated subquery.

The subquery is executed before the DELETE statement is executed.

All existing rows in the EMPLOYEE table are deleted.

The DELETE statement executes successfully even if the subquery selects multiple rows.

UPDATE can be granted only on tables and views.

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

REFERENCES can be granted only on tables and views.

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

SELECT can be granted only on tables and views.

ALTER can be granted only on tables and sequences.

NULLS are not ignored during duplicate checking.

Duplicates are eliminated automatically by the UNION ALL operator

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

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

The output is sorted by the UNION ALL operator.

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

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

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

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

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

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

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

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

SPLECT product_id, expiry_date * 2 FROM products;

SELEGT product_id, unit_price, unit_price + surcharge FROM products;

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

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

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

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

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

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

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

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

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

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

A subquery can be used in a WHERE clause.

A subquery can be used in a HAVING clause.

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

A subquery cannot be used in a FROM clause.

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

A subquery cannot be used in the select list.

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

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

One of the LONG columns must be changed to a VARCHAR2 or CLOB.

The NOT NULL constraint on ENAME must be defined at the column level instead of the table level.

The primary key constraint on BMP_ID must have a name.

It must be an inner join.

It must be an equijoin.

The query must use two different aliases for the table.

The on clause can be used.

The on clause must be used.

It can be an outer join.

A scalar subquery expression that returns zero rows evaluates to zoro

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

They can be nested.

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

They cannot be used in group by clauses.

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

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

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

using aggregate functions in the WHERE clause requires a subquery

using aggregate functions in the HAVING clause requires a subquery

Statement 2 returns only one row of output.

Both the statement given the same output.

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

Statement 2 may return multiple rows of output.

Statement 1 returns only one row of output.

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

CURRENT_TIMESTAMP returns the same date as CURRENT_DATE.

CURRENT_DATE returns the current date and time as per the session time zone

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

CURRENT_TIMESTAMP returns the same date and time as SYSDATE with additional details of functional seconds.

SYSDATE can be queried only from the DUAL table.

DELETE FROM orders WHERE order_total<1000;

DELETE * FROM orders WHERE order_total<1000;

DELETE orders WHERE order_total<1000;

DELETE FROM orders;

DELETE order_id FROM orders WHERE order_total<1000;

SELECT TO _ CHAR (SYSDATE, ' FMDAY, DD MONTH, YYYY') FROM DUAL;

SELECT TO _ DATE (SYSDATE, ' FMDAY, DD MONTH, YYYY') FROM DUAL;

SELECT TO_ CHAR (SYSDATE, ' FMDD, DAY MONTH, YYYY') FROM DUAL;

SELECT TO_ CHAR (SYSDATE, ' FMDAY, DDTH MONTH, YYYY') FROM DUAL;

+ (addition) has a higher order of precedence than * (mliplpition)

NOT has a higher order of precedence than AND and OR in a condition.

AND and OR have the same order of precedence in a condition

Operators are evaluated before conditions.

|| has a higher order of precedence than +(addition)

A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,024.

A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,023.

A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as $1,0236.

A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as $1,023.

A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as #####

It can only be used for NUMBER data types.

COUNT (DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULLs in the INV_AMT column

COUNT(*) returns the number of rows in a table including duplicate rows and rows containing NULLs in any column.

A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.

COUNT(inv_amt) returns the number of rows in a table including rows with NULL in the INV_AMT column.

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

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.

The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause I do not match.

The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table

an INVISIBLE Index consumes no storage

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

The query optimizer never considers INVISIBLE Indexes when determining execution plans

You use AlTER INDEX to make an INVISIBLE Index VISIBLE

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

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

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

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

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

tablet join table2 ON (table1.column = table2.column) where table2.column LIKE 'A'

table1 join table2 on (table1.column between table2.column] and table2.column2)

table1 natural JOIN table2

table1 join table2 using (column1, column2)

table1 join table2 ON (table1.column >= table2.column)

Option A

Option B

Option C

Option D

A role is owned by the user who created it.

System privileges always set privileges for an entire database.

All roles are owned by the SYS schema.

A role can contain a combination of several privileges and roles.

A user has all object privileges for every object in their schema by default.

PUBLIC can be revoked from a user.

PUBLIC acts as a default role granted to every user in a database

A GLOBAL TEMPORARY TABLE cannot have PUBLIC SYNONYM.

A GLOBAL TEMPORARY TABLE can have multiple indexes

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

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

A GLOBAL TEMPORARY TABLE can have only one index.

A trigger can be created on a GLOBAL TEMPORARY TABLE

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

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

SELECT * FROM customers WHERE customer_ id = 0001;

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

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

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

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

=AMERICAN' ) FROM DUAL;

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

AMERICAN')

FROM DUAL;

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

FROM DUAL;

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

FROM DUAL

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

FROM DUAL

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

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

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

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

Data consistency is not guaranteed after a rollback.

CREATE SEQUENCE emp_seq START WITH 1 INCRENENT BY 1 NOCACHE;

CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CYCLE;

CREATE SEQUENCE emp_seq NOCACHE;

CREATE SEQUENCE emp_seq START WITH 1 CACHE;

CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CACHE;

CREATE SEQUENCE emp_seq;

SELECT *

FROM employees e

JOIN department d

WHERE e.department_id=d.department_id

AND d.department_id=90;

SELECT *

FROM employees e

JOIN departments d

ON e.department_id=d.department_id

WHERE d.department_id=90;

SELECT *

FROM employees e

JOIN departments d

ON e.department_id=d.department_id

AND d.department_id=90;

SELECT *

FROM employees e

JOIN departments d

ON d.departments_id=90

WHERE e.department_id=d.department_id;

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

FROM employees

WHERE department_ id = 90

ORDER BY tenure ;

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

FROM employees

WHERE department_ id = 90

ORDER BY tenure ;

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

FROM employees

WHERE department_ id = 90

ORDER BY tenure DESC;

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

FROM employees

WHERE department_id = 90

ORDER BY tenure DESC;

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

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

In a character sort, the values are case-sensitive.

Column aliases can be used in the ORDER BY clause.

NULLS are not included in the sort operation.

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

It can never be used with onatural join.

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

It can never be used with a full outer join.

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

LAST_DAY returns the date of the last day of the current ,month onlyu.

CEIL requires an argument which is a numeric data type.

ADD_MONTHS adds a number of calendar months to a date.

ADD_MONTHS works with a character string that can be implicitlyt converted to a DATE data type.

LAST_DAY return the date of the last day the previous month only.

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

LAST_DAY returns the date of the last day of the month for the date argument passed to the function.

It drops any triggers defined on the table.

It retains the indexes defined on the table.

It retains the integrity constraints defined on the table.

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

It always retains the space used by the removed rows.

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

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

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

USER_ TABLES displays all tables owned by the current user.

ALL TABLES displays all tables owned by the current user.

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

All users can query DBA TABLES successfully.

It will return rows common to both SELECT statements.

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

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

It will return rows from both SELECT statements including duplicate rows.

The names of employees earning the maximum salary will appear first in descending order.

The names of employees earning the maximum salary will appear first In ascending order.

All remaining employee names will appear in ascending order.

All remaining employee names will appear in an unspecified order.

All remaining employee names will appear in descending order.

The names of employees earning the maximum salary will appear first in an unspecified order.

SELECT FIRST_NAME LAST_NAME FROM EMPLOYEES;

SELECT FIRST_NAME,LAST_NAME FROM EMPLOYEES;

SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY

FROM EMPLOYEES

WHERE ANNUAL_SALARY > 100000

ORDER BY 12 * SALARY ;

SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY

FROM EMPLOYEES

WHERE 12 * SALARY > 100000

ORDER BY ANNUAL_SALARY;

SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY

FROM EMPLOYEES

WHERE 12 * SALARY > 100000

ORDER BY 12 * SALARY;

SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY

FROM EMPLOYEES

WHERE ANNUAL_SALARY > 100000

ORDER BY ANNUAL_SALARY;

MERGE INTO new_prices n

USING (SELECT * FROM products) p

WHEN MATCHED THEN

UPDATE SET n.price= p.cost* 01

WHEN NOT MATCHED THEN

INSERT(n.prod_id, n.price) VALUES(p.prod_id, cost*.01)

WHERE(p.cost<200);

MERGE INTO new_prices n

USING (SELECT * FROM products WHERE cost>150) p

ON (n.prod_id= p.prod_id)

WHEN MATCHED THEN

UPDATE SET n.price= p.cost*.01

DELETE WHERE (p.cost<200);

MERGE INTO new_prices n

USING products p

ON (p.prod_id =n.prod_id)

WHEN NOT MATCHED THEN

INSERT (n.prod _id, n.price) VALUES (p.prod_id, cost*.01)

WHERE (p.cost<200);

MERGE INTO new_prices n

USING (SELECT * FROM products WHERE cost>150) p

ON (n.prod_id= p.prod_id)

WHEN MATCHED THEN

DELETE WHERE (p.cost<200)

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

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

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

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

0 rows

an error

1 row

2 rows

PRODUCT_ PRICE can be used in an arithmetic expression even if it has no value stored in it.

PRODUCT_ ID can be assigned the PRIMARY KEY constraint.

EXPIRY_ DATE cannot be used in arithmetic expressions.

EXPIRY_ DATE contains the SYSDATE by default if no date is assigned to it.

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

PRODUCT_ NAME cannot contain duplicate values.

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

It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted.

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

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

It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted.

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

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

All remaining employee names will appear in an ascending order

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

All remaining employee names will appear in ascending order

All remaining employee names will appear in descending order

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

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

invoice_date> '01-02-2019': uses implicit conversion

qty_sold ='05549821 ': requires explicit conversion

CONCAT(qty_sold, invoice_date): requires explicit conversion

qty_sold BETWEEN '101' AND '110': uses implicit conversion

invoice_date = '15-march-2019': uses implicit conversion

the result of a delete can be undone by issuing a rollback

delete can use a where clause to determine which row(s) should be removed.

TRUNCATE can use a where clause to determine which row(s) should be removed.

truncate leavers any indexes on the table in an UNUSABLE STATE.

the result of a truncate can be undone by issuing a ROLLBACK.

Drop pseudo columns from a table.

Restrict all DML statements on a table.

Drop all columns simultaneously from a table.

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

Rename a table

Enable or disable constraints on a table.

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

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

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

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

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

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

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

USER TABLES displays all tables owned by the current user.

All TABLES displays all tables owned by the current user.

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

All users can query DBA TABLES successfully.

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

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

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

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

Roles are assigned to roles using the ALTER ROLE statement.

A single user can be assigned multiple roles.

Roles are assigned to users using the ALTER USER statement.

A single role can be assigned to multiple users.

Privileges are assigned to a role using the ALTER ROLE statement.

A role is a named group of related privileges that can only be assigned to a user.

Privileges are assigned to a role using the GRANT statement.

SELECT last_name, salary

FROM employees

WHERE ROWNUM<=3

SELECT last_name,salary

FROM employees

ORDER BY salary

FETCH FIRST 3 ROWS ONLY;

SELECT last_name,salary

FROM employees

WHERE ROWNUM<=3

ORDER BY (SELECT salary FROM employees);

SELECT last_name,salary

FROM (SELECT * FROM employees ORDER BY salary)

SELECT last_name,salary

FROM employees

FETCH FIRST 3 ROWS ONLY

ORDER BY salary;

WHERE MONTHS_ BETWEEN (SYSDATE, start_ date) <= 25

WHERE MONTHS_ BETWEEN (start_ date, SYSDATE) <= 25

WHERE TO_ NUMBER (start_ date - SYSDATE) <= 25

WHERE ADD_ MONTHS (start_ date, 25) <= SYSDATE

The outer join operator (+) can be used on both sides of the join condition in an outer join.

An outer join is used to retrieve only the rows that do not meet the join condition.

The IN operator cannot be used in a condition that Involves an outer join.

A condition representing an outer join cannot be linked to another condition using the or logical operator.

The outer join operator (+) is used next to the column of the table without the matching rows.

A Column can have only one CHECK Constraint.

A NOT NULL Constraint can be defined at the table level.

A UNIQUE constraint permits NULLS.

A PRIMARY KEY Constraint can be added after a table has been created and populated.

A CHECK Constraint can refer to values in other rows.

A UNIQUE Constraint can use a pre-existing index on the constrained column or columns.

A FOREIGN KEY Column can contain NULLS.

an attribute

a cardinality

label

an optionality

a unique identifier

an entity

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

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

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

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

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

GLOBAL TEMPORARY TABLE space allocation occurs at session start.

ORDER BY“CUST NO"

ORDER BY 2, cust_id

ORDERBY2, 1

ORDER BY "Last Name"

ORDER BY CUSTNO

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

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

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

A BLOB stores unstructured binary data within the database.

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

A BFILE stores unstructured binary data in operating system files.

The PROD ID column can be renamed.

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

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

The EXPIRY DATE column cannot be dropped.

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

LEFT OUTER JOIN with self join

PULL OUTER JOIN with self join

subquery

RIGHT OUTER JOIN with self join

self Join

You must have ANY TABLE system privileges, or be granted object privilges on the table, to viewa tabl e in DBA TABLES.

USER TABLES displays all tables owned by the current user.

You must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in USER_TABLES.

ALL TABLES displays all tables owned by the current user.

You must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in ALL_TABLES.

All users can query DBA_TABLES successfully.

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

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

FROM DUAL;

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

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

FROM DUAL;

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

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

FROM DUAL;

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

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

FROM DUAL;

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

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

FROM DUAL;

It includes rows that are returned by an inner join.

The Oracle join operator (+) must be used on both sides of the join condition in the WHERE clause.

It includes rows that are returned by a Cartesian product.

It returns matched and unmatched rows from both tables being joined.

It returns only unmatched rows from both tables being joined.

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

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

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

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

The FOR UPDATE clause cannot be specified.

A synonym can have a synonym.

A synonym has an object number.

Any user can create a public synonym.

All private synonym names must be unique in the database.

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

CREATE TABLE bookings (

bk_id NUMBER(4) NOT NULL PRIMARY KEY,

start_date DATE NOT NULL,

end_date DATE DEFAULT SYSDATE);

CREATE TABLE bookings (

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

start_date DATE NOT NULL,

end_date DATE DEFAULT SYSDATE);

CREATE TABLE bookings (

bk_id NUMBER(4) DEFAULT book_seq.CURRVAL,

start_date DATE DEFAULT SYSDATE,

end_date DATE DEFAULT start date);

CREATE TABLE bookings ( bk_id NUMBER(4),

start_date DATE DEFAULT SYSDATE,

end_date DATE DEFAULT (end_date >= start_date));

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

Schema owners can grant object privileges on objects in their schema to any other user or role.

A combination of object and system privileges can be granted to a role.

All types of schema objects have associated object privileges .

Only users with the DBA role can create roles .

Object privileges granted on a table automatically apply to all synonyms for that table.

Only users with the GRANT ANY PRIVILEGE privilege can grant and revoke system privileges from other users.

intersect ignores nulls.

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

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

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

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

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

A column drop is implicitly committed

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

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

A primary key column cannot be dropped.

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

SELECT TO_DATE(TO_NUMBER(INTERVATL '800' SECOND)) FROM DUAL;

SELECT TO_NUMBER(INTERVAL'800' SECOND, 'HH24:MM') FROM DUAL;

SELECT TO_DATE(INTERVAL '800' SECOND,'HH24:MM') FROM DUAL;

SELECT TO_NUWBER(TO_DATE(INTERVAL '800' SECOND)) FROM DUAL;

SELECT TO_CHAR(INTERVAL '800' SECOND, 'HH24:MM') FROM DUAL;