Advanced Query Writing, concluded - Quiz

(Page 4 of 4 )

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).

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

Comments On: Advanced Query Writing, concluded

· This article is an excerpt from the book "SQL DeMYSTified," published by...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter six of the book SQL DeMYSTiFied, written by Andrew Oppel (McGraw-Hill/Osborne, 2005; ISBN: 0072262249). Check it out today at your favorite bookstore. Buy this book now.