Create table and copy data from csv file

Setup mysql database

brew install mysql
brew services start mysql
brew services stop mysql
brew services list
mysql_secure_installation
mysql --version
mysql -u root -p

Put this into a file sudo nano /etc/my.cnf:

[client]
local_infile=1

[mysqld]
local_infile=1

brew services restart mysql

CREATE DATABASE database_name;

Setup postgres SQL database

Install Postgres via website or via brew:

brew install postgresql
brew services start postgresql
psql --version

Add psql to path to ~/.zshrc:

nano ~/.zshrc
export PATH="/Library/PostgreSQL/16/bin:$PATH"

Now login as postgres user:

psql -U postgres

Crate a new database:

CREATE DATABASE hello_world_db;

List all databases:

\l

Now one can use SQLTools extension in VSCode, and connect to database.

Constraint in SQL

Foreign key (FK) constraint can be added to a table, for example below, Orders table must have a CustomerID that matches Customer(CustomerID). This ensures data integrity, so we don’t have nonsense Orders that have no matching Customer.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    CustomerID INT,
    CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Exercises

hint: avg(Survived) is the same as (COUNT(CASE WHEN Survived = 1 THEN 1 END) * 1.0 / count(*)) since Survived is 0 or 1.

SELECT * FROM titanic LIMIT 10;
select (COUNT(CASE WHEN Survived = 1 THEN 1 END) * 1.0 / count(*)) as overall_rate FROM titanic;
select avg(Survived) as women_children_rate FROM titanic WHERE (Sex="female" OR Age<=12);
select avg(Survived) as others_rate FROM titanic WHERE NOT (Sex="female" OR Age<=12);

SELECT 
    Pclass, 
    AVG(Survived) AS survival_rate
FROM 
    titanic
GROUP BY 
    Pclass
ORDER BY 
    Pclass;

select
    Products.ProductName,
    Suppliers.CompanyName
from
    Products
left join
    Suppliers
on
    Products.SupplierID = Suppliers.SupplierID
order by
    Products.ProductName;