Take ownership of one of the primary key Entities of the database. Perform the following types of queries using tables of the segment of the database. Answer these, with the information below.

1. Write a query that performs an Union on you main table and your duplicate mirroring table

2. Write a query that performs an intersection on your main table and your duplicate mirroring table.

3. Write two queries that performs an Difference on you main table and your duplicate mirroring table

4. You are to write a query using your table and a table that it has a relationship with:

5. Create two queries that will alter the structure of your entity table

6. Write two queries that will update two different categories of rows in your entity table

7. Write a Query that will delete two different categories of rows in your entity table

8. Write a few queries that will insert a few unique rows of information into Entity Table

9. Write a Two queries that perform aggregate functions on at least your Primary Table 10. Write Two Queries that uses a HAVING and or GROUP BY clause on different categories of rows in your entity table or a combination of your entity table and other tables

11. Write a Query that sorts the result

12. Create a Stored Procedure that describes the structure of the table that you took responsibility for and also lists all of the tuples in the table.

13. Create a set of Stored Triggers that will automatically backup a tuple when it is deleted or updated. This will involve writing two triggers. One that inserts the older version of the tuple into a backup table before it was updated. Another trigger that will insert the tuple that was deleted into the same backup table that the update trigger uses. Both stored triggers should be linked to the one table that you took individual responsibility for in the project.

Information needed to answer -

CREATE DATABASE investComp; USE investComp;

CREATE TABLE InvestmentCompany ( CompanyName VARCHAR(26),

CONSTRAINT InvestmentCompany_PK PRIMARY KEY (CompanyName) );

CREATE TABLE Portfolio

( PID CHAR(10), Pvalue CHAR(10) NULL, Pname VARCHAR(30), CompanyName_FK VARCHAR(26),

CONSTRAINT Portfolio_PK PRIMARY KEY (PID), CONSTRAINT Portfolio_FK1 FOREIGN KEY (CompanyName_FK) REFERENCES InvestmentCompany(CompanyName)

) ENGINE = INNODB;

CREATE TABLE Customer

( Cid VARCHAR(26), Cname VARCHAR(26), CSSN CHAR(15), PID_FK CHAR(10),

CONSTRAINT Customer_PK PRIMARY KEY (Cid), CONSTRAINT Customer_FK1 FOREIGN KEY (PID_FK) REFERENCES Portfolio(PID)

) ENGINE = INNODB;

CREATE TABLE StockTable

( SID VARCHAR(26), Sshares CHAR(10) NULL, Svalue CHAR(10) NULL, Cid_FK VARCHAR(26),

CONSTRAINT StockTable_PK PRIMARY KEY (SID), CONSTRAINT StockTable_FK1 FOREIGN KEY (Cid_FK) REFERENCES Customer(Cid)

) ENGINE = INNODB;

INSERT INTO InvestmentCompany (CompanyName)

VALUES ("Vanguard");

INSERT INTO InvestmentCompany (CompanyName) VALUES ("Dodge & Cox");

INSERT INTO InvestmentCompany (CompanyName) VALUES ("USAA");

INSERT INTO InvestmentCompany (CompanyName) VALUES ("Ally Bank");

INSERT INTO InvestmentCompany (CompanyName) VALUES ("ScotTrade");

INSERT INTO InvestmentCompany (CompanyName) VALUES ("ABC");

INSERT INTO InvestmentCompany (CompanyName) VALUES ("GetGo");

INSERT INTO InvestmentCompany (CompanyName) VALUES ("PentaOne");

INSERT INTO InvestmentCompany (CompanyName) VALUES ("Wanza");

INSERT INTO InvestmentCompany (CompanyName) VALUES ("OnePrize");

INSERT INTO Portfolio (PID, Pvalue, Pname, CompanyName_FK)

VALUES ("225487", "200", "sys","Vanguard");

INSERT INTO Portfolio (PID, Pvalue, Pname, CompanyName_FK) VALUES ("228894", "150", "rrr","Dodge & Cox");

INSERT INTO Portfolio (PID, Pvalue, Pname, CompanyName_FK) VALUES ("229784", "300", "lol","USAA");

INSERT INTO Portfolio (PID, Pvalue, Pname, CompanyName_FK) VALUES ("223134", "500", "got","Ally Bank");

INSERT INTO Portfolio (PID, Pvalue, Pname, CompanyName_FK) VALUES ("221546", "50", "mas","ScotTrade");

INSERT INTO Portfolio (PID, Pvalue, Pname, CompanyName_FK) VALUES ("227864", "800", "url","ABC");

INSERT INTO Portfolio (PID, Pvalue, Pname, CompanyName_FK) VALUES ("229314", "650", "qnb","GetGo");

INSERT INTO Portfolio (PID, Pvalue, Pname, CompanyName_FK) VALUES ("228135", "420", "skg","PentaOne");

INSERT INTO Portfolio (PID, Pvalue, Pname, CompanyName_FK) VALUES ("224716", "481", "sjv","Wanza");

INSERT INTO Portfolio (PID, Pvalue, Pname, CompanyName_FK) VALUES ("229964", "220", "awf", "OnePrize");

INSERT INTO Customer (Cid, Cname, CSSN, PID_FK)

VALUES ("11111", "Douglas", "000112222", "225487");

INSERT INTO Customer (Cid, Cname, CSSN, PID_FK) VALUES ("22222", "John", "000223333", "228894" );

INSERT INTO Customer (Cid, Cname, CSSN, PID_FK) VALUES ("33333", "Adam", "000334444", "229784" );

INSERT INTO Customer (Cid, Cname, CSSN, PID_FK) VALUES ("44444", "Moe", "000445555", "223134" );

INSERT INTO Customer (Cid, Cname, CSSN, PID_FK) VALUES ( "55555", "Walker", "000556666", "221546");

INSERT INTO Customer (Cid, Cname, CSSN, PID_FK) VALUES ("66666", "David", "000667777", "227864");

INSERT INTO Customer (Cid, Cname, CSSN, PID_FK) VALUES ("77777", "Rowan", "000778888", "229314");

INSERT INTO Customer (Cid, Cname, CSSN, PID_FK) VALUES ("88888", "Suzan", "000889999", "228135");

INSERT INTO Customer (Cid, Cname, CSSN, PID_FK) VALUES ("99999", "Haidy", "000991111", "224716");

INSERT INTO Customer (Cid, Cname, CSSN, PID_FK) VALUES ("12345", "James", "000102222", "229964");

LOAD DATA LOCAL INFILE 'c:/data/StockTable.csv' INTO TABLE StockTable FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ' ' IGNORE 1 LINES;

DROP DATABASE investComp;

Tables

STOCK TABLE

SID	Sshares	Svalue	Cid_FK
12345	1000	20	11111
23456	2500	60	22222
34567	1500	101	33333
45678	500	200	44444
56789	800	37	55555
67890	4000	95	66666
78910	300	87	77777
89101	700	69	88888
91011	3300	88	99999
10111	3500	150	12345

Portfolio Table

PID	Pvalue	Pname	CompanyName_FK
225487	200	sys	Vanguard
228894	150	rrr	Dodge & Cox
229784	300	lol	USAA
223134	500	got	Ally Bank
221546	50	mas	ScotTrade
227864	800	url	ABC
229314	650	qnb	GetGo
228135	420	skg	PentaOne
224716	481	sjv	Wanza
229964	220	awf	OnePrize

Investment Company Table

CompanyName

Vanguard

Dodge & Cox

USAA

Ally Bank

ScotTrade

ABC

GetGo

PentaOne

Wanza

OnePrize

Customer Table

Cid	Cname	CSSN	PID_FK
11111	Douglas	000-11-2222	225487
22222	John	000-22-3333	228894
33333	Adam	000-33-4444	229784
44444	Moe	000-44-5555	223134
55555	Walker	000-55-6666	221546
66666	David	000-66-7777	227864
77777	Rowan	000-77-8888	229314
88888	Suzan	000-88-9999	228135
99999	Haidy	000-99-1111	224716
12345	James	000-10-2222	229964