SQL> SELECT * FROM TEAMSTATS;
NAME POS AB HITS WALKS SINGLES DOUBLES TRIPLES HR SO --------- --- --- ---- ----- ------- ------- ------- -- -- JONES 1B 145 45 34 31 8 1 5 10 DONKNOW 3B 175 65 23 50 10 1 4 15 WORLEY LF 157 49 15 35 8 3 3 16 DAVID OF 187 70 24 48 4 0 17 42 HAMHOCKER 3B 50 12 10 10 2 0 0 13 CASEY DH 1 0 0 0 0 0 0 1 6 rows selected.
SQL> SELECT COUNT(*) 2 FROM TEAMSTATS 3 WHERE HITS/AB < .35; COUNT(*) -------- 4To make the code more readable, try an alias:
SQL> SELECT COUNT(*) NUM_BELOW_350 2 FROM TEAMSTATS 3 WHERE HITS/AB < .35; NUM_BELOW_350 ------------- 4Would it make any difference if you tried a column name instead of the asterisk? (Notice the use of parentheses around the column names.) Try this:
SQL> SELECT COUNT(NAME) NUM_BELOW_350 2 FROM TEAMSTATS 3 WHERE HITS/AB < .35; NUM_BELOW_350 ------------- 4The answer is no. The NAME column that you selected was not involved in the WHERE statement. If you use COUNT without a WHERE clause, it returns the number of records in the table.
SQL> SELECT COUNT(*) 2 FROM TEAMSTATS; COUNT(*) --------- 6
SQL> SELECT SUM(SINGLES) TOTAL_SINGLES 2 FROM TEAMSTATS;
TOTAL_SINGLES ------------- 174To get several sums, use
SQL> SELECT SUM(SINGLES) TOTAL_SINGLES, SUM(DOUBLES) TOTAL_DOUBLES, SUM(TRIPLES) TOTAL_TRIPLES, SUM(HR) TOTAL_HR 2 FROM TEAMSTATS; TOTAL_SINGLES TOTAL_DOUBLES TOTAL_TRIPLES TOTAL_HR ------------- ------------- ------------- -------- 174 32 5 29To collect similar information on all 300 or better players, type
SQL> SELECT SUM(SINGLES) TOTAL_SINGLES, SUM(DOUBLES) TOTAL_DOUBLES, SUM(TRIPLES) TOTAL_TRIPLES, SUM(HR) TOTAL_HR 2 FROM TEAMSTATS 3 WHERE HITS/AB >= .300; TOTAL_SINGLES TOTAL_DOUBLES TOTAL_TRIPLES TOTAL_HR ------------- ------------- ------------- -------- 164 30 5 29To compute a team batting average, type
SQL> SELECT SUM(HITS)/SUM(AB) TEAM_AVERAGE 2 FROM TEAMSTATS; TEAM_AVERAGE ------------ .33706294SUM works only with numbers. If you try it on a nonnumerical field, you get
SQL> SELECT SUM(NAME) 2 FROM TEAMSTATS; ERROR: ORA-01722: invalid number no rows selectedThis error message is logical because you cannot sum a group of names.
SQL> SELECT AVG(SO) AVE_STRIKE_OUTS 2 FROM TEAMSTATS;
AVE_STRIKE_OUTS --------------- 16.166667The following example illustrates the difference between SUM and AVG:
SQL> SELECT AVG(HITS/AB) TEAM_AVERAGE 2 FROM TEAMSTATS; TEAM_AVERAGE ------------ .26803448
SQL> SELECT AVG(HITS)/AVG(AB) TEAM_AVERAGE 2 FROM TEAMSTATS;
TEAM_AVERAGE ------------ .33706294Like the SUM function, AVG works only with numbers.
SQL> SELECT MAX(HITS) 2 FROM TEAMSTATS;
MAX(HITS) --------- 70Can you find out who has the most hits?
SQL> SELECT NAME 2 FROM TEAMSTATS 3 WHERE HITS = MAX(HITS); ERROR at line 3: ORA-00934: group function is not allowed hereUnfortunately, you can't. The error message is a reminder that this group function (remember that aggregate functions are also called group functions) does not work in the WHERE clause. Don't despair, Day 7, "Subqueries: The Embedded SELECT Statement," covers the concept of subqueries and explains a way to find who has the MAX hits.
What happens if you try a nonnumerical column?
SQL> SELECT MAX(NAME) 2 FROM TEAMSTATS;
MAX(NAME) --------------- WORLEYHere's something new. MAX returns the highest (closest to Z) string. Finally, a function that works with both characters and numbers.
SQL> SELECT MIN(AB) 2 FROM TEAMSTATS;
MIN(AB) --------- 1The following statement returns the name closest to the beginning of the alphabet:
SQL> SELECT MIN(NAME) 2 FROM TEAMSTATS; MIN(NAME) --------------- CASEYYou can combine MIN with MAX to give a range of values. For example:
SQL> SELECT MIN(AB), MAX(AB) 2 FROM TEAMSTATS; MIN(AB) MAX(AB) -------- -------- 1 187This sort of information can be useful when using statistical functions.
NOTE: As we mentioned in the introduction, the first five aggregate functions are described in the ANSI standard. The remaining aggregate functions have become de facto standards, present in all important implementations of SQL. We use the Oracle7 names for these functions. Other implementations may use different names.
SQL> SELECT VARIANCE(HITS) 2 FROM TEAMSTATS;
VARIANCE(HITS) -------------- 802.96667If you try a string
SQL> SELECT VARIANCE(NAME) 2 FROM TEAMSTATS; ERROR: ORA-01722: invalid number no rows selectedyou find that VARIANCE is another function that works exclusively with numbers.
SQL> SELECT STDDEV(HITS) 2 FROM TEAMSTATS;
STDDEV(HITS) ------------ 28.336666It also returns an error when confronted by a string:
SQL> SELECT STDDEV(NAME) 2 FROM TEAMSTATS; ERROR: ORA-01722: invalid number no rows selectedThese aggregate functions can also be used in various combinations:
SQL> SELECT COUNT(AB), 2 AVG(AB), 3 MIN(AB), 4 MAX(AB), 5 STDDEV(AB), 6 VARIANCE(AB), 7 SUM(AB) 8 FROM TEAMSTATS; COUNT(AB) AVG(AB) MIN(AB) MAX(AB) STDDEV(AB) VARIANCE(AB) SUM(AB) --------- ------- ------- ------- ---------- ------------ ------- 6 119.167 1 187 75.589 5712.97 715The next time you hear a sportscaster use statistics to fill the time between plays, you will know that SQL is at work somewhere behind the scenes.
SQL> SELECT * FROM PROJECT;
TASK STARTDATE ENDDATE -------------- --------- --------- KICKOFF MTG 01-APR-95 01-APR-95 TECH SURVEY 02-APR-95 01-MAY-95 USER MTGS 15-MAY-95 30-MAY-95 DESIGN WIDGET 01-JUN-95 30-JUN-95 CODE WIDGET 01-JUL-95 02-SEP-95 TESTING 03-SEP-95 17-JAN-96
6 rows selected.
NOTE: This table used the Date data type. Most implementations of SQL have a Date data type, but the exact syntax may vary.
SQL> SELECT TASK, 2 STARTDATE, 3 ENDDATE ORIGINAL_END, 4 ADD_MONTHS(ENDDATE,2) 5 FROM PROJECT;
TASK STARTDATE ORIGINAL_ ADD_MONTH -------------- --------- --------- --------- KICKOFF MTG 01-APR-95 01-APR-95 01-JUN-95 TECH SURVEY 02-APR-95 01-MAY-95 01-JUL-95 USER MTGS 15-MAY-95 30-MAY-95 30-JUL-95 DESIGN WIDGET 01-JUN-95 30-JUN-95 31-AUG-95 CODE WIDGET 01-JUL-95 02-SEP-95 02-NOV-95 TESTING 03-SEP-95 17-JAN-96 17-MAR-96 6 rows selected.Not that a slip like this is possible, but it's nice to have a function that makes it so easy. ADD_MONTHS also works outside the SELECT clause. Typing
SQL> SELECT TASK TASKS_SHORTER_THAN_ONE_MONTH 2 FROM PROJECT 3 WHERE ADD_MONTHS(STARTDATE,1) > ENDDATE;produces the following result:
TASKS_SHORTER_THAN_ONE_MONTH ---------------------------- KICKOFF MTG TECH SURVEY USER MTGS DESIGN WIDGET
SQL> SELECT ENDDATE, LAST_DAY(ENDDATE) 2 FROM PROJECT;Here's the result:
ENDDATE LAST_DAY(ENDDATE) --------- ----------------- 01-APR-95 30-APR-95 01-MAY-95 31-MAY-95 30-MAY-95 31-MAY-95 30-JUN-95 30-JUN-95 02-SEP-95 30-SEP-95 17-JAN-96 31-JAN-96
6 rows selected.How does LAST DAY handle leap years?
SQL> SELECT LAST_DAY('1-FEB-95') NON_LEAP, 2 LAST_DAY('1-FEB-96') LEAP 3 FROM PROJECT; NON_LEAP LEAP --------- --------- 28-FEB-95 29-FEB-96 28-FEB-95 29-FEB-96 28-FEB-95 29-FEB-96 28-FEB-95 29-FEB-96 28-FEB-95 29-FEB-96 28-FEB-95 29-FEB-96
6 rows selected.
SQL> SELECT DISTINCT LAST_DAY('1-FEB-95') NON_LEAP, 2 LAST_DAY('1-FEB-96') LEAP 3 FROM PROJECT;This statement uses the word DISTINCT (see Day 2, "Introduction to the Query: The SELECT Statement") to produce the singular result
NON_LEAP LEAP --------- --------- 28-FEB-95 29-FEB-96Unlike me, this function knows which years are leap years. But before you trust your own or your company's financial future to this or any other function, check your implementation!
SQL> SELECT TASK, STARTDATE, ENDDATE,MONTHS_BETWEEN(STARTDATE,ENDDATE) DURATION 2 FROM PROJECT;
TASK STARTDATE ENDDATE DURATION -------------- --------- --------- --------- KICKOFF MTG 01-APR-95 01-APR-95 0 TECH SURVEY 02-APR-95 01-MAY-95 -.9677419 USER MTGS 15-MAY-95 30-MAY-95 -.483871 DESIGN WIDGET 01-JUN-95 30-JUN-95 -.9354839 CODE WIDGET 01-JUL-95 02-SEP-95 -2.032258 TESTING 03-SEP-95 17-JAN-96 -4.451613
6 rows selected.Wait a minute--that doesn't look right. Try this:
SQL> SELECT TASK, STARTDATE, ENDDATE, 2 MONTHS_BETWEEN(ENDDATE,STARTDATE) DURATION 3 FROM PROJECT; TASK STARTDATE ENDDATE DURATION -------------- --------- --------- --------- KICKOFF MTG 01-APR-95 01-APR-95 0 TECH SURVEY 02-APR-95 01-MAY-95 .96774194 USER MTGS 15-MAY-95 30-MAY-95 .48387097 DESIGN WIDGET 01-JUN-95 30-JUN-95 .93548387 CODE WIDGET 01-JUL-95 02-SEP-95 2.0322581 TESTING 03-SEP-95 17-JAN-96 4.4516129
6 rows selected.
SQL> SELECT * 2 FROM PROJECT 3 WHERE MONTHS_BETWEEN('19 MAY 95', STARTDATE) > 0;
TASK STARTDATE ENDDATE -------------- --------- --------- KICKOFF MTG 01-APR-95 01-APR-95 TECH SURVEY 02-APR-95 01-MAY-95 USER MTGS 15-MAY-95 30-MAY-95
Abbreviation | Time Zone |
AST or ADT | Atlantic standard or daylight time |
BST or BDT | Bering standard or daylight time |
CST or CDT | Central standard or daylight time |
EST or EDT | Eastern standard or daylight time |
GMT | Greenwich mean time |
HST or HDT | Alaska-Hawaii standard or daylight time |
MST or MDT | Mountain standard or daylight time |
NST | Newfoundland standard time |
PST or PDT | Pacific standard or daylight time |
YST or YDT | Yukon standard or daylight time |
SQL> SELECT ENDDATE EDT, 2 NEW_TIME(ENDDATE, 'EDT','PDT') 3 FROM PROJECT;
EDT NEW_TIME(ENDDATE ---------------- ---------------- 01-APR-95 1200AM 31-MAR-95 0900PM 01-MAY-95 1200AM 30-APR-95 0900PM 30-MAY-95 1200AM 29-MAY-95 0900PM 30-JUN-95 1200AM 29-JUN-95 0900PM 02-SEP-95 1200AM 01-SEP-95 0900PM 17-JAN-96 1200AM 16-JAN-96 0900PM
6 rows selected.Like magic, all the times are in the new time zone and the dates are adjusted.
SQL> SELECT STARTDATE, 2 NEXT_DAY(STARTDATE, 'FRIDAY') 3 FROM PROJECT;which would return
STARTDATE NEXT_DAY( --------- --------- 01-APR-95 07-APR-95 02-APR-95 07-APR-95 15-MAY-95 19-MAY-95 01-JUN-95 02-JUN-95 01-JUL-95 07-JUL-95 03-SEP-95 08-SEP-95
6 rows selected.
SQL> SELECT DISTINCT SYSDATE 2 FROM PROJECT;
SYSDATE ---------------- 18-JUN-95 1020PMIf you wanted to see where you stood today in a certain project, you could type
SQL> SELECT * 2 FROM PROJECT 3 WHERE STARTDATE > SYSDATE; TASK STARTDATE ENDDATE -------------- --------- --------- CODE WIDGET 01-JUL-95 02-SEP-95 TESTING 03-SEP-95 17-JAN-96Now you can see what parts of the project start after today.
SQL> SELECT * 2 FROM NUMBERS;
A B --------- --------- 3.1415 4 -45 .707 5 9 -57.667 42 15 55 -7.2 5.3 6 rows selected.
SQL> SELECT ABS(A) ABSOLUTE_VALUE 2 FROM NUMBERS;
ABSOLUTE_VALUE -------------- 3.1415 45 5 57.667 15 7.2 6 rows selected.ABS changes all the negative numbers to positive and leaves positive numbers alone.
SQL> SELECT B, CEIL(B) CEILING 2 FROM NUMBERS;
B CEILING --------- --------- 4 4 .707 1 9 9 42 42 55 55 5.3 6
6 rows selected.And
SQL> SELECT A, FLOOR(A) FLOOR 2 FROM NUMBERS; A FLOOR --------- --------- 3.1415 3 -45 -45 5 5 -57.667 -58 15 15 -7.2 -8
6 rows selected.
SQL> SELECT A, COS(A) 2 FROM NUMBERS;
A COS(A) --------- --------- 3.1415 -1 -45 .52532199 5 .28366219 -57.667 .437183 15 -.7596879 -7.2 .60835131
SQL> SELECT A, COS(A* 0.01745329251994) 2 FROM NUMBERS;
A COS(A*0.01745329251994) --------- ----------------------- 3.1415 .99849724 -45 .70710678 5 .9961947 -57.667 .5348391 15 .96592583 -7.2 .9921147
SQL> SELECT A, COS(A*0.017453), COSH(A*0.017453) 2 FROM NUMBERS;
A COS(A*0.017453) COSH(A*0.017453) --------- --------------- ---------------- 3.1415 .99849729 1.0015035 -45 .70711609 1.3245977 5 .99619483 1.00381 -57.667 .53485335 1.5507072 15 .96592696 1.0344645 -7.2 .99211497 1.0079058
6 rows selected.And
SQL> SELECT A, SIN(A*0.017453), SINH(A*0.017453) 2 FROM NUMBERS;
A SIN(A*0.017453) SINH(A*0.017453) --------- --------------- ---------------- 3.1415 .05480113 .05485607 -45 -.7070975 -.8686535 5 .08715429 .0873758 -57.667 -.8449449 -1.185197 15 .25881481 .26479569 -7.2 -.1253311 -.1259926
6 rows selected.And
SQL> SELECT A, TAN(A*0.017453), TANH(A*0.017453) 2 FROM NUMBERS; A TAN(A*0.017453) TANH(A*0.017453) --------- --------------- ---------------- 3.1415 .05488361 .05477372 -45 -.9999737 -.6557867 5 .08748719 .08704416 -57.667 -1.579769 -.7642948 15 .26794449 .25597369 -7.2 -.1263272 -.1250043
6 rows selected.
SQL> SELECT A, EXP(A) 2 FROM NUMBERS;
A EXP(A) --------- --------- 3.1415 23.138549 -45 2.863E-20 5 148.41316 -57.667 9.027E-26 15 3269017.4 -7.2 .00074659
6 rows selected.
SQL> SELECT A, LN(A) 2 FROM NUMBERS;
ERROR: ORA-01428: argument '-45' is out of rangeDid we neglect to mention that the argument had to be positive? Write
SQL> SELECT A, LN(ABS(A)) 2 FROM NUMBERS; A LN(ABS(A)) --------- ---------- 3.1415 1.1447004 -45 3.8066625 5 1.6094379 -57.667 4.0546851 15 2.7080502 -7.2 1.974081
6 rows selected.
SQL> SELECT B, LOG(B, 10) 2 FROM NUMBERS; B LOG(B,10) ----------- --------- 4 1.660964 .707 -6.640962 9 1.0479516 42 .61604832 55 .57459287 5.3 1.3806894
6 rows selected.
SQL> SELECT A, B, MOD(A,B) 2 FROM NUMBERS;
A B MOD(A,B) --------- --------- --------- 3.1415 4 3.1415 -45 .707 -.459 5 9 5 -57.667 42 -15.667 15 55 15 -7.2 5.3 -1.9
6 rows selected.
SQL> SELECT A, B, POWER(A,B) 2 FROM NUMBERS;
ERROR: ORA-01428: argument '-45' is out of range
SQL> SELECT A, CEIL(B), POWER(A,CEIL(B)) 2 FROM NUMBERS;
A CEIL(B) POWER(A,CEIL(B)) --------- --------- ---------------- 3.1415 4 97.3976 -45 1 -45 5 9 1953125 -57.667 42 9.098E+73 15 55 4.842E+64 -7.2 6 139314.07
6 rows selected.That's better!
SQL> SELECT A, SIGN(A) 2 FROM NUMBERS;
A SIGN(A) --------- --------- 3.1415 1 -45 -1 5 1 -57.667 -1 15 1 -7.2 -1 0 0
7 rows selected.You could also use SIGN in a SELECT WHERE clause like this:
SQL> SELECT A 2 FROM NUMBERS 3 WHERE SIGN(A) = 1;
A --------- 3.1415 5 15
SQL> SELECT A, SQRT(A) 2 FROM NUMBERS;
ERROR: ORA-01428: argument '-45' is out of rangeHowever, you can fix this limitation with ABS:
SQL> SELECT ABS(A), SQRT(ABS(A)) 2 FROM NUMBERS; ABS(A) SQRT(ABS(A)) --------- ------------ 3.1415 1.7724277 45 6.7082039 5 2.236068 57.667 7.5938791 15 3.8729833 7.2 2.6832816 0 0
7 rows selected.
SQL> SELECT * FROM CHARACTERS;
LASTNAME FIRSTNAME M CODE --------------- --------------- - --------- PURVIS KELLY A 32 TAYLOR CHUCK J 67 CHRISTINE LAURA C 65 ADAMS FESTER M 87 COSTALES ARMANDO A 77 KONG MAJOR G 52
6 rows selected.
SQL> SELECT CODE, CHR(CODE) 2 FROM CHARACTERS;
CODE CH --------- -- 32 67 C 65 A 87 W 77 M 52 4
6 rows selected.The space opposite the 32 shows that 32 is a space in the ASCII character set.
SQL> SELECT CONCAT(FIRSTNAME, LASTNAME) "FIRST AND LAST NAMES" 2 FROM CHARACTERS;
FIRST AND LAST NAMES ------------------------ KELLY PURVIS CHUCK TAYLOR LAURA CHRISTINE FESTER ADAMS ARMANDO COSTALES MAJOR KONG 6 rows selected.
Also notice that even though the table looks like two separate columns, what you are seeing is one column. The first value you concatenated, FIRSTNAME, is 15 characters wide. This operation retained all the characters in the field.
SQL> SELECT FIRSTNAME BEFORE, INITCAP(FIRSTNAME) AFTER 2 FROM CHARACTERS;
BEFORE AFTER -------------- ---------- KELLY Kelly CHUCK Chuck LAURA Laura FESTER Fester ARMANDO Armando MAJOR Major
6 rows selected.
The following example starts by doing a little magic with the UPDATE function (you learn more about this next week) to change one of the values to lowercase:
SQL> UPDATE CHARACTERS 2 SET FIRSTNAME = 'kelly' 3 WHERE FIRSTNAME = 'KELLY';
1 row updated.
SQL> SELECT FIRSTNAME 2 FROM CHARACTERS;
FIRSTNAME --------------- kelly CHUCK LAURA FESTER ARMANDO MAJOR
6 rows selected.Then you write
SQL> SELECT FIRSTNAME, UPPER(FIRSTNAME), LOWER(FIRSTNAME) 2 FROM CHARACTERS;
FIRSTNAME UPPER(FIRSTNAME LOWER(FIRSTNAME --------------- --------------- --------------- kelly KELLY kelly CHUCK CHUCK chuck LAURA LAURA laura FESTER FESTER fester ARMANDO ARMANDO armando MAJOR MAJOR major
6 rows selected.Now you see the desired behavior.
SQL> SELECT LASTNAME, LPAD(LASTNAME,20,'*') 2 FROM CHARACTERS;
LASTNAME LPAD(LASTNAME,20,'*' -------------- -------------------- PURVIS *****PURVIS TAYLOR *****TAYLOR CHRISTINE *****CHRISTINE ADAMS *****ADAMS COSTALES *****COSTALES KONG *****KONG
6 rows selected.
SQL> SELECT LASTNAME, RPAD(LASTNAME,20,'*') 2 FROM CHARACTERS;
LASTNAME RPAD(LASTNAME,20,'*' --------------- -------------------- PURVIS PURVIS ***** TAYLOR TAYLOR ***** CHRISTINE CHRISTINE ***** ADAMS ADAMS ***** COSTALES COSTALES ***** KONG KONG *****
6 rows selected.
SQL> SELECT LASTNAME, RTRIM(LASTNAME) 2 FROM CHARACTERS;
LASTNAME RTRIM(LASTNAME) --------------- --------------- PURVIS PURVIS TAYLOR TAYLOR CHRISTINE CHRISTINE ADAMS ADAMS COSTALES COSTALES KONG KONG
6 rows selected.You can make sure that the characters have been trimmed with the following statement:
SQL> SELECT LASTNAME, RPAD(RTRIM(LASTNAME),20,'*') 2 FROM CHARACTERS;
LASTNAME RPAD(RTRIM(LASTNAME) --------------- -------------------- PURVIS PURVIS************** TAYLOR TAYLOR************** CHRISTINE CHRISTINE*********** ADAMS ADAMS*************** COSTALES COSTALES************ KONG KONG****************
6 rows selected.The output proves that trim is working. Now try LTRIM:
SQL> SELECT LASTNAME, LTRIM(LASTNAME, 'C') 2 FROM CHARACTERS;
LASTNAME LTRIM(LASTNAME, --------------- --------------- PURVIS PURVIS TAYLOR TAYLOR CHRISTINE HRISTINE ADAMS ADAMS COSTALES OSTALES KONG KONG
6 rows selected.Note the missing Cs in the third and fifth rows.
SQL> SELECT LASTNAME, REPLACE(LASTNAME, 'ST') REPLACEMENT 2 FROM CHARACTERS;
LASTNAME REPLACEMENT --------------- --------------- PURVIS PURVIS TAYLOR TAYLOR CHRISTINE CHRIINE ADAMS ADAMS COSTALES COALES KONG KONG
6 rows selected.If you have a third argument, it is substituted for each occurrence of the search key in the target string. For example:
SQL> SELECT LASTNAME, REPLACE(LASTNAME, 'ST','**') REPLACEMENT 2 FROM CHARACTERS;
LASTNAME REPLACEMENT --------------- ------------ PURVIS PURVIS TAYLOR TAYLOR CHRISTINE CHRI**INE ADAMS ADAMS COSTALES CO**ALES KONG KONG
6 rows selected.If the second argument is NULL, the target string is returned with no changes.
SQL> SELECT LASTNAME, REPLACE(LASTNAME, NULL) REPLACEMENT 2 FROM CHARACTERS;
LASTNAME REPLACEMENT --------------- --------------- PURVIS PURVIS TAYLOR TAYLOR CHRISTINE CHRISTINE ADAMS ADAMS COSTALES COSTALES KONG KONG
6 rows selected.
SQL> SELECT FIRSTNAME, SUBSTR(FIRSTNAME,2,3) 2 FROM CHARACTERS;
FIRSTNAME SUB --------------- --- kelly ell CHUCK HUC LAURA AUR FESTER EST ARMANDO RMA MAJOR AJO
6 rows selected.If you use a negative number as the second argument, the starting point is determined by counting backwards from the end, like this:
SQL> SELECT FIRSTNAME, SUBSTR(FIRSTNAME,-13,2) 2 FROM CHARACTERS;
FIRSTNAME SU --------------- -- kelly ll CHUCK UC LAURA UR FESTER ST ARMANDO MA MAJOR JO
6 rows selected.
SQL> SELECT FIRSTNAME, SUBSTR(FIRSTNAME,3) 2 FROM CHARACTERS;
FIRSTNAME SUBSTR(FIRSTN --------------- ------------- kelly lly CHUCK UCK LAURA URA FESTER STER ARMANDO MANDO MAJOR JOR
6 rows selected.The rest of the target string is returned.
SQL> SELECT * FROM SSN_TABLE;
SSN__________ 300541117 301457111 459789998
3 rows selected.
SQL> SELECT SUBSTR(SSN,1,3)||'-'||SUBSTR(SSN,4,2)||'-'||SUBSTR(SSN,6,4) SSN 2 FROM SSN_TABLE;
SSN_________ 300-54-1117 301-45-7111 459-78-9998
3 rows selected.
Here is another good use of the SUBSTR function. Suppose you are writing a report and a few columns are more than 50 characters wide. You can use the SUBSTR function to reduce the width of the columns to a more manageable size if you know the nature of the actual data. Consider the following two examples:
NOTE: This particular use of the substr function could come in very handy with large numbers using commas such as 1,343,178,128 and in area codes and phone numbers such as 317-787-2915 using dashes.
SQL> SELECT NAME, JOB, DEPARTMENT FROM JOB_TBL;
NAME______________________________________________________________ JOB_______________________________DEPARTMENT______________________ ALVIN SMITH VICEPRESIDENT MARKETING 1 ROW SELECTED.
SQL> SELECT SUBSTR(NAME, 1,15) NAME, SUBSTR(JOB,1,15) JOB, DEPARTMENT 2 FROM JOB_TBL;
NAME________________JOB_______________DEPARTMENT_____
ALVIN SMITH VICEPRESIDENT MARKETINGMuch better!
SQL> SELECT FIRSTNAME, TRANSLATE(FIRSTNAME 2 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ 3 'NNNNNNNNNNAAAAAAAAAAAAAAAAAAAAAAAAAA) 4 FROM CHARACTERS;
FIRSTNAME TRANSLATE(FIRST --------------- --------------- kelly kelly CHUCK AAAAA LAURA AAAAA FESTER AAAAAA ARMANDO AAAAAAA MAJOR AAAAA
6 rows selected.Notice that the function is case sensitive.
SQL> SELECT LASTNAME, INSTR(LASTNAME, 'O', 2, 1) 2 FROM CHARACTERS;
LASTNAME INSTR(LASTNAME,'O',2,1) --------------- ----------------------- PURVIS 0 TAYLOR 5 CHRISTINE 0 ADAMS 0 COSTALES 2 KONG 2
6 rows selected.
SQL> SELECT FIRSTNAME, LENGTH(RTRIM(FIRSTNAME)) 2 FROM CHARACTERS;
FIRSTNAME LENGTH(RTRIM(FIRSTNAME)) --------------- ------------------------ kelly 5 CHUCK 5 LAURA 5 FESTER 6 ARMANDO 7 MAJOR 5
6 rows selected.
SQL> SELECT * FROM CONVERSIONS;
NAME TESTNUM --------------- --------- 40 95 13 23 74 68The NAME column is a character string 15 characters wide, and TESTNUM is a number.
SQL> SELECT TESTNUM, TO_CHAR(TESTNUM) 2 FROM CONVERT;
TESTNUM TO_CHAR(TESTNUM) --------- ---------------- 95 95 23 23 68 68Not very exciting, or convincing. Here's how to verify that the function returned a character string:
SQL> SELECT TESTNUM, LENGTH(TO_CHAR(TESTNUM)) 2 FROM CONVERT;
TESTNUM LENGTH(TO_CHAR(TESTNUM)) --------- ------------------------ 95 2 23 2 68 2
SQL> SELECT NAME, TESTNUM, TESTNUM*TO_NUMBER(NAME) 2 FROM CONVERT;
NAME TESTNUM TESTNUM*TO_NUMBER(NAME) --------------- -------- ----------------------- 40 95 3800 13 23 299 74 68 5032
SQL> SELECT GREATEST('ALPHA', 'BRAVO','FOXTROT', 'DELTA') 2 FROM CONVERT;
GREATEST ------- FOXTROT FOXTROT FOXTROT
SQL> SELECT LEAST(34, 567, 3, 45, 1090) 2 FROM CONVERT;
LEAST(34,567,3,45,1090) ----------------------- 3 3 3As you can see, GREATEST and LEAST also work with numbers.
SQL> SELECT USER FROM CONVERT;
USER ------------------------------ PERKINS PERKINS PERKINSThere really is only one of me. Again, the echo occurs because of the number of rows in the table. USER is similar to the date functions explained earlier today. Even though USER is not an actual column in the table, it is selected for each row that is contained in the table.
A ANSI standards are broad strokes and are not meant to drive companies into bankruptcy by forcing all implementations to have dozens of functions. On the other hand, when company X adds a statistical package to its SQL and it sells well, you can bet company Y and Z will follow suit.
Q I thought you said SQL was simple. Will I really use all of these functions?
A The answer to this question is similar to the way a trigonometry teacher might respond to the question, Will I ever need to know how to figure the area of an isosceles triangle in real life? The answer, of course, depends on your profession. The same concept applies with the functions and all the other options available with SQL. How you use functions in SQL depends mostly on you company's needs. As long as you understand how functions work as a whole, you can apply the same concepts to your own queries.
2. Which functions are also known by the name group functions?
3. Will this query work?
SQL> SELECT COUNT(LASTNAME) FROM CHARACTERS;
SQL> SELECT SUM(LASTNAME) FROM CHARACTERS;
6. What does the answer 6 mean from the following SELECT?
SQL> SELECT COUNT(*) FROM TEAMSTATS;OUTPUT:
COUNT(*)
SQL> SELECT SUBSTR LASTNAME,1,5 FROM NAME_TBL;
2. Using today's CHARACTERS table, write a query that will return the following:
INITIALS__________CODE K.A.P. 32 1 row selected.