mySQL Queries (Financial Asset Database)

The following is a hypothetical dataset of 20 securities with various financial variables for each. As a database language, mySQL allows us to select specific data as specified by the user, as well as conduct certain calculations on the data already available. In this regard, we use mySQL queries below to illustrate the use of the same in manipulating the database and conducting various calculations (note that the securities in this database are hypothetical, and any resemblance to a real-life security or company is merely coincidental).

Our database for this exercise is named MGFinance and the primary table we will create is called stock_selections.

Firstly, we create the stock_selections table using the following query:

CREATE TABLE stock_selections
(
ID varchar(255),
YearlyReturn decimal(18,5),
DividendYield decimal(18,5),
DebtEquity decimal(18,5),
PERatio decimal(18,5),
PricetoFCFRatio decimal(18,5),
YearlyEPSChange decimal(18,5),
NetIncome decimal(18,5),
ShareholderEquity decimal(18,5),
Sales decimal(18,5),
Inventory decimal(18,5)
);

Note that if the table already exists in the database, and we wish to delete it and start from scratch, we can always run the following query first:

DROP TABLE IF EXISTS stock_selections

We then use the below query to define our relevant variables for the stock_selections table contained in the MGFinance database, and insert the relevant values:

INSERT INTO MGFinance.stock_selections (ID, YearlyReturn, DividendYield, DebtEquity, PERatio, PricetoFCFRatio, YearlyEPSChange, NetIncome, ShareholderEquity, Sales, Inventory)

VALUES ('3SOWO', '8.11', '0.7', '0.46', '14.8', '7.39', '-9.58', '718805730', '3146789135', '398935732', '152591876'), ('AFASM', '14.2', '0.71', '1.29', '5.94', '7.4', '29.29', '493395901', '3171592425', '871819920', '495389897'), ('ASLWQ', '16.12', '1.23', '0.11', '17.94', '12.37', '-17.11', '785854992', '3366086537', '804417721', '498203160'), ('BBDAA', '-14.08', '4.85', '1.61', '16.54', '12.64', '1.61', '273214402', '1810273098', '242358112', '145935702'), ('BXXOW', '17.11', '0.11', '1.31', '7.57', '11.98', '14.31', '266647538', '2941392555', '377324475', '176547145'), ('CBLEE', '-15.3', '4.16', '1.48', '8.43', '14.68', '6.48', '136913987', '4520039721', '306417258', '155456791'), ('IOSPA', '19.26', '2.87', '0.59', '22.63', '15.98', '26.59', '759573606', '2119671374', '618321357', '478249155'), ('KWOSP', '-15.64', '5.45', '0.96', '21.3', '13.81', '50.26', '440168276', '1787569036', '158191662', '386845657'), ('MMRMM', '13.78', '0.39', '1.17', '16.32', '6.5', '17.17', '505687204', '1843253620', '495740645', '456053827'), ('NNFFN', '14.36', '4.9', '0.96', '18.37', '9.74', '-5.96', '465839986', '4945897852', '634646570', '491299402'), ('OOSLA', '8.17', '0.3', '1.83', '8.74', '9.3', '3.83', '664444354', '3548211543', '525069484', '102283045'), ('SADLA', '13.11', '3.44', '0.78', '18.65', '6.42', '30.78', '433585899', '2026413087', '603175377', '244785459'), ('SLDWE', '-3.54', '2.75', '0.88', '8.48', '13.89', '-7.88', '244254641', '3128993268', '642955083', '390710702'), ('SPQPO', '-6.55', '2.13', '0.14', '25.66', '15.31', '-9.14', '677650559', '2328483810', '456055649', '321279232'), ('STOWG', '-14.06', '2.85', '0.61', '10.94', '8.62', '11.61', '475063755', '3283736400', '795785580', '385985579'), ('WOEPQ', '0.03', '3.13', '1.84', '22.84', '9.23', '21.84', '410089090', '3591560399', '160777388', '218052057'), ('WOQPP', '-2.07', '2.64', '0.59', '19.1', '8.47', '10.59', '626769580', '4357602324', '680939681', '210111931'), ('WOWOL', '18.87', '0.18', '0.21', '24.29', '12.86', '-20.21', '167320788', '2665596994', '707040194', '337396151'), ('WWIOP', '-2.25', '3.31', '0.69', '13.13', '13.7', '-2.69', '382902244', '1469725591', '624286274', '226692854'), ('ZZXAAZ', '0.77', '3.5', '2.9', '12.94', '7.26', '-6.9', '398499039', '3379796323', '432637933', '309340545')

Server in SQL Format


Query 1 – SELECT, FROM, WHERE, ORDER BY

