Advanced Query Writing

This article, the first 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).

Before we move on to Data Manipulation Language in Chapter 7, this chapter is intended to round out your knowledge of SQL queries by covering some advanced topics that were only brushed upon in Chapters 4 and 5. It covers the following topics:

  • Advanced SQL functions, including character, mathematical, and date/time functions
  • A description of how to take advantage of views
  • A description of how to use SQL to generate SQL statements
  • Information on the SQL CASE expression and its use in forming statements that have portions that are only executed under predefined conditions

Advanced SQL Functions

SQL functions were introduced in Chapter 4. The topics that follow describe functions that were not covered in Chapter 4 but which you will find useful. In addition to character and mathematical functions, selected date and time functions are also included. Remember that all SQL functions have a common characteristic in that they return a single value, so they are useful in several places in SQL statements, including the SELECT statement column list and WHERE clause. As a reminder, there are many more implementation-specific functions provided by the various DBMS vendors, so always check the vendor-supplied documentation for more useful functions.

Character Functions

Character functions operate on character data. This topic presents some commonly used functions in addition to those covered in Chapter 4. Except as noted, you will find the function supported by the most popular SQL implementations, including Microsoft SQL Server, Oracle, DB2, and MySQL.

REPLACE

The REPLACE function searches a character string and replaces characters found in a search string with characters listed in a replacement string. Here is the general syntax:

REPLACE(character_string, search_string, replacement_string)

  • character_string is the string to be searched and is most often a table column name, but it can be any expression that yields a character string.
  • search_string is the string of one or more characters to be found in character_string.
  •  replacement_string is the string that replaces any occurrences of search_string that are found in character_string.

Here is an example that replaces all hyphens (dashes) found in a person’s phone number with periods (only the first two rows in the result set are shown):

SELECT PERSON_PHONE,
      
REPLACE(PERSON_PHONE,’-‘,’.’) AS DISPLAY_PHONE
  
FROM PERSON;
PERSON_PHONE     DISPLAY_PHONE
—————  —————
230-229-8976     230.229.8976
401-617-7297     401.617.7297

LTRIM

The LTRIM function removes any leading (left-hand) spaces in a character string. Note that only leading spaces are removed—embedded and trailing spaces are left in the string. There is no data with leading and/or trailing spaces in the video store database, so here is a general example:

LTRIM (‘ String with spaces ‘)
Returns this string: ‘String with spaces ‘

RTRIM

The RTRIM function works like LTRIM, but it removes trailing spaces. If you need to remove both leading and trailing spaces, you can nest LTRIM and RTRIM like this:

RTRIM(LTRIM (‘ String with spaces ‘))
Returns this string: ‘String with spaces’

NOTE: Oracle provides a convenient function named TRIM that trims both leading and trailing spaces. For other implementations, you can always nest the LTRIM and RTRIM functions and achieve the same result.

{mospagebreak title=More Character Functions}

Null Value Function (NVL, ISNULL, IFNULL)

Oracle, Microsoft SQL Server, and MySQL all provide a function that replaces null values with a selected value. Unfortunately, they each give the function a different name: NVL in Oracle, ISNULL in SQL Server, and IFNULL in MySQL. Apparently, DB2 has no equivalent function. The following examples select the LATE_OR_LOSS_FEE from the MOVIE_RENTAL table with null values replaced by 0. Transaction 9 was selected because it has two movies on it, one of which has a null value for LATE_OR_LOSS_FEE (a good example that shows that null values are transformed while non-null values are left just the way they are).

   Oracle:

SELECT NVL(LATE_OR_LOSS_FEE, 0) AS LATE_OR_LOSS_FEE
 
FROM MOVIE_RENTAL
WHERE TRANSACTION_ID=9;
LATE_OR_LOSS_FEE
—————-
              
0
          
29.98
2 rows selected.

Microsoft SQL Server:

SELECT ISNULL(LATE_OR_LOSS_FEE, 0) AS LATE_OR_LOSS_FEE
 
FROM MOVIE_RENTAL
WHERE TRANSACTION_ID=9;
LATE_OR_LOSS_FEE
—————-
          
