Program 4 - Election Database SQL
(Oracle-Compatible)
1. Table Creation
CREATE TABLE Constituency (
Constituency_ID INT PRIMARY KEY,
Name VARCHAR(50),
State VARCHAR(50),
Number_of_Voters INT
);
CREATE TABLE Voter (
Voter_ID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
HouseNo VARCHAR(20),
City VARCHAR(50),
State VARCHAR(50),
Pincode VARCHAR(10),
Constituency_ID INT,
FOREIGN KEY (Constituency_ID) REFERENCES Constituency(Constituency_ID)
);
CREATE TABLE Party (
Party_ID INT PRIMARY KEY,
Party_Name VARCHAR(50),
Party_Symbol VARCHAR(50)
);
CREATE TABLE Candidate (
Candidate_ID INT PRIMARY KEY,
Name VARCHAR(50),
Phone_No VARCHAR(15),
Age INT,
State VARCHAR(50),
Party_ID INT,
FOREIGN KEY (Party_ID) REFERENCES Party(Party_ID)
);
CREATE TABLE Candidate_Constituency (
Candidate_ID INT,
Constituency_ID INT,
PRIMARY KEY (Candidate_ID, Constituency_ID),
FOREIGN KEY (Candidate_ID) REFERENCES Candidate(Candidate_ID),
FOREIGN KEY (Constituency_ID) REFERENCES Constituency(Constituency_ID)
);
CREATE TABLE Vote (
Voter_ID INT PRIMARY KEY,
Candidate_ID INT,
FOREIGN KEY (Voter_ID) REFERENCES Voter(Voter_ID),
FOREIGN KEY (Candidate_ID) REFERENCES Candidate(Candidate_ID)
);
2. Insert Sample Data
Constituency Table Inserts
INSERT INTO Constituency VALUES (1, 'Bangalore Central', 'Karnataka',
0);
INSERT INTO Constituency VALUES (2, 'Mumbai South', 'Maharashtra', 0);
INSERT INTO Constituency VALUES (3, 'Delhi East', 'Delhi', 0);
INSERT INTO Constituency VALUES (4, 'Pune Central', 'Maharashtra', 0);
INSERT INTO Constituency VALUES (5, 'Hyderabad North', 'Telangana', 0);
Party Table Inserts
INSERT INTO Party VALUES (1, 'National Party', 'NP');
INSERT INTO Party VALUES (2, 'State Party', 'SP');
INSERT INTO Party VALUES (3, 'People Party', 'PP');
Candidate Table Inserts
INSERT INTO Candidate VALUES (101, 'Rahul Verma', '9876543210', 45,
'Karnataka', 1);
INSERT INTO Candidate VALUES (102, 'Anjali Rao', '9123456780', 50,
'Maharashtra', 2);
INSERT INTO Candidate VALUES (103, 'Sunil Kumar', '9988776655', 42,
'Delhi', 3);
INSERT INTO Candidate VALUES (104, 'Pooja Sharma', '9012345678', 48,
'Telangana', 1);
INSERT INTO Candidate VALUES (105, 'Amit Singh', '9888888888', 46,
'Maharashtra', 1);
Candidate_Constituency Table Inserts
INSERT INTO Candidate_Constituency VALUES (101, 1);
INSERT INTO Candidate_Constituency VALUES (101, 3);
INSERT INTO Candidate_Constituency VALUES (102, 2);
INSERT INTO Candidate_Constituency VALUES (102, 4);
INSERT INTO Candidate_Constituency VALUES (103, 3);
INSERT INTO Candidate_Constituency VALUES (104, 5);
INSERT INTO Candidate_Constituency VALUES (105, 2);
Voter Table Inserts
INSERT INTO Voter VALUES (1001, 'Suresh', 30, '101', 'Bangalore',
'Karnataka', '560001', 1);
INSERT INTO Voter VALUES (1002, 'Ramesh', 19, '102', 'Bangalore',
'Karnataka', '560001', 1);
INSERT INTO Voter VALUES (1003, 'Meena', 45, '201', 'Mumbai',
'Maharashtra', '400001', 2);
INSERT INTO Voter VALUES (1004, 'Geeta', 40, '202', 'Mumbai',
'Maharashtra', '400001', 2);
INSERT INTO Voter VALUES (1005, 'Anil', 25, '301', 'Delhi', 'Delhi',
'110001', 3);
INSERT INTO Voter VALUES (1006, 'Sunita', 35, '401', 'Pune',
'Maharashtra', '411001', 4);
INSERT INTO Voter VALUES (1007, 'Rajesh', 50, '501', 'Hyderabad',
'Telangana', '500001', 5);
Vote Table Inserts
INSERT INTO Vote VALUES (1001, 101);
INSERT INTO Vote VALUES (1002, 101);
INSERT INTO Vote VALUES (1003, 102);
INSERT INTO Vote VALUES (1004, 105);
INSERT INTO Vote VALUES (1005, 103);
INSERT INTO Vote VALUES (1006, 102);
INSERT INTO Vote VALUES (1007, 104);
3. Queries and Procedures
i. Candidates contesting in more than one constituency from different states
SELECT C.Candidate_ID, C.Name, COUNT(DISTINCT Co.State) AS StateCount
FROM Candidate C
JOIN Candidate_Constituency CC ON C.Candidate_ID = CC.Candidate_ID
JOIN Constituency Co ON CC.Constituency_ID = Co.Constituency_ID
GROUP BY C.Candidate_ID, C.Name
HAVING COUNT(DISTINCT Co.State) > 1;
ii. Display the state having the maximum number of constituencies
SELECT State
FROM (
SELECT State, COUNT(*) AS ConstituencyCount
FROM Constituency
GROUP BY State
ORDER BY COUNT(*) DESC
)
WHERE ROWNUM = 1;
iii. Stored Procedure to insert voter if age ≥ 18
CREATE OR REPLACE PROCEDURE InsertVoter (
p_Voter_ID IN INT,
p_Name IN VARCHAR,
p_Age IN INT,
p_HouseNo IN VARCHAR,
p_City IN VARCHAR,
p_State IN VARCHAR,
p_Pincode IN VARCHAR,
p_Constituency_ID IN INT
)
IS
BEGIN
IF p_Age >= 18 THEN
INSERT INTO Voter VALUES (
p_Voter_ID, p_Name, p_Age, p_HouseNo, p_City, p_State, p_Pincode,
p_Constituency_ID
);
ELSE
DBMS_OUTPUT.PUT_LINE('Not an eligible voter');
END IF;
END;
/
iv. Stored Procedure to get number of voters in a constituency
CREATE OR REPLACE PROCEDURE GetVoterCount (
p_ConstituencyName IN VARCHAR
)
IS
cnt INT;
BEGIN
SELECT Number_of_Voters INTO cnt
FROM Constituency
WHERE Name = p_ConstituencyName;
DBMS_OUTPUT.PUT_LINE('Number of Voters in ' || p_ConstituencyName || '
is ' || cnt);
END;
/
v. Trigger to update Number_of_Voters after inserting voter
CREATE OR REPLACE TRIGGER UpdateVoterCount
AFTER INSERT ON Voter
FOR EACH ROW
BEGIN
UPDATE Constituency
SET Number_of_Voters = Number_of_Voters + 1
WHERE Constituency_ID = :NEW.Constituency_ID;
END;
/
