Postgres 15

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

CREATE TABLE gender(
  gender_id int PRIMARY KEY,
  gender text
);

 CREATE TABLE student(
  student_id int PRIMARY KEY,
  first_name text NOT NULL,
  last_name text NOT NULL,
  gender_id int NOT NULL,
  CONSTRAINT gender_fk FOREIGN key(gender_id) REFERENCES gender(gender_id)
);

INSERT INTO gender VALUES
  (1, 'Male')
, (2, 'Female')
, (3, 'Other')
;

INSERT INTO student VALUES
  (101, 'John'  , 'Smith'   , 1)
, (102, 'Sara'  , 'Bradford', 2) 
, (103, 'Joseph', 'Brown'   , 1)
, (104, 'David' , 'Lopez'   , 3)
;

CREATE TABLE



CREATE TABLE



INSERT 0 3



INSERT 0 4


SELECT student_id, first_name || ' ' || last_name AS full_name
     , CASE WHEN gender_id = 2 THEN 'Female' END AS chk_female
FROM   student;


student_id
full_name
chk_female


101
John Smith
null

102
Sara Bradford
Female

103
Joseph Brown
null

104
David Lopez
null



SELECT 4


SELECT s.student_id, s.first_name || ' ' || s.last_name AS full_name
     , g.gender AS chk_female
FROM   student s
LEFT   JOIN gender g ON g.gender_id = s.gender_id AND g.gender = 'Female';


student_id
full_name
chk_female


101
John Smith
null

102
Sara Bradford
Female

103
Joseph Brown
null

104
David Lopez
null



SELECT 4

student_id	full_name	chk_female
101	John Smith	null
102	Sara Bradford	Female
103	Joseph Brown	null
104	David Lopez	null