Database Management Systems

Database Management Systems Chapter 5 Advanced Queries Jerry Post Copyright © 2003 1

D A T A B A S E

Tables Animal AnimalOrder OrderID OrderDate ReceiveDate SupplierID ShippingCost EmployeeID Supplier SupplierID Name ContactName Phone Address ZipCode CityID

City CityID ZipCode City State AreaCode Population1990 Population1980 Country Latitude Longitude Merchandise Order PONumber OrderDate ReceiveDate SupplierID EmployeeID ShippingCost

Animal OrderItem OrderID AnimalID Cost

Employee EmployeeID LastName FirstName Phone Address ZipCode CityID TaxPayerID DateHired DateReleased

OrderItem PONumber ItemID Quantity Cost

Breed Category Breed

AnimalID Name Category Breed DateBorn Gender Registered Color ListPrice Photo Sale

Category

SaleID SaleDate EmployeeID CustomerID SalesTax

Category Registration

SaleAnimal SaleID AnimalID SalePrice

Customer CustomerID Phone FirstName LastName Address ZipCode CityID

SaleItem Merchandise ItemID Description QuantityOnHand ListPrice Category

SaleID ItemID Quantity SalePrice

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 “instock” 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 SaleAnimal.AnimalID, Animal.Category, SaleAnimal.SalePrice FROM Animal INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID WHERE ((Animal.Category=‘Cat’) AND (SaleAnimal.SalePrice>170)); SELECT SaleAnimal.AnimalID, Animal.Category, SaleAnimal.SalePrice FROM Animal INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID WHERE ((Animal.Category=‘Cat’) AND (SaleAnimal.SalePrice> ( SELECT AVG(SalePrice) FROM Animal INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID WHERE (Animal.Category=‘Cat’) ) ) );

5

D A T A B A S E

Query04_13

Query Sets (IN) SELECT Customer.LastName, Customer.FirstName, SaleItem.ItemID FROM (Customer INNER JOIN Sale ON Customer.CustomerID = Sale.CustomerID) INNER JOIN SaleItem ON Sale.SaleID = SaleItem.SaleID WHERE (SaleItem.ItemID In (1,2,30,32,33)) ORDER BY Customer.LastName, Customer.FirstName;

Customer

Sale

CustomerID Phone FirstName LastName

SaleID SaleDate EmployeeID CustomerID

Field

LastName

FirstName

ItemID

Table

Customer

Customer

SaleItem

Sort

Ascending

Ascending

Criteria

SaleItem SaleID ItemID Quantity SalePrice

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.LastName, Customer.FirstName, SaleItem.ItemID FROM (Customer INNER JOIN Sale ON Customer.CustomerID = Sale.CustomerID) INNER JOIN SaleItem ON Sale.SaleID = SaleItem.SaleID WHERE (SaleItem.ItemID In (SELECT ItemID FROM Merchandise WHERE Category=‘Cat’) );

7

D A T A B A S E

Query04_14

SubQuery (IN: Look up a Set) SELECT Customer.LastName, Customer.FirstName FROM Customer INNER JOIN Sale ON Customer.CustomerID = Sale.CustomerID WHERE ((Month([SaleDate])=3)) And Customer.CustomerID In (SELECT CustomerID FROM Sale WHERE (Month([SaleDate])=5) );

Customer

Sale

CustomerID Phone FirstName LastName

SaleID SaleDate EmployeeID CustomerID

LastName Adkins McCain Grimes

First Inga Sam Earl

Field

LastName

FirstName

Month(SaleDate)

CustomerID

Table

Customer

Customer

Sale

Customer

Sort

Ascending

Ascending 3

In (SELECT CustomerID FROM State WHERE (Month(SaleDAte)=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

Query04_15

SubQuery (ANY, ALL) SELECT Animal.AnimalID, Name, SalePrice, ListPrice FROM Animal INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID WHERE ((SalePrice > Any (SELECT 0.80*ListPrice FROM Animal INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID 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

Query04_16

SubQuery: NOT IN (Subtract) Animal AnimalID Name Category Breed

SELECT Animal.AnimalID, Animal.Name, Animal.Category FROM Animal WHERE (Animal.AnimalID Not In (SELECT AnimalID From SaleAnimal));

Field

AnimalID

Name

Category

Table

Animal

Animal

Animal

Sort Criteria

Not In (SELECT AnimalID FROM SaleAnimal)

Or

AnimalID 12 19 25 34 88 181

Name Leisha Gene Vivian Rhonda Brandy

Category Dog Dog 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

SubQuery: 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

SaleAnimal Breed Angel Dalmation Shark Oriental Shorthair Bombay Norfolk Terrier Guppy Siberian Huskie Dalmation Rottweiler

ID 2 4 6 7 8 10 11

SaleID 35 80 27 25 4 18 17

SalePrice $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

Query04_17

Left Outer Join SELECT Animal.AnimalID, Animal.Name, Animal.Category FROM Animal LEFT JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID WHERE (SaleAnimal.SaleID Is Null);

Animal

SaleAnimal

AnimalID Name Category Breed

SaleID AnimalID SalePrice

Field

AnimalID

SaleID

Name

Category

Table

Animal

SaleAnimal

Animal

Animal

AnimalID 12 19 25 34 88 181

Name Leisha Gene Vivian Rhonda Brandy

Category Dog Dog Dog Dog Dog 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 (SaleAnimal) 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

ID 2 4 Null 6 7 8 Null 10 11 Null

SaleID 35 80 Null 27 25 4 Null 18 17 Null

SalePrice $10.80 $156.66 Null $173.99 $251.59 $183.38 Null $150.11 $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, SaleAnimal WHERE Animal.AnimalID *= SaleAnimal.AnimalID And SaleAnimal.SaleID Is Null;

Old Oracle syntax— note that the (+) symbol is on the reversed side. SELECT ALL FROM Animal, SaleAnimal WHERE Animal.AnimalID = SaleAnimal.AnimalID (+) And SaleAnimal.SaleID Is Null; 14

D A T A B A S E

Query04_18

SubQuery for Computation

SELECT SaleAnimal.AnimalID, Animal.Category, SaleAnimal.SalePrice FROM Animal INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID WHERE ((Animal.Category=‘Cat’) AND (SaleAnimal.SalePrice> ( SELECT AVG(SalePrice) FROM Animal INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID WHERE (Animal.Category=‘Cat’) ) ) );

Animal

SaleAnimal

AnimalID Name Category Breed

SaleID AnimalID SalePrice

 Don’t know the average, so use a subquery to look it up.  Watch parentheses.

Field

AnimalID

Name

Category

SalePrice

Table

Animal

Animal

Animal

SaleAnimal

Sort Criteria

Or

Descending 3

> (SELECT Avg(SalePrice) FROM Animal INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID 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 AnimalID, Name, Category, SalePrice FROM Animal INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID WHERE (SaleAnimal.SalePrice> (SELECT Avg(SaleAnimal.SalePrice) FROM Animal INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID WHERE (Animal.Category = Animal.Category) ) ) ORDER BY SaleAnimal.SalePrice 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

Query04_19

Correlated SubQuery (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 A1.AnimalID, A1.Name, A1.Category, SaleAnimal.SalePrice FROM Animal As A1 INNER JOIN SaleAnimal ON A1.AnimalID = SaleAnimal.AnimalID WHERE (SaleAnimal.SalePrice> (SELECT Avg(SaleAnimal.SalePrice) FROM Animal As A2 INNER JOIN SaleAnimal ON A2.AnimalID = SaleAnimal.AnimalID WHERE (A2.Category = A1.Category) ) ) ORDER BY SaleAnimal.SalePrice 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 + SaleAnimal Category Fish Dog Fish Cat Cat Dog Fish Dog Dog

SalePrice $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 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 + SaleAnimal Category Fish Dog Fish Cat Cat Dog Fish Dog Dog

Saved Query

SalePrice $10.80 $156.66 $19.80 $173.99 $251.59 $183.38 $1.80 $150.11 $148.47

Category AvgOfSalePrice

JOIN Animal.Category = Query1.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 EmployeeEast UNION SELECT EID, Name, Phone, Salary, ‘West’ AS Office FROM EmployeeWest

   

EID 352 876 372

Name Jones Inez Stoiko

Phone 3352 8736 7632

Salary 45,000 47,000 38,000

Office East East East

890 361

Smythe Kim

9803 7736

62,000 73,000

West 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

T1

C

List the name of any employee who has worked for both the East and West regions.

T2 SELECT EID, Name FROM EmployeeEast INTERSECT SELECT EID, Name FROM EmployeeWest

21

D A T A B A S E

Multiple JOIN Columns Breed Category Breed

Animal AnimalID Name Category Breed DateBorn Gender ...

SELECT * FROM Breed INNER JOIN Animal ON Breed.Category = Animal.Category AND Breed.Breed = Animal.Breed

 Sometimes need to JOIN tables on more than one column.  PetStore: Category and Breed. 22

D A T A B A S E

Reflexive Join Employee SQL SELECT Employee.EID, Employee.Name, Employee.Manager, E2.Name

EID 115 462 523 765

Name ... Sanchez Miller Hawk Munoz

Manager 765 115 115 886

FROM Employee INNER JOIN Employee AS E2 ON Employee.Manager = E2.EID

Result EID 115 462 523

Name Sanchez Miller Hawk

Manager 765 115 115

Name Munoz Sanchez 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 200x) WITH RECURSIVE EmployeeList (EmployeeID, Title, Salary) AS ( SELECT EmployeeID, Title, 0.00 FROM Manages WHERE Title = “CEO” -- starting level UNION ALL SELECT Manages.EmployeeID, Manages.Title, Manages.Salary FROM EmployeeList INNER JOIN Manages ON EmployeeList.EmployeeID = Manages.ManagerID ) SELECT EmployeeID, Count(Title), Sum(Salary) FROM EmployeeList GROUP BY EmployeEID ;

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 AnimalID, CASE WHEN Date()-DateBorn < 90 Then “Baby” WHEN Date()-DateBorn >= 90 AND Date()-DateBorn < 270 Then “Young” WHEN Date()-DateBorn >= 270 AND Date()-DateBorn < 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  AccountsReceivable  Categorize by Days Late  30, 90, 120+

 Three queries?  New table for business rules AR(TransactionID, CustomerID, Amount, DateDue) LateCategory(Category, MinDays, MaxDays, Charge, …) Month Quarter Overdue

30 90 120

90 120 9999

3% 5% 10%

SELECT * FROM AR INNER JOIN LateCategory ON ((Date() - AR.DateDue) >= LateCategory.MinDays) AND ((Date() - AR.DateDue) < LateCategory.MaxDays) 26

D A T A B A S E

SQL SELECT SELECT DISTINCT Table.Column {AS alias} , . . . FROM Table/Query INNER JOIN Table/Query ON T1.ColA = T2.ColB 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 dbName password  Create Table TableName (Column Type, . . .)  Alter Table Table {Add, Column, Constraint, Drop}  Drop {Table Table | Index Index On table}  Create Index IndexName ON Table (Column {ASC| DESC})

29

D A T A B A S E

Syntax Examples CREATE TABLE Customer (CustomerID INTEGER NOT NULL, LastName CHAR (10), more columns ); ALTER TABLE Customer DROP COLUMN ZipCode; ALTER TABLE Customer ADD COLUMN CellPhone 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 SaleAnimal 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 SaleAnimal ON Sale.SaleID = SaleAnimal.SaleID) ON Animal.AnimalID = SaleAnimal.AnimalID WHERE Sale.EmployeeID = 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 EmployeeID, LastName FROM Employee WHERE NOT EXISTS (above query slightly modified.)

32

D A T A B A S E

Query for Every SELECT Employee.EmployeeID, Employee.LastName 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 SaleAnimal ON Sale.SaleID = SaleAnimal.SaleID) ON Animal.AnimalID = SaleAnimal.AnimalID WHERE Sale.EmployeeID = Employee.EmployeeID) ); 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 (OrderID INTEGER NOT NULL, OrderDate DATE, CustomerID INTEGER CONSTRAINT pkorder PRIMARY KEY (OrderID), CONSTRAINT fkorder FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID) ); Order OrderID OrderDate CustomerID

Customer *

CustomerID LastName FirstName Address … 35

D A T A B A S E

SQL Data Manipulation Commands Insert Into target (column1 . . .) VALUES (value1 . . .) Insert Into target (column1 . . .) 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 OldAnimals SELECT * FROM Animal WHERE AnimalID IN (SELECT AnimalOrderItem.AnimalID FROM AnimalOrder INNER JOIN AnimalOrderItem ON AnimalOrder.OrderID = AnimalOrderItem.OrderID WHERE (AnimalOrder.OrderDate<’01-Jan-2004’) );

37

D A T A B A S E

Delete Old Animal Data DELETE FROM Animal WHERE AnimalID IN (SELECT AnimalOrderItem.AnimalID FROM AnimalOrder INNER JOIN AnimalOrderItem ON AnimalOrder.OrderID = AnimalOrderItem.OrderID WHERE (AnimalOrder.OrderDate<’01-Jan-2004’) );

38

D A T A B A S E

Update Example UPDATE Animal SET ListPrice = ListPrice*1.10 WHERE Category = ‘Cat’ ; UPDATE Animal SET ListPrice = ListPrice*1.20 WHERE Category = ‘Dog’ ;

 Change the ListPrice of Animals at the PetStore.  For cats, increase the ListPrice by 10%.  For dogs, increase the ListPrice 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.  Check the SQL.  Look at the data.  Check computations

Which customers who bought Dogs also bought products for Cats (at any time)?

 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.

 Test SELECT queries before executing UPDATE queries.

Dogs and cat products on the same sale. Dogs and cat products at different times. Dogs and never any cat products. Cat products and never any Dogs. 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.CustomerID FROM Sale INNER JOIN (Animal INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID) ON Sale.SaleID = SaleAnimal.SaleID WHERE (((Animal.Category)=‘Dog’)) AND Sale.CustomerID IN ( SELECT DISTINCT Sale.CustomerID FROM Sale INNER JOIN (Merchandise INNER JOIN SaleItem ON Merchandise.ItemID = SaleItem.ItemID) ON Sale.SaleID = SaleItem.SaleID 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 Button1 Button2

Form--Module Code Sub Button1_Click() Dim i1 As Integer i1 = 3 End Sub Sub Button2_Click() Dim i1 As Integer i1 = 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 Button1 Button2

Form--Module Code

Dim i2 As Integer Sub Button1_Click() i2 = 20 End Sub

Variable is created when form is opened. Sub Button2_Click() Clicking Button1 sets the i2 = i2 + 7 initial value. End Sub Clicking Button2 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” → “FrankRose” Left(“Jackson”,5) → “Jacks” Trim(“

Maria “) → “Maria”

 String  & Concatenation  Left, Right, Mid  Trim, LTrim, RTrim  String  Chr, Asc  LCase, UCase  InStr  Len  StrComp  Format

Len(“Ramanujan”) → 9 String(5,”a”) → “aaaaa” InStr(“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

x = loge (e ) x

?

Trigonometric functions

φ =30 92

√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  DateAdd, DateDiff  “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 DateDue

DateDue = DateAdd(“d”, 30, Date())

47

D A T A B A S E

Programming: Standard Functions: Variant  Variant  IsDate  IsNumeric  VarType  IsEmpty  IsNull

48

D A T A B A S E

Programming: Debug      

Stop Ctrl-Break F5: Go F8: Step through S-F8: Step over Breakpoints

 Immediate Window  ? or Print  Any assignment  Any code

49

D A T A B A S E

 MsgBox

Programming: Output: Message Box

 Message  Type  Title

 Types: Use Constants  vbOKOnly vbOKCancel  vbAbortRetryIgnore  vbYesNoCancel  vbYesNo vbRetryCancel

 Defaults  vbDefaultButton1  vbDefaultButton2  vbDefaultButton3

 Icons

 vbCritical Stop sign  vbQuestion Question mark  vbExclamation Warning  vbInformation Circle i

 Responses  vbOK vbCancel  vbAbort vbRetry  vbIgnore  vbYes vbNo

MsgBox "This is a message box", vbYesNoCancel + vbInformation, "Sample Box" 50

D A T A B A S E

 InputBox

Programming: Input: InputBox

 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 = InputBox( "Enter your name:", "Sample Input", , 5000, 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 value2  Case Else  End Select

If (Condition1) Then statements for true Else statements for false If (Condition2) Then statements for true End If 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 = MsgBox(…) If (response == vbYes) Then ‘ statements for Yes Else If (response == vbNo) Then ‘ statements for No Else ‘statements for Cancel End If End If

response = MsgBox(…) Select Case response Case vbYes ‘ statements for Yes Case vbNo ‘ statements for No Case vbCancel ‘ 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

‘ Statements

x=x+1

x=x+1

Loop

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 (var1 As . . ., var2, . . .)  End Sub  Function fname (var1 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 ByVal  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 … StatusMessage “Trying to connect.” … StatusMessage “Verifying access.” … End main program Sub StatusMessage (Msg As String) ‘ Display Msg, location, color End Sub

57

D A T A B A S E

Programming: Parameter Types Main j=3 DoSum j … ‘ j is now equal to 8 Subroutine DoSum (j2 As Integer) j2 = 8 End Sub Main j=3 DoSum j … ‘ j is still equal to 3 Subroutine DoSum (ByVal j2 As Integer) j2 = 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 ◆

Arrays ●

●

◆

◆

Dim array(sub, . . .) As type Dim iSorts(10) As Integer

●

●

● ●

(lower To upper, . . .) ReDim [Preserve] array .. . Option Base 0 | 1 v 2.0 arrays less than 64KB

Type Tname ❖ ❖

Specifying bounds: ●

User defined types

●

◆ ◆ ◆

ename1 As type ename2 As type

End Type

Dim var1 As Tname var1.ename1 = . . . var1.ename2 = . . .

59

D A T A B A S E

Programming: Financial Functions ◆

Fixed payments ● ● ●

● ● ●

●

◆ ◆ ◆ ◆ ◆ ◆

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)

◆

Arrays ● ● ●

◆

NPV (rate, array) IRR (array, guess) MIRR (array, finrate, re_rate)

Depreciation ● ● ●

DDB (cost, salv, life, period) SLN (cost, salvage, life) SYD (cost, salv., life, period)

rate interest rate per 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

    

ChDir, ChDirve Dir Kill, (re)Name Lock, Unlock CurDir, MkDir, RmDir

61

D A T A B A S E

OLE: Object Linking & Embedding  CreateObject (class)  “appname . objecttype”

 GetObject (file, class)  Methods and syntax are defined by the software that exports the object.

 Example  Dim obj As Object  set obj = CreateObject(“Word.Basic”)  obj.Bold  obj.Insert “text”  obj.SaveAs “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