Activedumpsnet Logo
Which two statements are true about selecting related rows from two tables based on entity relationship diagram (ERD)?
Relating data from a table with data from the same table is implemented with a self join.
An inner join relates rows within the same table.
Rows from unrelated tables cannot be joined.
Implementing a relationship between two tables might require joining additional tables.
Every relationship between the two tables must be implemented in a Join condition.
Understanding the functionality of SQL joins and their application in a relational database:
In the PROMOTIONS table, the PROMO_ BEGIN_DATE column is of data type and the default date format is DD-MON-RR
Which two statements are true about expressions using PROMO_ BEGIN_DATE in a query?
TONUMBER (PROMO BEGIN_DATE) - 5 will return a number
PROMO_ BEGIN_DATE - 5 will return a date
PROMO_ BEGIN_DATE - SYSDATE will return a number
PROMO_ BEGIN_DATE - SYSDATE will return an error
TODATE(PROMO BEGIN_DATE *5) will return a date
In Oracle SQL, when you subtract a number from a date, the result is a date. When you subtract one date from another, the result is the number of days between the two dates.
The incorrect options are:
References:
You create a table by using this command:
CREATE TABLE rate_list (rate NUMBER(6,2));
Which two are true about executing statements?
INSERT INTO rate_list VALUES (-.9) inserts the value as -.9.
INSERT INTO rate_list VALUES (0.999) produces an error.
INSERT INTO rate_list VALUES (-10) produces an error.
INSERT INTO rate_list VALUES (87654. 556) inserts the value as 87654.6.
INSERT INTO rate_list VALUES (0.551) inserts the value as .55.
INSERT INTO rate_list VALUES (-99.99) inserts the value as 99.99.
The behavior of data insertion into a table with a specific numeric format in Oracle Database 12c can be explained as follows:
References:
Which two are true about granting privilege on objects?
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
C. True, the owner of an object automatically has all object privileges for that object by default. This is an inherent property of the object owner in Oracle.D. True, to allow the creation of foreign key constraints that reference a particular table, the owner of the table must grant the REFERENCES privilege on the key columns to other users or roles.
A, B, and E are not correct because: A. Object privileges can be granted to a role by any user with the appropriate permissions, not only by the owner. B. Object privileges can also be granted by users who have been given the privileges with the WITH GRANT OPTION, not just by the owner. E. The WITH GRANT OPTION is not limited to DBA users; it can be used by any user who has the privilege to grant a specific object privilege.
References:
Examine these statements executed in a single Oracle session:
CREATE TABLE product (pcode NUMBER(2),pname VARCHAR2(20));
INSERT INTO product VALUES(1,'pen');
INSERT INTO product VALUES (2,'pencil');
INSERT INTO product VALUES(3,'fountain pen');
SAVEPOINT a;
UPDATE product SET pcode=10 WHERE pcode =1;
COMMIT;
DELETE FROM product WHERE pcode =2;
SAVEPOINT b;
UPDATE product SET pcode=30 WHERE pcode =3;
SAVEPOINT c;
DELETE FROM product WHERE pcode =10;
ROLLBACK TO SAVEPOINT b;
COMMIT;
Which three statements are true？
The code for pen is 10.
There is no row containing fountain pen.
There is no row containing pencil.
The code for pen is 1.
The code for fountain pen is 3
There is no row containing pen
Which two statements about INVISIBLE indexes are true?
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
INVISIBLE indexes are a feature in Oracle Database 12c and later versions that allow an index to be maintained but not used by the optimizer unless explicitly hinted.
A. False. An INVISIBLE index still consumes storage space as it is maintained in the background.
B. False. There is no such restriction. You can create multiple INVISIBLE indexes on the same column list.
C. False. The optimizer can consider INVISIBLE indexes if they are hinted at in the query.
D. True. You can alter the visibility of an index using the ALTER INDEX command to make an INVISIBLE index VISIBLE.
E. True. Even though they are invisible to the optimizer by default, all DML operations such as INSERT, UPDATE, and DELETE continue to maintain the index as they would with a VISIBLE index.
Which three are true about multiple INSERT statements?
They can be performed only by using a subquery.
They can be performed on relational tables.
They can be performed on views.
They can be performed on remote tables.
They can be performed on external tables using SQL*Loader.
They can insert each computed row into more than one table.
Multiple INSERT statements allow data insertion into one or more tables based on different conditions or datasets:
Which two statements execute successfully?
SELECT TO_ DATE('2019-DEC-25 15:30'， 'YYYY-MON-DD HH24:MI', 'NLS_ DATE_ LANGUAGE
=AMERICAN' ) FROM DUAL;
SELECT TO_CHAR('2019-DEC-25 15:30"， YY-MON-D HH24:M2', 'NLS_DATE LANGUAGE =
AMERICAN')
FROM DUAL;
SELECT TO _DATE (TO_ CHAR (‘2019-DEC-25 03:30’, ‘YYYY-MON-DD HH12:MI’))
FROM DUAL;
SELECT TO _ CHAR (TO_ DATE (‘2019-DEC-25 03:30’,’YYYY-MON-DD HH12:MI’))
FROM DUAL
SELECT TO _ CHAR (‘2019-DEC-25 15:30’.’YYYY-MON-DD HH24:MI’)
FROM DUAL
References for the TO_DATE and TO_CHAR functions and their proper usage can be found in the Oracle Database SQL Language Reference 12c documentation.
Which two statements are true about an Oracle database?
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.
You own table DEPARTMENTS, referenced by views, indexes, and synonyms.
Examine this command which executes successfully:
DROP TABLE departments PURGE;
Which three statements are true?
Neither can it be rolled back nor can the DEPARTMENTS table be recovered.
It will remove all views that are based on the DEPARTMENTS table.
It will delete all rows from the DEPARTMENTS table, but retain the empty table.
It will remove the DE PARTMENTS table from the database.
It will remove all synonyms for the DEPARTMENTS table.
It will drop all indexes on the DEPARTMENTS table.
Which three statements are true about performing Data Manipulation Language (DML) operations on a view In an Oracle Database?
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.
When performing DML operations on a view, certain restrictions apply:
References:
Which two are true about transactions in the Oracle Database?
DDL statements automatically commit only data dictionary updates caused by executing the DDL.
A DDL statement issued by a session with an uncommitted transation automaticall commits that transaction.
An uncommitted transaction is automatically committed when the user exits SQL*PLUS
DML statements always start new transactions.
A session can see uncommitted updates made by the same user in a different session
B. True. DDL (Data Definition Language) statements in Oracle Database are auto-commit statements. This means that if a DDL statement is issued, any uncommitted transactions in the same session will automatically be committed.
D. False. This is a common misconception. DML (Data Manipulation Language) statements do not automatically start new transactions. In Oracle, a transaction begins when the first DML statement (INSERT, UPDATE, DELETE, MERGE, SELECT FOR UPDATE) is encountered after a previous transaction has been committed or rolled back.
A is incorrect because DDL statements automatically commit the entire transaction, not just the data dictionary updates. C is incorrect because an uncommitted transaction is not automatically committed when a user exits SQL*Plus; instead, it is rolled back. E is incorrect because a session cannot see uncommitted updates made by other sessions.
Which statement is true about TRUNCATE and DELETE?
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.
A: True. TRUNCATE is generally faster than DELETE for removing all rows from a table because TRUNCATE is a DDL (Data Definition Language) operation that minimally logs the action and does not generate rollback information. TRUNCATE drops and re-creates the table, which is much quicker than deleting rows one by one as DELETE does, especially for large tables. Also, TRUNCATE does not fire triggers.
References:
Which two statements are true about single row functions?
CONCAT: can be used to combine any number of values
FLOOR: returns the smallest integer greater than or equal to a specified number
CEIL: can be used for positive and negative numbers
TRUNC: can be used with NUMBER and DATE values
MOD: returns the quotient of a division operation
Regarding single row functions in Oracle Database 12c:
Options A, B, and E are incorrect:
The STORES table has a column START_DATE of data type DATE, containing the datethe row was inserted.
You only want to display details of rows where START_DATEis within the last 25 months.which WHERE clause can be used?
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
For filtering rows based on the START_DATE within the last 25 months:
Incorrect options:
Examine the description of the CUSTONERS table:
CUSTNO is the PRIMARY KEY.
You must determine if any customers' details have been entered more than once using a different CUSTNO, by listing all duplicate names.
Which two methods can you use to get the required result?
LEFT OUTER JOIN with self join
PULL OUTER JOIN with self join
subquery
RIGHT OUTER JOIN with self join
self Join
To find duplicate customer details entered under different CUSTNO values, one would typically use methods that compare records within the same table:
References:
Which three queries use valid expressions?
SELECT product_id,(unit_price * 0.15 / (4.75 + 552.25)) FROM products;
SELECT product_id,(expiry_date - delivery_date) * 2 FROM products;
SELECT product_id,unit_price || 5 "Discount" , unit_price + surcharge - discount FROM products;
SELECT product_id, expiry_date * 2 from products;
SELECT product_id,unit_price,5 "Discount", unit_price + surcharge-discount FROM products;
SELECT product_id, unit_price, unit_price + surcharge FROM products;
When evaluating whether expressions in SQL queries are valid, consider data type compatibility and the operations performed:
Which two statements will return the names of the three employees with the lowest salaries?
SELECT last_name, salary
FROM employees
WHERE ROWNUM<=3
SELECT last_name,salary
FROM employees
ORDER BY salary
FETCH FIRST 3 ROWS ONLY;
SELECT last_name,salary
FROM employees
WHERE ROWNUM<=3
ORDER BY (SELECT salary FROM employees);
SELECT last_name,salary
FROM (SELECT * FROM employees ORDER BY salary)
SELECT last_name,salary
FROM employees
FETCH FIRST 3 ROWS ONLY
ORDER BY salary;
To retrieve the names of the three employees with the lowest salaries, the correct SQL syntax and logic are crucial:
Options A, C, D, and E do not correctly implement the logic for fetching the lowest three salaries due to misuse of ROWNUM or incorrect placement of ORDER BY and FETCH clauses.
Which two are true about queries using set operators (UNION, UNION ALL, INTERSECT and MINUS)?
There must be an equal number of columns in each SELECT list.
The name of each column in the first SELECT list must match the name of the corresponding column in each subsequent SELECT list.
Each SELECT statement in the query can have an ORDER BY clause.
None of the set operators can be used when selecting CLOB columns.
The FOR UPDATE clause cannot be specified.
A. True. When using set operators, the number and order of columns must be the same in all SELECT statements involved in the query. The data types of those columns must also be compatible.
E. True. The FOR UPDATE clause cannot be specified in a subquery or a SELECT statement that is combined with another SELECT statement by using a set operator.
B is incorrect because the column names do not need to match; only the number and data types of the columns must be compatible. C is incorrect because only the final SELECT statement in the sequence of UNION/INTERSECT/MINUS operations can have an ORDER BY clause. D is incorrect because, as of Oracle 12c, UNION ALL can be used with CLOB columns, but UNION, INTERSECT, and MINUS cannot.
Which two statements are true about the results of using the INTERSECT operator in compound queries?
Reversing the order of the intersected tables can sometimes affect the output.
Column names in each SELECT in the compound query can be different.
INTERSECT returns rows common to both sides of the compound query.
The number of columns in each SELECT in the compound query can be different.
INTERSECT ignores NULLs
In Oracle Database 12c, the INTERSECT operator is used in compound queries to return only the rows that are common to both SELECT statements involved in the query. Here's a breakdown of why the correct answer is C:
Table EMPLOYEES contains columns including EMPLOYEE_ID, JOB_ID and SALARY.
Only the EMPLOYEES_ID column is indexed.
Rows exist for employees 100 and 200.
Examine this statement:
UPDATE employees
SET (job_id, salary) =
(SELECT job_id, salary
FROM employees
WHERE employee_id = 200)
WHERE employee id=100;
Which two statements are true?
Employees 100 and 200 will have the same SALARY as before the update command.
Employee 100 will have SALARY set to the same value as the SALARY of employee 200.
Employee 100 will have JOB_ID set to the same value as the JOB_ID of employee 200.
Employees 100 and 200 will have the same JOB ID as before the update command.
Employee 200 will have SALARY set to the same value as the SALARY of employee 100.
Employee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100
The SQL UPDATE statement is used to modify existing records in a table. In the given statement, there is a subquery that selects the JOB_ID and SALARY of EMPLOYEE_ID 200 and sets these values to the corresponding columns of EMPLOYEE_ID 100.
References:
Examine the data in the EMPLOYEES table:
Which statement will compute the total annual compensation for each employee?
SELECT last _ NAME (monthly_ salary + monthly _commission _ pct) * 12 AS annual_ comp FROM employees;
select last _ name, (monthly_ salary * 12) + (monthly_ salary * 12 *monthly_ commission_ pct) AS annual_ camp FROM employees
SELECT last _ name, (monthly_ salary * 12) + (monthly_ salary * 12 * NVL (monthly_ commission _pct, 0)) AS annual _comp
SELECT last _ name, (monthly _ salary * 12) + (monthly_ commission _ pct * 12) AS FROM employees:
The correct way to compute the total annual compensation, which includes the monthly salary and the monthly commission (if any), is:
Options A, B, and D are incorrect because:
Which two statements are true regarding the UNION ALL operators?
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 UNION ALL operator in SQL combines the results of two or more SELECT statements into a single result set:
Which three statements are true about inner and outer joins?
A full outer join returns matched and unmatched rows.
A full outer join must use Oracle syntax.
Outer joins can be used when there are multiple join conditions on two tables.
Outer joins can only be used between two tables per query.
An inner join returns matched rows.
A left or right outer join returns only unmatched rows.
A: True. A full outer join does indeed return both matched and unmatched rows from both tables involved in the join. It combines the results of both left and right outer joins.
E: True. An inner join, by definition, returns rows that have matching values in both tables. Rows from both tables that do not match are not returned in an inner join result set.
Inner joins match rows from the joined tables based on the join condition, while outer joins include all rows from one or both tables regardless of whether a matching row exists in the other table.
References:The Oracle SQL documentation explains different types of joins, including inner joins, left and right outer joins, and full outer joins, clarifying how they differ in the result sets they produce.
Which two statements are true about the DUAL table?
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.
A. Incorrect. DUAL consists of a single row and column. B. Incorrect. DUAL is a special one-row, one-column table present by default in all Oracle database installations and can be accessed by any user with SELECT privilege. C. Correct. Any user with the SELECT privilege can select from the DUAL table. It is a dummy table used primarily for selecting a constant, pseudocolumn, or expression not involving any tables. D. Incorrect. DUAL has only one row and one column. E. Correct. The DUAL table has a single VARCHAR2 column called DUMMY, which has a value of 'X'. F. Incorrect. DUAL can be used for a variety of purposes, such as selecting system constants, user environment variables, expressions, and sequences.
This information is detailed in the Oracle Database SQL Language Reference, which describes the properties and uses of the DUAL table.
Examine the description of the CUSTOMERS table:
Which three statements will do an implicit conversion?
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’;
Each answer has been verified with reference to the official Oracle Database 12c SQL documentation, ensuring accuracy and alignment with Oracle's specified functionalities.
Examine the description of the BOOKS table:
The table has 100 rows.
Examine this sequence of statements issued in a new session;
INSERT INTO BOOKS VALUES (‘ADV112’ , ‘Adventures of Tom Sawyer’, NULL, NULL);
SAVEPOINT a;
DELETE from books;
ROLLBACK TO SAVEPOINT a;
ROLLBACK;
Which two statements are true?
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.
B: True. The second ROLLBACK command would not do anything because the first ROLLBACK TO SAVEPOINT a; already undid the delete operation, and there was no other DML operation between the first and the second ROLLBACK.
E: True. The second ROLLBACK command undoes the insert because after the first ROLLBACK TO SAVEPOINT a; there are no savepoints defined, so a subsequent ROLLBACK would undo all transactions to the beginning of the current session or last COMMIT.
The ROLLBACK command is used to undo transactions that have not yet been committed. Rolling back to a savepoint only undoes transactions up to that savepoint, and a subsequent ROLLBACK without a savepoint name will undo all uncommitted changes since the last COMMIT.
References:Oracle SQL documentation on the ROLLBACK statement provides details on how it interacts with savepoints and the effects it has on the transactions within a session.
Evaluate these commands which execute successfully CREATE SEQUENCE ord_seq
INCREMENT BY 1
START WITH 1
MAXVALUE 100000
CYCLE
CACHE 5000;
Create table ord_items(
ord_no number(4) default ord_seq.nextval not null,
Item_no number(3),
Qty number(3),
Expiry_date date,
Constraint it_pk primary key(ord_no,item_no),
Constraint ord_fk foreign key (ord_no) references orders(ord_no));
Which two statements are true about the ORD_ITEMS table and the ORD_SEQ sequence?
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.
Sequences and default values in Oracle play a crucial role in providing unique values for table columns.
Examine the BRICKS table:
You write this query:
SELECT
FROM bricks b1 CROSS JOIN bricks b2
WHERE b1. Weight < b2. Weight:
How many rows will the query return?
1
16
10
6
4
0
Which two are true about the NVL, NVL2, and COALESCE functions?
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. This statement is false. In NVL2, the first expression is returned if it is not null.
B. This statement is false. NVL2 takes exactly three arguments, not any number of expressions.
C. This statement is false. COALESCE stops evaluating its list of expressions as soon as it finds the first non-null value.
D. This is true. COALESCE returns the first non-null expression in its list.
E. This statement is true. NVL requires the first and second expressions to be of the same or compatible data types because it returns the first expression if it is not null, otherwise it returns the second.
F. This statement is false. NVL takes exactly two arguments, not any number.
References:
Which two statements are true about Oracle databases and SQL?
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.
B. True. Oracle databases guarantee read consistency at the transaction level, meaning that each query can only see data that has been committed before the query began executing.
C. True. When an UPDATE statement is executed, Oracle locks each row as it is updated to prevent other transactions from modifying it until the transaction is committed or rolled back.
A is incorrect because updates made by one user cannot be rolled back by another user. D is incorrect because a query can access tables in other schemas if proper permissions are granted. E is incorrect because a user can own only one schema, which has the same name as the user in an Oracle database.
What is true about non-equijoin statement performance?
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.
When comparing the performance of different SQL join types and conditions, Oracle SQL optimizations generally ensure that performance is consistent across different syntactical forms of expressing the same logic:
Which two statements are true about Entity Relationships?
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
A relationship in an Entity-Relationship (ER) model can indeed be mandatory for both entities, meaning that an instance of one entity must relate to one and only one instance of another entity, and vice versa. This is commonly seen in a one-to-one relationship where both sides are mandatory.
Option B is incorrect; a one-to-one relationship does not have to be self-referencing. Self-referencing (or recursive) relationships occur when an entity has a relationship with itself.
Option C is incorrect; a many-to-many relationship typically requires a join table or associative entity with foreign keys that reference the primary keys of the two entities it connects.
Option D is incorrect; in the case of a self-referencing relationship, you may need to use aliases to specify the table more than once to differentiate between the self-referenced columns.
Option E is incorrect because a one-to-many relationship in one direction does not equate to a one-to-one relationship in the opposite direction.
References:
Which two statements are true about substitution variables?
A substitution variable used to prompt for a column name must be endorsed in single quotation marks.
A substitution variable used to prompt for a column name must be endorsed in double quotation marks.
A substitution variable prefixed with & always prompts only once for a value in a session.
A substitution variable can be used with any clause in a SELECT statement.
A substitution variable can be used only in a SELECT statement.
A substitution variable prefixed with 6 prompts only once for a value in a session unless is set to undefined in the session.
Substitution variables in Oracle are used to replace a value dynamically during the execution of SQL statements. The behavior of these variables is well-documented:
References:
Which two statements are true about INTERVAL data types
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.
Regarding INTERVAL data types in Oracle Database 12c:
Options B, C, D, and F are incorrect:
Which two are true about external tables that use the ORACLE _DATAPUMP access driver?
Creating an external table creates a directory object.
When creating an external table, data can be selected only from a table whose rows are stored in database blocks.
When creating an external table, data can be selected from another external table or from a table whose rows are stored in database blocks.
Creating an external table creates a dump file that can be used by an external table in the same or a different database.
Creating an external table creates a dump file that can be used only by an external table in the same database.
External tables using the ORACLE_DATAPUMP access driver have specific characteristics:
C. Creating an external table using the ORACLE_DATAPUMP access driver allows you to select data from other tables, including another external table or a regular table whose rows are stored in database blocks.
A, B, D, and E are incorrect. Specifically:
References:
Examine this statement which executes successfully:
Which statement will violate the CHECK constraint?
UPDATE emp80
SET department_id=90
WHERE department_id=80;
DELETE FROM emp80
WHERE department_id=90;
SELECT *
FROM emp80
WHERE department_id=80；
SELECT *
FROM emp80
WHERE department_id=90;
The CHECK constraint is generally used to limit the range of values that can be placed in a column. If a CHECK constraint exists on the department_id to only allow values such as 80 (assuming from the context provided):
Incorrect options:
Examine these statements:
CREATE TABLE alter_test (c1 VARCHAR2(10), c2 NUMBER(10));
INSERT INTO alter_test VALUES ('123'， 123);
COMMIT;
Which is true ahout modifyIng the columns in AITER_TEST?
c1 can be changed to NUMBER(10) and c2 can be changed to VARCHAN2 (10).
c2 can be changed to NUMBER(5) but c1 cannot be changed to VARCHAN2 (5).
c2 can be changed to VARCHAR2(10) but c1 cannot be changed to NUMBER (10).
c1 can be changed to NUMBER(10) but c2 cannot be changed to VARCHAN2 (10).
c1 can be changed to VARCHAR2(5) and c2 can be changed to NUMBER (12,2).
In Oracle SQL, the ALTER TABLE statement is used to modify the definition of a table after it has been created. Specific changes are allowed depending on the current data type of the column and the data it contains.
A. You cannot change a VARCHAR2 column to NUMBER if the table contains rows with non-numeric values, which is the case with '123' as a VARCHAR2; it is a string, not a number.
B. You can reduce the size of a NUMBER column if the data will still fit within the new precision, but you cannot change a VARCHAR2 column to a smaller size if it contains values that exceed the new size limit.
C. You cannot change a NUMBER column to VARCHAR2 if the table contains rows because the existing data type of NUMBER does not match the new data type of VARCHAR2.
D. Similar to A, you cannot change the data type from VARCHAR2 to NUMBER if non-numeric data is present.
E. You can change a VARCHAR2(10) column to VARCHAR2(5) if all existing rows have values that fit within the new size, and you can change a NUMBER column to have a different scale (NUMBER(12,2)).
References:
Which statement will return a comma-separated list of employee names in alphabetical order for each department in the EMP table?
SELECT deptno,LISTAGG(ename, ' , ') WITHIN GROUP AS employee_list FROM emp GROUP BY deptno;
SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP AS employee_list FROM emp GROUP BY deptno ORDER BY ename;
SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (GROUP BY deptno) AS employee_list FROM emp ORDER BY ename;
SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS employee_list FROM emp GROUP BY deptno;
The LISTAGG function is used in Oracle to aggregate strings from data in a group specified by the GROUP BY clause, producing a single row of concatenated values. The correct syntax also specifies an ORDER BY clause within the WITHIN GROUP parenthesis to sort the values in the concatenated list.
The correct query is:
SELECT deptno, LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS employee_list FROM emp GROUP BY deptno;
This statement will return a comma-separated list of employee names (ename) in alphabetical order for each department (deptno) in the EMP table.
The SALES table has columns PROD_ID and QUANTITY_SOLD of data type NUMBER. Which two queries execute successfully?
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;
When crafting SQL queries, especially those involving aggregate functions and GROUP BY clauses, correct syntax and logical order of SQL clauses are crucial. Here's why A and B are the correct answers:
Which two statements are true about conditional INSERT ALL?
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.
For conditional INSERT ALL in Oracle Database 12c:
Options A, B, and C are incorrect:
Examine these two queries and their output:
SELECT deptno, dname FROM dept;
SELECT ename, job, deptno FROM emp ORDER BY deptno;
Now examine this query:
SELECT ename, dname
FROM emp CROSS JOIN dept WHERE job = 'MANAGER'
AND dept.deptno IN (10, 20) ;
64
6
3
12
In a CROSS JOIN (also known as a Cartesian join), each row from the first table (emp) is joined to every row of the second table (dept). When we apply the filter for job = 'MANAGER' and dept.deptno IN (10, 20), we are restricting the results to managers in departments 10 and 20.
From the given data:
With a cross join, each of these emp records will join with each dept record where deptno is 10 or 20. Since there are two departments (10 and 20) in the dept table, each employee will match with two departments.
Therefore, the result set will be:
That gives us a total of 6 rows, which means the correct answer is B: 6.
Table ORDER_ITEMS contains columns ORDER_ID, UNIT_PRICE and QUANTITY, of data type NUMBER
Statement 1:
SELECT MAX (unit price*quantity) "Maximum Order FROM order items;
Statement 2:
SELECT MAX (unit price*quantity "Maximum order" FROM order items GROUP BY order id;
Which two statements are true?
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.
Analyzing the given SQL statements on the ORDER_ITEMS table:
Incorrect options:
Which three are true about system and object privileges
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
A: True. The WITH GRANT OPTION allows the grantee to grant the object privileges they received to another user or role. This can be used for both system and object privileges, thereby extending the privilege chain.
D: True. When an object privilege granted with the WITH GRANT OPTION is revoked, it also revokes the privileges that the grantee had re-granted to others. This is referred to as a cascading effect, which can impact multiple users and roles depending on the extent of re-granting.
F: True. To add a foreign key constraint that references a table in another schema, the user must have the REFERENCES object privilege on the target table in the other schema. This privilege is specific and necessary for the integrity constraints involving foreign keys.
Which two are true about the USING clause when joining tables?
All column names in a USING clause must be qualified with a table name or table alias.
It can never be used with onatural join.
It is used to specify an equijoin of columns that have the same name in both tables.
It can never be used with a full outer join.
It is used to specify an explicit join condition involving operators.
When joining tables in Oracle Database 12c, the USING clause has specific behaviors:
Options A, B, and E are incorrect:
Which three are true about the CREATE TABLE command?
It can include the CREATE...INDEX statement for creating an index to enforce the primary key constraint.
The owner of the table should have space quota available on the tablespace where the table is defined.
It implicitly executes a commit.
It implicitly rolls back any pending transactions.
A user must have the CREATE ANY TABLE privilege to create tables.
The owner of the table must have the UNLIMITED TABLESPACE system privilege.
A. False - The CREATE TABLE command cannot include a CREATE INDEX statement within it. Indexes to enforce constraints like primary keys are generally created automatically when the constraint is defined, or they must be created separately using the CREATE INDEX command.
B. True - The owner of the table needs to have enough space quota on the tablespace where the table is going to be created, unless they have the UNLIMITED TABLESPACE privilege. This ensures that the database can allocate the necessary space for the table. Reference: Oracle Database SQL Language Reference, 12c Release 1 (12.1).
C. True - The CREATE TABLE command implicitly commits the current transaction before it executes. This behavior ensures that table creation does not interfere with transactional consistency. Reference: Oracle Database SQL Language Reference, 12c Release 1 (12.1).
D. False - It does not implicitly roll back any pending transactions; rather, it commits them.
E. True - A user must have the CREATE ANY TABLE privilege to create tables in any schema other than their own. To create tables in their own schema, they need the CREATE TABLE privilege. Reference: Oracle Database Security Guide, 12c Release 1 (12.1).
F. False - While the UNLIMITED TABLESPACE privilege allows storing data without quota restrictions on any tablespace, it is not a mandatory requirement for a table owner. Owners can create tables as long as they have sufficient quotas on the specific tablespaces.
Examine the description of the PRODUCTS table:
Which three queries use valid expressions?
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;
B. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products; C. SELECT product_id, (expiry_date - delivery_date) * 2 FROM products; E. SELECT product_id, unit_price, unit_price + surcharge FROM products;
Comprehensive and Detailed Explanation WITH all References:
A. This is invalid because "Discount" is a string literal and cannot be used without quotes in an arithmetic operation. Also, there is a typo in unit_pricer, and 'discount' is not a defined column in the table. B. This is valid. It shows a mathematical calculation with unit_price, which is of NUMBER type. Division and multiplication are valid operations on numbers. C. This is valid. The difference between two DATE values results in the number of days between them, and multiplying this value by a number is a valid operation. D. This is invalid because expiry_date is of DATE type and cannot be multiplied by a number. Also, there's a typo: "SPLECT" should be "SELECT". E. This is valid. Both unit_price and surcharge are NUMBER types, and adding them together is a valid operation. F. This is invalid because concatenation operator || is used between a number (unit_price) and a string literal "Discount", which is not enclosed in single quotes, and 'discount' is not a defined column in the table.
In SQL, arithmetic operations on numbers and date arithmetic are valid expressions. Concatenation is also a valid expression when used correctly between string values or literals. Operations that involve date types should not include multiplication or division by numbers directly without a proper interval type in Oracle SQL.
These rules are detailed in the Oracle Database SQL Language Reference, where expressions, datatype precedence, and operations are defined.
Examine the data in the CUST_NAME column of the CUSTOMERS table:
CUST_NAME
---------------------
Renske Ladwig
Jason Mallin
Samuel McCain
Allan MCEwen
Irene Mikkilineni
Julia Nayer
You want to display the CUST_NAME values where the last name starts with Mc or MC.
Which two WHERE clauses give the required result?
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%’
To find customer names where the last name starts with Mc or MC, the correct queries are:
Options B, C, and D are incorrect:
Which two queries execute successfully?
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 CO ALESCE(100, 'A' ) FROM DUAL；
The NULLIF and COALESCE functions in Oracle SQL serve specific purposes:
Examine the data in the INVOICES table:
Examine the data in the CURRENCIES table:
CURRENCY_CODE
-------------
JPY
GPB
CAD
EUR
USD
Which query returns the currencies in CURRENCIES that are not present in INVOICES?
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;
To calculate the length of employment, you would compare the current date to the HIRE_DATE and calculate the difference in years.
A. This is the correct answer. The expression calculates the number of days between the current date (SYSDATE) and the HIRE_DATE, then divides by 365 to convert it to years, and checks if it is greater than 5.
B. SYSTIMESTAMP includes a time component including fractional seconds and time zone information, making this comparison incorrect.
C. There are typos in this option (CUARENT_DATE should be CURRENT_DATE and hire_data should be hire_date). Even with the correct function and column names, CURRENT_DATE returns the current date in the session time zone, not in years.
D. There are typos in this option (SYSNAYW should be SYSDATE). Additionally, dividing by 12 incorrectly assumes that a month is equivalent to a year.
E. This is a repetition of option D with the same issues.
F. There are typos in this option (CUNACV_DATE should be CURRENT_DATE and hire_data should be hire_date). Additionally, dividing by 12 incorrectly assumes that a month is equivalent to a year.
References:
Which three are true about subqueries?
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.
About the roles and behavior of subqueries in SQL:
Incorrect options:
Which two statements are true regarding a SAVEPOINT?
Rolling back to a SAVEPOINT can undo a CREATE INDEX statement.
Only one SAVEPOINT may be issued in a transaction.
A SAVEPOINT does not issue a COMMIT
Rolling back to a SAVEPOINT can undo a TRUNCATE statement.
Rolling back to a SAVEPOINT can undo a DELETE statement
Regarding the use of SAVEPOINT in transactions:
Incorrect options:
Examine the data in the EMP table:
You execute this query:
SELECT deptno AS "Department", AVG(sal) AS AverageSalary, MAX(sal) AS "Max Salary"
FROM emp
WHERE sal >= 12000
GROUP BY "Department "
ORDER BY AverageSalary;
Why does an error occur?
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.
C. True. The error occurs because the alias "Department" used in the GROUP BY clause is enclosed in double quotes, which makes it case-sensitive. However, the column deptno is not originally created with double quotes in the table definition, so you cannot refer to it with a case-sensitive alias in the GROUP BY clause. Oracle interprets "Department" as a string literal, not a column alias, in the GROUP BY clause.
A is incorrect because you can use an alias in an ORDER BY clause. B is incorrect because an alias can contain space characters if it is quoted. D is incorrect because an alias does not always have to be specified in quotes, only when it includes special characters or spaces or if it is case-sensitive.
In which three situations does a new transaction always start?
When issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session
When issuing a CREATE INDEX statement after a CREATE TABLE statement completed unsuccessfully in the same session
When issuing a TRUNCATE statement after a SELECT statement was issued in the same session
When issuing a CREATE TABLE statement after a SELECT statement was issued in the same session
When issuing the first Data Manipulation Language (OML) statement after a COMMIT or ROLLBACK statement was issued in the same session
When issuing a DML statement after a DML statement filed in the same session.
Substitution variables in Oracle are used to replace a value dynamically during the execution of SQL statements. The behavior of these variables is well-documented:
References:
Which three statements are true about GLOBAL TEMPORARY TABLES?
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
A. Incorrect. There is no such restriction in Oracle that a global temporary table cannot have a public synonym. B. Correct. Global temporary tables can have more than one index, just like permanent tables. C. Correct. Global temporary tables can be referenced in the defining query of a view. However, any data selected by the view will be session-specific if it comes from the global temporary table. D. Correct. DML operations on global temporary tables do not generate redo log entries for the data changes; however, undo information for these tables is stored in the undo tablespace and can generate redo entries. E. Incorrect. As stated in B, global temporary tables can have more than one index. F. Correct. Triggers can be created on global temporary tables.
These properties are documented in the Oracle Database SQL Language Reference and Oracle Database Concepts Guide.
Examine these statements and results：
SQL> SELECT COUNT(*) FROM emp
COUNT(*)
---------------------
14
sQL> CREATE GLOBAL TEMPORARY TABLE t emp As SELECT * FROM emp;
Table created
SQL> INSERT INTo temp SELECT * FROM emp;
14 rows created
SQL> COMMIT:
Commit complete*
SQL> INSERT INTo temp SELECT * EROM emp;
14. rows created
SQL> SELECT COUNT(*) FROM t emp
How many rows are retrieved by the last query?
28
0
14
42
Global temporary tables in Oracle Database are designed to hold temporary data for the duration of a session or a transaction.
Given that the global temporary table is created and then populated twice from the emp table without any mention of data deletion, one would initially think the count would be 28, since 14 rows are inserted twice. However, if the temporary table was created with the default ON COMMIT DELETE ROWS option, the rows are deleted at the end of the transaction, which is signified by the COMMIT operation.
Thus, after the first COMMIT, the temporary table would be empty. After the second INSERT, another 14 rows would be added. So, the last query would retrieve 14 rows unless a DELETE operation was committed.
Which three items does a direction of a relationship contain?
an attribute
a cardinality
label
an optionality
a unique identifier
an entity
In data modeling and database design, the direction of a relationship typically includes:
Options A, E, and F are not part of the direction of a relationship:
Examine this Statement which returns the name of each employee and their manager,
SELECT e.last name AS emp,,m.last_name AS mgr
FROM employees e JOIN managers m
ON e.manager_ id = m. employee_ id ORDER BY emp;
You want to extend the query to include employees with no manager. What must you add before JOIN to do this?
CROSS
FULL OUTER
LEFT OUTER
RIGHT OUTER
To include employees with no manager in the query results, a LEFT OUTER JOIN should be used. This type of join returns all records from the left table (employees), and the matched records from the right table (managers). The result is NULL from the right side if there is no match.
Here's the modified query:
SELECT e.last_name AS emp, m.last_name AS mgr FROM employees e LEFT OUTER JOIN managers m ON e.manager_id = m.employee_id ORDER BY emp;
This ensures that even if an employee does not have a manager (i.e., e.manager_id is NULL or there is no corresponding m.employee_id), that employee will still be included in the results.
Examine this query:
SELECT INTERVAL '100' MONTH DURATION FROM DUAL;
What will be the output?
DURATION
+08-04
DUFATION
+100
DURATION
+08
an error
When you use the INTERVAL literal for months, the result is displayed in years and months if the total number of months exceeds 12.
In the query given:
SELECT INTERVAL '100' MONTH DURATION FROM DUAL;
the output will display the interval of 100 months converted to years and the remaining months. 100 months is equal to 8 years and 4 months. Hence, the correct answer is:
DURATION +08-04
The PROD_ID column is the foreign key in the SALES table.Which references the PRODUCTS table.
Similarly,the CUST_ID and TIME_ID columns are Also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_sales(prod_id, I cust_id, order_date DEFAULT SYSDATE)
AS SELECT I prod_id,cust_id,time_id FROM sales.
Which statement is true regarding the above command?
The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.
The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.
The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause I do not match.
The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table
The statement true regarding the CREATE TABLE command:
Incorrect options:
Which two are true about using constraints?
A FOREIGN KEY column in a child table and the referenced PRIMARY KEY column in the parenttable must have the same names.
A table can have multiple PRIMARY KEY and multiple FOREIGN KEY constraints.
A table can have only one PRIMARY KEY and one FOREIGN KEY constraint.
PRIMARY KEY and FOREIGNY constraints can be specified at the column and at the table level
A table can have only one PRIMARY KEY but may have multiple FOREIGN KEY constraints.
NOT NULL can be specified at the column and at the table level.
In Oracle Database 12c, it is important to understand the behavior and properties of constraints.
A. This statement is false. A FOREIGN KEY column in a child table does not need to have the same name as the referenced PRIMARY KEY column in the parent table. What is required is that the data type is the same and that the values in the FOREIGN KEY column correspond to values in the PRIMARY KEY column of the parent table.
B. This statement is false. A table cannot have multiple PRIMARY KEY constraints. By definition, a PRIMARY KEY is a unique identifier for a row in a table, and there can only be one such identifier.
C. This statement is false for the same reasons as B; a table can have only one PRIMARY KEY. However, it can have multiple FOREIGN KEY constraints that reference PRIMARY KEYS in other tables.
D. This is true. PRIMARY KEY and FOREIGN KEY constraints can indeed be specified at the column level with the column definition or at the table level with the ALTER TABLE statement.
E. This is true. A table can have only one PRIMARY KEY constraint because it defines a unique row identifier. However, it can have multiple FOREIGN KEY constraints referencing keys in other tables, allowing for complex relational structures.
F. This statement is false. NOT NULL constraints are always defined at the column level, as they apply to individual columns. They cannot be specified at the table level.
References:
Examine these statements which execute successfully:
ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MON-YYYY HH24 MI: SS’
ALTER SESSION SET TIME_ ZONE = ‘-5:00’;
SELECT DBTIMEZONE, SYSDATE FROM DUAL
Examine the result:
If LOCALTIMESTAMP was selected at the same time what would it return?
11-JUL-2019 6,00,00,00000000 AM – 05:00
11-JUL-2019 11,00,00,00000000 AM
11-JUL-2019 6,00,00,000000 AM
11-JUL-2019 11,00,00,000000AM -05:00
The LOCALTIMESTAMP function in Oracle Database returns the current date and time in the session time zone. Given that the session time zone was set to -5:00 with the ALTER SESSION SET TIME_ZONE command, LOCALTIMESTAMP will return the date and time adjusted to this session time zone.
The SYSDATE function, on the other hand, returns the current date and time from the operating system of the database server, which is not affected by the session time zone setting. In the result shown, SYSDATE returns 11-JUL-2019 11:00:00, which reflects the system time zone, not the session time zone.
The time difference between the DBTIMEZONE (+00:00) and the session time zone (-5:00) is 5 hours. Since SYSDATE shows the time as 11:00:00 in the system time zone, the LOCALTIMESTAMP adjusted to the session time zone would show the time as 06:00:00.
The correct answer is A: 11-JUL-2019 6:00:00.00000000 AM – 05:00. This represents the local timestamp in the session time zone, which is 5 hours behind the DBTIMEZONE.
Please note that the exact format returned by LOCALTIMESTAMP includes the fractional seconds up to 9 decimal places, and the time zone offset is specified as part of the timestamp.
Examine the description of the EMPLOYEES table:
Examine this query:
Which line produces an error?
Line 7
Line 8
Line 3
Line 5
In the provided SQL query, the issue arises from the alias 'a.avg_sal' which is defined in the subquery but is being referenced in the SELECT list of the outer query. This is not permitted in SQL as the scope of the alias defined in the subquery is only within that subquery.
Here is the breakdown of the code and the error:
Therefore, the error occurs at Line 3 where 'a.avg_sal' is not a valid reference in the SELECT list of the main query because it is defined in the subquery.
The rules of scope for aliases in subqueries are specified in the Oracle Database SQL Language Reference 12c documentation. Subquery aliases cannot be referenced outside their subquery.
You want to write a query that prompts for two column names and the WHERE condition each time It is executed in a session but only prompts for the table name the first time it is executed. The variables used in your
query are never undefined in your session . Which query can be used?
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 scenario requires prompting for column names and WHERE condition each time the query is executed, but only prompting for the table name once. This behavior is achievable through the use of substitution variables in SQL*Plus or SQL Developer:
SELECT &col1, &col2 FROM &&table WHERE &condition;
This query uses & for columns and condition, which will prompt every time the query is run. &&table will prompt for the table name the first time and reuse the same value in the same session without re-prompting.
Options B, C, D, and E use incorrect syntax or variable types, leading to various errors or undesired behaviors, such as not maintaining the value of the table name across executions or incorrect usage of single quotes and comparison in SQL.
Examine the description of the ENPLYEES table:
Which two queries return all rows for employees whose salary is greater than the average salary in their department?
SELECT ”
FROM employees
WHERE salary > ANY
SELECT AVG (salary)
EROM employees
GROUP BY department_ id);
SELECT
FROM employees
WHERE salary > AVG (salary) OVER (PARTITION BY department _ id);
SELECT”
FROM employees e1
WHERE salary >!
SELECT AVG (salary)
FROM employees e2
WHERE e1. Department _id = e2, department_ id
SELECT.
FROM
SELECT e.", AVG (salary) OVER (PARTITION BY department id) avg_ sal
FROM employees e
WHERE salary > avg_ sal;
SELECT”
FROM employees
WHERE salary >
( SELECT AVG
(salary) FROM
employees
GROUP BY department _ id
To return all rows for employees whose salary is greater than the average salary in their department, you would use either a subquery or an analytic function:
Options A, D, and E are incorrect because:
Examine this partial query:
SELECT ch.channel_type, t.month, co.country_code, SUM(s.amount_sold) SALES
FROM sales s, times t, channels ch, countries co
WHERE s.time_ id = t.time id
AND s.country_ id = co. country id
AND s. channel id = ch.channel id
AND ch.channel type IN ('Direct Sales', 'Internet')
AND t.month IN ('2000-09', '2000-10')
AND co.country code IN ('GB', 'US')
Examine this output:
Which GROUP BY clause must be added so the query returns the results shown?
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) ;
A. True. The GROUP BY clause needs to include all non-aggregated columns from the SELECT list to provide the correct grouping for the output. The output shown in the image indicates that the data is grouped by channel_type, month, and country_code.
B, C, and D are incorrect because:
The EMPLOYEES table contains columns EMP_ID of data type NUMBER and HIRE_DATE of data type DATE
You want to display the date of the first Monday after the completion of six months since hiring.
The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first day of the week Which query can be used?
SELECT emp_id,NEXT_DAY(ADD_MONTHS(hite_date,6),'MONDAY') FROM employees;
SELECT emp_id,ADD_MONTHS(hire_date,6), NEXT_DAY('MONDAY') FROM employees;
SELECT emp_id,NEXT_DAY(MONTHS_BETWEEN(hire_date,SYSDATE),6) FROM employees;
SELECT emp_id,NEXT_DAY(ADD_MONTHS(hire_date,6),1) FROM employees;
The function ADD_MONTHS(hire_date, 6) adds 6 months to the hire_date. The function NEXT_DAY(date, 'day_name') finds the date of the first specified day_name after the date given. In this case, 'MONDAY' is used to find the date of the first Monday after the hire_date plus 6 months.
Option A is correct as it accurately composes both ADD_MONTHS and NEXT_DAY functions to fulfill the requirement.
Options B, C, and D do not provide a valid use of the NEXT_DAY function, either because of incorrect syntax or incorrect logic in calculating the required date.
The Oracle Database SQL Language Reference for 12c specifies how these date functions should be used.
Which is true about the & and && prefixes with substitution variables?
& 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.
E. True. Both & and && can prefix substitution variables in queries as well as in DML statements. & is used for defining a substitution variable that prompts the user every time the variable is encountered. && is used for a persistent substitution variable that, once defined in the session, will not prompt the user again for a value if used subsequently.
Examine the description of the employees table:
Examine these requirements:
1- Display the last name, date of hire and the number of years of service for each employee.
2. If the employee has been employed 5 or more years but less than 10, display -5+ years of service".
3. If the employee has been employed 10 or more years but less than 15, display "10+ years of
service".
4. If the employee has been employed 15 or more years, display "15-*- years of service".
5. If none of these conditions matches, display "<5 years of service".
6. Sort the results by the hire_date column.
Which statement satisfies all the requirements?
A)
B)
C)
D)
Option A
Option B
Option C
Option D
Option D is the correct SQL statement that satisfies all the requirements mentioned. The CASE statement correctly compares the hire_date to date intervals subtracted from the current date (SYSDATE) to determine the number of years of service. This CASE statement is also appropriately ordered to ensure that the first condition matched is the one returned, preventing overlapping of the conditions.
Here is how Option D works according to the requirements:
In Option D, the intervals are defined in the correct order, ensuring that the first true condition is the one that is used:
The correct syntax for the intervals is SYSDATE - hire_date >= TO_YMINTERVAL('15-0') and similarly for the 10 and 5 years intervals.
Options A, B, and C are incorrect because they have various issues, such as incorrect ordering of the CASE statement's conditions, which could lead to incorrect results due to overlapping intervals, or the use of the TO_YMINTERVAL function that may not properly cover the intended date ranges.
References:
Therefore, Option D is the statement that fulfills all the requirements for displaying the number of years of service based on the employee's hire date and ordered by the hire date.
Which two statements are true about a self join?
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.
A self join is a regular join, but the table is joined with itself. This kind of join can take the form of an inner join, a left outer join, or even a full outer join depending on the requirement.
While indexes can improve the performance of joins by reducing the cost of the lookup operations, they are not a requirement for a self join. A self join can be performed with or without an index on the join key columns.
A self join can indeed be a left outer join. This is useful when you want to include all records from the 'left' side of the join (the table itself), even if the join condition does not find any matching record on the 'right' side (the table itself again).
Examine this data in the EMPLOYERS table：
Which statement will execute successfully?
SELECT dept_id, MAX (Last_name), SUM (salary) FROM employees GROUP BY dept_id
SELECT dept_id, LENGTH (last_name), SUM (salary) FROM employees GROUP BY dept_id
SELECT dept_id, STDDEV (last_name), SUM (salary) FROM employees GROUP BY dept_id
SELECT dept_id, INSTR (last_name,'A'), SUM (salary) FROM employees GROUP BY dept_id
In SQL, the GROUP BY clause is used in conjunction with aggregate functions to group the result-set by one or more columns.
A. This statement will execute successfully. MAX() is an aggregate function that can be used to return the highest value of the selected column, and SUM() is an aggregate function used to sum the values. Both are valid in the SELECT statement with a GROUP BY clause grouping the results by dept_id.
B. This statement will not execute successfully when used with GROUP BY because LENGTH(last_name) is not an aggregate function and it doesn't appear in the GROUP BY clause. All selected columns that are not under an aggregate function must be included in the GROUP BY clause.
C. This statement will not execute successfully because STDDEV() is an aggregate function that calculates the standard deviation of a set of numbers, and it can only be used on numeric data types. last_name is not a numeric column.
D. This statement will not execute successfully for the same reason as B: INSTR(last_name, 'A') is not an aggregate function and must appear in the GROUP BY clause if it's in the SELECT clause.
Viev the Exhibit and examine the structure of the PRODUCT INFORMATION and INVENTORIEStables.
You have a requirement from the supplies department to give a list containing PRODUCT _ID,SUPPLIER ID, and QUANTITY_ON HAND for all the products where in QUANTITY ON HAND is lessthan five.
Which two SQL statements can accomplish the task? (Choose two)
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;
Given the requirement to list PRODUCT_ID, SUPPLIER_ID, and QUANTITY_ON_HAND for products with QUANTITY_ON_HAND less than five:
Incorrect options:
Examine the description or the CUSTOMERS table:
For Customers whose income level has a value, you want to display the first name and due amount as 5% of their credit limit. Customers whose due amount is null should not be displayed.
Which query should 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_AMONT FROM customers
WHERE cust_income_level <> NULL AND due_amount <> NULL;
SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMONT 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_AMONT 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_AMONT FROM customers
WHERE cust_income_level != NULL AND due_amount != NULL;
In Oracle SQL, the correct way to check for non-null values is to use the IS NOT NULL condition. Using != NULL or <> NULL is incorrect because NULL represents the absence of any value, and you cannot use equality or inequality operators to check for NULL.
References:
Examine the description of the PRODUCT_STATUS table:
The STATUS column contains the values 'IN STOCK' or 'OUT OF STOCK' for each row
Which two queries will execute successfully?
SELECT prod_id "CURRENT AVAILABILITY" || q'('s not available)' FROM product_status WHERE status = ’OUT OF STOCK';
SELECT prod_id || q's not available'' FROM product_status WHERE status=’OUT OF STOCK’;
SELECT prod_id || q'('s not available)’ "CURRENT AVAILABILITY" FROM product_status WHERE status = 'OUT OF STOCK';
SELECT prod_id || q'('s not available)' FROM product_status WHERE status = ’OUT OF STOCK’;
SELECT prod_id || q’(’s not available)' 'CURRENT AVAILABILITY' FROM product_status WHERE status = 'OUT OF STOCK';
SELECT prod_id || q"'s not available" FROM product_status WHERE status = 'OUT OF STOCK';
Queries that will execute successfully given the PRODUCT_STATUS table:
Options A, B, E, and F are incorrect because of the incorrect or incomplete use of the quote operator:
You issued this command: DROP TABLE hr. employees;
Which three statements are true?
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.
Regarding the DROP TABLE command:
Incorrect options:
Choose two
Examine the description of the PRODUCT DETALS table:
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.
In Oracle Database 12c, a PRIMARY KEY constraint is a combination of a NOT NULL constraint and a unique constraint. It ensures that the data contained in a column, or a group of columns, is unique among all the rows in the table and not null. Given the PRODUCT_ID is marked as NOT NULL, it is a candidate for being a primary key because we can assume that it is intended to uniquely identify each product in the table.
Which three statements are true?
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.
Note: The other options are incorrect because:
Which three statements are true about single row functions?
They can be used only in the where clause of a select statement.
They can accept only one argument.
They return a single result row per table.
The argument can be a column name, variable, literal or an expression.
They can be nested to any level.
The date type returned can be different from the data type of the argument.
Single-row functions in SQL operate on each row independently and can modify the returned value:
Which two are true about granting privilege on objects?
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.
A. True, the owner of an object in Oracle automatically has all privileges on that object. This means they can perform any operation on the object, including SELECT, INSERT, UPDATE, DELETE, and so forth.C. True, the REFERENCES privilege must be granted explicitly by the table owner to another user or role to allow the grantee to define foreign key constraints that reference the primary or unique key of the owner's table. This is crucial in scenarios involving relational integrity constraints across tables owned by different users.
References:
Which two are true about the WITH GRANT OPTION clause?
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.
The WITH GRANT OPTION clause in Oracle SQL allows the grantee to grant the privilege they have received to another user or role.
References:
In the PROMOTIONS table, the PROMO_BEGTN_DATE column is of data type DATE and the default date format is DD-MON-RR.
Which two statements are true about expressions using PROMO_BEGIN_DATE contained in a query?
TO_NUMBER(PROMO_BEGIN_DATE)-5 will return number
TO_DATE(PROMO_BEGIN_DATE * 5) will return a date
PROMO_BEGIN_DATE-SYSDATE will return a number.
PROMO_BEGIN_DATE-5 will return a date.
PROMO_BEGIN_DATE-SYSDATE will return an error.
A. This statement is incorrect because TO_NUMBER expects a character string as an argument, not a date. Directly converting a date to a number without an intermediate conversion to a character string would result in an error. B. This statement is incorrect. Multiplying a date by a number does not make sense in SQL, and attempting to convert such an expression to a date will also result in an error. C. This statement is correct. Subtracting two dates in Oracle SQL results in the number of days between those dates, hence the result is a number. D. This statement is correct. Subtracting a number from a date in Oracle SQL will subtract that number of days from the date, returning another date. E. This statement is incorrect. As stated in C, subtracting a date from SYSDATE correctly returns the number of days between those two dates, not an error.
These concepts are explained in the Oracle Database SQL Language Reference, which details date arithmetic in SQL.
Which two are true about queries using set operators such as UNION?
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.
Set operators allow you to combine multiple queries into a single query. The key points for each option are:
A. An expression in the first SELECT list does not need to have a column alias; however, the column alias given to an expression in the first SELECT list is used as the column name of the output.
B. This statement is true. When CHAR columns of different lengths are combined with a set operator, Oracle will pad the shorter values with spaces to match the length of the longest value. This behavior is consistent with the SQL standard and Oracle's treatment of the CHAR datatype.
C. Oracle Database performs implicit conversion between compatible data types where necessary when using set operators. For example, a NUMBER can be implicitly converted to a VARCHAR2 and vice versa if the context requires it.
D. This is true. When multiple set operators are used in a single query, the INTERSECT operator takes precedence over the UNION and UNION ALL operators. This order of operation can be overridden by using parentheses.
E. Not all set operators work with all data types. For instance, LOB and LONG RAW data types cannot be used with set operators.
References:
which is true about the round,truncate and mod functions>?
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.
Both ROUND and TRUNC functions can be applied to numbers, and MOD is a function that returns the remainder of a division. The ROUND function rounds a number to a specified number of decimal places, which can be positive, zero, or negative. The TRUNC function truncates a number to a specified number of decimal places.
ROUND(MOD(25,3),-1) rounds the result of MOD(25,3), which is 1, to tens place, which results in 0. TRUNC(MOD(25,3),-1) truncates the result of MOD(25,3), which is 1, to tens place, which also results in 0.
Both are valid, but in this specific case, they give the same result because the remainder (1) when rounded or truncated to tens place (-1) will be 0.
Examine the description of the PRODCTS table which contains data:
Which two are true?
The PROD ID column can be renamed.
The PROD_ ID column data type can be changed to VARCHAR2 (2).
The EXPIRY DATE column data type can be changed to TIME STAMP.
The EXPIRY DATE column cannot be dropped.
The PROD NAME column cannot have a DEFAULT clause added to it.
These statements are verified against the Oracle Database 12c SQL documentation, specifically the sections on data types, the ALTER TABLE command, and the use of literals in SQL expressions.
Examine the description products table:
Examine the description of the new_projects table;
Which two queries execute successfully?
A)
B)
C)
D)
E)
Option A
Option B
Option C
Option D
Option E
To determine which queries will execute successfully, we need to consider the compatibility of the column definitions and the structure of the SELECT statements:
References:
In conclusion, only Option B and Option D will execute successfully because they adhere to the rules of the UNION ALL and UNION operators respectively, regarding column count and data type compatibility.
Examine this SQL statement:
SELECT cust_id, cust_last_name "Last Name
FROM customers
WHERE countryid=10
UNION
SELECT custid CUSTNO, cust_last_name
FROM customers
WHERE countryid=30
Identify three ORDER BY clauses, any one of which can complete the query successfully.
ORDER BY“CUST NO"
ORDER BY 2, cust_id
ORDERBY2, 1
ORDER BY "Last Name"
ORDER BY CUSTNO
Examine this partial statement:
SELECT ename, sal,comm FROM emp
Now examine this output:
WHICH ORDER BY clause will generate the displayed output?
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
The ORDER BY clause is used in a SELECT statement to sort the returned rows by one or more columns.
A. This clause would attempt to order by ename as if it were a numeric column, which is not correct since ename is not numeric.
B. This is the correct answer. The NVL function replaces nulls with the specified value, in this case, 0. This clause sorts by comm, with nulls considered as 0 and appearing first, followed by the actual values, and then sorts by ename.
C. This clause is incorrect because it would place null values at the end, but in the output, rows with null comm appear at the beginning.
D. This clause would sort the comm in descending order with the nulls at the end, which is not consistent with the output shown.
References:
Please note that the correct formatting of SQL statements and clauses is crucial for the successful execution of queries.
Which three are true about dropping columns from a table?
A column can be removed only if it contains no data.
A column drop is implicitly committed
A column that is referenced by another column in any other table cannot be dropped.
A column must be set as unused before it is dropped from a table.
A primary key column cannot be dropped.
Multiple columns can be dropped simultaneously using the ALTER TABLE command.
In Oracle Database 12c, the operations related to dropping columns from a table include several behaviors, each specified clearly in Oracle documentation and best practices:
References:
In Oracle Database 12c, the operations related to dropping columns from a table include several behaviors, each specified clearly in Oracle documentation and best practices:
References:
Examine the description of the EMPLOYEES table:
Which statement will fail?
SELECT department_id, COUNT (*)
FROM employees
HAVING department_ id <> 90 AND COUNT(*) >= 3
GROUP BY department_id;
SELECT department_id, COUNT (*)
FROM employees
WHERE department_ id <> 90 AND COUNT(*) >= 3
GROUP BY department_id;
SELECT department_id, COUNT(*)
FROM employees
WHERE department_id <> 90 HAVING COUNT(*) >= 3
GROUP BY department_id;
SELECT department_id, COUNT(*)
FROM employees
WHERE department_id <> 90 GROUP BY department_id
HAVING COUNT(*) >= 3;
The statement that will fail is B. In Oracle SQL, the WHERE clause cannot contain aggregate functions directly. The HAVING clause is used instead to apply conditions that involve aggregates, but it is applied after the GROUP BY clause.
A. While the HAVING clause is used before the GROUP BY clause which is not standard SQL syntax, Oracle SQL may still execute it successfully due to its flexibility in syntax.
B. This statement will fail because it uses an aggregate function, COUNT(*), in the WHERE clause, which is not allowed. The correct approach is to use the HAVING clause to filter the results of aggregate functions after the GROUP BY clause.
C. This statement is correct; it places the HAVING clause after the GROUP BY clause, applying the filter on the aggregated count.
D. This is a correctly constructed statement with the WHERE clause filtering individual records before grouping, and the HAVING clause filtering groups based on the aggregate function.
References:
Examine this partial command:
Which two clauses are required for this command to execute successfully?
the DEFAULT DIRECTORY clause
the REJECT LIMIT clause
the LOCATION clause
the ACCESS PARAMETERS clause
the access driver TYPE clause
In Oracle Database 12c, when creating an external table using the CREATE TABLE ... ORGANIZATION EXTERNAL statement, there are certain clauses that are mandatory for the command to execute successfully.
For reference, you can find more details in the Oracle Database SQL Language Reference for version 12c, under the CREATE TABLE statement for external tables.
Which two statements are true about Oracle synonyms?
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.
Oracle synonyms are used to simplify the referencing of complex schema objects:
Which two tasks require subqueries?
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.
C: True. To display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE, you would need to use a subquery to first calculate the average PROD_LIST_PRICE and then use that result to compare each product’s list price to the average.
E: True. Displaying products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products and whose status is orderable would require a subquery. The subquery would be used to determine the average PROD_LIST_PRICE, and then this average would be used in the outer query to filter the products accordingly.
Subqueries are necessary when the computation of a value relies on an aggregate or a result that must be obtained separately from the main query, and cannot be derived in a single level of query execution.
References:Oracle's SQL documentation provides guidelines for using subqueries in scenarios where an inner query's result is needed to complete the processing of an outer query.
Which two are true about creating tables in an Oracle database?
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.
Regarding creating tables in an Oracle database:
Incorrect options for all three repeated questions:
These answers and explanations are aligned with Oracle Database 12c SQL documentation and standard practices.
The ORDERS table has a column ORDER_DATE of date type DATE The default display format for a date is DD-MON-RR
Which two WHERE conditions demonstrate the correct usage of conversion functions?
WHERE ordet_date> TO_CHAR(ADD_MONTHS(SYSDATE, 6),'MON DD YYYY')
WHERE TO_CHAR(order_date,'MON DD YYYY') ='JAN 20 2019';
WHERE order_date> TO_DATE('JUL 10 2018','MON DD YYYY');
WHERE order_date IN (TO_DATE ('Oct 21 2018','MON DD YYYY'), TO_CHAR('Nov 21 2018','MON DD YYYY'));
WHERE order_date> TO_DATE(ADD_MONTHS(SYSDATE,6),'MON DD YYYY');
In SQL, the correct usage of conversion functions is crucial when performing operations on dates. Oracle uses the TO_DATE function to convert a string to a date, and the TO_CHAR function to convert dates or numbers to strings.
Which two statements are true regarding non equijoins?
The ON clause can be used.
The USING clause can be used.
The SQL:1999 compliant ANSI join syntax must be used.
Table aliases must be used.
The Oracle join syntax can be used.
Non-equi joins are joins where the join condition is based on something other than equality. In Oracle SQL, you can perform non-equi joins using various clauses:
References:
The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2.
The table has two rows whose COST_LAST_MANE values are Anderson and Ausson.
Which query produces output for CUST_LAST_SAME containing Oder for the first row and Aus for the second?
SELECT REPLACE (REPLACE(cust_last_name,'son',''),'An','O') FROM customers;
SELECT REPLACE (TRIM(TRALING'son' FROM cust_last_name),'An','O') FROM customers;
SELECT INITCAP (REPLACE(TRIM('son' FROM cust_last_name),'An','O')) FROM customers;
SELECT REPLACE (SUBSTR(cust_last_name,-3),'An','O') FROM customers;
The REPLACE function in Oracle SQL is used to replace occurrences of a specified substring with another substring. In this query, the inner REPLACE function call REPLACE(cust_last_name, 'son', '') removes the substring 'son' from cust_last_name. The outer REPLACE function call then replaces the substring 'An' with 'O'. For the given data, 'Anderson' would first be transformed to 'Ander' by the inner REPLACE, and then 'Ander' would be transformed to 'Oder' by the outer REPLACE. Similarly, 'Ausson' would first change to 'Aus' by the inner REPLACE, which is unaffected by the outer REPLACE.
Reference can be found in the Oracle Database SQL Language Reference documentation, which details the functionality of string functions, including REPLACE.
Examine the data in the EMPLOYEES table:
Which statement will compute the total annual compensation for each employee?
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;
To calculate the total annual compensation, you need to sum the annual salary with the annual commission. The annual commission is the monthly commission percentage times the monthly salary times 12 (months). The NVL function is used to replace NULL with 0 for commission percentage when calculating the commission.