Untitled

drop database parties;
create database parties;

use parties;

/*correct command
mysql -u root -p
mysql> source file_name
mysql> source file_name
source C:\Users\achyu\Desktop\cloudera\q5\q5.sql
*/

CREATE TABLE Candidates
(id int,
gender char,
age int,
party varchar(10),
PRIMARY KEY (ID)
);

CREATE TABLE Results
(
constituency_id int NOT NULL,
cand_id int NOT NULL,
votes int,
Primary key (cand_id)
);

/*INSERT INTO table_name
VALUES (value1, value2, value3, ...);*/


INSERT into Candidates values(1,'M',55,'Democratic');
INSERT into Candidates values(2,"M",51,"Democratic");
INSERT into Candidates values(3,"F",62,"Democratic");
INSERT into Candidates values(4,"M",60,"Republic");
INSERT into Candidates values(5,"F",61,"Republic");
INSERT into Candidates values(6,"F",58,"Republic");


INSERT into Results values(1,1,847529);
INSERT into Results values(1,4,283409);
INSERT into Results values(2,2,293841);
INSERT into Results values(2,5,394385);
INSERT into Results values(3,3,429084);
INSERT into Results values(3,6,303890);

SELECT * from Candidates;
SELECT * from Results;

create TABLE ans as 
SELECT id,party,constituency_id,votes
FROM Candidates,Results
WHERE Candidates.id=Results.cand_id;
/*GROUP BY constituency_id;*/

SELECT * FROM ans;