Hands+on+Guide+to+SQL (1)

Hands+on+Guide+to+SQL (1) - fin'flw‘lmuy-v-m-‘w m1......

Info iconThis preview shows pages 1–4. Sign up to view the full content.

View Full Document Right Arrow Icon
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Background image of page 2
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Background image of page 4
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: fin'flw‘lmuy-v-m-‘w m1... Hands—On Guide to SQL 'I'his HandsAOn Guide is a brin introduction to Structured Query Language (SQL), the standard 1anguage for re1a- tiona1 database management systems. can be used for defining database structures, querying databases, and updating database data. 1t you are using Access, you wi11 probably use its Query, Report, and '1‘ab1e—bui1diug too1s rather than SQL for most of your querying and reporting work However, you may want to use SQL to edit Access CUSTOMER Table with sample data M11) HUMP. (2118111) li'1\/\1\11: NAMI‘ l.N/\M|‘. ADDRI ADRZ (21“ 5111\1‘1‘. ZIP 1’II()NI-. 1 (3110:5111 \ 111111111 58 \1.1111511ttl Miumlld 1'\\ Will 1014-1131311 3 11111111 '\|.1|nnt 1381111111111." 1. 0111.11.1111 \‘1 10308 1011122213“ 7 Julie! L) Minimum: |1\1111u 1’111/11 .\pl 33 linuimm N1 103% (01-11 SEN \ISI 4 \l.11\ 1‘11k11 s1111ll1 0.11.1111 1.1111 (11.11.11.111 N1 rosin 1014191 "1111111 :1 H.111 \11111 5111101 32 Sqmmhti 1111(11141111 N1 05% (1 1111111. 1 111111111111 (123104211111 (:.11;1L111 m 1113311 " '111111nm 1‘ 111mm 3 5(‘11111 \u 1111151 (2 Batman: N1 lthts H 7:11: \11I11u ‘)1(111nrlati Bunuaiu N\ 109% 13131371311150 ‘1 Mari \1111 8111101 >1 Kan Road (710.1111: 1\‘\ 10§20 1‘1-1113331S\h 10 1111111P1111I 1mth t1(1(>u-.111\11-u 0111.11.11“ \‘\ rows 13 ICltHIlL | (lwcm 9-190'1111uu1t' 1511mm”: N\ 10505 I? Mark \1ilk(11k11111 11 \11110 \11 (7101111111 N1 10:20 1% 11111 9 111111111 1) Pcnbnld I’mL \liwmla \‘1 10921 mm :1; 2:11 NiimbLn APPOIN'HWENT Table with sample data Cl 1511 D SISRVICILI D APP' LDA'I‘E APP'IL'I‘IM 1‘} 1 2 1/5/3001 1:11PM 1 2 /7/200'1‘ 3:00 PM 2 2 5/200? 030 AM 2 3 1/7/3003 1:00 PM 2 5 5/200? 10110AM 2 E 1 /7/20(R 1130AM -1 2 05/300? 1 00 PM 4 1 0/200? L):00 AM § 1 5/200} 1200 PM § 1 /§/Z0(H 1) 00 AM (1 § 5/200? 11.10 AM 7 (1 /§/300§ 10:10.v\.\'1 7 3 0/300: 4 10 PM 8 1 /9/Z01H 100 PM 0 -1 15/300? 330 PM 10 3 /§/30[R >1 3011M 13 1 1 /§/300§ LIMAM 13 3 1 5/2002 8:10 AM queries and you can use your 1<now1edge of when you work with other database management systems. We \yi11 use tables from the HisNHers database dcve1oped in the first appendix to i11ustrate how SQL works, focusing on the most important SQ]. commands for retrieving data. We wi11 show you how to create SQL queries using samp1e data from the CUSTOMER '1‘ab1e and the A1’P()11\"111\/11*1N'1' Table i11ustrated here. “()RK (11:11.1. PI'SON \1,» 0141) P11()N[‘. PHONI'. 1‘11\1A11. 1X11! SlaX INN) (Zl'S'I‘N 01141333 511K: 011311§WV§§V1 a11r111111m111111111: 37110;] I: \\()|{1\\’ \l \1\ 131113333131 |"\1\1.tt1\()|('.(l\| (1/13/1081 \1 131310310404 121313311331 13/33/111‘1 1~ 1/31/‘1‘13‘1 |~ 191111111!) 111112 1/33/1117: I. 11111111I11g111111111111111111 11.111/1w111 M lemma \1 Ina/inns I‘ VIN/1111‘: |» 1410108053“ \1 |II11N|II111lm\1111111 (11111 131311131311‘ 1‘11311§(1-'h3‘1 l.()\1 1111. \1l. 10-11 1 71; ~\~I3-1 13131430175715 11.1111 :1.“ ‘1.“1 1111 11111uu111 0/10/10") \1 11411IL1\(VIIK1 1’.MP_SS 1335156789 131450709 (1076962 121-15678‘ 121%670‘) 8 00769412 8 0876543 1214:1(178‘ 121196731) 13?-1§(178‘ 087M113 1334;6781 00769123 90709-123 131%(171‘1‘ 131-19033 00769113 1% 131-”(170‘) PRODJVUM APP’1;(I()1\11V11“.N'1' .1. 1) la .u 1's 593 594 APPENDIX 2 I Hands—0n Guide to SQL BASIC SEN. SYNTAX RULES SQL, like all computer languages has a particular syntax or grammar which must be followed in order for the com— mands to be understood and executed properly. Its most basic rules are: 1. Identifiers (names oftablcs, columns, and other objects) should contain between I and 30 charac— ters. The identifiers can be upper or lower case, but no embedded spaces are allowed. For example, WORK PHONE would have to be written as WORKPI'IONE or WORK_PHONE. 2. is not case sensitive, although keywords such as SELEC'I' or EROM are usually capitalized. Keywords have predefined meanings and cannot be used as identifiers. 3. SQL statements can take up more than one line (and there are no restrictions on the number of words per line or where to break a line). However, a new line is often started when a new clause in an statement begins. 4. Commands begin with the SQL operator (c.g., CREATE or SELECT). 5. Field (column) names are separated from each other by a comma (cg, SELECT ENAME, LNAME FROM tblCUSTOMER). 6. Field (column) names are separated from table names by a period (e.g., tblCUSTOMER.ENAME, tblCUS'I‘OMERLNAME). (Access uses this con- vention.) The name of the table does not have to be written before the name of the field from that table unless two tables used in the same SQL state— ment have identical field names (such as the CUSTID field in both the CUSFI'OMER and APPOINTMENT tables). Then the table name must be included (e.g., tblCUSTOMERCUSTID and tbl.APPOIN'HVIEN']ICUSTID). 7. Strings must be enclosed in quotation marks. A string is text (or a collection ofbits) that is foreign to the database. The statement to find the phone number for all of the customers whose last name is MaeCregor would be written as: SELECT HOMEPHONE FROM tblCUSTOMER WHERE LNAME : “MacGregor”; Remember too that a space is considered a character and must always be included in the quotation marks if it is needed for the comparison. “Mac Cregor" and “MacCregor” will not return the same Scotchman. 8. Nested operations must be enclosed in parentheses. One of the strengths of SQL is its ability to perform quite complex data numipulation. This is primarin due to its use of nesting. Commands within corri— mands are enclosed in parentheses and the inner- most ones are executed first. USENG THE SELEBT STATEMENT The most often used SQ], command is SELECT which returns rows of data from columns in the tables that the user would like to see in a result table. Following are a series of SEl ,ECT statements which demonstrate how to have all or specified columns returned in the result table. The names of the colunms to be queried follow the keyword SELECT and the name of the table to use follows the keyword FROM. SELECT every column and every row SELECT * FROM tblCUSTOMER; The asterisk after SELECT causes every column to be included in the records from the table, tblCUS'llOMER. The results show all the rows in the CUSTOMER Table as displayed earlier. SELECT only some columns and every row SELECT ADDRl, LNAME FROM tthUSTOMER; Only the columns which are listed are returned from the CUSTOMER Table and they are presented in the order they are listed in the statement. r ADDRI L LNAME 88 Main Street Brown 42 Sweetbriar Lane Malone J r_]l Venice Plaza Montague Marian Lane Smith L 22 September Street V Smith j 6233 Ridgeview hnnpup f 7 SchiffAve T Brown 91 Glendale Nother 8 Race Road Smith _] 66 Ocean View F Lones 9499 'l’hrews Le. J Greene 43 Sehiff Ave MacCr_e_gor f9 Penland Park Brown 1 SDRTING DATA: USING DRDER BY The following examples show the ORDER BY statement which is used to sort the data returned by the SELECT statement. Select only some columns and every row in sorted order by one field SELECT LNAME, FNAME FROM tblCUSTOMER ORDER BY LNAME; The ORDER BY command causes the records to be sorted in order by the contents ofthe column name following it. LNAME FNAME Brown im Brown 'l'homas Brown Georgia Creene eanne ones John Paul unipup Iuslin MacGregor Mark Malone Patrick Montague J_uliet Nother Zoe Smith Mary Ann Smith Mary Allen Smith Mary Ellen Select only some columns and every row in sorted order by several fields The returned records may be sorted in order by several fields. Each listed field after the ORDER BY clause is sepa— rated by a comma. First the records are sorted by last name, and ifthe last names are the same they are sorted by first name. SELECT LNAME, FNAME FROM tthUSTOMER ORDER BY Ll\AME, FNAME; Ceor Iia im Thomas eanne ohn Paul Iumpup Justin MacCrerr Mark Malone Patrick Montaoue uliet Brown Brown Brown Creene Nother Zoe Smith Mary Allen Smith Mary Aim Smith Mary Ellen SELECTING DNLY RECORDS THAT MEET SPECIFIED CRITERIA: Ll SIN G W H E R E The WHERE clause determines which records are returned based on criteria described in the clause. It includes conditions for selecting specific rows in a table. Operators, such as 2 (equal), > (greater than), < (less than), and <> (not equal), as well as logical operators such as AND and OR are used to create the selection criteria. “IS NULL,” is used to find fields that are empty (i.e., they con— tain no data). Remember that to a computer, a space is a APPENIDIX 2 I Hands»0n Guide to SQL 595 character and a field into which only one space has been keyed is not considered NULL. Select only records where the field SEX contains an I" in sorted order by last name and first name. SELECT LNAME, FNAME, SEX FROM tblCUSTOMER WHERE SEX = “F” ORDER BY LNAME, FNAME; Note that the letter I“ is a string a rd therefore must be enclosed in quotes. LNAME I FNAME SEX Brown Georgia F Montague Juliet lr‘ Nother Zoe F Smith Mary Allen F Smith I Marv Ann l“ Smith I Mary Ellen l“ Select only the records where SEX does not contain an F in sorted order by last name and first name. SELECT FNAME, LNAME, SEX FROM tblCUSTOMER WHERE SEX<>"F" ORDER BY LNAME, FNAME; FNAME I LNAMF. SEX Jim I Brown M Thomas Brown M I John Paul Jones M I Justin Jumpup M I Patrick I Malone M Select only the records where SEX contains an F and the zip code is 10520 in sorted order by last name and first name. SELECT LNAME, FNAME, SEX, ZIP FROM tthUSTOMER WHERE SEX = "F" AND ZIP =“10520” ORDER BY LNAME, FNAME; The logical operator AND is used to select information that is contained within two different fields. Multiple AND and OR operators can be contained within one \Nl*ll*‘.Rl*i clause. The zip code 10520 is enclosed in quotes because it is a string. The ZIP code field is a text field and even though its data may look numeric, it is stored as text characters and can only be found with a string. 1f the field had been defined as a numeric one, the quotes around 10520 would not be used. ILNAME FNAME SEX ZIP Smith Mary Ann I“ 10520 Smith I Mary Ellen F 10520 596 APPENDIX 2 [ Hands-On Guide to SQL J U l N l N E M Ll LTI F' LE TAB LES SELECT tblAPPOINTMENT.APPT_DATE, Data from two or more tables can be joined together to pro— tblAPPOINTMENT-APPTJIMEI tblCUSTOMERiNAME \‘ide data from both tables where the contents of a field FROM tblCUSTOMER found in both tables matches. Both tblCUSTOMER and INNER JOIN tblAPPOINTMENT 0N tblCUSTOMER-CUSTID : tblAPPOlNTMENT are needed to see who has an appoint- tblAPPOINTMENT-CUSUD 1nent on a particular date. Since both tables contain the WHERE (((tblCUSTOMER-SEX)="M")l? customer’s ID, the contents of this field must match for the records to be returned.- APPT_DATE LNAME 11/5/2003 8:30 AM Malone 11/7/2003 | 3:00 PM Malone 11/5/2003 | 11:30 AM um 311) SELECT APPT_DATE, APPLTIME, LNAME 11/5/2003 4:30 PM Jones FROM tthUSTOMER, tblAPPOINTMENT WHERE tblCUSTOMER.CUSTID = tbt.APPOINTMENT.CUSTID AND SEX : “M”; Select the appointment date, time, and last name ofall male customers wit/1 appointments. Above is the SQL syntax that works with most DBMS products. Access, however, uses a slightly different syntax for this particular operation. It would be: ...
View Full Document

This note was uploaded on 04/01/2012 for the course MIS 245 taught by Professor Havelka during the Spring '12 term at Miami University.

Page1 / 4

Hands+on+Guide+to+SQL (1) - fin'flw‘lmuy-v-m-‘w m1......

This preview shows document pages 1 - 4. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online