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

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.

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.

64

6

3

12

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.

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.

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;

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.

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.

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

GRANT UPDATE (title, address) ON customers TO andrew WITH ADMIN OPTION;

GRANT UPDATE ON customers. title, customers.address TO andrew WITH GRANT OPTION;

GRANT UPDATE ON customers.title, customers.address TO andrew WITH ADMIN OPTION;

GRANT UPDATE (title, address) ON customers TO andrew;

GRANT UPDATE ON customers. title, customers.address TO andrew;

GRANT UPDATE (title, address) ON customers TO andrew WITH GRANT OPTION:

RIGHT OUTER JOIN with seif join

FULL OUTER JOIN with seif join

SUBQUERY

seif join

LEFT OUTER JOIN with seif join

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.

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

none

&hiredate and &mgr_id

only &hiredate

only &mgr_id

A synonym can have a synonym.

A synonym has an object number.

Any user can create a public synonym.

All private synonym names must be unique in the database.

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

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 produet_id, unit_pricer, 5 "Discount",unit_price+surcharge-discount FROM products;

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

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

SPLECT product_id, expiry_date * 2 FROM products;

SELEGT product_id, unit_price, unit_price + surcharge FROM products;

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

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;

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

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

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

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

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

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.

28

0

14

42

A query can display data from the DEPARTMENT_ID column.

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

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

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

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

The DEPARTMENT_ID column can be recovered from the recycle bin

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.

An INVISIBLE index is not maintained when Data Manipulation Language (DML) is performed on its underlying table.

An index can be created as part of a CREATE TABLE statement.

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

A UNIQUE and non-unique index can be created on the same table column

A descending index is a type of function-based index

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

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.

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.

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

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.

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.

A scalar subquery expression that returns zero rows evaluates to zoro

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

They can be nested.

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

They cannot be used in group by clauses.

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

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

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

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

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

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

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

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.

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;

LEFT OUTER JOIN with self join

PULL OUTER JOIN with self join

subquery

RIGHT OUTER JOIN with self join

self Join

An ORDER BY clause always sorts NULL values last.

An ORDER BY clause can perform a binary sort

An ORDER BY clause can perform a linguistic sort

By default an ORDERBY clause sorts rows in ascending order

An ORDR BY clause will always precede a HAVI NG clause if both are used in the same top-level

1

16

10

6

4

0

Use the DEFINE command before executing the query.

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

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

Execute the SET VERIFY OFF command before executing the query.

Use the UNDEFINE command before executing the query.

Execute the SET VERIFY ON command before executing the query.

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.

It can display multiple rows and columns.

It can be accessed only by the SYS user.

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

It can display multiple rows but only a single column.

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

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

An associative table must be created with a composite key of STUDENT_ID and PROJRCT_ID, which is the foreign key linked to the STUDENTS and PROJECTS entities.

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

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

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

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

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.

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

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.

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

Column ORD_NO gets the next number from sequence ORD_SEQ whenever a row is inserted into ORD_ITEMS and no explicit value is given for ORD_NO.

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

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

Sequence ORD_SEQ is guaranteed not to generate duplicate numbers.

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.

CREATE TABLE bookings (

bk_id NUMBER(4) NOT NULL PRIMARY KEY,

start_date DATE NOT NULL,

end_date DATE DEFAULT SYSDATE);

CREATE TABLE bookings (

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

start_date DATE NOT NULL,

end_date DATE DEFAULT SYSDATE);

CREATE TABLE bookings (

bk_id NUMBER(4) DEFAULT book_seq.CURRVAL,

start_date DATE DEFAULT SYSDATE,

end_date DATE DEFAULT start date);

CREATE TABLE bookings ( bk_id NUMBER(4),

start_date DATE DEFAULT SYSDATE,

end_date DATE DEFAULT (end_date >= start_date));

CREATE TABLE bookings (

bk_id NUMBER(4) DEFAULT book_seq.NEXTVAL PRIMARY KEY,

start_date DATE DEFAULT SYSDATE,

end_date DATE DEFAULT SYSDATE NOT NULL);

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.

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;

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 attribute

a cardinality

label

an optionality

a unique identifier

an entity

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.

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.

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.

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.

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.

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' 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' AND member_id = 'A101' OR member_id = 'A102');

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

sorted by DEPARTMENT_NAME

sorted by DEPARTMENT_NAME and AVGSAL

