Question

In: Computer Science

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

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
);

___________________________________________________

All three tables will be written to a single SQL file.

___________________________________________________

-- 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

Solutions

Expert Solution

mysql>create table candidate(
-> cand_id varchar(12) primary key, -- cand_id
-> name varchar(40) -- cand_nm
-> );
Query OK, 0 rows affected (0.31 sec)


mysql> insert into candidate values(1,'john');
Query OK, 1 row affected (0.11 sec)

mysql> insert into candidate values(2,'ram');
Query OK, 1 row affected (0.09 sec)

mysql> insert into candidate values(3,'sham');
Query OK, 1 row affected (0.09 sec)

mysql> insert into candidate values(4,'sita');
Query OK, 1 row affected (0.10 sec)

mysql> desc candidate;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| cand_id | varchar(12) | NO | PRI | NULL | |
| name | varchar(40) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.41 sec)

mysql> select * from candidate;
+---------+------+
| cand_id | name |
+---------+------+
| 1 | john |
| 2 | ram |
| 3 | sham |
| 4 | sita |
+---------+------+
4 rows in set (0.05 sec)

mysql> 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
-> );
Query OK, 0 rows affected (0.19 sec)

mysql> insert into contributor values(12,'chinu','csn','maharastra','fdt','sales
person','mba');
Query OK, 1 row affected (0.10 sec)

mysql> insert into contributor values(13,'tina','nsk','maharastra','nng','softde
veloper','engg');
Query OK, 1 row affected (0.09 sec)

mysql> insert into contributor values(14,'hiina','pune','maharastra','wer','doct
or','mbbs');
Query OK, 1 row affected (0.09 sec)

mysql> desc contributor;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| contbr_id | int(11) | NO | PRI | NULL | |
| name | varchar(40) | YES | | NULL | |
| city | varchar(40) | YES | | NULL | |
| state | varchar(40) | YES | | NULL | |
| zip | varchar(20) | YES | | NULL | |
| employer | varchar(60) | YES | | NULL | |
| occupation | varchar(40) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
7 rows in set (0.17 sec)

mysql> select * from contributor;
+-----------+-------+------+------------+------+---------------+------------+
| contbr_id | name | city | state | zip | employer | occupation |
+-----------+-------+------+------------+------+---------------+------------+
| 12 | chinu | csn | maharastra | fdt | salesperson | mba |
| 13 | tina | nsk | maharastra | nng | softdeveloper | engg |
| 14 | hiina | pune | maharastra | wer | doctor | mbbs |
+-----------+-------+------+------------+------+---------------+------------+
3 rows in set (0.00 sec)

mysql> insert into contributor values(15,'ram','mumbai','maharastra','wer','doct
or','mbbs');
Query OK, 1 row affected (0.11 sec)

mysql> insert into contributor values(16,'sham','nsk','maharastra','nng','softde
veloper','engg');
Query OK, 1 row affected (0.09 sec)

mysql> insert into contributor values(17,'sita','csn','maharastra','fdt','salesp
erson','mba');
Query OK, 1 row affected (0.10 sec)

mysql> desc contributor;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| contbr_id | int(11) | NO | PRI | NULL | |
| name | varchar(40) | YES | | NULL | |
| city | varchar(40) | YES | | NULL | |
| state | varchar(40) | YES | | NULL | |
| zip | varchar(20) | YES | | NULL | |
| employer | varchar(60) | YES | | NULL | |
| occupation | varchar(40) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
7 rows in set (0.09 sec)

mysql> select * from contributor;
+-----------+-------+--------+------------+------+---------------+------------+
| contbr_id | name | city | state | zip | employer | occupation |
+-----------+-------+--------+------------+------+---------------+------------+
| 12 | chinu | csn | maharastra | fdt | salesperson | mba |
| 13 | tina | nsk | maharastra | nng | softdeveloper | engg |
| 14 | hiina | pune | maharastra | wer | doctor | mbbs |
| 15 | ram | mumbai | maharastra | wer | doctor | mbbs |
| 16 | sham | nsk | maharastra | nng | softdeveloper | engg |
| 17 | sita | csn | maharastra | fdt | salesperson | mba |
+-----------+-------+--------+------------+------+---------------+------------+
6 rows in set (0.05 sec)


mysql> create table contribution( contb_id integer primary key, cand_id varchar(
12),contbr_id integer,amount numeric(6,2),date varchar(20),election_type varchar
(20),trans_id varchar(20),foreign key(cand_id)references candidate(cand_id),fore
ign key(contbr_id)references contributor(contbr_id) );
Query OK, 0 rows affected (0.17 sec)

mysql> desc contribution;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| contb_id | int(11) | NO | PRI | NULL | |
| cand_id | varchar(12) | YES | MUL | NULL | |
| contbr_id | int(11) | YES | MUL | NULL | |
| amount | decimal(6,2) | YES | | NULL | |
| date | varchar(20) | YES | | NULL | |
| election_type | varchar(20) | YES | | NULL | |
| trans_id | varchar(20) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
7 rows in set (0.07 sec)

mysql> insert into contribution values(44,2,15,5677.00,'22jun2015','yes','tyeyw6
7')
-> ;
Query OK, 1 row affected (0.09 sec)

mysql> insert into contribution values(45,3,16,7678.00,'29jun2015','no','iuiuo78
');
Query OK, 1 row affected (0.09 sec)

mysql> insert into contribution values(46,4,17,9999.00,'23jun2015','yes','mnmnm3
4');
Query OK, 1 row affected (0.09 sec)

mysql> desc contribution;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| contb_id | int(11) | NO | PRI | NULL | |
| cand_id | varchar(12) | YES | MUL | NULL | |
| contbr_id | int(11) | YES | MUL | NULL | |
| amount | decimal(6,2) | YES | | NULL | |
| date | varchar(20) | YES | | NULL | |
| election_type | varchar(20) | YES | | NULL | |
| trans_id | varchar(20) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
7 rows in set (0.01 sec)

mysql> select * from contribution;
+----------+---------+-----------+---------+-----------+---------------+--------
--+
| contb_id | cand_id | contbr_id | amount | date | election_type | trans_i
d |
+----------+---------+-----------+---------+-----------+---------------+--------
--+
| 44 | 2 | 15 | 5677.00 | 22jun2015 | yes | tyeyw67
|
| 45 | 3 | 16 | 7678.00 | 29jun2015 | no | iuiuo78
|
| 46 | 4 | 17 | 9999.00 | 23jun2015 | yes | mnmnm34
|
+----------+---------+-----------+---------+-----------+---------------+--------
--+
3 rows in set (0.00 sec)

mysql>

venereology answered 2 days ago
Next > < Previous

Related Solutions

create table node( node_id integer primary key, node_color varchar(10)); create table edge( edge_id integer primary key,...
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)); write an SQL query that lists all those nodes that have edges with a destination node that has color 'red'.
CREATE TABLE Branch ( branchNo VARCHAR(4), address VARCHAR(50), city VARCHAR(30), state VARCHAR(2), phone VARCHAR(20), PRIMARY KEY...
CREATE TABLE Branch ( branchNo VARCHAR(4), address VARCHAR(50), city VARCHAR(30), state VARCHAR(2), phone VARCHAR(20), PRIMARY KEY (branchNo)); INSERT INTO Branch VALUES('B001','366 Tiger Ln','Los Angeles','CA','213-539-8600'); INSERT INTO Branch VALUES('B002','18 Harrison Rd','New Haven','CT','203-444-1818'); INSERT INTO Branch VALUES('B003','55 Waydell St','Essex','NJ','201-700-7007'); INSERT INTO Branch VALUES('B004','22 Canal St','New York','NY','212-055-9000'); INSERT INTO Branch VALUES('B005','1725 Roosevelt Ave','Queens','NY','718-963-8100'); INSERT INTO Branch VALUES('B006','1471 Jerrold Ave','Philadelphia','PA','267-222-5252'); CREATE TABLE Staff ( staffNo VARCHAR(4), fName VARCHAR(20), lName VARCHAR(20), position VARCHAR(20), sex VARCHAR(1), age INTEGER, salary NUMBER(8,2), phone VARCHAR(20), address VARCHAR(50), city VARCHAR(20),...
This is the database CREATE TABLE AIRCRAFT ( AC_NUMBER varchar(5) primary key, MOD_CODE varchar(10), AC_TTAF double,...
This is the database CREATE TABLE AIRCRAFT ( AC_NUMBER varchar(5) primary key, MOD_CODE varchar(10), AC_TTAF double, AC_TTEL double, AC_TTER double ); INSERT INTO AIRCRAFT VALUES('1484P','PA23-250',1833.1,1833.1,101.8); INSERT INTO AIRCRAFT VALUES('2289L','DC-90A',4243.8,768.9,1123.4); INSERT INTO AIRCRAFT VALUES('2778V','MA23-350',7992.9,1513.1,789.5); INSERT INTO AIRCRAFT VALUES('4278Y','PA31-950',2147.3,622.1,243.2); /* -- */ CREATE TABLE CHARTER ( CHAR_TRIP int primary key, CHAR_DATE date, AC_NUMBER varchar(5), CHAR_DESTINATION varchar(3), CHAR_DISTANCE double, CHAR_HOURS_FLOWN double, CHAR_HOURS_WAIT double, CHAR_TOT_CHG double, CHAR_OIL_QTS int, CUS_CODE int, foreign key (AC_NUMBER) references AIRCRAFT(AC_NUMBER) ); INSERT INTO CHARTER VALUES(10001,'2008-02-05','2289L','ATL',936,5.1,2.2,354.1,1,10011); INSERT INTO CHARTER VALUES(10002,'2008-02-05','2778V','BNA',320,1.6,0,72.6,0,10016);...
CREATE TABLE campaign ( cmte_id             varchar(12), cand_id             varchar(12), cand_nm     &nbsp
CREATE TABLE campaign ( cmte_id             varchar(12), cand_id             varchar(12), cand_nm             varchar(40), contbr_nm           varchar(40), contbr_city         varchar(40), contbr_st           varchar(40), contbr_zip          varchar(20), contbr_employer     varchar(60), contbr_occupation   varchar(40), contb_receipt_amt   numeric(6,2), contb_receipt_dt    varchar(20), receipt_desc        varchar(40), memo_cd             varchar(20), memo_text           varchar(20), form_tp               varchar(20), file_num            varchar(20), tran_id             varchar(20), election_tp         varchar(20) Write SQL queries using the campaign data table. -- 4. show the candidate name and number of contributions, for each candidate -- Order by decreasing number of contributions. -- 5. show the candidate name and average contribution amount for each...
Consider the following table definitions create table node( node_id integer primary key, node_color varchar(10)); create table...
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)); What is the result of the following query? select node_id, node_color, destination_id from node, edge; An inner join of the tables node and edge that lists origin node_id and node_color together with the node_id of the destination node for all those nodes that have outgoing...
Consider the following SQL DDL statements: CREATE TABLE DEPT ( did INTEGER, dname VARCHAR(20), PRIMARY KEY(did));...
Consider the following SQL DDL statements: CREATE TABLE DEPT ( did INTEGER, dname VARCHAR(20), PRIMARY KEY(did)); CREATE TABLE EMP( eid INTEGER, name VARCHAR(20), did INTEGER, PRIMARY KEY(eid), FOREIGN KEY(did) REFERENCES DEPT); In the database created by above statements, which of the following operations may cause violation of referential integrity constraints? Question 1 options: UPDATE on DEPT INSERT into DEPT DELETE on EMP Both DELETE on EMP and INSERT into DEPT
USE SQL CREATE TABLE IF NOT EXISTS football_games ( visitor_name VARCHAR(30),       /* Name of the visiting...
USE SQL CREATE TABLE IF NOT EXISTS football_games ( visitor_name VARCHAR(30),       /* Name of the visiting team                     */ home_score SMALLINT NOT NULL,   /* Final score of the game for the Buffs         */ visitor_score SMALLINT NOT NULL,/* Final score of the game for the visiting team */ game_date DATE NOT NULL,        /* Date of the game                              */ players INT[] NOT NULL,         /* This array consists of the football player ids (basically a foreign key to the football_player.id) */ PRIMARY KEY(visitor_name, game_date)...
Create table, create primary and foreign key constraints. Create index on the table to satisfy a...
Create table, create primary and foreign key constraints. Create index on the table to satisfy a query with aggregate functions.
Create a table book_store with columns Book_id VARCHAR(255) NOT NULL, Book_Name VARCHAR(255) NOT NULL, Book_genre VARCHAR(255)...
Create a table book_store with columns Book_id VARCHAR(255) NOT NULL, Book_Name VARCHAR(255) NOT NULL, Book_genre VARCHAR(255) NOT NULL, Status VARCHAR(255) NOT NULL, PRIMARY KEY (Book_id) Create a table book with columns Book_id VARCHAR(255) NOT NULL, Book_Name VARCHAR(255) NOT NULL, Book_release integer, Book_price integer , Publisher Varchar(10), Book_genre VARCHAR(255) NOT NULL, PRIMARY KEY (Book_id) CREATE TABLE price_logs with columns id INT(11) NOT NULL AUTO_INCREMENT, Book_id VARCHAR(255) NOT NULL, Old_Book_price DOUBLE NOT NULL, New_Book_price DOUBLE NOT NULL, updated_at TIMESTAMP NOT NULL DEFAULT...
Create a table book_store with columns Book_id VARCHAR(255) NOT NULL, Book_Name VARCHAR(255) NOT NULL, Book_genre VARCHAR(255)...
Create a table book_store with columns Book_id VARCHAR(255) NOT NULL, Book_Name VARCHAR(255) NOT NULL, Book_genre VARCHAR(255) NOT NULL, Status VARCHAR(255) NOT NULL, PRIMARY KEY (Book_id) Create a table book with columns Book_id VARCHAR(255) NOT NULL, Book_Name VARCHAR(255) NOT NULL, Book_release integer, Book_price integer , Publisher Varchar(10), Book_genre VARCHAR(255) NOT NULL, PRIMARY KEY (Book_id) CREATE TABLE price_logs with columns id INT(11) NOT NULL AUTO_INCREMENT, Book_id VARCHAR(255) NOT NULL, Old_Book_price DOUBLE NOT NULL, New_Book_price DOUBLE NOT NULL, updated_at TIMESTAMP NOT NULL DEFAULT...
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT