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

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;

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.

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 REPLACE (REPLACE(cust_last_name,'son',''),'An','O') FROM customers;

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

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

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

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.

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.

ALL constraints defined on HR, EMPLOYEES are dropped.

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

Synonyms for HR EMPLOYEES are dropped.

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

All indexes defined on HR, EMPLOYEES are dropped.

Views referencing HR, EMPLOYEES are dropped.

3

2

1

5 01-MAR-2019

4 01-FEB-2019

3 01-JAN-2019

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

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

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

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

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

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

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.

SELECT prod_id, exp_date FROM products

UNION ALL

SELECT prod_id, NULL FROM new_products;

SELECT prod_id, prod_name FROM products

INTERSECT

SELECT 100, prod_name FROM newproducts;

SELECT * FROM products

UNION

SELECT * FROM new_products;

SELECT k FROM products

MINUS

SELECT prod_id FROM new_products;

SELECT prod_id FROM products

UNION ALL

SELECT prod_id, prod_name FROM new_products;

ORDER BY

WHERE

GROUP BY

SELECT

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 #####

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.

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

Creating an external table creates a directory object.

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

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

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

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

SELECT currency_ code FROM currencies

MINUS

SELECT currency_ code FROM invoices;

SELECT * FROM currencies

WHERE NOT EXISTS (

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

SELECT currency_ code FROM currencies

INTERSECT

SELECT currency_ code FROM invoices;

SELECT * FROM currencies

MINUS

SELECT * FROM invoices;

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 first expression in NVL2 is never returned.

NVL2 can have any number of expressions in the list.

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

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

NVL must have expressions of the same data type.

NVL can have any number of expressions in the list.

The Oracle join syntax only supports right outer joins,

The Oracle join syntax supports creation of a Cartesian product of two tables.

The SQL:1999 compliant ANSI join syntax supports natural joins.

The Oracle join syntax supports natural joins.

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

The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables.

The Oracle join syntax performs less well than the SQL:1999 compliant ANSI Join Answer.

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.

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;

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

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.

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

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

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

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

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

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 department_id, MAX(salary)

FROM employees

GROUP BY department_id;

SELECT MAX (salary)

FROM employees;

SELECT MAX (salary)

FROM employees

GROUP BY department_id;

SELECT MAX (salary)

FROM employees

GROUP BY department_id

HAVING MAX (salary) = MAX (MAX (salary));

SELECT MAX (MAX (salary))

FROM employees

GROUP BY department_id;

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

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

Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.

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

Delete statements can always be done on a table tough a view.

Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view.

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.

INTERSECT ignores NULLs

Execute the SET VERIFY ON command before executing the query.

Execute the SET VERIFY OFF command before executing the query.

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

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

Use the UNDEFINE command before executing the query.

Use the DEFINE command before executing the query

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

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

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.

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

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

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

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

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

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

The USER SYNONYMS view can provide information about private synonyms.

The user SYSTEM owns all the base tables and user-accessible views of the data dictionary.

All the dynamic performance views prefixed with V$ are accessible to all the database users.

The USER OBJECTS view can provide information about the tables and views created by the user only.

DICTIONARY is a view that contains the names of all the data dictionary views that the user can access.

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

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

INSERT INTO rate_list VALUES (0.999) produces an error.

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

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

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

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

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.

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.

A customer can exist in many countries.

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

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

The SALES table has five foreign keys.

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

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

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

CREATE GLOBAL SYNONYM emp FOR hcm.employee_records;

CREATE SYNONYM emp FOR hcm.employee_records;

CREATE SYNONYM PUBLIC.emp FOR hcm.employee_records;

CREATE SYNONYM SYS.emp FOR hcm.employee_records;

CREATE PUBLIC SYNONYM emp FOR hcm. employee_records;

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;

COMMIT erases all the transaction’s savepoints and releases its locks.

COMMIT ends the transaction and makes all its changes permanent.

ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes but does not release its locks.

ROLLBACK to SAVEPOTNT undoes the transaction's changes made since the named savepoint and then ends the transaction.

ROLLBACK without the TO SAVEPOINT clause undoes alt the transaction's changes, releases its locks, and erases all its savepoints.

ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes but does not erase its savepoints.

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.

SALES1 is created with 1 row.

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

SALES1 Is created with 5,000 rows.

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

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

1 and 4 give the same result.

2 returns the value 20.

2 and 3 give the same result.

3 returns an error.

1 and 4 give different results.

DELETE FROM scott. emp;

GRANT UPDATE ON scott. emp TO fin manager;

AITER SESSION set NLs. _DATE FORMAT = 'DD/MM/YYYY';

TRUNCATE TABLE emp:

SELECT * FROM user_ tab._ privs;

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 relatonship in one direction is a one-to-one relationship in the other direction

1 and 3

1, 2 and 3

2 and 3

1 and 2

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

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.

DURATION

+08-04

DUFATION

+100

DURATION

+08

an error

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

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.

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;

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 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.

SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

SELECT promo_cost, promo_category FROM promotions ORDER BY 1

SELECT promo_category, DISTINCT promo_cost FROM promotiong ORDER BY 2:

select DISTINCT promo_categoryIl ‘has’||promol_cost as COSTS FROM promotions ORDER BY 1:

SELECT DISTINCT promo_cost ||’in’IIDISTINCT promo_category promotions ORDER BY1:

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.

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

A BFILE stores unstructured binary data in operating systerm files

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

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

Line 7

Line 8

Line 3

Line 5

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.

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

CROSS

FULL OUTER

LEFT OUTER

RIGHT OUTER

SELECT q'! Hello! We're ready! 'FROM DUAL;

SELECT "Hello! We're ready "FROM |DUAL;

SELECT q'[Hello! We're ready]'FROM DUAL;

