Financial Data Access with SQL, Excel & VBA

Computational Finance and Risk Management

Financial Data Access

with SQL, Excel & VBA

Guy Yollin

Instructor, Applied Mathematics

University of Washington

Outline

1

Introduction to SQL

2

SQLite and sample databases

3

Simple queries

4

Queries with additional clauses

5

Querying multiple tables with subqueries

6

Querying multiple tables with join

Lecture references

Ben Forta

Sams Teach Yourself SQL in 10 Minutes

Sams, 1999

Chapter 1-12

sqlzoo.net

SQL ZOO: Interactive SQL Tutorial

http://sqlzoo.net/

sqlite.org

SQL As Understood By SQLite

http://www.sqlite.org/lang.html

Outline

1

Introduction to SQL

2

SQLite and sample databases

3

Simple queries

4

Queries with additional clauses

5

Querying multiple tables with subqueries

6

Querying multiple tables with join

SQL

SQL

SQL

(pronounced squeal) stands for Structured Query Language, a

special-purpose programming language designed for managing data in

relational database management systems (RDBMS)

†

SQL has both an ANSI and ISO standard but minor compatibility

issues are commonn

frequent updates to the standards

vendor-specific procedural extensions

vendor-specific deviations

∗

†

http://en.wikipedia.org/wiki/SQL

∗

MS Access SQL has many proprietary incompatibilities

Importance of SQL

Why SQL?

Knowledge of SQL is critical because the vast majority of real data owned

by the mast majority of real companies is maintained in an SQL

compatible database

†

Common databases that support SQL

Microsoft SQL Server

Oracle Database

IBM DB2

Sybase

Microsoft Access

MySQL

PostgreSQL

SQLite

A stylized fact

Relational Database

Relational Database

A relational database is a collection of data items organized as a set of

formally described tables from which data can be accessed easily

†

Relational database theory uses a set of mathematical terms, which are

roughly equivalent to SQL database terminology:

Relational Term

SQL equivalent

relation, base relvar

table

derived relvar

view, query result, result set

tuple

row

attribute

column

†

http://en.wikipedia.org/wiki/Relational_database

Outline

1

Introduction to SQL

2

SQLite and sample databases

3

Simple queries

4

Queries with additional clauses

5

Querying multiple tables with subqueries

6

Querying multiple tables with join

SQLite

SQLite is a self-contained,

serverless, zero-configuration

SQL database engine

SQLite is the most widely

deployed SQL database engine

in the world

SQLite is open-source

Chinook sample database

The Chinook data model represents a digital media store, including tables

for artists, albums, media tracks, invoices and customers.

Chinook sample database

SQLite Manager for Firefox

SQLite Manager for Firefox

SQL and SQLite data types

SQLite storage mode

SQL datatype

Description

TEXT

TEXT

CHAR

variable length text

fixed length string (size specified at create time)

NCHAR

like CHAR but support Unicode characters

NVARCHAR

like text but with Unicode support

INTEGER

INTEGER

SMALLINT

4-byte signed integer

2-byte signed integer

TINYINT

1-byte unsigned integer

REAL

REAL

_FLOAT

4-byte floating point

_{floating point}

NUMERIC

NUMERIC

fixed or floating point with specified precision

DECIMAL

fixed or floating point with specified precision

BOOLEAN

true or false

DATE

date value

DATETIME

date time value

NONE

BLOB

binary data

http://www.sqlite.org/datatype3.html

Database keys

The relationships between columns located in different tables are usually

described through the use of keys

†

Primary Key

A column (or set of columns) whose values uniquely

identify every row in a table

Foreign Key

A column in a table which is also the Primary Key in

another table

†

http://www.atlasindia.com/sql.htm

Outline

1

Introduction to SQL

2

SQLite and sample databases

3

Simple queries

4

Queries with additional clauses

5

Querying multiple tables with subqueries

6

Querying multiple tables with join

SELECT statement

The most common operation in SQL is the query which is performed with

the SELECT statement

SELECT retrieves data from one or more tables

returned data is called a resultset or recordset

Standard SELECT queries just read from the database and do not

change any underlying data

Notes about the SQL language:

SQL is not case-sensitive

SQL is ignores whitespace

strings must use single quotes

SELECT/FROM wildcard

SQL: SELECT/FROM wildcard syntax

SELECT

∗

FROM

tableName

The * character is a wildcard meaning all columns

SELECT/FROM wildcard

SQL: SELECT/FROM wildcard syntax

SELECT

∗

FROM

tableName

SELECT/FROM

SQL: SELECT/FROM syntax

SELECT

columnName

(

s

)

FROM

tableName

multiple columns are separated with commas

SELECT/FROM

SQL: SELECT/FROM syntax

SELECT

columnName

(

s

)

FROM

tableName

SELECT/FROM/WHERE

SQL: SELECT/FROM/WHERE syntax

SELECT

columnName

(

s

)

FROM

tableName

WHERE

someCondition

SELECT/FROM/WHERE

SQL: SELECT/FROM/WHERE syntax

SELECT

columnName

(

s

)

FROM

tableName

WHERE

someCondition

WHERE clause operators

Operator

Description

=

equality

<>

non-equality

! =

non-equality

<

less than

<=

less than or equal to

! <

not less than

>

greater than

>=

greater than or equal to

! >

not greater than

BETWEEN

non-equality

IS NULL

is a NULL value

WHERE clause can also include AND, OR, and NOT

parenthesis are used for complex logic

SELECT/FROM/WHERE

SQL: SELECT/FROM/WHERE syntax

SELECT

columnName

(

s

)

FROM

tableName

WHERE

someCondition

WHERE clause with arithmetic and logical operators

WHERE clause with IN

SQL: WHERE clause with IN

SELECT

columnName

(

s

)

FROM

tableName

WHERE

someColumn

IN

listOfValues

list for WHERE IN is in parenthesis with items separated with commas

WHERE clause with NOT IN

SQL: WHERE clause with NOT IN

SELECT

columnName

(

s

)

FROM

tableName

WHERE

someColumn

NOT IN

listOfValues

Partial matching with WHERE LIKE

The LIKE keyword is used in SQL expression to perform partial matching

by including wildcard characters:

_

represents a single unspecified character

%

represents a series of one or more unspecified character

SQL: WHERE clause with LIKE

SELECT

columnName

(

s

)

FROM

tableName

WHERE

someColumn

LIKE

wildcardSting

Partial matching with WHERE LIKE

SQL: WHERE clause with LIKE

SELECT

columnName

(

s

)

FROM

tableName

WHERE

someColumn

LIKE

wildcardSting

Partial matching with WHERE LIKE

SQL: WHERE clause with LIKE

SELECT

columnName

(

s

)

FROM

tableName

WHERE

someColumn

LIKE

wildcardSting

Outline

1

Introduction to SQL

2

SQLite and sample databases

3

Simple queries

4

Queries with additional clauses

5

Querying multiple tables with subqueries

6

Querying multiple tables with join

ORDER BY clause

SQL: ORDER BY clause

SELECT

columnName

(

s

)

FROM

tableName

WHERE

someCondition

ORDER BY

columnName

ORDER BY clause

SQL: ORDER BY clause

SELECT

columnName

(

s

)

FROM

tableName

WHERE

someCondition

ORDER BY

columnName

use DESC with ORDER BY to sort in descending order

SQL aggregate functions

SQL supports the use of arithmetic formulas and it also provides a number

of aggregate functions

Function

Description

COUNT

counts the number of rows in the resultset

SUM

sums a column of the resultset

AVG

take the average of a column of the resultset

MAX

finds the maximum value in a column of the resultset

MIN

finds the maximum value in a column of the resultset

The results of an arithmetic operation are usually assigned an alias

with the AS keyword

Aggregate functions are frequently used with the GROUP BY clause

of the SELECT statement

COUNT function

SQL: COUNT function

SELECT COUNT

(

columnName

)

FROM

tableName

total count of the number of rows in the Track table

COUNT function

SQL: COUNT function

SELECT COUNT

(

columnName

)

FROM

tableName

count of the number of non-NULL Composers

