Oracle Mock 3

34 Questions | Total Attempts: 211

SettingsSettingsSettings
Please wait...
Oracle Mock 3

Questions and Answers
  • 1. 
    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.

  • 2. 
    You want to display the titles of books that meet these criteria: 1. Purchased before January 21, 2001 2. Price is less then $500 or greater than $900 You want to sort the results by their data of purchase, starting with the most recently bought book. Which statement should you use?
    • A. 

      SELECT book_title FROM books WHERE price between 500 and 900 AND purchase_date < ’21-JAN-2001’ ORDER BY purchase_date;

    • B. 

      SELECT book_title FROM books WHERE price IN (500,900) AND purchase_date < ’21-JAN-2001’ ORDER BY purchase date ASC;

    • C. 

      SELECT book_title FROM books WHERE price < 500 or > 900 AND purchase_date < ’21-JAN-2001’ ORDER BY purchase date DESC;

    • D. 

      SELECT book_title FROM books WHERE (price < 500 OR price > 900) AND purchase_date < ’21-JAN-2001’ ORDER BY purchase date DESC;

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

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

    • B. 

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

    • C. 

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

    • D. 

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

    • E. 

      ALTER TABLE student grades ADD NAME student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id);

  • 4. 
    You created a view called EMP_DEPT_VU that contains three columns from the EMPLOYEES and DEPARTMENTS tables: EMPLOYEE_ID, EMPLOYEE_NAME AND DEPARTMENT_NAME. The DEPARTMENT_ID column of the EMPLOYEES table is the foreign key to the primary key DEPARTMENT_ID column of the DEPARTMENTS table. You want to modify the view by adding a fourth column, MANAGER_ID of NUMBER data type from the EMPLOYEES tables. How can you accomplish this task?
    • A. 

      ALTER VIEW emp_dept_vu (ADD manager_id NUMBER);

    • B. 

      MODIFY VIEW emp_dept_vu (ADD manager_id NUMBER);

    • C. 

      ALTER VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employee e, departments d WHERE e.department_id = d.department_id;

    • D. 

      MODIFY VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id;

    • E. 

      CREATE OR REPLACE VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id;

    • F. 

      You must remove the existing view first, and then run the CREATE VIEW command with a new column list to modify a view.

  • 5. 
    For which two constraints does the Oracle Server implicitly create a unique index? (Choose two.)
    • A. 

      NOT NULL

    • B. 

      PRIMARY KEY

    • C. 

      FOREIGN KEY

    • D. 

      CHECK

    • E. 

      UNIQUE

  • 6. 
    Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: EMPLOYEES EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHARD2(25) LAST_NAME VARCHARD2(25) HIRE_DATE DATE NEW EMPLOYEES EMPLOYEE_ID NUMBER Primary Key NAME VARCHAR2(60) Which UPDATE statement is valid?
    • A. 

      UPDATE new_employees SET name = (Select last_name|| first_name FROM employees Where employee_id =180) WHERE employee_id =180;

    • B. 

      UPDATE new_employees SET name = (SELECT last_name||first_name FROM employees) WHERE employee_id =180;

    • C. 

      UPDATE new_employees SET name = (SELECT last_name|| first_name FROM employees WHERE employee_id =180) WHERE employee_id =(SELECT employee_id FROM new employees);

    • D. 

      UPDATE new_employees SET name = (SELECT last name|| first_name FROM employees WHERE employee_id= (SELECT employee_id FROM new_employees)) WHERE employee_id =180;

  • 7. 
    Examine the structure if the EMPLOYEES table: Column name Data Type Remarks EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_NAME VARCHAR2(30) JOB_ID VARCHAR2(20) NOT NULL SAL NUMBER MGR_ID NUMBER References EMPLOYEE_ID column DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column of the DEPARTMENTS table You need to create a view called EMP_VU that allows the user to insert rows through the view. Which SQL statement, when used to create the EMP_VU view, allows the user to insert rows?
    • A. 

      CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, department_id FROM employees WHERE mgr_id IN (102, 120);

    • B. 

      CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id department_id FROM employees WHERE mgr_id IN (102, 120);

    • C. 

      CREATE VIEW emp_Vu AS SELECT department_id, SUM(sal) TOTALSAL FROM employees WHERE mgr_id IN (102, 120) GROUP BY department_id;

    • D. 

      CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id, DISTINCT department_id FROM employees;

  • 8. 
    The STUDENT_GRADES table has these columns: STUDENT_ID NUMBER(12) SEMESTER_END DATE GPA NUMBER(4,3) The registrar has asked for a report on the average grade point average (GPA) for students enrolled during semesters that end in the year 2000. Which statement accomplish this?
    • A. 

      SELECT AVERAGE(gpa) FROM student_grades WHERE semester_end > ’01-JAN-2000’ and semester end < 31-DEC-2000’;

    • B. 

      SELECT COUNT(gpa) FROM student grades WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’;

    • C. 

      SELECT MIN(gpa) FROM student grades WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’;

    • D. 

      SELECT AVG(gpa) FROM student_grades WHERE semester_end BETWEEN ’01-JAN-2000’ and ’31.DEC.2000’;

    • E. 

      SELECT SUM(gpa) FROM student grades WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’;

    • F. 

      SELECT MEDIAN(gpa) FROM student_grades WHERE semester end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’;

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

    • F. 

      You cannot group the rows of a table by more than one column while using aggregate functions.

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

  • 11. 
    Which SQL statement defines a FOREIGN KEY constraint on the DEPTNO column of the EMP table?
    • A. 

      CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_deptno_fk FOREIGN KEY deptno REFERENCES dept deptno);

    • B. 

      CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));

    • C. 

      CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_deptno_fk REFERENCES dept (deptno) FOREIGN KEY (deptno));

    • D. 

      CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) FOREIGN KEY CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));

  • 12. 
    Click the Exhibit button and examine the data in the EMPLOYEES table. Which three subqueries work? (Choose three.)    
    • A. 

      SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department_id);

    • B. 

      SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);

    • C. 

      SELECT distinct department_id FROM employees WHERE salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);

    • D. 

      SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);

    • E. 

      SELECT last_name FROM employees WHERE salary > ANY (SELECT MAX(salary) FROM employees GROUP BY department_id);

    • F. 

      SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY));

  • 13. 
    Which SQL statement accepts user input for the columns to be displayed, the table name, and the WHERE condition?
    • A. 

      SELECT &1,

    • B. 

      SELECT &1, '&2' FROM &3 WHERE '&last_name = '&4'';

    • C. 

      SELECT &1, &2 FROM &3 WHERE last_name = '&4';

    • D. 

      SELECT &1, '&2' FROM EMP WHERE last_name = '&4';

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

      The two statements produce identical results.

    • B. 

      The second statement returns a syntax error.

    • C. 

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

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

  • 15. 
    In which scenario would an 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.

  • 16. 
    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) Which statement produces the number of different departments that have employees with last name Smith?
    • A. 

      SELECT COUNT(*) FROM employees WHERE last_name='Smith';

    • B. 

      SELECT COUNT(dept_id) FROM employees WHERE last_name='Smith';

    • C. 

      SELECT DISTINCT(COUNT(dept_id)) FROM employees WHERE last_name='Smith';

    • D. 

      SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name='Smith';

    • E. 

      SELECT UNIQUE(dept_id) FROM employees WHERE last_name='Smith';

  • 17. 
    What does the FORCE option for creating a view do?
    • A. 

      Creates a view with constraints

    • B. 

      Creates a view even if the underlying parent table has constraints

    • C. 

      Creates a view in another schema even if you don't have privileges

    • D. 

      Creates a view regardless of whether or not the base tables exist

  • 18. 
    What is true about joining tables through an equijoin?
    • A. 

      You can join a maximum of two tables through an equijoin.

    • B. 

      You can join a maximum of two columns through an equijoin.

    • C. 

      You specify an equijoin condition in the SELECT or FROM clauses of a SELECT statement.

    • D. 

      To join two tables through an equijoin, the columns in the join condition must be primary key and foreign key columns.

    • E. 

      You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n-1 join conditions.

  • 19. 
    Which two statements are true about WHERE and HAVING clauses? (Choose two)
    • A. 

      A WHERE clause can be used to restrict both rows and groups.

    • B. 

      A WHERE clause can be used to restrict rows only.

    • C. 

      A HAVING clause can be used to restrict both rows and groups.

    • D. 

      A HAVING clause can be used to restrict groups only.

    • E. 

      A WHERE clause CANNOT be used in a query of the query uses a HAVING clause.

    • F. 

      A HAVING clause CANNOT be used in subqueries.

  • 20. 
    You are sorting data in a table in you SELECT statement in descending order. The column you are sorting on contains NULL records, where will the NULL record appears?
    • A. 

      At the beginning of the list.

    • B. 

      At the end of the list

    • C. 

      In the middle of the list.

    • D. 

      At the same location they are listed in the unordered table.

  • 21. 
    You are sorting data in a table in you SELECT statement in descending order. The column you are sorting on contains NULL records, where will the NULL record appears?
    • A. 

      At the beginning of the list.

    • B. 

      At the end of the list

    • C. 

      In the middle of the list.

    • D. 

      At the same location they are listed in the unordered table.

  • 22. 
    The ACCOUNT table contains these columns: ACCOUNT_ID NUMBER(12) PREVIOUS_BALANCE NUMBER(7,2) PAYMENTS NUMBER(7,2) NEW_PURCHASES NUMBER(7,2) CREDIT_LIMIT NUMBER(7) You need to display the account number, finance charge, and current balance for accounts 1500 through 2000 with a current balance greater than the account's credit limit. The finance charge is .9 percent (.009) of the previous balance. Adding the previous balance value, new purchases value, and finance charge value, and then subtracting the payments value yields the current balance value. Evaluate this statement: SELECT account_id, NVL(previous_balance, 0) * .009 finance_charge, NVL(new_purchases, 0) + (NVL(previous_balance, 0) * 1.009) - NVL(payments, 0) current balance FROM account WHERE (new_purchases + (previous_balance * 1.009)) - payments > credit_limit AND account_id BETWEEN 1500 AND 2000; Which statement about this SELECT statement is true?
    • A. 

      The statement calculates the finance charge incorrectly.

    • B. 

      The statement calculates the current balance incorrectly.

    • C. 

      The statement returns only accounts that have NO previous balance.

    • D. 

      The statement returns only accounts that have new purchases, previous balance, and

  • 23. 
    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;

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

      SUM(start_date)

    • B. 

      AVG(start_date)

    • C. 

      COUNT(start_date)

    • D. 

      AVG(start_date, end_date)

    • E. 

      MIN(start_date)

    • F. 

      MAXIMUM(start_date)

  • 25. 
    You want to use single row function in your SQL statements which three of the following are number functions? (Choose three).
    • A. 

      SINH

    • B. 

      TO_NUMBER.

    • C. 

      SQRT.

    • D. 

      ROUND.