![]() |
|||
![]()
|
![]() |
![]() Click Here! |
![]() |
Teach Yourself SQL in 21 Days, Second Edition- Day 4 -
|
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 |
You can adjust your time like this:
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.
NEXT_DAY finds 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
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.
The output tells you the date of the first Friday that occurs after your STARTDATE.
SYSDATE returns the system time and date:
SQL> SELECT DISTINCT SYSDATE 2 FROM PROJECT;
SYSDATE ---------------- 18-JUN-95 1020PM
If 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-96
Now you can see what parts of the project start after today.
Many of the uses you have for the data you retrieve involve mathematics. Most implementations of SQL provide arithmetic functions similar to the functions covered here. The examples in this section use the NUMBERS table:
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.
The ABS function returns the absolute value of the number you point to. For example:
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.
CEIL returns the smallest integer greater than or equal to its argument. FLOOR does just the reverse, returning the largest integer equal to or less than its argument. For example:
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.
The COS, SIN, and TAN functions provide support for various trigonometric concepts. They all work on the assumption that n is in radians. The following statement returns some unexpected values if you don't realize COS expects A to be in radians.
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
You would expect the COS of 45 degrees to be in the neighborhood of .707, not .525. To make this function work the way you would expect it to in a degree-oriented world, you need to convert degrees to radians. (When was the last time you heard a news broadcast report that a politician had done a pi-radian turn? You hear about a 180-degree turn.) Because 360 degrees = 2 pi radians, you can write
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
Note that the number 0.01745329251994 is radians divided by degrees. The trigonometric functions work as follows:
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.
EXP enables you to raise e (e is a mathematical constant used in various formulas) to a power. Here's how EXP raises e by the values in column A:
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.
These two functions center on logarithms. LN returns the natural logarithm of its argument. For example:
SQL> SELECT A, LN(A) 2 FROM NUMBERS;
ERROR: ORA-01428: argument '-45' is out of range
Did 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.
Notice how you can embed the function ABS inside the LN call. The other logarith-mic function, LOG, takes two arguments, returning the logarithm of the first argument in the base of the second. The following query returns the logarithms of column B in base 10.
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.
You have encountered MOD before. On Day 3, "Expressions, Conditions, and Operators," you saw that the ANSI standard for the modulo operator % is sometimes implemented as the function MOD. Here's a query that returns a table showing the remainder of A divided by B:
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.
To raise one number to the power of another, use POWER. In this function the first argument is raised to the power of the second:
SQL> SELECT A, B, POWER(A,B) 2 FROM NUMBERS;
ERROR: ORA-01428: argument '-45' is out of range
At first glance you are likely to think that the first argument can't be negative. But that impression can't be true, because a number like -4 can be raised to a power. Therefore, if the first number in the POWER function is negative, the second must be an integer. You can work around this problem by using CEIL (or FLOOR):
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!
SIGN returns -1 if its argument is less than 0, 0 if its argument is equal to 0, and 1 if its argument is greater than 0, as shown in the following example:
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
The function SQRT returns the square root of an argument. Because the square root of a negative number is undefined, you cannot use SQRT on negative numbers.
SQL> SELECT A, SQRT(A) 2 FROM NUMBERS;
ERROR: ORA-01428: argument '-45' is out of range
However, 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.
Many implementations of SQL provide functions to manipulate characters and strings of characters. This section covers the most common character functions. The examples in this section use the table CHARACTERS.
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.
CHR returns the character equivalent of the number it uses as an argument. The character it returns depends on the character set of the database. For this example the database is set to ASCII. The column CODE includes numbers.
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.
You used the equivalent of this function on Day 3, when you learned about operators. The || symbol splices two strings together, as does CONCAT. It works like this:
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.
Quotation marks surround the multiple-word alias FIRST AND LAST NAMES. Again, it is safest to check your implementation to see if it allows multiple-word aliases.
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.
INITCAP capitalizes the first letter of a word and makes all other characters lowercase.
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.
As you might expect, LOWER changes all the characters to lowercase; UPPER does just the reverse.
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.
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 character to pad it with. The third argument defaults to a blank, or it can be a single character or a character string. The following statement adds five pad characters, assuming that the field LASTNAME is defined as a 15-character field:
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.
Why were only five pad characters added? Remember that the LASTNAME column is 15 characters wide and that LASTNAME includes the blanks to the right of the characters that make up the name. Some column data types eliminate padding characters if the width of the column value is less than the total width allocated for the column. Check your implementation. Now try the right side:
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.
Here you see that the blanks are considered part of the field name for these operations. The next two functions come in handy in this type of situation.
LTRIM and RTRIM take at least one and at most two arguments. The first argument, like LPAD and RPAD, is a character string. The optional second element is either a character or character string or defaults to a blank. If you use a second argument that is not a blank, these trim functions will trim that character the same way they trim the blanks in the following examples.
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.
REPLACE does just that. 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.
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.
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.
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.
Remember the character field FIRSTNAME in this example is 15 characters long. That is why you used a -13 to start at the third character. Counting back from 15 puts you at the start of the third character, not at the start of the second. If you don't have a third argument, use the following statement instead:
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.
Reading the results of the preceding output is difficult--Social Security numbers usually have dashes. Now try something fancy and see whether you like the results:
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.
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.
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:
SQL> SELECT NAME, JOB, DEPARTMENT FROM JOB_TBL;
NAME______________________________________________________________ JOB_______________________________DEPARTMENT______________________ ALVIN SMITH VICEPRESIDENT MARKETING 1 ROW SELECTED.
Notice how the columns wrapped around, which makes reading the results a little too difficult. Now try this select:
SQL> SELECT SUBSTR(NAME, 1,15) NAME, SUBSTR(JOB,1,15) JOB, DEPARTMENT 2 FROM JOB_TBL;
NAME________________JOB_______________DEPARTMENT_____
ALVIN SMITH VICEPRESIDENT MARKETING
Much better!
The function TRANSLATE takes 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.
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.
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 forth are numbers representing where to start looking and which match to report. This example returns a number representing the first occurrence of O starting with the second character:
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.
The default for the third and fourth arguments is 1. If the third argument is negative, the search starts at a position determined from the end of the string, instead of from the beginning.
LENGTH returns the length of its lone character argument. For example:
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.
Note the use of the RTRIM function. Otherwise, LENGTH would return 15 for every value.
These three conversion functions provide a handy way of converting one type of data to another. These examples use the table CONVERSIONS.
SQL> SELECT * FROM CONVERSIONS;
NAME TESTNUM --------------- --------- 40 95 13 23 74 68
The NAME column is a character string 15 characters wide, and TESTNUM is a number.
The primary use of TO_CHAR is to convert a number into a character. Different implementations may also use it to convert other data types, like Date, into a character, or to include different formatting arguments. The next example illustrates the primary use of TO_CHAR:
SQL> SELECT TESTNUM, TO_CHAR(TESTNUM) 2 FROM CONVERT;
TESTNUM TO_CHAR(TESTNUM) --------- ---------------- 95 95 23 23 68 68
Not 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
LENGTH of a number would have returned an error. Notice the difference between TO CHAR and the CHR function discussed earlier. CHR would have turned this number into a character or a symbol, depending on the character set.
TO_NUMBER is the companion function to TO_CHAR, and of course, it converts a string into a number. For example:
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
This test would have returned an error if TO_NUMBER had returned a character.
Here are three miscellaneous functions you may find useful.
These functions find the GREATEST or the LEAST member from a series of expressions. For example:
SQL> SELECT GREATEST('ALPHA', 'BRAVO','FOXTROT', 'DELTA') 2 FROM CONVERT;
GREATEST ------- FOXTROT FOXTROT FOXTROT
Notice GREATEST found the word closest to the end of the alphabet. Notice also a seemingly unnecessary FROM and three occurrences of FOXTROT. If FROM is missing, you will get an error. Every SELECT needs a FROM. The particular table used in the FROM has three rows, so the function in the SELECT clause is performed for each of them.
SQL> SELECT LEAST(34, 567, 3, 45, 1090) 2 FROM CONVERT;
LEAST(34,567,3,45,1090) ----------------------- 3 3 3
As you can see, GREATEST and LEAST also work with numbers.
USER returns the character name of the current user of the database.
SQL> SELECT USER FROM CONVERT;
USER ------------------------------ PERKINS PERKINS PERKINS
There 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.
It has been a long day. We covered 47 functions--from aggregates to conversions. You don't have to remember every function--just knowing the general types (aggregate functions, date and time functions, arithmetic functions, character functions, conversion functions, and miscellaneous functions) is enough to point you in the right direction when you build a query that requires a function.
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.
The Workshop provides quiz questions to help solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you have learned. Try to answer the quiz and exercise questions before checking the answers in Appendix F, "Answers to Quizzes and Exercises."
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.
© Copyright, Macmillan Computer Publishing. All rights reserved.
![]() |
|
Use of this site is subject certain Terms & Conditions. Copyright (c) 1996-1999 EarthWeb, Inc.. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Please read our privacy policy for details. |