SQL Alfajores example

Running SQL in Jupyter

In [1]:
#!pip install ipython-sql
#!pip install pysqlite3

import sqlalchemy as alchemy
In [2]:
alchemy.create_engine('sqlite:///alfajores.db')
Out[2]:
Engine(sqlite:///alfajores.db)
In [3]:
%load_ext sql
In [4]:
%sql sqlite:///alfajores.db
Out[4]:
'Connected: @alfajores.db'

Creating tables

It is required to use '%%sql' in Jupyter to indicate that is a SQL cell (if the code is executed in SQL it is not required).
Comments in SQL are: / comment /. It can be used to avoid executing lines.

In [5]:
%%sql

/*DROP TABLE Movements;
DROP TABLE Master_Prod;
DROP TABLE Master_Prov;
DROP TABLE Master_Brand;
DROP TABLE Profit_by_dates;
DROP TABLE Profit_by_brand;*/

 * sqlite:///alfajores.db
0 rows affected.
Out[5]:
[]

Create table Master_Brand

In [6]:
%%sql

CREATE TABLE Master_Brand
(Cod_Brand integer PRIMARY KEY AUTOINCREMENT,
Description varchar(50));

 * sqlite:///alfajores.db
Done.
Out[6]:
[]
In [7]:
%%sql

INSERT INTO Master_Brand
(Description)
VALUES
('Terrabusi');

INSERT INTO Master_Brand
(Description)
VALUES
('Havanna');

INSERT INTO Master_Brand
(Description)
VALUES
('Balcarce');

INSERT INTO Master_Brand
(Description)
VALUES
('Guaymallen');

 * sqlite:///alfajores.db
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
Out[7]:
[]
In [8]:
%%sql

SELECT *
FROM Master_Brand;

 * sqlite:///alfajores.db
Done.
Out[8]:
Cod_Brand Description
1 Terrabusi
2 Havanna
3 Balcarce
4 Guaymallen

Create table Master_Prov

In [9]:
%%sql

CREATE TABLE Master_Prov
(Cod_Prov integer primary key AUTOINCREMENT,
Description varchar(50),
Adress varchar(50),
PhoneNumber integer);

 * sqlite:///alfajores.db
Done.
Out[9]:
[]
In [10]:
%%sql

INSERT INTO Master_Prov
(Description, Adress, PhoneNumber)
VALUES
('Pedrito', 'Av. del Maestro 123, CABA', 45657898);

INSERT INTO Master_Prov
(Description, Adress, PhoneNumber)
VALUES
('Juancito', 'Papudo 48, CABA', 26428486);

INSERT INTO Master_Prov
(Description, Adress, PhoneNumber)
VALUES
('Pocho', 'Jacob Aals 45, CABA', 15846235);

 * sqlite:///alfajores.db
1 rows affected.
1 rows affected.
1 rows affected.
Out[10]:
[]
In [11]:
%%sql

SELECT *
FROM Master_Prov;

 * sqlite:///alfajores.db
Done.
Out[11]:
Cod_Prov Description Adress PhoneNumber
1 Pedrito Av. del Maestro 123, CABA 45657898
2 Juancito Papudo 48, CABA 26428486
3 Pocho Jacob Aals 45, CABA 15846235

Create table Master_Prod

In [12]:
%%sql

CREATE TABLE Master_Prod
(Cod_Prod integer PRIMARY KEY AUTOINCREMENT,
Cod_Brand integer REFERENCES Master_Brand(Cod_Brand),
Cod_Prov integer REFERENCES Master_Prov(Cod_Prov),
Description varchar(50));

 * sqlite:///alfajores.db
Done.
Out[12]:
[]
In [13]:
%%sql

INSERT INTO Master_Prod
(Cod_Brand, Cod_Prov, Description)
VALUES
(1, 1, 'Fruta');

INSERT INTO Master_Prod
(Cod_Brand, Cod_Prov, Description)
VALUES
(1, 1, 'Dulce de leche');

INSERT INTO Master_Prod
(Cod_Brand, Cod_Prov, Description)
VALUES
(2, 2, 'Nuez');

INSERT INTO Master_Prod
(Cod_Brand, Cod_Prov, Description)
VALUES
(3, 1, 'Fruta');

INSERT INTO Master_Prod
(Cod_Brand, Cod_Prov, Description)
VALUES
(3, 1, 'Chocolate');

INSERT INTO Master_Prod
(Cod_Brand, Cod_Prov, Description)
VALUES
(4, 3, 'Fruta');

INSERT INTO Master_Prod
(Cod_Brand, Cod_Prov, Description)
VALUES
(4, 3, 'Dulce de Leche');

INSERT INTO Master_Prod
(Cod_Brand, Cod_Prov, Description)
VALUES
(4, 3, 'Chocolate');

INSERT INTO Master_Prod
(Cod_Brand, Cod_Prov, Description)
VALUES
(1, 1, 'Nuez');

INSERT INTO Master_Prod
(Cod_Brand, Cod_Prov, Description)
VALUES
(2, 3, 'Chocolate');

INSERT INTO Master_Prod
(Cod_Brand, Cod_Prov, Description)
VALUES
(3, 2, 'Dulce de leche');

INSERT INTO Master_Prod
(Cod_Brand, Cod_Prov, Description)
VALUES
(3, 2, 'Nuez');

 * sqlite:///alfajores.db
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
Out[13]:
[]
In [14]:
%%sql

SELECT *
FROM Master_Prod;

 * sqlite:///alfajores.db
Done.
Out[14]:
Cod_Prod Cod_Brand Cod_Prov Description
1 1 1 Fruta
2 1 1 Dulce de leche
3 2 2 Nuez
4 3 1 Fruta
5 3 1 Chocolate
6 4 3 Fruta
7 4 3 Dulce de Leche
8 4 3 Chocolate
9 1 1 Nuez
10 2 3 Chocolate
11 3 2 Dulce de leche
12 3 2 Nuez

Create table Movements

In [15]:
%%sql

CREATE TABLE Movements
(Cod_Mov integer PRIMARY KEY AUTOINCREMENT,
Cod_Prod integer REFERENCES Master_Prod(Cod_Prod),
Date date,
Quantity integer,
Cost float,
Price float);

 * sqlite:///alfajores.db
Done.
Out[15]:
[]
In [16]:
%%sql

INSERT INTO Movements
(Cod_Prod, Date, Quantity, Cost, Price)
VALUES
(1, '2019-01-11', 4, 25.11, 36.45);

INSERT INTO Movements
(Cod_Prod, Date, Quantity, Cost, Price)
VALUES
(2, '2019-01-13', 8, 27.11, 38.45);

INSERT INTO Movements
(Cod_Prod, Date, Quantity, Cost, Price)
VALUES
(3, '2019-01-13', 4, 24.11, 40.45);

INSERT INTO Movements
(Cod_Prod, Date, Quantity, Cost, Price)
VALUES
(4, '2019-01-15', 1, 28.11, 42.45);

INSERT INTO Movements
(Cod_Prod, Date, Quantity, Cost, Price)
VALUES
(5, '2019-01-17', 2, 28.11, 42.45);

INSERT INTO Movements
(Cod_Prod, Date, Quantity, Cost, Price)
VALUES
(6, '2019-01-17', 3, 22.11, 38.22);

INSERT INTO Movements
(Cod_Prod, Date, Quantity, Cost, Price)
VALUES
(2, '2019-02-19', 4, 27.11, 38.45);

INSERT INTO Movements
(Cod_Prod, Date, Quantity, Cost, Price)
VALUES
(8, '2019-02-21', 1, 33.12, 54.65);

INSERT INTO Movements
(Cod_Prod, Date, Quantity, Cost, Price)
VALUES
(3, '2019-03-01', 4, 24.11, 40.45);

INSERT INTO Movements
(Cod_Prod, Date, Quantity, Cost, Price)
VALUES
(8, '2019-03-11', 2, 33.12, 54.65);

INSERT INTO Movements
(Cod_Prod, Date, Quantity, Cost, Price)
VALUES
(1, '2019-04-01', 4, 25.11, 36.45);

INSERT INTO Movements
(Cod_Prod, Date, Quantity, Cost, Price)
VALUES
(6, '2019-04-05', 3, 22.11, 38.22);

 * sqlite:///alfajores.db
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
Out[16]:
[]
In [17]:
%%sql

SELECT *
FROM Movements;

 * sqlite:///alfajores.db
Done.
Out[17]:
Cod_Mov Cod_Prod Date Quantity Cost Price
1 1 2019-01-11 4 25.11 36.45
2 2 2019-01-13 8 27.11 38.45
3 3 2019-01-13 4 24.11 40.45
4 4 2019-01-15 1 28.11 42.45
5 5 2019-01-17 2 28.11 42.45
6 6 2019-01-17 3 22.11 38.22
7 2 2019-02-19 4 27.11 38.45
8 8 2019-02-21 1 33.12 54.65
9 3 2019-03-01 4 24.11 40.45
10 8 2019-03-11 2 33.12 54.65
11 1 2019-04-01 4 25.11 36.45
12 6 2019-04-05 3 22.11 38.22

Queries

  1. Total of Sales, Cost, and Profit.
In [18]:
%%sql

SELECT sum(Quantity * Price) TotalSales,
        sum(Quantity * Cost) TotalCost, 
        (sum(Quantity * Price) - sum(Quantity * Cost)) TotalProfit
FROM Movements

 * sqlite:///alfajores.db
Done.
Out[18]:
TotalSales TotalCost TotalProfit
1597.22 1035.43 561.79
  1. Total of Sales, Cost, and Profit, grouped by Date.
In [19]:
%%sql

SELECT Date, 
        round(sum(Quantity * Cost),2) TotalSales, 
        round(sum(Quantity * Price),2) TotalCost,
        round((sum(Quantity * Price) - sum(Quantity * Cost)),2) TotalProfit
FROM Movements
GROUP BY Date

 * sqlite:///alfajores.db
Done.
Out[19]:
Date TotalSales TotalCost TotalProfit
2019-01-11 100.44 145.8 45.36
2019-01-13 313.32 469.4 156.08
2019-01-15 28.11 42.45 14.34
2019-01-17 122.55 199.56 77.01
2019-02-19 108.44 153.8 45.36
2019-02-21 33.12 54.65 21.53
2019-03-01 96.44 161.8 65.36
2019-03-11 66.24 109.3 43.06
2019-04-01 100.44 145.8 45.36
2019-04-05 66.33 114.66 48.33
  1. Total of Sales, Cost, and Profit, grouped by Provider.
In [20]:
%%sql

SELECT PR.Cod_Prov,
        PR.Description,
        round(sum(Quantity * Cost),2) TotalSales, 
        round(sum(Quantity * Price),2) TotalCost,
        round((sum(Quantity * Price) - sum(Quantity * Cost)),2) TotalProfit
FROM Movements M
    INNER JOIN Master_Prod P ON M.Cod_Prod = P. Cod_Prod
    INNER JOIN Master_Prov PR ON P.Cod_Prov = PR.Cod_Prov
GROUP BY PR.Cod_Prov

 * sqlite:///alfajores.db
Done.
Out[20]:
Cod_Prov Description TotalSales TotalCost TotalProfit
1 Pedrito 610.53 880.35 269.82
2 Juancito 192.88 323.6 130.72
3 Pocho 232.02 393.27 161.25
  1. Average Price per Brand.
In [21]:
%%sql

SELECT Ma.Description, round(AVG(Price),2) AveragePrice
FROM Movements M
    INNER JOIN Master_Prod P ON M.Cod_Prod = P.Cod_Prod
    INNER JOIN Master_Brand Ma ON P.Cod_Brand = Ma.Cod_Brand
GROUP BY Ma.Description

 * sqlite:///alfajores.db
Done.
Out[21]:
Description AveragePrice
Balcarce 42.45
Guaymallen 46.44
Havanna 40.45
Terrabusi 37.45
  1. Average Price per Product.
In [22]:
%%sql

SELECT P.Description, round(AVG(Price),2) AveragePrice
FROM Movements M
    INNER JOIN Master_Prod P ON M.Cod_Prod = P.Cod_Prod
    INNER JOIN Master_Brand B ON P.Cod_Brand = B.Cod_Brand
GROUP BY P.Description

 * sqlite:///alfajores.db
Done.
Out[22]:
Description AveragePrice
Chocolate 50.58
Dulce de leche 38.45
Fruta 38.36
Nuez 40.45
  1. Generate a list of all movements with the following content:
    . Date (Order by this field)
    . Product description
    . Brand Description
    . Provider Description
    . Quantity
    . Cost
    . Sales
    . Profit
In [23]:
%%sql

SELECT M.Date, P.Description Product, Ma.Description Brand, 
        Pr.Description Provider, M.Quantity, 
        (Quantity*M.Cost) as Cost, 
        (Quantity* M.Price) as Sales, 
        round((Quantity *M.Price-Quantity*M.Cost),2) as Profit
FROM Movements M
    INNER JOIN Master_Prod P ON M.Cod_Prod = P.Cod_Prod
    INNER JOIN Master_Brand Ma ON P.Cod_Brand = Ma.Cod_Brand
    INNER JOIN Master_Prov Pr ON P.Cod_Prov = Pr.Cod_Prov
ORDER BY M.Date

 * sqlite:///alfajores.db
Done.
Out[23]:
Date Product Brand Provider Quantity Cost Sales Profit
2019-01-11 Fruta Terrabusi Pedrito 4 100.44 145.8 45.36
2019-01-13 Dulce de leche Terrabusi Pedrito 8 216.88 307.6 90.72
2019-01-13 Nuez Havanna Juancito 4 96.44 161.8 65.36
2019-01-15 Fruta Balcarce Pedrito 1 28.11 42.45 14.34
2019-01-17 Chocolate Balcarce Pedrito 2 56.22 84.9 28.68
2019-01-17 Fruta Guaymallen Pocho 3 66.33 114.66 48.33
2019-02-19 Dulce de leche Terrabusi Pedrito 4 108.44 153.8 45.36
2019-02-21 Chocolate Guaymallen Pocho 1 33.12 54.65 21.53
2019-03-01 Nuez Havanna Juancito 4 96.44 161.8 65.36
2019-03-11 Chocolate Guaymallen Pocho 2 66.24 109.3 43.06
2019-04-01 Fruta Terrabusi Pedrito 4 100.44 145.8 45.36
2019-04-05 Fruta Guaymallen Pocho 3 66.33 114.66 48.33
  1. Create a table with the Profit grouped by Dates.
In [24]:
%%sql

CREATE TABLE Profit_by_dates
(Date date,
Profit integer);

 * sqlite:///alfajores.db
Done.
Out[24]:
[]
In [25]:
%%sql

INSERT INTO Profit_by_dates
(Date, Profit)
SELECT Date, round((Quantity*Price-Quantity*Cost),2)
FROM Movements
GROUP BY Date
ORDER BY Date ASC;

select *
from Profit_by_dates;

 * sqlite:///alfajores.db
10 rows affected.
Done.
Out[25]:
Date Profit
2019-01-11 45.36
2019-01-13 90.72
2019-01-15 14.34
2019-01-17 28.68
2019-02-19 45.36
2019-02-21 21.53
2019-03-01 65.36
2019-03-11 43.06
2019-04-01 45.36
2019-04-05 48.33
  1. Create a table with the Profit grouped by Dates.
In [26]:
%%sql

CREATE TABLE Profit_by_brand
(Cod_Brand integer,
 Brand varchar(50),
Profit integer);

 * sqlite:///alfajores.db
Done.
Out[26]:
[]
In [27]:
%%sql

INSERT INTO Profit_by_brand
(Cod_brand, Brand, Profit)
SELECT B.Cod_Brand, B.Description, ROUND(SUM(Quantity*Price-Quantity*Cost),2)
FROM Movements M
INNER JOIN Master_Prod P ON M.Cod_Prod = P.Cod_Prod
    INNER JOIN Master_Brand B ON P.Cod_Brand = B.Cod_Brand
GROUP BY B.Cod_Brand;

select *
from Profit_by_brand;

 * sqlite:///alfajores.db
4 rows affected.
Done.
Out[27]:
Cod_Brand Brand Profit
1 Terrabusi 226.8
2 Havanna 130.72
3 Balcarce 43.02
4 Guaymallen 161.25
  1. Asign Cod_Prod = 3 to all the registers in Movements.
In [28]:
%%sql

UPDATE Movements
SET Cod_Prod = 3;

SELECT *
FROM Movements;

 * sqlite:///alfajores.db
12 rows affected.
Done.
Out[28]:
Cod_Mov Cod_Prod Date Quantity Cost Price
1 3 2019-01-11 4 25.11 36.45
2 3 2019-01-13 8 27.11 38.45
3 3 2019-01-13 4 24.11 40.45
4 3 2019-01-15 1 28.11 42.45
5 3 2019-01-17 2 28.11 42.45
6 3 2019-01-17 3 22.11 38.22
7 3 2019-02-19 4 27.11 38.45
8 3 2019-02-21 1 33.12 54.65
9 3 2019-03-01 4 24.11 40.45
10 3 2019-03-11 2 33.12 54.65
11 3 2019-04-01 4 25.11 36.45
12 3 2019-04-05 3 22.11 38.22
  1. Asign (Cod_Prov = 3) to all the registers in Movements where Product Description starts with letter 'D'.
In [29]:
%%sql

UPDATE Master_Prod
SET Cod_Prov = 3
WHERE Cod_Prod IN (SELECT Cod_Prod
                    FROM Master_Prod 
                   WHERE Description LIKE 'D%');

SELECT *
FROM Master_Prod
WHERE Description LIKE 'D%';

 * sqlite:///alfajores.db
3 rows affected.
Done.
Out[29]:
Cod_Prod Cod_Brand Cod_Prov Description
2 1 3 Dulce de leche
7 4 3 Dulce de Leche
11 3 3 Dulce de leche