1z0-071 Oracle Database 12c SQL Questions and Answers

SELECT dept_id, join_date,SUM(salary) FROM employees GROUP BY dept_id, join_date;

SELECT depe_id,join_date,SUM(salary) FROM employees GROUP BY dept_id:

SELECT dept_id,MAX(AVG(salary)) FROM employees GROUP BY dept_id;

SELECT dept_id,AVG(MAX(salary)) FROM employees GROUP BY dapt_id;

INTERVAL ‘12:00’

INTERVAL’0,5’DAY

INTERVAL’12’ HOUR

INTERVAL’720’MINUTE

INTERVAL’0 12’DAY TO HOUR

INTERVAL’11:60’HOUR TO MINUTE

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

An allas name must not contain space characters.

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

An alias name must always be specified in quotes.

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

SALES1 created with 55, 000 rows

SALES1 created with no rows.

SALES1 created with 1 row.

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

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.

The date is in the time zone of DBTIMEZONE.

The value varies depending on the setting of SESSIONTIMEZONE.

It returns the same date as CURRENT_DATE.

The time is in the time zone of DBTIMEZONE.

It returns a value of data type TIMESTAMP

It always returns the same value as SYSTIMESTAMP

SELECT 'The first_name is '' || first_name || '' FROM employees ;

