Oracle 12c SQL Certified Associate 1Z0-071 Real Exam 2019 Set 3

Which statement is true regarding the USING clause in table joins? (Choose two.)


Options are :

  • It can be used to access data from tables through equijoins as well as nonequijoins.
  • It can be used to join tables that have columns with the same name and compatible data types.
  • It can be used to join a maximum of three tables.
  • It can be used to restrict the number of columns used in a NATURAL join.

Answer :It can be used to join tables that have columns with the same name and compatible data types. It can be used to restrict the number of columns used in a NATURAL join.

1Z0-208 Oracle Comm Billing & Revenue Mgmt Server Developer Set 4

View the Exhibit and examine the structure in the DEPARTMENTS tables. (Choose two.)


Examine this SQL statement:
SELECT department_id "DEPT_ID", department_name, 'b' FROM
departments

WHERE departments_id=90 -

UNION -
SELECT department_id, department_name DEPT_NAME, 'a' FROM
departments

WHERE department_id=10 -
Which two ORDER BY clauses can be used to sort the output?


Options are :

  • ORDER BY DEPT_NAME;
  • ORDER BY 3;
  • ORDER BY 'b';
  • ORDER BY DEPT_ID;

Answer :ORDER BY 3; ORDER BY DEPT_ID;

Which three statements are true regarding single-row functions? (Choose three.)


Options are :

  • The data type returned, can be different from the data type of the argument that is referenced.
  • They can be nested up to only two levels.
  • They can be used in SELECT, WHERE, and ORDER BY clauses.
  • They can return multiple values of more than one data type.
  • They can accept only one argument.
  • They can accept column names, expressions, variable names, or a user-supplied constants as arguments.

Answer :The data type returned, can be different from the data type of the argument that is referenced. They can be used in SELECT, WHERE, and ORDER BY clauses. They can accept column names, expressions, variable names, or a user-supplied constants as arguments.

Examine the create table statements for the stores and sales tables.
SQL> CREATE TABLE stores(store_id NUMBER(4) CONSTRAINT store_id_pk PRIMARY KEY, store_name VARCHAR2(12), store_address VARCHAR2(20), start_date DATE);
SQL> CREATE TABLE sales(sales_id NUMBER(4) CONSTRAINT sales_id_pk PRIMARY KEY, item_id NUMBER(4), quantity NUMBER(10), sales_date DATE, store_id NUMBER(4), CONSTRAINT store_id_fk FOREIGN KEY(store_id) REFERENCES stores(store_id));
You executed the following statement:

SQL> DELETE from stores -
WHERE store_id=900;
The statement fails due to the integrity constraint error:
ORA-02292: integrity constraint (HR.STORE_ID_FK) violated
Which three options ensure that the statement will execute successfully?


Options are :

  • DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table.
  • Create the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option
  • Disable the FOREIGN KEY in SALES table and then delete the rows.
  • Use CASCADE keyword with DELETE statement.
  • Disable the primary key in the STORES table.

Answer :DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table. Create the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option Disable the FOREIGN KEY in SALES table and then delete the rows.

1Z0-208 Oracle Comm Billing & Revenue Mgmt Server Developer Set 4

Examine the types and examples of relationship that follow:
1 One-to-one a) teacher to Student
2 One-to-many b) Employees to Manager
3 Many-to-one c) Person to SSN
4 Many-to-many d) Customers to Products
Which option indicates correctly matched relationships?


Options are :

  • 1-a, 2-b, 3-c, and 4-d
  • 1-c, 2-d, 3-a, and 4-b
  • 1-d, 2-b, 3-a, and 4-c
  • 1-c, 2-a, 3-b, and 4-d

Answer :1-a, 2-b, 3-c, and 4-d

View the Exhibit and examine the structure of the ORDERS table.


Which UPDATE statement is valid?


Options are :

  • UPDATE orders SET order_date = TO_DATE(’12-mar-2007’,’dd-mon-yyyy’), SET order_total = TO_NUMBER (NULL) WHERE order_id = 2455;
  • UPDATE orders SET order_date = ’12-mar-2007’, order_total IS NULL WHERE order_id = 2455;
  • UPDATE orders SET order_date = ’12-mar-2007’, AND order_total = TO_NUMBER(NULL) WHERE order_id = 2455;
  • UPDATE orders SET order_date = ’12-mar-2007’, order_total = NULL WHERE order_id = 2455;

