Database management systems
This presentation is the property of its rightful owner.
Sponsored Links
1 / 79

Database Management Systems PowerPoint PPT Presentation


  • 58 Views
  • Uploaded on
  • Presentation posted in: General

Database Management Systems. Chapter 5 Advanced Queries. Tables. 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

Download Presentation

Database Management Systems

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Database management systems

Database Management Systems

Chapter 5

Advanced Queries


Tables

Tables


Organization

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


Harder questions

How many cats are “in-stock” on 10/1/01?

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/01 and 12/31/01?

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

Harder Questions


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.

Sub-query for Calculation

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”)

)

) );


Query sets in

List all customers (Name) who purchased one of the following items: 1, 2, 30, 32, 33.

Could use Or, but harder to write.

In ( . . .) matches any in the list.

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;


Using in with a sub query

List all customers who bought items for cats.

Using IN with a Sub-query

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")

);


Subquery in look up a set

List all of the customers who bought something in March and who bought something in May. (Two tests on the same data!)

LastNameFirst

AdkinsInga

McCainSam

GrimesEarl

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) );


Subquery any all

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.

Query04_15

SubQuery (ANY, ALL)

SELECT DISTINCTROW 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"));


Subquery not in subtract

Which animals have not been sold?

Start with list of all animals.

Subtract out list of those who were sold.

AnimalIDNameCategory

12LeishaDog

19GeneDog

25VivianDog

34RhondaDog

88BrandyDog

181Fish

Query04_16

SubQuery: NOT IN (Subtract)

SELECT Animal.AnimalID, Animal.Name, Animal.Category

FROM Animal

WHERE (Animal.AnimalID Not In

(SELECT AnimalID From SaleAnimal));


Subquery not in data

SubQuery: NOT IN (Data)

Animal

SaleAnimal

IDNameCategoryBreed

2FishAngel

4GaryDogDalmation

5FishShark

6RosieCatOriental Shorthair

7EugeneCatBombay

8MirandaDogNorfolk Terrier

9FishGuppy

10SherriDogSiberian Huskie

11SusanDogDalmation

12LeishaDogRottweiler

IDSaleIDSalePrice

235$10.80

480$156.66

627$173.99

725$251.59

84$183.38

1018$150.11

1117$148.47

Which animals have not been sold?


Left outer join

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.

AnimalIDNameCategory

12LeishaDog

19GeneDog

25VivianDog

34RhondaDog

88BrandyDog

181Fish

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);


Left outer join example

Left Outer Join (Example)

IDSaleIDSalePrice

235$10.80

480$156.66

NullNullNull

627$173.99

725$251.59

84$183.38

NullNullNull

1018$150.11

1117$148.47

NullNullNull

IDNameCategoryBreed

2FishAngel

4GaryDogDalmation

5FishShark

6RosieCatOriental Shorthair

7EugeneCatBombay

8MirandaDogNorfolk Terrier

9FishGuppy

10SherriDogSiberian Huskie

11SusanDogDalmation

12LeishaDogRottweiler


Older syntax for left join

Which animals have not been sold?

Older Syntax for Left Join

SELECT ALL

FROM Animal, SaleAnimal

WHERE Animal.AnimalID *= SaleAnimal.AnimalID

And SaleAnimal.SaleID Is Null;

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;


Subquery for computation

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

Watch parentheses.

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”) ) ) );


Correlated subquery

List the Animals that have sold for a price higher than the average for animals in that Category.

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.

Correlated Subquery

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;


Correlated subquery avoid

List the Animals that have sold for a price higher than the average for animals in that Category.

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.

Query04_19

Correlated SubQuery (Avoid)

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;


Correlated subquery problem

Assume small query

100,000 rows

5 categories of 20,000 rows

100,000 * 20,000 = 1 billion rows to read!

Correlated Subquery Problem

Animal + SaleAnimal

CategorySalePrice

Compute Avg: $37.78

Fish$10.80

Dog$156.66

Fish$19.80

Cat$173.99

Cat$251.59

Dog$183.38

Fish$1.80

Dog$150.11

Dog$148.47

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!


More efficient solution 2 queries

Compute the averages once and save query

JOIN saved query to main query

Two passes through table: 1 billion / 200,000 => 10,000

More Efficient Solution: 2 queries

Animal + SaleAnimal

Saved Query

CategorySalePrice

CategoryAvgOfSalePrice

Fish$10.80

Dog$156.66

Fish$19.80

Cat$173.99

Cat$251.59

Dog$183.38

Fish$1.80

Dog$150.11

Dog$148.47

Bird$176.57

Cat$169.73

Dog$174.20

Fish$37.78

Mammal$80.72

Reptile$181.83

Spider$118.16

JOIN

Animal.Category = Query1.Category


Union operator

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.

UNION Operator

SELECT EID, Name, Phone, Salary, ‘East’ AS Office

FROM EmployeeEast

UNION

SELECT EID, Name, Phone, Salary, ‘West’ AS Office

FROM EmployeeWest

EIDNamePhoneSalaryOffice

352Jones335245,000East

876Inez873647,000East

372Stoiko763238,000East

890Smythe980362,000West

361Kim773673,000West


Union intersect except

UNION, INTERSECT, EXCEPT

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

A

B

C

T1

T2

SELECT EID, Name

FROM EmployeeEast

INTERSECT

SELECT EID, Name

FROM EmployeeWest


Multiple join columns

Sometimes need to JOIN tables on more than one column.

PetStore: Category and Breed.

Multiple JOIN Columns

Animal

AnimalID

Name

Category

Breed

DateBorn

Gender

. . .

Breed

Category

Breed

SELECT *

FROM Breed INNER JOIN Animal

ON Breed.Category = Animal.Category

AND Breed.Breed = Animal.Breed


Reflexive join

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.

Reflexive Join

Employee

EIDName. . .Manager

115Sanchez765

462Miller115

523Hawk115

765Munoz886

SQL

SELECT Employee.EID, Employee.Name, Employee.Manager, E2.Name

FROM Employee INNER JOIN Employee AS E2

ON Employee.Manager = E2.EID

Result

EIDNameManagerName

115Sanchez765Munoz

462Miller115Sanchez

523Hawk115Sanchez


Case function

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

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;


Inequality join

AccountsReceivable

Categorize by Days Late

30, 90, 120+

Three queries?

New table for business rules

Inequality Join

AR(TransactionID, CustomerID, Amount, DateDue)

LateCategory(Category, MinDays, MaxDays, Charge, …)

Month30903%

Quarter901205%

Overdue120999910%

SELECT *

FROM AR INNER JOIN LateCategory

ON ((Date() - AR.DateDue) >= LateCategory.MinDays)

AND ((Date() - AR.DateDue) < LateCategory.MaxDays)


Sql select

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 }

  • TRANSFORM aggfunction{Crosstab values}

    • SELECT . . . FROM . . . GROUP BY{Crosstab rows}

    • PIVOT pivotfield {Crosstab columns}


Sql mnemonic

SQL Mnemonic

Someone

From

Ireland

Will

Grow

Horseradish and

Onions

SELECT

FROM

INNER JOIN

WHERE

GROUP BY

HAVING

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.


Sql data definition

SQL Data Definition

  • Create Schema AuthorizationdbNamepassword

  • Create TableTableName (Column Type, . . .)

  • Alter TableTable {Add, Column, Constraint, Drop}

  • Drop {Table Table | Index Index On table}

  • Create IndexIndexName ON Table (Column {ASC|DESC})


Syntax examples

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);


Queries with every need exists

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.


Query with exists

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


Query for every

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


Simpler query for every

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.


Sql foreign key

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

Customer

OrderID

OrderDate

CustomerID

CustomerID

LastName

FirstName

Address

*


Sql data manipulation commands

SQL Data Manipulation Commands

Insert Intotarget (column1 . . .) VALUES (value1 . . .)

Insert Intotarget (column1 . . .) SELECT . . . FROM. . .

Delete Fromtable WHERE condition

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


Copy old animal data

Copy Old Animal Data

INSERT INTO OldAnimals

SELECT *

FROM Animals

WHERE AnimalID IN

(SELECT AnimalOrderItem.AnimalID

FROM AnimalOrder INNER JOIN AnimalOrderItem

ON AnimalOrder.OrderID = AnimalOrderItem.OrderId

WHERE (AnimalOrder.OrderDate<#1/1/01#) );


Delete old animal data

Delete Old Animal Data

DELETE

FROM Animals

WHERE AnimalID IN

(SELECT AnimalOrderItem.AnimalID

FROM AnimalOrder INNER JOIN AnimalOrderItem

ON AnimalOrder.OrderID = AnimalOrderItem.OrderId

WHERE (AnimalOrder.OrderDate<#1/1/01#) );


Update example

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.

Update Example

UPDATE Animal

SET ListPrice = ListPrice*1.10

WHERE Category = “Cat” ;

UPDATE Animal

SET ListPrice = ListPrice*1.20

WHERE Category = “Dog” ;


Quality building queries

Break questions into smaller pieces.

Test each query.

Check the SQL.

Look at the data.

Check computations

Combine into subqueries.

Use cut-and-paste to avoid errors.

Check for correlated subqueries.

Test sample data.

Identify different cases.

Check final query and subqueries.

Verify calculations.

Quality: Building Queries

Which customers who bought Dogs

also bought products for Cats

(at any time)?

Who bought dogs?

Who bought cat products?

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.

Test SELECT queries

before executing

UPDATE queries.


Quality queries example

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"))

);


Data warehouse

Data Warehouse

Predefined

reports

Interactive

data analysis

Operations

data

Daily data

transfer

OLTP Database

3NF tables

Data warehouse

Star configuration

Flat files


Data warehouse goals

Existing databases optimized for Online Transaction Processing (OLTP)

Online Analytical Processing (OLAP) requires fast retrievals, and only bulk writes.

Different goals require different storage, so build separate dta warehouse to use for queries.

Extraction, Transformation, Transportation (ETT)

Data analysis

Ad hoc queries

Statistical analysis

Data mining (specialized automated tools)

Data Warehouse Goals


Oltp v olap

OLTP v. OLAP


Multidimensional cube

Multidimensional Cube

Pet Store

Item Sales

Amount = Quantity*Sale Price

Category

Customer

Location

Time

Sale Date


Sales date time hierarchy

Sales Date: Time Hierarchy

Year

Roll-up

To get higher-level totals

Levels

Quarter

Month

Drill-down

To get lower-level details

Week

Day


Star design

Star Design

Dimension Tables

Products

Sales Date

Fact Table

Sales

Quantity

Amount=SalePrice*Quantity

Customer

Location


Snowflake design sql server analysis

Snowflake Design: SQL Server Analysis


Olap computation issues

OLAP Computation Issues

Compute Quantity*Price in base query, then add to get $23.00

If you use Calculated Measure in the Cube, it will add first and multiply second to get $45.00, which is wrong.


Sql server analysis cube design

SQL Server Analysis Cube Design

  • Define the Data Source (choose the database)

  • Create a new cube—avoid the wizard

    • Create the OLAPItems query, Amount=Quantity*SalePrice

    • Pick the fact table: OLAPItems query

    • Create a new dimension: PetItemSaleDate

      • Star design

      • Sale table, SaleDate column, Time dimension

    • Create a second dimension: PetItemCategory

      • Star

      • Merchandise table, Category column

    • Create a third dimension: PetCustomerLocation

      • Snowflake

      • Sale, Customer, City tables

      • Country, State, City columns

    • Select the data to measure in the fact table

      • Measures, New: Quantity, Amount

    • Save the cube as: PetItemSales

    • Process the cube (icon)

      • MOLAP (stores data in a cube layout)

      • Select the defaults on the other options.


Oracle olap design

Oracle OLAP Design

Create snapshots (Materialized Views) to preprocess queries.

Mostly an issue of performance—use Star and bitmap indexes.

Need Oracle Express ($$$$$) or have to build tables yourself.


Sql server olap data browsing

SQL Server OLAP Data Browsing


Microsoft pivot table

Microsoft Pivot Table


Excel pivot table reports

Excel Pivot Table Reports

Can place data in rows or columns.

By grouping months, can instantly get quarterly or monthly totals.

Will have to reconnect to the database: PetStore2000.mdb

And the query: qryPivotExample


Oracle olap browser

Oracle OLAP Browser

DECODE … GROUPING assigns titles to the totals


Oracle olap query

Oracle OLAP Query

ROLLUP (left) computes totals only as specified.

CUBE (right) computes all totals.


Microsoft access crosstab result

Microsoft Access Crosstab Result

MonthBirdCatDogFishMammalReptileSpider

1$217.51$1,655.01

2$324.87$597.74$1,281.81$39.60$127.78

3$364.18$198.85$650.17$378.25$40.50

4$334.50$1,221.10$172.88

5$396.84$335.48$1,192.56$126.20

6$119.71$459.91$1,607.46$5.40$19.80

7$573.60$1,644.73$319.07$19.80$182.31

8$578.35$1,444.75$1,859.71$27.90$320.52

9$538.07$792.46$1,219.03

10$173.50$942.89$1,429.27$10.80$11.70$229.73$313.97

11$153.07$1,308.97$1,784.06$112.50

12$770.87$1,178.47


Crosstab

Total animal sales by Category for each month.

Query04_20

Crosstab

TRANSFORM Sum(SaleAnimal.SalePrice) AS SumOfSalePrice

SELECT Month([Sale].[SaleDate]) AS SaleMonth

FROM Sale INNER JOIN (Animal INNER JOIN SaleAnimal

ON Animal.AnimalID = SaleAnimal.AnimalID) ON Sale.SaleID = SaleAnimal.SaleID

GROUP BY Month([Sale].[SaleDate])

PIVOT Animal.Category;


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

Programming Review: Variables


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?

Programming: Scope and Lifetime

Form

Button1

Button2

Form--Module Code

Sub Button1_Click()

Dim i1 As Integer

i1 = 3

End Sub

Different procedures,

different variables.

Created and destroyed

each time the button

is clicked.

Sub Button2_Click()

Dim i1 As Integer

i1 = 7

End Sub


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.

Programming: Global Variables

Form

Button1

Button2

Form--Module Code

Dim i2 As Integer

Sub Button1_Click()

i2 = 20

End Sub

Variable is created when

form is opened.

Clicking Button1 sets the

initial value.

Clicking Button2 modifies

the value.

What if user clicks buttons in a different order?

Sub Button2_Click()

i2 = i2 + 7

End Sub


Programming computations

Standard Math

+ - * /

\ Integer divide

^ Exponentiation

(2^3 = 2*2*2 = 8)

Mod

(15 Mod 4 = 3) (12 + 3 = 15)

String

& Concatenation

Left, Right, Mid

Trim, LTrim, RTrim

String

Chr, Asc

LCase, UCase

InStr

Len

StrComp

Format

Programming: Computations

“Frank” & “Rose”  “FrankRose”

Left(“Jackson”,5)  “Jacks”

Trim(“ Maria “)  “Maria”

Len(“Ramanujan”)  9

String(5,”a”)  “aaaaa”

InStr(“8764 Main”,” “)  5


Programming standard functions

Numeric

Exp, Log

Atn, Cos, Sin, Tan

Sqr

Abs

Sgn

Int, Fix

Rnd, Randomize

?

=30

92

Programming: Standard Functions

x = loge (ex)

Trigonometric functions

2 = 1.414

Abs(-35)  35

Sgn(-35)  -1

Int(17.893)  17

Rnd()  0.198474


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.

Programming:Standard Functions: Date/Time

02/19/01

03/21/01

today

DateDue

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


Programming standard functions variant

Variant

IsDate

IsNumeric

VarType

IsEmpty

IsNull

Programming:Standard Functions: Variant


Programming debug

Stop

Ctrl-Break

F5: Go

F8: Step through

S-F8: Step over

Breakpoints

Immediate Window

? or Print

Any assignment

Any code

Programming: Debug


Programming output message box

MsgBox

Message

Type

Title

Types: Use Constants

vbOKOnlyvbOKCancel

vbAbortRetryIgnore

vbYesNoCancel

vbYesNovbRetryCancel

Defaults

vbDefaultButton1

vbDefaultButton2

vbDefaultButton3

Icons

vbCritical Stop sign

vbQuestion Question mark

vbExclamation Warning

vbInformation Circle i

Responses

vbOKvbCancel

vbAbortvbRetry

vbIgnore

vbYesvbNo

Programming:Output: Message Box

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


Programming input inputbox

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

Programming:Input: InputBox

Dim str As String

str = InputBox(

"Enter your name:",

"Sample Input", ,

5000, 5000)


Programming conditions

If

If (Condition) Then

statements for true

Else

statements for false

End If

IIF (Cond., True, False)

Select Case (expr)

Case value

statements

Case value2

Case Else

End Select

Conditions

<, <=, >, >=, =, <>

And, Or, Not, Xor

Eqv, Imp (logic)

Programming: Conditions

  • If (Condition1) Then

    • statements for true

  • Else

    • statements for false

    • If (Condition2) Then

    • statements for true

    • End If

  • End If


Programming select example

Message Box

Could use repeated If statements

Better to use Select Case

ProgrammingSelect Example

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


Programming loops

Do

For … Next

For Each

Programming: Loops

Initialize value

Statements

Change value

Test condition

Do Until (x > 10)

‘ Statements

x = x + 1

Loop

Do While (x <= 10)

‘ Statements

x = x + 1

Loop

Do

‘ Statements

x = x + 1

Loop Until (x > 10)

For x = 1 to 10

‘ Statements

Next x


Programming loops again

Do

Do {While | Until}

Exit Do (optional)

Loop

Do

Loop {While | Until}

For/Next

For counter = start To end Step increment

Exit For (optional)

Next counter

For/Each (objects)

For Each element In group

[Exit For] (optional)

Next element

With (objects)

With object

End With

Programming: Loops Again


Programming subroutines and functions

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


Programming example subroutine

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


Programming parameter types

Programming: Parameter Types

Main

j = 3

DoSum j

…‘ j is now equal to 8

Subroutine DoSum (j2 As Integer)

j2 = 8

End Sub

By Reference

Changes to data in the

subroutine are passed back.

Main

j = 3

DoSum j

…‘ j is still equal to 3

Subroutine DoSum (ByVal j2 As Integer)

j2 = 8

End Sub

By Value

Creates a copy of the

variable, so changes are

not returned.


Programming arrays and user types

Arrays

Dim array(sub, . . .) As type

Dim iSorts(10) As Integer

Specifying bounds:

(lower To upper, . . .)

ReDim [Preserve] array .. .

Option Base 0 | 1

v 2.0 arrays less than 64KB

User defined types

Type Tname

ename1 As type

ename2 As type

End Type

Dim var1 As Tname

var1.ename1 = . . .

var1.ename2 = . . .

ProgrammingArrays and User Types


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)

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

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)

Programming: Financial Functions


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

Programming: Text File Input/Output


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”

OLE: Object Linking & Embedding


Dde dynamic data exchange

Shell

DDEInitiate

DDEExecute

DDEPoke, DDE Send

Send data

DDE, DDERequest

Request data

DDETerminate

Application must be running

Start a conversation/topic

Issue a command

Place data

Get data

Close the session

DDE: Dynamic Data Exchange


  • Login