Oracle Test 5

50 Questions | Total Attempts: 150

SettingsSettingsSettings
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