Creating Tables

13.1. Creating Tables#

13.1.1. Basic#

A database is made up of tables. Tables are made up of columns and rows. A database can be considered like an Excel file and the sheets in your Excel file are like tables. The power of database comes from joining different tables based on shared column information/name between tables and filtering and aggregating rows. SQL is the language that allows you to create, update, delete, filter, and aggregate tables. Often the first step in data wrangling is to extract the data you need from an SQL table. Sometimes you might even have to parse data and populate into a database before you can start using it.

13.1.2. SQLite3#

SQLite3 supports three primitive data types: INTEGER, REAL, TEXT, and BLOB.

Warning

SQLite3 is dynamically typed. This means that even if you specify a column to be data type real or integer, you can insert a string type into it. If the string type can be cast into a numeric type, it will be, otherwise it will be left as is. If you apply a sum or other math function to a column with mixed data types, only the numerical values will be used.

13.1.3. Creating a table#

Tables are created using the CREATE TABLE <name_of_table> directive followed by a pair of parenthesis inside which is a definition of each of the columns, meaning column names, their respective data type, and if they allow NULL values or not, default is to allow NULL values, and if they are a PRIMARY KEY. In addition, create statements can link a table column to another column using the REFERENCES keyword.

13.1.3.1. A simple table#

The following SQL command creates a students table with six columns

CREATE TABLE Students (
    last_name TEXT,
    first_name TEXT,
    username TEXT,
    exam1 REAL,
    exam2 REAL,
    exam3 REAL
);

13.1.3.2. Inserting some values into a table#

You can insert values into a table using the INSERT INTO <name_of_table> (<name_of_column_1>, <name_of_column_2>, ....) VALUES (<value_of_column_1>, <value_of_column_1>, ...); The following SQL statements insert some values into a table with some variations.

13.1.3.2.1. Valid Insert statements#

INSERT INTO Students (last_name, first_name, username, exam1, exam2, exam3) VALUES ('Doe', 'John', 'johndoe', 98, 76, 89);
INSERT INTO Students (first_name, last_name, username, exam1, exam2, exam3) VALUES ('Emily', 'Shepard', 'eshepard', 88, 96, 90);
INSERT INTO Students VALUES ('Doe', 'Jane', 'janedoe', 89, 64, 39);
INSERT INTO Students (last_name, first_name, username) VALUES ('Smith', 'John', 'johnsmith');
INSERT INTO Students (exam1, exam2, exam3) VALUES (78, 99, 83);
INSERT INTO Students (exam1, exam2, exam3) VALUES ('25', '33', '45');
INSERT INTO Students  VALUES (56, 32, 33, 'Eaglestone', 'Ken', 'keagle');

In the first insert statement all columns names and their corresponding values are supplied in the order of create table statement.
In the second insert statement, the order of the columns names are changed (first and last name), but SQLite3 will put them in the correct column.
In the third insert statement, if all the number of values supplied correspond to the number of rows, then SQLite3 will insert them into the table in the order of the create statement.
In the fourth insert statement, it is possible to only supply some values since by default all columns allow null values.
In the fifth insert statement, only the names exams values a provided. This is allowed because by default all columns allow null values, but his is poor database design.
In the sixth insert statement, only exams values are provided, but they are type string. SQLite3 will convert them to REAL before inserting them into the table.
In the seventh insert statement, all the values are inserted into the wrong column, which is allowed by SQLite3 since it is dynamically types, so be very careful!

13.1.3.2.2. Invalid Insert Statement#

INSERT INTO Students  VALUES ('Smith', 'John', 'johnsmith');

Execution finished with errors.
Result: table Students has 6 columns but 3 values were supplied
At line 1:
INSERT INTO Students  VALUES ('Smith', 'Jake', 'johnsmith');

If fewer values are provided without explicitly mentioning the column names, SQLite3 will raise an error.

13.1.3.3. Adding `NOT NULL` constraint#

In the previous table we highlighted a poor database design where you could insert exams scores without a student. We fix this by making the student fields NOT NULL, meaning they have to be specified.

Note you can drop a table using DROP TABLE <name_of_table>.

DROP TABLE Students;
CREATE TABLE Students (
    last_name TEXT NOT NULL,
    first_name TEXT NOT NULL,
    username TEXT NOT NULL,
    exam1 REAL,
    exam2 REAL,
    exam3 REAL
);

Note that the two separate SQL statements are separated by ;.

If we know attempt to enter a student exam score without the name fields, SQLite3 will raise an error.

INSERT INTO Students('exam1') VALUES ('52');

Execution finished with errors.
Result: NOT NULL constraint failed: Students.last_name
At line 10:
INSERT INTO Students('exam1') VALUES ('52');

13.1.4. Primary Keys#

When you create any table, SQLite3 adds a hidden column called rowid.

DROP TABLE IF EXISTS Teachers;
CREATE TABLE Teachers(
  TeacherName  TEXT NOT NULL
);

INSERT INTO Teachers ('TeacherName') VALUES ('John Smith');
INSERT INTO Teachers ('TeacherName') VALUES ('John Smith');
SELECT rowid, * FROM Teachers;

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[1], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('primary_key_example.db')
      6 sql_statements = """
      7 DROP TABLE IF EXISTS Teachers;
      8 CREATE TABLE Teachers(
   (...)     14 SELECT rowid, * FROM Teachers;
     15 """

ModuleNotFoundError: No module named 'pandas'

Note: DROP TABLE IF EXISTS Teachers; is used to drop the table if it exists. This is useful if you want to run the create statement over and over again with changes.

When you create a table that has an INTEGER NOT NULL PRIMARY KEY column, this column is the alias of the rowid column.

Note

It uniquely identifies a row in a table. Primary keys have the following properties:

Primary keys can consist of one or more columns, meaning multiple columns combined can uniquely define a row.
Primary keys cannot be duplicated since they uniquely identify a row in a table.
Primary keys cannot be NULL since a blank value cannot identify a row in a table.
There can only be one primary key per table. Note that this is different from a primary key being made up of multiple tables.
Primary keys are indexed automatically.
INTEGER primary keys are auto-increment fields starting at 1.

DROP TABLE Teachers;
CREATE TABLE Teachers (
   TeacherId INTEGER NOT NULL PRIMARY KEY,
   TeacherName  TEXT NOT NULL
);
INSERT INTO Teachers ('TeacherName') VALUES ('John Smith');
INSERT INTO Teachers ('TeacherName') VALUES ('John Smith');
SELECT * FROM Teachers;

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[2], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('primary_key_example.db')
      6 sql_statements = """
      7 DROP TABLE Teachers;
      8 CREATE TABLE Teachers (
   (...)     14 SELECT * FROM Teachers;
     15 """

ModuleNotFoundError: No module named 'pandas'

13.1.5. Adding a unique constraint#

In the previous examples teachers table, it is possible to add multiple John Smiths. How can you identify one John Smith from another John Smith. One way would be to assign teachers unique IDs that cannot be reused. A column can be made unique by using the UNIQUE keyword.

DROP TABLE Teachers;
CREATE TABLE Teachers (
   TeacherId INTEGER NOT NULL PRIMARY KEY,
   TeacherName  TEXT NOT NULL,
   TeacherEmployeeID INTEGER NOT NULL,
   UNIQUE (TeacherEmployeeID)
);
INSERT INTO Teachers ('TeacherName', 'TeacherEmployeeID') VALUES ('John Smith', 100001);
INSERT INTO Teachers ('TeacherName', 'TeacherEmployeeID') VALUES ('John Smith', 100002);
SELECT * FROM Teachers;

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[3], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('primary_key_example.db')
      6 sql_statements = """
      7 DROP TABLE Teachers;
      8 CREATE TABLE Teachers (
   (...)     15 INSERT INTO Teachers ('TeacherName', 'TeacherEmployeeID') VALUES ('John Smith', 100002);
     16 """

ModuleNotFoundError: No module named 'pandas'

13.1.6. Foreign Keys#

What is a foreign key? In a relational database, you can relate one table to another table. The two tables can be related if and only if both tables have one column in common. This column has to be declared as a INTEGER/TEXT data type that cannot be NULL and has the PRIMARY KEY constraint, e.g., <name_of_column> INTEGER NOT NULL PRIMARY KEY.It seems INTEGER foreign keys are preferred over TEXT ones. See the following references:

Warning

Foreign key constraint are NOT enabled by default in SQLite. Execute PRAGMA foreign_keys = ON; to enable foreign key constraint.

13.1.6.1. Creating a relationship#

