chapter 8
data type
A column's data type specifies the type of information that the column is intended to store. A column's data type also determines the types of operations that can be performed on the data.
mod function
A function which finds the remainder of a division problem. For example Mod 2 would find the remainder of x/2, thus determining if the number is even or odd.
grouping function
If you want to assign a value other than null to these columns, you can do that by using the GROUPING function, as illustrated in figure 8-21. This function accepts the name of a column as its argument. The column you specify must be one of the columns named in a GROUP BY clause that includes the ROLLUP or CUBE operator. Description • You can use the GROUPING function to determine when a null value is assigned to a column as the result of the ROLLUP or CUBE operator. The column you name in this function must be one of the columns named in the GROUP BY clause. • If a null value is assigned to the specified column as the result of the ROLLUP or CUBE operator, the GROUPING function returns a value of 1. Otherwise, it returns a value of 0. • You typically use the GROUPING function with the CASE expression. Then, if the GROUPIN
The ROUND function
ROUND is a pre-defined function, meaning the code to make it work already exists. If you use the ROUND function, the computer will round a value to a certain number of decimal places: SET size TO ROUND (measurement, 1) This takes the value of the measurement variable and rounds it to one decimal place. It then returns this new value to the size variable
The TRUNC function
Returns the number truncated as specified by the length argument. If the length argument is omitted, this func- tion truncates all numbers to the right of the decimal point. If it's negative, this function truncates the speci- fied number of digits to the left of the decimal point.
ntile function
The NTILE function divides the rows in a partition into the specified number of groups. • If the rows can't be evenly divided into groups, the later groups may have one less row than the earlier grou
e RANK and DENSE_RANK
The RANK and DENSE_RANK functions both return the rank of each row within the partition of a result set. • If there is a tie, both of these functions give the same rank to all rows that are tied. • To determine the rank for the next distinct row, the RANK function adds 1 to the total number of rows, while the DENSE_RANK function adds 1 to the rank for the previous ro
Ranking Functions
The ROW_NUMBER, RANK, DENSE_RANK, and NTILE functions are known as ranking functions. • The ROW_NUMBER function returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. • The ORDER BY clause of a ranking function specifies the sort order in which the ranking function is applied. • The optional PARTITION BY clause of a ranking function specifies the column that's used to divide the result set into g
Large object data types
The large object (LOB) data types are used to store large amounts of text, images, sound, video, and so on. I CLOB Character large object. Stores up to 8 terabytes of character data inside the database. NCLOB National character large object. Stores up to 8 terabytes of national character data inside the database. BLOB Binary large object. Stores up to 8 terabytes of unstructured binary data inside the database. BFILE Binary file. Stores a pointer to a large binary file store
numeric data type
The numeric data types are used to store numbers that can be used for mathematical calculations. These numbers can be integers, which are numbers that don't contain decimal places, or they can be numbers that contain decimal places. They can also be floating-point numbers that are used to store approxi- mate values for very large and very small numbers.
Temporal Data Type
The temporal data types are used primarily to store dates and times. In Oracle terminology, these data types are referred to as the datetime, date/time, or just date types. Remember, though that these types always include a time component as well as a date component.
The CASE function is used to Evaluates a list of conditions and returns one of multiple possible result expressions
This function returns a value that's determined by the conditions you specify. The t
variable length strings
Varchar2 and nVarchchar2 - variable number of bytes
power function
a function of the form y=ax^b, where a is a real number and b is a rational number
fixed length string
char and nchar same number of bytes
precision and scale
for number data type The precision is the total number of digits that can be stored in the column, and the scale is the number of digits that can be stored to the right of the decimal point.
float data type
holds a floating point value of up to six or seven significant digits of accuracy
The NVL function can be used to include records containing null values in calculations.
nvl function
abs function
returns the absolute value of the number analyzed by the function
SUBSTR function ex
returns the characters you want to access from a string variable
ceil function
returns the smallest integer greater than or equal to the argument
convert data functions
shows how to use three of the Oracle functions to convert data from one type to another. The TO_CHAR function converts an expression to the VARCHAR2 data type. The TO_NUMBER function converts an expression to the NUMBER type. And the TO_DATE function converts an expression to the DATE type
search for date
• If you perform a search using a date string that doesn't include the time, the date string is converted implicitly to a date/time value of 12:00:00 AM (midnight) or 00:00:00 on a 24-hour clock. Then, if the date columns you're searching have time values other than 12:00:00 AM, you have to accommodate the times in the search condition. • You can accommodate non-zero time components by searching for a range of dates rather th
search for a time value
• To ignore the date component of a date/time value, you can convert the DATE type to a CHAR type and only return the part of the time value that you want to use.
rowid data type
Addresses for each row in the database
cast function
CAST is an ANSI-standard function that you can use for simple conversions, but the TO functions give you more control over the conversions that are don First_Name, CAST(Score AS Integer) Int_Score FROM Student_Score
COALESCE, NVL, and NVL2
COALESCE is the most flexible because it lets you specify a list of values. Then, it returns the first non-null value in the list. In contrast, the NVL and NVL2 functions aren't as flexible since they only let you substitute a single non-null value for a null value. The COALESCE, NVL, and NVL2 functions let you substitute non-null values for null values. • The COALESCE function returns the first expression in a list of expressions that isn't null. All of the expressions in the list must have the same data type. If all of the expressions are null, this function returns a null valu
floor function
Calculates largest whole number that is less than or equal to its argument.
Character Data Type
Character data includes the letters of the alphabet (upper and lower cases), the digits 0-9 and special characters such as ! ? . , *. All these symbols combined are called alphanumeric.
substr function
Extracts a portion of a character variable: *NewVar = SUBSTR (string, start, <length>);
NUMBER(7, 2)
For example, a column defined as NUMBER(7, 2) allows for a number seven digits long with two digits to the right of the decimal point. As a result, the maximum value for the column is 99999.99. In contrast to the fixed numbers stored by the
The INSTR function
INSTR function e INSTR examples show how to search for a string within a string and to return an integer value for its starting position.
date data type examples
DATE TIMESTAMP[(fsp)] TIMESTAMP [(fsp)] WITH TIME ZONE TIMESTAMP [(fsp)] WITH LOCAL TIME ZONE INTERVAL YEAR [(yp)] TO MONTH INTERVAL DAY [(dp)] TO SECOND [(fsp)]