sorted by DEPARTMENT_NAME and MAXSAL

sorted by AVGSAL

Sorted by MAXSAL

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;

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.

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

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 * FROM customers WHERE TO_ CHAR (customer_ id) = '0001';

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

SELECT * FROM customers WHERE customer_ id = 0001;

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

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

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

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

PRODUCT_ ID can be assigned the PRIMARY KEY constraint.

EXPIRY_ DATE cannot be used in arithmetic expressions.

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

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

PRODUCT_ NAME cannot contain duplicate values.

SELECT NEXTVAL FROM emp_ seq;

SELECT CURRVAL FROM emp_ seq;

SELECT emp_ seq. CURRVAL FROM DUAL;

SELECT emp_ seq . NEXTVAL FROM DUAL;

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.

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

Backup and recovery operations are available for these tables.

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

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

Indexes can be created on them.

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

Create a directory object for an external table.

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

Query data from an external table.

Create a directory object for a flat file.

Execute DML statements on an external table.

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

SELECT emp. *

FROM employees emp

WHERE manager_ id NOT IN (

SELECT mgr.employee_ id

FROM employees mgr

WHERE emp. department_ id < > mgr.department_ id

);

SELECT emp.*

FROM employees emp

WHERE NOT EXISTS (

SELECT NULL

FROM employees mgr

WHERE emp.manager id = mgr.employee_ id

AND emp.department_id<>mgr.department_id

);

SELECT emp.*

FROM employees emp

LEFT JOIN employees mgr

ON emp.manager_ id = mgr.employee_ id

AND emp. department id < > mgr. department_ id;

SELECT emp. *

FROM employees emp

RIGHT JOIN employees mgr

ON emp.manager_ id = mgr. employee id

AND emp. department id <> mgr.department_ id

WHERE emp. employee_ id IS NOT NULL;

SELECT emp. *

FROM employees emp

JOIN employees mgr

ON emp. manager_ id = mgr. employee_ id

AND emp. department_ id<> mgr.department_ id;

INTERVAL ‘12:00’

INTERVAL’0,5’DAY

INTERVAL’12’ HOUR

INTERVAL’720’MINUTE

INTERVAL’0 12’DAY TO HOUR

INTERVAL’11:60’HOUR TO MINUTE

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.

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.

A role is owned by the user who created it.

System privileges always set privileges for an entire database.

All roles are owned by the SYS schema.

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

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

PUBLIC can be revoked from a user.

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

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

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

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

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

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

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.

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

Line 7

Line 8

Line 3

Line 5

They always use subqueries.

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

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

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

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

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 COUNT(prod_id) FROM sales WHERE quantity_sold>55000 GROUP BY prod_id;

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

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

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

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

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;

INSERT INTO (SELECT order_id, order date, customer_id FROM orders) VALUES (1, ‘09-mar-2007“，101);

INSERT INTO orders (order_id, order_date, order mode,customer_id, order_total) VALUES (1, TO_DATE (NULL),‘online‘,101, NULL) ;

INSERT INTO orders VALUES (1, ‘09-mar-2007’, ‘online’,’ ’,1000);

INSERT INTO orders (order id, order_date, order mode, order_total)VALUES (1，‘10-mar-2007’,’online’, 1000)

INSERT INTO orders VALUES(‘09-mar-2007’，DEFAULT,101, DEFALLT);

MERGE INTO new_prices n

USING (SELECT * FROM products) p

WHEN MATCHED THEN

UPDATE SET n.price= p.cost* 01

WHEN NOT MATCHED THEN

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

WHERE(p.cost<200);

MERGE INTO new_prices n

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

ON (n.prod_id= p.prod_id)

WHEN MATCHED THEN

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

DELETE WHERE (p.cost<200);

MERGE INTO new_prices n

USING products p

ON (p.prod_id =n.prod_id)

WHEN NOT MATCHED THEN

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

WHERE (p.cost<200);

MERGE INTO new_prices n

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

ON (n.prod_id= p.prod_id)

WHEN MATCHED THEN

DELETE WHERE (p.cost<200)

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.

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:

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.

16

160

150

200

100

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.

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.

WHERE TO_NUMBER(start_date - SYSDATE)<=25

WHERE ADD_MONTHS (start date , 25)<= SYSDATE

WHERE MONTHS_BETWEEN(SYSDATE, start_date)<=25

WHERE MONTHS_BETWEEN (start_date, SYSDATE)<=25