# SQL III- 7

The flashcards below were created by user dau108 on FreezingBlue Flashcards.

1. wholesale
the selling of goods in large quantities to be retailed by others
2. retail
the sale of goods to the public in relatively small quantities for use of consumption rather for resale
3. offset
1. a amount that balaces the effect of a contrary one: an offser against taxable profits.

2. the amount of distance by which something is out of line
4. functions

COUNT
AVG
MAX
MIN
SUM
• SELECT COUNT (*)
• FROM FOODS ;

• SELECT COUNT (Calories)
• FROM FOODS ;

The AVG function calculates and returns the average of the values in the specified column. Of course, you can use the AVG function only on columns that contain numeric data, as in the following example:

• SELECT AVG (Fat)
• FROM FOODS ;

• SELECT AVG (Fat)
• FROM FOODS
• WHERE Food <> ‘Butter’ ;

The MAX function returns the maximum value found in the specified column. The following statement returns a value of 81 (the fat content in 100 grams of butter):

• SELECT MAX (Fat)
• FROM FOODS ;

The MIN function returns the minimum value found in the specified column. The following statement returns a value of 0.4, because the function doesn’t treat the nulls as zeros:

• SELECT MIN (Carbohydrate)
• FROM FOODS;

The SUM function returns the sum of all the values found in the specified column. The following statement returns 3,924, which is the total caloric content of all 15 foods:

• SELECT SUM (Calories)
• FROM FOODS ;
5. value functions
• 􏰏 Numeric value functions
• 􏰏 Datetime value functions
• String value function
6. string value functions

􏰏 SUBSTRING
􏰏 UPPER
􏰏 LOWER
􏰏 TRIM
􏰏 TRANSLATE
􏰏 CONVERT
SUBSTRING (‘Bread, whole wheat’ FROM 8 FOR 7)

• Thesubstringextractedis‘whole w’.This substring starts with the eighth character of the source string and has a length of seven characters. On the surface, SUBSTRING doesn’t seem like a very valuable function; if you have a literal like‘Bread, whole wheat’,youdon’tneedafunctiontofigureout
• characters 8 through 14. SUBSTRING really is a valuable function, however, because the string value doesn’t need to be a literal. The value can be any expression that evaluates to a character string. Thus, you could have a variable named fooditem that takes on different values at different times.
• The following expression would extract the desired substring regardless of what character string the fooditem variable currently represents:

SUBSTRING (:fooditem FROM 8 FOR 7)

• upper
• lower

• trim
7. numeric value functions
• 􏰏 Position expression (POSITION)
• 􏰏 Extract expression (EXTRACT)
• 􏰏 Length expression (CHAR_LENGTH, CHARACTER_LENGTH, OCTET_LENGTH)
• 􏰏 Cardinality expression (CARDINALITY)
• 􏰏 Absolute value expression (ABS)
• 􏰏 Modulus expression (MOD)
• 􏰏 Natural logarithm (LN)
• 􏰏 Exponential function (EXP)
• 􏰏 Power function (POWER)
• 􏰏 Square root (SQRT)
• 􏰏 Floor function (FLOOR)
• 􏰏 Ceiling function (CEIL, CEILING)
• 􏰏 Width bucket function (WIDTH_BUCKET)
8. POSITION

POSITION searches for a specified target string within a specified source string and returns the character position where the target string begins. The syntax looks like this:

POSITION (target IN source)
9. The EXTRACT function extracts a single field from a datetime or an interval.
• The following statement, for example, returns 08:
• EXTRACT (MONTH FROM DATE ‘2006-08-20’)
10. character length

The CHARACTER_LENGTH function returns the number of characters in a character string
The following statement, for example, returns 16:

CHARACTER_LENGTH (‘Opossum, roasted’)
11. Datetime value functions
 Author: dau108 ID: 145529 Card Set: SQL III- 7 Updated: 2012-04-03 21:33:41 Tags: SQL III Folders: Description: SQL III -7 Show Answers: