normalisation.sql

================ TUESDAY ================

1. Inserted the avg,min,max from main JobBank table into job_bank_temp
2. added occupation_id into regions_salaries
3. added name_br to occupations table
4. ALTER TABLE occupations DROP COLUMN programme_id;

================ TUESDAY ================


================ MONDAY ================

INSERT INTO programmes(university_id,campus_id,course_id,city_id) (SELECT uni.id,cam.id,cou.id,cit.id
  FROM universities uni
  INNER JOIN (SELECT `guia do estudante - university` AS uni_name,
                     `guia do estudante - campus name` AS cam_name,
                     `guia do estudante - original course names` AS cou_name,
                     `guia do estudante - campus city` AS cit_name
              FROM job_bank_temp
              GROUP BY uni_name,cam_name,cou_name,cit_name) AS main_table
  ON uni.name = main_table.uni_name
  INNER JOIN campuses cam
  ON cam.name = main_table.cam_name
  INNER JOIN courses cou
  ON cou.name = main_table.cou_name
  INNER JOIN cities cit
  ON cit.name = main_table.cit_name);

================ TUESDAY ================

UPDATE regions_salaries rs
INNER JOIN (SELECT `cbo region` AS jb_region,
                   `cbo average salary (dec 2013)` AS jb_avg,
                   `cbo min salary` AS jb_min,
                   `cbo max salary` AS jb_max
            FROM job_bank_temp
            GROUP BY jb_avg,jb_min,jb_max) main_table
ON rs.average_salary = jb_avg
AND rs.min_salary = jb_min
AND rs.max_salary = jb_max
INNER JOIN regions r
ON r.name = main_table.jb_region
SET rs.region_id = r.id;

INSERT INTO occupations(name_en,code,name_pt,name_br,description_en,description_pt, description_fun,cbo_code) (SELECT DISTINCT(`o-net occupation`),`o-net code`,
`o-net occupation pt-br`,
`cbo occupation`,
`o-net job description`,
`o-net job description pt-br`,
`descricao fun`,
`cbo code`
FROM job_bank_temp);

UPDATE occupations o
INNER JOIN (SELECT `groups eng` AS g_name,`o-net occupation` AS o_name
            FROM job_bank_temp
            GROUP BY g_name, o_name) main_table
ON o.name_en = main_table.o_name
INNER JOIN groups g
ON g.name_en = main_table.g_name
SET group_id = g.id;

UPDATE occupations o
INNER JOIN (SELECT `o-net major group` AS mg_name,`o-net occupation` AS o_name
            FROM job_bank_temp
            GROUP BY mg_name, o_name) main_table
ON o.name_en = main_table.o_name
INNER JOIN majorgroups mg
ON mg.name_en = main_table.mg_name
SET majorgroup_id = mg.id;

—  ONLY 4 rows updated, why? —
UPDATE occupations o
INNER JOIN (SELECT `o-net major group` AS min_name,`o-net occupation` AS o_name
            FROM job_bank_temp
            GROUP BY min_name, o_name) main_table
ON o.name_en = main_table.o_name
INNER JOIN minorgroups min
ON min.name_en = main_table.min_name
SET minorgroup_id = min.id;


UPDATE occupations o
INNER JOIN (SELECT `o-net broad (group)` AS b_name,`o-net occupation` AS o_name
            FROM job_bank_temp
            GROUP BY b_name, o_name) main_table
ON o.name_en = main_table.o_name
INNER JOIN broadgroups b
ON b.name_en = main_table.b_name
SET broadgroup_id = b.id;

UPDATE occupations o
INNER JOIN (SELECT `cbo family code` AS f_name,`o-net occupation` AS o_name
            FROM job_bank_temp
            GROUP BY f_name, o_name) main_table
ON o.name_en = main_table.o_name
INNER JOIN families f
ON f.code = main_table.f_name
SET family_id = f.id;

———— JOIN TABLES ———

INSERT INTO interests_occupations(interest_id,occupation_id)
(SELECT i.id,o.id FROM interests i
INNER JOIN
(SELECT `o-net occupation` AS o_name,`O-Net Interests` AS interest
            FROM job_bank_temp
            GROUP BY o_name,interest) main_table
ON i.code = main_table.interest
INNER JOIN occupations o
ON o.name_en = main_table.o_name);

INSERT INTO occupations_regions(region_id,occupation_id)
(SELECT r.id,o.id FROM regions r
INNER JOIN
(SELECT `o-net occupation` AS o_name,`cbo region` AS region
            FROM job_bank_temp
            GROUP BY o_name,region) main_table
ON r.name = main_table.region
INNER JOIN occupations o
ON o.name_en = main_table.o_name);

UPDATE regions_salaries rs
INNER JOIN (SELECT `o-net occupation` AS o_name,
                   `cbo region` AS jb_region,
                   `cbo average salary (dec 2013)` AS jb_avg,
                   `cbo min salary` AS jb_min,
                   `cbo max salary` AS jb_max
            FROM job_bank_temp
            GROUP BY jb_avg,jb_min,jb_max) main_table
ON rs.average_salary = jb_avg
AND rs.min_salary = jb_min
AND rs.max_salary = jb_max
INNER JOIN regions r
ON r.name = main_table.jb_region
INNER JOIN occupations o
ON o.name_en = main_table.o_name
SET rs.occupation_id = o.id;

INSERT INTO occupation_programmes(occupation_id,programme_id)
(SELECT occ.id,pro.id
  FROM universities uni
  INNER JOIN (SELECT `guia do estudante - university` AS uni_name,
                     `guia do estudante - campus name` AS cam_name,
                     `guia do estudante - original course names` AS cou_name,
                     `guia do estudante - campus city` AS cit_name,
                     `o-net occupation` AS o_name
              FROM job_bank_temp
              GROUP BY uni_name,cam_name,cou_name,cit_name,o_name) AS main_table
  ON uni.name = main_table.uni_name
  INNER JOIN campuses cam
  ON cam.name = main_table.cam_name
  INNER JOIN courses cou
  ON cou.name = main_table.cou_name
  INNER JOIN cities cit
  ON cit.name = main_table.cit_name
  INNER JOIN occupations occ
  ON occ.name_en = main_table.o_name
  INNER JOIN programmes pro
  ON pro.university_id = uni.id
  AND pro.campus_id = cam.id
  AND pro.course_id = cou.id
  AND pro.city_id = cit.id);

================ TUESDAY ================