Oracle_test6

20 Questions

Settings
Please wait...
Oracle Quizzes & Trivia

This is a basic SQL test


Questions and Answers
  • 1. 
    You issue the following query:  SELECT salary "Employee Salary" FROM employees;      How will the column heading appear in the result?
    • A. 

      EMPLOYEE SALARY

    • B. 

      EMPLOYEE_SALARY

    • C. 

      Employee Salary

    • D. 

      Employee_salary

  • 2. 
    The EMP table is defined as follows: EMP Table   Datatype Length Empno(NUMBER) 4 Ename(VARCHAR2) 30 Sal(NUMBER) 14,2 Bonus(NUMBER) 10,2 Deptno(NUMBER) 2 You perform the following two queries:   SELECT empno enumber,  ename FROM emp ORDER BY 1; SELECT empno,   ename FROM emp ORDER BY  empno ASC; Which of the following is true?
    • A. 

      Statements 1 and 2 will produce the same result.

    • B. 

      Statement 1 will execute; statement 2 will return an error

    • C. 

      Statement 2 will execute; statement 1 will return an error.

    • D. 

      Statements 1 and 2 will execute but produce different results

  • 3. 
    You issue the following SELECT statement on the EMP table . SELECT (200+((salary*0.1)/2)) FROM emp;What will happen to the result if all of the parentheses are removed                             
    • A. 

      No difference, because the answer will always be NULL

    • B. 

      No difference, because the result will be the same.

    • C. 

      The result will be higher

    • D. 

      The result will be lower

  • 4. 
    In the following SELECT statement, which component is a literal?  SELECT 'Employee Name: ' || ename FROM emp where deptno = 10;    
    • A. 

      10

    • B. 

      Ename

    • C. 

      Employee Name:

    • D. 

      ||

  • 5. 
    When you try to save 34567.2255 into a column defined as NUMBER(7,2) what value is actually saved?
    • A. 

      34567.00

    • B. 

      34567.23

    • C. 

      34567.22

    • D. 

      3456.22

  • 6. 
    What is the default display length of the DATE datatype  column?
    • A. 

      8

    • B. 

      9

    • C. 

      19

    • D. 

      6

  • 7. 
    • A. 

      EMPNO, unique values of ENAME and then SALARY are displayed.

    • B. 

      EMPNO, unique values of the two columns, ENAME and SALARY, are displayed

    • C. 

      DISTINCT is not a valid keyword in SQL

    • D. 

      No values will be displayed because the statement will return an error.

  • 8. 
    Which clause in a query limits the rows selected?
    • A. 

      ORDER BY

    • B. 

      WHERE

    • C. 

      SELECT

    • D. 

      FROM

  • 9. 
    The following listing shows the records of the EMP table.   EMPNO ENAME         SALARY      COMM    DEPTNO      --------- ---------- --------- --------- ---------       7369 SMITH           800                   20          7499  ALLEN                    1600             300                30            7521 WARD            1250       500        30      7566 JONES           2975                  20     7654 MARTIN          1250      1400        30     7698 BLAKE           2850                  30     7782 CLARK           2450     24500        10       7788 SCOTT           3000                  20       7839 KING            5000     50000        10        7844 TURNER          1500         0        30        7876 ADAMS           1100                  20       7900 JAMES            950                  30     7902 FORD            3000                  20     7934 MILLER          1300     13000        10When you issue the following query  Which value will be displayed in the first row?   SELECT empno FROM emp WHERE deptno = 10 ORDER BY ename DESC
    • A. 

      MILLER

    • B. 

      7934

    • C. 

      7876

    • D. 

      No rows will be returned ename cannot be used in the ORDER BY clause

  • 10. 
    The following listing shows the records of the EMP table.   EMPNO ENAME         SALARY      COMM    DEPTNO      --------- ---------- --------- --------- ---------       7369 SMITH           800                   20          7499  ALLEN                    1600             300                30            7521 WARD            1250       500        30      7566 JONES           2975                  20     7654 MARTIN          1250      1400        30     7698 BLAKE           2850                  30     7782 CLARK           2450     24500        10       7788 SCOTT           3000                  20       7839 KING            5000     50000        10        7844 TURNER          1500         0        30        7876 ADAMS           1100                  20       7900 JAMES            950                  30     7902 FORD            3000                  20     7934 MILLER          1300     13000        10 How many rows will the following query return?        SELECT * FROM emp WHERE ename BETWEEN 'A' AND 'C'
    • A. 

      4

    • B. 

      2

    • C. 

      A character column cannot be used in the BETWEEN operator

    • D. 

      3

  • 11. 
    ) Refer to the EMP table in question 2. When you issue the following query, which line has an error? 1)SELECT empno "Enumber", ename "EmpName"                           2)       FROM emp 3)WHERE deptno = 10                                                                            4)    AND  "Enumber" = 7782 5)ORDER BY "Enumber";          
    • A. 

      1

    • B. 

      5

    • C. 

      4

    • D. 

      No error; the statement will finish successfully

  • 12. 
    You issue the following query:               SELECT empno, ename  FROM emp WHERE empno = 7782 OR empno = 7876; Which other operator can replace the OR condition in the WHERE clause?
    • A. 

      .IN

    • B. 

      BETWEEN .. AND

    • C. 

      LIKE

    • D. 

    • E. 

      .>=

  • 13. 
    The following are clauses of the SELECT statement: In which order should they appear in a query?    1) WHERE                                         2) FROM                                                           3) ORDER BY
    • A. 

      . 1, 3, 2

    • B. 

      2, 1, 3

    • C. 

      2, 3, 1

    • D. 

      The order of these clauses does not matter.

  • 14. 
    • A. 

      SELECT * FROM ORDERS WHERE PRODUCT_ID = 'DI%';

    • B. 

      SELECT * FROM ORDERS WHERE PRODUCT_ID LIKE 'DI_' ESCAPE '\';

    • C. 

      SELECT * FROM ORDERS WHERE PRODUCT_ID LIKE 'DI\_%' ESCAPE '\';

    • D. 

      SELECT * FROM ORDERS WHERE PRODUCT_ID LIKE 'DI\_' ESCAPE '\';

    • E. 

      SELECT * FROM ORDERS WHERE PRODUCT_ID LIKE 'DI_%' ESCAPE '\';

  • 15. 
    COUNTRY_NAME and REGION_ID are valid column names in the COUNTRIES table. Which one of the following statements will execute without an error?
    • A. 

      SELECT country_name, region_id, CASE region_id = 1 THEN 'Europe', region_id = 2 THEN 'America', region_id = 3 THEN 'Asia', ELSE 'Other' END Continent FROM countries;

    • B. 

      . SELECT country_name, region_id, CASE (region_id WHEN 1 THEN 'Europe', WHEN 2 THEN 'America', WHEN 3 THEN 'Asia', ELSE 'Other') Continen FROM countries;

    • C. 

      SELECT country_name, region_id CASE region_id WHEN 1 THEN 'Europe' WHEN 2 THEN 'America' WHEN 3 THEN 'Asia' ELSE 'Other' END Continent FROM countries;

    • D. 

      SELECT country_name, region_id, CASE region_id WHEN 1 THEN 'Europe' WHEN 2 THEN 'America' WHEN 3 THEN 'Asia' ELSE 'Other' Continent FROM countries;

  • 16. 
    Which special character is used to query all the columns from the table without listing each column by name?
    • A. 

      %

    • B. 

      &

    • C. 

      @

    • D. 

      *

  • 17. 
    The EMPLOYEE table has the following data: EMP_NAME   HIRE_DATE     SALARY ---------- --------- ---------- SMITH        17-DEC-90                800         ALLEN         20-FEB-91               1600 WARD         22-FEB-91              1250JONES          02-APR-91             5975 WARDEN     28-SEP-91              1250 BLAKE          01-MAY-91            2850   What will be the value in the first row of the result set when the following query  is executed?    SELECT hire_date FROM employee  ORDER BY salary, emp_name        
    • A. 

      02-APR-91

    • B. 

      17-DEC-90

    • C. 

      28-SEP-91

    • D. 

      The query is invalid, because you cannot have a column in the ORDER BY that is not part of the SELECT clause.

  • 18. 
    When doing pattern matching using the LIKE operator, which character is used as the default escape character by Oracle?
    • A. 

      |

    • B. 

      /

    • C. 

      \

    • D. 

      There is no default escape character in Oracle9i

  • 19. 
    Column alias names cannot be used in which clause?
    • A. 

      SELECT

    • B. 

      WHERE

    • C. 

      ORDER BY

    • D. 

      None of the above

  • 20. 
    Which SQL statement will query the EMPLOYEES table for FIRST_NAME, LAST_NAME, and SALARY of all employees in DEPARTMENT_ID 40 in the alphabetical order of last name?
    • A. 

      SELECT first_name last_name salary FROM employees ORDER BY last_name WHERE department_id = 40;

    • B. 

      SELECT first_name, last_name, salary FROM employees ORDER BY last_name ASC WHERE department_id = 40;

    • C. 

      SELECT first_name last_name salary FROM employees WHERE department_id = 40 ORDER BY last_name ASC;

    • D. 

      SELECT first_name, last_name, salary FROM employees WHERE department_id = 40 ORDER BY last_name;

    • E. 

      SELECT first_name, last_name, salary FROM TABLE employees WHERE department_id IS 40 ORDER BY last_name ASC;