OCA Oracle Database 11g Administrator Certified Associate Set 3

Jim is trying to add records from the ORDER_DETAILS table to ORDER_DETAIL_HISTORY for orders placed before the current year. Which insert statement would accomplish his task?


Options are :

  • INSERT INTO ORDER_DETAIL_HISTORY FROM ORDER_DETAIL WHERE ORDER_DATE < TRUNC(SYSDATE,'YY');
  • INSERT INTO ORDER_DETAIL_HISTORY VALUES (SELECT * FROM ORDER_DETAIL WHERE ORDER_DATE < TRUNC(SYSDATE,'YY'))
  • INSERT INTO ORDER_DETAIL_HISTORY SELECT * FROM ORDER_DETAIL WHERE ORDER_DATE < TRUNC(SYSDATE,'YY');
  • INSERT FROM ORDER_DETAIL INTO ORDER_DETAIL_HISTORY WHERE ORDER_DATE < TRUNC(SYSDATE,'YY');

Answer : INSERT INTO ORDER_DETAIL_HISTORY SELECT * FROM ORDER_DETAIL WHERE ORDER_DATE < TRUNC(SYSDATE,'YY');

Consider the following code segment. How many rows will be in the CARS table after all these statements are executed?

SELECT COUNT(*) FROM CARS;
COUNT(*)
--------
30
DELETE FROM CARS WHERE MAKE = 'TOYOTA';
2 rows deleted.

SAVEPOINT A;
Savepoint creted.

INSERT INTO CARS VALUES ('TOYOTA','CAMRY',4,220);
1 row created.

SAVEPOINT A;

INSERT INTO CARS VALUES ('TOYOTA','COROLLA',4,180);
1 row created.

ROLLBACK TO SAVEPOINT A;
Rollback complete.


Options are :

  • 29
  • 32
  • 30
  • 28

Answer : 29

Which SQL statements do not give an error? 


Options are :

  • SELECT last_name, e.hire_date, department_id FROM employees e WHERE hire_date = (SELECT max(hire_date) max_hire_date FROM employees ORDER BY 1)
  • SELECT last_name, e.hire_date, department_id FROM employees e JOIN (SELECT max(hire_date) max_hire_date FROM employees ORDER BY 1) me ON (e.hire_date = me.max_hire_date)
  • SELECT last_name, e.hire_date, department_id FROM employees e JOIN (SELECT department_id, max(hire_date) hire_date FROM employees GROUP BY department_id) me USING (hire_date)

Answer : SELECT last_name, e.hire_date, department_id FROM employees e JOIN (SELECT max(hire_date) max_hire_date FROM employees ORDER BY 1) me ON (e.hire_date = me.max_hire_date)

1Z0-497 Oracle Database 12c Essentials Certification Exam Set 1

Which of the following statements will not implicitly begin a transaction?


Options are :

  • SELECT FOR UPDATE
  • INSERT
  • DELETE
  • UPDATE
  • None of the above; they all implicitly begin a transaction, if not started already.

Answer : None of the above; they all implicitly begin a transaction, if not started already.

Consider the following three SQL statements. Choose the most appropriate option.

1. DELETE FROM CITY WHERE CNT_CODE = 1;
2. DELETE CITY WHERE CNT_CODE = 1;
3. DELETE (SELECT * FROM CITY WHERE CNT_CODE = 1);


Options are :

  • Statements 1 and 2 will produce the same result, statement 3 will error out.
  • Statements 1, 2, and 3 will produce different results.
  • Statements 1 and 2 will produce the same result; statement 3 will produce a different result.
  • Statements 1, 2, and 3 will produce the same result.

Answer : Statements 1, 2, and 3 will produce the same result.

Which line of the code has an error?

1 SELECT department_id, count(*)
2 FROM employees
3 GROUP BY department_id
4 HAVING COUNT(department_id) =
5 (SELECT max(count(department_id))
6 FROM employees
7 GROUP BY department_id);



Options are :

  • Line 7
  • Line 3
  • Line 5
  • Line 4
  • No error

Answer : No error

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

Which datatype shows the time-zone information along with the date value?


