drop table if exists residence;
create table residence
(
 id         bigint not null auto_increment primary key,
 ownername  varchar(20) not null,
 city       varchar(20) not null,
 state      varchar(2) not null
 );
insert into residence values (1, 'James Grishom',     'Tucson',      'AZ'    );
insert into residence values (2, 'Walter Vickers',    'San Diego',   'CA'    );
insert into residence values (3,'Callow Bruce',      'Casa Grande', 'AZ'    );
insert into residence values (4,'Wright Frank',      'Casa Grande', 'AZ'    );
insert into residence values (5,'Hawthorne Seymour', 'Casa Grande', 'AZ'    );
insert into residence values (6,'Ellen Troy',        'Phoenix',     'AZ'    );
insert into residence values (7,'Charles Rogers',    'Miami',       'FL'    );
insert into residence values (8,'Lei Yu',            'Riverside',   'CA'    );
insert into residence values (9,'Anne Nance',        'Los Angeles', 'CA'    );
insert into residence values (10,'Pete Edwards',      'Casa Grande', 'AZ'    );
insert into residence values (11,'Kathy Cloverfield', 'Portland',    'OR'    );
insert into residence values (12,'Victoria Song',     'Denver',      'CO'    );
insert into residence values (13,'Monica Hussain',    'Los Angeles', 'CA'    );
insert into residence values (14,'Wesley Boyd',       'Dallas',      'TX'    );
insert into residence values (15,'Sue Appleton',      'San Antonio', 'TX'    );
insert into residence values (16,'Sandra Hernandez',  'Tucson',      'AZ'    );
insert into residence values (17,'Richard Lomotey',   'Casa Grande', 'AZ'    );
insert into residence values (18,'George Sulter',     'Dallas',      'TX'    );
insert into residence values (19,'Abe Davidson',      'Denver',      'CO'    );
insert into residence values (20,'Qiyam Tung',        'Tucson',      'AZ'    );

drop table if exists plant;
create table plant
(
 id         bigint not null auto_increment primary key,
 plantname  varchar(35) not null,
 price      real not null,
 maxheight  real not null,
 exposure   varchar(15) not null
 );
insert into plant values (1, 'Glossy Abelia',                    6,           6,                 'full sun'    );
insert into plant values (2, 'Grand Fir',                        25,          25,                'full sun'    );
insert into plant values (3, 'Spanish Fir',                      25,          15,                'full sun'    );
insert into plant values (4, 'Blue Spanish Fir',                 25,          15,                'full sun'    );
insert into plant values (5, 'Common Boxwood',                   6,           7,                 'full sun'    );
insert into plant values (6, 'Incense Cedar',                    25,          18,                'full sun'    );
insert into plant values (7, 'Harebell',                         3,           1,                 'full sun'    );
insert into plant values (8, 'Red Laceleaf Japanese Maple',      20,          4,                 'shade'       );
insert into plant values (9, 'Floating Clouds Japanese Maple',   20,          7,                 'shade'       );
insert into plant values (10, 'Green Laceleaf Japanese Maple',    25,          4,                 'shade'       );
insert into plant values (11, 'Blue-Needled Japanese White Pine', 25,          12,                'shade'       );
insert into plant values (12, 'Himalayan White Pine',             25,          18,                'shade'       );
insert into plant values (13, 'Makinoi''s Holly Fern',             4,           2,                 'indoor'      );
insert into plant values (14, 'Sword Fern',                       4,           3,                 'indoor'      );
Insert into plant values (15, 'Asian Saber Fern',                 4,           2,                 'indoor'      );
insert into plant values (16, 'Japanese Tassel Fern',             4,           2,                 'indoor'      );
insert into plant values (17, 'Black Mondo Grass',                3,           0.5,               'indoor'      );
insert into plant values (18, 'Hybrid Sweet Olive',               6,           5,                 'indoor'      );
insert into plant values (19, 'Sweet Olive',                      6,           5,                 'indoor'      );
Insert into plant values (20, 'Spring Vetchling',                 3,           1,                 'partial sun' );
insert into plant values (21, 'Golden Boxleaf Honeysuckle',       6,           5,                 'partial sun' );
insert into plant values (22, 'Box-Leaf Honeysuckle',             6,           2,                 'partial sun' );

drop table if exists installation;
create table installation
(
 id         bigint not null auto_increment primary key,
 ownername  varchar(20) not null,
 plantname  varchar(35) not null,
 location  varchar(10) not null,
 year   int not null
 );
insert into installation values (1, 'James Grishom',     'Glossy Abelia',                    'front',     2010   );
insert into installation values (2, 'Walter Vickers',    'Common Boxwood',                   'back',      2013   );
insert into installation values (3, 'Callow Bruce',      'Incense Cedar',                    'leftside',  2013   );
insert into installation values (4, 'Wright Frank',      'Red Laceleaf Japanese Maple',      'rightside', 2013   );
insert into installation values (5, 'Hawthorne Seymour', 'Floating Clouds Japanese Maple',   'front',     2012   );
insert into installation values (6, 'Ellen Troy',        'Himalayan White Pine',             'back',      2013   );
insert into installation values (7, 'charles Rogers',    'Makinoi''S Holly Fern',             'back',      2010   );
insert into installation values (8, 'Lei Yu',            'Japanese Tassel Fern',             'leftside',  2012   );
insert into installation values (9, 'Anne Nance',        'Black Mondo Grass',                'front',     2013   );
insert into installation values (10, 'Pete Edwards',      'Sweet Olive',                      'rightside', 2013   );
insert into installation values (11, 'Kathy Cloverfield', 'Box-Leaf Honeysuckle',             'leftside',  2012   );
insert into installation values (12, 'Victoria Song',     'Japanese Tassel Fern',             'back',      2010   );
insert into installation values (13, 'Monica Hussain',    'Himalayan White Pine',             'rightside', 2013   );
insert into installation values (14, 'Wesley Boyd',       'Spring Vetchling',                 'leftside',  2010   );
insert into installation values (15, 'Sue Appleton',      'Green Laceleaf Japanese Maple',    'front',     2010   );
insert into installation values (16, 'Sandra Hernandez',  'Black Mondo Grass',                'rightside', 2011   );
insert into installation values (17, 'Richard Lomotey',   'Red Laceleaf Japanese Maple',      'rightside', 2013   );
insert into installation values (18, 'Richard Lomotey',   'Red Laceleaf Japanese Maple',      'front', 2013   );
insert into installation values (19, 'George Sulter',     'Floating Clouds Japanese Maple',   'front',     2012   );
insert into installation values (20, 'Abe Davidson',      'Black Mondo Grass',                'front',     2011   );
insert into installation values (21, 'Qiyam Tung',        'Blue-Needled Japanese White Pine', 'front',     2012   );
