Untitled

-- voters tablosunu oluşturmak
CREATE TABLE voters (
  voters_id int,
  voters_code varchar(255),
  voters_area varchar(255),
  gender varchar(50),
  votesFor varchar(255),
  votes_id int
);

-- votes tablosunu oluşturmak
CREATE TABLE votes (
  votes_id int,
  voters_id int,
  candidate_id int
);

-- candidate tablosunu oluşturmak
CREATE TABLE candidate (
  candidate_id int,
  candidate_code varchar(255),
  candidate_party varchar(255)
);

-- voters tablosu için örnek veriler eklemek
INSERT INTO voters (voters_id, voters_code, voters_area, gender, votesFor, votes_id) VALUES
(1, 'V001', 'Bogor', 'female', 'C001', 101),
(2, 'V002', 'Jakarta', 'male', 'C002', 102),
(3, 'V003', 'Bogor', 'female', 'C003', 103),
(4, 'V004', 'Bandung', 'female', 'C001', 104);

-- votes tablosu için örnek veriler eklemek
INSERT INTO votes (votes_id, voters_id, candidate_id) VALUES
(101, 1, 1),
(102, 2, 2),
(103, 3, 3),
(104, 4, 1);

-- candidate tablosu için örnek veriler eklemek
INSERT INTO candidate (candidate_id, candidate_code, candidate_party) VALUES
(1, 'A-1', 'Party A'),
(2, 'B-1', 'Party B'),
(3, 'A-3', 'Party A');

-- Sorular

-- Female voters who voted Party A
SELECT *
FROM voters v
JOIN votes vt ON v.votes_id = vt.votes_id
JOIN candidate c ON vt.candidate_id = c.candidate_id
WHERE v.gender = 'female' AND c.candidate_party = 'Party A';

-- Bogor area who voted for Candidate A-3

SELECT *
FROM voters v
JOIN votes vt ON v.votes_id = vt.votes_id
JOIN candidate c ON vt.candidate_id = c.candidate_id
WHERE v.voters_area = 'Bogor' AND c.candidate_code = 'A-3';