SELECT 'The first_name is '''||first_name ||'''' FROM employees ;

SELECT 'The first_name is ''' ||first_name||''' FROM employees ;

SELECT 'The first_name is '|| first_name|| '' FROM employees;

SELECT 'The first_name is \'' || first_name || '\'' FROM employees;

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

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

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

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

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

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

SELECT * FROM customers WHERE customer_id=’0001’;

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

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

SELECT * FROM customers WHERE customer_id=0001;

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

Line 7

Line 8

Line 3

Line 5

column positions must be used in the order by clause.

The first column in the first select of the compound query with the union operator is used by default to sort output in the absence of an order by clause.

Each select statement in the compound query must have its own order by clause.

only column names from the first select statement in the compound query are recognized.

Each select statement in the compound query can have its own order by clause.

It processes NULLS in the selected columns.

INTERSECT is of lower precedence than UNION or UNION ALL.

It ignores NULLS.

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

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.

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

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.

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

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

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

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

All set operators are valid on columns all data types.

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

Backup and recovery operations are available for these tables.

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

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

Indexes can be created on them.

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

SELECT * FROM customers WHERE customer_ id = 0001 ;

SELECT * FROM customers WHERE customer id = ‘0001’;

SELECT * FROM customers WHERE insert_ date = DATE ‘2019-01-01’;

SELECT * FROM customers WHERE insert date =’01-JAN-19’

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

SELECT ”

FROM employees

WHERE salary > ANY

SELECT AVG (salary)

EROM employees

GROUP BY department_ id);

SELECT

FROM employees

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

SELECT”

FROM employees e1

WHERE salary >!

SELECT AVG (salary)

FROM employees e2

WHERE e1. Department _id = e2, department_ id

SELECT.

FROM

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

FROM employees e

WHERE salary > avg_ sal;

SELECT”

FROM employees

WHERE salary >

( SELECT AVG

(salary) FROM

employees

GROUP BY department _ id

2 rows

0 rows

An error

1 ROW

A SELECT statement can access one or more indices without accessing any tables.

A table belonging to one user can have an index that belongs to a different user,

When a table is dropped and is moved to the RECYCLE BIN, all Indexes built on that table are permanently dropped.

A UNIQUE index can be altered to be non-unique.

An update to a table can result in no updates to any of the table's indexes.

An update to a table can result in updates to any or all of the table's indexes.

SELECT last_ name, salary

FROM employees

FETCH FIRST 3 ROWS ONLY

ORDER BY salary;

SELECT last name, salary

FROM employees

ORDER BY salary

FETCE FIRST 3 RONS ONLY;

SELECT last_ name, salary

FBOM employees

WEERE

ORDER BY SELECT

ROINUM <= 3

salary FROM

employees);

SELECT last_ name, salary

FROM

(SELECT” FROM employees ORDER BY salary)

WHERE ROWNUM <=3

SELECT last_ name, salary

FROM employees

WHERE ROWNUM <=3

ORDER BY salary

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

It best supports relational databases

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

It requires that data be contained in hierarchical data storage

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

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

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

28

0

14

42

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

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.

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

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

Sequence ORD_SEQ is guaranteed not to generate duplicate numbers.

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.

(SELECT * FROM orders

UNION ALL

SELECT* FROM invoices) ORDER BY order _id;

SELECE order _id, order _ date FRON orders

LNTERSECT

SELECT invoice_ id, invoice_ id, order_ date FROM orders

SELECT order_ id, invoice_ data order_ date FROM orders

MINUS

SELECT invoice_ id, invoice_ data FROM invoices ORDER BY invoice_ id;

SELECT * FROM orders ORDER BY order_ id

INTERSEOT

SELECT * FROM invoices ORDER BY invoice_ id;

SELECT order_ id, order_ data FROM orders

UNION ALL

SELECT invoice_ id, invoice_ data FROM invoices ORDER BY order_ id;

SELECT * FROM orders

MINUS

SELECT * FROM INVOICES ORDER BY 1

SELECT * FROM orders ORDER BY order_ id

UNION

SELECT * FROM invoices;

They always use subqueries.

They can transform a row from a source table into multiple rows in a target table.

The conditional INSERT FIRST statement always inserts a row into a single table.

The conditional INSERT ALL statement inserts rows into a single table by aggregating source rows.

The unconditional INSERT ALL statement must have the same number of columns in both the source and target tables.

SELECT 1, 2 FROM DUAL

UNION

SELECT 3, 4 FROM DUAL

ORDER BY 1, 2;

SELECT 3 FROM DUAL

UNION

SELECT 4 FROM DUAL

ORDER BY 3 ;

SELECT 1, 2 FROM DUAL

UNION

SELECT 3, 4 FROM DUAL

ORDER BY 3, 4;

SELECT 1 FROM DUAL

UNION

SELECT 2 FROM DUAL

ORDER BY 1, 2;

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

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

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

SELECT product_id, expiry_date * 2 from products;

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

SELECT product_id, unit_price, unit_price + surcharge FROM products;

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

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

SELECT customer_ name FROM customers WHERE customer_ name LIKE ' % a % ’ ;

SELECT customer_ name FROM customers WHERE customer name LIKE 'Ma%' ;

SELECT customer_ name FROM customers WHERE customer_ name='*Ma*';

SELECT customer_ name FROM customers WHERE UPPER (customer_ name ) LIKE 'MA*. ;

SELECT customer_ name FROM customers WHERE customer name LIKE 'Ma*';

SELECT customer_ name FROM customers WHERE UPPER (customer name) LIKE 'MA&';

SELECT customer_ name FROM customers WHERE customer_ name KIKE .*Ma*';

ORDERBY 2, 1

ORDER BY "CUST_NO"

ORDER BY 2,cust_id

ORDER BY CUST_NO

ORDER BY "Last Name"

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;

Use the DEFINE command before executing the query.

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

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

Execute the SET VERIFY OFF command before executing the query.

Use the UNDEFINE command before executing the query.

Execute the SET VERIFY ON command before executing the query.

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

Only one SAVEPOINT may be issued in a transaction.

A SAVEPOINT does not issue a COMMIT

Rolling back to a SAVEPOINT can undo a TRUNCATE statement.

Rolling back to a SAVEPOINT can undo a DELETE statement

TIMESTAMP WITH TIMEZONE

TIMESTAMP

TIMESTAMP WITH LOCAL TIMEZONE

INTERVAL YEAR TO MONTH

INTERVAL DAY TO SECOND

When the session logs out is successfully

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

When a CREATE INDEX statement is executed successfully in same session

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

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

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

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

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

The join syntax used makes no difference to performance.

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

Table aliases can improve performance.

ROLLBACK;

COMMIT;

ROLIBACK TO SAVEPOINT post_ insert;

CONOIT TO SAVEPOINT post_ insert;

ROLLEBACK TO post_ insert;

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.

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

The ON clause can be used.

The USING clause can be used.

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

Table aliases must be used.

The Oracle join syntax can be used.

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

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

A SQL statement may have multiple single row subquery blocks.

They can be used in the HAVING clause.

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

They can be used in the clause.

UPDATE employees e1

SET salary =(SELECT e2. salary + MIN(e2.salary)

FROM employees e2

WHERE e1.department_ id = e2. department_id GROUP BY e2. department_id) ;

UPDATE employees e1

SET salary = salary +

(SELECT MIN(e1. salary)

FROM employees e2

WHERE e1.department_id = e2 .department_id);

UPDATE employees e1

SET salary = salary+(SELECT MIN (salary)

FROM employees e2) ;

UPDATE employees e1

SET salary=

(SELECT e1.salary + MIN(e2.salary)

FROM employees e2

WHERE e1. department_ id = e2.department_id);

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.

They can contain HAVING clauses.

Two or more values are always returned from the subquery.

They cannot contain subquery.

They can return multiple columns.

They can contain GROUP BY clauses.

The subquery is executed before the UPDATE statement is executed.

All existing rows in the ORDERS table are updated.

The subquery is executed for every updated row in the ORDERS table.

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

The subquery is not a correlated subquery.

It can merge rows only from tables.

It can use views to produce source rows.

It can combine rows from multiple tables conditionally to insert into a single table.

It can use subqueries to produce source rows.

It can update the same row of the target table multiple times.

It can update, insert, or delete rows conditionally in multiple tables.

the ACCESS PARAMETERS clause

the DEFAULT DIRECTORY clause

the access driver TYPE clause

the LOCATION clause

the REJECT LIMIT clause

Roles are assigned to roles using the ALTER ROLE Statement

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

Roles are assigned to users using the ALTER USER statement

A single role can be assigned to multiple users.

A single user can be assigned multiple roles

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

Privileges are assigned to a role using the GRANT statement.

SELECT (COUNT(list_price) FROM Product_intormation WHERE list_price=NULL;

SELECT count(nvl( list_price,0)) FROM product_information WHERE list_price is null;

SELECT COUNT(DISTINCT list_price) FROM product_information WHERE list_price is null.

BELECT COUNT(list_price) FROM product_information where list_price is NULL;

MERGE INTO new_prices n

USING(SELECT*FROM products)p

WHEN MATECHED 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 product WHERE cost>150) p

ON (n.prod_id=p.prod_id)

WHEN NATCHED THEN

DELETE WHERE(p.cost<200)

WHEN NOT MATCHED THEN

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

MERGE INTO new_prices n

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

ON (n.prod_id=p.prod_id)

WHEN NATCHED THEN

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

DELETE WHERE (p.cost<200);

MERGE INTO new_prices n

USING products p

WHEN NOT NATCHED THEN

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

WHERE (p.cost <200);

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

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

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

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

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

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

The second ROLLBACK command does nothing.

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

The second ROLLBACK command replays the delete.

The second ROLLBACK command undoes the insert.

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

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

SELECT COALESCE(O,SYSDATE) TRCH DUAL;

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

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

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

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

A system privilege is required.

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

A primary key constraint is manadatory.

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;

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

An inner join relates rows within the same table.

Rows from unrelated tables cannot be joined.

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

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

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

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

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

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

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

Dropping user FINANCE will automatically revoke SELECT on SCOTT. EMP from user FIN _ CLERK

Revoking SELECT on SCOTT. EMP from user FINANCE will also revoke the privilege from user FIN_ CLERK.

User FINANCE can grant CREATE SESSION to user FIN MANAGER.

User FIN CLERK can grant SELECT on SCORT, ENP to user FIN MANAGER.

User FINANCE is unable to grant ALL on SCOTT.ENP to FIN MANAGER.

RIGHT OUTER JOIN with seif join

FULL OUTER JOIN with seif join

SUBQUERY

seif join

LEFT OUTER JOIN with seif join

A full outer join returns matched and unmatched rows.

A full outer join must use Oracle syntax.

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

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

An inner join returns matched rows.

A left or right outer join returns only unmatched rows.

SELECT INTERVAL '1' DAY - SYSDATE FROM DUAL;

SELECT SYSTIMESTAMP + INTERVAL '1' DAY FROM DUAL;

SELECT INTERVAL '1' DAY - INTERVAL '1' MINUTE FROM DUAL;

select INTERVAL '1' DAY +INTERVAL '1' MONTH FROM DUAL;

SELECT SYSDATE “INTERRVAL '1' DAY FROM DUAL;

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

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

CASE is a function and DECODE is not.

DECODE is a function and CASE is not.

Neither CASE nor DECODE is a function.

Both CASE and DECODE are functions.

It can display multiple rows and columns.

It can be accessed only by the SYS user.

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

It can display multiple rows but only a single column.

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

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

SELECT shape,color,weight from bricks

MINUS

SELECT * FROM bricks_stage;

SELECT shape,color FROM bricks

MINUS

SELECT WEIGHT,color FROM bricks_stage;

select * from bricks

MINUS

select * from bricks_stage;

SELECT shape,color FROM bricks

MINUS

SELECT color,shape FROM bricks_stage;

SELECT brick_id,shape FROM bricks

MINUS

SELECT WEIGHT,COLOR from bricks_stage;

Indexes can be created on them.

You can populate them from existing data in the database by using the CREATE TABLE AS SELECT command.

DML statements cannot be used on them.

Their data can be retrieved by using only SQL or PL/SQL.

Their metadata and actual data are both stored outside the database.

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.

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

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

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

It executes successfully with an implicit data type conversion

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

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

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

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

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

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

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

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

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

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

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

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.

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

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.

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

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

SELECT shape FROM bricks JOIN boxes ON weight > min_weight;

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

SELECT shape FROM bricks JOIN boxes ON weight < max_weight;

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

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

System prilgese always set prilge for an entire database.

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

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

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

A role is owned by the user who created it.

All roles are owned by the sYs schema.

PUBIIC can be revoked from a user.

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.

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

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

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

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

SELECT NEXTVAL FROM emp_ seq;

SELECT CURRVAL FROM emp_ seq;

SELECT emp_ seq. CURRVAL FROM DUAL;

SELECT emp_ seq . NEXTVAL FROM DUAL;

ALTER

REFERENCES

UPDATE

SELECT

INDEX

INSERT

DELETE

Views can be indexed.

Theethi CHEcK clause prevents certalin rows from being updated or inserted in the underying table through the view.

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

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

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

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

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

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

A sequence can issue duplicate values.

Sequences can always have gaps.

A sequence can only be dropped by a DBA.

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

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

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

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

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.

SELECT dept_ id, AVG (MAX(salary)) FROM employees GROUP By dept_id HAVING hire_date> ' O1-JAN-19';

SELECT dept_ id, AVG(MAX(salary)) FROM employees GROUP BY dept_id, salary;

SELECT dept id, MAX (SUM(salary)) FROM employees GROUP BY dept_id;

SELECT dept_ iD, sum(salary) FROM employees WHERE hire_date > '01-JAN-9' GROUP BY dept_id;

SELECT AVG(MAX(salary)) FROM employees GROUP BY 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)

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%'

TO_NUMBER(PROMO_BEGIN_DATE)-5 will return number

TO_DATE(PROMO_BEGIN_DATE * 5) will return a date

PROMO_BEGIN_DATE-SYSDATE will return a number.

PROMO_BEGIN_DATE-5 will return a date.

PROMO_BEGIN_DATE-SYSDATE will return an error.