Creating and managing
MySQL databases
RDBMS stands for Relational Database Management System.
Basis for SQL, and for all modern database systems such as MS SQL.
Data is stored in database objects called tables.
A table is a collection of related data entries and it consists of columns and rows.
Search for Cloud SQL
An instance has been created called fintech, click on the instance
Scroll down to the link that says Open Cloud Shell and click
Authorize access
Note: May need this installed on cloud shell
Configure local client defaults in ~/.my.cnf (Recommended)
Set your local MySQL client to allow public key retrieval over local connections so gcloud sql connect works seamlessly.
Add the configuration option to your Cloud Shell ~/.my.cnf file:
cat <<EOF >> ~/.my.cnf
[client]
get-server-public-key=1
EOF
chmod 600 ~/.my.cnf
Note: password is Uconnstamford1!
Welcome to Cloud Shell! Type "help" to get started.
Your Cloud Platform project in this session is set to sentiment-analysis-379200.
Use `gcloud config set project [PROJECT_ID]` to change to a different project.
gcloud sql connect fintech --user=root --quietjohn_iacovacci1@cloudshell:~ (sentiment-analysis-379200)$ gcloud sql connect fintech --user=root --quiet
Starting Cloud SQL Proxy: [/usr/bin/cloud-sql-proxy sentiment-analysis-379200:us-east1:fintech --port 9470]
2026/09/09 17:47:34 Authorizing with Application Default Credentials
2026/09/09 17:47:35 [sentiment-analysis-379200:us-east1:fintech] Listening on 127.0.0.1:9470
2026/09/09 17:47:35 The proxy has started successfully and is ready for new connections!
Connecting to database with SQL user [root].Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 35058
Server version: 8.4.10-google (Google)
Copyright (c) 2000, 2026, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
A database instance set of memory structures manage database files.
Set of physical files created by the CREATE DATABASE statement.
Instance manages its data and serves the users of the database.
Follow the rules in Schema Object Names when you create your database name. If not set on creation, new databases have
the following default values:
To create a database on the Cloud SQL instance:
Now create a database
CREATE DATABASE (database name);
Creates a new database with name as parameter
SHOW DATABASES;
We use the DROP DATABASE statement to drop a SQL database.
DROP DATABASE databasename;
Note: Your database will be first initial then lastname.
mysql> CREATE database jiacovacci;
Query OK, 1 row affected (0.09 sec)
mysql>
USE (database name);
you must select it for use each time you begin a mysql session.
SQL USE statement used to select any existing database in the SQL schema.
mysql> use jiacovacci;
Data Definition Language
Data Definition Language describes the portion of SQL that creates, alters,
and deletes database objects. These database objects include schemas,
tables, views, sequences, catalogs, indexes, variables, masks,
permissions, and aliases.
Database changed
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
We can remove a table by using the DROP command.
Table name is the database table name being created within the database.
Databases may have many tables in them and tables can be related to each via relationship or field.
The column parameters specify the names of the columns of the table.
Column is the name of the field you wish to store in the table.
Datatype is the type of data to store.
The datatype parameter specifies the type of data the column can hold (e.g. varchar, integer, date, etc.).
SQL Data Types
Each column in a table is required to have a name and a data type.
Developers decide what type of data to be stored in each column.
E.G.
Numeric - INT, FLOAT, DECIMAL
Date and Time - DATE, TIME
String Types - CHAR, VARCHAR, TEXT
SQL Create Constraints
Constraints can be specified when the table is created with the CREATE
TABLE statement, or after the table is created with the ALTER TABLE
Statement.
Syntax
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
....
Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table.
The following constraints are commonly used in SQL:
NOT NULL - Ensures that a column cannot have a NULL value
column should always accept an explicit value of the given data.
UNIQUE - Ensures that all values in a column are different.
PRIMARY KEY - A combination of NOT NULL and UNIQUE. Uniquely identifies each row in a table.
FOREIGN KEY - Prevents actions that would destroy links between tables.
CHECK - Ensures that the values in a column satisfies a specific condition.
DEFAULT - Sets a default value for a column if no value is specified.
CREATE INDEX - Used to create and retrieve data from the database very quickly.
AUTO INCREMENT used for incrementing a value of a field .
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.
Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
Security table
Create a table within the database.
This table will hold security information
Security Table
figi -unique primary key
ticker- text
name - text
exchCode - text
securityType - text
marketSector - text
shareClassFIGI - text
securityType2 - text
securityDescription - text
mysql> CREATE TABLE security ( figi varchar(255), name varchar(255), ticker varchar(20), exchCode varchar(10), securityType varchar(25), marketSector varchar(50), shareClassFIGI varchar(20), securityType2 varchar(20), securityDescription varchar(30), PRIMARY KEY(figi));
Query OK, 0 rows affected (0.11 sec)
mysql>
SQL INSERT INTO Statement is used to add new rows of data to a table.
VALUES is the value that will be stored in the column name.
INSERT INTO (table name) column name VALUES "data";
Insert multiple rows with one insert statement by adding commas.
INSERT INTO MyTable ( Column1, Column2, Column3 ) VALUES ('John', 123, 'Lloyds Office'), ('Jane', 124, 'Lloyds Office'), ('Billy', 125, 'London Office'), ('Miranda', 126, 'Bristol Office');
mysql> INSERT INTO security (figi, name, ticker, exchCode, securityType, marketSector, shareClassFIGI, securityType2, securityDescription) values ('BBG000BJ29X7', 'SS FINANCIAL SELECT SECTOR', 'XLF', 'US', 'ETP', 'Equity', 'BBG001S7T223', 'Mutual Fund', 'XLF');
Query OK, 1 row affected (0.05 sec)
mysql>
SELECT (field list or *) FROM (table name) WHERE column name = "value";
SELECT * FROM (table name)
lists all records
Select (column name) FROM (table name) WHERE (column name) = "value";
mysql> select * from security;
+--------------+----------------------------+--------+------------+--------------+--------------+---------------+--------
| FIGI | NAME | TICKER | ExchangeCd | SecurityType | MarketSector | FIGIComposite | ShareClass |
+--------------+----------------------------+--------+------------+--------------+--------------+---------------+--------------+
| BBG000BJ29X7 | SS FINANCIAL SELECT SECTOR | XLF | US | ETP | Equity | BBG000BJ29X7 | BBG001S7T223 |
+--------------+----------------------------+--------+------------+--------------+--------------+---------------+--------------+
1 row in set (0.00 sec)
The SQL UPDATE Statement
The UPDATE statement is used to modify the existing records in a table.
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Updates a record in a table by setting a value of a column where a condition is
mysql> UPDATE security SET name = "SS FINANCIAL SELECT STR" WHERE figi = "BBG000BJ29X7";
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> select * from security;
--------------+--------------+
| FIGI | NAME | TICKER | ExchangeCd | SecurityType | MarketSector | FIGIComposite | ShareClass |
+--------------+-------------------------+--------+------------+--------------+--------------+---------------+--------------+
| BBG000BJ29X7 | SS FINANCIAL SELECT STR | XLF | US | ETP | Equity | BBG000BJ29X7 | BBG001S7T223 |
+--------------+-------------------------+--------+------------+--------------+--------------+---------------+--------------+
1 row in set (0.00 sec)
mysql>
The SQL DELETE Statement
The DELETE statement is used to delete existing records in a table.
DELETE Syntax
DELETE FROM table_name WHERE condition;
mysql> DELETE FROM security WHERE figi = "BBG000BJ29X7";
Query OK, 1 row affected (0.01 sec)
mysql> select * from security;
Empty set (0.00 sec)
Build Tables
Company table
mysql> CREATE TABLE company ( Ticker varchar(20), Comp_Name varchar(50), Comp_Street varchar(50), Comp_City varchar(50), Comp_State varchar(25), Comp_Zip varchar(20), Comp_Country varchar(20), Sector varchar(50), Industry varchar(50), PRIMARY KEY(Ticker));
Query OK, 0 rows affected (0.04 sec)
Sector table
mysql> CREATE TABLE sector ( sec_name varchar(50), sec_percentage decimal(10, 2) , sec_desc varchar(100), PRIMARY KEY(sec_name));
Query OK, 0 rows affected (0.04 sec)
mysql>
Holdings Table
mysql> CREATE TABLE holdings (Holding_strategy VARCHAR(50) NOT NULL, Holdings_date DATE NOT NULL, Holdings_ticker VARCHAR(20) NOT NULL, purchase_price DECIMAL(12, 2), holdings_amount INT, purchase_date DATE, purchase_cost DECIMAL(19, 4), PRIMARY KEY (Holding_strategy, Holdings_date, Holdings_ticker));
Query OK, 0 rows affected (0.10 sec)
Strategy Table
mysql> CREATE TABLE strategy ( Strategy_name VARCHAR(50) NOT NULL, Strategy_desc VARCHAR(50), Strategy_formula VARCHAR(255), PRIMARY KEY (Strategy_name));
Query OK, 0 rows affected (0.06 sec)
Transaction Table
mysql> CREATE TABLE transactions ( transaction_id INT AUTO_INCREMENT PRIMARY KEY, strategy VARCHAR(50), ticker VARCHAR(10), transaction_date DATE, transaction_type ENUM('BUY', 'SELL'), quantity DECIMAL(19, 4), price_per_share DECIMAL(19, 2), CONSTRAINT fk_transaction_strategy FOREIGN KEY (strategy) REFERENCES strategy(Strategy_name) ON UPDATE CASCADE ON DELETE CASCADE);
Query OK, 0 rows affected (0.07 sec)
We can enhance this transactions table by adding a Foreign Key constraint. This ensures that every transaction is tied to a valid strategy existing in your strategy table.
Foreign Key (FOREIGN KEY (strategy) REFERENCES strategy(Strategy_name)): Connects the transaction's strategy name directly to the primary key of your strategy table.
Cascading Updates/Deletes: ON UPDATE CASCADE and ON DELETE CASCADE ensure that if a strategy name changes or is deleted, your transaction records stay synchronized.
Daily Price Table
mysql> CREATE TABLE daily_price ( ticker VARCHAR(10) NOT NULL, date DATE NOT NULL, open DECIMAL(19, 4), high DECIMAL(19, 4), low DECIMAL(19, 4), close DECIMAL(19, 4), volume BIGINT, PRIMARY KEY (ticker, date));
Query OK, 0 rows affected (0.03 sec)
Daily Metrics Table
mysql> CREATE TABLE IF NOT EXISTS daily_metrics (
-> ticker VARCHAR(10) NOT NULL,
-> date DATE NOT NULL,
-> target_high_price DECIMAL(19, 4),
-> target_low_price DECIMAL(19, 4),
-> target_mean_price DECIMAL(19, 4),
-> target_median_price DECIMAL(19, 4),
-> current_price DECIMAL(19, 4),
-> recommendation_key VARCHAR(50),
-> recommendation_mean DECIMAL(10, 4),
-> number_of_analyst_opinions INT,
-> trailing_pe DECIMAL(10, 4),
-> forward_pe DECIMAL(10, 4),
-> price_to_book DECIMAL(10, 4),
-> peg_ratio DECIMAL(10, 4),
-> ebitda DECIMAL(24, 4),
-> revenue_growth DECIMAL(10, 4),
-> return_on_equity DECIMAL(10, 4),
-> fifty_two_week_high DECIMAL(19, 4),
-> fifty_two_week_low DECIMAL(19, 4),
-> fifty_day_average DECIMAL(19, 4),
-> two_hundred_day_average DECIMAL(19, 4),
-> beta DECIMAL(10, 4),
-> PRIMARY KEY (ticker, date)
-> );
Query OK, 0 rows affected (0.22 sec)
No comments:
Post a Comment