Multiple Choice Quiz Questions Of Oracle

30 Questions | Total Attempts: 170

SettingsSettingsSettings
Please wait...
Oracle Quizzes & Trivia

.


Questions and Answers
  • 1. 
    Which two statements are true about rebuilding an index? (Choose two.)
    • A. 

      The resulting index may contain deleted entries.

    • B. 

      A new index is built using an existing index as the data source.

    • C. 

      Queries cannot use the existing index while the new index is being built.

    • D. 

      During a rebuild, sufficient space is needed to accommodate both the old and the new index in their respective tablespaces.

  • 2. 
    Consider this SQL statement: UPDATE employees SET first_name = 'John' WHERE emp_id = 1009; COMMIT; What happens when a user issues the COMMIT in the above SQL statement?
    • A. 

      Dirty buffers in the database buffer cache are flushed.

    • B. 

      The server process places the commit record in the redo log buffer.

    • C. 

      Log Writer (LGWR) writes the redo log buffer entries to the redo log files and data files.

    • D. 

      The user process notifies the server process that the transaction is complete

    • E. 

      The user process notifies the server process that the resource locks can be released.

  • 3. 
    Which type of file is part of the Oracle database?
    • A. 

      Control file

    • B. 

      password file

    • C. 

      Parameter files

    • D. 

      Archived log files

  • 4. 
    Examine this TRUNCATE TABLE command: TRUNCATE TABLE departments; Which four are true about the command? (Choose four.)
    • A. 

      All extents are released.

    • B. 

      All rows of the table are deleted.

    • C. 

      Any associated indexes are truncated.

    • D. 

      No undo data is generated for the table's rows.

    • E. 

      It reduces the number of extents allocated to the DEPARTMENTS table to the original setting for MINEXTENTS.

  • 5. 
    Which data dictionary view would you use to get a list of object privileges for all database users?
    • A. 

      DBA_TAB_PRIVS

    • B. 

      ALL_TAB_PRIVS

    • C. 

      USER_TAB_PRIVS

    • D. 

      ALL_TAB_PRIVS_MADE

  • 6. 
    Your developers asked you to create an index on the PROD_ID column of the SALES_HISTORY table, which has 100 million rows. The table has approximately 2 million rows of new data loaded on the first day of every month. For the remainder of the month, the table is only queried. Most reports are generated according to the PROD_ID, which has 96 distinct values. Which type of index would be appropriate?
    • A. 

      Bitmap

    • B. 

      Reverse key

    • C. 

      Unique B-Tree

    • D. 

      Normal B-Tree

  • 7. 
    Which privilege is required to create a database?
    • A. 

      DBA

    • B. 

      SYSDBA

    • C. 

      SYSOPER

    • D. 

      RESOURCE

  • 8. 
    User A issues this command: UPDATE emp SET id=200 WHERE id=1 Then user B issues this command: UPDATE emp SET id=300 WHERE id=1 User B informs you that the UPDATE statement seems to be hung. How can you resolve the problem so user B can continue working?
    • A. 

      No action is required

    • B. 

      Ask user B to abort the statement

    • C. 

      Ask user A to commit the transaction

    • D. 

      Ask user B to commit the transaction

  • 9. 
     The STUDENT_GRADES table has these columns: STUDENT_ID NUMBER(12) SEMESTER_END DATE GPA NUMBER(4,3) Which statement finds students who have a grade point average (GPA) greater than 3.0 for the calendar year 2001? 
    • A. 

      SELECT student_id, gpa FROM student_grades WHERE semester_end BETWEEN '01-JAN-2001' AND '31-DEC-2001' OR gpa > 3.0;

    • B. 

      SELECT student_id, gpa FROM student_grades WHERE semester_end BETWEEN '01-JAN-2001' AND '31-DEC-2001' AND gpa gt 3.0;

    • C. 

      SELECT student_id, gpa FROM student_grades WHERE semester_end BETWEEN '01-JAN-2001' AND '31-DEC-2001' AND gpa > 3.0;

    • D. 

      SELECT student_id, gpa FROM student_grades WHERE semester_end BETWEEN '01-JAN-2001' AND '31-DEC-2001' AND gpa >= 3.0;

    • E. 

      SELECT student_id, gpa FROM student_grades WHERE semester_end > '01-JAN-2001' OR semester_end < '31-DEC-2001' AND gpa >= 3.0;

  • 10. 
    Scott issues the SQL statements: to be done CREATE TABLE dept (deptno number(2) dname VARCHAR2(14) loc VARCHAR2(13)); GRANT SELECT ON DEPT TO SUE; If Sue needs to select from Scott’s DEPT table, which command should she use?
    • A. 

      SELECT * FROM DEPT

    • B. 

      SELECT * FROM SCOTT. DEPT

    • C. 

      SELECT * FROM DBA.SCOTT.DEPT

    • D. 

      SELECT * FROM ALL_USERS WHERE USER_NAME = ‘SCOTT’ AND TABLE NAME= 'DEPT'

  • 11. 
    The CUSTOMERS table has these columns: CUSTOMER_ID  NUMBER(4) NOT NULL CUSTOMER_NAME  VARCHAR2(100) NOT NULL STREET_ADDRESS  VARCHAR2(150) CITY_ADDRESS  VARCHAR2(50)  STATE_ADDRESS  VARCHAR2(50) PROVINCE_ADDRESSVARCHAR2(50) COUNTRY_ADDRESS  VARCHAR2(50) POSTE_CODE  VARCHAR2(12)  CUSTOMER_PHONE  VARCHAR2(20) THE CUSTOMER_ID column is the primary key for the table  which two statements find the number of customer? (Choose two.)
    • A. 

      SELECT TOTAL (*) FROM customers;

    • B. 

      SELECT COUNT (*) FROM customers;

    • C. 

      SELECT TOTAL (customer_id) FROM customer;

    • D. 

      SELECT COUNT(costomer_id) FROM customer;

    • E. 

      SELECT COUNT(customers) FROM customers;

    • F. 

      SELECT TOTAL (customer_name) FROM customers;

  • 12. 
     In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed statement?
    • A. 

      Before the WHERE clause

    • B. 

      Before the FROM clause

    • C. 

      After the ORDER BY clause

    • D. 

      After the WHERE clause

  • 13. 
    Which two are true about aggregate functions? (Choose two)
    • A. 

      You can use aggregate functions in any clause of a SELECT statement.

    • B. 

      You can use aggregate functions only in the column list of the SELECT clause and in the WHERE clause of a SELECT statement.

    • C. 

      You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns

    • D. 

      You can pass column names, expressions, constants, or functions as parameters to an aggregate function.

    • E. 

      You can use aggregate functions on a table, only by grouping the whole table as one single group.

  • 14. 
    . Examine the structure of the EMPLOYEES table: EMPLOYEE_ID      NUMBER primary Key FIRST_NAME         VARCHAR2(25) LAST_NAME           VARCHAR2(25) HIRE_DATE            DATE You issue these statements: CREATE table new_emp (employee_id NUMBER, name VARCHAR2 (30)); INSERT INTO new_emp SELECT employee_id, last_name from employees; Savepoint s2; Delete from new_emp; Rolback to s2; Delete from new_emp where employee_id = 180; UPDATE new_emp set name = ‘James’; Rolback to s2; UPDATE new_emp set name = ‘James’ WHERE employee_id = 180; Rollback; At the end of this transaction, what is true?
    • A. 

      You have no rows in the table.

    • B. 

      You have an employee with the name of James

    • C. 

      You cannot roll back to the same savepoint more than once.

    • D. 

      Your last update fails to update any rows because employee ID 180 was already deleted.

  • 15. 
     Which / SQL* Plus feature can be used to replace values in the where clause?
    • A. 

      Substitution variables

    • B. 

      Replacement variables

    • C. 

      Prompt variables

    • D. 

      Instead-of variables

  • 16. 
    .Evaluate the SQL statement: SELECT ROUND(TRUNC(MOD(1600,10),-1),2) FROM dual; What will be displayed?
    • A. 

      0

    • B. 

      1

    • C. 

      0.00

    • D. 

      An error statement

  • 17. 
    Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME                 VARCHAR2(25) LAST_NAME                  VARCHAR2(25) DEPARTMENT_ID         NUMBER SALARY                          NUMBER
    • A. 

      SELECT a last_name, a salary, a department_id, b.maxsal FROM employees a, (SELECT department_id, max(salary)maxsal FROM employees GROUP BY department_id)b WHERE a department_id = department-id AND a_salary

    • B. 

      SELECT a. last name, a salary, a. department_id FROM employees a WHERE a. department_id IN (SELECT department_id FROM employees b GROUP BY department_id having salary = SELECT max(salary) from employees

    • C. 

      SELECT a last_name, a salary, a.department_id FROM employees a WHERE a salary = SELECT max(salary) FROM employees b WHERE a department_id = department_id);

    • D. 

      SELECT a last_name, a salary, a.department_id FROM employees a WHERE (a department_id, a salary) IN (SELECT department_id, a salary) IN (SELECT department_id max(salary) FROM employees b GROUP BY department_id ORDER BY department_id);

  • 18. 
    Examine the structure of the EMPLOYEES table:         EMPLOYEE_ID NUMBER NOT NULL EMP_ID         VARCHAR2(30) JOB_ID          VARCHAR2(20) DEFAULT ‘SA_REP’ SAL                 NUMBER COMM_PCT  NUMBER MGR_ID         NUMBER DEPARTMENT_ID NUMBER you need to update the records of emloyees 103 and 115. The UPDATE statement you specify should update the rows with the values specified below: JOB_ID Default value specified for this column definition SAL maximum salary earned for the_job ID SA_REP COMM_PCT Default value is specified for the column, the value should be NULL DEPARTMENT_ID: Supplied by the user during run time through substitution variable which UPDATE statement meets the requirements?
    • A. 

      UPDATE employees SET job_id=DEFAULT AND Sal=(SELECT MAX(sal) FROM emoployees WHERE job_id='SA_REP' AND comm_pet=DEFALUT AND department_id =&did WHERE employee_id IN (103, 115),

    • B. 

      UPDATE employees SET job_id = DEFAULT AND Sal = MAX(sal) AND comm_pct = DEFAULT OR NULL AND department _id = & did WHERE employee_id IN (103,115) AND ob_id = 'SA_REP'

    • C. 

      UPDATE employees SET job_id = DEFAULT Sal = (SELECT MAX (sal) FROM employees WHERE job_id = 'SA_REP') comm_pct = DEFAULT, department _id = &did WHERE employee_id IN (103,115)

    • D. 

      UPDATE emplouees SET job_id = DEFAULT sal = MAX (sal) comm_pct = DEFAULT department_id = &did WHERE employee_id IN (103,115) AND job_id = 'SA_REP'

    • E. 

      UPDATE employees SET job_id = DEFAULT Sal = (SELECT MAX(sal) FROM employees WHERE job_id = 'SA_REP') comm_pct = DEFAULT OR NULL, department_id = &did WHEREemployee_id IN (103,115)

  • 19. 
    Which three are DATETIME data types that can be used when specifying column definitions? (Choose three)
    • A. 

      TIMESTAMP

    • B. 

      INTERVAL MONTH TO DAY

    • C. 

      INTERVAL DAY TO SECOND

    • D. 

      INTERVAL YEAR TO MONTH

    • E. 

      TIMESTAMP WITH DATABASE TIMEZONE

  • 20. 
    Examine the structure of the EMPLOYEES table: column name   data type  remarks EMPLOYEE_ID        NUMBER  NOT NULL, primary key LAST_NAME            VARCHAR2(30)  FIRST_NAME           VARCHAR2(30)   JOB_ID                     NUMBER SAL                           NUMBER MGR_ID                    NUMBER  References EMPLOYEE_ID column DEPARTMENT_ID   NUMBER You need to create an index called NAME IDX on the first name and last name fields of the EMPLOYEES table.  Which SQL statement would you use to perform this task?
    • A. 

      CREATE INDEX NAME_IDX (first_name, last_name)

    • B. 

      CREATE INDEX NAME_IDX (first_name AND last_name)

    • C. 

      CREATE INDEX NAME_IDX ON (first_name, last_name)

    • D. 

      CREATE INDEX NAME_IDX ON employees (first_name AND last_name)

    • E. 

      CREATE INDEX NAME_IDX ON employees (first_name, last_name)

  • 21. 
    You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column.  The table is currently empty.  Which statement accomplishes this task?
    • A. 

      ALTER TABLE students ADD PRIMARY KEY student_id;

    • B. 

      ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);

    • C. 

      ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY stuent_id;

    • D. 

      ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

    • E. 

      ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id)

  • 22. 
    Which two statements about views are true? (Choose two)
    • A. 

      A view can be created as read only

    • B. 

      A view can be created as a join on two or more tables.

    • C. 

      A view cannot have an ORDER BY clause in the SELECT statement.

    • D. 

      A view cannot be created with a GROUP BY clause in the SELECT statement.

    • E. 

      A view must have aliases defined for the column names in the SELECT statement.

  • 23. 
    Which is an /SQL * Plus command?
    • A. 

      INSERT

    • B. 

      DELETE

    • C. 

      EXPAND

    • D. 

      DESCRIBE

  • 24. 
    Which SELECT statement should you use to extract the year form the system date and display it in the format “1998”?
    • A. 

      SELECT TO_CHAR(SYSDATE, ‘yyyy’) FROM dual

    • B. 

      SELECT TO_DATE(SYSDATE,’yyyy’) FROM dual

    • C. 

      SELECT DECODE (SUBSTR (SYSDATE, 8), ‘YYYY’) FROM dual

    • D. 

      SELECT TO_CHAR (SUBSTR(SYSDATE, 8,2),’yyyy’) FROM dual

  • 25. 
    A subquery can be used to _________.
    • A. 

      Create groups of data

    • B. 

      Sort data in a specific order

    • C. 

      Convert data to a different format

    • D. 

      Retrieve data based on an unknown condition