Oracle Quiz-2

30 Questions

Settings
Please wait...
Oracle Quizzes & Trivia

Questions and Answers
  • 1. 
    • A. 

      SELECT last_name FROM employees WHERE last_name LIKE '%A\_B%' ESCAPE '\\';

    • B. 

      SELECT last_name FROM employees WHERE last_name LIKE '%A_B%' ESCAPE;

    • C. 

      SELECT last_name FROM employees WHERE last_name LIKE 'A_B%' ESCAPE '%';

    • D. 

      SELECT last_name FROM employees WHERE last_name LIKE '%A\_B%' ESCAPE '\';

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

  • 3. 
    Which of the following correctly shows the correct use of the TRUNC command on a date?
    • A. 

      A. SELECT TRUNC(TO_DATE(12-Feb-99,DD-MON-YY, 'YEAR')) "Date " FROM DUAL;

    • B. 

      B. TRUNC = TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR', "Date " FROM DUAL;

    • C. 

      C. SELECT TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL;

    • D. 

      D. date = TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL

  • 4. 
    The EMPLOYEES table contains these columns: EMPLOYEE_ID NUMBER(4) ENAME VARCHAR2 (25) JOB_ID VARCHAR2(10) Which SQL statement will return the ENAME, length of the ENAME, and the numeric position of the letter aaa in the ENAME column, for those employees whose ENAME ends with a the letter ana?   
    • A. 

      A. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, 'a') FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = 'n';

    • B. 

      B. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, ,-1,1) FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = 'n';

    • C. 

      C. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR(ENAME, 1, 1) = 'n';

    • D. 

      D. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR(ENAME, -1, 1) = 'n';

  • 5. 
    What is true about joining tables through an Equijoin?
    • A. 

      A. You can join a maximum of two tables through an Equijoin.

    • B. 

      B. You can join a maximum of two columns through an Equijoin.

    • C. 

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

    • D. 

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

    • E. 

      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.

  • 6. 
    Which three is true regarding the use of outer joins? (Choose three.)
    • A. 

      A. You cannot use IN operator in a condition that involves an outer join.

    • B. 

      B. You use (+) on both sides of the WHERE condition to perform an outer join.

    • C. 

      C. You use (*) on both sides of the WHERE condition to perform an outer join.

    • D. 

      D. You use an outer join to see only the rows that do not meet the join condition.

    • E. 

      E. In the WHERE condition, you use (+) following the name of the column in the table without matching rows, to perform an outer join.

    • F. 

      F. You cannot link a condition that is involved in an outer join to another condition by using the OR operator.

  • 7. 
    Which statement adds a constraint that ensures the CUSTOMER_NAME column of the CUSTOMERS table holds a value?   
    • A. 

      A. ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;

    • B. 

      B. ALTER TABLE customers MODIFY CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;

    • C. 

      C. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn NOT NULL;

    • D. 

      D. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn IS NOT NULL;

    • E. 

      E. ALTER TABLE customers MODIFY name CONSTRAINT cust_name_nn NOT NULL;

    • F. 

      F. ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name NOT NULL;

  • 8. 
    • A. 

      ALTER TABLE students ADD PRIMARY KEY student_id;

    • B. 

      B. ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);

    • C. 

      C. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;

    • D. 

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

    • E. 

      E. ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

  • 9. 
    . Mark for review Examine the description of the CUSTOMERS table: 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_ADDRESS VARCHAR2 (50) COUNTRY_ADDRESS   VARCHAR2 (50) POSTAL_CODE            VARCHAR2 (12) CUSTOMER_PHONE    VARCHAR2 (20) The CUSTOMER_ID column is the primary key for the table.  
    • A. 

      A. SELECT city_address, COUNT(*) FROM customers WHERE city_address IN ('Los Angeles', 'San Francisco');

    • B. 

      B. SELECT city_address, COUNT(*) FROM customers WHERE city_address IN ('Los Angeles', 'San Francisco') GROUP BY city_address;

    • C. 

      C. SELECT city_address, COUNT(customer_id) FROM customers WHERE city_address IN ('Los Angeles', 'San Francisco') GROUP BY city_address, customer_id;

    • D. 

      D. SELECT city_address, COUNT(customer_id) FROM customers GROUP BY city_address IN ('Los Angeles', 'San Francisco');

  • 10. 
    Mark for review what does the FORCE option for creating a view do?   
    • A. 

      Creates a view with constraints

    • B. 

      B. creates a view even if the underlying parent table has constraints

    • C. 

      C. creates a view in another schema even if you don't have privileges

    • D. 

      D. creates a view in another schema even if you don't have privileges

  • 11. 
    Mark for review. Which constraint can be defined only at the column level?
    • A. 

      UNIQUE

    • B. 

      NOT NULL

    • C. 

      CHECK

    • D. 

      PRIMARY KEY

    • E. 

      FOREIGN KEY

  • 12. 
     Evaluate the SQL statement: TRUNCATE TABLE DEPT; Which three are true about the SQL statement? (Choose three.)   
    • A. 

      It releases the storage space used by the table.

    • B. 

      B. It does not release the storage space used by the table.

    • C. 

      C. You can roll back the deletion of rows after the statement executes.

    • D. 

      D. You can NOT rollback the deletion of rows after the statement executes.

    • E. 

      E. An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will display an error.

    • F. 

      F. You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT table

  • 13. 
     Evaluate the SQL statement DROP TABLE DEPT: Which four statements are true of the SQL statement? (Choose four)
    • A. 

      You cannot roll back this statement.

    • B. 

      All pending transactions are committed.

    • C. 

      All views based on the DEPT table are deleted.

    • D. 

      All indexes based on the DEPT table are dropped.

    • E. 

      E. All data in the table is deleted, and the table structure is also deleted.

    • F. 

      F. All data in the table is deleted, but the structure of the table is retained.

    • G. 

      All synonyms based on the DEPT table are deleted.

  • 14. 
    Which three are true? (Choose three.)
    • A. 

      A. A MERGE statement is used to merge the data of one table with data from another.

    • B. 

      B. A MERGE statement replaces the data of one table with that of another.

    • C. 

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

    • D. 

      A MERGE statement can be used to update existing rows in a table.

  • 15. 
    Which is a valid CREATE TABLE statement?
    • A. 

      CREATE TABLE EMP9$# AS (emp_id number(2));

    • B. 

      CREATE TABLE EMP*123 AS (emp_id number(2));

    • C. 

      CREATE TABLE PACKAGE AS (pack_id number(2));

    • D. 

      CREATE TABLE 1EMP_TEST AS (emp_id number(2));

  • 16. 
    • A. 

      Difference, projection, join

    • B. 

      Selection, projection, join

    • C. 

      Selection, intersection, join

    • D. 

      Intersection, projection, join

    • E. 

      Difference, projection, product

  • 17. 
    Which three statements correctly describe the functions and use of constraints?
    • A. 

      Constraints provide data independence

    • B. 

      Constraint make complex queries easy

    • C. 

      Constraints enforce rules at the view level

    • D. 

      Constraints enforce rules at the table level

    • E. 

      E. constraints prevent the deletion of a table if there are dependencies

    • F. 

      F. constraints prevent the deletion of an index if there are dependencies

  • 18. 
    Which collection type must be explicitly initialized with a constructor?
    • A. 

      Index-by table

    • B. 

      Nested table

    • C. 

      VARRAY

    • D. 

      Nested table and VARRAY

  • 19. 
    What type of exception requires a RAISE statement?
    • A. 

      A named server exception

    • B. 

      A programmer-defined exception

    • C. 

      An unnamed server exception

    • D. 

      The RAISE statement is never required for an exception

  • 20. 
    Which Statement will create a record team_rec based on the table TEAMS , having a field in each record for each column in the table?
    • A. 

      Type team_rec is record like teams;

    • B. 

      Team_rec teams%tabletype;

    • C. 

      Team_rec teams%type;

    • D. 

      Team_rec teams%rowtype;

    • E. 

      E. You must list all the column in the TYPE statement that defines the record.

  • 21. 
    In which PL/SQL section is a server error associated with a named exception?
    • A. 

      Header

    • B. 

      Declaration

    • C. 

      Executable

    • D. 

      Exception

  • 22. 
    Which statement about packages is true?(choose two)
    • A. 

      Packages can be nested

    • B. 

      You can pass parameter to packages

    • C. 

      A package is loaded into memory each time it is invoked

    • D. 

      The contents of packages can be shared by many applications

  • 23. 
    1. What will be displayed from the following PL/SQL?
      1. DECLARE 
      2. X VARCHAR2 (10) := ‘TITLE’;
      3. Y VARCHAR2 (10) := ‘TITLE ‘;
      4. BEGIN
      5. IF X>=Y THEN
      6. dbms_output.put _ line(‘X is greater’);
      7. END IF;
      8. IF Y>=X THEN
      9. dbms_output.put_ line(‘ Y is greater’);
      10.  END IF;
      11.   END;
    • A. 

      X is greater

    • B. 

      Y is greater

    • C. 

      Both X is greater and Y is greater

    • D. 

      Neither X is greater nor Y is greater

  • 24. 
    What output will the follwing statement produce?                   Select NVL2(NULL,'NOT NULL', NULL) from dual;
    • A. 

      NULL

    • B. 

      NOT NULL

    • C. 

      Function NVL2 is not defined

    • D. 

      None of the above

  • 25. 
    Which three definitions are associated with implicit cursors?
    • A. 

      %rowtype, %toomanyrows, %found

    • B. 

      %found, %notfound, %rowcount

    • C. 

      %rowtype, %rowcount, %notfound

    • D. 

      None of the above

  • 26. 
    What will the following SQL statement return? SELECT MOD(25,5) , MOD(8,2.5) FROM DUAL;
    • A. 

      0 and .5

    • B. 

      5 and 4.5

    • C. 

      0 and 2

    • D. 

      This will raise an exception, since MOD can only operate on integer values

  • 27. 
    Given the relational schema consisting of  Department(Dnumber,Dname,Budget)  and Employee(Enumber,Ename,City,Salary,Dnumber), which SQL query retrieves  the names of departments whose budget is less than the the total salaries for employees working in that department? 
    • A. 

      Select Dname From Department, Employee Where Employee.Dnumber = Department.Dnumber AND Budget < Salary;

    • B. 

      Select Dname From Department Where Budget < (Select sum(Salary) From Employee);

    • C. 

      Select Dname From Department;

    • D. 

      Select Dname From Department Where Budget < (Select sum(Salary) From Employee Where Employee.Dnumber = Department.Dnumber);

  • 28. 
    In which cases would you use the USING clause? (Choose three.)
    • A. 

      You want to create a nonequijoin.

    • B. 

      The tables to be joined have multiple NULL columns.

    • C. 

      The tables to be joined have columns of the same name and different data types.

    • D. 

      The tables to be joined have columns with the same name and compatible data types.

    • E. 

      You want to use a NATURAL join, but you want to restrict the number of columns in the join condition

  • 29. 
    Declare cursor cl is select batchcode, expectedincome from batch FOR UPDATE; Z cl%ROWTYPE; begin OPEN cl; Loop fetch cl into Z; Exit when cl%notfound; Update batch set expectedincome = 1.1 * expectedincome where current of cl; End loop; CLOSE cl; END;
    • A. 

      Locking the rows before update

    • B. 

      Locking the row before update

    • C. 

      No lock just update

    • D. 

      Lock table

  • 30. 
    Statement 1: VARRAY :- it is like and array in programming language like ‘C’ having single dimension. Statement 2: Nested Table :- It is also like  one dimensional array but it does not have limit on upper bound.
    • A. 

      Statement 1 is true and Statement 2 is false

    • B. 

      Statement 2 is true and Statement 1 is false

    • C. 

      Both Statements are false

    • D. 

      Both Statements are true