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';