SQLite 3.8

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE crime (cr_rating VARCHAR (25) PRIMARY KEY);
INSERT INTO crime VALUES ('Low'), ('Medium'), ('High');
SELECT * FROM crime;


cr_rating


Low

Medium

High


CREATE TABLE weather (weather_type VARCHAR (50) PRIMARY KEY);
INSERT INTO weather VALUES ('Scorching'), ('Sunny'), ('Mild'), ('Overcast'), ('Drizzle'), ('Rainy'), ('Stormy'), ('Hurricane');
SELECT * FROM weather;


weather_type


Scorching

Sunny

Mild

Overcast

Drizzle

Rainy

Stormy

Hurricane


CREATE TABLE job (job_id INTEGER PRIMARY KEY, job_type VARCHAR (30) NOT NULL);
INSERT INTO job (job_type) VALUES ('Unknown'), ('Unemployed'), ('Blue collar'), ('White collar'), ('Manager'), ('Self-employed');
SELECT * FROM job;


job_id
job_type


1
Unknown

2
Unemployed

3
Blue collar

4
White collar

5
Manager

6
Self-employed


CREATE TABLE the_location
(

  loc_key INTEGER PRIMARY KEY,
  loc_coords VARCHAR (50) NOT NULL, -- not sure how you store these in your system
  loc_status INTEGER CHECK (loc_status IN (0,1)),
  loc_rating INTEGER CHECK (loc_rating BETWEEN 1 AND 10), 
  loc_crime VARCHAR (25) NOT NULL, 
  -- could use a CHECK CONSTRAINT, but use a lookup table instead
  -- note also that the types should match exactly
  loc_weather VARCHAR (50) NOT NULL,
  -- location_rating_tourism, -- same lookup table idea here (Good, Medium, Poor, Death-zone)
  -- location_rating_income, -- list of social classes table
  -- location_rating_jobs
  CONSTRAINT ct_loc_crime FOREIGN KEY (loc_crime) REFERENCES crime (cr_rating),
  CONSTRAINT ct_loc_weather FOREIGN KEY (loc_weather) REFERENCES weather (weather_type)
);
INSERT INTO the_location 
(loc_coords, loc_status, loc_rating, loc_crime, loc_weather) 
VALUES 
('coord_1', 0, 7, 'Medium', 'Rainy'), 
('coord_2', 1, 5, 'High', 'Mild');
SELECT * FROM the_location;


loc_key
loc_coords
loc_status
loc_rating
loc_crime
loc_weather


1
coord_1
0
7
Medium
Rainy

2
coord_2
1
5
High
Mild


CREATE TABLE location_job
( 
  lj_loc INTEGER NOT NULL,
  lj_job      INTEGER NOT NULL,
  lj_percent  INTEGER DEFAULT 0 NOT NULL,
  CONSTRAINT pk_lj PRIMARY KEY (lj_loc, lj_job),
  CONSTRAINT ct_lj_loc FOREIGN KEY (lj_loc) REFERENCES the_location (loc_key),
  CONSTRAINT ct_lj_job FOREIGN KEY (lj_job) REFERENCES job(job_id)
);
INSERT INTO location_job 
VALUES 
(1, 1, 10), (1, 2, 10), (1, 3, 10), (1, 4, 10), (1, 5, 10), (1, 6, 50),
(2, 1, 30), (2, 2, 30), (2, 3, 10), (2, 4, 10), (2, 5, 10), (2, 6, 10);
SELECT * FROM location_job;


lj_loc
lj_job
lj_percent


1
1
10

1
2
10

1
3
10

1
4
10

1
5
10

1
6
50

2
1
30

2
2
30

2
3
10

2
4
10

2
5
10

2
6
10


SELECT 
  tl.loc_key, tl.loc_coords, 
  lj.lj_loc, lj.lj_job, lj.lj_percent, 
  j.job_id, j.job_type
FROM the_location tl
JOIN location_job lj ON tl.loc_key = lj.lj_loc
JOIN job j ON lj.lj_job = j.job_id
ORDER BY tl.loc_key, j.job_id;


loc_key
loc_coords
lj_loc
lj_job
lj_percent
job_id
job_type


1
coord_1
1
1
10
1
Unknown

1
coord_1
1
2
10
2
Unemployed

1
coord_1
1
3
10
3
Blue collar

1
coord_1
1
4
10
4
White collar

1
coord_1
1
5
10
5
Manager

1
coord_1
1
6
50
6
Self-employed

2
coord_2
2
1
30
1
Unknown

2
coord_2
2
2
30
2
Unemployed

2
coord_2
2
3
10
3
Blue collar

2
coord_2
2
4
10
4
White collar

2
coord_2
2
5
10
5
Manager

2
coord_2
2
6
10
6
Self-employed


-- Experimental for the moment...

WITH job (j) AS ( VALUES ('Unknown'), ('Clean'), ('Cook') ),
location (loc) AS ( VALUES ('Here'), ('There') ),
data (j, loc, cnt) AS ( VALUES ('Clean', 'Here', 1) )
SELECT j, loc, COALESCE(cnt, 0) AS effective_count
FROM (job CROSS JOIN location) AS master_list
LEFT JOIN data USING (j, loc);


j
loc
effective_count


Unknown
Here
0

Unknown
There
0

Clean
Here
1

Clean
There
0

Cook
Here
0

Cook
There
0


-- Experimental for the moment...

WITH jb (j) AS
(
  SELECT job_id FROM job
),
lk (lok) AS
(
  SELECT loc_coords FROM the_location
),
data (j, lok, cnt) AS 
(VALUES
(1, 2, 10), (1, 3, 10), (1, 4, 10), (1, 5, 10), (1, 6, 50), -- (1, 1, 10),
(2, 1, 30), (2, 2, 30), (2, 3, 10), (2, 4, 10), (2, 5, 10), (2, 6, 10)
)
SELECT j, lok, COALESCE(cnt, 0) AS effective_count
FROM (jb CROSS JOIN lk) AS master_list
LEFT JOIN data USING (j, lok);


j
lok
effective_count


1
coord_1
0

1
coord_2
0

2
coord_1
0

2
coord_2
0

3
coord_1
0

3
coord_2
0

4
coord_1
0

4
coord_2
0

5
coord_1
0

5
coord_2
0

6
coord_1
0

6
coord_2
0

job_id	job_type
1	Unknown
2	Unemployed
3	Blue collar
4	White collar
5	Manager
6	Self-employed

loc_key	loc_coords	lj_loc	lj_job	lj_percent	job_id	job_type
1	coord_1	1	1	10	1	Unknown
1	coord_1	1	2	10	2	Unemployed
1	coord_1	1	3	10	3	Blue collar
1	coord_1	1	4	10	4	White collar
1	coord_1	1	5	10	5	Manager
1	coord_1	1	6	50	6	Self-employed
2	coord_2	2	1	30	1	Unknown
2	coord_2	2	2	30	2	Unemployed
2	coord_2	2	3	10	3	Blue collar
2	coord_2	2	4	10	4	White collar
2	coord_2	2	5	10	5	Manager
2	coord_2	2	6	10	6	Self-employed

j	loc	effective_count
Unknown	Here	0
Unknown	There	0
Clean	Here	1
Clean	There	0
Cook	Here	0
Cook	There	0