Chapter 14 Skill Builders

CREATE TABLE  political_unit (
 unitname       VARCHAR(30) not null,
 unitcode       CHAR(2),
 unitpop        DECIMAL(6,2),
 PRIMARY KEY(unitcode));

CREATE TABLE boundary (
 boundid        INTEGER,
 boundpath      POLYGON NOT NULL,
 unitcode       CHAR(2),
 PRIMARY KEY(boundid),
 CONSTRAINT fk_boundary_polunit FOREIGN KEY(unitcode)
   REFERENCES political_unit(unitname)) TYPE MYISAM;

CREATE TABLE city (
 cityname       VARCHAR(30),
 cityloc        POINT NOT NULL,
 unitcode       CHAR(2),
 PRIMARY KEY(unitcode,cityname),
 CONSTRAINT fk_city_polunit FOREIGN KEY(unitcode) 
   REFERENCES political_unit(unitname)) TYPE MYISAM; 		

INSERT INTO political_unit VALUES ('Republic of Ireland','ie', 3.9);
INSERT INTO political_unit VALUES ('Northern Ireland','ni', 1.7);
INSERT INTO boundary VALUES
  (1,GeomFromText('polygon((9 8, 9 3, 4 1, 2 2, 1 3, 3 5, 3 6, 2 6,
	2 9, 5 9, 5 10, 6 11, 7 11, 7 10, 6 9, 7 8, 7 9, 8 9, 8 8, 9 8))'),'ie');
INSERT INTO boundary VALUES
  (2,GeomFromText('polygon((7 11, 9 11, 10 9, 10 8, 8 8, 8 9, 7 9,
	7 8, 6 9, 7 10, 7 11))'),'ni');
INSERT INTO city VALUES ('Dublin',GeomFromText('POINT(9 6)'),'ie');
INSERT INTO city VALUES ('Cork',GeomFromText('POINT(5 2)'),'ie');
INSERT INTO city VALUES ('Limerick',GeomFromText('POINT(4 4)'),'ie');
INSERT INTO city VALUES ('Galway',GeomFromText('POINT(4 6)'),'ie');
INSERT INTO city VALUES ('Sligo',GeomFromText('POINT(5 8)'),'ie');
INSERT INTO city VALUES ('Tipperary',GeomFromText('POINT(5 3)'),'ie');
INSERT INTO city VALUES ('Belfast',GeomFromText('POINT(9 9)'),'ni');
INSERT INTO city VALUES ('Londonderry',GeomFromText('POINT(7 10)'),'ni');	

SELECT Area(boundpath)*526 
 AS 'Area (sq miles)' from political_unit, boundary 
 WHERE unitname = 'Northern Ireland'
 AND political_unit.unitcode = boundary.unitcode; 

SELECT GLength(LineStringFromWKB(LineString(AsBinary(orig.cityloc),     AsBinary(dest.cityloc))))*23 as 'Distance (miles)'
 FROM city orig, city dest
 WHERE orig.cityname = 'Belfast' 
 AND dest.cityname = 'Londonderry';

SELECT north.cityname FROM city north
WHERE not exists
  (select * from city other where Y(other.cityloc) > Y(north.cityloc)); 
				

* INTERVAL not yet implemented in MySQL

CREATE TABLE planet (
 pltname VARCHAR(7),
 pltday INTERVAL,
 pltyear INTERVAL,
 CONSTRAINT pk_planet PRIMARY KEY(pltname));

INSERT INTO planet VALUES ('Mercury','1407.51 hours','0.24 years');
INSERT INTO planet VALUES ('Venus','-5832.44 hours','0.62 years');
INSERT INTO planet VALUES ('Earth','23.93 hours','1.00 years');
INSERT INTO planet VALUES ('Mars','24.62 hours','1.88 years');
INSERT INTO planet VALUES ('Jupiter','9.92 hours','11.86 years');
INSERT INTO planet VALUES ('Saturn','10.66 hours','29.45 years');
INSERT INTO planet VALUES ('Uranus','17.24 hours','84.02 years');
INSERT INTO planet VALUES ('Neptune','16.11 hours','164.79 years');
INSERT INTO planet VALUES ('Pluto','153.28 hours', '247.92 years');

SELECT pltname, pltyear FROM planet;