Home arrow MySQL arrow Page 4 - Advanced Query Writing, concluded

Quiz - MySQL

This article, the second of two parts, covers some advanced topics concerning SQL queries and functions. It is excerpted from chapter six of the book SQL DeMYSTiFied, written by Andrew Oppel (McGraw-Hill/Osborne, 2005; ISBN: 0072262249).

TABLE OF CONTENTS:
  1. Advanced Query Writing, concluded
  2. Using SQL to Generate SQL
  3. The CASE Expression
  4. Quiz
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 8
March 09, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Choose the correct responses to each of the multiple-choice questions. Note that there may be more than one correct response to each question.

  1. SQL functions

    a. Return a set of values

    b. Return a single value

    c. Can be used in the WHERE clause of an SQL statement

    d. Can be used as a table name alias in an SQL statement

    e. Can be used in the column list of an SQL statement
  2. The REPLACE function

    a. Replaces a table name with a view name

    b. Replaces a column name with a column alias

    c.  Replaces a character string in a column with another character string

    d. Replaces all values in a column with a new set of values

    e. Replaces all rows in a view with rows containing data from another table 
  3. The null value function

    a. Is called NVL in Oracle databases

    b. Is called ISNULL in IBM DB2 databases

    c. Is called ISNULL in Microsoft SQL Server databases

    d. Is called ISNULL in MySQL databases

    e. Is called IFNULL in the SQL standard
  4. The LTRIM function

    a. Removes trailing spaces from character strings
    b. Removes leading spaces from character strings

    c. Can be nested with other functions

    d. Replaces null values with other values in character strings

    e. Removes both leading and trailing spaces from character strings 
  5. The CHAR function

    a. Is named CHR in some SQL implementations

    b. Is identical to the ASCII function in some SQL implementations

    c. Returns the ASCII character set value for a character

    d. Returns the character for an ASCII character set value

    e. Converts a numeric value to a character string
  6. The SIGN function

    a. Returns −1 if the supplied parameter has a negative value

    b. Returns 0 if the supplied parameter has a value of zero

    c. Returns +1 if the supplied parameter has a value greater than or equal to zero

    d. Returns 0 if the supplied parameter is null

    e. Returns a null value if the supplied parameter is not a number
  7. The CEILING function

    a. Rounds a number down to the next whole number

    b. Rounds a number up to the next whole number

    c. Always returns an integer

    d. Returns either an integer or a null value

    e. Is named CEIL in some SQL implementations
  8. The FLOOR function

    a. Rounds a number down to the next whole number

    b. Rounds a number up to the next whole number

    c. Always returns an integer

    d. Returns either an integer or a null value

    e. Is named FLR in some SQL implementations
  9. Date and time functions

    a. Are very similar across different vendor implementations

    b. Vary markedly across different vendor implementations

    c. Include functions that format date and time data items for display

    d. Include functions that convert character strings to dates and times

    e. Include functions that convert date and time data items to character strings
  10. CASE expressions

    a. Allow for conditional execution of clauses within an SQL statement

    b. Come in two forms named static and dynamic

    c. Come in two forms named searched and nonsearched

    d. Come in two forms named simple and searched

    e. Come in two forms named standard and searched

    Write SQL statements to solve each of the following problems.
  11. List the MPAA_RATING_CODE values from the MPAA_RATING table, with hyphens (dashes) translated to spaces.
  12. Using the CHAR function (called CHR in Oracle), list the MOVIE_ID and MOVIE_TITLE for all movies that have a single quote (ASCII character 39) in their names.
  13. Find the ASCII character set value for an exclamation point (!).
  14. Find the average price of a DVD movie (column DVD_RETAIL_PRICE in the MOVIE table), with the calculated average rounded up to the nearest whole dollar.
  15. Find the average price of a VHS movie (column VHS_RETAIL_PRICE in the MOVIE table), with the calculated average rounded down to the nearest whole dollar.
  16. For an Oracle database, generate the SQL commands to drop all the referential constraints owned by the current user. The Oracle catalog view is called USER_CONSTRAINTS, and referential constraints have a CONSTRAINT_TYPE of  R. The table name on which the constraint is based is in the TABLE_NAME column of the catalog view. Remember that you have to use the ALTER TABLE command to drop table constraints.
  17. For a Microsoft SQL Server database, list all the foreign key constraints. Use the SYSOBJECTS system table, where NAME is the name of the constraint, and XTYPE has the value  F  for foreign key constraints.
  18. Write an SQL statement that lists each Customer Account (CUSTOMER_ACCOUNT_ID) with a character string based on the value of CHILD_RENTAL_ALLOWED_INDIC where the string shows  Child Rental OK  if the value is Y and  NO CHILD RENTAL  if the value is  N. Hint: A simple CASE expression should work the best here.
  19. Write an SQL statement that lists each Movie (MOVIE_ID) along with the decade based on the value of YEAR_PRODUCED (80s, 90s, 00s, Unknown). A search CASE expression should be helpful here.
  20. Write an SQL statement that lists each movie rental (MOVIE_RENTAL table) with the LATE_OR_LOSS_FEE categorized as follows: None (data value null or 0), Minor (data value < 10), Major (data value >=10).


 
 
>>> More MySQL Articles          >>> More By McGraw-Hill/Osborne
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: