Banner

 

3 - The one-to-many Relationship
Slide Exercises


Design a data model for recording details of Olympic cities.

Use MySQL Workbench to design your data model for recording details of Olympic cities.
Create a table and add rows for the first three Olympics.

Note: Make year an integer, because MySQL permits only years in the range 1901-2155 when the length is 4.

INSERT INTO city (cityname, country, number, season, year, opendate, closedate) VALUES ('Athens', 'Greece', 1, 'Summer', 1896, '18960406', '18960415'); 
INSERT INTO city (cityname, country, number, season, year, opendate, closedate) VALUES ('Paris', 'France', 2, 'Summer', 1900, '19000514', '19001026'); 
INSERT INTO city (cityname, country, number, season, year, opendate, closedate) VALUES ('St. Louis', 'United States', 3, 'Summer', 1904, '19040829', '19040903');   

Report the name and price of those shares where the share price is greater than 10.
SELECT shrfirm, shrprice FROM share WHERE shrprice > 10;

Calculate the total dividends earned by each share. Report the name of the firm and the payment sorted from highest to lowest payment.
SELECT shrfirm, shrdiv*shrqty AS payment FROM share 
	ORDER BY payment DESC;

List names of shares whose name contains sheep or geese.
SELECT shrfirm FROM share 
	WHERE shrfirm REGEXP 'sheep|Sheep|geese|Geese';

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: 22-Aug-2017