Financial Data Structures

Essential Types of Financial Data

• Fundamental Data
• Market Data
• Analytics
• Alternative Data

Bars

Time bars

• Timestamp
• Volume-weighted average price (VWAP)
• Open (i.e., first) price
• Close (i.e., last) price
• High price
• Low price
• Volume traded, etc.

Tick bars

brew install rlwrap

set QLIC and QHOME

Intro to python for data science

Boolean

True and False, First letter should be uppercase.

Delete list elements

Finally, you can also remove elements from your list. You can do this with the del statement:

1
2

x = ["a", "b", "c", "d"]
del(x[1])

More explicit copy

Do list copy with list() or by using [:].

Functions

1
2
3
4
5
6
7
8
9
10
11
12
13

# Create variables var1 and var2
var1 = [1, 2, 3, 4]
var2 = True
# Print out type of var1
print(type(var1))
# Print out length of var1
print(len(var1))
# Convert var2 to an integer: out2
out2 = int(var2)

Ironically, you have to ask for information about a function with another function: help()

List methods

• upper()

str.upper(): Notice from the printouts that the upper() method does not change the object it is called on.

• index(), to get the index of the first element of a list that matches its input and

• count(), to get the number of times an element appears in a list.

• append(), that adds an element to the list it is called on,

• remove(), that removes the first element of a list that matches the input, and
• reverse(), that reverses the order of the elements in the list it is called on.

Numpy

1

import numpy as np

Use np.array() to create a numpy array

1
2
3
4

In [1]: np.array([True, 1, 2]) + np.array([3, 4, False]) 
## True is converted to 1, False is converted to 0
... 
Out[1]: array([4, 5, 2])

numpy arrays slicing including the start element, excluding the end element.

1
2

# Print out sub-array of np_height: index 100 up to and including index 110
print(np_height[100:111])

Intermediate Python for data science

Matplotlib.pyplot

Build a histogram (1)

1
2
3
4
5

# Create histogram of life_exp data
plt.hist(life_exp)
# Display histogram
plt.show()

Build a histogram (2): bins

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# Build histogram with 5 bins
plt.hist(life_exp, bins = 5)
# Show and clean up plot
plt.show()
plt.clf()
# Build histogram with 20 bins
plt.hist(life_exp, bins = 20)
# Show and clean up again
plt.show()
plt.clf()

Labels

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# Basic scatter plot, log scale
plt.scatter(gdp_cap, life_exp)
plt.xscale('log') 
# Strings
xlab = 'GDP per Capita [in USD]'
ylab = 'Life Expectancy [in years]'
title = 'World Development in 2007'
# Add axis labels
plt.xlabel(xlab)
plt.ylabel(ylab)
# Add title
plt.title(title)
# After customizing, display the plot
plt.show()

Additional Customizations

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# Scatter plot
plt.scatter(x = gdp_cap, y = life_exp, s = np.array(pop) * 2, c = col, alpha = 0.8)
# Previous customizations
plt.xscale('log') 
plt.xlabel('GDP per Capita [in USD]')
plt.ylabel('Life Expectancy [in years]')
plt.title('World Development in 2007')
plt.xticks([1000,10000,100000], ['1k','10k','100k'])
# Additional customizations
plt.text(1550, 71, 'India')
plt.text(5700, 80, 'China')
# Add grid() call
plt.grid(True)
# Show the plot
plt.show()

Dictionary manipulation

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

# Definition of dictionary
europe = {'spain':'madrid', 'france':'paris', 'germany':'berlin', 'norway':'oslo' }
# Add italy to europe
europe['italy'] = 'rome'
# Print out italy in europe
print('italy' in europe)
# Add poland to europe
europe['poland'] = 'warsaw'
# Print europe
print(europe)
# Definition of dictionary
europe = {'spain':'madrid', 'france':'paris', 'germany':'bonn',
          'norway':'oslo', 'italy':'rome', 'poland':'warsaw',
          'australia':'vienna' }
# Update capital of germany
europe['germany'] = 'berlin'
# Remove australia
del(europe['australia'])
# Print europe
print(europe)
# Dictionary of dictionaries
europe = { 'spain': { 'capital':'madrid', 'population':46.77 },
           'france': { 'capital':'paris', 'population':66.03 },
           'germany': { 'capital':'berlin', 'population':80.62 },
           'norway': { 'capital':'oslo', 'population':5.084 } }
# Print out the capital of France
print(europe['france']['capital'])
# Create sub-dictionary data
data = {}
data['capital'] = 'rome'
data['population'] = 59.83
# Add data to europe under key 'italy'
europe['italy'] = data
# Print europe
print(europe)

Pandas

Dictionary to DataFrame

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

# Pre-defined lists
names = ['United States', 'Australia', 'Japan', 'India', 'Russia', 'Morocco', 'Egypt']
dr =  [True, False, False, False, True, True, True]
cpc = [809, 731, 588, 18, 200, 70, 45]
# Import pandas as pd
import pandas as pd
# Create dictionary my_dict with three key:value pairs: my_dict
my_dict = {'country': names,'drives_right':dr,'cars_per_cap':cpc}
# Build a DataFrame cars from my_dict: cars
cars = pd.DataFrame(my_dict)
# Print cars
print(cars)
import pandas as pd
# Build cars DataFrame
names = ['United States', 'Australia', 'Japan', 'India', 'Russia', 'Morocco', 'Egypt']
dr =  [True, False, False, False, True, True, True]
cpc = [809, 731, 588, 18, 200, 70, 45]
dict = { 'country':names, 'drives_right':dr, 'cars_per_cap':cpc }
cars = pd.DataFrame(dict)
print(cars)
# Definition of row_labels
row_labels = ['US', 'AUS', 'JAP', 'IN', 'RU', 'MOR', 'EG']
# Specify row labels of cars
cars.index = row_labels
# Print cars again
print(cars)

CSV to DataFrame

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

# Import pandas as pd
import pandas as pd
# Import the cars.csv data: cars
cars = pd.read_csv('cars.csv')
# Print out cars
print(cars)
# Import pandas as pd
import pandas as pd
# Fix import by including index_col
cars = pd.read_csv('cars.csv', index_col = 0)
# Print out cars
print(cars)

Square brackets

• The single bracket version gives a Pandas Series, the double bracket version gives a Pandas DataFrame.

1
2
3
4
5
6
7
8
9
10
11
12
13

# Import cars data
import pandas as pd
cars = pd.read_csv('cars.csv', index_col = 0)
# Print out country column as Pandas Series
print(cars['country'])
# Print out country column as Pandas DataFrame
print(cars[['country']])
# Print out DataFrame with country and drives_right columns
print(cars[['country','drives_right']])

• You can only select rows using square brackets if you specify a slice, like 0:4. Also, you’re using the integer indexes of the rows here, not the row labels!

1
2
3
4
5
6
7
8
9

# Import cars data
import pandas as pd
cars = pd.read_csv('cars.csv', index_col = 0)
# Print out first 3 observations
print(cars[0:3])
# Print out fourth, fifth and sixth observation
print(cars[3:6])

loc and iloc

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

# Import cars data
import pandas as pd
cars = pd.read_csv('cars.csv', index_col = 0)
# Print out observation for Japan
print(cars.loc['JAP'])
print(cars.iloc[2])
# Print out observations for Australia and Egypt
print(cars.loc[['AUS','EG']])
print(cars.iloc[[1,6]])
# Import cars data
import pandas as pd
cars = pd.read_csv('cars.csv', index_col = 0)
# Print out drives_right value of Morocco
print(cars.loc['MOR','drives_right'])
# Print sub-DataFrame
print(cars.loc[['RU','MOR'],['country','drives_right']])
# Import cars data
import pandas as pd
cars = pd.read_csv('cars.csv', index_col = 0)
# Print out drives_right column as Series
print(cars.loc[:,'drives_right'])
print(cars.iloc[:,2])
# Print out drives_right column as DataFrame
print(cars.loc[:,['drives_right']])
print(cars.iloc[:,[2]])
# Print out cars_per_cap and drives_right as DataFrame
print(cars.loc[:,['cars_per_cap','drives_right']])
print(cars.iloc[:,[0,2]])

Logic, Control Flow and Filtering

Comparison

Remember that for string comparison, Python determines the relationship based on alphabetical order.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

# Comparison of integers
x = -3 * 6
print (x >= -10)
# Comparison of strings
y = "test"
print("test" <= y)
# Comparison of booleans
print(True > False)
### output:
###     False
###     True
###     True

<> not equal

What is the difference between single and double quotes in SQL?

Stack Overflow Link

• Single quotes are used to indicate the beginning and end of a string in SQL. Double quotes generally aren’t used in SQL, but that can vary from database to database.

• Stick to using single quotes.

• [S]ingle quotes are for [S]trings ; [D]ouble quotes are for [D]atabase identifiers;

BETWEEN

It’s important to remember that BETWEEN is inclusive, meaning the beginning and end values are included in the results!

LIKE

• % - The percent sign represents zero, one, or multiple characters
• _ - The underscore represents a single character

NULL

In SQL, NULL represents a missing or unknown value. You can check for NULL values using the expression IS NULL.

Aggregate Functions

MIN(),MAX(),AVG(),SUM()

arithmetic

/SQL assumes that if you divide an integer by an integer, you want to get an integer back. So be careful when dividing!

/ in python will convert to float, no matter whether two values are integer or not.

Getting Started

Tutorial platform:plnkr

1
2
3
4
5
6

let value1 = "hello";
let value2 = 99;
console.log(value1);
console.log(typeof(value1));
console.log(typeof(value2));

1
2
3
4
5
6
7
8

let value1 = 10 / 0;
let value2 = 0 / 0;
console.log((value1));
console.log((value2));
console.log(typeof(value1));
console.log(typeof(value2));

Week 1： Getting Started and Selecting & Retrieving Data with SQL

Introduction

What is SQL?

Structured Query Language (SQL) is a standard computer language for relational database management and data manipulation.

Used to query, insert, update and modify data.

Pronounced as “sequel” or S-Q-L.

Used to communicate with databases;

SQL is non-procedural language:

• Cannot write complete applications

• Simple, but powerful

How is SQL used?

Read/retrieve data

Write data - add data to a table;

Update data - insert new data;

Who uses SQL?

• Backend Developer
• Data Architect
• QA Engineer
• ETL Developer
• Database Admin (DBA)
• Data analyst
• System Admin
• System Engineer
• Data Scientist

DBA or Data Scientist

How do data scientists use SQL?

Retrieve data;

May create their own table or test enviroments;

Combine multiple sources together;

Write complex queries for analysis.

Relational Database Management Systems

• SQL Server
• IBM DB2 Oracle
• Sybase ASE
• PostgreSQL
• MySQL
• Microsoft SQL Server
• Apache Open Office Base
• SQLite

Write syntax depends on what DBMS you are using.

Each DBMS has its own dialect.

SQL can translate.

Tweak based on the dialect your DBMS speaks.

Data Models

Thinking about your data

Understand the business process or subject matter the data is modeled after

Know the bussiness rules

Understand how your data is organized and structured in the table(modeled).

Why this is worthwhile

Get more accurate results,

Speed up your work,

Have less rework.

Some concepts

What is data modeling?

Organizes and structures information into multiple, related tables.

Can represent a bussiness process or show relationships between bussiness processes.

Should closely represent real world.

Types of data modeling

Models for prediction built by data scientist.

Data model as data tables represented and organized in a database.

Evolution of data models

Relational vs. Transactional Models

Data model building blocks

• One-to-many: customer to invoices.
• Many-to-many: student to classes.
• One-to-one: manager to store.

ER Diagrams

ER Model

• Show relationships
• Bussiness process
• Represented visually
• Show links (Primary keys)

Some examples of ER Diagram

Retrieving data with SELECT statement

1
2

SELECT prod_name
FROM Products;

For multiple columns, be sure to use a comma.

1
2

SELECT prod_name,prod_id,prod_price
FROM Products;

The following expression is more clearly.

1
2
3
4

SELECT 	prod_name
		,prod_id
		,prod_price
FROM Products;

Retrieving multiple columns using a wildcard

Request all columns by using a asterisk(*) wildcard character.

1
2

SELECT *
FROM Products;

Limit results

If the database is very large, we can use LIMIT to limit the number of retrieved results.

1
2
3

SELECT *
FROM Products
LIMIT 5;

• Different Syntaxes

1
2
3
4
5
6
7
8
9
10
11
12
13
14