SELECT 'Hello! we\ re ready' ESCAPE'N'FROMDUAL:

SELECT 'Hello! We're ready' FROM DUAL;

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.

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

It cannot have an ELSE clause.

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

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

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

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.

providing graphical capabilities

providing variable definition capabilities.

providing database transaction control

processing sets of data

providing update capabilities for data in external files

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.

only departments where the total salary is greater than 3000, returned in no particular order

all departments and a sum of the salaries of employees with a salary greater than 3000

an error

only departments where the total salary is greater than 3000, ordered by department

SERIAL_NO

ORDER_DATE

PRODUCT_ ID

ORDER TOTAL

ORDER_ ID

STATUS

ROLLBACK;

COMMIT;

ROLIBACK TO SAVEPOINT post_ insert;

CONOIT TO SAVEPOINT post_ insert;

ROLLEBACK TO post_ insert;

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

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

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

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

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;

It executes successfully and displays rows in the descend ignore of PROMO CATEGORY.

It produces an error because positional, notation cannot be used in the ORDER BY clause with SBT operators.

It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement.

It produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement.

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

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

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

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

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

SELECT COUNT (*)

FROM employees

JOIN employees a

ON e. salary< a. salary

WHERE a. employee_ id= 110;

SELECT COUNT (* )

FROM employees

WHERE salary < (SELECT salary FROM employees WHERE employee 业id =

110) ;

SELECT COUNT (*)

FROM employees e

JOIN (SELECT salary FROM employees WHERE employee_ id= 110) a

ON e. salary< a. salary;

SELECT COUNT (* )

FROM employees e

WHERE e. salary < (SELECT a. salary FROM employees a WHERE e. employee_ id = 110);

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;

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

SELECT * FROM TABLE (123) ;

SELECT * FROM '123';

SELECT * FROM "123";

SELECT * FROM V'123V';

CONCAT joins two or more character strings together.

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

CONCAT joins two character strings together.

INSTR finds the offset within a string of a single character only.

INSTR finds the offset within a character string, starting from position 0.

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

CONCAT: can be used to combine any number of values

MOD: returns the quotient of a division operation

CEIL: can be used for positive and negative numbers

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

TRUNC: can be used with NUMBER and DATE values

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.

Add a new column as the table's first column.

Define a default value that is automatically inserted into a column containing nulls.

Add a new NOT NULL Column with a DEFAULT value.

Change a DATE Column containing data to a NUMBER data type.

Increase the width of a numeric column.

Change the default value of a column.

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.

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

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

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

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

INTERVAL DAY TO SECOND columns support fractions of seconds.

INTERVAL YEAR TO MONTH columns support yearly intervals.