Oracle Test 5

50 Questions

Settings
Please wait...
Oracle Quizzes & Trivia

Questions and Answers
  • 1. 
    1.     Evaluate the SQL statement: 1 SELECT a.emp_name, a.sal, a.dept_id, b.maxsal 2 FROM employees a, 3 (SELECT dept_id, MAX(sal) maxsal 4. FROM employees 5 GROUP BY dept_id) b 6 WHERE a.dept_id = b.dept_id 7 AND a.sal < b.maxsal; What is the result of the statement?
    • A. 

      The statement produces an error at line 1.

    • B. 

      The state ment produces an error at line 3.

    • C. 

      The statement produces an error at line 6.

    • D. 

      The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all departments that pay less salary then the maximum salary paid in the company.

    • E. 

      The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department.

  • 2. 
    1.     QUESTION NO: 18 Evaluate the set of SQL statements: CREATE TABLE dept (deptno NUMBER(2), dname VARCNAR2(14), loc VARCNAR2(13)); ROLLBACK; DESCRIBE DEPT What is true about the set?
    • A. 

      The DESCRIBE DEPT statement displays the structure of the DEPT table.

    • B. 

      The ROLLBACK statement frees the storage space occupies by the DEPT table.

    • C. 

      The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist.

    • D. 

      The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement introduced before the ROLLBACK statement.

  • 3. 
    1.     The EMP table contains these columns: LAST NAME VARCHAR2(25) SALARY NUMBER(6,2) DEPARTMENT_ID NUMBER(6) You need to display the employees who have not been assigned to any department. You write the SELECT statement: SELECT LAST_NAME, SALARY, DEPARTMENT_ID FROM EMP WHERE DEPARTMENT_ID = NULL; What is true about this SQL statement?
    • A. 

      The SQL statement displays the desired results.

    • B. 

      The column in the WHERE clause should be changed to display the desired results.

    • C. 

      The operator in the WHERE clause should be changed to display the desired results.

    • D. 

      The WHERE clause should be changed to use an outer join to display the desired results.

  • 4. 
    A MERGE statement can be used to insert new rows into a table. 
    • A. 

      True

    • B. 

      False

  • 5. 
    A role is created  when the number of people using the database is very high
    • A. 

      True

    • B. 

      False

  • 6. 
    A subquery can be used in the GROUP BY clause of a SELECT statement  
    • A. 

      True

    • B. 

      False

  • 7. 
    Before making a tablespace read only, which of the following conditions must be met (Choose all that apply)? 
    • A. 

      The tablespace must contain an active rollback segments

    • B. 

      The tablespace must be online.

    • C. 

      The tablespace must not contain any active rollback segments.

    • D. 

      The tablespace must not be involved in an open backup.

    • E. 

      The tablespace must be involved in an open backup.

  • 8. 
    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

  • 9. 
    Evaluate this SQL statement: SELECT e.employee_id, (.15* e.salary) + (.5 * e.commission_pct) + (s.sales amount * (.35 * e.bonus)) AS CALC_VALUE FROM employees e, sales s WHERE e.employee_id = s.emp_id; What will happen if you remove all the parentheses from the calculation?
    • A. 

      The value displayed in the CALC_VALUE column will be lower.

    • B. 

      The value displayed in the CALC_VALUE column will be higher.

    • C. 

      There will be no difference in the value displayed in the CALC_VALUE column.

    • D. 

      An error will be reported.

  • 10. 
    Examine the description of the EMPLOYEES table: EMP_ID NUMBER(4) NOT NULL LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30) DEPT_ID NUMBER(2) JOB_CAT VARCHAR2(30) SALARY NUMBER(8,2) Which statement shows the department ID, minimum salary, and maximum salary paid in that department, only of the minimum salary is less then 5000 and the maximum salary is more than 15000?
    • A. 

      SELECT dept_id, MIN(salary(, MAX(salary) FROM employees WHERE MIN(salary) < 5000 AND MAX(salary) > 15000;

    • B. 

      SELECT dept_id, MIN(salary), MAX(salary) FROM employees WHERE MIN(salary) < 5000 AND MAX(salary) > 15000 GROUP BY dept_id;

    • C. 

      SELECT dept_id, MIN(salary), MAX(salary) FROM employees HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;

    • D. 

      SELECT dept_id, MIN(salary), MAX(salary) FROM employees GROUP BY dept_id HAVING MIN(salary) < 5000 AND MAX(salary) < 15000;

    • E. 

      SELECT dept_id, MIN(salary), MAX(salary) FROM employees GROUP BY dept_id, salary HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;

  • 11. 
    Examine the description of the MARKS table: STD_ID NUMBER(4) STUDENT_NAME VARCHAR2(30) SUBJ1 NUMBER((33)) SUBJ1 and SUBJ2 indicate the marks obtained by a student in two subjects. Examine this SELECT statement based on the MARKS table: SELECT subj1+subj2 total_marks, std_id FROM marks WHERE subj1 > AVG(subj1) AND subj2 > AVG(subj2) ORDER BY total_marks; What is the result of the SELECT statement?
    • A. 

      The statement executes successfully and returns the student ID and sum of all marks for each student who obtained more than the average mark in each subject

    • B. 

      The statement returns an error at the SELECT clause.

    • C. 

      The statement returns an error at the WHERE clause.

    • D. 

      The statement returns an error at the ORDER BY clause. SUBJ2

  • 12. 
    Examine the structure if the EMPLOYEES and NEW EMPLOYEES tables: EMPLOYEES EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE NEW EMPLOYEES EMPLOYEE_ID NUMBER Primary Key NAME VARCHAR2(60) Which MERGE statement is valid?
    • A. 

      MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||’,’|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||’, ‘||e.last_name);

    • B. 

      MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXIST THEN UPDATE SET c.name = e.first_name ||’,’|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||’, ‘||e.last_name);

    • C. 

      MERGE INTO new employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||’,’|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||’, ‘||e.last_name);

    • D. 

      MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||’,’|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees VALUES(e.employee_id, e.first_name ||’.’||e.last_name);

  • 13. 
    Examine the structure of the EMPLOYEES table: Column name EMPLOYEE_ID LAST_NAME VARCNAR2(30) FIRST_NAME VARCNAR2(30) JOB_ID MGR_ID 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);

    • F. 

      CREATE INDEX NAME_IDX FOR employees(first_name, la st_name);

  • 14. 
    Examine the structure of the STUDENTS table: STUDENT_ID STUDENT_NAME VARCHAR2(30) COURSE_ID MARKS NUMBER NOT NULL, Primary Key VARCHAR2(10) NOT NULL NUMBER START_DATE DATE FINISH_DATE DATE You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999. Which SQL statement accomplishes this task?
    • A. 

      SELECT student_ id, marks, ROWNUM "Rank" FROM students WHERE ROWNUM

    • B. 

      SELECT student_id, marks, ROWID "Rank" FROM students WHERE ROWID

    • C. 

      SELECT student_id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks FROM students WHERE ROWNUM

    • D. 

      SELECT student_id, marks, ROWNUM "Rank: FROM (SELECT student_id, marks FROM students ORDER BY marks) WHERE ROWNUM

  • 15. 
    Explicit conversion is required for conversion and display of date/time data to character form and vice-versa.  
    • A. 

      True

    • B. 

      False

  • 16. 
    From SQL*Plus, you issue this SELECT statement: SELECT * From orders; You use this statement to retrieve data from a data table for __________. (Choose all that apply)
    • A. 

      Updating

    • B. 

      Viewing

    • C. 

      Deleting

    • D. 

      Inserting

    • E. 

      Truncating

  • 17. 
    Group functions on columns include NULL values in calculations if you use the keyword INC_NULLS.
    • A. 

      True

    • B. 

      False

  • 18. 
    If a sequence starting from a value 100 and incremented by 1 is used by more then one application, then all of these applications could have a value of 105 assigned to theircolumn whose value is being generated by the sequence.  
    • A. 

      True

    • B. 

      False

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

      Immediately after the SELECT clause

    • B. 

      . Before the WHERE clause

    • C. 

      Before the FROM clause

    • D. 

      After the ORDER BY clause

    • E. 

      After the WHERE clause

  • 20. 
    In which four clauses can a subquery be used? (Choose four.)
    • A. 

      In the INTO clause of an INSERT statement

    • B. 

      In the FROM clause of a SELECT statement

    • C. 

      In the GROUP BY clause of a SELECT statement

    • D. 

      In the WHERE clause of a SELECT statement

    • E. 

      In the SET clause of an UPDATE statement

    • F. 

      In the VALUES clause of an INSERT statement

  • 21. 
    In which scenario would index be most useful?
    • A. 

      The indexed column is declared as NOT NULL.

    • B. 

      The indexed columns are used in the FROM clause

    • C. 

      The indexed columns are part of an expression

    • D. 

      The indexed column contains a wide range of values.

  • 22. 
    Once created, a sequence belongs to a specific schema
    • A. 

      True

    • B. 

      False

  • 23. 
    QUESTION NO: 41 The ORDERS table has these columns: ORDER_ID CUSTOMER_ID NUMBER(12) ORDER_TOTAL NUMBER(10,2) The ORDERS table tracks the Order number, the order total, and the customer to whom the Order belongs. Which two statements retrieve orders with an inclusive total that ranges between 100.00 and 2000.00 dollars? (Choose two.)
    • A. 

      SELECT customer_id, order_id, order_total FROM orders RANGE ON order_total (100 AND 2000) INCLUSIVE;

    • B. 

      SELECT customer_id, order_id, order_total NUMBER(4) NOT NULL NOT NULL . FROM orders HAVING order_total BETWEEN 100 and 2000;

    • C. 

      SELECT customer_id, order_id, order_total FROM orders WHERE order_total BETWEEN 100 and 2000;

    • D. 

      SELECT customer_id, order_id, order_total FROM orders WHERE order_total >= 100 and

    • E. 

      SELECT customer_id, order_id, order_total FROM orders WHERE order_total >= 100 and order_total

  • 24. 
    Refer to the SQL codes below: SELECT manager_id, last_name, hire_date, salary, AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg FROM employees; What has been achieved? 
    • A. 

      Because of a syntax problem, no row will be returned

    • B. 

      It calculates, for each employee in the employees table, the average salary of the employees reporting to his/her respective manager

    • C. 

      It calculates, for each employee in the employees table, the average salary of the employees reporting to his/her respective manager who were hired just before the employee

    • D. 

      It calculates, for each employee in the employees table, the average salary of the employees reporting to the same manager who were hired in the range just before through just after the employee

    • E. 

      It calculates, for each employee in the employees table, the average salary of the employees reporting to his/her respective manager who were hired just after the employee

  • 25. 
    SCN_TO_TIMESTAMP can be used to find out the latest timing on which a change is made to a particular table.
    • A. 

      True

    • B. 

      False

  • 26. 
    The concatenation operator manipulates character strings and CLOB data.
    • A. 

      True

    • B. 

      False

  • 27. 
    The database administrator of your company created a public synonym called HR for the HUMAN_RESOURCES table of the GENERAL schema, because many users frequently use this table. As a user of the database, you created a table called HR in your schema. What happens when you execute this query? SELECT * FROM HR; 
    • A. 

      You obtain the results retrieved from the public synonym HR created by the database administrator.

    • B. 

      You obtain the results retrieved from the HR table that belongs to your schema.

    • C. 

      You get an error message because you cannot retrieve from a table that has the same name as a public synonym.

    • D. 

      You obtain the results retrieved from both the public synonym HR and the HR table that belongs to your schema, as a Cartesian product.

    • E. 

      You obtain the results retrieved from both the public synonym HR and the HR table that belongs to your schema, as a FULL JOIN.

  • 28. 
    The EMPLOYEES table has these columns: LAST_NAME VARCHAR2(35) SALARY NUMBER(8,2) HIRE_DATE DATE Management wants to add a default value to the SALARY column. You plan to alter the table by using this SQL statement: ALTER TABLE EMPLOYEES MODIFY (SALARY DEFAULT 5000); Which is true about your ALTER statement?
    • A. 

      None

    • B. 

      A change to the DEFAULT value affects only subsequent insertions to the table.

    • C. 

      Column definitions cannot be altered to add DEFAULT values for columns with a NUMBER data type.

    • D. 

      All the rows that have a NULL value for the SALARY column will be updated with the value 5000.

  • 29. 
    The MANAGE TABLESPACE system privilege allows you to perform which of the following operations (Choose all that apply)? 
    • A. 

      Take the tablespace offline

    • B. 

      Begin a backup

    • C. 

      End a backup

    • D. 

      Take the tablespace online

    • E. 

      Make the tablespace read only

    • F. 

      Make the tablespace read write

  • 30. 
    TO_DATE is a character manipulation function
    • A. 

      True

    • B. 

      False

  • 31. 
    What is necessary for your query on an existing view to execute successfully?
    • A. 

      What is necessary for your query on an existing view to execute successfully?

    • B. 

      You need SELECT privileges on the view.

    • C. 

      The underlying tables must be in the same schema.

    • D. 

      You need SELECT privileges only on the underlying tables

  • 32. 
    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

    • E. 

      This feature cannot be implemented through /SQL*Plus.

  • 33. 
    Which four are attributes of single row functions? (Choose four.)
    • A. 

      Cannot be nested

    • B. 

      . manipulate data items

    • C. 

      Act on each row returned

    • D. 

      Return one result per row

    • E. 

      Accept only one argument and return only one value

    • F. 

      Accept arguments which can be a column or an expression

  • 34. 
    Which four are correct guidelines for naming database tables? (Choose four)
    • A. 

      Must begin with either a number or a letter.

    • B. 

      Must be 1-30 characters long.

    • C. 

      Should not be an Oracle Server reserved word.

    • D. 

      Must contain only A-Z, a-z, 0-+, _, *, and #.

    • E. 

      Must contain only A-Z, a-z, 0-9, _, $, and #.

    • F. 

      Must begin with a letter.

  • 35. 
    Which four are valid Oracle constraint types? (Choose four.)
    • A. 

      CASCADE

    • B. 

      UNIQUE

    • C. 

      CHECK

    • D. 

      NON UNIQUE

    • E. 

      PRIMARY KEY

    • F. 

      CONSTANT

    • G. 

      NOT NULL

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

      INSERT

    • B. 

      UPDATE

    • C. 

      SELECT

    • D. 

      DESCRIBE

    • E. 

      DELETE

    • F. 

      RENAME

  • 37. 
    Which of the following are the conditions that must b met before you can use RENAME DATAFILE with the alter tablepace command (Choose all that apply)? 
    • A. 

      . the database must be taken offline before renaming

    • B. 

      The database must be open

    • C. 

      When only a single datafile is to be renamed

    • D. 

      When only a single datafile on the same drive is to be renamed

  • 38. 
    Which operator can be used with a multiple -row subquery?
    • A. 

      =

    • B. 

      LIKE

    • C. 

      BETWEEN

    • D. 

      NOT IN

    • E. 

      IS

    • F. 

  • 39. 
    Which SQL statement generates the alias Annual Salary for the calculated column SALARY*12?
    • A. 

      SELECT ename, salary*12 ‘Annual Salary’ FROM employees;

    • B. 

      SELECT ename, salary*12 “Annual Salary” FROM employees;

    • C. 

      SELECT ename, salary*12 AS Annual Salary FROM employees;

    • D. 

      SELECT ename, salary*12 AS INITCAP(“ANNUAL SALARY”) FROM employees

  • 40. 
    Which statement creates a new user?
    • A. 

      CREATE USER susan;

    • B. 

      CREATE OR REPLACE USER susan;

    • C. 

      CREATE NEW USER susan DEFAULT;

    • D. 

      CREATE USER susan IDENTIFIED BY blue;

    • E. 

      CREATE NEW USER susan IDENTIFIED by blue;

    • F. 

      CREATE OR REPLACE USER susan IDENTIFIED BY blue;

  • 41. 
    Which statement describes the ROWID data type?
    • A. 

      Binary data up to 4 gigabytes.

    • B. 

      Character data up to 4 gigabytes.

    • C. 

      Raw binary data of variable length up to 2 gigabytes.

    • D. 

      Binary data stored in an external file, up to 4 gigabytes.

    • E. 

      A hexadecimal string representing the unique address of a row in its table.

  • 42. 
    Which substitution variable would you use if you want to reuse the variable without prompting the user each time?
    • A. 

      &

    • B. 

      ACCEPT

    • C. 

      PROMPT

    • D. 

      &&

  • 43. 
    Which three statements correctly describe the functions and use of constraints? (Choose three.)
    • A. 

      Constraints provide data independence.

    • B. 

      Constraints make complex queries easy.

    • C. 

      Constraints enforce rules at the view level.

    • D. 

      . Constraints enforce rules at the table level.

    • E. 

      . Constraints prevent the deletion of a table if there are dependencies

    • F. 

      Constraints prevent the deletion of an index if there are dependencies.

  • 44. 
    Which two are attributes of /SQL*Plus? (Choose two)
    • A. 

      /SQL*Plus commands cannot be abbreviated.

    • B. 

      /SQL*Plus commands are accesses from a browser

    • C. 

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

    • D. 

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

    • E. 

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

  • 45. 
    • A. 

      The sort is in ascending by order by default.

    • B. 

      The sort is in descending order by default.

    • C. 

      The ORDER BY clause must precede the WHERE clause

    • D. 

      The ORDER BY clause is executed on the client side.

    • E. 

      The ORDER BY clause comes last in the SELECT statement.

    • F. 

      The ORDER BY clause is executed first in the query execution

  • 46. 
    With 9i SQL Plus, what kind of commands can you enter at the command prompt (Choose all that apply)? 
    • A. 

      PL/SQL blocks

    • B. 

      SQL*Plus commands

    • C. 

      Security commands

    • D. 

      SQL commands

  • 47. 
    You added a PHONE_NUMBER column of NUMBER data type to an existing EMPLOYEES table. The EMPLOYEES table already contains records of 100 employees. Now, you want to enter the phone numbers of each of the 100 employees into the table. Some of the employees may not have a phone number available. Which data manipulation operation do you perform?
    • A. 

      MERGE

    • B. 

      INSERT

    • C. 

      UPDATE

    • D. 

      ADD

    • E. 

      ENTER

    • F. 

      You cannot enter the phone numbers for the existing employee records.

  • 48. 
    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 create.) How do you obtain the definition of the view?
    • A. 

      Use the DESCRIBE command in the EMP_DEPT VU view.

    • B. 

      Use the DEFINE VIEW command on the EMP_DEPT VU view.

    • C. 

      Use the DESCRIBE VIEW command on the EMP_DEPT VU view.

    • D. 

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

    • E. 

      Query the USER_SOURCE data dictionary view to search for the EMP_DEPT_VU view

    • F. 

      Query the USER_OBJECTS data dictionary view to search for the EMP_DEPT_VU view.

  • 49. 
    You would like to display the system date in the format "Monday, 01 June, 2001". Which SELECT statement should you use?
    • A. 

      SELECT TO_DATE(SYSDATE, 'FMDAY, DD Month, YYYY') FROM dual;

    • B. 

      SELECT TO_CHAR(SYSDATE, 'FMDD, DY Month, 'YYY') FROM dual;

    • C. 

      SELECT TO_CHAR(SYSDATE, 'FMDay, DD Month, YYYY') FROM dual;

    • D. 

      SELECT TO_CHAR(SYSDATE, 'FMDY, DDD Month, YYYY') FROM dual;

    • E. 

      SELECT TO_DATE(SYSDATE, 'FMDY, DDD Month, YYYY') FROM dual;

  • 50. 
    BULK COLLECT and the FORALL statements allows to bulk together all of the context switches into a single switch and pass that to the SQL engine. 
    • A. 

      True

    • B. 

      False