INTRODUCCION SOBRE ITS

TheSELECTstatement,unlikeINSERT,UPDATE,andDELETEstatements, doesnotmodifythedataina database.Itsimplyqueriesthedata.Whereas onlyoneuser atatimecanmodifydata,multipleuserscanqueryorselectthe dataconcurrently.Formoreinformationaboutstatementsthatmodifydata, seeChapter6,“ModifyingData,”onpage6-1.Thesyntax descriptionsofthe INSERT,UPDATE,and DELETEstatementsappearintheIBM Informix:Guide toSQLSyntax.

Ina relationaldatabase,a columnisadataelementthatcontainsa particular typeofinformationthatoccursineveryrowinthetable.Arowisagroupof relateditemsof informationabouta singleentityacrossallcolumns ina databasetable.

Youcanselectcolumnsand rowsfroma databasetable;fromasystemcatalog table,aspecial tablethatcontainsinformationonthedatabase;orfromaview, avirtual tablecreatedtocontainacustomized setofdata.Systemcatalog tablesaredescribed intheIBM Informix:GuidetoSQLReference.Views are discussedintheIBM Informix:DatabaseDesignandImplementationGuide.

Privileges

Beforeyoumake aqueryagainstdata,makesureyouhavetheConnect privilegeonthedatabase andtheSelectprivilegeonthetable.These privilegesarenormallygrantedto allusers.Databaseaccessprivilegesare discussedintheIBM Informix:DatabaseDesignandImplementationGuideandin theGRANTandREVOKEstatementsintheIBM Informix:GuidetoSQL Syntax.

RelationalOperations

Arelational operationinvolvesmanipulatingoneormoretables,or relations,to resultinanothertable.Thethreekindsof relationaloperationsare selection, projection,andjoin.Thischapterincludesexamples ofselection,projection, andsimplejoining.

SelectionandProjection

Inrelationalterminology,selectionisdefinedastakingthehorizontalsubsetof rowsofa singletablethatsatisfiesa particularcondition.Thiskindof SELECTstatementreturnssomeoftherowsand allthecolumnsina table. SelectionisimplementedthroughtheWHEREclauseofaSELECT statement, asFigure2-1shows.

Figure2-2containsthesamenumber ofcolumnsasthecustomertable,but onlya subsetofitsrows. Inthis example,DB–Accessdisplaysthedatafrom eachcolumnonaseparateline.

Inrelationalterminology,projectionisdefinedastakingaverticalsubsetfrom thecolumns ofasingle tablethatretainstheuniquerows.Thiskindof SELECTstatementreturnssomeofthecolumnsand alltherowsina table. SELECT * FROM customer WHERE state = ’NJ’

Figure2-1.Query

customer_num 119 fname Bob lname Shorter

company The Triathletes Club address1 2405 Kings Highway address2

city Cherry Hill state NJ zipcode 08002 phone 609-663-6079 customer_num 122 fname Cathy lname O‘Brian

company The Sporting Life address1 543d Nassau address2 city Princeton state NJ zipcode 08540 phone 609-342-0054

ProjectionisimplementedthroughtheprojectionlistintheProjectionclauseof aSELECTstatement, asFigure2-3shows.

Figure2-4containsthesamenumber ofrowsasthecustomertable,butit projectsonlyasubsetofthecolumns inthetable.Becauseonlyasmallamount ofdataisselectedfromeachrow,DB–Accessisabletodisplayallofthedata fromtherow ononeline.

ThemostcommonkindofSELECTstatementusesbothselectionand projection.Aqueryofthiskindreturnssomeoftherowsandsomeofthe columnsina table,asFigure2-5shows.

SELECT city, state, zipcode FROM customer

Figure2-3.Query

city state zipcode

Sunnyvale CA 94086 San Francisco CA 94117 Palo Alto CA 94303 Redwood City CA 94026 Los Altos CA 94022 Mountain View CA 94063 Palo Alto CA 94304 Redwood City CA 94063 Sunnyvale CA 94086 Redwood City CA 94062 Sunnyvale CA 94085 . . . Oakland CA 94609 Cherry Hill NJ 08002 Phoenix AZ 85016 Wilmington DE 19898 Princeton NJ 08540 Jacksonville FL 32256 Bartlesville OK 74006

Figure2-4.QueryResult

SELECT UNIQUE city, state, zipcode FROM customer

WHERE state = ’NJ’

Figure2-6containsa subsetoftherowsand asubsetofthecolumns inthe customertable.

Joining

Ajoinoccurswhentwoor moretablesareconnected byoneormore columns incommon,whichcreates anew tableofresults.Figure2-7showsaquery thatusesa subsetoftheitemsand stocktablestoillustratetheconceptof a join.

Figure2-8joinsthecustomerand statetables. city state zipcode

Cherry Hill NJ 08002 Princeton NJ 08540

Figure2-6.QueryResult

item_num order_num stock_num

1 1001 1

1 1002 4

2 1002 3

3 1003 5

1 1005 5

stock_num manu_code description

1 HRO baseball gloves 1 HSK baseball gloves 2 HRO baseball 4 HSK football 5 NRG tennis racquet SELECT UNIQUE item_num, order_num,

stock.stock_num, description FROM items, stock

WHERE items.stock_num = stock.stock_num

itemstable (example) stocktable (example)

item_num or der_num stock_num description

1 1001 1 baseball gloves

1 1002 4 football

3 1003 5 tennis racquet 1 1005 5 tennis racquet

Figure2-9consistsofspecifiedrowsandcolumns fromboththecustomer and statetables