Chapter 9

1. Chapter 9 SQL Structural Query Language Foxpro Chapter 9

2. Properties • Set-oriented database language • Used to manipulate and retrieve data from relational database • Non-procedural language (what, not how) • A procedural language stresses on HOW. • A non-procedural language stresses on What. • 4GL (Fourth Generation Language) • Powerful (a few commands can do lots of work) Foxpro Chapter 9

3. Syntax • A simple subset of SQL SELECT (some fields) && Not the SELECT in FoxPro FROM (a table) ORDER BY (some fields) TO (some place) • The SQL command SELECT is not the same as the Foxpro command SELECT • SQL - SELECT is used for data retrieval • Foxpro - SELECT is used for choosing a work area Foxpro Chapter 9

4. Example • Further Practice 15 Q.3 SELECT * && all fields FROM payroll ORDER BY staff_id, year, month • Two tables will be opened • the table payroll • the cursor Foxpro Chapter 9

5. Cursor • The result of a query is called a Cursor • A non-editable table • For browsing or reporting • A browsing window appears when no TO clause or INTO clause is present Foxpro Chapter 9

6. A Larger Subset of SQL SELECT [DISTINCT] fieldName1| expr1 [AS aliasName1] [, fieldName2| expr2 AS aliasName2] FROM tableName [ORDER BY fieldname1|aliasName1 [ASC|DESC] [fieldname1|aliasName1 [ASC|DESC]]] [TO SCREEN|TO PRINTER|TO textfile|INTO TABLE tablename] • Items inside […] may be omitted • choice1 | choice2 | … | choiceN && take ONE choice Foxpro Chapter 9

7. Example • STUDENT(class, no, name, house, result) 7A 01 Chan Love 13.3 7A 15 Lee Faith 13.8 7A 09 Cheung Wisdom 12.9 7S 38 Wong Hope 13.1 7S 12 Ho Faith 14.2 Foxpro Chapter 9

8. Select Individual Columns • SELECT name, class FROM student NameClass Chan 7A Lee 7A Cheung 7A Wong 7S Ho 7S Foxpro Chapter 9

9. Queries with Distinction SELECT DISTINCT house; FROM student; (Note the use of ‘;’) Faith Hope Love Wisdom (Note: the records are automatically sorted on house) Foxpro Chapter 9

10. Order the Result of the Query • SELECT * FROM student Order by result Class No Name House Result 7A 09 Cheung Wisdom 12.9 7S 38 Wong Hope 13.1 7A 01 Chan Love 13.3 7A 15 Lee Faith 13.8 7S 12 Ho Faith 14.2 Foxpro Chapter 9

11. A Procedural Alternative USE student INDEX ON result TO student LIST • 3 commands (one only for SQL) • step by step is procedural (steps unknown in SQL) • Know HOW to index, HOW to … (In SQL, give me WHAT is required) Foxpro Chapter 9

12. Specifying alias for column SELECT name, house, result AS time; FROM student; ORDER BY time && Not by result (key word AS can be omitted) Name House Time Cheung Wisdom 12.9 Wong Hope 13.1 Chan Love 13.3 Lee Faith 13.8 Ho Faith 14.2 Foxpro Chapter 9

13. Simple Expressions in Query • SELECT name, result old, result - 0.5 new; • FROM student ORDER BY new Name OldNew Cheung 12.9 12.4 Wong 13.1 12.6 Chan 13.3 12.8 Lee 13.8 13.3 Ho 14.2 13.7 Foxpro Chapter 9

14. Revision on Functions P.286... Name is “Betty Au ” && 1 space after Au • Len(name) 9 • UPPER(name) BETTY AU • LOWER(name) betty au • LEFT(name,3) Bet • RIGHT(name,3) Au • ALLTRIM(name) Betty Au • SUSTR(name, 1, at(‘ ‘,name)-1) Betty Foxpro Chapter 9

15. Special Character Processing • STR(123.456, 8, 2) 123.46 • VAL(‘5E2’) 5*102 500.00 • ASC(‘A’) 65 • CHR(97) ‘a’ • LEN(‘ABC’) 3 Foxpro Chapter 9

16. Date P.294 or P.56 Date is {09/11/2001} • DAY(date) 11 • DOW(date) 3 && Sunday=1st day • CDOW(date) Tuesday • MONTH(date) 9 • CMONTH(date) September • YEAR(date) 2001 Foxpro Chapter 9

17. Set Date, Century, ... Date is {09/11/01} • ?date 09/11/1901 Set Century On 09/11/01 Set Century Off • ?CMONTH(date) • September date is created after set date to American • November date is created after set date to British Foxpro Chapter 9

18. Exercise Chapter 9, P. 299 • 1, 2, 5 (Theory) • 6, 7, 8 (Warm Up) • 10, 11, 12, 13, 14 TOO MUCH? Practice Makes Prefect Complete at least half before the next lesson Foxpro Chapter 9