|Oracle|```SELECT prod_name FROM products WHERE ROWNUM <=5;```|
|DB2|```SELECT prod_name FROM products FETCH FIRST 5 ROWS ONLY;```|
## Creating Tables
```sql
CREATE TABLE Shoes
(
Id		char(10)		PRIMARY KEY,
Brand	char(10)		NOT NULL,
Price	decimal(8,2)	NOT NULL,
Desc	Varchar(750)	NULL
);

NULL and Primary Keys

Every column is either NULL or NOT NULL.

Primary keys cannot be null, they must have a value.

Adding data to the table

1
2
3
4
5
6
7
8
9
10
11
12
13

INSERT INTO Shoes
(Id
,Brand
,Price
,Desc
)
VALUES
('12345'
,'Gucci'
,'695.00'
,NULL
)
;

Create a template table

1
2
3
4
5
6

CREATE TEMPORARY TABLE Sandals AS
(
SELECT *
FROM Shoes
WHERE shoe_type = 'sandals'
);

Adding comments

Single line

1
2
3
4

SELECT shoe_id
--,brand_id
,shoe_name
FROM Shoes;

Section

1
2
3
4
5

SELECT shoe_id
/*,brand_id
,shoe_name
*/
FROM Shoes;

Week 2: Filtering, Sorting, and Calculating Data with SQL

Introductions

Clauses and Operators in SQL

• WHERE
• BETWEEN
• IN
• OR
• NOT
• LIKE
• ORDER BY
• GROUP BY

Wildcards

Math Operators

• AVERAGE
• COUNT
• MAX
• MIN

Basics of Filtering with SQL

Why?

• Be specific about the data you want to retrieve
• Reduce the number of records you retrieve
• Increase query performance
• Reduce the strain on the client application
• Goverance the limitations

WHERE Clause

1
2
3

SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

Some clause operators

Filtering on a single condition

1
2
3
4
5

SELECT ProductName
,UnitPrice
,SupplierID
FROM Products
WHERE ProductName =  'Tofu';

Filtering on a single value

1
2
3
4
5

SELECT ProductName
,UnitPrice
,SupplierID
FROM Products
WHERE UnitPrice >=  75;

Checking for non-matches

1
2
3
4
5

SELECT ProductName
,UnitPrice
,SupplierID
FROM Products
WHERE ProductName <>  'Alice';

Filtering with a range of values (BETWEEN…AND…)

1
2
3
4
5

SELECT ProductName
,UnitPrice
,SupplierID
FROM Products
WHERE UnitsInStock BETWEEN 15 AND 80;

Filtering no value (NULL)

1
2
3
4
5

SELECT ProductName
,UnitPrice
,SupplierID
FROM Products
WHERE ProductName IS NULL;

Advanced filtering: IN, OR, and NOT

IN operator

• Specifies a range of conditions
• Comma delimited list of values
• Enclosed in ()

Example

1
2
3
4
5

SELECT ProductID
,UnitPrice
,SupplierID
FROM Products
WHERE SupplierID IN (9,10,11);

OR operator

DBMS will not evaluate the second conditions in WHERE clause if the first condition is met.

Use for any rows matching the specific conditions.

1
2
3
4
5

SELECT ProductName
,UnitPrice
,SupplierID
FROM Products
WHERE ProductName =  'Tofu' OR 'Konbu';

IN vs. OR

IN works the same as OR.

Benfits of IN:

1. Long list of options,
2. IN executes faster than OR,
3. Don’t have to think about the order with IN,
4. Can contain another SELECT5

OR with AND

1
2
3
4
5

SELECT ProductName
,UnitPrice
,SupplierID
FROM Products
WHERE SupplierID =  9 OR SupplierID =  11 AND UnitPrice > 15;

1
2
3
4
5

SELECT ProductName
,UnitPrice
,SupplierID
FROM Products
WHERE (SupplierID =  9 OR SupplierID =  11) AND UnitPrice > 15;

Don’t rely on the default order, it’s better to have the habit of using the ().

NOT operator

1
2
3

SELECT *
FROM Employees
WHERE NOT City = 'London' AND NOT City = 'Seattle';

Using Wildcards in SQL

• Special character used to match parts of a value,
• Use LIKE Operator

What are wildcards?

• Can only be used with strings.
• Cannot be used for non-text datatype.
• Helpful for data scientists as they explore string variables.

Using % wildcards

• % wildcards will not match NULLS, NULL represents no value in a column.

Using underscore(_) wildcards

• Matches a single character.
• Is not supported by DB2.

Downsides of wildcards

• Takes longer to run.
• Better to use another operator (=, <, >=, and etc.)
• Statements with wildcards will take longer to run if used at the end of search pattern.
• Placement of wildcards is important.

Sorting with ORDER BY

1
2
3

SELECT something
FROM database
ORDER BY characteristic;

• Can sort by a column not retrieved
• Must always be the last clause in a select statement

Sorting by column position

1

ORDER BY 2,3;

It means data sorted by 2nd and 3rd column.

Sort direction

DESC: descending order

ASC: ascending order

Only applies to the column names it directly precedes.

Math operators

Order of operators

• Parenthese
• Exponents
• Multiplication
• Division
• Addition
• Subtraction

Use parenthese if needed

1
2
3
4
5
6

SELECT ProductID
,Quantity
,UnitPrice
,Discount
,(UnitPrice - Discount) / Quantity AS Total_Cost
FROM Orders;

Aggregate Functions

What?

• Used to summarize data
• Finding the highest and lowest values
• Finding the total number of rows
• Finding the average value

Functions

Average function

1
2

SELECT AVG(UnitPrice) AS avg_price
FROM products;

Count functions

1.COUNT(*) - Counts all rows (contain NULLs).

1
2

SELECT COUNT(*) AS total_customers
FROM Customers;

2.COUNT(Column_name) - Counts all rows in a specific column ignoring NULL value.

1
2

SELECT COUNT(CustomerID) AS total_customers
FROM Customers;

MIN and MAX functions

Columns with NULL values are ignored by MIN and MAX functions.

1
2
3

SELECT MAX(UnitPrice) AS max_prod_price
,MIN(UnitPrice) AS min_prod_price
FROM Products;

SUM aggregate functions

1
2

SELECT SUM(UnitPrice) AS total_prod_price
FROM Products;

Using DISTINCT on aggregate functions

• If DISTINCT is not specified, ALL is assumed.
• Cannot use DISTINCT on COUNT(*).
• No value to use with MIN and MAX function.

1
2

SELECT COUNT(DISTINCT CustomerID)
FROM Customers;

Grouping Data with SQL

Grouping examples

1
2
3
4
5

SELECT 
Region
,COUNT(CustomerID) AS total_customers
FROM Customers
GROUP BY Region;

Notices

• GROUP BY clause can contain multiple columns.
• Every column in your SELECT statement must be present in a GROUP BY clause (except for aggregate functions).
• NULLs will be grouped together if your GROUP BY column contains NULLs.

HAVING clause filtering for groups

WHERE does not work for GROUP.

WHERE filters on rows.

Instead using HAVING clause to filter for groups.

HAVING examples

1
2
3
4
5
6

SELECT 
CustomerID
,COUNT(*) AS orders
FROM Orders
GROUP BY CustomerID
HAVING COUNT(*) >=2;

WHERE vs. HAVING

WHERE filters before data is grouped.

HAVING filters after data is grouped.

Rows elimated by WHERE clause will not be included in the group.

1
2
3
4
5
6
7

SELECT 
SupplierID
,COUNT(*) AS Num_prod
FROM Products
WHERE UnitPrice >= 4
GROUP BY SupplierID
HAVING COUNT(*) >=2;

Putting it all together

Filtering is useful

Narrow down your results.

Increasing query & application performance.

Understanding your data:

• Finding specific values
• Finding a range of values
• Finding blank values

Key SQL clauses

Week 3: Subqueries and joins in SQL

Using Subqueries

What?

• Queries embedded into other queries
• Relational databases store data in multiple tables
• Subqueries merge data from multiple source together
• Helps with adding other filtering criteria

Example

1
2
3
4
5
6
7

SELECT CustomerID
,CompanyName
,Region
FROM Customers
WHERE customerID in (SELECT CustomerID
		FROM Orders
		WHERE Freight > 100);

Working with Subsequery statements

Always perform the innermost SELECT portion first.

DBMS is performing two operations

1. Getting the order numbers for the product selected,
2. Adding that to the WHERE clause and processing the overall SELECT statement.

Subquery best practices and considerations

1. There is no limit to the number of subqueries you can have.
2. Performance slows when you nest too deeply.
3. Subquery selects can only retrieve a single column.

Subquery in subquery

Be sure to use indenting.

1
2
3
4
5
6
7

SELECT customer_name
,customer_state
,(SELECT COUNT(*) AS orders
	FROM Orders
	WHERE Orders.customer_id = Customer.customer_id) AS orders
FROM Customers
ORDER BY Customer_name;

The power of subqueries

• Subqueries are powerful tools
• Not always the best option due to performance

Joining tables: an introduction

Benefits of breaking data into tables

• Efficient storage
• Easier manipulation
• Greater scalability
• Logically models a process
• Tables are related through common values (keys)

Joins

• Associate correct records from each table on the fly.
• Allows data retrieval from multiple tables in one query.
• Joins are not physical, they persist data for the duration of the query execution.

Cartesian (Cross) Joins

• Not frequently used.
• Computationally taxing.
• Will return products with the incorrect vendor or no vendor at all.

Examples

1
2
3
4
5

SELECT vendor_name
,unit_price
,company_name
FROM suppliers
CROSS JOIN products;

1
2
3
4
5

SELECT vendor_name
,product_name
,product_price
FROM Vendors,Products
WHERE Vendors.vendor_id = Products.vendor_id;

Inner Joins

What?

The INNER JOIN keyword selects records that have matching values in both tables.

Example

1
2
3
4
5

SELECT suppliers.CompanyName
,ProductName
,UnitPrice
FROM Suppliers INNER JOIN Products
ON Suppliers.supplierid = Products.supplierid;

Syntax

• Join type is specified (INNER JOIN)
• Join condition is in the FROM clause and uses ON clause
• Joining more tables together affects overall database performance
• You can join multiple tables, no limit
• List all the tables, then define conditions

Inner Join with multiple tables

1
2
3
4
5

SELECT o.OrderID,
c.CompanyName,
e.LastName
FROM ((Orders o INNER JOIN Customers c ON o.CustomerID = c.CustomerID)
INNER JOIN Employee e ON o.EmployeeID = e.EmployeeID);

Best practices

• Make sure you are pre-qualifying names
• Do not make unnecessary joins
• Think about the type if join you are making

Aliases and self joins

What?

1
2

SELECT column_name
FROM table_name AS alias_name;

Query example using alias

1
2
3
4
5

SELECT vendor_name
,product_name
,product_price
FROM Vendors AS v, Products AS p
WHERE v.vendor_id = p.vendor_id;

Self joins

• Match customers from the same city
• Take the table and treat it like two separate tables
• Join the original table to itself

1
2
3

SELECT column_names
FROM Table1 T1, Table2 T2
WHERE conditions;

Examples

Matching the customers from the same city:

1
2
3
4
5
6
7

SELECT A.CustomerName AS CustomerName1
,B.CustomerName AS CustomerName2
,A.City
FROM Customers A, Customers B
WHERE A.CustomerID = B.CustomerID
AND A.City = B.City
ORDER BY A.City;

Advanced joins: Left, Right and Full Outer Joins.

• SQL Lite only does left joins,
• Other DBMS use all joins.

Left Join

Return all records from the left table, and the matched records from the right table.

The result is NULL from the right side, if there is no match.

1
2
3
4
5

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

Right Join

Return all records from the right table, and the matched records from the left table.

The result is NULL from the left side, if there is no match.

1
2
3
4
5

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

Left Joins can be turned into right joins by reversing the order of tables.

Full Outer Join

Return all records when there is a match in either left table or right table records.

Select all customers and orders:

1
2
3
4
5

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL OUTER JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

Unions

• The UNION operator is used to combine the result-set of two or more SELECT statements.

• Each SELECT statement with UNION must have the same number of columns.

• Columns must have similar data types.
• The columns in each SELECT statement must be in the same order.

1
2
3

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

Note

• UNION selects only distinct values.
• Use UNION ALL to also select duplicate values!

Avoiding duplicate in a Union

1
2
3
4
5

SELECT * FROM table1
WHERE a = X
UNION ALL
SELECT * FROM table2
WHERE b = Y AND a!=X;

Summary

Best practices using joins

• Check the number of records.
• Check for duplicates.
• Check the number of records each time you make a new join.
• Start smart: one table at a time.

Join syntax

• Check the particular syntax for your DBMS.
• SQLite does not do RIGHT and FULL OUTER joins.

Further readings

1. Difference Between Union and Union All - Optimal Performance Comparison
2. Thinking in SQL vs Thinking in Python

Module 3 Coding Assignment

All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram in order to familiarize yourself with the table and column names in order to write accurate queries and get the appropriate answers.

1. Using a subquery, find the names of all the tracks for the album “Californication”.

1
2
3
4
5
6

SELECT Name
FROM Tracks
WHERE AlbumId IN (
SELECT AlbumId
FROM Albums
WHERE Title = "Californication");

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

+-------------------+
| Name              |
+-------------------+
| Around The World  |
| Parallel Universe |
| Scar Tissue       |
| Otherside         |
| Get On Top        |
| Californication   |
| Easily            |
| Porcelain         |
| Emit Remmus       |
| I Like Dirt       |
+-------------------+
(Output limit exceeded, 10 of 15 total rows shown)

2. Find the total number of invoices for each customer along with the customer’s full name, city and email.

1
2
3
4
5

SELECT Email,City,FirstName,
(SELECT COUNT(*)
FROM Invoices
WHERE Invoices.CustomerId = Customers.CustomerId) AS Numbers
FROM Customers;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

+--------------------------+---------------------+-----------+---------+
| Email                    | City                | FirstName | Numbers |
+--------------------------+---------------------+-----------+---------+
| luisg@embraer.com.br     | São José dos Campos | Luís      |       7 |
| leonekohler@surfeu.de    | Stuttgart           | Leonie    |       7 |
| ftremblay@gmail.com      | Montréal            | François  |       7 |
| bjorn.hansen@yahoo.no    | Oslo                | Bjørn     |       7 |
| frantisekw@jetbrains.com | Prague              | František |       7 |
| hholy@gmail.com          | Prague              | Helena    |       7 |
| astrid.gruber@apple.at   | Vienne              | Astrid    |       7 |
| daan_peeters@apple.be    | Brussels            | Daan      |       7 |
| kara.nielsen@jubii.dk    | Copenhagen          | Kara      |       7 |
| eduardo@woodstock.com.br | São Paulo           | Eduardo   |       7 |
+--------------------------+---------------------+-----------+---------+
(Output limit exceeded, 10 of 59 total rows shown)

3. Retrieve the track name, album, artist, and trackID for all the albums.

1
2
3
4

SELECT Tracks.Name, Tracks.trackID, Albums.Title, Albums.ArtistId
FROM Albums
LEFT JOIN Tracks
ON Albums.AlbumId = Tracks.AlbumId;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

+-----------------------------------------+---------+---------------------------------------+----------+
| Name                                    | TrackId | Title                                 | ArtistId |
+-----------------------------------------+---------+---------------------------------------+----------+
| For Those About To Rock (We Salute You) |       1 | For Those About To Rock We Salute You |        1 |
| Put The Finger On You                   |       6 | For Those About To Rock We Salute You |        1 |
| Let's Get It Up                         |       7 | For Those About To Rock We Salute You |        1 |
| Inject The Venom                        |       8 | For Those About To Rock We Salute You |        1 |
| Snowballed                              |       9 | For Those About To Rock We Salute You |        1 |
| Evil Walks                              |      10 | For Those About To Rock We Salute You |        1 |
| C.O.D.                                  |      11 | For Those About To Rock We Salute You |        1 |
| Breaking The Rules                      |      12 | For Those About To Rock We Salute You |        1 |
| Night Of The Long Knives                |      13 | For Those About To Rock We Salute You |        1 |
| Spellbound                              |      14 | For Those About To Rock We Salute You |        1 |
+-----------------------------------------+---------+---------------------------------------+----------+
(Output limit exceeded, 10 of 3503 total rows shown)

4. Retrieve a list with the managers last name, and the last name of the employees who report to him or her.

1
2
3
4

SELECT e1.LastName AS manager
, e2.LastName AS employee
FROM Employees e1, Employees e2
WHERE e1.EmployeeId = e2.ReportsTo;

1
2
3
4
5
6
7
8
9
10
11

+----------+----------+
| manager  | employee |
+----------+----------+
| Adams    | Edwards  |
| Adams    | Mitchell |
| Edwards  | Peacock  |
| Edwards  | Park     |
| Edwards  | Johnson  |
| Mitchell | King     |
| Mitchell | Callahan |
+----------+----------+

5. Find the name and ID of the artists who do not have albums.

1
2
3
4
5

SELECT Artists.Name, Artists.ArtistId
FROM Artists
LEFT JOIN Albums
ON Artists.ArtistId = Albums.ArtistId
WHERE Albums.AlbumId IS NULL;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

+----------------------------+----------+
| Name                       | ArtistId |
+----------------------------+----------+
| Milton Nascimento & Bebeto |       25 |
| Azymuth                    |       26 |
| João Gilberto              |       28 |
| Bebel Gilberto             |       29 |
| Jorge Vercilo              |       30 |
| Baby Consuelo              |       31 |
| Ney Matogrosso             |       32 |
| Luiz Melodia               |       33 |
| Nando Reis                 |       34 |
| Pedro Luís & A Parede      |       35 |
+----------------------------+----------+
(Output limit exceeded, 10 of 71 total rows shown)

6. Use a UNION to create a list of all the employee’s & customer’s first names and last names ordered by the last name in descending order.

1
2
3
4
5
6

SELECT FirstName,LastName
FROM Employees
UNION
SELECT FirstName,LastName
FROM Customers
ORDER BY LastName DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

+-----------+--------------+
| FirstName | LastName     |
+-----------+--------------+
| Fynn      | Zimmermann   |
| Stanisław | Wójcik       |
| František | Wichterlová  |
| Johannes  | Van der Berg |
| François  | Tremblay     |
| Mark      | Taylor       |
| Ellie     | Sullivan     |
| Victor    | Stevens      |
| Puja      | Srivastava   |
| Jack      | Smith        |
+-----------+--------------+
(Output limit exceeded, 10 of 67 total rows shown)

7. See if there are any customers who have a different city listed in their billing city versus their customer city.

1
2
3
4
5

SELECT COUNT(*)
FROM Customers
LEFT JOIN Invoices
ON Customers.CustomerId = Invoices.CustomerId
WHERE Customers.City <> Invoices.BillingCity;

1
2
3
4
5

+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

Week 4: Modifying and analyzing data with SQL

Working with text strings

Retrieve the data in the format you need

Support Joins

String Functions:

• Concatenations

1
2
3
4
5

SELECT
CompanyName
,ContactName
,CompanyName || '(' || ContactName || ')'
FROM customers;

SQL server supports + instead of ||

• Trimming strings

Trims the leading or trailing space from a string

TRIM

RTRIM

LTRIM

• Substring

Returns the specified number of characters from a particular position of a given strings.

1

SUBSTR(string name, string position, number of characters to be returned)

1
2
3

SELECT firstname
,SUBSTR(firstname,2,3)
FROM employees;

• Upper and Lower

1

SELECT UPPER(column_name) FROM tablename;

1

SELECT LOWER(column_name) FROM tablename;

1

SELECT UCASE(column_name) FROM tablename;

Working with date and time strings

• Dates are stored as datetypes
• Each DBMS uses it’s own variety of datetypes

DATE: Format YYYY-MM-DD

DATETIME: Format YYYY-MM-DD HH:MI:SS

TIMESTAMP: Format YYYY-MM-DD HH:MI:SS

Date and time string

Chapter 1

Glossary

|market-maker||Link|
|||Link|

Chapter 2

Glossary

|colocation||Link|

Reference

1. W3school
2. Mysql official manual
3. RUNNOOB

FUNCTIONS

REGEXP

[Challenge Name: Weather Observation Station 3]

Query a list of CITY names from STATION with even ID numbers only. You may print the results in any order, but must exclude duplicates from your answer.

Solution

1

select distinct city from station where (id % 2 = 0);

Challenge Name: Weather Observation Station 4

Let $N$ be the number of CITY entries in STATION, and let $N’$ be the number of distinct CITY names in STATION; query the value of $N - N’$ from STATION. In other words, find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table.

Solution

1

select (count(city) - count(distinct city)) from station;

[Challenge Name: Weather Observation Station 5]

Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.

Solution

1
2

select city, length(city) from station order by length(city),city limit 1;
select city, length(city) from station order by length(city) desc,city limit 1;

[Challenge Name: Weather Observation Station 6]

Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates.

Solution

MYSQL: REGEXP used for regular expression

1

SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '^[aeiou]';

[Challenge Name: Weather Observation Station 7]

Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION. Your result cannot contain duplicates.

Solution

1

SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '[aeiou]$'

[Challenge Name: Weather Observation Station 8]

Query the list of CITY names from STATION which have vowels (i.e., a, e, i, o, and u) as both their first and last characters. Your result cannot contain duplicates.

Solution

1

SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '^[aeiou].*[aeiou]$';

Challenge Name: Weather Observation Station 9

Query the list of CITY names from STATION that do not start with vowels. Your result cannot contain duplicates.

Solution

1

SELECT DISTINCT CITY FROM STATION WHERE CITY NOT REGEXP '^[aeiou]';

Challenge Name: Weather Observation Station 11

Query the list of CITY names from STATION that either do not start with vowels or do not end with vowels. Your result cannot contain duplicates.

Solution

either do not start with vowels or do not end with vowels == not start with vowels and end with vowels.

1

SELECT DISTINCT CITY FROM STATION WHERE CITY NOT REGEXP '^[aeiou].*[aeiou]$';

Challenge Name: Weather Observation Station 12

Query the list of CITY names from STATION that do not start with vowels and do not end with vowels. Your result cannot contain duplicates.

Solution

1

SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '^[^aeiou].*[^aeiou]$';

Challenge Name: Higher Than 75 Marks

Query the Name of any student in STUDENTS who scored higher than $75$ Marks. Order your output by the last three characters of each name. If two or more students both have names ending in the same last three characters (i.e.: Bobby, Robby, etc.), secondary sort them by ascending ID.

Solution

1

SELECT NAME FROM STUDENTS WHERE MARKS > 75 ORDER BY RIGHT(NAME,3), ID ASC;

All About High-frequency Trading

Amazon book link:

https://www.amazon.com/All-About-High-Frequency-Trading/dp/0071743448

I borrowed one copy from school library, and want to use this book to learn some basic concepts about HFT.

ANTLR Hands on

Environments & Installation

Install JDK

Reference:

In Ubuntu:

1

sudo apt-get install openjdk-8-jre

In CentOS:

1

sudo yum install java-1.8.0-openjdk

In Mac OS:

Download jdk-8uversion-macosx-amd64.dmg and double click on it.

Check Java Version

1
2
3
4
5

$ java -version
java version "1.8.0_51"
Java(TM) SE Runtime Environment (build 1.8.0_51-b16)
Java HotSpot(TM) 64-Bit Server VM (build 25.51-b03, mixed mode)

Download ANTLR

1

wget http://www.antlr.org/download/antlr-3.5.2-complete.jar

Add this to the CLASSPATH:

1

export CLASSPATH=~/software/antlr-3.5.2-complete.jar:$CLASSPATH

Verify:

1
2
3

$ java org.antlr.Tool -version
ANTLR Parser Generator  Version 3.5.2

Tutorials

Refer:
links

• $A Lexer$: This reads an input character or byte stream (i.e. characters, binary data, etc.), divides it into tokens using patterns you specify, and generates a token stream as output. It can also flag some tokens such as whitespace and comments as hidden using a protocol that ANTLR parsers automatically understand and respect.
• $A Parser$: This reads a token stream (normally generated by a lexer), and matches phrases in your language via the rules (patterns) you specify, and typically performs some semantic action for each phrase (or sub-phrase) matched. Each match could invoke a custom action, write some text via StringTemplate, or generate an Abstract Syntax Tree for additional processing.

Create SimpleCalc.g:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

grammar SimpleCalc;
 
tokens {
    PLUS    = '+' ;
    MINUS   = '-' ;
    MULT    = '*' ;
    DIV = '/' ;
}
 
@members {
    public static void main(String[] args) throws Exception {
        SimpleCalcLexer lex = new SimpleCalcLexer(new ANTLRFileStream(args[0]));
        CommonTokenStream tokens = new CommonTokenStream(lex);
 
        SimpleCalcParser parser = new SimpleCalcParser(tokens);
 
        try {
            parser.expr();
        } catch (RecognitionException e)  {
            e.printStackTrace();
        }
    }
}
 
/*------------------------------------------------------------------
 * PARSER RULES
 *------------------------------------------------------------------*/
 
expr    : term ( ( PLUS | MINUS )  term )* ;
 
term    : factor ( ( MULT | DIV ) factor )* ;
 
factor  : NUMBER ;
 
 
/*------------------------------------------------------------------
 * LEXER RULES
 *------------------------------------------------------------------*/
 
NUMBER  : (DIGIT)+ ;
 
WHITESPACE : ( '\t' | ' ' | '\r' | '\n'| '\u000C' )+    { $channel = HIDDEN; } ;
 
fragment DIGIT  : '0'..'9' ;

Run this file:

1

java org.antlr.Tool SimpleCalc.g

Then, we can find three generated files:

SimpleCalc.tokens, SimpleCalcParser.java and SimpleCalcLexer.java