Introduction to Structured Query Language by malj

VIEWS: 2,620 PAGES: 12

									Final Examination                                                          24 May 2006
Introduction to DBMS                                           Student No:____________________

Multiple Choice
Identify the letter of the choice that best completes the statement or answers the question.

____    1. Which of the following is used to uniquely identify each record?
           a. primary key                                   c. partial dependency
           b. row                                           d. account number
____    2. Which of the following usually correlates to a primary key in another table?
           a. transitive dependency                         c. foreign key
           b. composite primary key                         d. partial dependency
____    3. Which of the following is a tool that allows a user to create, edit, and manipulate data in Oracle9i?
           a. SQL                                           c. ASCII
           b. SQL*Plus                                      d. Script
____    4. Which of the following symbols represents all the columns contained in a table?
           a. /                                             c. *
           b. ;                                             d. :)
____    5. Which of the following symbols can be used to combine data from different columns into one column of
           output?
           a. ||                                            c. %
           b. ^                                             d. *
____    6. A string literal must be enclosed in ______________.
           a. single quotation marks (' ')                  c. asterisks (* *)
           b. double quotation marks (" ")                  d. percent signs (% %)

            Contents of the PUBLISHER table




____    7. Based upon the contents of the PUBLISHER table, which of the following SELECT statements will display the
           publisher's name first in the results?
           a. SELECT * FROM publisher;
           b. SELECT pubid, contact FROM publisher;
           c. SELECT name, publisherID FROM publishers;
           d. none of the above
____    8. Based upon the contents of the PUBLISHER table, which of the following is a valid SQL statement?
           a. SELECT * FROM pubid;
           b. SELECT * FROM name;
           c. SELECT * FROM contact;
           d. SELECT * FROM publisher;
           Name:_______________________________Student Number:________________________

____  9. Which of the following commands will delete only publisher 4 from the PUBLISHER table?
         a. DELETE FROM publisher;
         b. DELETE pubid = 4 FROM publisher;
         c. DROP FROM publisher WHERE pubid = 4;
         d. DELETE FROM publisher WHERE pubid = 4;
____ 10. Based on the contents of the PUBLISHER table, which of the following will add a new record to the table?
         a. INSERT INTO publisher
            VALUES ('BOOKS MADE CHEAP', '800-111-2222');
         b. INSERT INTO publisher (pubid, name)
            VALUES (6, 'BOOKS MADE CHEAP');
         c. UPDATE publisher
            VALUES ('BOOKS MADE CHEAP', '800-111-2222');
         d. UPDATE publisher (pubid, name)
            VALUES (6, 'BOOKS MADE CHEAP');

           Structure of the ORDERS table




____ 11. Based upon the structure of the ORDERS table, the column heading for which column will be truncated in the
         results?
         a. customer#                                    c. shipstreet
         b. orderdate                                    d. shipzip
____ 12. Which of the following statements can be used to view the structure of the ORDERS table?
         a. SELECT * FROM orders;
         b. DESCRIBE ORDERS
         c. LIST ORDERS;
         d. none of the above
____ 13. Which of the following clauses is used to indicate a particular sort sequence for presenting query results?
         a. SELECT                                       c. WHERE
         b. SORT BY                                      d. ORDER BY




           Dr. Wei Sun                                         Beihang University College of Software
            Name:_______________________________Student Number:________________________

            Contents of BOOK table




____ 14. Which of the following queries will return all books that cost at least $25.00?
         a. SELECT * FROM books
             WHERE cost > $25.00;
         b. SELECT * FROM books
             WHERE cost >= 25.00;
         c. SELECT * FROM books
             WHERE cost >= $25.00;
         d. none of the above
____ 15. Which of the following queries will list the title and retail price of each book stored in the BOOKS table, sorted
         in order of the book titles?
         a. SELECT title, retail FROM books
             ORDERED BY title;
         b. SELECT title, retail FROM books
             SORTED BY title;
         c. SELECT title, retail FROM books
             ORDER BY title;
         d. SELECT title, retail FROM books
             SORT BY title;




            Dr. Wei Sun                                           Beihang University College of Software
           Name:_______________________________Student Number:________________________

           Structure of the ORDERS table




           Structure of the CUSTOMERS table