Answer :UPDATE orders SET order_date = ’12-mar-2007’, order_total = NULL WHERE order_id = 2455;

Examine the structure of the MARKS table:


Which two statements would execute successfully? (Choose two.)


Options are :

  • SELECT student_name subject1 FROM marks WHERE subject1 > AVG(subject1);
  • SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1) FROM marks WHERE subject1 > subject2;
  • SELECT student_name,SUM(subject1) FROM marks WHERE student_name LIKE "R%";
  • SELECT SUM(subject1+subject2+subject3) FROM marks WHERE student_name IS NULL;

Answer :SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1) FROM marks WHERE subject1 > subject2; SELECT SUM(subject1+subject2+subject3) FROM marks WHERE student_name IS NULL;

1Z0-520 Oracle EBS R12 Purchasing Essentials Practice Exam Set 5

View the Exhibit and examine the data in the EMPLOYEES table.

Exhibit -


You want to generate a report showing the total compensation paid to each employee to date.
You issue the following query:

What is the outcome?


Options are :

  • It executes successfully but does not give the correct output.
  • IT executes successfully and gives the correct output.
  • It generates an error because the alias is not valid.
  • It generates an error because the usage of the ROUND function in the expression is not valid.
  • It generates an error because the concatenation operator can be used to combine only two items.

Answer :It executes successfully but does not give the correct output.

View the Exhibit and examine the structure of CUSTOMERS table.


Evaluate the following query:

Which statement is true regarding the above query?


Options are :

  • It produces an error because conditions on the CUST_CREDIT_LIMIT column are not valid.
  • It produces an error because the condition on the CUST_FIRST_NAME column is not valid.
  • It produces an error because the condition on the CUST_CITY column is not valid.
  • It executes successfully.

Answer :It executes successfully.

Which statement adds a column called SALARY to the EMPLOYEES table having 100 rows, which cannot contain null?


Options are :

  • ALTER TABLE EMPLOYEES ADD SALARY NUMBER(8,2) DEFAULT NOT NULL;
  • ALTER TABLE EMPLOYEES ADD SALARY NUMBER(8,2) NOT NULL;
  • ALTER TABLE EMPLOYEES ADD SALARY NUMBER(8,2) DEFAULT 0 NOT NULL;
  • ALTER TABLE EMPLOYEES ADD SALARY NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL;

Answer :ALTER TABLE EMPLOYEES ADD SALARY NUMBER(8,2) DEFAULT 0 NOT NULL;

1Z0-330 Oracle Fusion Workforce Compensation Cloud Test Set 2

See the Exhibit and examine the structure of the PROMOTIONS table:


Using the PROMOTIONS table,
you need to find out the average cost for all promos in the range $0-2000 and $2000-5000 in category A.
You issue the following SQL statements:

What would be the outcome?


Options are :

  • It generates an error because multiple conditions cannot be specified for the WHEN clause.
  • It executes successfully and gives the required result.
  • It generates an error because CASE cannot be used with group functions.
  • It generates an error because NULL cannot be specified as a return value.

Answer :It executes successfully and gives the required result.

Which two partitioned table maintenance operations support asynchronous Global Index Maintenance in Oracle database 12c?


Options are :

  • ALTER TABLE SPLIT PARTITION
  • ALTER TABLE ADD PARTITION
  • ALTER TABLE TRUNCATE PARTITION
  • ALTER TABLE DROP PARTITION
  • ALTER TABLE MOVE PARTITION
  • ALTER TABLE MERGE PARTITION

Answer :ALTER TABLE TRUNCATE PARTITION ALTER TABLE DROP PARTITION

Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)


Options are :

  • SELECT TO_CHAR (1890.55, '$99,999D99') FROM DUAL;
  • SELECT TO_CHAR (1890.55, '$0G000D00') FROM DUAL;
  • SELECT TO_CHAR (1890.55, '$99G999D00') FROM DUAL
  • SELECT TO_CHAR (1890.55, '$9,999V99') FROM DUAL;
  • SELECT TO_CHAR (1890.55, '$99G999D99') FROM DUAL

