/* code/sql/HW_Storm.sql */
DROP SCHEMA IF EXISTS HW_STORM;

CREATE SCHEMA HW_STORM;

USE HW_STORM;

-- start snippet schema
CREATE TABLE STORM (
  NAME VARCHAR(25) PRIMARY KEY,
  Kind ENUM ("Tropical
    Storm", "Hurricane"),
  WindSpeed INT,
  Creation DATE
);

-- We can change the enumerated datatype:
ALTER TABLE STORM MODIFY Kind ENUM ("Tropical Storm",
  "Hurricane", "Typhoon");

CREATE TABLE STATE (
  NAME VARCHAR(25) UNIQUE,
  Postal_abbr CHAR(2) PRIMARY KEY,
  Affected_by VARCHAR(25),
  FOREIGN KEY (Affected_by) REFERENCES STORM (NAME) ON
    DELETE SET NULL ON UPDATE CASCADE
);

--end snippet schema
-- start snippet insert
INSERT INTO STORM
VALUES (
  "Harvey",
  "Hurricane",
  130,
  "2017-08-17");

-- In the following, the entry gets created, but date is
--		   "corrected" to "2017-17-08"!
--		   INSERT INTO STORM
--		     VALUES ("Dummy", "Hurricane", 120,
--	   "2017-17-08");
--		    The error message returned is
--		    ERROR 1292 (22007) at line 34:
-- Incorrect
--      date
--	     value:
--		   "2017-17-08" for column
--	     `HW_STORM`.`STORM`.`Creation`
--		at
--		   row 1
--		    In the following, we explicitely use
--     "DATE",
--	 and
--	       since
--		   the date is incorrect, nothing gets
--     inserted.
--		   INSERT INTO STORM
--		     VALUES ("Dummy2", "Hurricane", 120,
--  DATE
--		 "2017-17-08");
--		    ERROR 1525 (HY000) at line 40:
-- Incorrect
--      DATE
--	     value:
--		   "2017-17-08"
--		    The next one sets NULL for DATE.
INSERT INTO STORM
VALUES (
  "Irma",
  "Tropical Storm",
  102,
  DEFAULT);

-- end snippet insert
-- start snippet date
INSERT INTO STATE
VALUES (
  "Georgia",
  "GA",
  NULL);

INSERT INTO STATE
VALUES (
  "Texas",
  "TX",
  NULL);

INSERT INTO STATE
VALUES (
  "Florida",
  "FL",
  NULL);

-- This instruction is not using the primary key, is that a
--		   problem?
UPDATE
  STATE
SET Affected_by = "Harvey"
WHERE Name = "Georgia";

UPDATE
  STORM
SET Name = "Harley"
WHERE Name = "Harvey";

DELETE FROM STORM
WHERE Name = "Harley";

-- end snippet date