ロスジェネ！ねこくんのリスキリングの学びをシェア

-- sazaefamily table
CREATE TABLE sazaefamily (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  gender TEXT NOT NULL,
  age INTEGER NOT NULL,
  jobId INTEGER NOT NULL
);

-- sazaefamily values
INSERT INTO sazaefamily VALUES (1, '磯野波平', '男性',54,100);
INSERT INTO sazaefamily VALUES (2, 'フグ田サザエ', '女性',24,200);
INSERT INTO sazaefamily VALUES (3, '磯野カツオ', '男性',11,300);
INSERT INTO sazaefamily VALUES (4, '磯野ワカメ', '女性',9,300);
INSERT INTO sazaefamily VALUES (5, '穴子さん', '男性',27,100);

-- fetch sazaefamily values
SELECT * FROM sazaefamily;

-- jobcode table
CREATE TABLE jobcode (
  jobId INTEGER PRIMARY KEY,
  jobs TEXT NOT NULL
);


-- jobcode values
INSERT INTO jobcode VALUES (100, '会社員');
INSERT INTO jobcode VALUES (200, '主婦');
INSERT INTO jobcode VALUES (300, '学生');


-- fetch jobcode values
SELECT * FROM jobcode;

SELECT sazaefamily.name, sazaefamily.age, jobcode.jobs FROM sazaefamily INNER JOIN jobcode ON sazaefamily.jobId = jobcode.jobId

-- sazaefamily table
CREATE TABLE sazaefamily (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  gender TEXT NOT NULL,
  age INTEGER NOT NULL,
  jobId INTEGER NOT NULL
);

-- sazaefamily values
INSERT INTO sazaefamily VALUES (1, '磯野波平', '男性',54,100);
INSERT INTO sazaefamily VALUES (2, 'フグ田サザエ', '女性',24,200);
INSERT INTO sazaefamily VALUES (3, '磯野カツオ', '男性',11,300);
INSERT INTO sazaefamily VALUES (4, '磯野ワカメ', '女性',9,300);
INSERT INTO sazaefamily VALUES (5, '穴子さん', '男性',27,100);

-- fetch sazaefamily values
SELECT * FROM sazaefamily;



-- jobcode table
CREATE TABLE jobcode (
  jobId INTEGER PRIMARY KEY,
  jobs TEXT NOT NULL
);


-- jobcode values
INSERT INTO jobcode VALUES (100, '会社員');
INSERT INTO jobcode VALUES (200, '主婦');
INSERT INTO jobcode VALUES (300, '学生');


-- fetch jobcode values
SELECT * FROM jobcode;

-- inner join
SELECT sazaefamily.name, sazaefamily.age, jobcode.jobs FROM sazaefamily INNER JOIN jobcode ON sazaefamily.jobId = jobcode.jobId