-- お天気日記テーブルの作成
DROP TABLE otenki;
CREATE TABLE otenki (
	day DATE DEFAULT 'today' PRIMARY KEY, -- 日付(主キー)
	tenki TEXT DEFAULT '晴れ',	-- 天気(晴れ、曇...)
	ondo INTEGER DEFAULT 25 CHECK(ondo IS NULL OR ONDO > -50 AND ONDO < 50),	-- 温度
	uryou INTEGER DEFAULT 0 CHECK(uryou IS NULL OR URYOU >= 0),		-- 雨量
	CONSTRAINT otenki_check
	CHECK(tenki IS NULL OR
	(tenki = '晴れ' AND uryou = 0 OR tenki = '曇' OR tenki = '雨'))
);
INSERT INTO otenki VALUES('2002-8-1','晴れ',30,0);
INSERT INTO otenki VALUES('2002-8-2','晴れ',30,0);
INSERT INTO otenki VALUES('2002-8-3','曇',27,10);
INSERT INTO otenki VALUES('2002-8-4','曇',27,8);
INSERT INTO otenki VALUES('2002-8-5','曇',26,7);
INSERT INTO otenki VALUES('2002-8-6','晴れ',29,0);
INSERT INTO otenki VALUES('2002-8-7','雨',27,120);
INSERT INTO otenki VALUES('2002-8-8','雨',25,140);
INSERT INTO otenki VALUES('2002-8-9','雨',25,200);
INSERT INTO otenki VALUES('2002-8-10','雨',25,100);
INSERT INTO otenki VALUES('2002-8-11','曇',27,10);