Take ownership of one of the primary key Entities of the database. Perform the f

Question

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

12345

Portfolio Table

Investment Company Table

OnePrize

Customer 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

Explanation / Answer

1. Write a query that performs an Union on you main table and your duplicate mirroring table
I want to find out pvalue with companies 50 or >500

select companyname_fk from portfolio where pvalue=50 union select company name_fk from portfolio where pvalue>=500;

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

Answer) Mysql doesnt suppot intersect so we using in operator

select companyname_fk from portfolio where companyname_fk in( select comp anyname from investmentcompany);

3. Write two queries that performs an Difference on you main table and your duplicate mirroring table
Answer) Mysql doesnt suppot minus so we using not in operator

select companyname_fk from portfolio where companyname_fk not in( select comp anyname from investmentcompany);

---It will return zero rows

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

answer) we want to view portfolio name of the customers.

select cus.cname,port.companyname_fk from customer cus,portfolio port where cus.pid_fk=port.pid;

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

ALTER TABLE customer CHANGE COLUMN cname cname VARCHAR(30);
alter table customer add column age integer(3) after cname;

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

update portfolio set pvalue=56 where companyname_fk='ABC';

update portfolio set pvalue=356 where companyname_fk='USAA';

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

delete from customer where cid=11111;

delete from customer where cname='John';

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

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

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

9. Write a Two queries that perform aggregate functions on at least your Primary Table

select count(*) from portfolio;
select sum(pvalue) from portfolio;

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

select pname,sum(pvalue) from portfolio group by pname ;

select pname,sum(pvalue) from portfolio group by pname having sum(pvalue)>500;

11. Write a Query that sorts the result

select * from portfolio order by pvalue;

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.