1z0-071 Exam Dumps - Oracle Database 12c SQL

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.

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.

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.

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.

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

ORDERBY 2, 1

ORDER BY "CUST_NO"

ORDER BY 2,cust_id

ORDER BY CUST_NO

ORDER BY "Last Name"

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

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

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

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

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

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

1

16

10

6

4

0

SELECT employee_ name FROM employees WHERE (SYSDATE – hire_ data) / 365>5

SELECT employee_ name FROM employees WHERE (SYSTIMESTAMP – hire_ data) / 365>

SELECT employee_ name FROM employees WHERE (CUARENT_ DATE – hire_ data / 365>5

SELECT employee_ name FROM employees WHERE (SYSNAYW – hire_ data / 12> 3

SELECT employee_ name FROM employees WHERE (SYSNAYW – hire_ data / 12> 3

SELECT employee_ name FROM employees WHERE (CUNACV_ DATE – hire_ data / 12> 3

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.

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

providing graphical capabilities

providing variable definition capabilities.

providing database transaction control

processing sets of data

providing update capabilities for data in external files

A synonym can have a synonym.

All private synonym names must be unique in the database.

Any user can create a PUBLIC synonym.

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

A synonym has an object number.

Drop pseudo columns from a table.

Restrict all DML statements on a table.

Drop all columns simultaneously from a table.

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

Rename a table

Enable or disable constraints on a table.

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.

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.

SELECT * FROM customers WHERE city ='D_%';

SELECT * FROM customers WHERE city ='%D_';

SELECT * FROM customers WHERE city LIKE'D %';

SELECT * FROM customers WHERE city LIKE'D_';

It must be an inner join.

It can be an outer join.

The ON clause must be used.

It must be an equijoin.

The query must use two different aliases for the table.

The ON clause can be used.

SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT

FROM customers

WHERE cust income_level !=NULL

AND cust credit_level !=NULL;

SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT

FROM customers

WHERE cust income_level IS NOT NULL

AND due_amount IS NOT NULL;

SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT

FROM customers

WHERE cust income_level <> NULL

AND due_amount <> NULL;

SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT

FROM customers

WHERE cust_income_level IS NOT NULL

AND cust_credit_limit IS NOT NULL;

SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT

FROM customers

WHERE cust income_level !=NULL

AND due_amount !=NULL;

ORDER BY 1,2.

ORDER BY last_name DESC,city ASC

CORADER BY 1, LNAME DESC

WHERE city=’%AN%;

WHERE city LIKE ’%AN%;

WHERE city IN (’%AN%’)

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.

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

It will return rows common to both select statements.

It will return rows both select statements including duplicate rows.

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

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

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

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.

ALTER

REFERENCES

UPDATE

SELECT

INDEX

INSERT

DELETE

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

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

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

Views cannot be used to query rows from an underlying table if the table has a PRIPOARY KEY and the PRIMARY KEY columns 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.

Views cannot be used to add on modify rows in an underlying table if the defining query of the view contains aggregating functions.

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.

A Relationship can be mandatory for both entities

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

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

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

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

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

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.

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.

2 rows

0 rows

An error

1 ROW

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.

64

6

3

12

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

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

ORDER BY“CUST NO"

ORDER BY 2, cust_id

ORDERBY2, 1

ORDER BY "Last Name"

ORDER BY CUSTNO

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

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

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

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

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

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

Base tables in the data dictionary have the prefix DBA_.

All user actions are recorded in the data dictionary.

The data dictionary is constantly updated to reflect changes to database objects, permissions, and data.

All users have permissions to access all information in the data dictionary by default

The SYS user owns all base tables and user-accessible views in the data dictionary.

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

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

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

FROM DUAL;

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

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

FROM DUAL;

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

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

FROM DUAL;

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

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

FROM DUAL;

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

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

FROM DUAL;

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;

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;

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.

It can be used from SQL Developer.

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

It can be used only from SQL*Plus.

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

It displays all constraints that are defined for each column.

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

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;

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.

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

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

A SEQUENCE can have a synonym.

A synonym can be available to all users .

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

Any user can drop a PUBLIC synonym.

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

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

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

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

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

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

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

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;

ORDER BY NVL(enam,0) DESC, ename

ORDER BY NVL(comm,0) ASC NULLS FIRST, ename

ORDER BY NVL(comm,0) ASC NULLS LAST, ename

ORDER BY comm DESC NULLS LAST, ename

Constraints are enforced only during INSERT operations.

A column with a foreign key constraint can never contain a null value.

All constraints can be defined at the table or column level.

A constraint can be disabled even if the constrained column contains data.

A column with a UNIQUE constraint can contain a NULL value.

LEFT OUTER JOIN with self join

PULL OUTER JOIN with self join

subquery

RIGHT OUTER JOIN with self join

self Join

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;

It drops any triggers defined on the table.

It retains the indexes defined on the table.

It retains the integrity constraints defined on the table.

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

It always retains the space used by the removed rows.

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

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.

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;

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.

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;

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.

DELETE FROM emp80

WHERE department_ id = 90;

SELECT

FROM emp80

WHERE department_ id = 90;

SELECT

FROM emp80

WHERE department. id = 80;

UPDATE emp80

SET department. 1d =80;

WHERE department_ id =90;

SUBTRACT

INTERSECT

UNION ALL

MINUS

UNION

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.

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

qty_sold ='05549821 ': requires explicit conversion

CONCAT(qty_sold, invoice_date): requires explicit conversion

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

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

For large tables TRUNCATE is faster than DELETE.

For tables with multiple indexes and triggers is faster than TRUNCATE.

You can never TRUNCATE a table if foreign key constraints will be violated.

You can never tows from a table if foreign key constraints will be violated.

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ï¼›

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

All remaining employee names will appear in an ascending order

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

All remaining employee names will appear in ascending order

All remaining employee names will appear in descending order

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

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

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;

They can be referenced In the where clause of an update or debete statement.

They can be referenced in the set clause of an update statement as the name of the column To be updated.

They can be indexed.

They cannot have a data type explicitly specified.

They can be referenced in the column expression of another virtxial column.

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.

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.

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.

the ACCESS PARAMETERS clause

the DEFAULT DIRECTORY clause

the access driver TYPE clause

the LOCATION clause

the REJECT LIMIT clause

The same table column can be part of a unique and non-unique index

A DESCENDING INDEX IS A type of function-based index

A DROP INDEX statement always prevents updates to the table during the drop operation

AN INVISIBLE INDEX is not maintained when DML is performed on its underlying table.

AN INDEX CAN BE CREATED AS part of a CREATE TABLE statement

IF a query filters on an indexed column then it will always be used during execution of query

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

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

Truncate a table in another schema.

Access flat files via a database, which are stored in an operating system directory.

Log in to a database.

Query any table in a database.

Use the WITH GRANT OPTION clause.

Execute a procedure in another schema.

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

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

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

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.

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

GRANT CREATE SEQUENCE TO manager, emp;

GRANT SELECT, INSERT ON hr.employees TO manager WITH GRANT OPTION:

GRANT CREATE TABLE, emp TO manager;

GRANT CREATE TABLE, SELECT ON hr. employees TO manager;

GRANT CREATE ANY SESSION, CREATE ANY TABLE TO manager;

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

Option A

Option B

Option C

Option D

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

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

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

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

16

160

150

200

100

1 and 3

1, 2 and 3

2 and 3

1 and 2

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.