29.98
            
.00
 (2 rows affected)

   MySQL:

SELECT IFNULL(LATE_OR_LOSS_FEE, 0) AS LATE_OR_LOSS_FEE
 
FROM MOVIE_RENTAL
WHERE TRANSACTION_ID=9;
+——————+
| LATE_OR_LOSS_FEE |
+——————+
|            29.98 |
|             0.00 |
+——————+
2 rows in set (0.16 sec)

Notice how differently the MySQL command line client formats its output. While SQL clients from different vendors typically format results differently, the good news is that the data is the same.

ASCII

The ASCII function returns the ASCII character set value (a number between 0 and 255) for a character string containing a single character. For example, the ASCII code for a space is 32, so ASCII(‘ ‘) would return a value of 32.

CHAR (CHR)

The CHAR function (named CHR in Oracle and DB2) returns the character associated with an ASCII value (a number between 0 and 255). For example, the function ASCII(44) returns a comma since the ASCII value for a comma is 44. This function is particularly useful for concatenating characters that either cannot be displayed or would be awkward to handle in SQL. Some of the ASCII characters typically used with this function are listed in the following table. You can use the ASCII function or an ASCII character set table (easily found on the Internet) if you need to know other values.

ASCII Value

Character

9

Tab

10

Line feed

13

Carriage return

39

Single quote

Some examples follow. Keep in mind that the concatenation operators are not the same for all DBMS implementations (‘+’ for Microsoft SQL Server, ‘||’ for most others).

  • Find any movie titles that have a Tab character in them:

    Microsoft SQL Server:

    SELECT MOVIE_ID FROM MOVIE
     WHERE MOVIE_TITLE LIKE ‘%’+CHAR(9)+’%';
    MOVIE_ID
    (0 rows affected)

    Oracle and DB2:

    SELECT MOVIE_ID FROM MOVIE
     
    WHERE MOVIE_TITLE LIKE ‘%’ || CHR(9) || ‘%';
    no rows selected

NOTE: You may modify the Tab query to find movies with single quotes in their titles by changing the 9 to 39. You should find Movie ID 3 (Something’s Gotta Give).

{mospagebreak title=Mathematical Functions}

As you might guess from the name, mathematical functions return the result of a mathematical operation and usually require a numeric expression as an input parameter, which can be a literal value, a numeric table column value, or any expression (including the output of another function) that yields a numeric value.

SIGN

The SIGN function takes in a numeric expression and returns one of the following values based on the sign of the input number:

Return Value

Meaning

1

Input number is negative

0

Input number is zero

1

Input number is positive

null

Input number is null

Here is an example:

SELECT LATE_OR_LOSS_FEE,
      
SIGN(LATE_OR_LOSS_FEE) AS FEE_SIGN
  
FROM MOVIE_RENTAL
 WHERE LATE_OR_LOSS_FEE IS NOT NULL;
LATE_OR_LOSS_FEE FEE_SIGN
—————- ——–
           29.99        1
              
4        1
              
4        1
          
29.98        1

SQRT

The SQRT function takes in a single numeric expression and returns its square root. The general syntax is

SQRT (numeric_expression)

The result is a bit meaningless, but let’s take the square root of the non-null Late or Loss Fees we just looked at:

SELECT LATE_OR_LOSS_FEE,
       SQRT(LATE_OR_LOSS_FEE) AS FEE_SQRT
 
FROM MOVIE_RENTAL
 
WHERE LATE_OR_LOSS_FEE IS NOT NULL;
LATE_OR_LOSS_FEE    FEE_SQRT
—————-  ———-
          
29.99  5.47631263
              
4           2
              
4           2
          
29.98  5.47539953

CEILING (CEIL)

The CEILING function returns the smallest integer that is greater than or equal to the value of the numeric expression provided as an input parameter. In other words, it rounds up to the next nearest whole number. There are some interesting naming compatibility issues across SQL implementations: Microsoft SQL Server uses the name CEILING, Oracle uses the name CEIL, and both DB2 and MySQL allow either name (CEIL or CEILING) to be used.

