Database Management Systems Chapter 5 Advanced Queries Jerry
Database Management Systems Chapter 5 Advanced Queries Jerry Post Copyright © 2003 1
D A T A B A S E Tables 2
D A T A B A S E Organization Harder Questions Subqueries Not In, LEFT JOIN UNION, Multiple JOIN columns, Recursive JOIN Other SQL Commands ª DDL: Data Definition Language ª DML: Data Manipulation Language OLAP ª Microsoft SQL Server ª Oracle ª Microsoft Access Crosstab 3
D A T A B A S E Harder Questions How many cats are “in-stock” on 10/1/04? Which cats sold for more than the average price? Which animals sold for more than the average price of animals in their category? Which animals have not been sold? Which customers (who bought something at least once) did not buy anything between 11/1/04 and 12/31/04? Which customers who bought Dogs also bought products for Cats (at any time)? 4
D A T A B A S E Sub-query for Calculation Which cats sold for more than the average sale price of cats? ª Assume we know the average price is $170. ª Usually we need to compute it first. SELECT Sale. Animal. ID, Animal. Category, Sale. Animal. Sale. Price FROM Animal INNER JOIN Sale. Animal ON Animal. ID = Sale. Animal. ID WHERE ((Animal. Category=‘Cat’) AND (Sale. Animal. Sale. Price>170)); SELECT Sale. Animal. ID, Animal. Category, Sale. Animal. Sale. Price FROM Animal INNER JOIN Sale. Animal ON Animal. ID = Sale. Animal. ID WHERE ((Animal. Category=‘Cat’) AND (Sale. Animal. Sale. Price> ( SELECT AVG(Sale. Price) FROM Animal INNER JOIN Sale. Animal ON Animal. ID = Sale. Animal. ID WHERE (Animal. Category=‘Cat’) ) ) ); 5
D A T A B A S E Query 04_13 Query Sets (IN) SELECT Customer. Last. Name, Customer. First. Name, Sale. Item. ID FROM (Customer INNER JOIN Sale ON Customer. ID = Sale. Customer. ID) INNER JOIN Sale. Item ON Sale. ID = Sale. Item. Sale. ID WHERE (Sale. Item. ID In (1, 2, 30, 32, 33)) ORDER BY Customer. Last. Name, Customer. First. Name; Customer Sale Customer. ID Phone First. Name Last. Name Sale. ID Sale. Date Employee. ID Customer. ID Field Last. Name First. Name Item. ID Table Customer Sale. Item Sort Ascending Criteria Sale. Item Sale. ID Item. ID Quantity Sale. Price In (1, 2, 30, 32, 33) Or List all customers (Name) who purchased one of the following items: 1, 2, 30, 32, 33. 6
D A T A B A S E Using IN with a Sub-query List all customers who bought items for cats. SELECT Customer. Last. Name, Customer. First. Name, Sale. Item. ID FROM (Customer INNER JOIN Sale ON Customer. ID = Sale. Customer. ID) INNER JOIN Sale. Item ON Sale. ID = Sale. Item. Sale. ID WHERE (Sale. Item. ID In (SELECT Item. ID FROM Merchandise WHERE Category=‘Cat’) ); 7
D A T A B A S E Query 04_14 Sub. Query (IN: Look up a Set) SELECT Customer. Last. Name, Customer. First. Name FROM Customer INNER JOIN Sale ON Customer. ID = Sale. Customer. ID WHERE ((Month([Sale. Date])=3)) And Customer. ID In (SELECT Customer. ID FROM Sale WHERE (Month([Sale. Date])=5) ); Customer Sale Customer. ID Phone First. Name Last. Name Sale. ID Sale. Date Employee. ID Customer. ID Last. Name First Adkins Inga Mc. Cain Sam Grimes Earl Field Last. Name First. Name Month(Sale. Date) Customer. ID Table Customer Sale Customer Sort Ascending 3 In (SELECT Customer. ID FROM State WHERE (Month(Sale. DAte)=5) Criteria Or List all of the customers who bought something in March and who bought something in May. (Two tests on the same data!) 8
D A T A B A S E Query 04_15 Sub. Query (ANY, ALL) SELECT Animal. ID, Name, Sale. Price, List. Price FROM Animal INNER JOIN Sale. Animal ON Animal. ID = Sale. Animal. ID WHERE ((Sale. Price > Any (SELECT 0. 80*List. Price FROM Animal INNER JOIN Sale. Animal ON Animal. ID = Sale. Animal. ID WHERE Category = ‘Cat’)) AND (Category=‘Cat’); Any: value is compared to each item in the list. If it is True for any of the items, the statement is evaluated to True. All: value is compared to each item in the list. If it is True for every item in the list, the statement is evaluated to True (much more restrictive than any. 9
D A T A B A S E Query 04_16 Sub. Query: NOT IN (Subtract) Animal. ID Name Category Breed SELECT Animal. ID, Animal. Name, Animal. Category FROM Animal WHERE (Animal. ID Not In (SELECT Animal. ID From Sale. Animal)); Field Animal. ID Name Category Table Animal Sort Criteria Not In (SELECT Animal. ID FROM Sale. Animal) Or Animal. ID Name 12 Leisha 19 Gene 25 Vivian 34 Rhonda 88 Brandy 181 Category Dog Dog Dog Fish Which animals have not been sold? ª Start with list of all animals. ª Subtract out list of those who were sold. 10
D A T A B A S E Sub. Query: NOT IN (Data) Animal ID 2 4 5 6 7 8 9 10 11 12 Name Category Fish Gary Dog Fish Rosie Cat Eugene Cat Miranda Dog Fish Sherri Dog Susan Dog Leisha Dog Sale. Animal Breed Angel Dalmation Shark Oriental Shorthair Bombay Norfolk Terrier Guppy Siberian Huskie Dalmation Rottweiler ID 2 4 6 7 8 10 11 Sale. ID 35 80 27 25 4 18 17 Sale. Price $10. 80 $156. 66 $173. 99 $251. 59 $183. 38 $150. 11 $148. 47 Which animals have not been sold? 11
D A T A B A S E Query 04_17 Left Outer Join SELECT Animal. ID, Animal. Name, Animal. Category FROM Animal LEFT JOIN Sale. Animal ON Animal. ID = Sale. Animal. ID WHERE (Sale. Animal. Sale. ID Is Null); Animal Sale. Animal. ID Name Category Breed Sale. ID Animal. ID Sale. Price Field Animal. ID Sale. ID Name Category Table Animal Sale. Animal. ID Name Category 12 Leisha Dog 19 Gene Dog 25 Vivian Dog 34 Rhonda Dog 88 Brandy Dog 181 Fish Sort Criteria Is Null Or Which animals have not been sold? LEFT JOIN includes all rows from left table (Animal) ª But only those from right table (Sale. Animal) that match a row in Animal. ª Rows in Animal without matching data in Sale Animal will have Null. 12
D A T A B A S E Left Outer Join (Example) ID 2 4 5 6 7 8 9 10 11 12 Name Category Fish Gary Dog Fish Rosie Cat Eugene Cat Miranda Dog Fish Sherri Dog Susan Dog Leisha Dog Breed Angel Dalmation Shark Oriental Shorthair Bombay Norfolk Terrier Guppy Siberian Huskie Dalmation Rottweiler IDSale. ID Sale. Price 235 $10. 80 480 $156. 66 Null 627 $173. 99 725 $251. 59 84 $183. 38 Null 1018 $150. 11 1117 $148. 47 Null 13
D A T A B A S E Older Syntax for Left Join Which animals have not been sold? SELECT ALL FROM Animal, Sale. Animal WHERE Animal. ID *= Sale. Animal. ID And Sale. Animal. Sale. ID Is Null; Old Oracle syntax— note that the (+) symbol is on the reversed side. SELECT ALL FROM Animal, Sale. Animal WHERE Animal. ID = Sale. Animal. ID (+) And Sale. Animal. Sale. ID Is Null; 14
D A T A B A S E Query 04_18 Sub. Query for Computation SELECT Sale. Animal. ID, Animal. Category, Sale. Animal. Sale. Price FROM Animal INNER JOIN Sale. Animal ON Animal. ID = Sale. Animal. ID WHERE ((Animal. Category=‘Cat’) AND (Sale. Animal. Sale. Price> ( SELECT AVG(Sale. Price) FROM Animal INNER JOIN Sale. Animal ON Animal. ID = Sale. Animal. ID WHERE (Animal. Category=‘Cat’) ) ) ); Animal Sale. Animal. ID Name Category Breed Sale. ID Animal. ID Sale. Price Don’t know the average, so use a subquery to look it up. Watch parentheses. Field Animal. ID Name Category Sale. Price Table Animal Sale. Animal Sort Criteria Or Descending 3 > (SELECT Avg(Sale. Price) FROM Animal INNER JOIN Sale. Animal ON Animal. ID = Sale. Animal. ID WHERE Animal. Category = ‘Cat’) 15
D A T A B A S E Correlated Subquery List the Animals that have sold for a price higher than the average for animals in that Category. SELECT Animal. ID, Name, Category, Sale. Price FROM Animal INNER JOIN Sale. Animal ON Animal. ID = Sale. Animal. ID WHERE (Sale. Animal. Sale. Price> (SELECT Avg(Sale. Animal. Sale. Price) FROM Animal INNER JOIN Sale. Animal ON Animal. ID = Sale. Animal. ID WHERE (Animal. Category = Animal. Category) ) ) ORDER BY Sale. Animal. Sale. Price DESC; The subquery needs to compute the average for a given category. Problem: Which category? Answer: the category that matches the category from the main part of the query. Problem: How do we refer to it? Both tables are called Animal. This query will not work yet. 16
D A T A B A S E Query 04_19 Correlated Sub. Query (Avoid) Match category in subquery with top level ª Rename tables (As) Correlated Subquery ª Recompute subquery for every row in top level--slow! ª Better to compute and save Subquery, then use in join. SELECT A 1. Animal. ID, A 1. Name, A 1. Category, Sale. Animal. Sale. Price FROM Animal As A 1 INNER JOIN Sale. Animal ON A 1. Animal. ID = Sale. Animal. ID WHERE (Sale. Animal. Sale. Price> (SELECT Avg(Sale. Animal. Sale. Price) FROM Animal As A 2 INNER JOIN Sale. Animal ON A 2. Animal. ID = Sale. Animal. ID WHERE (A 2. Category = A 1. Category) ) ) ORDER BY Sale. Animal. Sale. Price DESC; List the Animals that have sold for a price higher than the average for animals in that Category. 17
D A T A B A S E Correlated Subquery Problem Animal + Sale. Animal Category Sale. Price Fish Dog Fish Cat Dog Fish Dog $10. 80 $156. 66 $19. 80 $173. 99 $251. 59 $183. 38 $1. 80 $150. 11 $148. 47 Compute Avg: $37. 78 Compute Avg: $174. 20 Compute Avg: $37. 78 Compute Avg: $169. 73 Recompute average for every row in the main query! Assume small query ª 100, 000 rows ª 5 categories of 20, 000 rows 100, 000 * 20, 000 = 1 billion rows to read! 18
D A T A B A S E More Efficient Solution: 2 queries Animal + Sale. Animal Saved Query Category Sale. Price Fish Dog Fish Cat Dog Fish Dog $10. 80 $156. 66 $19. 80 $173. 99 $251. 59 $183. 38 $1. 80 $150. 11 $148. 47 Category Avg. Of. Sale. Price JOIN Animal. Category = Query 1. Category Bird Cat Dog Fish Mammal Reptile Spider $176. 57 $169. 73 $174. 20 $37. 78 $80. 72 $181. 83 $118. 16 Compute the averages once and save query JOIN saved query to main query Two passes through table: 1 billion / 200, 000 => 10, 000 19
D A T A B A S E UNION Operator SELECT EID, Name, Phone, Salary, ‘East’ AS Office FROM Employee. East UNION SELECT EID, Name, Phone, Salary, ‘West’ AS Office FROM Employee. West EID 352 876 372 Name Jones Inez Stoiko Phone 3352 8736 7632 Salary 45, 000 47, 000 38, 000 Office East 890 361 Smythe Kim 9803 7736 62, 000 73, 000 West Offices in Los Angeles and New York. Each has an Employee table (East and West). Need to search data from both tables. Columns in the two SELECT lines must match. 20
D A T A B A S E UNION, INTERSECT, EXCEPT A B T 1 C List the name of any employee who has worked for both the East and West regions. T 2 SELECT EID, Name FROM Employee. East INTERSECT SELECT EID, Name FROM Employee. West 21
D A T A B A S E Multiple JOIN Columns Breed Category Breed Animal. ID Name Category Breed Date. Born Gender. . . SELECT * FROM Breed INNER JOIN Animal ON Breed. Category = Animal. Category AND Breed = Animal. Breed Sometimes need to JOIN tables on more than one column. Pet. Store: Category and Breed. 22
D A T A B A S E Reflexive Join Employee SQL SELECT Employee. EID, Employee. Name, Employee. Manager, E 2. Name EID 115 462 523 765 Name. . . Sanchez Miller Hawk Munoz Manager 765 115 886 FROM Employee INNER JOIN Employee AS E 2 Result ON Employee. Manager = E 2. EID 115 462 523 Name Sanchez Miller Hawk Manager 765 115 Name Munoz Sanchez Need to connect a table to itself. Common example: Employee(EID, Name, . . . , Manager) ª A manager is also an employee. ª Use a second copy of the table and an alias. 23
D A T A B A S E Recursive Joins (SQL 99 and 200 x) WITH RECURSIVE Employee. List (Employee. ID, Title, Salary) AS ( SELECT Employee. ID, Title, 0. 00 FROM Manages WHERE Title = “CEO” -- starting level UNION ALL SELECT Manages. Employee. ID, Manages. Title, Manages. Salary FROM Employee. List INNER JOIN Manages ON Employee. List. Employee. ID = Manages. Manager. ID ) SELECT Employee. ID, Count(Title), Sum(Salary) FROM Employee. List GROUP BY Employe. EID ; List all of the employees and list everyone who reports to them. Not yet supported by vendors. It provides tree spanning capabilities. 24
D A T A B A S E Not available in Microsoft Access. It is in SQL Server and Oracle. CASE Function Select Animal. ID, CASE WHEN Date()-Date. Born < 90 Then “Baby” WHEN Date()-Date. Born >= 90 AND Date()-Date. Born < 270 Then “Young” WHEN Date()-Date. Born >= 270 AND Date()-Date. Born < 365 Then “Grown” ELSE “Experienced” END FROM Animal; Used to change data to a different context. Example: Define age categories for the animals. ª ª Less than 3 months Between 3 months and 9 months Between 9 months and 1 year Over 1 year 25
D A T A B A S E Inequality Join Accounts. Receivable Categorize by Days Late ª 30, 90, 120+ Three queries? New table for business rules AR(Transaction. ID, Customer. ID, Amount, Date. Due) Late. Category(Category, Min. Days, Max. Days, Charge, …) Month Quarter Overdue 30 90 120 9999 3% 5% 10% SELECT * FROM AR INNER JOIN Late. Category ON ((Date() - AR. Date. Due) >= Late. Category. Min. Days) AND ((Date() - AR. Date. Due) < Late. Category. Max. Days) 26
D A T A B A S E SQL SELECT DISTINCT Table. Column {AS alias} , . . . FROM Table/Query INNER JOIN Table/Query ON T 1. Col. A = T 2. Col. B WHERE (condition) GROUP BY Column HAVING (group condition) ORDER BY Table. Column { Union second select } 27
D A T A B A S E SQL Mnemonic Someone SELECT From FROM Ireland INNER JOIN Will WHERE Grow GROUP BY Horseradish and HAVING Onions ORDER BY SQL is picky about putting the commands in the proper sequence. If you have to memorize the sequence, this mnemonic may be helpful. 28
D A T A B A S E SQL Data Definition Create Schema Authorization db. Name password Create Table. Name (Column Type, . . . ) Alter Table {Add, Column, Constraint, Drop} Drop {Table | Index On table} Create Index. Name ON Table (Column {ASC|DESC}) 29
D A T A B A S E Syntax Examples CREATE TABLE Customer (Customer. ID INTEGER NOT NULL, Last. Name CHAR (10), more columns ); ALTER TABLE Customer DROP COLUMN Zip. Code; ALTER TABLE Customer ADD COLUMN Cell. Phone CHAR(15); 30
D A T A B A S E Queries with “Every” Need EXISTS List the employees who have sold animals from every category. By hand: List the employees and the categories. Go through the Sale. Animal list and check off the animals they have sold. 31
D A T A B A S E Query With EXISTS List the Animal categories that have not been sold by an employee (#5). SELECT Category FROM Category WHERE (Category <> "Other") And Category NOT IN (SELECT Animal. Category FROM Animal INNER JOIN (Sale INNER JOIN Sale. Animal ON Sale. ID = Sale. Animal. Sale. ID) ON Animal. ID = Sale. Animal. ID WHERE Sale. Employee. ID = 5) If this query returns any rows, then the employee has not sold every animal. So list all the employees for whom the above query returns no rows: SELECT Employee. ID, Last. Name FROM Employee WHERE NOT EXISTS (above query slightly modified. ) 32
D A T A B A S E Query for Every SELECT Employee. ID, Employee. Last. Name FROM Employee WHERE Not Exists (SELECT Category FROM Category WHERE (Category <> "Other") And Category NOT IN (SELECT Animal. Category FROM Animal INNER JOIN (Sale INNER JOIN Sale. Animal ON Sale. ID = Sale. Animal. Sale. ID) ON Animal. ID = Sale. Animal. ID WHERE Sale. Employee. ID = Employee. ID) ); Result: 3 Reasoner 33
D A T A B A S E Simpler Query for Every Sometimes it is easier to use Crosstab and the Count function. But some systems do not have Crosstab, and sometimes the lists would be too long. So you need to know both techniques. 34
D A T A B A S E SQL: Foreign Key CREATE TABLE Order (Order. ID INTEGER NOT NULL, Order. Date DATE, Customer. ID INTEGER CONSTRAINT pkorder PRIMARY KEY (Order. ID), CONSTRAINT fkorder FOREIGN KEY (Customer. ID) REFERENCES Customer (Customer. ID) ); Order Customer Order. ID Order. Date Customer. ID * Customer. ID Last. Name First. Name Address … 35
D A T A B A S E SQL Data Manipulation Commands Insert Into target (column 1. . . ) VALUES (value 1. . . ) Insert Into target (column 1. . . ) SELECT. . . FROM. . . Delete From table WHERE condition Update table SET Column=Value, . . . Where condition Note the use of the Select and Where conditions. Synatx is the same--only learn it once. You can also use subqueries. 36
D A T A B A S E Copy Old Animal Data INSERT INTO Old. Animals SELECT * FROM Animal WHERE Animal. ID IN (SELECT Animal. Order. Item. Animal. ID FROM Animal. Order INNER JOIN Animal. Order. Item ON Animal. Order. ID = Animal. Order. Item. Order. ID WHERE (Animal. Order. Date<’ 01 -Jan-2004’) ); 37
D A T A B A S E Delete Old Animal Data DELETE FROM Animal WHERE Animal. ID IN (SELECT Animal. Order. Item. Animal. ID FROM Animal. Order INNER JOIN Animal. Order. Item ON Animal. Order. ID = Animal. Order. Item. Order. ID WHERE (Animal. Order. Date<’ 01 -Jan-2004’) ); 38
D A T A B A S E Update Example UPDATE Animal SET List. Price = List. Price*1. 10 WHERE Category = ‘Cat’ ; UPDATE Animal SET List. Price = List. Price*1. 20 WHERE Category = ‘Dog’ ; Change the List. Price of Animals at the Pet. Store. ª For cats, increase the List. Price by 10%. ª For dogs, increase the List. Price by 20%. Typically use two similar UPDATE statements. With the CASE function, the statements can be combined. 39
D A T A B A S E Quality: Building Queries Break questions into smaller pieces. Test each query. Which customers who bought Dogs ª Check the SQL. also bought products for Cats ª Look at the data. (at any time)? ª Check computations Combine into subqueries. ª Use cut-and-paste to avoid errors. ª Check for correlated subqueries. Test sample data. Who bought dogs? Who bought cat products? ª Identify different cases. ª Check final query and subqueries. ª Verify calculations. Dogs and cat products on the same sale. Dogs and cat products at different times. Test SELECT queries Dogs and never any cat products. before executing Cat products and never any Dogs. UPDATE queries. 40
D A T A B A S E Quality Queries: Example Which customers who bought Dogs also bought products for Cats? A. Which customers bought dogs? B. Which customers bought cat products? SELECT DISTINCT Animal. Category, Sale. Customer. ID FROM Sale INNER JOIN (Animal INNER JOIN Sale. Animal ON Animal. ID = Sale. Animal. ID) ON Sale. ID = Sale. Animal. Sale. ID WHERE (((Animal. Category)=‘Dog’)) AND Sale. Customer. ID IN ( SELECT DISTINCT Sale. Customer. ID FROM Sale INNER JOIN (Merchandise INNER JOIN Sale. Item ON Merchandise. Item. ID = Sale. Item. ID) ON Sale. ID = Sale. Item. Sale. ID WHERE (((Merchandise. Category)=‘Cat’)) ); 41
D A T A B A S E Programming Review: Variables Integer ª 2 bytes ª -32768 32767 Long ª 4 bytes ª +/- 2, 147, 483, 648 Single ª 4 bytes ª +/- 3. 402823 E 38 ª +/- 1. 401298 E-45 Global, Const, Static Double ª 8 bytes ª +/- 1. 79769313486232 E 308 ª +/- 4. 94065645841247 E-324 Currency ª 8 bytes ª +/- 922, 337, 203, 685, 477. 5808 String & String*n Variant ª Any data type ª Null 42
D A T A B A S E Programming: Scope and Lifetime Scope ª Where is the variable, and which procedures can access it? Lifetime ª When is the variable created, and when is it destroyed? Different procedures, different variables. Created and destroyed each time the button is clicked. Form Button 1 Button 2 Form--Module Code Sub Button 1_Click() Dim i 1 As Integer i 1 = 3 End Sub Button 2_Click() Dim i 1 As Integer i 1 = 7 End Sub 43
D A T A B A S E Programming: Global Variables Wider scope and lifetime ª Created at a higher level © Form © Public module ª Accessible to any procedure in that form or module. ª Declare it Global to make it available to any procedure. Form Button 1 Button 2 Form--Module Code Dim i 2 As Integer Sub Button 1_Click() i 2 = 20 End Sub Variable is created when form is opened. Sub Button 2_Click() Clicking Button 1 sets the i 2 = i 2 + 7 initial value. End Sub Clicking Button 2 modifies the value. What if user clicks buttons in a different order? 44
D A T A B A S E Programming: Computations Standard Math ª+ - * / ª Integer divide ª ^ Exponentiation © (2^3 = 2*2*2 = 8) ª Mod © (15 Mod 4 = 3) (12 + 3 = 15) “Frank” & “Rose” “Frank. Rose” Left(“Jackson”, 5) “Jacks” Trim(“ Maria “) “Maria” String ª ª ª ª ª & Concatenation Left, Right, Mid Trim, LTrim, RTrim String Chr, Asc LCase, UCase In. Str Len Str. Comp Format Len(“Ramanujan”) 9 String(5, ”a”) “aaaaa” In. Str(“ 8764 Main”, ” “) 5 45
D A T A B A S E Programming: Standard Functions Numeric ª ª ª ª Exp, Log Atn, Cos, Sin, Tan Sqr Abs Sgn Int, Fix Rnd, Randomize =30 x = loge 92 Trigonometric functions (ex) ? 2 = 1. 414 Abs(-35) 35 Sgn(-35) -1 Int(17. 893) 17 Rnd() 0. 198474 46
D A T A B A S E Programming: Standard Functions: Date/Time Date, Now, Time Date. Add, Date. Diff ª ª “y”, “m”, “q”. . . Firstweekday 1=Sunday, . . . Can also be used to find number of Fridays, between two dates. 02/19/04 today 03/21/04 Date. Due = Date. Add(“d”, 30, Date()) 47
D A T A B A S E Programming: Standard Functions: Variant ª ª ª Is. Date Is. Numeric Var. Type Is. Empty Is. Null 48
D A T A B A S E Programming: Debug Stop Ctrl-Break F 5: Go F 8: Step through S-F 8: Step over Breakpoints Immediate Window ª ? or Print ª Any assignment ª Any code 49
D A T A B A S E Msg. Box Programming: Output: Message Box ª Message ª Type ª Title Types: Use Constants ª ª vb. OKOnly vb. OKCancel vb. Abort. Retry. Ignore vb. Yes. No. Cancel vb. Yes. No vb. Retry. Cancel Defaults ª vb. Default. Button 1 ª vb. Default. Button 2 ª vb. Default. Button 3 Icons ª ª vb. Critical Stop sign vb. Question mark vb. Exclamation Warning vb. Information Circle i Responses ª ª vb. OK vb. Cancel vb. Abort vb. Retry vb. Ignore vb. Yes vb. No Msg. Box "This is a message box", vb. Yes. No. Cancel + vb. Information, "Sample Box" 50
D A T A B A S E Input. Box ª ª Programming: Input. Box Prompt Title Default X-Pos, Y-Pos Prompt ª Cannot change box size ª Use Chr(10) & Chr(13) for blank lines. Returns text or Variant Cancel = zero string ““ Positions ª ª Twips Twentieth of inch point 72 points 1440 twips per inch Dim str As String str = Input. Box( "Enter your name: ", "Sample Input", , 5000) 51
D A T A B A S E Programming: Conditions If Conditions ª If (Condition) Then © statements for true ª Else ª <, <=, >, >=, =, <> ª And, Or, Not, Xor ª Eqv, Imp (logic) © statements for false ª End If IIF (Cond. , True, False) Select Case (expr) ª Case value © statements ª Case value 2 ª Case Else ª End Select If (Condition 1) Then statements for true Else statements for false If (Condition 2) Then statements for true End If 52
D A T A B A S E Programming Select Example Message Box Could use repeated If statements Better to use Select Case response = Msg. Box(…) If (response == vb. Yes) Then ‘ statements for Yes Else If (response == vb. No) Then ‘ statements for No Else ‘statements for Cancel End If response = Msg. Box(…) Select Case response Case vb. Yes ‘ statements for Yes Case vb. No ‘ statements for No Case vb. Cancel ‘ statements for Cancel End Case 53
D A T A B A S E Programming: Loops Initialize value Statements Do For … Next For Each Change value Test condition Do Until (x > 10) Do While (x <= 10) ‘ Statements x=x+1 Loop Do For x = 1 to 10 ‘ Statements x=x+1 ‘ Statements Next x Loop Until (x > 10) 54
D A T A B A S E Programming: Loops Again Do For/Each (objects) ª Do {While | Until} © Exit Do (optional) ª Loop ª For Each element In group © [Exit For] (optional) ª Next element With (objects) ª Do ª Loop {While | Until} ª With object ª End With For/Next ª For counter = start To end Step increment © Exit For (optional) ª Next counter 55
D A T A B A S E Programming Subroutines and Functions Sub name (var 1 As. . . , var 2, . . . ) End Sub Function fname (var 1 As. . . ) As datatype ª fname = … ‘ returns a specific value End Function Variables are passed by reference ª Changes made to the parameters in the subroutine are passed back to the caller. Unless you use By. Val ª Changes are made to a copy of the parameter, but are not returned to the calling program. 56
D A T A B A S E Programming: Example Subroutine Main program … Status. Message “Trying to connect. ” … Status. Message “Verifying access. ” … End main program Sub Status. Message (Msg As String) ‘ Display Msg, location, color End Sub 57
D A T A B A S E Programming: Parameter Types Main j=3 Do. Sum j … ‘ j is now equal to 8 Subroutine Do. Sum (j 2 As Integer) j 2 = 8 End Sub Main j=3 Do. Sum j … ‘ j is still equal to 3 Subroutine Do. Sum (By. Val j 2 As Integer) j 2 = 8 End Sub By Reference Changes to data in the subroutine are passed back. By Value Creates a copy of the variable, so changes are not returned. 58
D A T A B A S E Programming Arrays and User Types u Arrays l l u u Dim array(sub, . . . ) As type Dim i. Sorts(10) As Integer l l (lower To upper, . . . ) Re. Dim [Preserve] array. . . Option Base 0 | 1 v 2. 0 arrays less than 64 KB Type Tname v v Specifying bounds: l User defined types l u u u ename 1 As type ename 2 As type End Type Dim var 1 As Tname var 1. ename 1 =. . . var 1. ename 2 =. . . 59
D A T A B A S E Programming: Financial Functions u Fixed payments l l l l u u u PV (rate, nper, pmt, fv, due) FV (rate, nper, pmt, pv, due) IPmt (rate, per, nper, pv, fv, due) NPer (rate, pmt, pv, fv, due) Pmt (rate, nper, pv, fv, due) PPmt (rate, per, nper, pv, fv, due) Rate (nper, pmt, pv, fv, due, guess) u Arrays l l l u NPV (rate, array) IRR (array, guess) MIRR (array, finrate, re_rate) Depreciation l l l DDB (cost, salv, life, period) SLN (cost, salvage, life) SYD (cost, salv. , life, period) rate interest rate period per specific period number nper # of periods pv present value fv future value due 0=due at end, 1=due at start 60
D A T A B A S E Programming: Text File Input/Output Open filename As # file# Close # file#, Reset Print #, Put, Write Spc, Tab Get, Input #, Line Input # EOF, LOF Seek # file#, position Ch. Dir, Ch. Dirve Dir Kill, (re)Name Lock, Unlock Cur. Dir, Mk. Dir, Rm. Dir 61
D A T A B A S E OLE: Object Linking & Embedding Create. Object (class) ª “appname. objecttype” Get. Object (file, class) Methods and syntax are defined by the software that exports the object. Example ª Dim obj As Object ª set obj = Create. Object(“Word. Basic”) ª obj. Bold ª obj. Insert “text” ª obj. Save. As “file” 62
D A T A B A S E DDE: Dynamic Data Exchange Shell DDEInitiate DDEExecute DDEPoke, DDE Send Application must be running Start a conversation/topic Issue a command Place data ª Send data DDE, DDERequest Get data ª Request data DDETerminate Close the session 63
- Slides: 63