Turbo Toolbox

home *** CD-ROM | disk | FTP | other *** search

/ Turbo Toolbox / Turbo_Toolbox.iso / 1990 / 03 / ssql / select.shr < prev next >

Wrap

Text File | 1988-03-25 | 37.3 KB | 1,090 lines

SELECT (Single Table) SYNTAX: SELECT [DISTINCT] column_name [,column_name ...] FROM table_name [,table_name ...] [WHERE search_expression] [INTO file_name] <-- Non-Standard [GROUP BY column_name [,column_name ...] [HAVING criteria] [ORDER BY column_name [,column_name ...] If you want all the columns in a table you can replace the list of column names with an asterisk (*). The select command produces a report in the form of a table. Although the basic use of the select takes a short time to learn, the more advanced uses take a long time to master. The pm table is included so you can try entering the commands yourself. For those of you who know about data normalization, the pm table is not even in the second normal form. I did this so I could more easily explain the use of all the commands using a single table. The pm (product manufacture) table consists of: code - 2-character product code desc - 15-character product description mst - state in which the product was manufactured defects - % defects of the last batch of products manufactured Since the product can be manufactured in more that one state, the combination of code and mst is the primary key. All the examples have each clause starting on a new line for purposes of clarity. It is not necessary to have them on separate lines. It is sometimes advantageous to keep the query on one line since if you need to make a correction, you cannot backup a line. THE BASICS OF SELECT To find all the data in a table: select * from pm; code desc mst defects ---- --------------- --- ------- AB Megawamp WA 3 AC Gigasnarf AZ 0 DD Decawidgit WA 3 DA Technowidgit OR 10 DZ Electrowidgit AZ EA nanomouse AZ 12 EC RGBMouse ID 2 FA Dynamic Disk NV 3 AB Megawamp ID 5 9 rows selected SELECT-1 Instead of typing all the column names, you can use the * to denote all column names. On the next line, the from pm tells us that we want the data to come from the table named "p" (for product). All select commands must end with a semi-colon (;). If we just wanted the product code and the state that it was manufactured in you would type: select code, mst from pm; code mst ---- --- AB WA AC AZ DD WA DA OR DZ AZ EA AZ EC ID FA NV AB ID 9 rows selected DISTINCT The optional statement "distinct" will eliminate all rows with duplicate columns that you have specified. If you wanted to find out all the different states where products are manufactured, you would type: select distinct mst from pm; mst --- WA AZ OR ID NV 5 rows selected As you can see only five rows were found. This is because three products are manufactured in Arizona and two products are SELECT-2 manufactured in Washington. Only the first occurance of each data element is retained. The "distinct" modifier can be used with one or more column names in a select statement. You will see useful examples of this when we discuss more complex searches. SELECT-3 WHERE The where clause of the select command is the most powerful and complex. Our discussion will be broken down into various phases. The first phase is using the where in conjunction with a single table name. Later you will see it used with multiple tables and you will find that you can even put another select command within the where clause. search_expression =, <>, !=, >, <, >=, <=, IS NULL, IS NOT NULL, LIKE The simplest search expression relates a column name to a constant. A constant is an exact value that you enter. All column names are tested against that value. If the column name that you are testing is defined as a char, the constant must be enclosed in single quotes or double quotes. The constant you are searching for may not contain a quote. If the column name you are testing for is defined as a num, the constant is a number without any quotes. You can relate the column name to the constant in the following ways: = equal <> not equal != not equal > greater than < less than >= greater than or equal <= less than or equal If you wanted to find out which products are manufactured in Arizona: select * from pm where mst = 'AZ'; code desc mst defects ---- --------------- --- ------- AC Gigasnarf AZ 0 DZ Electrowidgit AZ EA nanomouse AZ 12 3 rows selected SELECT-4 Spaces are optional on either side of the "=". You could have typed: where mst='AZ'; Instead of single quotes you could have used double quotes: where mst="AZ"; The column name "defects" is the percent of defects when the product was manufactured. If you wanted a list of all the product descriptions and their corresponding defects for those products with defects over 10%: select desc, defects from pm where defects > 10; desc defects --------------- ------- nanomouse 12 1 row selected We find that only the nanomouse has more than 10% defects. SPECIAL SEARCH EXPRESSION - IS NULL, IS NOT NULL Nulls are an important concept in relational databases. For example, let's look at the percentage of defects in Arizona: select * from pm where mst = 'AZ'; code desc mst defects ---- --------------- --- ------- AC Gigasnarf AZ 0 DZ Electrowidgit AZ EA nanomouse AZ 12 3 rows selected In Arizona, the Gigasnarf has zero defects, the nanomouse has 12% defects but there is nothing listed for the Electrowidgit. This "nothing" is called a null. It does not mean zero or blank. A null means that the data element is not relevant in the row. In our case, Electrowidgits are not tracked for defects like other parts. This isn't to say that there are zero defects. It just means we have no entry. SELECT-5 If we wanted to look at all products for which we do not track defects: select * from pm where defects is null; code desc mst defects ---- --------------- --- ------- DZ Electrowidgit AZ 1 row found We could find all the products for which we track defects by using is not null: select * from pm where defects is not null; code desc mst defects ---- --------------- --- ------- AB Megawamp WA 3 AC Gigasnarf AZ 0 DD Decawidgit WA 3 DA Technowidgit OR 10 EA nanomouse AZ 12 EC RGBMouse ID 2 FA Dynamic Disk NV 3 AB Megawamp ID 5 8 rows selected SELECT-6 SPECIAL SEARCH EXPRESSION - LIKE, NOT LIKE The expression like is used when we want similar characters. Get all product descriptions that begin with the letter 'D': select * from pm where desc like 'D%'; code desc mst defects ---- --------------- --- ------- DD Decawidgit WA 3 FA Dynamic Disk NV 3 2 rows selected The special character '%' tells the expression to accept any characters at that position and any subsequent position. The underline is another special character. It tells the expression to accept any character at that position only. Get all product codes that end in 'A': select * from pm where code like '_A'; code desc mst defects ---- --------------- --- ------- DA Technowidgit OR 10 EA nanomouse AZ 12 FA Dynamic Disk NV 3 3 rows selected The two special characters '%' and '_' can also be used together in a search expression. You can produce the opposite result by using the not modifier. Get all product codes which do not end in 'A': select * from pm where code not like '_A'; code desc mst defects ---- --------------- --- ------- AB Megawamp WA 3 AC Gigasnarf AZ 0 DD Decawidgit WA 3 DZ Electrowidgit AZ EC RGBMouse ID 2 AB Megawamp ID 5 6 rows selected SELECT-7 AND, OR, NOT We can link expressions through the use of ands and ors. If you use an and, both expressions have to be true in order to add the row to the table we are producing. If you use an or, only one of the expressions have to be true. You can use as many ands and ors as you want in a query. The expressions are executed from left to right. All the ands are done first, then execution starts again from the left and the ors are processed. If you want to reference different column names in a search, the logic as stated in English is typically the same as it is in SQL. Usually there is no confusion between the and and the or as is the case when we are dealing with the same column name. If you want to find all products in Washington with defects over 2%: select * from pm where mst = 'WA' and defects > 2; code desc mst defects ---- --------------- --- ------- AB Megawamp WA 3 DD Decawidgit WA 3 2 rows selected Generally, the use of a single and or an or in a where clause is more or less common sense unless you are refering to the same column name. What if you wanted to find all the products manufactured in Washington and Idaho. This has two meanings in everyday English: 1. Out of all the products we manufacture, I want a list of the ones we manufacture in Washington and a list of the ones we manufacture in Idaho. 2. Out of all the products we manufacture, I want a list of each one we manufacture in BOTH Washington and Idaho. We will accept the first meaning in this example. It is interesting to note that we could have also said, "List all the products manufactured in Washington or Idaho." It seems odd that in English "or" can have the same meaning as "and". Even if you don't agree with my interpretation, beware that others do and that it may cause communication problems. In the English version you use the "and" so there is a tendency to always use the "and" in the SQL version too. This would be wrong. The English perspective and the SQL perspective are different. The English perspective often looks at the whole group of items. The SQL perspective looks at one item at a time. SELECT-8 To translate Washington and Idaho into SQL we look at it from its perspective. We will look at each item. If that item is manufactured in Washington or Idaho, we will add it to our table. Notice that when we look at it from the SQL perspective, we use the "or" instead of the "and". This not just a quirk of SQL logic, it is common to virtually all data bases. To find out all the products manufactured in Washington and Idaho: select desc from pm where mst='WA' or mst='ID'; desc ----------------- Megawamp Decawidgit RGBMouse Megawamp 4 rows selected Megawamp is repeated above because it is manufactured in both Washington and Idaho. To get rid of the duplicate you would replace the first line with: select distinct desc It is also interesting to note what the meaning would be if we used an and instead of an or. Would it tell us every product manufactured in BOTH Washington and Idaho? No, it wouldn't! Actually the following search makes no sense. How could a state for an individual item be both WA and ID????? select desc from pm where mst='WA' and mst='ID'; [ none found ] But how WOULD you find out every product manufactured in both Washington and Idaho? The search strategy is rather complex and the topic is covered under joining and virtual tables. In order to express the opposite logic, you use the not. If you wanted to find the products manufactured outside of Washington and Idaho: select * from pm where not (mst='WA' or mst='ID'); code desc mst defects ---- --------------- --- ------- AC Gigasnarf AZ 0 DA Technowidgit OR 10 DZ Electrowidgit AZ EA nanomouse AZ 12 FA Dynamic Disk NV 3 5 rows selected SELECT-9 This query finds products that may be manufactured in Washington or Idaho but they are manufactured in other states. It just so happens that in this small set of values, there is none. Notice that is not the same as finding the products not manufactured in Washington or Idaho. In this query we would have to find all the products manufactured in Washington or Idaho and then find those product code not in that set. Getting a bit complicated, huh? To solve this problem, a subquery is needed. Normally, if a condition is true, we add that row to the table. By using the not, if the condition is false, we add the row to the table. On the first row above, the state is AZ. It was selected because it is not equal to WA or ID. Just as we can express the same thought in English more than one way, we can do the same with SQL. We could have written: select * from pm where mst <> 'WA' and mst <> 'ID'; or select * from pm where not (mst = 'WA') and not (mst = 'ID'); What if we would have written "or" instead of "and" in the above two examples? Every row would have been selected. No matter what the state is, it would either be not equal to WA or not equal to ID! Remember that whenever you use the not, you must enclose the expression in parentheses. You can have as many ands and ors in a query as you need. What if you wanted all products in Washington and Idaho with defects over 3%: select * from pm where (mst='WA' or mst='ID') and defects > 3; code desc mst defects ---- --------------- --- ------- AB Megawamp ID 5 1 row selected We find that only one product is selected. Notice that parentheses enclose the or expression. This is because without the parentheses the and expression would have been executed first. Remember that without parentheses the ands are processed first and the ors are processed next. Not inserting the parentheses would have changed the meaning considerably: SELECT-10 select * from pm where mst='WA' or mst='ID' and defects > 3; code desc mst defects ---- --------------- --- ------- AB Megawamp WA 3 DD Decawidgit WA 3 AB Megawamp ID 5 3 rows selected The above query is the equivalent of saying, "List all products in Washington and list all products in Idaho with defects over 3%." Another way of stating it is "In looking through all the products, if a product is manufactured in Washington or an Idaho product has over 3% defects, add it to the list." The important concept is that the 3% defects relates to the products manufactured in Idaho, not all products. SELECT-11 SETS (used with constants) Instead of relating items one at a time, we can relate groups of items at once. These groups are called sets. They are enclosed by parentheses and the constants are separated by commas. ANY You saw above how you can use =, >, <, <>, >=, <=. We can expand that concept to include "any" and "all". As you will see, the "any" corresponds to an "or" and the "all" corresponds to an "and". All possible combinations: =any means equal to any >any means greater than any <any means less than any <>any means not equal to any >=any means greater than or equal to any <=any means less than or equal to any Let's rewrite the query concerning all products manufactured in Washington or Idaho: select desc from pm where mst =any ('WA','ID'); desc ----------------- Megawamp Decawidgit RGBMouse Megawamp 3 rows selected Translated into English it would be: Select the description from the p(roduct) table where the state of manufacture is equal to any of the following - WA or ID. We can use numbers too. What if you wanted to know the names of products that have a percentage of defects of 2% or 3%: select desc from pm where defects =any (2,3); desc ----------------- Megawamp Decawidgit RGBMouse Dynamic Disk 4 rows selected SELECT-12 IN An equivalent to =any is in. We will see that sets are much more useful when we discuss using nested selects and multiple tables. However, you could translate the above query to: select desc from pm where defects in (2,3); ALL The "all" is similar to the "any" except that instead of the items being connected by "or's", they are connected by "and's". It is useful in determining a group of items outside a certain set. If you wanted to find all products manufactured outside of WA and ID: select * from pm where mst !=all ('WA','ID'); BETWEEN constant AND constant An easy way to select a range of values is use the between/and component of the where clause. If you wanted to find the product descriptions with defects between 3% and 10%: select desc, defects from pm where defects between 3 and 10; desc defects --------------- ------- Megawamp 3 Decawidgit 3 Technowidgit 10 Dynamic Disk 3 Megawamp 5 5 rows selected Notice that when we use the between, we include the 3 and the 10. Unfortunately, the word "between" can have more than one meaning in standard English. If you say "The treasure is located between San Francisco and Walnut Creek", you would assume that the treasure is not IN San Francisco or Walnut Creek. That is, you would not include the end points. If you say "This insurance plan is available for those people between the ages of 18 and 65", you would assume that if you are 18 or 65, you are eligible. That is, you would include the end points. Just remember that in SQL, between includes the end points. SELECT-13 MATHEMATICAL FUNCTIONS - AVG, MIN, MAX, SUM, COUNT All of the mathematical functions operate on numeric columns except count which will operate on alphanumeric columns. SYNTAX: AVG([DISTINCT] column_name) MIN(column_name) MAX(column_name) SUM([DISTINCT]column_name) COUNT([DISTINCT]column_name) SUMMARY AVG - without DISTINCT: The average of all values in a column with DISTINCT : The average of non-duplicate values MIN - The minimum value in the column MAX - The maximum value in the column SUM - without DISTINCT: The sum of all values in the column with DISTINCT : The sum of non-duplicate values COUNT - without DISTINCT: the number of column entries with DISTINCT : the number of non-duplicate column entries Notice that some of the functions have the distinct option. This will delete all rows in the table which have duplicate column names. The following examples show the difference: select count(mst) from pm; CNT(mst) -------- 9 9 rows selected The above counts all the entries in the mst column. There are a total of nine. select count(distinct mst) from pm; CNT(mst) -------- 5 9 rows selected The above is typically more useful. It answers the question: "How many different states are used for manufacturing products?" There is one difference between the syntax for standard SQL and this program, SSQL. Standard SQL allows count(*). In SSQL the "*" is not allowed in the count function. You must have a valid column name. SELECT-14 Before we discuss the other functions, let's look at the percentage of defects in Arizona: select * from pm where mst = 'AZ'; code desc mst defects ---- --------------- --- ------- AC Gigasnarf AZ 0 DZ Electrowidgit AZ EA nanomouse AZ 12 3 rows selected In Arizona, the Gigasnarf has zero defects, the nanomouse has 12% defects but there is nothing listed for the Electrowidgit. This "nothing" is called a null. Nulls are important in relational data bases. It does not mean zero or blank. A null means that the data element is not relevant in the row. In our case, Electrowidgits are not tracked for defects like other parts. This isn't to say that there are zero defects. It just means we have no entry. Because there are only two valid entries for defects in Arizona, the functions will not take into account the null data element. The average percentage of defects will be 12/2 = 6, not 12/3 = 4. select avg(defects) from pm where mst='AZ'; AVG(defects) ------------ 6 3 rows selected select count(defects) from pm where mst = 'AZ'; CNT(defects) ------------ 2 3 rows selected SELECT-15 GROUP BY, HAVING The group by clause is normally used in conjunction with the mathematical functions. It does two operations: 1. It sorts by the column name. 2. The mathematical functions only operate based on the rows which have the same column name. The functions in essence create sub-totals based on the column name. What if we wanted to know the average percentage of defects in each state? select mst, avg(defects) from pm group by mst; mst AVG(defects) ----------------- AZ 6 ID 3 NV 3 OR 10 WA 3 5 rows selected In SSQL you must select the column name that you use in the group by clause. You can restrict the group by clause through the use of the "having" component. In SSQL you are allowed one simple selection which includes the =, >, <, >=, <=, and <>. As with the group by, the column name in the having component must exist in the select clause. If you only wanted a list of states with the average percentage of defects over 3%, you would type the following: select mst, avg(defects) from pm group by mst having avg(defects) > 3; mst AVG(defects) --- ------------ AZ 6 OR 10 2 rows selected The group by clause can have multiple fields. This is when you want a sort within a sort. However with such a small table we cannot create anything useful. SELECT-16 ORDER BY The order by clause sorts the table based on the column name(s) listed. As with the group by clause, the order by clause can be used with the where clause. For example, the following will produce a sorted list of descriptions for those items manufactured in Arizona: select desc from pm where mst = 'AZ' order by desc; desc ----------------- Electrowidgit Gigasnarf nanomouse 3 rows selected You can also produce a sort within a sort. The following will produce an ordered list of states and within each state the product descriptions will be sorted: select mst, desc from pm order by mst, desc; mst desc --- ----------------- AZ Electrowidgit AZ Gigasnarf AZ nanomouse ID Megawamp ID RGBMouse NV Dynamic Disk OR Technowidgit WA Decawidgit WA Megawamp 9 rows selected The order by clause assumes ascending order. You can reverse the order by adding the descending modifier to the sort. It alway is put after the the column name it is modifying. select * from pm where defects > 3 order by defects descending; code desc mst defects ---- --------------- --- ------- EA nanomouse AZ 12 DA Technowidgit OR 10 AB Megawamp ID 5 3 rows selected SELECT-17 INTO file_name Normally the report that is produced by the select command is displayed on the monitor. However, through the use of the into clause, the report can be redirected to a text file or to the printer. The text file is in standard ASCII text. That is, there are no special characters in it. The file name you give it has the extension ".txt" added. Your file name must be one to eight characters and you cannot have a period "." in the file name. If you wanted all the Arizona products stored in a file called ARIZ and you wanted the report sorted by product description: select * from pm where mst='AZ' into ariz order by desc; [ 3 found ] The only response to your query would be the number of rows selected. Now it would be stored on your disk under the name "ARIZ.TXT". To display the file on the monitor, you would exit SSQL and type the following AT THE DOS PROMPT. The report would be displayed: A>type ariz.txt code desc mst defects ---- --------------- --- ------- DZ Electrowidgit AZ AC Gigasnarf AZ 0 EA nanomouse AZ 12 You can also have the report printed on the printer. Replace the file name with prn or lpt1. Make sure the printer is on when you use this option. For example, the following would print the report instead of display it on the monitor. select * from pm where mst='AZ' into prn order by desc; [ 3 found ] SELECT-18