Banner

 

4 - The One-to-Many Relationship
Slide Exercises

Develop a data model to keep track of a distance runner’s times over various lengths.
Create the database and add 3 rows for each of 2 athletes

Report the total dividend payment for each country that has three or more stocks in the portfolio.
SELECT natname, sum(stkdiv*stkqty*exchrate) AS payment FROM stock JOIN  nation 
ON stock.natcode = nation.natcode
GROUP BY natname HAVING COUNT(*) >= 3;

Report the names of nations starting with ‘United’.
SELECT natname FROM nation WHERE natname REGEXP '^United';

Report the country, firm, and stock holding for the maximum quantity of stock held for each country.
SELECT natname, stkfirm, stkqty FROM 
stock JOIN  nation ON stock.natcode = nation.natcode 
WHERE stkqty =
(SELECT MAX(stkqty) FROM stock
WHERE stock.natcode = nation.natcode);

Create a view for dividend payment.
CREATE VIEW divvalue
	(nation, firm, price, qty, exchrate, divpayment)
	AS SELECT natname, stkfirm, stkprice, stkqty, exchrate,
		stkdiv*stkqty*exchrate
		FROM stock JOIN nation
			ON stock.natcode = nation.natcode;

This page is part of the promotional and support material for Data Management (sixth edition) by Richard T. Watson
For questions and comments please contact the author

Date revised: 19-Oct-2016