PRAGMA foreign_keys = ON;
DROP TABLE IF EXISTS  Courses;
DROP TABLE IF EXISTS Teachers;
CREATE TABLE Teachers (
   TeacherId INTEGER NOT NULL PRIMARY KEY,
   TeacherName  TEXT NOT NULL,
   TeacherEmployeeID INTEGER NOT NULL,
   UNIQUE (TeacherEmployeeID)
);

CREATE TABLE Courses(
  CourseId     INTEGER NOT NULL PRIMARY KEY,
  CourseName   TEXT NOT NULL,
  CourseShortID   TEXT NOT NULL,
  TeacherId INTEGER NOT NULL,
  FOREIGN KEY(TeacherId) REFERENCES Teachers(TeacherId),
  UNIQUE (CourseName, CourseShortID)
);

INSERT INTO Teachers (TeacherName, TeacherEmployeeID)
VALUES ('Melissa Larson', 10001);

INSERT INTO Teachers (TeacherName,TeacherEmployeeID)
VALUES ('Christopher Smith', 10002);

INSERT INTO Courses (CourseName, CourseShortID, TeacherId)
VALUES ('Introduction to Python', 'EAS503', 1);

INSERT INTO Courses (CourseName, CourseShortID, TeacherId)
VALUES ('Introduction to Probability', 'EAS506', 2);

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[4], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('teachers_courses.db')
      6 sql_statements = """
      7 PRAGMA foreign_keys = ON;
      8 DROP TABLE IF EXISTS  Courses;
   (...)     36 VALUES ('Introduction to Probability', 'EAS506', 2);
     37 """

ModuleNotFoundError: No module named 'pandas'

13.1.6.2. Using the relationship#

Method 1 – explicitly link two tables

Note this method will result in duplicated TeacherID fields if you select all the columns using ‘*’.

SELECT
    *
FROM
    Teachers t
INNER JOIN
    Courses c ON t.TeacherId = c.TeacherId;

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[5], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('teachers_courses.db')
      6 sql_statement = """
      7 SELECT
      8     *
   (...)     12     Courses c ON t.TeacherId = c.TeacherId;
     13 """

ModuleNotFoundError: No module named 'pandas'

SELECT
    t.TeacherName,
    c.CourseName,
    c.CourseShortID
FROM
    Teachers t
INNER JOIN
    Courses c ON t.TeacherId = c.TeacherId;

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[6], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('teachers_courses.db')
      6 sql_statement = """
      7 SELECT
      8     t.TeacherName,
   (...)     14     Courses c ON t.TeacherId = c.TeacherId;
     15 """

ModuleNotFoundError: No module named 'pandas'

Method 2 – let SQLite infers join condition – but you specify the shared column name

Note this method will NOT result in duplicated TeacherID fields if you select all the columns using ‘*’.

SELECT
    *
FROM
    Teachers t
INNER JOIN Courses c USING(TeacherId);

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[7], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('teachers_courses.db')
      6 sql_statement = """
      7 SELECT
      8     *
   (...)     11 INNER JOIN Courses c USING(TeacherId);
     12 """

ModuleNotFoundError: No module named 'pandas'

SELECT
    t.TeacherName,
    c.CourseName,
    c.CourseShortID
FROM
    Teachers t
INNER JOIN Courses c USING(TeacherId);

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[8], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('teachers_courses.db')
      6 sql_statement = """
      7 SELECT
      8     t.TeacherName,
   (...)     13 INNER JOIN Courses c USING(TeacherId);
     14 """

ModuleNotFoundError: No module named 'pandas'

13.1.7. Joining Tables#

There are two types of joins

Inner Join
Outer Join - Left Outer Join (or Left Join) - Right Outer Join (or Right Join) - Full Outer Join (or Full Join) Example database: join_example_database.db - Ref: https://www.diffen.com/difference/Inner_Join_vs_Outer_Join

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[9], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('join_example_database.db')
      6 sql_statement = """
      7 SELECT
      8     *
      9 FROM Prices
     10 """

ModuleNotFoundError: No module named 'pandas'

13.1.7.1. Inner Join#

SELECT
    Prices.Product,
    Prices.Price,
    Quantities.Quantity
FROM Prices
INNER JOIN Quantities ON Prices.Product = Quantities.Product;

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[10], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('join_example_database.db')
      6 sql_statement = """
      7 SELECT
      8     Prices.Product,
   (...)     12 INNER JOIN Quantities ON Prices.Product = Quantities.Product;
     13 """

ModuleNotFoundError: No module named 'pandas'

13.1.7.2. Left Join#

SELECT
    Prices.Product,
    Prices.Price,
    Quantities.Quantity
FROM
    Prices
LEFT OUTER JOIN Quantities ON Prices.Product = Quantities.Product;

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[11], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('join_example_database.db')
      6 sql_statement = """
      7 SELECT
      8     Prices.Product,
   (...)     13 LEFT OUTER JOIN Quantities ON Prices.Product = Quantities.Product;
     14 """

ModuleNotFoundError: No module named 'pandas'

13.1.7.3. Right Join – Doesn’t work in SQLITE3#

SELECT
    Prices.Product,
    Prices.Price,
    Quantities.Quantity
FROM
    Prices
RIGHT OUTER JOIN Quantities ON Prices.Product = Quantities.Product;

Execution finished with errors.
Result: RIGHT and FULL OUTER JOINs are not currently supported
At line 1:
SELECT
    Prices.Product,
    Prices.Price,
    Quantities.Quantity
FROM
    Prices
RIGHT OUTER JOIN Quantities

13.1.7.4. Right Join by doing Left Join#

Change the order of the tables to emulate right join!

SELECT
    Quantities.Product,
    Prices.Price,
    Quantities.Quantity
FROM
    Quantities
LEFT OUTER JOIN Prices ON Quantities.Product = Prices.Product;

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[12], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('join_example_database.db')
      6 sql_statement = """
      7 SELECT
      8     Quantities.Product,
   (...)     13 LEFT OUTER JOIN Prices ON Quantities.Product = Prices.Product;
     14 """

ModuleNotFoundError: No module named 'pandas'

13.1.7.5. Full Outer join – does not work in SQLite3#

SELECT Prices.Product, Prices.Price, Quantities.Quantity
FROM Prices FULL OUTER JOIN Quantities
ON Prices.Product = Quantities.Product;

Execution finished with errors.
Result: RIGHT and FULL OUTER JOINs are not currently supported
At line 1:
SELECT Prices.Product, Prices.Price, Quantities.Quantity
FROM Prices FULL OUTER JOIN Quantities

13.1.7.6. Emulate Full outer join#

You can use unions to emulate full outer join Ref: https://www.sqlitetutorial.net/sqlite-full-outer-join/

SELECT Prices.Product, Prices.Price,  Quantities.Product, Quantities.Quantity
FROM Prices
LEFT OUTER JOIN Quantities
ON Prices.Product = Quantities.Product
UNION ALL
SELECT Prices.Product, Prices.Price, Quantities.Product, Quantities.Quantity
FROM Quantities
LEFT OUTER JOIN Prices
ON Quantities.Product = Prices.Product;

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[13], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('join_example_database.db')
      6 sql_statement = """
      7 SELECT Prices.Product, Prices.Price,  Quantities.Product, Quantities.Quantity
      8 FROM Prices
   (...)     15 ON Quantities.Product = Prices.Product;
     16 """

ModuleNotFoundError: No module named 'pandas'

13.1.8. Joining Multiple Tables#

PRAGMA foreign_keys = ON;
DROP TABLE IF EXISTS Cars;
DROP TABLE IF EXISTS MakeModels;
DROP TABLE IF EXISTS Colors;


CREATE TABLE Colors (
   color_id INTEGER NOT NULL PRIMARY KEY,
   color  TEXT NOT NULL,
   UNIQUE (color)
);

CREATE TABLE MakeModels (
  make_model_id     INTEGER NOT NULL PRIMARY KEY,
  Make   TEXT NOT NULL,
  Model   TEXT NOT NULL,
  Year INTEGER NOT NULL,
  UNIQUE (Make, Model, Year)
);

CREATE TABLE Cars (
   car_id INTEGER NOT NULL PRIMARY KEY,
   make_model_id INTEGER NOT NULL,
   color_id INTEGER NOT NULL,
   available INTEGER NOT NULL,
   FOREIGN KEY(make_model_id) REFERENCES MakeModels(make_model_id),
   FOREIGN KEY(color_id) REFERENCES Colors(color_id)
);

INSERT INTO Colors (color) VALUES ('Red');
INSERT INTO Colors (color) VALUES ('Blue');
INSERT INTO Colors (color) VALUES ('Green');

INSERT INTO MakeModels (Make, Model, Year) VALUES ('Ford', 'Explorer', 2019);
INSERT INTO MakeModels (Make, Model, Year) VALUES ('Toyota', 'Camry', 2010);
INSERT INTO MakeModels (Make, Model, Year) VALUES ('Honda', 'Accord', 2015);

INSERT INTO Cars (make_model_id, color_id, available) VALUES (1, 1, 1);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (2, 1, 1);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (3, 1, 1);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (1, 2, 0);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (1, 2, 0);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (3, 3, 0);

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[14], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('cars.db')
      6 sql_statements = """
      7 PRAGMA foreign_keys = ON;
      8 DROP TABLE IF EXISTS Cars;
   (...)     48 INSERT INTO Cars (make_model_id, color_id, available) VALUES (3, 3, 0);
     49 """

ModuleNotFoundError: No module named 'pandas'

SELECT * FROM Colors;

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[15], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('cars.db')
      6 sql_statement = """
      7 SELECT * FROM Colors;
      8 """

ModuleNotFoundError: No module named 'pandas'

SELECT * FROM MakeModels;

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[16], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('cars.db')
      6 sql_statement = """
      7 SELECT * FROM MakeModels;
      8 """

ModuleNotFoundError: No module named 'pandas'

SELECT * FROM Cars;

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[17], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('cars.db')
      6 sql_statement = """
      7 SELECT * FROM Cars;
      8 """

ModuleNotFoundError: No module named 'pandas'

SELECT Cars.car_id, MakeModels.Make, MakeModels.model, MakeModels.year, Colors.color, Cars.available
FROM Cars
    INNER JOIN MakeModels ON MakeModels.make_model_id = Cars.make_model_id
    INNER JOIN Colors ON Colors.color_id = Cars.color_id;

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[18], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('cars.db')
      6 sql_statement = """
      7 SELECT Cars.car_id, MakeModels.Make, MakeModels.model, MakeModels.year, Colors.color, Cars.available
      8 FROM Cars
      9     INNER JOIN MakeModels ON MakeModels.make_model_id = Cars.make_model_id
     10     INNER JOIN Colors ON Colors.color_id = Cars.color_id;
     11 """

ModuleNotFoundError: No module named 'pandas'

13.1.9. Updating Tables#

The following tables have typos in them!

PRAGMA foreign_keys = ON;
DROP TABLE IF EXISTS Cars;
DROP TABLE IF EXISTS MakeModels;
DROP TABLE IF EXISTS Colors;


CREATE TABLE Colors (
   color_id INTEGER NOT NULL PRIMARY KEY,
   color  TEXT NOT NULL,
   UNIQUE (color)
);

CREATE TABLE MakeModels (
  make_model_id     INTEGER NOT NULL PRIMARY KEY,
  Make   TEXT NOT NULL,
  Model   TEXT NOT NULL,
  Year INTEGER NOT NULL,
  UNIQUE (Make, Model, Year)
);

CREATE TABLE Cars (
   car_id INTEGER NOT NULL PRIMARY KEY,
   make_model_id INTEGER NOT NULL,
   color_id INTEGER NOT NULL,
   available INTEGER NOT NULL,
   FOREIGN KEY(make_model_id) REFERENCES MakeModels(make_model_id),
   FOREIGN KEY(color_id) REFERENCES Colors(color_id)
);

INSERT INTO Colors (color) VALUES ('Red');
INSERT INTO Colors (color) VALUES ('Blue');
INSERT INTO Colors (color) VALUES ('Greene'); -- TYPO!!!

INSERT INTO MakeModels (Make, Model, Year) VALUES ('Frd', 'Explorer', 2019); -- TYPO!!!
INSERT INTO MakeModels (Make, Model, Year) VALUES ('Toyota', 'Camrey', 2010); -- TYPO!!!
INSERT INTO MakeModels (Make, Model, Year) VALUES ('Honda', 'Accord', 2015);

INSERT INTO Cars (make_model_id, color_id, available) VALUES (1, 1, 1);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (2, 1, 1);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (3, 1, 1);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (1, 2, 0);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (1, 2, 0);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (3, 3, 0);

Show code cell source Hide code cell source

import sqlite3
import pandas as pd

conn = sqlite3.connect('cars.db')

