NOTE: We used Oracle's Personal Oracle7 to generate today's examples. Other implementations of SQL may differ slightly in the way in which commands are entered or output is displayed, but the results are basically the same for all implementations that conform to the ANSI standard.
SELECT amount FROM checks;In the following statement NAME, ADDRESS, PHONE and ADDRESSBOOK are expressions:
SELECT NAME, ADDRESS, PHONE FROM ADDRESSBOOK;Now, examine the following expression:
WHERE NAME = 'BROWN'It contains a condition, NAME = 'BROWN', which is an example of a Boolean expression. NAME = 'BROWN' will be either TRUE or FALSE, depending on the condition =.
NAME = 'BROWN'To find everyone in your organization who worked more than 100 hours last month, your condition would be
NUMBEROFHOURS > 100Conditions enable you to make selective queries. In their most common form, conditions comprise a variable, a constant, and a comparison operator. In the first example the variable is NAME, the constant is 'BROWN', and the comparison operator is =. In the second example the variable is NUMBEROFHOURS, the constant is 100, and the comparison operator is >. You need to know about two more elements before you can write conditional queries: the WHERE clause and operators.
WHERE <SEARCH CONDITION>SELECT, FROM, and WHERE are the three most frequently used clauses in SQL. WHERE simply causes your queries to be more selective. Without the WHERE clause, the most useful thing you could do with a query is display all records in the selected table(s). For example:
SQL> SELECT * FROM BIKES;lists all rows of data in the table BIKES.
NAME FRAMESIZE COMPOSITION MILESRIDDEN TYPE -------------- --------- ------------ ----------- ------- TREK 2300 22.5 CARBON FIBER 3500 RACING BURLEY 22 STEEL 2000 TANDEM GIANT 19 STEEL 1500 COMMUTER FUJI 20 STEEL 500 TOURING SPECIALIZED 16 STEEL 100 MOUNTAIN CANNONDALE 22.5 ALUMINUM 3000 RACING
6 rows selected.If you wanted a particular bike, you could type
SQL> SELECT * FROM BIKES WHERE NAME = 'BURLEY';which would yield only one record:
NAME FRAMESIZE COMPOSITION MILESRIDDEN TYPE -------------- --------- -------------- ----------- ------- BURLEY 22 STEEL 2000 TANDEM
5 % 2 = 1 6 % 2 = 0The modulo operator does not work with data types that have decimals, such as Real or Number.
If you place several of these arithmetic operators in an expression without any parentheses, the operators are resolved in this order: multiplication, division, modulo, addition, and subtraction. For example, the expression
2*6+9/3equals
12 + 3 = 15However, the expression
2 * (6 + 9) / 3equals
2 * 15 / 3 = 10Watch where you put those parentheses! Sometimes the expression does exactly what you tell it to do, rather than what you want it to do.
The following sections examine the arithmetic operators in some detail and give you a chance to write some queries.
SQL> SELECT * FROM PRICE;
ITEM WHOLESALE -------------- ---------- TOMATOES .34 POTATOES .51 BANANAS .67 TURNIPS .45 CHEESE .89 APPLES .23
6 rows selected.Now type:
SQL> SELECT ITEM, WHOLESALE, WHOLESALE + 0.15 FROM PRICE;Here the + adds 15 cents to each price to produce the following:
ITEM WHOLESALE WHOLESALE+0.15 -------------- --------- -------------- TOMATOES .34 .49 POTATOES .51 .66 BANANAS .67 .82 TURNIPS .45 .60 CHEESE .89 1.04 APPLES .23 .38
6 rows selected.
Retype the original entry:
SQL> SELECT * FROM PRICE;The following table results:
ITEM WHOLESALE -------------- --------- TOMATOES .34 POTATOES .51 BANANAS .67 TURNIPS .45 CHEESE .89 APPLES .23
6 rows selected.
Type the following:
SQL> SELECT ITEM, WHOLESALE, (WHOLESALE + 0.15) RETAIL FROM PRICE;Here's the result:
ITEM WHOLESALE RETAIL -------------- --------- ------ TOMATOES .34 .49 POTATOES .51 .66 BANANAS .67 .82 TURNIPS .45 .60 CHEESE .89 1.04 APPLES .23 .38
6 rows selected.
For example, the query
SQL> SELECT ITEM PRODUCE, WHOLESALE, WHOLESALE + 0.25 RETAIL FROM PRICE;renames the columns as follows:
PRODUCE WHOLESALE RETAIL -------------- --------- --------- TOMATOES .34 .59 POTATOES .51 .76 BANANAS .67 .92 TURNIPS .45 .70 CHEESE .89 1.14 APPLES .23 .48
You might be wondering what use aliasing is if you are not using command-line SQL. Fair enough. Have you ever wondered how report builders work? Someday, when you are asked to write a report generator, you'll remember this and not spend weeks reinventing what Dr. Codd and IBM have wrought.
NOTE: Some implementations of SQL use the syntax <column name = alias>. The preceding example would be written as follows:SQL> SELECT ITEM = PRODUCE, WHOLESALE, WHOLESALE + 0.25 = RETAIL, FROM PRICE;Check your implementation for the exact syntax.
So far, you have seen two uses of the plus sign. The first instance was the use of the plus sign in the SELECT clause to perform a calculation on the data and display the calculation. The second use of the plus sign is in the WHERE clause. Using operators in the WHERE clause gives you more flexibility when you specify conditions for retrieving data.
In some implementations of SQL, the plus sign does double duty as a character operator. You'll see that side of the plus a little later today.
SQL> SELECT * FROM HILOW;
STATE HIGHTEMP LOWTEMP ---------- -------- --------- CA -50 120 FL 20 110 LA 15 99 ND -70 101 NE -60 100For example, here's a way to manipulate the data:
SQL> SELECT STATE, -HIGHTEMP LOWS, -LOWTEMP HIGHS FROM HILOW; STATE LOWS HIGHS ---------- -------- --------- CA 50 -120 FL -20 -110 LA -15 -99 ND 70 -101 NE 60 -100The second (and obvious) use of the minus sign is to subtract one column from another. For example:
SQL> SELECT STATE, 2 HIGHTEMP LOWS, 3 LOWTEMP HIGHS, 4 (LOWTEMP - HIGHTEMP) DIFFERENCE 5 FROM HILOW; STATE LOWS HIGHS DIFFERENCE ---------- -------- -------- ---------- CA -50 120 170 FL 20 110 90 LA 15 99 84 ND -70 101 171 NE -60 100 160Notice the use of aliases to fix the data that was entered incorrectly. This remedy is merely a temporary patch, though, and not a permanent fix. You should see to it that the data is corrected and entered correctly in the future. On Day 21, "Common SQL Mistakes/Errors and Resolutions," you'll learn how to correct bad data.
This query not only fixed (at least visually) the incorrect data but also created a new column containing the difference between the highs and lows of each state.
If you accidentally use the minus sign on a character field, you get something like this:
SQL> SELECT -STATE FROM HILOW; ERROR: ORA-01722: invalid number no rows selectedThe exact error message varies with implementation, but the result is the same.
SQL> SELECT * FROM PRICE;
ITEM WHOLESALE -------------- --------- TOMATOES .34 POTATOES .51 BANANAS .67 TURNIPS .45 CHEESE .89 APPLES .23
6 rows selected.You can show the effects of a two-for-one sale by typing the next statement:
SQL> SELECT ITEM, WHOLESALE, (WHOLESALE/2) SALEPRICE 2 FROM PRICE; ITEM WHOLESALE SALEPRICE -------------- --------- --------- TOMATOES .34 .17 POTATOES .51 .255 BANANAS .67 .335 TURNIPS .45 .225 CHEESE .89 .445 APPLES .23 .115
6 rows selected.The use of division in the preceding SELECT statement is straightforward (except that coming up with half pennies can be tough).
SQL> SELECT * FROM PRICE;
ITEM WHOLESALE -------------- --------- TOMATOES .34 POTATOES .51 BANANAS .67 TURNIPS .45 CHEESE .89 APPLES .23
6 rows selected.This query changes the table to reflect an across-the-board 10 percent discount:
SQL> SELECT ITEM, WHOLESALE, WHOLESALE * 0.9 NEWPRICE FROM PRICE; ITEM WHOLESALE NEWPRICE -------------- --------- -------- TOMATOES .34 .306 POTATOES .51 .459 BANANAS .67 .603 TURNIPS .45 .405 CHEESE .89 .801 APPLES .23 .207
6 rows selected.These operators enable you to perform powerful calculations in a SELECT statement.
SQL> SELECT * FROM REMAINS;
NUMERATOR DENOMINATOR --------- ------------ 10 5 8 3 23 9 40 17 1024 16 85 34
6 rows selected.You can also create a new column, REMAINDER, to hold the values of NUMERATOR % DENOMINATOR:
SQL> SELECT NUMERATOR, DENOMINATOR, NUMERATOR%DENOMINATOR REMAINDER FROM REMAINS; NUMERATOR DENOMINATOR REMAINDER --------- ----------- --------- 10 5 0 8 3 2 23 9 5 40 17 6 1024 16 0 85 34 17
6 rows selected.Some implementations of SQL implement modulo as a function called MOD (see Day 4, "Functions: Molding the Data You Retrieve"). The following statement produces results that are identical to the results in the preceding statement:
SQL> SELECT NUMERATOR, DENOMINATOR, MOD(NUMERATOR,DENOMINATOR) REMAINDER FROM REMAINS;
SQL> SELECT * FROM PRECEDENCE; N1 N2 N3 N4 --------- --------- --------- --------- 1 2 3 4 13 24 35 46 9 3 23 5 63 2 45 3 7 2 1 4Use the following code segment to test precedence:
SQL> SELECT 2 N1+N2*N3/N4, 3 (N1+N2)*N3/N4, 4 N1+(N2*N3)/N4 5 FROM PRECEDENCE; N1+N2*N3/N4 (N1+N2)*N3/N4 N1+(N2*N3)/N4 ----------- ------------- ------------- 2.5 2.25 2.5 31.26087 28.152174 31.26087 22.8 55.2 22.8 93 975 93 7.5 2.25 7.5Notice that the first and last columns are identical. If you added a fourth column N1+N2* (N3/N4), its values would also be identical to those of the current first and last columns.
To understand how you could get an Unknown, you need to know a little about the concept of NULL. In database terms NULL is the absence of data in a field. It does not mean a column has a zero or a blank in it. A zero or a blank is a value. NULL means nothing is in that field. If you make a comparison like Field = 9 and the only value for Field is NULL, the comparison will come back Unknown. Because Unknown is an uncomfortable condition, most flavors of SQL change Unknown to FALSE and provide a special operator, IS NULL, to test for a NULL condition.
Here's an example of NULL: Suppose an entry in the PRICE table does not contain a value for WHOLESALE. The results of a query might look like this:
SQL> SELECT * FROM PRICE;
ITEM WHOLESALE -------------- ---------- TOMATOES .34 POTATOES .51 BANANAS .67 TURNIPS .45 CHEESE .89 APPLES .23 ORANGESNotice that nothing is printed out in the WHOLESALE field position for oranges. The value for the field WHOLESALE for oranges is NULL. The NULL is noticeable in this case because it is in a numeric column. However, if the NULL appeared in the ITEM column, it would be impossible to tell the difference between NULL and a blank.
Try to find the NULL:
SQL> SELECT * 2 FROM PRICE 3 WHERE WHOLESALE IS NULL; ITEM WHOLESALE -------------- ---------- ORANGES
SQL> SELECT * FROM PRICE WHERE WHOLESALE = NULL;
no rows selected
This example also illustrates both the use of the most common comparison operator, the equal sign (=), and the playground of all comparison operators, the WHERE clause. You already know about the WHERE clause, so here's a brief look at the equal sign.
SQL> SELECT * FROM FRIENDS;
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP -------------- -------------- --------- -------- -- ----- BUNDY AL 100 555-1111 IL 22333 MEZA AL 200 555-2222 UK MERRICK BUD 300 555-6666 CO 80212 MAST JD 381 555-6767 LA 23456 BULHER FERRIS 345 555-3223 IL 23332Let's find JD's row. (On a short list this task appears trivial, but you may have more friends than we do--or you may have a list with thousands of records.)
SQL> SELECT * FROM FRIENDS WHERE FIRSTNAME = 'JD'; LASTNAME FIRSTNAME AREACODE PHONE ST ZIP -------------- -------------- --------- -------- -- ----- MAST JD 381 555-6767 LA 23456We got the result that we expected. Try this:
SQL> SELECT * FROM FRIENDS WHERE FIRSTNAME = 'AL'; LASTNAME FIRSTNAME AREACODE PHONE ST ZIP -------------- -------------- --------- -------- -- ----- BUNDY AL 100 555-1111 IL 22333 MEZA AL 200 555-2222 UK
Here's another very important lesson concerning case sensitivity:
NOTE: Here you see that = can pull in multiple records. Notice that ZIP is blank on the second record. ZIP is a character field (you learn how to create and populate tables on Day 8, "Manipulating Data"), and in this particular record the NULL demonstrates that a NULL in a character field is impossible to differentiate from a blank field.
SQL> SELECT * FROM FRIENDS WHERE FIRSTNAME = 'BUD'; FIRSTNAME -------------- BUD 1 row selected.Now try this:
SQL> select * from friends where firstname = 'Bud'; no rows selected.
SQL> SELECT * FROM FRIENDS WHERE AREACODE > 300;
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP -------------- -------------- --------- -------- -- ----- MAST JD 381 555-6767 LA 23456 BULHER FERRIS 345 555-3223 IL 23332
SQL> SELECT * 2 FROM FRIENDS 3 WHERE AREACODE >= 300; LASTNAME FIRSTNAME AREACODE PHONE ST ZIP -------------- -------------- --------- -------- -- ----- MERRICK BUD 300 555-6666 CO 80212 MAST JD 381 555-6767 LA 23456 BULHER FERRIS 345 555-3223 IL 23332
NOTE: Notice that no quotes surround 300 in this SQL statement. Number-defined fieldsnumber-defined fields do not require quotes.
SQL> SELECT * 2 FROM FRIENDS 3 WHERE STATE < 'LA';
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP -------------- -------------- --------- -------- -- ------ BUNDY AL 100 555-1111 IL 22333 MERRICK BUD 300 555-6666 CO 80212 BULHER FERRIS 345 555-3223 IL 23332
NOTE: How did STATE get changed to ST? Because the column has only two characters, the column name is shortened to two characters in the returned rows. If the column name had been COWS, it would come out CO. The widths of AREACODE and PHONE are wider than their column names, so they are not truncated.
SQL> SELECT * 2 FROM FRIENDS 3 WHERE STATE < 'la'; LASTNAME FIRSTNAME AREACODE PHONE ST ZIP -------------- -------------- --------- -------- -- ----- BUNDY AL 100 555-1111 IL 22333 MEZA AL 200 555-2222 UK MERRICK BUD 300 555-6666 CO 80212 MAST JD 381 555-6767 LA 23456 BULHER FERRIS 345 555-3223 IL 23332
To include the state of Louisiana in the original search, type
TIP: To be sure of how these operators will behave, check your language tables. Most PC implementations use the ASCII tables. Some other platforms use EBCDIC.
SQL> SELECT * 2 FROM FRIENDS 3 WHERE STATE <= 'LA'; LASTNAME FIRSTNAME AREACODE PHONE ST ZIP -------------- -------------- --------- -------- -- ----- BUNDY AL 100 555-1111 IL 22333 MERRICK BUD 300 555-6666 CO 80212 MAST JD 381 555-6767 LA 23456 BULHER FERRIS 345 555-3223 IL 23332
SQL> SELECT * 2 FROM FRIENDS 3 WHERE FIRSTNAME <> 'AL';
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP -------------- -------------- --------- -------- -- ----- MERRICK BUD 300 555-6666 CO 80212 MAST JD 381 555-6767 LA 23456 BULHER FERRIS 345 555-3223 IL 23332To find everyone not living in California, type this:
SQL> SELECT * 2 FROM FRIENDS 3 WHERE STATE != 'CA'; LASTNAME FIRSTNAME AREACODE PHONE ST ZIP -------------- -------------- --------- -------- -- ----- BUNDY AL 100 555-1111 IL 22333 MEZA AL 200 555-2222 UK MERRICK BUD 300 555-6666 CO 80212 MAST JD 381 555-6767 LA 23456 BULHER FERRIS 345 555-3223 IL 23332
NOTE: Notice that both symbols, <> and !=, can express "not equals."
SQL> SELECT * FROM PARTS;
NAME LOCATION PARTNUMBER -------------- -------------- ---------- APPENDIX MID-STOMACH 1 ADAMS APPLE THROAT 2 HEART CHEST 3 SPINE BACK 4 ANVIL EAR 5 KIDNEY MID-BACK 6How can you find all the parts located in the back? A quick visual inspection of this simple table shows that it has two parts, but unfortunately the locations have slightly different names. Try this:
SQL> SELECT * 2 FROM PARTS 3 WHERE LOCATION LIKE '%BACK%'; NAME LOCATION PARTNUMBER -------------- -------------- ---------- SPINE BACK 4 KIDNEY MID-BACK 6
SQL> SELECT * FROM PARTS WHERE LOCATION LIKE 'BACK%';you would get any occurrence that started with BACK:
NAME LOCATION PARTNUMBER -------------- -------------- ---------- SPINE BACK 4If you queried
SQL> SELECT * FROM PARTS WHERE NAME LIKE 'A%';you would get any name that starts with A:
NAME LOCATION PARTNUMBER -------------- -------------- ---------- APPENDIX MID-STOMACH 1 ADAMS APPLE THROAT 2 ANVIL EAR 5Is LIKE case sensitive? Try the next query to find out.
SQL> SELECT * FROM PARTS WHERE NAME LIKE 'a%'; no rows selected
What if you want to find data that matches all but one character in a certain pattern? In this case you could use a different type of wildcard: the underscore.
SQL> SELECT * FROM FRIENDS;
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP -------------- -------------- --------- -------- -- ----- BUNDY AL 100 555-1111 IL 22333 MEZA AL 200 555-2222 UK MERRICK UD 300 555-6666 CO 80212 MAST JD 381 555-6767 LA 23456 BULHER FERRIS 345 555-3223 IL 23332 PERKINS ALTON 911 555-3116 CA 95633 BOSS SIR 204 555-2345 CT 95633To find all the records where STATE starts with C, type the following:
SQL> SELECT * 2 FROM FRIENDS 3 WHERE STATE LIKE 'C_';
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP -------------- -------------- --------- -------- -- ----- MERRICK BUD 300 555-6666 CO 80212 PERKINS ALTON 911 555-3116 CA 95633 BOSS SIR 204 555-2345 CT 95633You can use several underscores in a statement:
SQL> SELECT * 2 FROM FRIENDS 3 WHERE PHONE LIKE'555-6_6_';
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP -------------- -------------- --------- -------- -- ----- MERRICK BUD 300 555-6666 CO 80212 MAST JD 381 555-6767 LA 23456The previous statement could also be written as follows:
SQL> SELECT * 2 FROM FRIENDS 3 WHERE PHONE LIKE '555-6%'; LASTNAME FIRSTNAME AREACODE PHONE ST ZIP -------------- -------------- --------- -------- -- ----- MERRICK BUD 300 555-6666 CO 80212 MAST JD 381 555-6767 LA 23456Notice that the results are identical. These two wildcards can be combined. The next example finds all records with L as the second character:
SQL> SELECT * 2 FROM FRIENDS 3 WHERE FIRSTNAME LIKE '_L%'; LASTNAME FIRSTNAME AREACODE PHONE ST ZIP -------------- -------------- --------- -------- -- ----- BUNDY AL 100 555-1111 IL 22333 MEZA AL 200 555-2222 UK PERKINS ALTON 911 555-3116 CA 95633
SQL> SELECT FIRSTNAME || LASTNAME ENTIRENAME 2 FROM FRIENDS;
ENTIRENAME ---------------------- AL BUNDY AL MEZA BUD MERRICK JD MAST FERRIS BULHER ALTON PERKINS SIR BOSS
7 rows selected.
SQL> SELECT FIRSTNAME + LASTNAME ENTIRENAME FROM FRIENDS; ERROR: ORA-01722: invalid numberIt is looking for two numbers to add and throws the error invalid number when it doesn't find any.
Here's a more practical example using concatenation:
NOTE: Some implementations of SQL use the plus sign to concatenate strings. Check your implementation.
SQL> SELECT LASTNAME || ',' || FIRSTNAME NAME FROM FRIENDS; NAME ------------------------------------------------------ BUNDY , AL MEZA , AL MERRICK , BUD MAST , JD BULHER , FERRIS PERKINS , ALTON BOSS , SIR
7 rows selected.
So far you have performed the comparisons one at a time. That method is fine for some problems, but what if you need to find all the people at work with last names starting with P who have less than three days of vacation time?
NOTE: Notice the extra spaces between the first name and the last name in these examples. These spaces are actually part of the data. With certain data types, spaces are right-padded to values less than the total length allocated for a field. See your implementation. Data types will be discussed on Day 9, "Creating and Maintaining Tables."
Vacation time is always a hot topic around the workplace. Say you designed a table called VACATION for the accounting department:
SQL> SELECT * FROM VACATION;
LASTNAME EMPLOYEENUM YEARS LEAVETAKEN -------------- ----------- --------- ---------- ABLE 101 2 4 BAKER 104 5 23 BLEDSOE 107 8 45 BOLIVAR 233 4 80 BOLD 210 15 100 COSTALES 211 10 78
6 rows selected.Suppose your company gives each employee 12 days of leave each year. Using what you have learned and a logical operator, find all the employees whose names start with B and who have more than 50 days of leave coming.
SQL> SELECT LASTNAME, 2 YEARS * 12 - LEAVETAKEN REMAINING 3 FROM VACATION 4 WHERE LASTNAME LIKE 'B%' 5 AND 6 YEARS * 12 - LEAVETAKEN > 50; LASTNAME REMAINING -------------- --------- BLEDSOE 51 BOLD 80
LIKE is used in line 4 with the wildcard % to find all the B names. Line 6 uses the > to find all occurrences greater than 50.
The new element is on line 5. You used the logical operator AND to ensure that you found records that met the criteria in lines 4 and 6.
SQL> SELECT LASTNAME 2 FROM VACATION 3 WHERE YEARS <= 5 4 AND 5 LEAVETAKEN > 20 ;
LASTNAME -------- BAKER BOLIVARIf you want to know which employees have been with the company for 5 years or more and have taken less than 50 percent of their leave, you could write:
SQL> SELECT LASTNAME WORKAHOLICS 2 FROM VACATION 3 WHERE YEARS >= 5 4 AND 5 ((YEARS *12)-LEAVETAKEN)/(YEARS * 12) < 0.50; WORKAHOLICS --------------- BAKER BLEDSOECheck these people for burnout. Also check out how we used the AND to combine these two conditions.
SQL> SELECT LASTNAME WORKAHOLICS 2 FROM VACATION 3 WHERE YEARS >= 5 4 OR 5 ((YEARS *12)-LEAVETAKEN)/(YEARS * 12) >= 0.50;
WORKAHOLICS --------------- ABLE BAKER BLEDSOE BOLD COSTALES
SQL> SELECT * 2 FROM VACATION 3 WHERE LASTNAME NOT LIKE 'B%';
LASTNAME EMPLOYEENUM YEARS LEAVETAKEN -------------- ----------- -------- ---------- ABLE 101 2 4 COSTALES 211 10 78NOT can also be used with the operator IS when applied to NULL. Recall the PRICES table where we put a NULL value in the WHOLESALE column opposite the item ORANGES.
SQL> SELECT * FROM PRICE; ITEM WHOLESALE -------------- --------- TOMATOES .34 POTATOES .51 BANANAS .67 TURNIPS .45 CHEESE .89 APPLES .23 ORANGES
7 rows selected.To find the non-NULL items, type this:
SQL> SELECT * 2 FROM PRICE 3 WHERE WHOLESALE IS NOT NULL; ITEM WHOLESALE -------------- --------- TOMATOES .34 POTATOES .51 BANANAS .67 TURNIPS .45 CHEESE .89 APPLES .23
6 rows selected.
SQL> SELECT * FROM FOOTBALL;
NAME -------------------- ABLE BRAVO CHARLIE DECON EXITOR FUBAR GOOBER
7 rows selected.
SQL> SELECT * FROM SOFTBALL;
NAME -------------------- ABLE BAKER CHARLIE DEAN EXITOR FALCONER GOOBER
7 rows selected.How many different people play on one team or another?
SQL> SELECT NAME FROM SOFTBALL 2 UNION 3 SELECT NAME FROM FOOTBALL; NAME -------------------- ABLE BAKER BRAVO CHARLIE DEAN DECON EXITOR FALCONER FUBAR GOOBER
10 rows selected.UNION returns 10 distinct names from the two lists. How many names are on both lists (including duplicates)?
SQL> SELECT NAME FROM SOFTBALL 2 UNION ALL 3 SELECT NAME FROM FOOTBALL; NAME -------------------- ABLE BAKER CHARLIE DEAN EXITOR FALCONER GOOBER ABLE BRAVO CHARLIE DECON EXITOR FUBAR GOOBER
14 rows selected.
SQL> SELECT * FROM FOOTBALL 2 INTERSECT 3 SELECT * FROM SOFTBALL;
NAME -------------------- ABLE CHARLIE EXITOR GOOBER
SQL> SELECT * FROM FOOTBALL 2 MINUS 3 SELECT * FROM SOFTBALL;
NAME -------------------- BRAVO DECON FUBAR
SQL> SELECT * FROM SOFTBALL 2 MINUS 3 SELECT * FROM FOOTBALL;
NAME -------------------- BAKER DEAN FALCONER
SQL> SELECT * 2 FROM FRIENDS 3 WHERE STATE= 'CA' 4 OR 5 STATE ='CO' 6 OR 7 STATE = 'LA';
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP -------------- -------------- --------- -------- -- ----- MERRICK BUD 300 555-6666 CO 80212 MAST JD 381 555-6767 LA 23456 PERKINS ALTON 911 555-3116 CA 95633Or you could type this:
SQL> SELECT * 2 FROM FRIENDS 3 WHERE STATE IN('CA','CO','LA');
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP -------------- -------------- --------- -------- -- ----- MERRICK BUD 300 555-6666 CO 80212 MAST JD 381 555-6767 LA 23456 PERKINS ALTON 911 555-3116 CA 95633
SQL> SELECT * 2 FROM FRIENDS 3 WHERE AREACODE IN(100,381,204); LASTNAME FIRSTNAME AREACODE PHONE ST ZIP -------------- -------------- --------- -------- -- ----- BUNDY AL 100 555-1111 IL 22333 MAST JD 381 555-6767 LA 23456 BOSS SIR 204 555-2345 CT 95633If you needed a range of things from the PRICE table, you could write the following:
SQL> SELECT * 2 FROM PRICE 3 WHERE WHOLESALE > 0.25 4 AND 5 WHOLESALE < 0.75; ITEM WHOLESALE -------------- --------- TOMATOES .34 POTATOES .51 BANANAS .67 TURNIPS .45Or using BETWEEN, you would write this:
SQL> SELECT * 2 FROM PRICE 3 WHERE WHOLESALE BETWEEN 0.25 AND 0.75; ITEM WHOLESALE -------------- --------- TOMATOES .34 POTATOES .51 BANANAS .67 TURNIPS .45Again, the second example is a cleaner, more readable solution than the first.
NOTE: If a WHOLESALE value of 0.25 existed in the PRICE table, that record would have been retrieved also. Parameters used in the BETWEEN operator are inclusive parametersinclusive.
A Whether you use SQL in COBOL as Embedded SQL or in Microsoft's Open Database Connectivity (ODBC), you use the same basic constructions. You will use what you learned today and yesterday repeatedly as you work with SQL.
Q Why are you constantly telling me to check my implementation? I thought there was a standard!
A There is an ANSI standard (the most recent version is 1992); however, most vendors modify it somewhat to suit their databases. The basics are similar if not identical, and each instance has extensions that other vendors copy and improve. We have chosen to use ANSI as a starting point but point out the differences as we go along.
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP -------------- -------------- --------- -------- -- ----- BUNDY AL 100 555-1111 IL 22333 MEZA AL 200 555-2222 UK MERRICK BUD 300 555-6666 CO 80212 MAST JD 381 555-6767 LA 23456 BULHER FERRIS 345 555-3223 IL 23332 PERKINS ALTON 911 555-3116 CA 95633 BOSS SIR 204 555-2345 CT 95633
2. Write a query that returns everyone who lives in Illinois with a first name of AL.
3. Given two tables (PART1 and PART2) containing columns named PARTNO, how would you find out which part numbers are in both tables? Write the query.
4. What shorthand could you use instead of WHERE a >= 10 AND a <=30?
5. What will this query return?
SELECT FIRSTNAME FROM FRIENDS WHERE FIRSTNAME = 'AL' AND LASTNAME = 'BULHER';
NAME ST ------------------- -- AL FROM ILINPUT:
SQL> SELECT (FIRSTNAME || 'FROM') NAME, STATE 2 FROM FRIENDS 3 WHERE STATE = 'IL' 4 AND 5 LASTNAME = 'BUNDY';OUTPUT:
NAME ST ------------------- -- AL FROM IL
NAME PHONE -------------------------- ------------- MERRICK, BUD 300-555-6666 MAST, JD 381-555-6767 BULHER, FERRIS 345-555-3223