Most commonly, we wish to use SQL to be able to filter among the entries in our database. In the case of the below, we wish to include only those securities with a dividend yield above 3%, and a yearly EPS change of 30% or higher. Moreover, using the net income, shareholder equity, sales, and inventory variables, we wish to use these to calculate the ratios of ROE and Inventory Turnover appropriately. Finally, we wish to order by Security in alphabetical order. To carry out these tasks, we use the following commands as below:

SELECT ID, YearlyReturn, DividendYield, NetIncome / ShareholderEquity ROE, Sales / Inventory Inventory_Turnover, YearlyEPSChange
FROM stock_selections
WHERE (DividendYield >= 3 ) **AND** YearlyEPSChange >= 30
ORDER BY ID

table1


Query 2 – COUNT

In order to query the number of entries in a particular table, we use the Count(*) query as follows:

SELECT COUNT( * )
FROM stock_selections

table2


Query 3 – LIKE

In addition, there will be occasions where we wish to select data based on a specified character entry. e.g. in this case we wish to select all securities beginning with the letter S. Therefore, we input the following:

SELECT *
FROM stock_selections
WHERE ID LIKE 'S%'

table3


Query 4 – DELETE

There are often instances where we wish to delete a specific entry from our database. To do so, we use the DELETE command as follows:
 

DELETE FROM stock_selections
WHERE ID='IOSPA'

Query 5 – UPDATE

Should we wish to update a certain entry in our database, we do so using the UPDATE command. For instance, the IOSPA entry in our database has values of 19.26 and 2.87 respectively. Using the UPDATE command, we change them to 15 and 2.5 as follows:
 

UPDATE stock_selections
SET YearlyReturn=15, DividendYield=2.5
WHERE ID='CBLEE';

Query 6 – INNER JOIN

There are occasions where we wish to join columns from two separate tables together. In the first instance, we will create an extra table in our database called “stock_orders”.

CREATE TABLE stock_orders
(
ID varchar(255),
OrderID int(11),
Units int(11),
ValueDate datetime
);

We expand our database using the additional query:

INSERT INTO stock_orders (ID, OrderID, Units, ValueDate)
VALUES ('3SOWO', '5', '30198', '2015/06/11'), ('AFASM', '7', '34871', '2012/07/25'), ('ASLWQ', '4', '3489', '2015/11/02'), ('BBDAA', '3', '27801', '2012/06/23'), ('BXXOW', '10', '32817', '2012/06/16'), ('CBLEE', '11', '16428', '2011/01/18'), ('IOSPA', '6', '48881', '2013/03/24'), ('KWOSP', '8', '1195', '2014/10/10'), ('MMRMM', '9', '5272', '2014/06/05'), ('NNFFN', '2', '15848', '2015/01/14'), ('OOSLA', '13', '49095', '2012/09/24'), ('SADLA', '19', '35282', '2011/07/11'), ('SLDWE', '16', '35727', '2012/12/15'), ('SPQPO', '14', '29756', '2015/12/20'), ('STOWG', '18', '43990', '2012/04/13'), ('WOEPQ', '15', '25589', '2011/09/26'), ('WOQPP', '1', '22271', '2015/04/05'), ('WOWOL', '12', '7355', '2013/12/29'), ('WWIOP', '20', '32895', '2011/08/10'), ('ZZXAAZ', '17', '8265', '2013/08/09')

Note that we have included the ‘ID’ field which is common to the first table that we had created. In this context, we will now use an INNER JOIN command to merge the ‘ID’ and ‘OrderID’ fields, and order the table by the ascending ‘OrderID’. Therefore, we run our query as follows:

SELECT stock_selections.ID, stock_orders.OrderID
FROM stock_selections
INNER JOIN stock_orders
ON stock_selections.ID=stock_orders.ID
ORDER BY stock_orders.OrderID;

We now see that our new table is ordered by the OrderID variable in ascending order as follows:

table6

Query 7 – CASE

The CASE function in mySQL functions as an “if-then” command, which triggers certain values should a given set of conditions be fulfilled.

In this particular example, we filter the stock_selection table to include the fields of ID and YearlyReturn. However, we also wish to include the dividend yield measurement under three categories, “Attractive Dividend Yield”, “Average Dividend Yield”, and “Unattractive Dividend Yield”. The conditions specified are as follows:

► If the dividend yield of a particular stock is equal to or greater than 2.5, then the dividend yield is specified as an “Attractive Dividend Yield”.

► If the dividend yield of a particular stock is equal to or greater than 2.5, then the dividend yield is specified as an “Average Dividend Yield”.

► If the dividend yield of a particular stock is equal to or greater than 2.5, then the dividend yield is specified as an “Unattractive Dividend Yield”.

SELECT ID, YearlyReturn, CASE
 WHEN DividendYield>= 2.5 THEN 'Attractive Dividend Yield'
WHEN DividendYield >= 2 THEN 'Average Dividend Yield'
 ELSE 'Unattractive Dividend Yield'
 END
"DividendYield"
FROM stock_selections;

 
table7