drop view dog_v;
drop table dog;

create table dog (
	code integer primary key,
	name text not null,
	sex integer not null check(sex in (1, 2)),
	weight float not null check(weight > 0),
	kind text,
	color text
);
insert into dog values(1, 'チャア', 2, 3.8, 'チワワ', '灰色');
insert into dog values(2, 'ごんた', 1, 43.5, '秋田犬', null);
insert into dog values(3, 'ジミー', 1, 9.3, null, '黄土色');
insert into dog values(4, 'ベティ', 2, 22.8, null, null);

create view dog_v as
	select
			code as 番号,
			name as 名前,
			case sex
				when 1 then 'オス'
				when 2 then 'メス'
			end as 性別,
			case
				when weight >= 36 then '超大型犬'
				when weight >= 18 then '大型犬'
				when weight >= 6 then '中型犬'
				else '小型犬'
			end as 大きさ,
			coalesce(kind, '') || coalesce(color, '') as 特徴
		from dog
;