Oracle SQL

30 Questions | Total Attempts: 762

SettingsSettingsSettings
Please wait...
Oracle SQL Quizzes & Trivia

Welcome to oracle quiz. . . . It is brought you by CHN11AJ112 batch members. . . . It contains the intro and basics of ORACLE. . .


Questions and Answers
  • 1. 
    The PLAYER table contains these columns: ID NUMBER(9) NAME VARCHAR(2) MANAGER_ID NUMBER(9) In this instance, managers are players and you need to display a list of players. Evaluate these two SQL statements: SELECT p.name, m.name FROMplayer p, player m WHEREm.id = p.manager_id; SELECT p.name, m.name FROMplayer p, player m WHEREm.manager_id = p.id; How will the results differ?
    • A. 

      Statement 1 will not execute; statement 2 will.

    • B. 

      The results will be the same, but the display will be different.

    • C. 

      Statement 1 will execute; statement 2 will not.

    • D. 

      Statement 1 is self-join; statement 2 is not.

  • 2. 
    • A. 

      .Any pending transactions on the table are rolled back.

    • B. 

      The table structure and its deleted data cannot be rolled back and restored once the DROP TABLE command is executed.

    • C. 

      The DROP TABLE command can be executed on a table on which there are pending transactions.

    • D. 

      Only a DBA can execute the DROP TABLE command.

  • 3. 
    • A. 

      Use the ALTER TABLE command with the MODIFY clause on the EMP table.

    • B. 

      Use the ALTER TABLE command with the MODIFY clause on the DEPT table.

    • C. 

      Use the ALTER TABLE command with the ADD clause on the EMP table.

    • D. 

      This task cannot be accomplished.

  • 4. 
    Evaluate this SQL script: CREATE ROLE manager; " CREATE ROLE clerk; CREATE ROLE inventory; CREATE USER scott IDENTIFIED BY tiger; GRANT inventory TO clerk; GRANT clerk TO manager; GRANT inventory TO scott / How many roles will user SCOTT have access to?
    • A. 

      1

    • B. 

      2

    • C. 

      0

    • D. 

      3

  • 5. 
    You want to display the details of all employees whose last name is Smith, but you are not sure in which case the last names are stored. Which statement will list all the employees whose last name is Smith?
    • A. 

      SELECT lastname, firstname FROM emp WHERE lastname = UPPER('smith');

    • B. 

      SELECT lastname, firstname FROM emp WHERE lastname = 'smith';

    • C. 

      SELECT lastname, firstname FROM emp WHERE UPPER(lastname) = 'smith';

    • D. 

      SELECT lastname, firstname FROM emp WHERE LOWER(lastname) = 'smith';

  • 6. 
    • A. 

      Date values are displayed in descending order by default. "

    • B. 

      C.The results are sorted by the first column in the SELECT list if the ORDER BY clause is not provided.

    • C. 

      Null values are displayed last in ascending sequences.

    • D. 

      You cannot sort query results by a column that is not included the SELECT list.

  • 7. 
    Evaluate this SQL statement: SELECTe.id, (.15 * e.salary) (.25 * e.bonus) (s.sale_amount * (.15 * e.commission_pct)) FROMemployee e, sale s WHEREe.id = s.emp_id; What would happen if you removed all the parentheses from the calculation?
    • A. 

      The results will be lower.

    • B. 

      The statement will achieve the same results.

    • C. 

      The statement will not execute.

    • D. 

      The results will be higher.

  • 8. 
    Which SELECT statement displays all the employees who do not have any subordinates?
    • A. 

      SELECT e.ename FROM emp e WHERE e.empno NOT IN (SELECT m.mgr FROM emp m WHERE m.mgr IS NOT NULL);

    • B. 

      SELECT e.ename FROM emp e WHERE e.empno IN (SELECT m.mgr FROM emp m);

    • C. 

      SELECT e.ename FROM emp e WHERE e.mgr IS NOT NULL;

    • D. 

      SELECT e.ename FROM emp e WHERE e.empno NOT IN (SELECT m.mgr FROM emp m);

  • 9. 
    Evaluate this IF statement: IF v_value > 100 THEN v_new_value := 2 * v_value; ELSIF v_value > 200 THEN v_new_value := 3 * v_value; ELSIF v_value < 300 THEN v_new_value := 4 * v_value; ELSE v_new_value := 5 * v_value; END IF; What would be assigned to V_NEW_VALUE if V_VALUE is 250?
    • A. 

      250

    • B. 

      1250

    • C. 

      500

    • D. 

      750

  • 10. 
    • A. 

      NOT IN operator is not allowed in subqueries.

    • B. 

      .All employees have a manager.

    • C. 

      None of the employees has a manager.

    • D. 

      .A NULL value is returned from the subquery.

  • 11. 
    Examine the structure of the STUDENT table: " Name Null?Type----------------- ----------- ------------STUD_ID NOT NULL NUMBER(3) NAME NOT NULL VARCHAR2(25) ADDRESS VARCHAR2(50) GRADUATION DATE Which statement inserts a new row into the STUDENT table?
    • A. 

      INSERT INTO student (stud_id, address, graduation) VALUES (101,'100 Main Street','17-JUN-99');

    • B. 

      INSERT INTO student table VALUES (101,'Smith','100 Main Street','17-JUN-99');

    • C. 

      .INSERT INTO student (stud_id, address, name, graduation) VALUES (101,'100 Main Street','Smith','17-JUN-99');

    • D. 

      INSERT INTO student VALUES (101,'100 Main Street','17-JUN-99','Smith');

  • 12. 
    Which operator is NOT appropriate in the join condition of a non-equi join SELECT statement?
    • A. 

      LIKE operator

    • B. 

      IN operator

    • C. 

      BETWEEN x AND y operator

    • D. 

      Equal operator

  • 13. 
    You have been granted UPDATE privileges on the last_name column of the employee table. Which data dictionary view would you query to display the column the privilege was granted on and the schema that owns the employee table?
    • A. 

      ALL_TABLES "

    • B. 

      ALL_COL_PRIVS_RECD

    • C. 

      ALL_SOURCE

    • D. 

      TABLE_PRIVILEGES

  • 14. 
    Examine the structure of the STUDENT table: Name Null?Type------------------ -------------- ------------STUD_ID NOT NULL NUMBER(3) NAME NOT NULL VARCHAR2(25) ADDRESS VARCHAR2(50) GRADUATION DATE Graduation column is a foreign key column to the GRADDATE table. " Examine the data in the GRADDATE table: GRADUATION--------------20-JAN-1999 12-MAY-1999 19-JAN-2000 25-MAY-2000 13-JAN-2001 29-MAY-2001 Which update statement produces the following error? ORA-02291: integrity constraint (SYS_C23) violated - parent key not found
    • A. 

      UPDATE student SET name = 'Smith', graduation = '15-AUG-2000' WHERE stud_id = 101;

    • B. 

      UPDATE studentD.UPDATE student SET stud_id = NULL, address = '100 Main Street' WHERE graduation = '20-JAN-1999';

    • C. 

      UPDATE student SET name = 'Smith', graduation = '29-MAY-2001' WHERE stud_id = 101;

    • D. 

      UPDATE student SET stud_id = 999, graduation = '29-MAY-2001' WHERE stud_id = 101;

  • 15. 
    The EMPLOYEE table contains these columns: FIRST_NAMEVARCHAR2(25) COMMISSION NUMBER(3,2) Evaluate this SQL statement: SELECT first_name, commission " FROM employee WHERE commission = (SELECTcommission FROMemployee WHEREUPPER(first_name) = 'SCOTT') What would cause this statement to fail?
    • A. 

      There is more than one employee with the first name Scott.

    • B. 

      The FIRST_NAME values in the database are in lowercase

    • C. 

      Scott has a NULL commission value.

    • D. 

      Scott has a zero commission value.

  • 16. 
    The EMPLOYEE table contains these columns: LAST_NAME VARCHAR2(25) FIRST_NAME VARCHAR2(25) SALARY NUMBER(7,2) You need to display the names of employees that earn more than the average salary of all employees. Evaluate this SQL statement: SELECT last_name, first_name FROMemployee WHEREsalary > AVG(salary); Which change should you make to achieve the desired results?
    • A. 

      Move the function to the SELECT clause and add a GROUP BY clause and a HAVING clause.

    • B. 

      Move the function to the SELECT clause and add a GROUP BY clause

    • C. 

      Use a subquery in the WHERE clause to compare the average salary value.

    • D. 

      .Change the function in the WHERE clause.

  • 17. 
    In which section of a PL/SQL block is a user-defined exception raised?
    • A. 

      Exception handling

    • B. 

      Executable

    • C. 

      Declarative

    • D. 

      Heading

  • 18. 
    You are a user of the PROD database which contains over 1000 tables, and you need to determine the number of tables you can access. Which data dictionary view could you query to display this information?
    • A. 

      ALL_OBJECTS

    • B. 

      DBA_SEGMENTS

    • C. 

      USER_OBJECTS

    • D. 

      .DBA_TABLES

  • 19. 
    Using SQL*Plus, you created a user with this command: CREATE USER jennifer IDENTIFIED BY jbw122; What should you do to allow the user database access?
    • A. 

      Use the ALTER USER to assign the user a default profile.

    • B. 

      .Use the ALTER USER command to assign the user a default tablespace.

    • C. 

      .No action is required to give the user database access.

    • D. 

      Grant the user the CREATE SESSION privilege.

  • 20. 
    You query the database with this command: SELECT last_name, first_name " FROM employee WHERE salary IN (SELECT salary FROM employee WHERE dept_no = 3 OR dept_no = 5); Which values are displayed?
    • A. 

      Name and first name of only the employees in department number 3 or 5

    • B. 

      Last name and first name of all employees with the same salary as employees in department 3 or 5

    • C. 

      .last name and first name of all employees except those working in department 3 or 5

    • D. 

      Last name and first name of only the employees whose salary falls in the range of salaries from department 3 or 5

  • 21. 
    You issue this command: CREATE PUBLIC SYNONYM emp FOR ed.employee; Which task has been accomplished?
    • A. 

      The need to qualify the object name with its schema has been eliminated for all users.

    • B. 

      All users were given object privileges to the table.

    • C. 

      The need to qualify the object name with its schema was eliminated for only you.

    • D. 

      .The object can now be accessed by all users.

  • 22. 
    Which statement about multiple column subqueries is true?
    • A. 

      In a non-pairwise subquery, the values returned from the subquery are compared as a group to the values in the outer query.

    • B. 

      In a pairwise subquery, the values returned from the subquery are compared individually to the values in the outer query.

    • C. 

      .A pairwise comparison produces a cross product.

    • D. 

      B.A non-pairwise comparison produces a cross product.

  • 23. 
    • A. 

      DELETE * FROM student;

    • B. 

      DROP TABLE student;

    • C. 

      TRUNCATE TABLE student;

    • D. 

      DROP TABLE student;

  • 24. 
    Examine the structure of the STUDENT table: Name Null?Type------------------ ---------- ------------STUD_ID NOT NULL NUMBER(3) NAME NOT NULL VARCHAR2(25) PHONE NOT NULL VARCHAR2(9) ADDRESS VARCHAR2(50) GRADUATION DATE There are 100 records in the STUDENT table. You want to change the name of the GRADUATION column to GRAD_DATE. Which statement is true? "
    • A. 

      You use the ALTER TABLE command with the RENAME COLUMN clause to rename the column.

    • B. 

      .You cannot rename a column.

    • C. 

      You use the ALTER TABLE command with the MODIFY clause to rename the column.

    • D. 

      .You use the ALTER TABLE command with the MODIFY COLUMN clause to rename the column.

  • 25. 
    Which statement shows the view definition of the view EMP_VIEW that is created based on the table EMP?
    • A. 

      SELECT view_text FROM TABLE emp WHERE view_name = 'EMP_VIEW';

    • B. 

      SELECT view_text FROM my_views WHERE view_name = 'EMP_VIEW';

    • C. 

      DESCRIBE VIEW emp_view

    • D. 

      SELECT text FROM user_views WHERE view_name = 'EMP_VIEW';