As an example, let’s apply CEILING to the Late or Loss Fees (if you are using Oracle, change CEILING to CEIL):

SELECT LATE_OR_LOSS_FEE,
      
CEILING(LATE_OR_LOSS_FEE) AS FEE_CEILING
 
FROM MOVIE_RENTAL
WHERE LATE_OR_LOSS_FEE IS NOT NULL;
LATE_OR_LOSS_FEE  FEE_CEILING
—————-  ———–
           
4.00            4
           
4.00            4
          
29.99           30
          
29.98           30

FLOOR

The FLOOR function is the logical opposite of the CEILING function—it returns the integer that is less than or equal to the value of the numeric expression provided as an input parameter. In other words, it rounds down to the next nearest whole number.

Here is an example showing FLOOR applied to Late or Loss Fees:

SELECT LATE_OR_LOSS_FEE,
       FLOOR(LATE_OR_LOSS_FEE) AS FEE_FLOOR
 
FROM MOVIE_RENTAL
 
WHERE LATE_OR_LOSS_FEE IS NOT NULL;
LATE_OR_LOSS_FEE  FEE_FLOOR
—————-  ———
            4.00          4
           
4.00          4
          
29.99         29
          
29.98         29

{mospagebreak title=Date and Time Functions}

There is very little consistency in date and time functions across different DBMS vendors. Largely, this is because most of them developed date and time data types ahead of the development of standards. Because of this diversity, date and time functions are presented in summary form for Microsoft SQL Server, Oracle, DB2, and MySQL. As always, the vendor documentation should be consulted for detailed explanations of the use of these functions. Terms shown in italics are defined in the notes at the bottom of each table. The term “datetime” is used throughout this section to mean a character string that contains both a date and time in a format that is acceptable to the particular DBMS.

Microsoft SQL Server Date and Time Functions

Microsoft SQL Server offers the date and time functions shown in the following table:

Function

Purpose

Input Parameters

DATEADD

Returns a new datetimecalculated by adding an interval to the datepart of the supplied date

datepart, interval quantity, datetime

DATEDIFF

Returns the number of datetime boundaries crossed between two dates

datepart, start datetime, end datetime

DATENAME

Returns a text name representing the selected datepartof the input datetime

datepart, datetime

Function

Purpose

Input Parameters

DATEPART

Returns an integer representing the selected datepartof the supplied datetime

datepart, datetime

DAY

Returns an integer representing the day contained in the supplied datetime

datetime

GETDATE

Returns the current system datetime

None

GETUTCDATE

Returns the current UTC (Universal Coordinated Time) datetime

None

MONTH

Returns an integer representing the month contained in the supplied datetime

datetime

YEAR

Returns an integer (four digits) representing the year contained in the supplied datetime

datetime

NOTE: Datepart is a parameter that specifies a part of a date, such as year, month, day, hour, minute, second, and millisecond. Refer to Microsoft SQL Server documentation for values and options.

Oracle Date and Time Functions

Oracle has more than 24 date and time functions. Remember that while Oracle calls the data type DATE, all dates contain a time component—it’s just set to zeros (representing midnight) when it’s not used. The functions you are most likely to use are listed in the following table:

Function

Purpose

Input Parameters

ADD_MONTHS

Adds the supplied number of months to the supplied date

date, number of months (positive or negative value)

CURRENT_DATE

Returns the current date in the time zone set for the database session

None

EXTRACT

Extracts the specified datetime field from the supplied date

datetime field keyword, date

LAST_DAY

Returns the supplied date with the day shifted to the last day of the month

date

Function

Purpose

Input Parameters

MONTHS_BETWEEN

Returns the number of months (including fractional parts) between the two supplied dates; result is negative if second date is before the first date

first date, second date

SYSDATE

Returns the current system date and time

None

TO_CHAR

When used with a date, converts the date to a character string in a format specified by the format string

date, format_string

TO_DATE

Converts the supplied character string into an Oracle internally formatted date, using the format string as a template for interpreting the character string’s contents

date, format_string

TRUNC

Truncates a date to the time unit specified in the datetime field keyword. If the keyword is omitted, the date is truncated to the current day

date, datetime field keyword

NOTE:

  • Datetime field keyword is a keyword that specifies one of the fields contained within an Oracle date, such as YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.
  • Format string is a character string of symbols that specify the format that is to be used for the date when converted to or from a character string. There are over 40 different symbols that may be used in the format string (see Oracle documentation for an exhaustive list). For example, the format string ‘MM/DD/YYYY HH:MI’ would refer to a date character string that would look like ‘12/01/2004 11:58’, while the format string ‘DD-MON-RR’ (the Oracle default format) would refer to a string that would look like ‘01-Dec-04’.
  • TO_CHAR can also be used to convert numeric values to character strings.
  • TRUNC can also be used to truncate numeric values, which chops off any numbers to the right of the decimal point.

MySQL Date and Time Functions

MySQL has well over 30 date and time functions. Of those, the ones you are most likely to use are listed in the following table:

Function

Purpose

Input Parameters

ADDDATE

Adds two date, interval, or datetime expressions, yielding a new date

expression 1, expression 2

ADDTIME

Adds two time expressions, yielding a new time

expression 1, expression 2

CURDATE

Returns the current date in YYYY-MM-DD format

None

DATE

Returns the date part of a date or datetime expression

datetime expression

DATEDIFF

Returns the number of days between two dates

start date, end date

DATE_FORMAT

Formats a date according to a format string

date, format string

DAYNAME

Returns the text name for the day of the week contained in a date

date

DAYOFMONTH

Returns the day of the month, in the range 1 to 31

date

DAYOFWEEK

Returns a weekday index number for the day contained in a date (1 for Sunday, 2 for Monday, and so forth)

date

DAYOFYEAR

Returns the day of the year for the day contained in a date with a valid range of 1 to 366

date

LAST_DAY

Changes the day in a date to the last day of the month

date

MONTH

Returns the month contained in a date with a valid range of 1 to 12

date

MONTHNAME

Returns the text name of the month contained in a date

date

NOW

Returns the current date and time

None

STR_TO_DATE

Converts a character string to a datetime format data item; the format stringindicates the format of the date information in the input character string

character string, format string

TIME

Extracts the time part of a datetime or time expression

datetime

Function

Purpose

Input Parameters

TIMEDIFF

Returns the time difference between two datetime or time expression parameters

expression 1, expression 2

TIME_FORMAT

Formats a time according to the format string

time, format string

UTC_DATE

Returns the current UTC (Universal Coordinated Time) date

None

UTC_TIME

Returns the current UTC (Universal Coordinated Time) time

None

WEEKOFYEAR

Returns the week of the year for a date, in the range 1 to 54

date

NOTE: Format string is a string of characters that indicates formatting options for parts of the date. Consult MySQL documentation for details.

DB2 Date and Time Functions

DB2 UDB contains over 20 date and time functions. Of those, the ones you are most likely to use are listed in the table that follows:

Function

Purpose

Input Parameters

DATE

Converts an expression into a date

expression

DAY

Returns the day part of a datetime expression

datetime expression

DAYNAME

Returns the text name of the day of the week for a date or datetime expression

datetime expression

DAYOFWEEK

Returns the day of the week (1 for Sunday, 2 for Monday, and so forth) for a datetime expression

datetime expression

DAYS

Returns an integer representation of a date

datetime expression

MINUTE

Returns the minute part of a datetime expression

datetime expression

MONTH

Returns the month part of a datetime expression

datetime expression

MONTHNAME

Returns the text name of the month for a date or datetime expression

datetime expression

QUARTER

Returns an integer in the range 1 to 4 representing the calendar quarter in which a date falls

datetime expression

SECOND

Returns the seconds part of a date or datetime expression

datetime expression

 

Function

Purpose

Input Parameters

TIME

Returns the time part of a date or datetime expression

datetime expression

WEEK

Returns the week of the year as an integer in the range 1 to 54

datetime expression

YEAR

Returns the year part of a date or datetime expression

datetime expression

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan