Let’s use ChatGPT to help with our project. Let’s make a SQLite database of the 17 United Nations Sustainable Development Goals (SDGs) and some social issues. The database is UNGoalsCategories.db. This part of the project could be attached to one of the other projects.
Let’s add a categories table.
CREATE TABLE UNGoals (
id INTEGER PRIMARY KEY,
goal_name TEXT NOT NULL,
explanation TEXT
);
INSERT INTO UNGoals (id, goal_name, explanation) VALUES
(1, 'No Poverty', 'End poverty in all its forms everywhere.'),
(2, 'Zero Hunger', 'End hunger, achieve food security and improved nutrition and promote sustainable agriculture.'),
(3, 'Good Health and Well-being', 'Ensure healthy lives and promote well-being for all at all ages.'),
(4, 'Quality Education', 'Ensure inclusive and equitable quality education and promote lifelong learning opportunities for all.'),
(5, 'Gender Equality', 'Achieve gender equality and empower all women and girls.'),
(6, 'Clean Water and Sanitation', 'Ensure availability and sustainable management of water and sanitation for all.'),
(7, 'Affordable and Clean Energy', 'Ensure access to affordable, reliable, sustainable and modern energy for all.'),
(8, 'Decent Work and Economic Growth', 'Promote sustained, inclusive and sustainable economic growth, full and productive employment and decent work for all.'),
(9, 'Industry, Innovation and Infrastructure', 'Build resilient infrastructure, promote inclusive and sustainable industrialization and foster innovation.'),
(10, 'Reduced Inequality', 'Reduce inequality within and among countries.'),
(11, 'Sustainable Cities and Communities', 'Make cities and human settlements inclusive, safe, resilient and sustainable.'),
(12, 'Responsible Consumption and Production', 'Ensure sustainable consumption and production patterns.'),
(13, 'Climate Action', 'Take urgent action to combat climate change and its impacts.'),
(14, 'Life Below Water', 'Conserve and sustainably use the oceans, seas and marine resources for sustainable development.'),
(15, 'Life on Land', 'Protect, restore and promote sustainable use of terrestrial ecosystems, sustainably manage forests, combat desertification, and halt and reverse land degradation and halt biodiversity loss.'),
(16, 'Peace, Justice and Strong Institutions', 'Promote peaceful and inclusive societies for sustainable development, provide access to justice for all and build effective, accountable and inclusive institutions at all levels.'),
(17, 'Partnerships for the Goals', 'Strengthen the means of implementation and revitalize the global partnership for sustainable development.');
--
Classification (Categories)
There are two classification systems.
- ✅ System 1: “5 Ps” (People, Planet, Prosperity, Peace, Partnership)
- ✅ System 2: “Three Pillars” (Social, Economic, Environmental)
🛠️ Table Setup for Multiple Classification Systems. To accommodate both systems, we’ll need:
- UNGoalCategories — to hold all category names
- CategorySystems — to distinguish between “5 Ps” and “Three Pillars”
- GoalCategoryAssignments — to link each goal to a category within a specific system
We will create the tables. Next we’ll use INSERT INTO to add two rows representing the two different systems. Thirdly we’ll insert the categories. Lastly we’ll link the UN Goals to the categories. I will combine all of the sql code and show it below.
-- Table of classification systems
CREATE TABLE CategorySystems (
id INTEGER PRIMARY KEY,
system_name TEXT NOT NULL UNIQUE
);
-- Table of categories (linked to a system)
CREATE TABLE UNGoalCategories (
id INTEGER PRIMARY KEY,
category_name TEXT NOT NULL,
system_id INTEGER NOT NULL,
FOREIGN KEY (system_id) REFERENCES CategorySystems(id)
);
-- Linking table between goals and categories
CREATE TABLE GoalCategoryAssignments (
goal_id INTEGER,
category_id INTEGER,
PRIMARY KEY (goal_id, category_id),
FOREIGN KEY (goal_id) REFERENCES UNGoals(id),
FOREIGN KEY (category_id) REFERENCES UNGoalCategories(id)
);
-- insert systems
INSERT INTO CategorySystems (id, system_name) VALUES
(1, '5 Ps'),
(2, 'Three Pillars');
-- insert categories
-- Categories for 5 Ps
INSERT INTO UNGoalCategories (id, category_name, system_id) VALUES
(1, 'People', 1),
(2, 'Planet', 1),
(3, 'Prosperity', 1),
(4, 'Peace', 1),
(5, 'Partnership', 1);
-- Categories for Three Pillars
INSERT INTO UNGoalCategories (id, category_name, system_id) VALUES
(6, 'Social', 2),
(7, 'Economic', 2),
(8, 'Environmental', 2);
-- link goals to categories
INSERT INTO GoalCategoryAssignments (goal_id, category_id) VALUES
(1, 1), -- People
(2, 1),
(3, 1),
(4, 1),
(5, 1),
(6, 2), -- Planet
(7, 3), -- Prosperity
(8, 3),
(9, 3),
(10, 1),
(11, 2),
(12, 2),
(13, 2),
(14, 2),
(15, 2),
(16, 4), -- Peace
(17, 5); -- Partnership
-- three pillars classification
INSERT INTO GoalCategoryAssignments (goal_id, category_id) VALUES
(1, 6), -- Social
(2, 6),
(3, 6),
(4, 6),
(5, 6),
(6, 8), -- Environmental
(7, 7), -- Economic
(8, 7),
(9, 7),
(10, 6),
(11, 8),
(12, 8),
(13, 8),
(14, 8),
(15, 8),
(16, 6),
(17, 7); -- Often listed as economic, but arguably also governance
--
✅ Done! Now your database supports both classification systems, and users can filter SDGs by either the “5 Ps” or “Three Pillars” model.
For only the above script, here is a text file.
🔎 OPTION 1: Create Views (One for Each System)
This makes it easy to query without repeating joins.
🔸 View: Goals by 5 Ps
CREATE VIEW Goals_By_5Ps AS
SELECT
g.id AS goal_id,
g.goal_name,
c.category_name AS category,
cs.system_name
FROM UNGoals g
JOIN GoalCategoryAssignments a ON g.id = a.goal_id
JOIN UNGoalCategories c ON a.category_id = c.id
JOIN CategorySystems cs ON c.system_id = cs.id
WHERE cs.system_name = '5 Ps';
🔸 View: Goals by Three Pillars
CREATE VIEW Goals_By_Three_Pillars AS
SELECT
g.id AS goal_id,
g.goal_name,
c.category_name AS category,
cs.system_name
FROM UNGoals g
JOIN GoalCategoryAssignments a ON g.id = a.goal_id
JOIN UNGoalCategories c ON a.category_id = c.id
JOIN CategorySystems cs ON c.system_id = cs.id
WHERE cs.system_name = 'Three Pillars';
🔎 OPTION 2: One Query with a System Filter
This lets users pass in a system name dynamically (or you can use it in an app).
SELECT
g.id AS goal_id,
g.goal_name,
c.category_name AS category,
cs.system_name
FROM UNGoals g
JOIN GoalCategoryAssignments a ON g.id = a.goal_id
JOIN UNGoalCategories c ON a.category_id = c.id
JOIN CategorySystems cs ON c.system_id = cs.id
WHERE cs.system_name = ?;
Replace ? with ‘5 Ps’ or ‘Three Pillars’.
One SQL File (except option 2 just above)
Click on the text file below for all of the SQL code in this blog post except for the last one just above. You can create a database file in DB Browser (for SQLite) and run the SQL script to get what you see in the diagram below.
Here it is in DBeaver.
