Question

create table candidate (
cand_id   varchar(12) primary key,   -- cand_id
name       varchar(40)           -- cand_nm
);

create table contributor (
contbr_id   integer primary key,
name       varchar(40),           -- contbr_nm
city     varchar(40),           -- contbr_city
state       varchar(40),           -- contbr_st
zip       varchar(20),           -- contbr_zip
employer   varchar(60),           -- contbr_employer
occupation   varchar(40)           -- contbr_occupation
);

create table contribution (
contb_id   integer primary key,
cand_id   varchar(12),           -- cand_id
contbr_id   varchar(12),           -- contbr_id
amount   numeric(6,2),           -- contb_receipt_amt
date       varchar(20),           -- contb_receipt_dt
election_type varchar(20),           -- election_tp
tran_id   varchar(20),           -- tran_id
foreign key (cand_id) references candidate,
foreign key (contbr_id) references contributor
);

-- 16. set the SQLite output to be a file named 'campaign-normal.sql'
.output 'campaign-normal.sql'

-- 17. output the candidate schema, and then all candidate rows as SQL
-- insert statements.
-- Hint: the SQLite .mode command allows you to select that you want
-- rows of a query to be output as SQL insert statements, and the
-- table name to be specified.

-- 18. output the contributor schema, and then all contributor rows as SQL
-- insert statements.

-- 19. output the contribution schema, and then all contribution rows as SQL
-- insert statements.

-- 20. set the SQL output so that it no longer goes to a file

Answer 1

Queries:

-- ************ Creating candidate table ************
create table candidate (
cand_id varchar(12) primary key, -- cand_id
name varchar(40) -- cand_nm
);

-- ************ Creating contributor table ************
create table contributor (
contbr_id integer primary key,
name varchar(40), -- contbr_nm
city varchar(40), -- contbr_city
state varchar(40), -- contbr_st
zip varchar(20), -- contbr_zip
employer varchar(60), -- contbr_employer
occupation varchar(40) -- contbr_occupation
);

-- ************ Creating contribution table ************
create table contribution (
contb_id integer primary key,
cand_id varchar(12), -- cand_id
contbr_id varchar(12), -- contbr_id
amount numeric(6,2), -- contb_receipt_amt
date varchar(20), -- contb_receipt_dt
election_type varchar(20), -- election_tp
tran_id varchar(20), -- tran_id
foreign key (cand_id) references candidate,
foreign key (contbr_id) references contributor
);

-- ************ Insert 3 sample records into candidate table *****************
INSERT INTO candidate VALUES('ID1234','Retheesh');
INSERT INTO candidate VALUES('ID1230','Sampath');
INSERT INTO candidate VALUES('ID1244','Ajay');

-- ************ Insert 2 sample records into contributor table *****************
INSERT INTO contributor VALUES(100, "Kiran", "Trivandrum", "Kerala", "695000", "ABCD", "Engineer");
INSERT INTO contributor VALUES(200, "John", "Kochi", "Kerala", "695010", "XYZ", "Sales Executive");

-- ************ Insert 2 sample records into contribution table *****************
INSERT INTO contribution VALUES(12500, 'ID1230', 100, 2000.00, '20/05/2018', 'MLA', 'TRN12345234');
INSERT INTO contribution VALUES(12600, 'ID1230', 200, 250.00, '23/11/2018', 'MP', 'TRN87354769');
INSERT INTO contribution VALUES(12700, 'ID1234', 200, 6000.00, '07/10/2018', 'MLA', 'TRN76767898');

-- 16. set the SQLite output to be a file named 'campaign-normal.sql'
-- ************ setting output file for sql results *****************
.output C:\\Users\\Retheesh\\Desktop\\test\\campaign-normal.sql

-- 17. output the candidate schema, and then all candidate rows as SQL insert statements.
-- ************ setting mode as sql insert query for candidate table *****************
.mode insert "candidate"

-- ************ getting schema of candidate table *****************
.schema candidate

-- ************ getting all records from candidate table as insert queries *****************
select * from candidate;

-- 18. output the contributor schema, and then all contributor rows as SQL insert statements.
-- ************ setting mode as sql insert query for contributor table *****************
.mode insert "contributor"

-- ************ getting schema of contributor table *****************
.schema contributor

-- ************ getting all records from contributor table as insert queries *****************
select * from contributor;

-- 19. output the contribution schema, and then all contribution rows as SQL insert statements.
-- ************ setting mode as sql insert query for contribution table *****************
.mode insert "contribution"

-- ************ getting schema of contribution table *****************
.schema contribution

-- ************ getting all records from contribution table as insert queries *****************
select * from contribution;

-- 20. set the SQL output so that it no longer goes to a file
-- ************ setting output *****************
.output stdout

Screenshot of queries execution:

Output file screenshot: