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
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:
create table candidate ( cand_id varchar(12) primary key, -- cand_id name varchar(40) --...
CREATE TABLE DEPT ( DEPTNO INTEGER NOT NULL, DNAME VARCHAR(14), LOC VARCHAR(13), PRIMARY KEY (DEPTNO)); INSERT INTO DEPT VALUES (10,'SPORTS','NEW YORK'); INSERT INTO DEPT VALUES (20,'HOME','DALLAS'); INSERT INTO DEPT VALUES (30,'OUTDOOR','CHICAGO'); INSERT INTO DEPT VALUES (40,'CLOTHING','BOSTON'); CREATE TABLE EMP ( EMPNO INTEGER NOT NULL, ENAME VARCHAR(10), JOB VARCHAR(9), MGR INTEGER, SAL FLOAT, COMM FLOAT, DEPTNO INTEGER NOT NULL, FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO), FOREIGN KEY (MGR) REFERENCES EMP(EMPNO), PRIMARY KEY (EMPNO)); INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL, 5000,NULL,10); INSERT INTO...
Help In Database: Next comes the data entry: Make up data for your database, or find real data. You can enter the data with INSERT statements, but it is much easier to use PhpAdmin. It has a form where you can type the data directly. Alternatively, you can use PhpAdmin to import CSV data (comma-separated values), which you can export from Excel. Include at least 3 rows in each of the entity relations, and as many rows as you need...
-- Schema definition create table Customer ( cid smallint not null, name varchar(20), city varchar(15), constraint customer_pk primary key (cid) ); create table Club ( club varchar(15) not null, desc varchar(50), constraint club_pk primary key (club) ); create table Member ( club varchar(15) not null, cid smallint not null, constraint member_pk primary key (club, cid), constraint mem_fk_club foreign key (club) references Club, constraint mem_fk_cust...
Given the schema, write a query and subquery to do the following: CREATE TABLE Majors major VARCHAR(12), description VARCHAR, PRIMARY KEY (major) ); CREATE TABLE Course ( courseMajor VARCHAR(12), courseNo VARCHAR(6), credits INTEGER NOT NULL, enroll_limit INTEGER, PRIMARY KEY(courseNo, courseMajor), FOREIGN KEY (courseMajor) REFERENCES Majors (major) CREATE TABLE Tracks trackMajor VARCHAR(12), trackCode VARCHAR(10), title VARCHAR, PRIMARY KEY(trackMajor, trackCode), FOREIGN KEY (trackMajor) REFERENCES Majors(major) CREATE TABLE Student ( SID CHAR(8), sName VARCHAR(30), studentMajor VARCHAR(12), trackCode VARCHAR(10), PRIMARY KEY(SID), FOREIGN KEY (studentMajor,...
Consider the following table definitions: create table node (node_id integer primary key, node_color varchar(10)); create table edge (edge_id integer primary key, origin_id integer, destination_id integer, foreign key (origin_id) references node (node_id), foreign key (destination_id) references node (node_id) ); Which of the following list the ids of nodes that link to both the node with id 3 and and the node with id 4? select node id from node inner join edge on node_id=origin_id where destination_id=3 and destination_id=4; select node_id from...
Use an INSERT INTO statement with a subquery containing a SQL string function to create user names for the volunteers based on their legal names and insert them into the table. +----------------------+ | Tables_in_volunteers | +----------------------+ | address | | email | | funds | | hours | | person | | phone | | users | +----------------------+ users legal names are in the address table user table is : CREATE TABLE USERS(volunteer_id INT NOT NULL, username VARCHAR(50), PRIMARY KEY...
rider_student Column Data Type Description student_id integer the primary key first_name varchar(25) student first name last_name varchar(25) student last name major_id integer the ID of the student's major; a foreign key for the major_id in the rider_major table rider_major Column Data Type Description major_id integer the primary key major_name varchar(50) student first name major_description varchar(100) student last name Use the Tables above to answer the questions. Questions: 1. Write a SQL statement to add a student record to the rider_student...
please answer 56789 CREATE TABLE ALLDRINKS( /* All legal drinks */ DRINK VARCHAR(30) NOT NULL, /* Drink name */ CONSTRAINT DRINKNAME_PKEY PRIMARY KEY(DRINK) ); CREATE TABLE DRINKERS ( /* All drinkers */ DRINKER VARCHAR(30) NOT NULL, CONSTRAINT DRINKERS_PKEY PRIMARY KEY (DRINKER)); CREATE TABLE LOCATED( /* Pubs have locations */ PUB VARCHAR(30) NOT NULL, /* Pub name */ STREET VARCHAR(30) NOT NULL, /* Street name */ BLDG_NO DECIMAL(4) NOT NULL, /* Building number */...
Given: Create table Book ( Book_id integer, Book_title varchar(50), Author varchar(50), Publish_date date, Type varchar(30), Edition number, Quantity number, Primary key (Book_id) ); insert into Book values (1,'The Old Man and the Sea','Hemingway' ,date '1978-1-1','hardcopy',3,2); insert into Book values (2,'The Old Man and the Sea','Hemingway' ,date '1979-1-1','hardcopy',4,1); insert into Book values (3,'The Old Man and the Sea','Hemingway' ,date '1980-1-1', 'hardcopy',5,10); insert into Book values (4,'A Farewell to Arms','Hemingway' ,date '1986-1-1','hardcopy',2,18); insert into Book values (5,'For Whom the Bell Tolls','Hemingway' ,date...
Relation Students has schema: CREATE TABLE Students ( BannerID CHAR(9), stuName VARCHAR(40) NOT NULL, scholarship INT, PRIMARY KEY(BannerID)); The relation Students is currently empty. Develop a test that determines whether an insertion into Students is currently legal. Then apply your test to determine which of the following INSERT statements is allowable. a. INSERT INTO Students VALUES(950111333, ’John Smith’, 1000); b. INSERT INTO Students (BannerID, stuName) VALUES(‘950111333’, ’John Smith’); c. INSERT INTO Students VALUES(‘950111222’, NOT NULL,...