Oracle Academy Database by gsmsajithali

VIEWS: 119 PAGES: 16

More Info
									Section 17
 (Answer all questions in this section)

    1. Which statement about the ORDER BY clause is true?
                                                                                              Mark for Review

                                                                                                    (1) Points

             You can use a column alias in the ORDER BY clause. (*)

             The default sort order of the ORDER BY clause is descending.

             The ORDER BY clause can only contain columns that are included in the SELECT
             list.
             The ORDER BY clause should immediately precede the FROM clause in a SELECT
             statement

              Correct.

    2. Which logical operator returns TRUE if either condition is true?
                                                                                              Mark for Review

                                                                                                    (1) Points

             OR (*)

             AND

             NOT

             BOTH

              Correct.

    3. You need to change the default sort order of the ORDER BY clause so that the data is
                                                                                              Mark for Review
       displayed in reverse alphabetical order. Which keyword should you include in the
                                                                                                    (1) Points
       ORDER BY clause?

             DESC (*)

             ASC

             SORT

             CHANGE

              Correct.

    4. The ORDER BY clause always comes last. True or False?
                                                                                              Mark for Review

                                                                                                    (1) Points

             True (*)

             False

              Correct.
5. From left to right, what is the correct order of Precedence?
                                                                                       Mark for Review

                                                                                             (1) Points

        Arithmetic, Concatenation, Comparison, OR (*)

        NOT, AND, OR, Arithmetic

        Arithmetic, NOT, Logical, Comparison

        Arithmetic, NOT, Concatenation, Logical

         Correct.

6. Which clause would you include in a SELECT statement to sort the rows returned by
                                                                                       Mark for Review
   the LAST_NAME column?
                                                                                             (1) Points

        ORDER BY (*)

        WHERE

        FROM

        HAVING

         Correct.

7. You attempt to query the database with this SQL statement:
                                                                                       Mark for Review

   SELECT product_id "Product Number", category_id "Category", price "Price"                 (1) Points

   FROM products
   WHERE "Category" = 5570
   ORDER BY "Product Number";


   This statement fails when executed. Which clause contains a syntax error?

        SELECT product_id "Product Number", category_id "Category", price "price"

        ORDER BY "Product Number";

        FROM products

        WHERE "Category" = 5570 (*)

         Correct.

8. Evaluate this SELECT statement:
                                                                                       Mark for Review

   SELECT *                                                                                  (1) Points

   FROM employees
   WHERE salary > 30000
   AND department_id = 10
    OR email IS NOT NULL;


    Which statement is true?

         The OR condition will be evaluated before the AND condition.

         The AND condition will be evaluated before the OR condition. (*)

         The OR and AND conditions have the same precedence and will be evaluated
         from left to right
         The OR and AND conditions have the same precedence and will be evaluated
         from right to left

          Correct.

 9. Evaluate this SELECT statement:
                                                                                          Mark for Review

    SELECT last_name, first_name, email                                                         (1) Points

    FROM employees
    ORDER BY email;


    If the EMAIL column contains null values, which statement is true?

         Null email values will be displayed first in the result.

         Null email values will be displayed last in the result. (*)

         Null email values will not be displayed in the result.

         The result will not be sorted.

          Correct.

10. Evaluate this SELECT statement:
                                                                                          Mark for Review

    SELECT first_name, last_name, email                                                         (1) Points

    FROM employees
    ORDER BY last_name;


    Which statement is true?

         The rows will not be sorted.

         The rows will be sorted alphabetically by the LAST_NAME values. (*)

         The rows will be sorted in reverse alphabetical order by the LAST_NAME values.

         The rows will be sorted alphabetically by the FIRST_NAME and then the
         LAST_NAME values

          Correct.
Section 17
 (Answer all questions in this section)

  11. Evaluate this SELECT statement:
                                                                                             Mark for Review

       SELECT employee_id, last_name, first_name, salary ‘Yearly Salary'                           (1) Points

       FROM employees
       WHERE salary IS NOT NULL
       ORDER BY last_name, 3;


       Which clause contains an error?

             SELECT employee_id, last_name, first_name, salary 'Yearly Salary' (*)

             FROM employees

             WHERE salary IS NOT NULL

             ORDER BY last_name, 3;

              Correct.

  12. Which SELECT statement should you use to limit the display of product information to
                                                                                             Mark for Review
       those products with a price of less than 50?
                                                                                                   (1) Points

             SELECT product_id, product_name
             FROM products
             WHERE price < 50;


             (*)
             SELECT product_id, product_name
             FROM products
             HAVING price < 50;
             SELECT product_id, product_name
             FROM products
             WHERE price <= 50;
             SELECT product_id, product_name
             FROM products
             GROUP BY price < 50;
             SELECT product_id, product_name
             FROM products
             WHERE price < 50.00
             GROUP BY price;

              Correct.
13. The EMPLOYEES table contains these columns:
                                                                                         Mark for Review

    EMPLOYEE_ID NUMBER(9) PK                                                                   (1) Points

    LAST_NAME VARCHAR2(25)
    FIRST_NAME VARCHAR2(25)
    DEPARTMENT_ID NUMBER(9)
    Compare these two SQL statements: ;


    1.
    SELECT DISTINCT department_id DEPT, last_name, first_name
    FROM employees
    ORDER BY department_id;


    2.
    SELECT department_id DEPT, last_name, first_name
    FROM employees
    ORDER BY DEPT;
    How will the results differ?

         One of the statements will return a syntax error.

         One of the statements will eliminate all duplicate DEPARTMENT_ID values.

         There is no difference in the result between the two statements. (*)

         The statements will sort on different column values.

          Correct.

14. The PLAYERS table contains these columns:
                                                                                         Mark for Review

    PLAYERS TABLE:                                                                             (1) Points

    LAST_NAME VARCHAR2 (20)
    FIRST_NAME VARCHAR2 (20)
    SALARY NUMBER(8,2)
    TEAM_ID NUMBER(4)
    MANAGER_ID NUMBER(9)
    POSITION_ID NUMBER(4)


    You want to display all players' names with position 6900 or greater. You want the
    players names to be displayed alphabetically by last name and then by first name.
    Which statement should you use to achieve the required results?

         SELECT last_name, first_name
           FROM players
           WHERE position_id >=6900
           ORDER BY last_name, first_name;


           (*)
           SELECT last_name, first_name
           FROM players
           WHERE position_id > 6900
           ORDER BY last_name, first_name;
           SELECT last_name, first_name
           FROM players
           WHERE position_id <= 6900
           ORDER BY last_name, first_name;
           FROM players
           WHERE position_id >= 6900
           ORDER BY last_name DESC, first_name;

             Correct.

 15. Evaluate this SQL statement:
                                                                                             Mark for Review

      SELECT e.employee_id, e.last_name, e.first_name, m.manager_id                                (1) Points

      FROM employees e, employees m
      ORDER BY e.last_name, e.first_name
      WHERE e.employee_id = m.manager_id;


      This statement fails when executed. Which change will correct the problem?

           Reorder the clauses in the query. (*)

           Remove the table aliases in the WHERE clause.

           Remove the table aliases in the ORDER BY clause.

           Include a HAVING clause.

             Correct.


Section 16
(Answer all questions in this section)

 16. You want to retrieve a list of customers whose last names begin with the letters Fr .
                                                                                             Mark for Review
      Which symbol should you include in the WHERE clause of your SELECT statement to
                                                                                                   (1) Points
      achieve the desired result?

           % (*)
         ~

         #

         *

          Correct.

17. Evaluate this SELECT statement:
                                                                                             Mark for Review

    SELECT last_name, first_name, salary                                                           (1) Points

    FROM employees;


    How will the heading for the SALARY column appear in the display by default in Oracle
    Application Express?

         The heading will display with the first character capitalized and centered.

         The heading will display with the first character capitalized and left justified.

         The heading will display as uppercase and centered. (*)

         The heading will display as uppercase and left justified.

          Correct.

18. You want to determine the orders that have been placed by customers who reside in
                                                                                             Mark for Review
    Chicago. You write this partial SELECT statement:
                                                                                                   (1) Points

    SELECT orderid, orderdate, total
    FROM orders;


    What should you include in your SELECT statement to achieve the desired results?

         AND city = Chicago;

         AND city = 'Chicago';

         WHERE city = 'Chicago'; (*)

         WHERE city = Chicago;

          Correct.

19. You need to display all the values in the EMAIL column that contains the underscore
                                                                                             Mark for Review
    (_) character as part of that email address. The WHERE clause in your SELECT
                                                                                                   (1) Points
    statement contains the LIKE operator. What must you include in the LIKE operator?

         The ESCAPE option (\) and one or more percent signs (%) (*)

         The (+) operator

         A percent sign (%)
             The ESCAPE option (\)

             Incorrect. See Section 16 Lesson 2.

  20. Evaluate this SELECT statement:
                                                                                            Mark for Review

       SELECT *                                                                                   (1) Points

       FROM employees
       WHERE department_id IN(10, 20, 30)
       AND salary > 20000;


       Which values would cause the logical condition to return TRUE?

             DEPARTMENT_ID = 10 and SALARY = 20000

             DEPARTMENT_ID = 20 and SALARY = 20000

             DEPARTMENT_ID = null and SALARY = 20001

             DEPARTMENT_ID = 10 and SALARY = 20001 (*)

             Correct.
Section 16
 (Answer all questions in this section)

  21. The EMPLOYEES table contains these columns:
                                                                                            Mark for Review

       LAST_NAME VARCHAR2(25)                                                                     (1) Points

       FIRST_NAME VARCHAR2(25)
       EMAIL VARCHAR2(50)


       You are writing a SELECT statement to retrieve the names of employees that have an
       email address.


       SELECT last_name||', '||first_name "Employee Name"
       FROM employees;


       Which WHERE clause should you use to complete this statement?

             WHERE email = NULL;

             WHERE email != NULL;

             WHERE email IS NULL;

             WHERE email IS NOT NULL; (*)

             Correct.
22. Which comparison operator searches for a specified character pattern?
                                                                                            Mark for Review

                                                                                                  (1) Points

         IN

         LIKE (*)

         BETWEEN...AND...

         IS NULL

          Correct.

23. Which symbol represents the not equal to condition?
                                                                                            Mark for Review

                                                                                                  (1) Points

         #

         +'

         != (*)

         ~

          Correct.

24. You need to display employees whose salary is in the range of 30000 and 50000.
                                                                                            Mark for Review
    Which comparison operator should you use?
                                                                                                  (1) Points

         IN

         LIKE

         BETWEEN...AND... (*)

         IS NULL

          Correct.

25. Which clause would you include in a SELECT statement to restrict the data returned to
                                                                                            Mark for Review
    only the employees in department 10?
                                                                                                  (1) Points

         WHERE (*)

         FROM

         SELECT

         IS

          Correct.

26. Which operator is used to combine columns of character strings to other columns?
                                                                                            Mark for Review
                                                                                                     (1) Points

           *

           /

           +

           || (*)

               Correct.

 27. When using the LIKE condition, which symbol represents any sequence of none, one
                                                                                               Mark for Review
      or more characters?
                                                                                                     (1) Points

           _

           % (*)

           #

           &

               Correct.

 28. You need to combine the FIRST_NAME and LAST_NAME columns in the EMPLOYEES
                                                                                               Mark for Review
      table and display the columns as a combined character string. Which operator should
                                                                                                     (1) Points
      you use?

           +

           |

           || (*)

           AND

               Correct.


Section 12
(Answer all questions in this section)

 29. Once you have created a table, it is not possible to alter the definition of it. If you
                                                                                               Mark for Review
      need to add a new column you must delete the table definition and create a new,
                                                                                                     (1) Points
      correct table. True or False?

           True

           False (*)

               Correct.

 30. The SQL statement ALTER TABLE EMPLOYEES DELETE COLUMN SALARY is a valid
                                                                                               Mark for Review
      statement. True or False?
                                                                                               (1) Points

               True

               False (*)

                Correct.
Section 12
 (Answer all questions in this section)

  31. What command will return data from the database to you?
                                                                                         Mark for Review

                                                                                               (1) Points

               FETCH

               GET

               SELECT (*)

               RETURN

               Correct.

  32. The _______ clause can be added to a SELECT statement to return a subset of the
                                                                                         Mark for Review
       data.
                                                                                               (1) Points

               ANYWHERE

               WHICH

               WHERE (*)

               EVERY

               Correct.

  33. The DESCRIBE command returns all rows from a table. True or False?
                                                                                         Mark for Review

                                                                                               (1) Points

               True

               False (*)

               Correct.


 Section 15
 (Answer all questions in this section)

  34. In which clause of a SELECT statement would you specify the name of the table or
                                                                                         Mark for Review
       tables being queried?
                                                                                               (1) Points
         The FROM clause (*)

         The SELECT clause

         The WHERE clause

         Any of the above options, you can list tables wherever you want to in a SELECT
         statement.

          Correct.

35. You query the database with this SQL statement:
                                                                                          Mark for Review
    SELECT * FROM students;
                                                                                                (1) Points

    Why would you use this statement?

         To insert data

         To view data (*)

         To display the table structure

         To delete data

          Correct.

36. Which SQL keyword specifies that an alias will be substituted for a column name in
                                                                                          Mark for Review
    the output of a SQL query?
                                                                                                (1) Points

         AS (*)

         OR

         AND

         SUBSTITUTE

          Correct.

37. What would you use in the SELECT clause to return all the columns in the table?
                                                                                          Mark for Review

                                                                                                (1) Points

         An asterisk (*) (*)

         A minus sign (-)

         A plus sign (+)

         The ALL keyword

          Correct.

38. When listing columns in the SELECT list, what should you use to separate the
                                                                                          Mark for Review
    columns?
                                                                                           (1) Points

             Commas (*)

             Semicolons

             Dashes

             Underscores

             Correct.

  39. You query the database with this SQL statement:
                                                                                     Mark for Review

       SELECT *                                                                            (1) Points

       FROM transaction
       WHERE product_id = 4569;


       Which SQL SELECT statement capabilities are achieved when this statement is
       executed?

             Selection only (*)

             Projection only

             Selection and projection only

             Projection, selection and joining

             Correct.

  40. Which SQL statement will return an error?
                                                                                     Mark for Review

                                                                                           (1) Points

             SEL * FR sky; (*)

             select star from sky;

             SELECT star FROM sky;

             SELECT * FROM sky;

             Correct.

Section 11
 (Answer all questions in this section)

  41. Why would this table name NOT work in an Oracle database?
                                                                                     Mark for Review
       this_year_end+next_year
                                                                                           (1) Points

             Table names must begin with an alphabetic character

             Too long
         The Plus sign + is not allowed in object names (*)

         None of the above

           Correct

42. In a physical data model, a relationship is represented as a combination of: (Choose
                                                                                            Mark for Review
    Two)
                                                                                                  (1) Points

    (Choose all correct answers)

         Column

         Primary Key or Unique Key (*)

         Check Constraint or Unique Key

         Foreign Key (*)

           Correct

43. Attributes become tables in a database. True or False?
                                                                                            Mark for Review

                                                                                                  (1) Points

         True

         False (*)

           Correct

44. A table must have a primary key. True or False?
                                                                                            Mark for Review

                                                                                                  (1) Points

         True

         False (*)

           Correct

45. The explanation below is a User Defined integrity rule and must therefore be manually
                                                                                            Mark for Review
    coded, the Database cannot enforce this rule automatically:
                                                                                                  (1) Points

    A primary key must be unique, and no part of the primary key can be null. True or
    False?

         True

         False (*)

           Correct
46. A foreign key cannot refer to a primary key in the same table. True or False?
                                                                                            Mark for Review

                                                                                                  (1) Points

         True

         False (*)

          Correct

47. The explanation below is a column integrity constraint:
                                                                                            Mark for Review

    A column must contain only values consistent with the defined data format of the              (1) Points

    column. True or False?

         True (*)

         False

          Correct

48. If a primary key is a set of columns then one column must be null. True or False?
                                                                                            Mark for Review

                                                                                                  (1) Points

         True

         False (*)

          Correct

49. Many to many relationships are implemented via a structure called a:
                                                                                            Mark for Review
    ________________
                                                                                                  (1) Points

         Supertype

         Intersection Table (*)

         Intersection Entity

         Subtype

          Correct

50. An "Arc Implementation" can be done just like any other Relationship - you simply add
                                                                                            Mark for Review
    the required Foreign Keys. True or False?
                                                                                                  (1) Points

         True

         False (*)

          Correct

								
To top