Oracle 1z0-071 Dumps

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

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

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.

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.

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.

DURATION

+08-04

DUFATION

+100

DURATION

+08

an error

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;

16

160

150

200

100

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.

ROLLBACK;

COMMIT;

ROLIBACK TO SAVEPOINT post_ insert;

CONOIT TO SAVEPOINT post_ insert;

ROLLEBACK TO post_ insert;

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.

PRODUCT_ID can be assigned the PEIMARY 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 can be used in an arithmetic expression even if it has no value stored in it

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

PRODUCT_NAME cannot contain duplicate values.

ORDER BY“CUST NO"

ORDER BY 2, cust_id

ORDERBY2, 1

ORDER BY "Last Name"

ORDER BY CUSTNO

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

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

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

A query can access only tables within the same schema.

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

ALTER

REFERENCES

UPDATE

SELECT

INDEX

INSERT

DELETE

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.

Option A

Option B

Option C

Option D

Option E

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.

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;

a table

an attribute

a unique identifier

an activity

a relationship

an entity

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

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

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

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

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

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;

the ACCESS PARAMETERS clause

the DEFAULT DIRECTORY clause

the access driver TYPE clause

the LOCATION clause

the REJECT LIMIT clause

The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC)

A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the time zone of the session that inserted the row

A TIMESTAMP data type column contains information about year, month, and day

The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC)

The CURRENT_TIMESTAMP function returns data without time zone information

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.

0 rows

an error

8 rows

6 rows

1 row

3 rows

The WHERE clause can be used to exclude rows after dividing them into groups

WHERE and HAVING clauses can be used in the same statement only if applied to different table columns.

The HAVING clause can be used with aggregating functions in subqueries.

Aggregating functions and columns used in HAVING clauses must be specified in these SELECT list of a query.

The WHERE clause can be used to exclude rows before dividing them into groups.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1

16

10

6

4

0

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;

Arithmetic operators with equal precedence are evaluated from left to right within an expression.

Multiple parentheses can be used to override the default precedence of operators in an expression.

The + binary operator has the highest precedence in an expression in a SQL statements.

NULLS influence the precedence of operators in an expression.

The concatenation operator || is always evaluated before addition and subtraction in an expression.

SELECT (SYSDATE-DATE '2019-01-01') / 1 FROM DUAL;

SELECT 1 / SYSDATE - DATE '2019-01-01' FROM DUAL;

SELECT SYSDATE / DATE '2019-01-01' - 1 FROM DUAL

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

SELECT 1 – SYSDATE- DATE '2019-01-01' FROM DUAL;

SELECT SYSDATE - 1 - DATE'2019-01-01' EROM DUAL;

A SELECT statement cannot contain a where clause when querying a view containing a WHERE clause in its defining query

Rows inserted into a table using a view are retained in the table if the view is dropped

Views can join tables only if they belong to the same schema.

Views have no segment.

Views have no object number.

A view can be created that refers to a non-existent table in its defining query.

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)’ 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 “CURRENT AVAILABILITY”||q’ (‘s not available)’ from product_ status WHERE status

= ‘OUT OF STOCK’;

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

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.

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.

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

A DDL statement issued by a session with an uncommitted transaction automatically Commits that transaction.

DML statements always start new transactions.

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

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

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

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

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

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

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

THEY CAN BE USED in the where clause.

A SQL STATEMENT MAY HAVE MULTIPLE SINGLE ROW SUBQUERY BLOCKS.

THEY MUST BE PLACED ON THE RIGHT SIDE OF THE COMPARISON OPERATOR OR CONDITION.

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

THEY CAN BE USED IN THE HAVING CLAUSE

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

1 and 3

1, 2 and 3

2 and 3

1 and 2

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

Regardless of salary,only if the employee id is less than 125,insert EMPLOYEE_ID,NANAGER_ID,SALARY into the MGR_HISTORY table.

If the salary is more than 20000 and the employee is less than 125,insert EMPLOYEE_ID and SALARY into the SPECIAL_SAL table.

Only if the salary is 20000 or less and the employee id is less than 125,insert EMPLOYEE_ID,MANAGER_ID,and SALARY into the MGR_HISTORY table.

Regardless of salary and employee id,insert EMPLOYEE_ID,MANAGER_ID,and SALARY into the MGR_HISTORY table.

If the salary is 20000 or less and the employee id is less than 125,insert EMPLOYEE_ID,HIRE_DATE,and SALARY into the SAL_HISTORY table.

Only if the salary is 20000 or less and the employee id is 125 or higher,insert EMPLOYEE_ID,MANAGER_ID,and SALARY into the MDR_HISTORY table.

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.

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.