Answer :SELECT TO_CHAR (1890.55, '$0G000D00') FROM DUAL; SELECT TO_CHAR (1890.55, '$99G999D00') FROM DUAL SELECT TO_CHAR (1890.55, '$99G999D99') FROM DUAL

Oracle 12c SQL Certified Associate 1Z0-071 Real Exam 2019 Set 3

Examine the structure of the EMPLOYEES table.


You must display the details of employees who have manager with MANAGER_ID 100, who were hired in the past 6 months and who have salaries greater than
10000.
Which query would retrieve the required result?


Options are :

  • (SELECT last_name, hire_date, salary FROM employees WHERE salary > 10000 UNION ALL SELECT last_name, hire_date, salary FROM employees WHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100)) UNION SELECT last_name, hire_date, salary FROM employees WHERE hire_date > SYSDATE -180;
  • SELECT last_name, hire_date, salary FROM employees WHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = 100) UNION ALL (SELECT last_name, hire_date, salary FROM employees WHERE hire_date > SYSDATE -180 INTERSECT SELECT last_name, hire_date, salary FROM employees WHERE salary > 10000);
  • SELECT last_name, hire_date, salary FROM employees WHERE salary > 10000 UNION ALL SELECT last_name, hire_date, salary FROM employees WHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100) INTERSECT SELECT last_name, hire_date, salary FROM employees WHERE hire_date > SYSDATE- 180;
  • SELECT last_name, hire_date, salary FROM employees WHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = '100') UNION SELECT last_name, hire_date, salary FROM employees WHERE hire_date > SYSDATE -180 INTERSECT SELECT last_name, hire_date, salary FROM employees WHERE salary > 10000;

Answer :SELECT last_name, hire_date, salary FROM employees WHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = '100') UNION SELECT last_name, hire_date, salary FROM employees WHERE hire_date > SYSDATE -180 INTERSECT SELECT last_name, hire_date, salary FROM employees WHERE salary > 10000;

Which two statements are true regarding multiple-row subqueries? (Choose two.)


Options are :

  • They use the < ALL operator to imply less than the maximum.
  • They can contain group functions.
  • They can be used to retrieve multiple rows from a single table only.
  • They always contain a subquery within a subquery.
  • they should not be used with the NOT IN operator in the main query if NULL is likely to be a part of the result of the subquery.

Answer :They can contain group functions. they should not be used with the NOT IN operator in the main query if NULL is likely to be a part of the result of the subquery.

Examine the structure of the MEMBERS table:
Name Null? Type
------------------ --------------- ------------------------------
MEMBER_ID NOT NULL VARCHAR2 (6)
FIRST_NAME VARCHAR2 (50)
LAST_NAME NOT NULL VARCHAR2 (50)
ADDRESS VARCHAR2 (50)
You execute the SQL statement:
SQL > SELECT member_id, ' ' , first_name, ' ' , last_name "ID FIRSTNAME LASTNAME " FROM members;
What is the outcome?


Options are :

  • It fails because the alias name specified after the column names is invalid.
  • It executes successfully and displays the column details in three separate columns and replaces only the last column heading with the alias.
  • It fails because the space specified in single quotation marks after the first two column names is invalid.
  • It executes successfully and displays the column details in a single column with only the alias column heading.

Answer :It executes successfully and displays the column details in three separate columns and replaces only the last column heading with the alias.

OCP Oracle Database 11g Administrator Certified Professional Set 2

Which statement correctly grants a system privilege?


Options are :

  • GRANT CREATE SESSION TO ALL;
  • GRANT CREATE TABLE TO user1, user2;
  • GRANT CREATE VIEW ON table1 TO user1;
  • GRANT ALTER TABLE TO PUBLIC;

Answer :GRANT CREATE TABLE TO user1, user2;

View the exhibit and examine the structure of the STORES table.


You must display the NAME of stores along with the ADDRESS, START_DATE, PROPERTY_PRICE, and the projected property price, which is 115% of property price.
The stores displayed must have START_DATE in the range of 36 months starting from 01-Jan-2000 and above.
Which SQL statement would get the desired output?


Options are :

  • SELECT name, concat (address ',' city ', ', country) AS full_address, start_date, property_price, property_price*115/100 FROM stores WHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;
  • SELECT name, concat (address ',' city ', ', country) AS full_address, start_date, property_price, property_price*115/100 FROM stores WHERE MONTHS_BETWEEN (start_date, '01-JAN-2000') <=36;
  • SELECT name, concat (address ',' city ', ', country) AS full_address, start_date, property_price, property_price*115/100 FROM stores WHERE TO_NUMBER(start_date-TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;
  • SELECT name, address ', ' city ', ' country AS full_address, start_date, property_price, property_price*115/100 FROM stores WHERE MONTHS_BETWEEN(start_date,TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;

Answer :SELECT name, concat (address ',' city ', ', country) AS full_address, start_date, property_price, property_price*115/100 FROM stores WHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;

Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)


Options are :

  • The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.
  • It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.
  • The outer query stops evaluating the result set of the inner query when the first value is found.
  • It is used to test whether the values retrieved by the inner query exist in the result of the outer query.

Answer :It is used to test whether the values retrieved by the outer query exist in the result set of the inner query. The outer query stops evaluating the result set of the inner query when the first value is found.

1Z0-207 Oracle Comm Billing & Revenue Mgmt Pricing Exam Set 1

Which two statements are true about sequences created in a single instance Oracle database? (Choose two.)


Options are :

  • The numbers generated by an explicitly defined sequence can only be used to insert data in one table.
  • When the MAXVALUE limit for a sequence is reached, it can be increased by using the ALTER SEQUENCE statement. > would remove a sequence from the database.
  • When a database instance shuts down abnormally, sequence numbers that have been cached but not used are available again when the instance is restarted.
  • CURRVAL is used to refer to the most recent sequence number that has been generated for a particular sequence.

Answer :When the MAXVALUE limit for a sequence is reached, it can be increased by using the ALTER SEQUENCE statement. > would remove a sequence from the database. CURRVAL is used to refer to the most recent sequence number that has been generated for a particular sequence.

Which statement is true regarding the default behavior of the ORDER BY clause?


Options are :

  • Numeric values are displayed from the maximum to the minimum value if they have decimal positions.
  • NULL values are not considered at all by the sort operation.
  • Only those 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.

Answer :In a character sort, the values are case-sensitive.

View the exhibit and examine the structure of the EMPLOYEES table.


You want to display all employees and their managers having 100 as the MANAGER_ID. You want the output in two columns: the first column would have the of the managers and the second column would have LAST_NAME of the employees.

LAST_NAME -
Which SQL statement would you execute?


Options are :

  • SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees e ON m.employee_id = e.manager_id WHERE m.manager_id = 100;
  • SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees e ON e.employee_id = m.manager_id WHERE m.manager_id = 100;
  • SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees e ON m.employee_id = e.manager_id WHERE e.manager_id = 100;
  • SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees e WHERE m.employee_id = e.manager_id AND e.manager_id = 100

Answer :SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees e ON m.employee_id = e.manager_id WHERE e.manager_id = 100;

1Z0-238 R12 Oracle Install,Patch & Maintain Oracle APP Test Set 5

Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.)


Options are :

  • Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups.
  • If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY
  • Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.
  • You can use column alias in the GROUP BY clause.
  • The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause.

Answer :If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.

View the exhibit for the structure of the STUDENT and FACULTY tables.


You need to display the faculty name followed by the number of students handled by the faculty at the base location.
Examine the following two SQL statements:

Statement 1 -
SQL>SELECT faculty_name, COUNT(student_id)

FROM student JOIN faculty -
USING (faculty_id, location_id)
GROUP BY faculty_name;

Statement 2 -
SQL>SELECT faculty_name, COUNT(student_id)
FROM student NATURAL JOIN faculty
GROUP BY faculty_name;
Which statement is true regarding the outcome?


Options are :

  • Both statements 1 and 2 execute successfully and give the same required result.
  • Both statements 1 and 2 execute successfully and give different results.
  • Only statement 1 executes successfully and gives the required result.
  • Only statement 2 executes successfully and gives the required result.

Answer :Only statement 1 executes successfully and gives the required result.

Evaluate the following SQL statement:
SELECT product_name 'it's not available for order'

FROM product_information -
WHERE product_status = 'obsolete';
You received the following error while executing the above query:

ERROR -
ORA-01756: quoted string not properly terminated
What would you do to execute the query successfully?


Options are :

  • Use the Oracle (q) operator and delimiter to allow the use of a single quotation mark within the literal character string in the SELECT clause
  • Use the escape character to negate the single quotation mark within the literal character string in the SELECT clause
  • Enclose the character literal string in the SELECT clause within double quotation marks
  • Remove the single quotation marks enclosing the character literal string in the SELECT clause

Answer :Use the Oracle (q) operator and delimiter to allow the use of a single quotation mark within the literal character string in the SELECT clause

1Z0-409 Oracle Linux Fundamentals Practice Test Set 2

You execute the following commands:
SQL > DEFINE hiredate = '01-APR-2011'
SQL >SELECT employee_id, first_name, salary

FROM employees -
WHERE hire_date > '&hiredate'
AND manager_id > &mgr_id;
For which substitution variables are you prompted for the input?


Options are :

  • only hiredate'
  • only 'mgr_id'
  • none, because no input required
  • both the substitution variables ''hiredate' and 'mgr_id'.

Answer :only 'mgr_id'

View the Exhibit and examine the structure of CUSTOMERS table.
Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.
Which SQL statement would produce the required result?



Options are :

  • SELECT NVL (TO CHAR(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers;
  • SELECT NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers;
  • SELECT TO_CHAR (NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers;
  • SELECT NVL(cust_credit_limit), 'Not Available') "NEW CREDIT" FROM customers;

Answer :SELECT NVL (TO CHAR(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers;

Which three statements are true about the ALTER TABLE....DROP COLUMN.... command?


Options are :

  • A column can be dropped only if it does not contain any data.
  • A column can be dropped only if another column exists in the table.
  • A parent key column in the table cannot be dropped.
  • A dropped column can be rolled back.
  • The column in a composite PRIMARY KEY with the CASCADE option can be dropped.

Answer :A column can be dropped only if another column exists in the table. A parent key column in the table cannot be dropped. The column in a composite PRIMARY KEY with the CASCADE option can be dropped.

Oracle 1Z0-516 EBS R12 General Essential Practice Exam Set 16

Examine the structure of the MEMBERS table.


Which query can be used to display the last names and city names only for members from the states MO and MI?


Options are :

  • SELECT DISTINCT last_name, city FROM members WHERE state ='MO' OR state ='MI';
  • SELECT last_name, city FROM members WHERE state ='MO' AND state ='MI';
  • SELECT last_name, city FROM members WHERE state LIKE 'M%';
  • SELECT last_name, city FROM members WHERE state IN ('MO', 'MI');

Answer :SELECT last_name, city FROM members WHERE state IN ('MO', 'MI');

Which three statements are correct regarding indexes? (Choose three.)


Options are :

  • Indexes should be created on columns that are frequently referenced as part of any expression.
  • When a table is dropped, corresponding indexes are automatically dropped.
  • For each DML operation performed on a table, the corresponding indexes are automatically updated if required.
  • A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically attempts to create a unique index.

Answer :When a table is dropped, corresponding indexes are automatically dropped. For each DML operation performed on a table, the corresponding indexes are automatically updated if required. A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically attempts to create a unique index.

Which three statements are true about multiple-row subqueries?


Options are :

  • They cannot contain a subquery within a subquery.
  • They can return multiple columns as well as rows.
  • They can contain group functions and GROUP BY and HAVING clauses.
  • They can contain group functions and the GROUP BY clause, but not the HAVING clause.
  • They can contain a subquery within a subquery.
  • They can return only one column but multiple rows.

Answer :They can return multiple columns as well as rows. They can contain group functions and the GROUP BY clause, but not the HAVING clause. They can contain a subquery within a subquery.

1Z0-982 Oracle Enterprise Planning and Budgeting Cloud Test Set 2

Examine the structure of the INVOICE table.


Which two SQL statements would execute successfully?


Options are :

  • SELECT inv_no, NVL2(inv_amt, inv_date, 'Not Available') FROM invoice;
  • SELECT inv_no, NVL2(inv_date, 'Pending', 'Incomplete') FROM invoice;
  • SELECT inv_no, NVL2(inv_amt, inv_amt*.25, 'Not Available') FROM invoice;
  • SELECT inv_no, NVL2(inv_date, sysdate-inv_date, sysdate) FROM invoice;

Answer :SELECT inv_no, NVL2(inv_date, 'Pending', 'Incomplete') FROM invoice; SELECT inv_no, NVL2(inv_date, sysdate-inv_date, sysdate) FROM invoice;

Which statement is true about an inner join specified in the WHERE clause of a query?


Options are :

  • It requires the column names to be the same in all tables used for the join conditions.
  • It is applicable for only equijoin conditions.
  • It must have primary-key and foreign-key constraints defined on the columns used in the join condition.
  • It is applicable for equijoin and nonequijoin conditions.

Answer :It is applicable for equijoin and nonequijoin conditions.

Which statement is true regarding external tables? 


Options are :

  • The default REJECT LIMIT for external tables is UNLIMITED.
  • The data and metadata for an external table are stored outside the database
  • The CREATE TABLE AS SELECT statement can be used to upload data into a normal table in the database from an external table.
  • ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.

Answer :The CREATE TABLE AS SELECT statement can be used to upload data into a normal table in the database from an external table.

1Z0-238 R12 Oracle Install,Patch & Maintain Oracle APP Test Set 4

Examine the command:


What does ON DELETE CASCADE imply?


Options are :

  • When the BOOKS table is dropped, all the rows in the BOOK_TRANSACTIONS table are deleted but the table structure is retained.
  • When a row in the BOOKS table is deleted, the rows in the BOOK_TRANSACTIONS table whose BOOK_ID matches that of the deleted row in the BOOKS table are also deleted.
  • When the BOOKS table is dropped, the BOOK_TRANSACTIONS table is dropped.
  • When a value in the BOOKS.BOOK_ID column is deleted, the corresponding value is updated in the BOOKS_TRANSACTIONS.BOOK_ID column.

Answer :When a row in the BOOKS table is deleted, the rows in the BOOK_TRANSACTIONS table whose BOOK_ID matches that of the deleted row in the BOOKS table are also deleted.

View the exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.


You want to update EMPLOYEES table as follows:
? Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
? Set department_id for these employees to the department_id corresponding to London (location_id 2100).
? Set the employees' salary in location_id 2100 to 1.1 times the average salary of their department.
? Set the employees' commission in location_id 2100 to 1.5 times the average commission of their department.
You issue the following command:

What is outcome?


Options are :

  • It executes successfully and gives the desired update
  • It executes successfully but does not give the desired update
  • It generates an error because a subquery cannot have a join condition in a UPDATE statement.
  • It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an UPDATE statement.

Answer :It executes successfully but does not give the desired update

xamine the business rule:
Each student can work on multiple projects and each project can have multiple students.
You need to design an Entity Relationship Model (ERD) for optimal data storage and allow for generating reports in this format:
STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK
Which two statements are true in this scenario? (Choose two.)


Options are :

  • The ERD must have a 1:M relationship between the STUDENTS and PROJECTS entities.
  • An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the STUDENTS and entities. PROJECTS
  • PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity.
  • STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity.
  • The ERD must have a M:M relationship between the STUDENTS and PROJECTS entities that must be resolved into 1:M relationships.

Answer :An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the STUDENTS and entities. PROJECTS The ERD must have a M:M relationship between the STUDENTS and PROJECTS entities that must be resolved into 1:M relationships.

OCP Oracle Database 11g Administrator Certified Professional Set 1

View the exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables.


Evaluate the following MERGE statement:

MERGE_INTO orders_master o -

USING monthly_orders m -
ON (o.order_id = m.order_id)

WHEN MATCHED THEN -
UPDATE SET o.order_total = m.order_total
DELETE WHERE (m.order_total IS NULL)

WHEN NOT MATCHED THEN -
INSERT VALUES (m.order_id, m.order_total)
What would be the outcome of the above statement?


Options are :

  • The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 4.
  • The ORDERS_MASTER table would contain the ORDER_IDs 1, 2, 3 and 4.
  • The ORDERS_MASTER table would contain the ORDER_IDs 1 and 2.
  • The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 3.

Answer :The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 4.

Examine the structure of the EMPLOYEES table.


There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID.
You want to display the last names and manager IDs of employees who work for the same manager as the employee whose EMPLOYEE_ID is 123.
Which query provides the correct output?


Options are :

  • SELECT e.last_name, m.manager_id FROM employees e RIGHT OUTER JOIN employees m on (e.manager_id = m.employee_id) AND e.employee_id = 123;
  • SELECT m.last_name, e.manager_id FROM employees e LEFT OUTER JOIN employees m on (e.manager_id = m.manager_id) WHERE e.employee_id = 123;
  • SELECT e.last_name, m.manager_id FROM employees e LEFT OUTER JOIN employees m on (e.employee_id = m.manager_id) WHERE e.employee_id = 123;
  • SELECT e.last_name, e.manager_id FROM employees e RIGHT OUTER JOIN employees m on (e.employee_id = m.employee_id) WHERE e.employee_id = 123;

Answer :SELECT m.last_name, e.manager_id FROM employees e LEFT OUTER JOIN employees m on (e.manager_id = m.manager_id) WHERE e.employee_id = 123;

Examine the structure of the BOOKS_TRANSACTIONS table:


You want to display the member IDs, due date, and late fee as $2 for all transactions.
Which SQL statement must you execute?


Options are :

  • SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE FROM BOOKS_TRANSACTIONS;
  • SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE" FROM BOOKS_TRANSACTIONS;
  • SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE FEE" FROM BOOKS_TRANSACTIONS;
  • SELECT member_id 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM BOOKS_TRANSACTIONS;

Answer :SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE FEE" FROM BOOKS_TRANSACTIONS;

1Z0-207 Oracle Comm Billing & Revenue Mgmt Pricing Exam Set 1

View the exhibit and examine the structure of ORDERS and CUSTOMERS tables.


Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is
? Assume there exists only one row with CUST_LAST_NAME as Roberts and CREDIT_LIMIT as 600.
600


Options are :

  • INSERT INTO (SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_total FROM orders o, customers c WHERE o.customer_id = c.customer_id AND c.cust_last_name='Roberts' AND c.credit_limit=600) VALUES (1,'10-mar-2007', 'direct', (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
  • INSERT INTO orders VALUES (1,'10-mar-2007', 'direct', (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
  • INSERT INTO orders (order_id, order_date, order_mode, (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), order_total) VALUES (1,'10-mar-2007', 'direct', &&customer_id, 1000);
  • INSERT INTO orders (order_id, order_date, order_mode, (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), order_total) VALUES (1,'10-mar-2007', 'direct', &customer_id, 1000);

Answer :INSERT INTO orders VALUES (1,'10-mar-2007', 'direct', (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);

Which three statements are true regarding the data types? (Choose three.)


Options are :

  • The value for a CHAR data type column is blank-padded to the maximum defined column width.
  • The BLOB data type column is used to store binary data in an operating system file.
  • The minimum column width that can be specified for a VARCHAR2 data type column is one.
  • Only one LONG column can be used per table.
  • A TIMESTAMP data type column stores only time values with fractional seconds.

Answer :The value for a CHAR data type column is blank-padded to the maximum defined column width. The minimum column width that can be specified for a VARCHAR2 data type column is one. Only one LONG column can be used per table.

You issue the following command to drop the PRODUCTS table:
SQL > DROP TABLE products;
Which three statements are true about the implication of this command? (Choose three.)


Options are :

  • All indexes on the table remain but they are invalidated.
  • A pending transaction in the session is committed.
  • All views and synonyms on the table remain but they are invalidated.
  • All data along with the table structure is deleted.
  • E. All data in the table is deleted but the table structure remains.

Answer :A pending transaction in the session is committed. All views and synonyms on the table remain but they are invalidated. All data along with the table structure is deleted.

Oracle 1Z0-207 Billing & Revenue Mgmt Pricing Practice Exam Set 7

You want to display the date for the first Monday of the next month and issue the following command:


What is the outcome?


Options are :

  • It executes successfully and returns the correct result.
  • In generates an error because rrrr should be replaced by rr in the format string.
  • In generates an error because TO_CHAR should be replaced with TO_DATE.
  • it executes successfully but does not return the correct result.
  • In generates an error because fm and double quotation marks should not be used in the format string.

Answer :It executes successfully and returns the correct result.

Which task can be performed by using a single Data Manipulation Language (DML) statement?


Options are :

  • removing all data only from a single column on which a unique constraint is defined
  • removing all data only from a single column on which a primary key constraint is defined
  • adding a column with a default value while inserting a row into a table
  • adding a column constraint while inserting a row into a table

Answer :removing all data only from a single column on which a unique constraint is defined

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions