SQL Functions

CURDATE

CURDATE() returns today's date as a value in YYYY-MM-DD or YYYYMMDD format

CURTIME

CURTIME() returns the current time as a value in HH:MM:SS or HHMMSS format

DATE_FORMAT

DATE_FORMAT(date,format) formats the date value according to the fromat string.

MIN

Find the lowest value in a column SELECT MIN(AB) FROM TEAMSTATS; Can be used on nonnumerical column. MAX returns the lowest (closest to A) string

MAX

Finds the largest value in a column SELECT MAX(HITS) FROM TEAMSTATS; Can be used on nonnumerical column. MAX returns the highest (closest to Z) string

Logical Operator (OR)

If any of the comparisons are true, OR returns TRUE. SELECT LASTNAME WORKAHOLICS FROM VACATION WHERE YEARS >=5 OR ((YEARS*12)-LEAVETAKEN)/(YEARS*12)>=0.5;

Set Operators (MINUS)

MINUS returns the rows from the first query that were not present in the second. SELECT * FROM FOOTBALL MINUS SELECT * FROM SOFTBALL;

NEXT_DAY

NEXT_DAY find the name of the first day of the week that is equal to or later than another specified date. For example, to send a report on the Friday following the first day of each event, you would type SELECT STARTDATE NEXT_DAY(STARTDATE, 'FRIDAY') FROM PROJECT;

LAST_DAY

Specifically used in Oracle and returns the last date of a specified month SELECT ENDDATE, LAST_DAY(ENDDATE) FROM PROJECT; Can be used with a DISTINCT to eliminate duplicates

Minus (-)

Subtract value (column) from another column Change the sign of a number SELECT STATE, -HIGHTEMPS LOWS, -LOWTEMPS HIGHS FROM HILOW;

TIME_FORMAT

TIME_FORMAT(time,format) handles hours, minutes, and seconds

AVG

The AVG function computes the average of a column SELECT AVG(SO) "AVE_STRIKE_OUTS" FROM TEAMSTATS; The following two quires are not equivalent SELECT AVG(HITS/AB) "TEAM AVERAGE" FROM TEAMSTATS; ------ SELECT AVG(HITS)/AVG(AB) "TEAM AVERAGE" FROM TEAMSTATS; ---- The second function is correct.

Concatenation (||)

The double pipe symbol concatenates two strings. SELECT FIRSTNAME || LASTNAME ENTIRENAME FROM FRIENDS; Sometimes the number of characters in a name field is fixed (at 15 for example) and the code above will display those spaces in the name. Use the following strategy to make the name more normal looking. SELECT CONCAT(LASTNAME, ","," ",FIRSTNAME) NAME FROM FRIENDS;

Miscellaneous Operators: IN and BETWEEN

The two operators IN and BETWEEN provide a shorthand for functions you already know how to do. SELECT * FROM FRIENDS WHERE ST IN('CA', 'CO', 'LA'); SELECT * FROM PRICE WHERE WHOLESALE BETWEEN 0.25 AND 0.75;

GREATEST and LEAST

These functions find the GREATEST or the LEAST member from a series of expressions SELECT GREATEST ('ALPHA', 'BRAVO', "FOXTROT', 'DELTA') FROM CONVERT; This would yield FOXTROT These functions also work with numbers

SUBSTR

This three-argument function enables you to take a piece out of a target string. The first argument is the target string. The second argument is the position of the first character to be output. The third argument is the number of characters to show. SELECT FIRSTNAME, SUBSTR(FIRSTNAME,2,3) FROM CHARACTERS; The implementation of MySQL uses the function MID() or SUBSTRING. If you use a negative number as the second argument, the starting point is determined by counting backward from the end.

Set Operators (UNION and UNION ALL)

UNION returns the results of two queries minus the duplicate rows. SELECT NAME FROM SOFTBALL UNION SELECT NAME FROM FOOTBALL; UNION ALL returns the results of two queries including duplicates SELECT NAME FROM SOFTBALL UNION ALL SELECT NAME FROM FOOTBALL;

LIKE

Use like if you want to select parts of a database that fit a pattern but if you are not quite exact matches. SELECT * FROM PARTS WHERE LOCATION LIKE '%BACK%';

Greater Than (>) and Greater Than or Equal To (>=)

Used in a WHERE clause to select a range of values greater than a value or less than and including the value itself. SELECT * FROM FRIENDS WHERE AREACODE >= 300;

MONTHS_BETWEEN

Used to determine how many months fall between month x and month y. SELECT TASK, STARTDATE, ENDDATE MONTHS_BETWEEN(ENDDATE,STARTDATE) DURATION FROM PROJECT; This function is sensitive to the way you order the months. Negative months might not be bad, especially if you want to determine whether one date happend before another.

Divide (/)

Used to divide a value (column) from another value (column) SELECT ITEM, WHOLESALE, (WHOLESALE/2) SALEPRICE FROM PRICE;

Multiply (*)

Used to multiply a value (column) by another value (column) SELECT ITEM, WHOLESALE, WHOLESALE * 0.9 NEWPRICE FROM PRICE;

LIKE Wildcards

When used in a LIKE expression, % is a wildcard SELECT * FROM PARTS WHERE LOCATION LIKE 'BACK%'; would give you any occurrence that started with BACK When used in a LIKE expression, _ is a single-character wildcard. SELECT * FROM FRIENDS WHERE ST LIKE 'C_';

CONCAT

Works the same as || SELECT CONCAT(FIRSTNAME, LASTNAME) "FIRST AND LAST NAMES" FROM CHARACTERS Oracle will retain the blank characters SELECT CONCAT(FIRSTNAME,',',' ',LASTNAME)

Logical Operator (AND)

AND requires that both expressions on either side to be true to return TRUE. SELECT LASTNAME FROM VACATION WHERE YEARS <=5 AND LEAVETAKEN > 20;

ABS

Absolute value of a number SELECT ABS(A) "ABSOLUTE_VALUE" FROM NUMBERS;

Plus (+)

Add a value (column) to another column SELECT ITEM, WHOLESALE, WHOLESALE + 0.15 FROM PRICE;

CONCAT_WS

Allows you to concatenate columns together plus it allows you to specify what will separate the columns SELECT CONCAT_WS(', ',FIRSTNAME,LASTNAME) NAME FROM CHARACTERS;

CEIL and FLOOR

CEIL returns the smallest integer greater than or equal to its argument. FLOOR does the reverse, returning the largest integer equal to or less than its argument.

CHR

CHR returns the character equivalent of the number it uses as an argument. The character it returns depnds on the character set of the character set of the database. SELECT CODE, CHR(CODE) FROM CHARACTERS;

ADD_MONTHS/ADD_DATE

Function adds a number of months to a specified date. SELECT TASK, ENDDATE ORIGINAL, ADD_MONTHS(ENDDATE,2) FROM PROJECT; Can also be used in WHERE clause SELECT TASK FROM PROJECT WHERE ADD_MONTHS(STARTDATE,1)>ENDDATE These functions do not work with other data types like CHaracter or Number/Numeric without the help of the function TO_CHAR and TO_DATE

INITCAP

INITCAP capitalizes the first letter of a word and makes all other characters lowercase in Oracle SQL*Plus SELECT FIRSTNAME BEFORE, INITCAP(FIRSTNAME) AFTER FROM CHARACTERS;

Set Operators (Intersect)

INTERSECT returns only the rows found by both queries. SELECT * FROM FOOTBALL INTERSECT SELECT * FROM SOFTBALL;

Logical Operator (NOT)

If the condition it applies to evaluates to TRUE, NOT makes it FALSE. If the condition after the NOT is FALSE, it becomes TRUE. SELECT * FROM VACATION WHERE LASTNAME NOT LIKE 'B%'; NOT can also be used with the operator IS when applied to NULL. SELECT * FROM PRICE WHERE WHOLESALE IS NOT NULL;

NEW_TIME

If you need to adjust the time according to the time zone you are in.

LENGTH

LENGTH returns the length of its lone character argument. SELECT FIRSTNAME, LENGTH(RTRIM(FIRSTNAME)) FROM CHARACTERS;

LOCATE

LOCATE(substr,str) returns the position of the first occurrence of substring substr in string str

LOWER and UPPER

LOWER changes all the characters to lowercase, UPPER changes all the characters to uppercase SELECT FIRSTNAME, UPPER(FIRSTNAME), LOWER(FIRSTNAME) FROM CHARACTERS;

LPAD and RPAD

LPAD and RPAD take a minimum of two and a maximum of three arguments. The first argument is the character string to be operated on. The second is the number of characters to pad it with, and the optional third argument is the characters to pad it with. The thrid argument defaults to a blank, or it can be a single character or a character string. SELECT LASTNAME, LPAD(LASTNAME,20,'*') FROM CHARACTERS;

LTRIM and RTRIM

LTRIM and RTRIM take at most two arguments. The first argument is a character string. The optional second element is either a character or a character string, or defaults to a blank. If you use a second argument that is not a blank, the trim functions will trim that character the same way they trim the blanks in the following example. SELECT LASTNAME, RTRIM(LASTNAME) FROM CHARACTERS; ---- SELECT LASTNAME, RTRIM(LASTNAME, 'C') FROM CHARACTERS;

REPLACE

Replaces data. Of its three arguments, the first is the string to be searched. The second is the search key. The last is the optional replacement string. If the third argument is left out or NULL, each occurrence of the search key on the string to be searched is removed and is not replaced with anything.

SIGN

Returns -1 if its argument is less than 0, 0 if the argument is equal to 0 and 1 if its argument is greater than 0. SELECT A, SIGN(A) FROM NUMBERS;

SQRT

Returns the square root of an argument SELECT A, SQRT(A) FROM NUMBERS;

SYSDATE

Returns the system time and date SELECT * FROM PROJECT WHERE STARTDATE>SYSDATE;

STDDEV

STDDEV find the standard deviation of a column of numbers SELECT STDDEV(HITS) FROM TEAMSTATS; Only works with numbers

TO_NUMBER

TO_NUMBER is the companion function to TO_CHAR, and of course, it converts a string into a number. SELECT NAME, TESTNUM, TESTNUM*TO_NUMBER(NAME) FROM CONVERT;

COUNT

The function COUNT returns the number of rows that satisfy the condition in the WHERE clause. SELECT COUNT(*) FROM TEAMSTATS WHERE HITS/AB < 0.35; Can also use COUNT on a column name

SUM

The function SUM returns the sum of all values in a column SELECT SUM(SINGLES) FROM TEAMSTATS; To compute an average SELECT SUM(HITS)/SUM(AB) FROM TEAMSTATS; Cannot sum a nonnumerical field

TRANSLATE

The function TRANSLATE take three arguments: the target string, the FROM string, and the TO string. Elements of the target string that occur in the FROM string are translated to the corresponding element in the TO string. SELECT FIRSTNAME, TRANSLATE(FIRSTNAME '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ 'NNNNNNNNNNAAAAAAAAAAAAAAAAAAAAAAAAAAA) FROM CHARACTERS;

Modulo (%)

The modulo operator returns the integer remainder of the division operation SELECT NUMERATOR, DENOMINATOR, NUMERATOR%DENOMINATOR REMAINDER FROM REMAINS;

TO_CHAR

The primary use of TO_CHAR is to convert a number into a character. Different implementation may also use it to convert other data types, like Date, into a character, or to include different formatting arguments. SELECT TESTNUM, TO_CHAR(TESTNUM) FROM CONVERT; A good test to is check the length; the length of a number would have returned an error. SELECT TESTNUM, LENGTH(TO_CHAR(TESTNUM)) FROM CONVERT;

MOD**

The same as Modulo (%). The following query that returns a table showing the remainder of A divided by B. SELECT A, B, MOD(A,B) FROM NUMBERS;

INSTR

To find out where in a string a particular pattern occurs, use INSTR. Its first argument is the target string. The second argument is the pattern to match. The third and fourth arguments are numbers representing where to start looking and which match to report. SELECT LASTNAME, INSTR(LASTNAME, 'O', 2, 1) FROM CHARACTERS;

USER

USER returns the character name of the current user of the database SELECT USER FROM CONVERT;

Less Than (<) and Less Than or Equal To (<=)

Used in a WHERE clause to select a range of values less than a value or less than and including the value itself. SELECT * FROM FRIENDS WHERE STATE > 'la'; Can also be used on a character field. Uppercase is usually sorted before lowercase; therefore, the uppercase codes return are less than 'la'.

Equal Sign (=)

Used in a WHERE clause to select a single value SELECT * FROM FRIENDS WHERE FIRSTNAME = 'JD';

VARIANCE

VARIANCE produces the square of the standard deviation. SELECT VARIANCE(HITS) FROM TEAMSTATS; Only works with numbers

Inequalities (<> or !=)

When you need to find everything except for certain data. SELECT * FROM FRIENDS WHERE FIRSTNAME <> 'AL'; or SELECT * FROM FRIENDS WHERE FIRSTNAME != 'AL';