OCA Oracle Database 11g Administrator Certified Associate Set 4

The STATE table has six rows. You issue the following command: ALTER TABLE STATE ADD UPDATE_DT DATE DEFAULT SYSDATE;
Which of the following is correct?


Options are :

  • The DEFAULT value cannot be provided if the table has rows.
  • A new column, UPDATE_DT, is added to STATE and is populated with the current system date and time.
  • A new column, UPDATE_DT, is added to the STATE table, and its contents for the existing rows are NULL.
  • Since the table is not empty, you cannot add a new column

Answer : A new column, UPDATE_DT, is added to STATE and is populated with the current system date and time.

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

The EMPLOYEE table has the following columns:

EMP_ID      NUMBER (4)
EMP_NAME    VARCHAR2 (30)
SALARY      NUMBER (6,2)
DEPT_ID     VARCHAR2 (2)
Which query will show the top five highest-paid employees?


Options are :

  • SELECT EMP_NAME, SALARY FROM (SELECT * FROM EMPLOYEE ORDER BY SALARY DESC) WHERE ROWNUM < 5;
  • SELECT * FROM (SELECT EMP_NAME, SALARY FROM EMPLOYEE ORDER BY SALARY DESC) WHERE ROWNUM <= 5;
  • SELECT EMP_NAME, SALARY (SELECT * FROM EMPLOYEE ORDER BY SALARY DESC) WHERE ROWNUM = 5;
  • SELECT * FROM (SELECT EMP_NAME, SALARY FROM EMPLOYEE ORDER BY SALARY ASC) WHERE ROWNUM <= 5;

Answer : SELECT * FROM (SELECT EMP_NAME, SALARY FROM EMPLOYEE ORDER BY SALARY DESC) WHERE ROWNUM <= 5;

Which of the following check constraints will raise an error?


Options are :

  • CONSTRAINT ck_gender CHECK (gender IN ('M', 'F'))
  • CONSTRAINT ck_old_order CHECK (order_date > (SYSDATE - 30))
  • CONSTRAINT ck_profit CHECK (gross_amt > net_amt)

Answer : CONSTRAINT ck_old_order CHECK (order_date > (SYSDATE - 30))

Which statement successfully adds a new column, ORDER_DATE, to the table ORDERS?


Options are :

  • ALTER TABLE ORDERS NEW COLUMN ORDER_DATE TYPE DATE;
  • ALTER TABLE ORDERS ADD ORDER_DATE (DATE);
  • ALTER TABLE ORDERS ADD ORDER_DATE DATE;
  • ALTER TABLE ORDERS ADD COLUMN ORDER_DATE DATE;

Answer : ALTER TABLE ORDERS ADD ORDER_DATE DATE;

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

What are the special characters allowed in a table name?


Options are :

  • @
  • *
  • #
  • &

Answer : #

The SALARY column of the EMPLOYEE table is defined as NUMBER(8,2), and the COMMISSION_PCT column is defined as NUMBER(2,2). A view is created with the following code:

CREATE VIEW EMP_COMM AS
SELECT LAST_NAME,
SALARY * NVL(COMMISSION_PCT,0) Commission
FROM EMPLOYEES; 

What is the datatype of the COMMISSION column in the view?


Options are :

  • NUMBER (8,2)
  • FLOAT
  • NUMBER (10,2)
  • NUMBER

Answer : NUMBER

Which dictionary view would you query to list only the tables you own?


Options are :

  • ALL_TABLES
  • USR_TABLES
  • USER_TABLES
  • DBA_TABLES

Answer : USER_TABLES

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

Which clause in the SELECT statement is not supported in a view definition subquery?


Options are :

  • CUBE
  • HAVING
  • ORDER BY
  • GROUP BY
  • FOR UPDATE OF

Answer : FOR UPDATE OF

The STATE table has the following constraints (the constraint status is shown in parentheses):

Primary key          pk_state (enabled)
Foreign key          COUNTRY table: fk_state (enabled)
Check constraint     ck_cnt_code (disabled)
Check constraint     ck_st_code (enabled)
Not null constraint  nn_st_name (enabled)

You execute the following SQL:

CREATE TABLE STATE_NEW AS SELECT * FROM STATE;

How many constraints will there be in the new table?


Options are :

  • 3
  • 0
  • 1
  • 5
  • 2

Answer : 1

What is the default length of a CHAR datatype column if no length is specified in the table definition?


Options are :

  • 256
  • You must always specify a length for CHAR columns.
  • 64
  • 1000
  • 1

Answer : 1

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

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions