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 (open edition) by Richard T. Watson
For questions and comments please contact the author
Date revised: 02-Dec-2022