____ 16. Which of the following queries will return the same results as the following SQL statement?
                         SELECT c.customer#, lastname, firstname, order#
                         FROM customers c, orders o
                         WHERE c.customer# = o.customer#;

           a. SELECT customer#, lastname, firstname, order#
              FROM customers NATURAL JOIN orders;
           b. SELECT customer#, lastname, firstname, order#
              FROM customers JOIN orders USING(customer#);
           c. SELECT c.customer#, lastname, firstname, order#
              FROM customers c, orders o;
           d. both a and b
____ 17.   Which of the following functions can be used to convert a character string to lower-case letters?
           a. LOW                                           c. SMALLER
           b. LOWER                                         d. SMALLCAP
____ 18.   Which of the following functions will convert the first letter of each word to an upper-case letter and the
           remaining letters of the word to lower-case letters?
           a. INITIALCAPITAL                                c. UPPERFIRST
           b. INITIALCAP                                    d. none of the above
____ 19.   Which of the following functions can be used to substitute another value for a NULL value during calculations?
           a. NVL                                           c. NULVAL
           b. NULLVAL                                       d. NLV
____ 20.   Which of the following keywords will return the value of the date according to the computer?
           a. SYSTEMDATE                                    c. DATE
           b. SYSDATE                                       d. DATESYS
____ 21.   Which of the following is the name assigned to a dummy table in Oracle9i?
           a. DUMMY                                         c. DUAL
           b. DUM                                           d. EMPTY




           Dr. Wei Sun                                           Beihang University College of Software
           Name:_______________________________Student Number:________________________

           Contents of the BOOKS table




____ 22. Based on the contents of the BOOKS table, which of the following SQL statements will return the total profit
         generated by books in the COOKING category?
         a. SELECT TOTAL(retail-cost) FROM books
             WHERE category = 'COOKING';
         b. SELECT TOTAL(retail-cost) FROM books
             GROUP BY category;
         c. SELECT SUM(retail-cost) FROM books
             WHERE category = 'COOKING';
         d. SELECT SUM(retail-cost) FROM books
             GROUP BY category = 'COOKING';
____ 23. Which command instructs Oracle9i to create a new table?
         a. CREATE NEW TABLE                           c. CREATE TABLE
         b. CREATE TABLE...FROM                        d. ALTER TABLE
____ 24. Which command instructs Oracle9i to create a new table from existing data?
         a. CREATE NEW TABLE                           c. CREATE TABLE
         b. CREATE TABLE...FROM                        d. CREATE TABLE...AS
____ 25. When defining columns for a table in the CREATE TABLE command, the column list must be enclosed in
         which of the following symbols?
         a. " "                                        c. ( )
         b. ' '                                        d. | |
____ 26. Which of the following keywords is used to change the size, datatype, and/or default value of an existing
         column?
         a. ADD                                        c. CHANGE
         b. MODIFY                                     d. RESET
____ 27. Which of the following keywords is used to mark a column for deletion at a later time?
         a. ALTER TABLE...MARK UNUSED                  c. ALTER TABLE...DROP UNUSED
         b. ALTER TABLE...SET UNUSED                   d. ALTER TABLE...SET COLUMN
____ 28. Which of the following commands will drop any columns marked as unused from the TABLEA table?
         a. DROP COLUMN FROM tablea WHERE column_status = UNUSED;
         b. ALTER TABLE tablea DROP UNUSED COLUMNS;
         c. ALTER TABLE tablea DROP (unused);
         d. DROP UNUSED COLUMNS:



           Dr. Wei Sun                                         Beihang University College of Software
            Name:_______________________________Student Number:________________________

____ 29. To delete all the rows in a table and free up the storage space that was occupied by those rows, the _______
         command should be used.
         a. ALTER TABLE...DELETE                           c. TRUNCATE TABLE
         b. TRUNCATE ROWS                                  d. DELETE...FROM
____ 30. Which of the following keywords is used to remove a database table in Oracle9i?
         a. DROP TABLE                                     c. DELETE TABLE
         b. ALTER TABLE...DROP                             d. TRUNCATE TABLE

            Structure of the CUSTOMERS table




____ 31. Which of the following commands will add a new column named FIRSTORDERDATE to the CUSTOMERS
         table to store the date that the customer first placed an order with the company?
         a. CREATE COLUMN firstorderdate, DATE TO customers;
         b. ALTER TABLE customers ADD COLUMN firstorderdate DATE;
         c. ALTER TABLE customers ADD firstorderdate DATE;
         d. ALTER TABLE customers ADD (firstorderdate DATE);

            Structure of the PROMOTION table




____ 32. Which of the following SQL statements was used to create the PROMOTION table?
         a. CREATE TABLE promotion
             (gift VARCHAR2(15), minretail NUMBER(5, 2), maxretail NUMBER(5, 2));
         b. CREATE TABLE promotion
              AS (gift VARCHAR2(15), minretail NUMBER(5, 2), maxretail NUMBER(5, 2));
         c. CREATE TABLE promotion
             ADD (gift VARCHAR2(15), minretail NUMBER(5, 2), maxretail NUMBER(5, 2));
         d. CREATE TABLE promotion
             (gift VARCHAR2(15), minretail NUMBER(5, 2), maxretail NUMBER(5, 2);
____ 33. Which of the following is a valid statement?
         a. Constraints are rules used to enforce business rules, practices, and policies.
         b. Constraints prevent errors by not allowing data to be added to tables if the data violates
            specific rules.
         c. Constraints ensure the accuracy and integrity of data.
         d. all of the above
            Dr. Wei Sun                                          Beihang University College of Software
           Name:_______________________________Student Number:________________________

____ 34. The UNIQUE constraint differs from the PRIMARY KEY constraint in what way?
         a. The UNIQUE constraint does not allow NULL values.
         b. The UNIQUE constraint can be created at either the column level or the table level.
         c. The UNIQUE constraint allows NULL values.
         d. The UNIQUE constraint ensures that a specific condition is true before a data value is added
            to a table.
____ 35. Which of the following is not a constraint type in Oracle9i?
         a. CHECK                                         c. NOT NULL
         b. UNIQUE                                        d. REFERENCE
____ 36. Only one ______ constraint can exist for each table.
         a. PRIMARY KEY                                   c. UNIQUE
         b. FOREIGN KEY                                   d. CHECK
____ 37. Which view will display the names of all the constraints that you own?
         a. DISPLAY_CONSTRAINTS                           c. USER_CONSTRAINTS
         b. ALL_CONSTRAINTS                               d. TABLE_CONSTRAINTS
____ 38. Which of the following statements about the FOREIGN KEY constraint is incorrect?
         a. The constraint exists between two tables, called the parent table and the child table.
         b. When the constraint exists, by default a record cannot be deleted from the parent table if
            matching entries exist in the child table.
         c. The constraint can reference any column in another table, even a column that has not been
            designated as the primary key for the referenced table.
         d. When the keywords ON DELETE CASCADE are included in the constraint definition, a
            corresponding child record will automatically be deleted when the parent record is deleted.
____ 39. The ____ constraint prevents the user from adding a NULL value in the specified column.
         a. IS NULL                                       c. NULL
         b. NOT NULL                                      d. NULL KEY

           Structure of the ORDERITEMS table




____ 40. Based on the structure of the ORDERITEMS table, which of the following commands will make certain that the
         ISBN entered actually exists in the ISBN column of the BOOKS table?
         a. ALTER TABLE orderitems
            ADD FOREIGN KEY isbn REFERENCES BOOKS TABLE (isbn);
         b. ALTER TABLE orderitems
            MODIFY FOREIGN KEY (isbn) REFERENCES books(isbn);
         c. ALTER TABLE orderitems
            CREATE FOREIGN KEY (isbn) REFERENCES books(isbn);
         d. ALTER TABLE orderitems
            ADD FOREIGN KEY (isbn) REFERENCES books(isbn);
____ 41. Which of the following commands is used to remove rows from a table?
         a. DELETE                                     c. REMOVE
         b. DROP                                       d. MODIFY



           Dr. Wei Sun                                          Beihang University College of Software
           Name:_______________________________Student Number:________________________


____ 42. Which of the following commands allows a user to "undo" uncommitted changes to data?
         a. UNDO                                          c. UNCOMMIT
         b. ROLLBACK                                      d. UNLOCK
____ 43. When the INSERT command is being used to enter data into a non-numeric column, the data must be enclosed
         in _________.
         a. parentheses                                   c. single quotation marks
         b. double quotation marks                        d. upper-case letters
____ 44. When inserting a row into a table, how can you indicate that a row contains a NULL value?
         a. In the VALUES clause, substitute two single quotation marks for the NULL value.
         b. In the VALUES clause, include the keyword NULL in the position where the value should
             be listed.
         c. In the VALUES clause, include a blank space in the position where the value should be
             listed.
         d. both a and b
____ 45. What will happen if you try to use the INSERT command to insert a NULL value into column that has been
         assigned a PRIMARY KEY constraint?
         a. The command will execute because a PRIMARY KEY value can be NULL.
         b. An error message is returned and the row is not added to the table.
         c. The command will execute because only a UNIQUE constraint forbids NULL values.
         d. An error message is returned but the unaffected portion of the row is added to the table.
____ 46. Commands used to modify data are called _____ commands.
         a. data control language (DCL)                   c. data modification language (DML)
         b. data manipulation language (DML)              d. data definition language (DDL)
____ 47. A(n) _____ shows a pseudo table, which is a table that can be created to present a particular display of a
         database's contents.
         a. view                                          c. function
         b. constraint                                    d. argument
____ 48. Which of the following statements about views is incorrect?
         a. Views assist users who do not have the training to issue complex SQL inquiries.
         b. Views restrict users' access to sensitive data.
         c. Views are database objects that actually store data.
         d. A view can be referenced in a SELECT...FROM statement, just like any table.
____ 49. Which statement is true about the view created with the following command?
                     CREATE VIEW inventory AS SELECT isbn, title, retail price
                     FROM books
                     WITH READ ONLY;
         a. The command creates a simple view.
         b. DML operations are NOT allowed on the data displayed by the view.
         c. A view named INVENTORY did not previously exist.
         d. all of the above




           Dr. Wei Sun                                        Beihang University College of Software
            Name:_______________________________Student Number:________________________


            Contents of Titlereport7.sql script file




____ 50. Which of the following commands is included in the Titlereport7.sql script file to actually retrieve data for the
         report?
         a. SET                                       c. COLUMN
         b. TTITLE                                    d. SELECT




            Dr. Wei Sun                                           Beihang University College of Software
           Name:_______________________________Student Number:________________________

Completion
Complete each sentence or statement.

      51. A solid straight line in an E-R Model depicts a(n) _________________________ relationship.

      52. A(n) _________________________ is used to uniquely identify each record.

      53. Data is in second normal form if it is in first normal form and has no _________________________.

      54. If at least one value in a record does not depend upon the primary key, then ________________________
          exists.

      55. A column's _________________________ identifies the type of data that can be stored in a column.

      56. Any DML operation can be performed on a(n) _________________________ view as long as the view was not
          created with the WITH READ ONLY option and existing constraints are not violated.

      57. _______________, _______________, and ______________ are all DML commands.

      58. The CREATE TABLE command is an example of _____________ _____________________ language..

      59. Sometimes we need to get data that is stored in more than one table. We use a statement called a
          _____________ to do this.

      60. I would like to invite you to share any comments you may have about this class, including things you may like
          or dislike, or suggestions for improvement.




           Dr. Wei Sun                                          Beihang University College of Software
           Name:_______________________________Student Number:________________________


Introduction to Structured Query Language
Answer Section

MULTIPLE CHOICE

      1.   ANS:   A         REF:   7
      2.   ANS:   C         REF:   9
      3.   ANS:   B         REF:   14
      4.   ANS:   C         REF:   25
      5.   ANS:   A         REF:   34
      6.   ANS:   A         REF:   35
      7.   ANS:   D         REF:   28
      8.   ANS:   D         REF:   28
      9.   ANS:   D         REF:   307
     10.   ANS:   B         REF:   294
     11.   ANS:   D         REF:   26
     12.   ANS:   B         REF:   26
     13.   ANS:   D         REF:   63
     14.   ANS:   B         REF:   53
     15.   ANS:   C         REF:   63
     16.   ANS:   D         REF:   91
     17.   ANS:   B         REF:   124
     18.   ANS:   D         REF:   126
     19.   ANS:   A         REF:   140
     20.   ANS:   B         REF:   149
     21.   ANS:   C         REF:   150
     22.   ANS:   C         REF:   164
     23.   ANS:   C         REF:   232
     24.   ANS:   D         REF:   235
     25.   ANS:   C         REF:   232
     26.   ANS:   B         REF:   240
     27.   ANS:   B         REF:   244
     28.   ANS:   B         REF:   245
     29.   ANS:   C         REF:   248
     30.   ANS:   A         REF:   249
     31.   ANS:   D         REF:   240
     32.   ANS:   A         REF:   232
     33.   ANS:   D         REF:   259
     34.   ANS:   C         REF:   260
     35.   ANS:   D         REF:   261
     36.   ANS:   A         REF:   263
     37.   ANS:   C         REF:   264
     38.   ANS:   C         REF:   267
     39.   ANS:   B         REF:   271
     40.   ANS:   D         REF:   266
     41.   ANS:   A         REF:   290
     42.   ANS:   B         REF:   290
     43.   ANS:   C         REF:   291
           Dr. Wei Sun                             Beihang University College of Software
          Name:_______________________________Student Number:________________________


    44.   ANS:   D              REF:    293
    45.   ANS:   B              REF:    296
    46.   ANS:   B              REF:    304
    47.   ANS:   A              REF:    321
    48.   ANS:   C              REF:    321
    49.   ANS:   D              REF:    324
    50.   ANS:   D              REF:    411


COMPLETION

    51. ANS:
        one-to-one
        one to one

          REF: 4
    52.   ANS: primary key                    REF: 7
    53.   ANS: partial dependencies           REF: 8
    54.   ANS: transitive dependency          REF: 8
    55.   ANS:
          data type
          datatype

          REF:   230
    56.   ANS:   simple                       REF: 326
    57.   ANS:   insert, update and delete
    58.   ANS:   data definition
    59.   ANS:   join
    60.   ANS:   answers will vary




          Dr. Wei Sun                                  Beihang University College of Software

								
To top