Oracle Functions And Coding Quiz Questions

20 Questions | Total Attempts: 331

SettingsSettingsSettings
Please wait...
Oracle Quizzes & Trivia

.


Questions and Answers
  • 1. 
    Which two are attributes of /SQL*Plus? (Choose two)
    • A. 

      A. /SQL*Plus commands cannot be abbreviated.

    • B. 

      B. /SQL*Plus commands are accesses from a browser.

    • C. 

      C. /SQL*Plus commands are used to manipulate data in tables.

    • D. 

      D. /SQL*Plus commands manipulate table definitions in the database.

    • E. 

      E. /SQL*Plus is the Oracle proprietary interface for executing SQL statements.

  • 2. 
    You need to perform certain data manipulation operations through a view called EMP_DEPT_VU, which you previously created. You want to look at the definition of the view (the SELECT statement on which the view was created.) How do you obtain the definition of the view?
    • A. 

      A. Use the DESCRIBE command in the EMP_DEPT VU view.

    • B. 

      B. Use the DEFINE VIEW command on the EMP_DEPT VU view.

    • C. 

      C. Use the DESCRIBE VIEW command on the EMP_DEPT VU view.

    • D. 

      D. Query the USER_VIEWS data dictionary view to search for the EMP_DEPT_VU view.

  • 3. 
    Evaluate this SQL*Plus command: START delaccount Which task will this command accomplish?
    • A. 

      A. It executes the DELACCOUNT PL/SQL routine.

    • B. 

      B. It runs the DELACCOUNT.SQL script file.

    • C. 

      C. It creates the DELACCOUNT file using the default file extension.

    • D. 

      D. It invokes the editor to edit the contents of the DELACCOUNT file.

  • 4. 
    Evaluate these two SQL statements: SELECT last_name, salary , hire_date FROM EMPLOYEES ORDER BY salary DESC; SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC; What is true about them?  
    • A. 

      A. The two statements produce identical results.

    • B. 

      B. The second statement returns a syntax error.

    • C. 

      C. There is no need to specify DESC because the results are sorted in descending order by default.

    • D. 

      D. The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement.

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

      A. Substitution variables

    • B. 

      B. Replacement variables

    • C. 

      C. Prompt variables

    • D. 

      D. This feature cannot be implemented through /SQL*Plus.

  • 6. 
    Evaluate this SQL statement: SELECT c.customer_id, o.order_id, o.order_date, p.product_name FROM customer c, curr_order o, product p WHERE customer.customer_id = curr_order.customer_id AND o.product_id = p.product_id ORDER BY o.order_amount; This statement fails when executed. Which change will correct the problem?
    • A. 

      A. Include the ORDER_AMOUNT column in the SELECT list.

    • B. 

      B. Use the table name in the ORDER BY clause.

    • C. 

      C. Remove the table aliases from the WHERE clause.

    • D. 

      D. Use the table aliases instead of the table names in the WHERE clause.

  • 7. 
    Examine the description of the STUDENTS table: STD_ID NUMBER(4) COURSE_ID VARCHARD2(10) START_DATE DATE END_DATE DATE. Which two aggregate functions are valid on the START_DATE column? (Choose two)
    • A. 

      A. SUM(start_date)

    • B. 

      B. AVG(start_date)

    • C. 

      C. COUNT(start_date)

    • D. 

      D. AVG(start_date, end_date)

    • E. 

      E. MIN(start_date)

  • 8. 
    Which two tasks can you perform by using the TO_CHAR function?
    • A. 

      A. Convert 10 to ‘TEN’

    • B. 

      B. Convert ‘10’ to 10

    • C. 

      C. Convert ‘10’ to ‘10’

    • D. 

      D. Convert ‘TEN’ to 10

  • 9. 
    Evaluate the SQL statement: SELECT ROUND(45.953, -1), TRUNC(45.936, 2) FROM dual; Which values are displayed?
    • A. 

      A. 46 and 45.93

    • B. 

      B. 50 and 45.93

    • C. 

      C. 50 and 45.9

    • D. 

      D. 45 and 45.93

  • 10. 
    In which case would you use a FULL OUTER JOIN?
    • A. 

      A. Both tables have NULL values.

    • B. 

      B. You want all unmatched data from one table.

    • C. 

      C. You want all matched data from both tables.

    • D. 

      D. You want all unmatched data from both tables.

    • E. 

      E. One of the tables has more data than the other.

  • 11. 
    Which operator can be used in an outer join condition?
    • A. 

      A. NOT

    • B. 

      B. OR

    • C. 

      C. IN

    • D. 

      D. AND

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

      A. Create groups of data.

    • B. 

      B. Sort data in a specific order

    • C. 

      C. Convert data to a different format

    • D. 

      D. Retrieve data based on an unknown condition

  • 13. 
    Which statement explicitly names a constraint?
    • A. 

      A. ALTER TABLE student_grades ADD FOREIGN KEY (student_id) REFERENCES students(student_id);

    • B. 

      B. ALTER TABLE student_grades ADD CONSTRAINT NAME = student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id);

    • C. 

      C. ALTER TABLE student_grades ADD CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id);

    • D. 

      D. ALTER TABLE student grades ADD NAMED CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id);

  • 14. 
    Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCNAR2(25) LAST_NAME VARCNAR2(25) Which statement inserts a row into the table?
    • A. 

      A. INSERT INTO employees( first_name, last_name) VALUES(‘John’,‘Smith’);

    • B. 

      B.INSERT INTO employees(first_name,last_name, employee_id) VALUES ( 1000, ‘John’,‘Smith’);

    • C. 

      C.INSERT INTO employees (employee_id) VALUES (1000);

    • D. 

      INSERT INTO employees VALUES ( NULL, ‘John’,‘Smith’);

  • 15. 
    Which action will cause an automatic rollback?
    • A. 

      A.exiting from iSQL*Plus without first committing the chang

    • B. 

      B.System crash

    • C. 

      C.subsequent DML statement

    • D. 

      D.GRANT statement

  • 16. 
    The user Sue issues this SQL statement: GRANT SELECT ON sue.EMP TO alice WITH GRANT OPTION; The user Alice issues this SQL statement: GRANT SELECT ON sue.EMP TO reena WITH GRANT OPTION; The user Reena issues this SQL statement: GRANT SELECT ON sue.EMP TO timber; The user Sue issues this SQL statement: REVOKE select on sue.EMP FROM alice; For which users does the revoke command revoke SELECT privileges on the SUE.EMP table?
    • A. 

      A. Alice only

    • B. 

      B. Alice and Reena

    • C. 

      C. Alice, Reena, and Timber

    • D. 

      D. Sue, Alice, Reena, and Timber

  • 17. 
    The EMPLOYEES table contains these columns: EMPLOYEE_ID NUMBER(4) LAST_NAME VARCHAR2 (25) JOB_ID VARCHAR2(10) You want to search for strings that contain 'SA_' in the JOB_ID column. Which SQL statement do  you use?
    • A. 

      A. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA\_%' ESCAPE '\';

    • B. 

      B. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_';

    • C. 

      C. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_' ESCAPE "\";

    • D. 

      D. SELECT employee_id, last_name, job_id FROM employees WHERE job_id = '%SA_';

  • 18. 
    Examine this statement: SELECT student_id, gpa FROM student_grades WHERE gpa > &&value; You run the statement once, and when prompted you enter a value of 2.0. A report is produced. What happens when you run the statement a second time?
    • A. 

      A. An error is returned.

    • B. 

      B. You are prompted to enter a new value.

    • C. 

      C. A report is produced that matches the first report produced.

    • D. 

      D. You are asked whether you want a new value or if you want to run the report based on the previous value.

  • 19. 
    Which best describes an inline view?
    • A. 

      A. a schema object

    • B. 

      B. a subquery that can contain an ORDER BY clause

    • C. 

      C. another name for a view that contains group functions

    • D. 

      D. a subquery that is part of the FROM clause of another query

  • 20. 
    The user Alice wants to grant all users query privileges on her DEPT table. Which SQL statement accomplishes this?
    • A. 

      A. GRANT select ON dept TO ALL_USERS;

    • B. 

      B. GRANT select ON dept TO ALL;

    • C. 

      C. GRANT QUERY ON dept TO ALL_USERS

    • D. 

      D. GRANT select ON dept TO PUBLIC;