Options are :

  • TIMESTAMP
  • Both options B and C
  • DATE
  • TIMESTAMP WITH LOCAL TIME ZONE
  • TIMESTAMP WITH TIME ZONE

Answer : TIMESTAMP WITH TIME ZONE

Which of the following INSERT statements will raise an exception?


Options are :

  • INSERT FIRST WHEN DEPT_NO IN (12,14) THEN INTO EMP SELECT * FROM NEW_EMP;
  • INSERT FIRST WHEN DEPT_NO IN (12,14) THEN INSERT INTO EMP SELECT * FROM NEW_EMP;
  • INSERT ALL WHEN DEPT_NO IN (12,14) THEN INTO EMP SELECT * FROM NEW_EMP;
  • INSERT INTO EMP SELECT * FROM NEW_EMP;

Answer : INSERT FIRST WHEN DEPT_NO IN (12,14) THEN INSERT INTO EMP SELECT * FROM NEW_EMP;

You have a large job that will load many thousands of rows into your ORDERS table. To speed up the loading process, you want to temporarily stop enforcing the foreign key constraint FK_ORDERS. Which of the following statements will satisfy your requirement?


Options are :

  • ALTER TABLE ORDERS DISABLE CONSTRAINT FK_ORDERS;
  • ALTER TABLE ORDERS DISABLE FOREIGN KEY FK_ORDERS;
  • ALTER TABLE ORDERS DISABLE ALL CONSTRAINTS;
  • ALTER CONSTRAINT FK_ORDERS DISABLE;

Answer : ALTER TABLE ORDERS DISABLE CONSTRAINT FK_ORDERS;

1Z0-516 Oracle EBS R12 General Ledger Essentials Exam Set 4

The EMPLOYEES table has EMPLOYEE_ID, DEPARTMENT_ID, and FULL_NAME columns. The DEPARTMENTS table has DEPARTMENT_ID and DEPARTMENT_NAME columns. Which two of the following queries return the department ID, name, and employee name, listing department names even if there is no employee assigned to that department?


Options are :

  • SELECT d.department_id, d.department_name, e.full_name FROM departments d LEFT OUTER JOIN employees e USING (d.department_id);
  • SELECT department_id, department_name, full_name FROM departments NATURAL LEFT JOIN employees;
  • SELECT d.department_id, d.department_name, e.full_name FROM departments d NATURAL LEFT OUTER JOIN employees e;

Answer : SELECT department_id, department_name, full_name FROM departments NATURAL LEFT JOIN employees;

How many rows will be counted in the last SQL statement that follows?

SELECT COUNT(*) FROM emp;
120 returned
INSERT INTO emp (emp_id)
VALUES (140);
SAVEPOINT emp140;
INSERT INTO emp (emp_id)
VALUES (141);
INSERT INTO emp (emp_id)
VALUES (142);
INSERT INTO emp (emp_id)
VALUES (143);
TRUNCATE TABLE employees;
INSERT INTO emp (emp_id)
VALUES (144);
ROLLBACK;
SELECT COUNT(*) FROM emp;


Options are :

  • 143
  • 124
  • 0
  • 121

Answer : 124

What is the default precision for fractional seconds in a TIMESTAMP datatype column?


Options are :

  • 0
  • 6
  • 2
  • 9

Answer : 6

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

Consider the following UPDATE statement. Which UPDATE statements from the options will accomplish the same task?

UPDATE ACCOUNTS
SET LAST_UPDATED = SYSDATE,
UPDATE_USER = USER;


Options are :

  • UPDATE ACCOUNTS SET LAST_UPDATED = (SELECT SYSDATE FROM DUAL), UPDATE_USER = (SELECT USER FROM DUAL);
  • UPDATE ACCOUNTS SET LAST_UPDATED = SYSDATE AND UPDATE_USER = USER;
  • UPDATE ACCOUNTS SET (LAST_UPDATED, UPDATE_USER) = (SYSDATE, USER);

Answer : UPDATE ACCOUNTS SET LAST_UPDATED = (SELECT SYSDATE FROM DUAL), UPDATE_USER = (SELECT USER FROM DUAL);

Jim executes the following SQL statement. What will be the result?

DELETE salary, commission_pct
FROM employees
WHERE department_id = 30;



Options are :

  • All the rows belonging to department_id 30 are deleted from the table.
  • The salary and commission_pct columns for all records with department_id 30 are deleted (changed to NULL).
  • The salary and commission_pct columns are deleted from the employees table.
  • The statement will produce an error.

Answer : The statement will produce an error.

Jim noticed that the HIRE_DATE and START_DATE columns in the EMPLOYEES table had date and time values, and hence when he is trying to find employees hired on a certain date, he is not getting the desired result. Which SQL statement will update all the rows in the EMPLOYEES table with no time portion in the HIRE_DATE and START_DATE columns (00:00:00).


Options are :

  • UPDATE EMPLOYEES SET HIRE_DATE = TRUNC(HIRE_DATE) AND START_DATE = TRUNC(START_DATE);
  • UPDATE TABLE EMPLOYEES SET TRUNC(HIRE_DATE) AND TRUNC(START_DATE);
  • FUPDATE HIRE_DATE = TRUNC(HIRE_DATE), START_DATE = TRUNC(START_DATE) IN EMPLOYEES;
  • UPDATE EMPLOYEES SET HIRE_DATE = TRUNC(HIRE_DATE), START_DATE = TRUNC(START_DATE);

Answer : UPDATE EMPLOYEES SET HIRE_DATE = TRUNC(HIRE_DATE), START_DATE = TRUNC(START_DATE);

1Z0-409 Oracle Linux Fundamentals Practice Test Set 6

Which of the following statements will create a primary key for the CITY table with the columns STATE_CD and CITY_CD?


Options are :

  • ALTER TABLE CITY ADD CONSTRAINT PK_CITY PRIMARY KEY (STATE_CD, CITY_CD);
  • CREATE CONSTRAINT PK_CITY PRIMARY KEY ON CITY (STATE_CD, CITY_CD);
  • ALTER TABLE CITY ADD PRIMARY KEY CONSTRAINT PK_CITY ON (STATE_CD, CITY_CD);
  • CREATE PRIMARY KEY ON CITY (STATE_CD, CITY_CD);

Answer : ALTER TABLE CITY ADD CONSTRAINT PK_CITY PRIMARY KEY (STATE_CD, CITY_CD);

1Z0-516 Oracle EBS R12 General Ledger Essentials Exam Set 9

Consider the datatypes DATE, TIMESTAMP (TS), TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ), INTERVAL YEAR TO MONTH (IY2M), and INTERVAL DAY TO SECOND (ID2S). Which operations are not allowed by Oracle Database 11g?


Options are :

  • TSLTZ+IY2M
  • DATE+DATE
  • TSLTZ–DATE
  • ID2S/2

Answer : DATE+DATE

The HIRING table has the following data:
EMPNO           HIREDATE
---------      ----------
1021            12-DEC-00
3400            24-JAN-01
2398            30-JUN-01

What will be result of the following query?
SELECT hiredate+1 FROM hiring WHERE empno = 3400;


Options are :

  • 25-JAN-01
  • N-02
  • 4-FEB-01
  • None of the above

Answer : 25-JAN-01

Consider the following statement:

CREATE TABLE MY_TABLE (
1ST_COLUMN NUMBER,
2ND_COLUMN VARCHAR2 (20));

Which of the following best describes this statement?


Options are :

  • There is no maximum length specified for the first column definition. You must always specify a length for character and numeric columns.
  • Tables cannot be created without a defining a primary key. The table definition here is missing the primary key. >
  • There is no error in the statement
  • The reserved word COLUMN cannot be part of the column name.
  • The column names are invalid.

Answer : The column names are invalid.

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

How do you remove the view USA_STATES from the schema?


Options are :

  • DROP VIEW USA_STATES CASCADE;
  • ALTER VIEW USA_STATES REMOVE;
  • DROP VIEW USA_STATES;
  • DROP USA_STATES;

Answer : DROP VIEW USA_STATES;

Which is a valid status of a constraint created on a view?


Options are :

  • DISABLE VALIDATE
  • DISABLE NOVALIDATE
  • All of the above
  • ENABLE NOVALIDATE

Answer : DISABLE NOVALIDATE

Which line of code has an error?
1 CREATE TABLE FRUITS_VEGETABLES
2 (FRUIT_TYPE VARCHAR2,
3 FRUIT_NAME CHAR (20),
4 QUANTITY NUMBER);


Options are :

  • 2
  • 3
  • 4
  • 1

Answer : 2

OCA Oracle Database 10g Administrator I-1Z0-042 & II-1Z0-043 Set 2

Which statements are used to modify a view definition?


Options are :

  • CREATE FORCE VIEW
  • ALTER VIEW
  • CREATE OR REPLACE VIEW
  • REPLACE VIEW

Answer : CREATE OR REPLACE VIEW

Which statement will remove the column UPDATE_DT from the table STATE?


Options are :

  • ALTER TABLE STATE REMOVE COLUMN UPDATE_DT;
  • ALTER TABLE STATE SET UNUSED COLUMN UPDATE_DT;
  • ALTER TABLE STATE DROP COLUMN UPDATE_DT;
  • You cannot drop a column from the table
  • DROP COLUMN UPDATE_DT FROM STATE;

Answer : ALTER TABLE STATE DROP COLUMN UPDATE_DT;

The EMPLOYEE table has the following columns:

EMP_ID       NUMBER (4)     PRIMARY KEY
EMP_NAME     VARCHAR2 (30)
SALARY       NUMBER (6,2)
DEPT_ID      VARCHAR2 (2)

A view is defined using the following SQL:
CREATE VIEW EMP_IN_DEPT10 AS
SELECT * FROM EMPLOYEE
WHERE DEPT_ID = 'HR';

Which INSERT statement will succeed through the view?


Options are :

  • INSERT INTO EMP_IN_DEPT10 VALUES (1000, 'JOHN',1500,'HR');
  • All of the above
  • INSERT INTO EMP_IN_DEPT10 VALUES (1002, 'BILL',2500,'AC');
  • INSERT INTO EMP_IN_DEPT10 VALUES (1001, NULL,1700,'AM');

Answer : All of the above

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

Which actions are allowed on a table that is marked as read-only?


Options are :

  • Truncating a table
  • Inserting new data
  • Dropping a constraint

Answer : Dropping a constraint

A constraint is created with the DEFERRABLE INITIALLY IMMEDIATE clause. What does this mean?


Options are :

  • Constraint checking is done after each SQL statement is executed, but you can change this behavior by specifying SET CONSTRAINTS ALL DEFERRED.
  • Constraint checking is done only at commit time.
  • The constraint is immediately checked in a DML operation, but subsequent constraint verification is done at commit time.
  • Existing rows in the table are immediately checked for constraint violation.

Answer : Constraint checking is done after each SQL statement is executed, but you can change this behavior by specifying SET CONSTRAINTS ALL DEFERRED.

You create a view based on the EMPLOYEES table using the following SQL.
CREATE VIEW MYVIEW AS SELECT * FROM EMPLOYEES;
You modify the table to add a column named EMP_SSN. What do you need to do to have this new column appear in the view?


Options are :

  • Recompile the view using ALTER VIEW MYVIEW RECOMPILE.
  • Re-create the view using CREATE OR REPLACE VIEW.
  • Nothing. Since the view definition is selecting all columns, the new column will appear in the view automatically.
  • Add the column to the view using ALTER VIEW MYVIEW ADD EMP_SSN.

Answer : Re-create the view using CREATE OR REPLACE VIEW.

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

The following code is used to define a view. The EMP table does not have a primary key or any other constraints.

CREATE VIEW MYVIEW AS
SELECT DISTINCT ENAME, SALARY
FROM EMP
WHERE DEPT_ID = 10;

Which operation is allowed on the view?


Options are :

  • SELECT, UPDATE
  • SELECT, UPDATE, DELETE
  • SELECT, INSERT, DELETE
  • SELECT, INSERT, UPDATE, DELETE
  • SELECT

Answer : SELECT

In a join view, on how many base tables can you perform a DML operation (UPDATE/ INSERT/DELETE) in a single step?


Options are :

  • The number of base tables in the view definition
  • One
  • The number of base tables minus one
  • None

Answer : One

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions