Database management systems
Download
1 / 79

Database Management Systems - PowerPoint PPT Presentation


  • 84 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Database Management Systems' - sukey


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



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. items: 1, 2, 30, 32, 33.

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

LastName First

Adkins Inga

McCain Sam

Grimes Earl

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 True for any of the items, the statement is evaluated to True.not been sold?

Start with list of all animals.

Subtract out list of those who were sold.

AnimalID Name Category

12 Leisha Dog

19 Gene Dog

25 Vivian Dog

34 Rhonda Dog

88 Brandy Dog

181 Fish

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) True for any of the items, the statement is evaluated to True.

Animal

SaleAnimal

ID Name Category Breed

2 Fish Angel

4 Gary Dog Dalmation

5 Fish Shark

6 Rosie Cat Oriental Shorthair

7 Eugene Cat Bombay

8 Miranda Dog Norfolk Terrier

9 Fish Guppy

10 Sherri Dog Siberian Huskie

11 Susan Dog Dalmation

12 Leisha Dog Rottweiler

ID SaleID SalePrice

2 35 $10.80

4 80 $156.66

6 27 $173.99

7 25 $251.59

8 4 $183.38

10 18 $150.11

11 17 $148.47

Which animals have not been sold?


Left outer join

Which animals have True for any of the items, the statement is evaluated to True.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.

AnimalID Name Category

12 Leisha Dog

19 Gene Dog

25 Vivian Dog

34 Rhonda Dog

88 Brandy Dog

181 Fish

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) True for any of the items, the statement is evaluated to True.

ID SaleID SalePrice

2 35 $10.80

4 80 $156.66

Null Null Null

6 27 $173.99

7 25 $251.59

8 4 $183.38

Null Null Null

10 18 $150.11

11 17 $148.47

Null Null Null

ID Name Category Breed

2 Fish Angel

4 Gary Dog Dalmation

5 Fish Shark

6 Rosie Cat Oriental Shorthair

7 Eugene Cat Bombay

8 Miranda Dog Norfolk Terrier

9 Fish Guppy

10 Sherri Dog Siberian Huskie

11 Susan Dog Dalmation

12 Leisha Dog Rottweiler


Older syntax for left join

Which animals have not been sold? True for any of the items, the statement is evaluated to True.

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. True for any of the items, the statement is evaluated to True.

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 average for animals in that Category.

100,000 rows

5 categories of 20,000 rows

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

Correlated Subquery Problem

Animal + SaleAnimal

Category SalePrice

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 average for animals in that Category.

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

Category SalePrice

Category AvgOfSalePrice

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. average for animals in that Category.

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

EID Name Phone Salary Office

352 Jones 3352 45,000 East

876 Inez 8736 47,000 East

372 Stoiko 7632 38,000 East

890 Smythe 9803 62,000 West

361 Kim 7736 73,000 West


Union intersect except
UNION, INTERSECT, EXCEPT average for animals in that Category.

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. average for animals in that Category.

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. average for animals in that Category.

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

EID Name . . . Manager

115 Sanchez 765

462 Miller 115

523 Hawk 115

765 Munoz 886

SQL

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

FROM Employee INNER JOIN Employee AS E2

ON Employee.Manager = E2.EID

Result

EID Name Manager Name

115 Sanchez 765 Munoz

462 Miller 115 Sanchez

523 Hawk 115 Sanchez


Case function

Used to change data to a different context. average for animals in that Category.

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 average for animals in that Category.

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, …)

Month 30 90 3%

Quarter 90 120 5%

Overdue 120 9999 10%

SELECT *

FROM AR INNER JOIN LateCategory

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

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


Sql select
SQL SELECT average for animals in that Category.

  • 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 average for animals in that Category.

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 average for animals in that Category.

  • 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 average for animals in that Category.

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 average for animals in that Category.

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 average for animals in that Category.

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 average for animals in that Category.

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 average for animals in that Category.

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 average for animals in that Category.

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 average for animals in that Category.

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 average for animals in that Category.

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 average for animals in that Category.

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. average for animals in that Category.

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. average for animals in that Category.

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 average for animals in that Category.

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 average for animals in that Category.

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 Processing (OLTP)


Multidimensional cube
Multidimensional Cube Processing (OLTP)

Pet Store

Item Sales

Amount = Quantity*Sale Price

Category

Customer

Location

Time

Sale Date


Sales date time hierarchy
Sales Date: Time Hierarchy Processing (OLTP)

Year

Roll-up

To get higher-level totals

Levels

Quarter

Month

Drill-down

To get lower-level details

Week

Day


Star design
Star Design Processing (OLTP)

Dimension Tables

Products

Sales Date

Fact Table

Sales

Quantity

Amount=SalePrice*Quantity

Customer

Location



Olap computation issues
OLAP Computation Issues Processing (OLTP)

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 Processing (OLTP)

  • 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 Processing (OLTP)

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.



Microsoft pivot table
Microsoft Pivot Table Processing (OLTP)


Excel pivot table reports
Excel Pivot Table Reports Processing (OLTP)

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 Processing (OLTP)

DECODE … GROUPING assigns titles to the totals


Oracle olap query
Oracle OLAP Query Processing (OLTP)

ROLLUP (left) computes totals only as specified.

CUBE (right) computes all totals.


Microsoft access crosstab result
Microsoft Access Crosstab Result Processing (OLTP)

Month Bird Cat Dog Fish Mammal Reptile Spider

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. Processing (OLTP)

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 Processing (OLTP)

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 Processing (OLTP)

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 Processing (OLTP)

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 Processing (OLTP)

+ - * /

\ 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 Processing (OLTP)

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 Processing (OLTP)

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 Processing (OLTP)

IsDate

IsNumeric

VarType

IsEmpty

IsNull

Programming:Standard Functions: Variant


Programming debug

Stop Processing (OLTP)

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 Processing (OLTP)

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

Programming:Output: Message Box

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


Programming input inputbox

InputBox Processing (OLTP)

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 Processing (OLTP)

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 Processing (OLTP)

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 Processing (OLTP)

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 Processing (OLTP)

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
Programming Processing (OLTP)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.


Programming example subroutine
Programming: Example Subroutine Processing (OLTP)

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 Processing (OLTP)

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 Processing (OLTP)

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 Processing (OLTP)

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# Processing (OLTP)

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) Processing (OLTP)

“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 Processing (OLTP)

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