+ (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 primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option.

A DROP COLUMN command can be rolled back.

An UNUSED column's space is remained automatically when the block containing that column is next queried.

An UNUSED column's space is remained automatically when the row containing that column is next queried.

Partition key columns cannot be dropped.

A column that is set to NNUSED still counts towards the limit of 1000 columns per table.

SELECT &col1, &col2

FROM &&table

WHERE &condition;

SELECT &col1, &col2

FROM “&table”

WHERE &condition;

SELECT &&col1,&&col2

FROM &table

WHERE &&condition= &&cond;

SELECT'&co11','&&co12'

FROM &table

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

SELECT&&col1, &&col2

FROM &table

WHERE &&condition;

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.

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;

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

Foreign key columns allow null values.

Unique key columns allow null values

Primary key columns allow null values.

Every primary or unique key value must refer to a matching foreign key value.

Every foreign key value must refer to a matching primary or unique key value.

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.

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

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.

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

REFERENCES can be grantrd only on tables.

INSERT can be granted only on tables and sequences.

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

ALTER can be granted only on tables and sequences.

SELECT last name,

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

annual comp

FROM employees

;

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

FROM employees ;

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

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

FROM employees;

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

annual_ comp

FROM employees;

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

The WITH CHECK clause prevents certain rows from being updated or inserted.

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

Date Manipulation Language (DML) can always be used on views.

Deleting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error.

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

Inserting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error.

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.

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.

ALTER TAELE products DROP COLUMN expiry_date;

CREATE INDEX price_idx on products (price);

ALTER TABLE products SET UNUSED(expiry_date);

TRUNCATE TABLE products;

ALTER TABLE products DROP UNUSED COLUMNS

DROP TABLE products

SUBTRACT

INTERSECT

UNION ALL

MINUS

UNION

MERGE INTO employees e

USING new employees ne

WHERE e.employee_id = ne.employee_ id

WHEN MATCHED THEN

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

WHEN NOT MATCHED THEN

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

MERGE INTO employees e

USING new_employees n

ON (e.employee_id = ne.employee_id)

WHEN MATCHED THEN

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

WHEN NOT MATCHED THEN

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

MERGE INTO employees e

USING new employees ne

ON (e.employee_id = ne.employee_id)

WHEN FOUND THEN

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

WHEN NOT FOUND THEN

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

MERGE INTO employees e

USING new_employees n

WHERE e.employee_id = ne.employee_id

WHEN FOUND THEN

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

WHEN NOT FOUND THEN

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

The join key column must have an index.

It can be a left outer join.

It must be a full outer join.

It can be an inner join.

It must be an equijoin.

The WHEN NOT MATCHED clause can be used to specify the deletions to be performed.

The WHEN NOT MATCHED clause can be used to specify the inserts to be performed.

The WHEN MATCHED clause can be used to specify the inserts to be performed.

The WHEN NOT MATCHED clause can be used to specify the updates to be performed.

The WHEN MATCHED clause can be used to specify the updates to be performed.

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;

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.

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

Display suppliers whose PROD_LIST_PRICE is less than 1000.

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

Display the minimum PROD_LIST_PRICE for each product status.

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

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.

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

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

It can be used when granting privileges to roles.

It can be used for system and object privileges.

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

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

2 rows

0 rows

An error

1 ROW

The MGR column in the DEPT table will not be able to contain NULL values.

The CREATE TABLE EMP statement must precede the CREATE TABLE DEPT statement for all threestatements to execute successfully.

Both foreign key constraint definitions must be removed from the CREATE TABLE statements, andbe added with ALTER TABLE statements once both tables are created, for the two CREATE TABLEstatements to

execute successfully in the order shown.

The DEFT FKEY constraint definition must be removed from the CREATE TABLE DEF statement.and be added with an AITER TABLE statement once both tables are created, for the two CREATE TABLE statements

to execute successfully in the order shown.

The Deptno column in the emp table will be able to contain nulls values.

All three statements execute successfully in the order shown

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

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

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

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

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

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.

3

2

1

5 01-MAR-2019

4 01-FEB-2019

3 01-JAN-2019

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:

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 *

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;

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.

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.

It displays values for variables created by the DEFINE command.

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

It can be used only in SQL*plus.

It displays values for variables prefixed with &&.

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

SELECT NULLIF(100, 100) FROM DUAL；

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

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

SELECT NULLIF(NULL, 100) FROM DUAL；

SELECT COALESCE(100, 'A' ) FROM DUAL；

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.

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;

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)

TIMESTAMP WITH TIMEZONE

TIMESTAMP

TIMESTAMP WITH LOCAL TIMEZONE

INTERVAL YEAR TO MONTH

INTERVAL DAY TO SECOND

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;