sql_statements = """
PRAGMA foreign_keys = ON;
DROP TABLE IF EXISTS Cars;
DROP TABLE IF EXISTS MakeModels;
DROP TABLE IF EXISTS Colors;

CREATE TABLE Colors (
   color_id INTEGER NOT NULL PRIMARY KEY,
   color  TEXT NOT NULL,
   UNIQUE (color)
);

CREATE TABLE MakeModels (
  make_model_id     INTEGER NOT NULL PRIMARY KEY,
  Make   TEXT NOT NULL,
  Model   TEXT NOT NULL,
  Year INTEGER NOT NULL,
  UNIQUE (Make, Model, Year)
);

CREATE TABLE Cars (
   car_id INTEGER NOT NULL PRIMARY KEY,
   make_model_id INTEGER NOT NULL,
   color_id INTEGER NOT NULL,
   available INTEGER NOT NULL,
   FOREIGN KEY(make_model_id) REFERENCES MakeModels(make_model_id),
   FOREIGN KEY(color_id) REFERENCES Colors(color_id)
);

INSERT INTO Colors (color) VALUES ('Red');
INSERT INTO Colors (color) VALUES ('Blue');
INSERT INTO Colors (color) VALUES ('Greene'); -- TYPO!!!

INSERT INTO MakeModels (Make, Model, Year) VALUES ('Frd', 'Explorer', 2019); -- TYPO!!!
INSERT INTO MakeModels (Make, Model, Year) VALUES ('Toyota', 'Camrey', 2010); -- TYPO!!!
INSERT INTO MakeModels (Make, Model, Year) VALUES ('Honda', 'Accord', 2015);

INSERT INTO Cars (make_model_id, color_id, available) VALUES (1, 1, 1);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (2, 1, 1);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (3, 1, 1);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (1, 2, 0);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (1, 2, 0);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (3, 3, 0);
"""

with conn:
    cur = conn.cursor()
    cur.executescript(sql_statements)

sql_statement = """
SELECT Cars.car_id, MakeModels.Make, MakeModels.Model, MakeModels.year, Colors.color, Cars.available
FROM Cars
    INNER JOIN MakeModels ON MakeModels.make_model_id = Cars.make_model_id
    INNER JOIN Colors ON Colors.color_id = Cars.color_id;
"""
df = pd.read_sql_query(sql_statement, conn)
df.style.set_table_attributes('style="font-size: 12px"')

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[19], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('cars.db')
      6 sql_statements = """
      7 PRAGMA foreign_keys = ON;
      8 DROP TABLE IF EXISTS Cars;
   (...)     48 INSERT INTO Cars (make_model_id, color_id, available) VALUES (3, 3, 0);
     49 """

ModuleNotFoundError: No module named 'pandas'

13.1.9.1. Update Color#

UPDATE Colors
SET color = 'Green'
WHERE color = "Greene";

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[20], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('cars.db')
      6 sql_statement = """
      7 UPDATE Colors
      8 SET color = 'Green'
      9 WHERE color = "Greene";
     10 """

ModuleNotFoundError: No module named 'pandas'

SELECT Cars.car_id, MakeModels.Make, MakeModels.Model, MakeModels.year, Colors.color, Cars.available
FROM Cars
    INNER JOIN MakeModels ON MakeModels.make_model_id = Cars.make_model_id
    INNER JOIN Colors ON Colors.color_id = Cars.color_id;

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[21], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('cars.db')
      6 sql_statement = """
      7 SELECT Cars.car_id, MakeModels.Make, MakeModels.Model, MakeModels.year, Colors.color, Cars.available
      8 FROM Cars
      9     INNER JOIN MakeModels ON MakeModels.make_model_id = Cars.make_model_id
     10     INNER JOIN Colors ON Colors.color_id = Cars.color_id;
     11 """

ModuleNotFoundError: No module named 'pandas'

13.1.9.2. Update MakeModel#

UPDATE MakeModels
SET Make = 'Ford'
WHERE Make = "Frd";

UPDATE MakeModels
SET Model = 'Camry'
WHERE Model = "Camrey";

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[22], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('cars.db')
      6 sql_statements = """
      7 UPDATE MakeModels
      8 SET Make = 'Ford'
   (...)     13 WHERE Model = "Camrey";
     14 """

ModuleNotFoundError: No module named 'pandas'

SELECT Cars.car_id, MakeModels.Make, MakeModels.Model, MakeModels.year, Colors.color, Cars.available
FROM Cars
    INNER JOIN MakeModels ON MakeModels.make_model_id = Cars.make_model_id
    INNER JOIN Colors ON Colors.color_id = Cars.color_id;

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[23], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('cars.db')
      6 sql_statement = """
      7 SELECT Cars.car_id, MakeModels.Make, MakeModels.Model, MakeModels.year, Colors.color, Cars.available
      8 FROM Cars
      9     INNER JOIN MakeModels ON MakeModels.make_model_id = Cars.make_model_id
     10     INNER JOIN Colors ON Colors.color_id = Cars.color_id;
     11 """

ModuleNotFoundError: No module named 'pandas'

13.1.9.3. Update Car#

UPDATE Cars
SET available = 1
WHERE car_id = 6;

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[24], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('cars.db')
      6 sql_statement = """
      7 UPDATE Cars
      8 SET available = 1
      9 WHERE car_id = 6;
     10 """

ModuleNotFoundError: No module named 'pandas'

SELECT Cars.car_id, MakeModels.Make, MakeModels.Model, MakeModels.year, Colors.color, Cars.available
FROM Cars
    INNER JOIN MakeModels ON MakeModels.make_model_id = Cars.make_model_id
    INNER JOIN Colors ON Colors.color_id = Cars.color_id;

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[25], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('cars.db')
      6 sql_statement = """
      7 SELECT Cars.car_id, MakeModels.Make, MakeModels.Model, MakeModels.year, Colors.color, Cars.available
      8 FROM Cars
      9     INNER JOIN MakeModels ON MakeModels.make_model_id = Cars.make_model_id
     10     INNER JOIN Colors ON Colors.color_id = Cars.color_id;
     11 """

ModuleNotFoundError: No module named 'pandas'

13.1.10. Simple Delete#

You can easily delete a non-referenced row.

DELETE FROM Cars
WHERE available = 0;

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[26], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('cars.db')
      6 sql_statement = """
      7 DELETE FROM Cars
      8 WHERE available = 0;
      9 """

ModuleNotFoundError: No module named 'pandas'

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[27], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('cars.db')
      6 sql_statement = """
      7 SELECT Cars.car_id, MakeModels.Make, MakeModels.Model, MakeModels.year, Colors.color, Cars.available
      8 FROM Cars
      9     INNER JOIN MakeModels ON MakeModels.make_model_id = Cars.make_model_id
     10     INNER JOIN Colors ON Colors.color_id = Cars.color_id;
     11 """

ModuleNotFoundError: No module named 'pandas'

13.1.11. Deleting a referenced row#

A referenced row can be deleted or not depending on how the table was defined. By default, a referenced row cannot be deleted. This default behavior can be changed. You can either set the foreign key to ON DELETE SET NULL or ON DELETE SET CASCADE. In the former case, the references to the row being deleted is set to NULL. In the latter case, the anything that references the row being deleted is also deleted.

DELETE FROM Colors
WHERE color = "Red";

Execution finished with errors.
Result: FOREIGN KEY constraint failed
At line 1:
DELETE FROM Colors
WHERE color = "Red";

DELETE FROM Cars
WHERE car_id IN (SELECT car_id from Cars INNER JOIN Colors clr USING(color_id ) WHERE clr.color='Red');
DELETE FROM Colors
WHERE color = "Red";

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[28], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('cars.db')
      6 sql_statements = """
      7 DELETE FROM Cars
      8 WHERE car_id IN (SELECT car_id from Cars INNER JOIN Colors clr USING(color_id ) WHERE clr.color='Red');
      9 DELETE FROM Colors
     10 WHERE color = "Red";
     11 """

ModuleNotFoundError: No module named 'pandas'

SELECT Cars.car_id, MakeModels.Make, MakeModels.Model, MakeModels.year, Colors.color, Cars.available
FROM Cars
    INNER JOIN MakeModels ON MakeModels.make_model_id = Cars.make_model_id
    INNER JOIN Colors ON Colors.color_id = Cars.color_id;

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[29], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('cars.db')
      6 sql_statement = """
      7 SELECT Cars.car_id, MakeModels.Make, MakeModels.Model, MakeModels.year, Colors.color, Cars.available
      8 FROM Cars
      9     INNER JOIN MakeModels ON MakeModels.make_model_id = Cars.make_model_id
     10     INNER JOIN Colors ON Colors.color_id = Cars.color_id;
     11 """

ModuleNotFoundError: No module named 'pandas'

13.1.11.1. `ON DELETE`#

PRAGMA foreign_keys = ON;
DROP TABLE IF EXISTS Cars;
DROP TABLE IF EXISTS MakeModels;
DROP TABLE IF EXISTS Colors;


CREATE TABLE Colors (
   color_id INTEGER NOT NULL PRIMARY KEY,
   color  TEXT NOT NULL,
   UNIQUE (color)

);

CREATE TABLE MakeModels (
  make_model_id     INTEGER NOT NULL PRIMARY KEY,
  Make   TEXT NOT NULL,
  Model   TEXT NOT NULL,
  Year INTEGER NOT NULL,
  UNIQUE (Make, Model, Year)
);

CREATE TABLE Cars (
   car_id INTEGER NOT NULL PRIMARY KEY,
   make_model_id INTEGER NOT NULL,
   color_id INTEGER, --> Have to allow for NULL values for ON DELETE SET NULL to work!!!!
   available INTEGER NOT NULL,
   FOREIGN KEY(make_model_id) REFERENCES MakeModels(make_model_id) ON DELETE CASCADE,
   FOREIGN KEY(color_id) REFERENCES Colors(color_id) ON DELETE SET NULL
);

INSERT INTO Colors (color) VALUES ('Red');
INSERT INTO Colors (color) VALUES ('Blue');
INSERT INTO Colors (color) VALUES ('Green');

INSERT INTO MakeModels (Make, Model, Year) VALUES ('Ford', 'Explorer', 2019);
INSERT INTO MakeModels (Make, Model, Year) VALUES ('Toyota', 'Camry', 2010);
INSERT INTO MakeModels (Make, Model, Year) VALUES ('Honda', 'Accord', 2015);

INSERT INTO Cars (make_model_id, color_id, available) VALUES (1, 1, 1);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (2, 1, 1);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (3, 1, 1);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (1, 2, 0);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (1, 2, 0);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (3, 3, 0);

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[30], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('cars.db')
      6 sql_statements = """
      7 PRAGMA foreign_keys = ON;
      8 DROP TABLE IF EXISTS Cars;
   (...)     50 INSERT INTO Cars (make_model_id, color_id, available) VALUES (3, 3, 0);
     51 """

ModuleNotFoundError: No module named 'pandas'

DELETE FROM Colors
WHERE color = "Red";

DELETE FROM MakeModels
WHERE make_model_id = 1;

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[31], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('cars.db')
      6 sql_statements = """
      7 DELETE FROM Colors
      8 WHERE color = "Red";
   (...)     11 WHERE make_model_id = 1;
     12 """

ModuleNotFoundError: No module named 'pandas'

SELECT Cars.car_id, MakeModels.Make, MakeModels.Model, MakeModels.year, Colors.color, Cars.available
FROM Cars
    INNER JOIN MakeModels ON MakeModels.make_model_id = Cars.make_model_id
    INNER JOIN Colors ON Colors.color_id = Cars.color_id;

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[32], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('cars.db')
      6 sql_statement = """
      7 SELECT Cars.car_id, MakeModels.Make, MakeModels.Model, MakeModels.year, Colors.color, Cars.available
      8 FROM Cars
      9     INNER JOIN MakeModels ON MakeModels.make_model_id = Cars.make_model_id
     10     INNER JOIN Colors ON Colors.color_id = Cars.color_id;
     11 """

ModuleNotFoundError: No module named 'pandas'

SELECT Cars.car_id, MakeModels.Make, MakeModels.Model, MakeModels.year, Colors.color, Cars.available
FROM Cars
    LEFT JOIN MakeModels ON MakeModels.make_model_id = Cars.make_model_id
    LEFT JOIN Colors ON Colors.color_id = Cars.color_id;

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[33], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('cars.db')
      6 sql_statement = """
      7 SELECT Cars.car_id, MakeModels.Make, MakeModels.Model, MakeModels.year, Colors.color, Cars.available
      8 FROM Cars
      9     LEFT JOIN MakeModels ON MakeModels.make_model_id = Cars.make_model_id
     10     LEFT JOIN Colors ON Colors.color_id = Cars.color_id;
     11 """

ModuleNotFoundError: No module named 'pandas'