[ Pobierz całość w formacie PDF ]
.34POTATOES.51BANANAS.67TURNIPS.45CHEESE.89APPLES.236 rows selected.Now type:INPUT/OUTPUT:SQL> SELECT ITEM, WHOLESALE, WHOLESALE + 0.15FROM PRICE;Here the + adds 15 cents to each price to produce the following:ITEM WHOLESALE WHOLESALE+0.15-------------- --------- --------------TOMATOES.34.49POTATOES.51.66BANANAS.67.82TURNIPS.45.60CHEESE.89 1.04APPLES.23.386 rows selected.ANALYSIS:What is this last column with the unattractive column heading WHOLESALE+0.15? It'snot in the original table.(Remember, you used * in the SELECT clause, which causes all the columns to be shown.) SQL allows you to create a virtual or derived column bycombining or modifying existing columns.Retype the original entry:INPUT/OUTPUT:SQL> SELECT * FROM PRICE;The following table results:ITEM WHOLESALE-------------- ---------TOMATOES.34POTATOES.51BANANAS.67TURNIPS.45CHEESE.89APPLES.236 rows selected.ANALYSIS:The output confirms that the original data has not been changed and that the columnheading WHOLESALE+0.15 is not a permanent part of it.In fact, the column heading is sounattractive that you should do something about it.Type the following:INPUT/OUTPUT:SQL> SELECT ITEM, WHOLESALE, (WHOLESALE + 0.15) RETAILFROM PRICE;Here's the result:ITEM WHOLESALE RETAIL-------------- --------- ------TOMATOES.34.49POTATOES.51.66BANANAS.67.82TURNIPS.45.60CHEESE.89 1.04APPLES.23.386 rows selected. ANALYSIS:This is wonderful! Not only can you create new columns, but you can also rename themon the fly.You can rename any of the columns using the syntax column_name alias(note the space between column_name and alias).For example, the queryINPUT/OUTPUT:SQL> SELECT ITEM PRODUCE, WHOLESALE, WHOLESALE + 0.25 RETAILFROM PRICE;renames the columns as follows:PRODUCE WHOLESALE RETAIL-------------- --------- ---------TOMATOES.34.59POTATOES.51.76BANANAS.67.92TURNIPS.45.70CHEESE.89 1.14APPLES.23.48NOTE: Some implementations of SQL use the syntax.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.You might be wondering what use aliasing is if you are not using command-line SQL.Fairenough.Have you ever wondered how report builders work? Someday, when you areasked to write a report generator, you'll remember this and not spend weeks reinventingwhat Dr.Codd and IBM have wrought.So far, you have seen two uses of the plus sign.The first instance was the use of the plussign in the SELECT clause to perform a calculation on the data and display thecalculation.The second use of the plus sign is in the WHERE clause.Using operators inthe 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.Minus (-)Minus also has two uses.First, it can change the sign of a number.You can use the tableHILOW to demonstrate this function.INPUT:SQL> SELECT * FROM HILOW;OUTPUT:STATE HIGHTEMP LOWTEMP---------- -------- ---------CA -50 120FL 20 110LA 15 99ND -70 101NE -60 100For example, here's a way to manipulate the data:INPUT/OUTPUT:SQL> SELECT STATE, -HIGHTEMP LOWS, -LOWTEMP HIGHSFROM HILOW;STATE LOWS HIGHS---------- -------- ---------CA 50 -120FL -20 -110LA -15 -99ND 70 -101NE 60 -100The second (and obvious) use of the minus sign is to subtract one column from another.For example:INPUT/OUTPUT:SQL> SELECT STATE,2 HIGHTEMP LOWS,3 LOWTEMP HIGHS,4 (LOWTEMP - HIGHTEMP) DIFFERENCE 5 FROM HILOW;STATE LOWS HIGHS DIFFERENCE---------- -------- -------- ----------CA -50 120 170FL 20 110 90LA 15 99 84ND -70 101 171NE -60 100 160Notice the use of aliases to fix the data that was entered incorrectly.This remedy ismerely a temporary patch, though, and not a permanent fix.You should see to it thatthe data is corrected and entered correctly in the future.On Day 21, "Common SQLMistakes/Errors and Resolutions," you'll learn how to correct bad data [ Pobierz całość w formacie PDF ]