[ Pobierz całość w formacie PDF ]
. Queries with DistinctionIf you look at the original table, CHECKS, you see that some of the data repeats.Forexample, if you looked at the AMOUNT column usingINPUT:SQL> select amount from checks;you would seeOUTPUT:AMOUNT---------150245.34200.32981502525.1Notice that the amount 150 is repeated.What if you wanted to see how may differentamounts were in this column? Try this:INPUT:SQL> select DISTINCT amount from checks;The result would beOUTPUT:AMOUNT---------2525.198150200.32245.346 rows selected.ANALYSIS: Notice that only six rows are selected.Because you specified DISTINCT, only oneinstance of the duplicated data is shown, which means that one less row is returned.ALL is a keyword that is implied in the basic SELECT statement.You almost never seeALL because SELECT and SELECT ALL have the same result.Try this example--for the first (and only!) time in your SQL career:INPUT:SQL> SELECT ALL AMOUNT2 FROM CHECKS;OUTPUT:AMOUNT---------150245.34200.32981502525.17 rows selected.It is the same as a SELECT.Who needs the extra keystrokes?SummaryThe keywords SELECT and FROM enable the query to retrieve data.You can make abroad statement and include all tables with a SELECT * statement, or you canrearrange or retrieve specific tables.The keyword DISTINCT limits the output so thatyou do not see duplicate values in a column.Tomorrow you learn how to make yourqueries even more selective.Q&AQ Where did this data come from and how do I connect to it?A The data was created using the methods described on Day 8.The databaseconnection depends on how you are using SQL.The method shown is thetraditional command-line method used on commercial-quality databases.Thesedatabases have traditionally been the domain of the mainframe or theworkstation, but recently they have migrated to the PC. Q OK, but if I don't use one of these databases, how will I use SQL?A You can also use SQL from within a programming language.EmbeddedSQLEmbedded SQL is normally a language extension, most commonly seen inCOBOL, in which SQL is written inside of and compiled with the program.Microsoft has created an entire Application Programming Interface (API) thatenables programmers to use SQL from inside Visual Basic, C, or C++.Librariesavailable from Sybase and Oracle also enable you to put SQL in your programs.Borland has encapsulated SQL into database objects in Delphi.The concepts inthis book apply in all these languages.WorkshopThe Workshop provides quiz questions to help solidify your understanding of thematerial covered, as well as exercises to provide you with experience in using what youhave learned.Try to answer the quiz and exercise questions before checking theanswers in Appendix F, "Answers to Quizzes and Exercises," and make sure youunderstand the answers before starting tomorrow's work.Quiz1.Do the following statements return the same or different output:SELECT * FROM CHECKS;select * from checks;?2.The following queries do not work.Why not?a.Select *b.Select * from checksc.Select amount name payee FROM checks;3.Which of the following SQL statements will work?a.select *from checks;b.select * from checks;c.select * from checks /Exercises1.Using the CHECKS table from earlier today, write a query to return just thecheck numbers and the remarks.2.Rewrite the query from exercise 1 so that the remarks will appear as the firstcolumn in your query results.3.Using the CHECKS table, write a query to return all the unique remarks.© Copyright, Macmillan Computer Publishing.All rights reserved. Teach Yourself SQL in 21 Days, SecondEdition- Day 3 -Expressions, Conditions, and OperatorsObjectivesOn Day 2, "Introduction to the Query: The SELECT Statement," you used SELECT andFROM to manipulate data in interesting (and useful) ways.Today you learn more aboutSELECT and FROM and expand the basic query with some new terms to go with query,table, and row, as well as a new clause and a group of handy items called operators
[ Pobierz całość w formacie PDF ]