COUNT function with DISTINCT clause

SQL: COUNT function

SELECT COUNT

(

DISTINCT

columnName

)

FROM

tableName

number of unique composers

AVG function

SQL: COUNT function

SELECT AVG

(

columnName

)

FROM

tableName

average invoice amount

MAX function

SQL: COUNT function

SELECT MAX

(

columnName

)

FROM

tableName

maximum invoice amount

GROUP BY clause

SQL: GROUP BY clause

SELECT

AggFunc

(

columnName

) . . .

GROUP BY

columnName

GROUP BY and HAVING clause

SQL: GROUP BY and HAVING clause

SELECT

AggFunc

(

columnName

) . . .

GROUP BY

columnName

HAVING

someCondition

Outline

1

Introduction to SQL

2

SQLite and sample databases

3

Simple queries

4

Queries with additional clauses

5

Querying multiple tables with subqueries

6

Querying multiple tables with join

Input data from 2 tables, output from 1 table

Problem:

Find all songs belonging to a particular genre of music

Solution:

Find the GenreId of the desired style (from the Genre table)

then select all of the tracks that match the GenreID (from the

Track table)

Manually running 2 queries

Subquery

SQL: Subquery syntax

SELECT

columnNames

FROM

tableName

WHERE

someColumn

IN

(

SELECT

/

FROM

/

WHERE

statement

)

Subquery provides the list used in the top-level WHERE IN clause

Input data from 3 tables, output from 1 table

Problem:

Find all the albums containing songs belonging to a particular

genre of music

Solution:

Find the GenreId of the desired style (from the Genre table)

then select all of the Tracks that match the GenreID (from the

Track table) then select all of the Titles that match the

AlbumId (from the Album table)

Nested subquery

Lowest-level query provides a list of GenreIds

Mid-level query provides a list of AlbumIds

Top-level query returns the album names

Outline

1

Introduction to SQL

2

SQLite and sample databases

3

Simple queries

4

Queries with additional clauses

5

Querying multiple tables with subqueries

6

Querying multiple tables with join

Input data from 2 tables, output from 2 table

Problem:

Display a list of all songs and their genre

Solution:

Create a new table with the name of the song (from the Track

table) and the name of the song’s genre (from the Genre table)

Join 2 tables with FROM/WHERE syntax

SQL: JOIN WHERE syntax

SELECT

tableName1

.

columnName

,

tableName2

.

columnName

FROM

tableName1

,

tableName2

WHERE

tableName1

.

keyColumn

=

tableName2

.

keyColumn

WHERE clause connects the keys from the two different tables

Join 2 tables with JOIN/ON syntax

SQL: JOIN ON syntax

SELECT

tableName1

.

columnName

,

tableName2

.

columnName

FROM

tableName1

JOIN

tableName2

ON

tableName1

.

keyColumn

=

tableName2

.

keyColumn

ON clause connects the keys from the two different tables

Join 3 tables with FROM/WHERE syntax

SQL: JOIN WHERE syntax

SELECT

tableName1

.

columnName

,

tableName2

.

columnName

,

tableName3

.

columnName

FROM

tableName1

,

tableName2

,

tableName3

WHERE

tableName1

.

keyColumn

=

tableName2

.

keyColumn

AND

tableName1

.

keyColumn

=

tableName3

.

keyColumn

Join 3 tables with JOIN/ON syntax

SQL: JOIN ON syntax

SELECT

tabName1

.

columnName

,

tabName2

.

columnName

,

tabName3

.

columnName

FROM

tabName1

JOIN

tabName2

ON

tabName1

.

keyCol

=

tabName2

.

keyCol

JOIN

tabName3

ON

tabName1

.

keyCol

=

tabName3

.

keyCol

Join 4 tables with FROM/WHERE syntax

WHERE clause can contain additional constraints as well as

specifying table linkages

Join 4 tables with JOIN/ON syntax

Can add WHERE clause to specify additional constraints

Join operation with aggregates by group

Who are the largest customers?

Join operation with aggregates by group

What countries produce the most sales other than the US?

Computational Finance and Risk Management

http://depts.washington.edu/compfin