PART 1: ALREADY COMPLETED. NEED PART 2

Tasks

Query Description	SQL Statement
Create a new database in MySQL called horsestable. List the databases in MySql. Result set should look like:	Create database horsestable; Show databases;
Change to us the database horsestable.	Use horsestable;
Show the current tables in database horsestable. Result set should look like:	Show tables;
Create a table in database horsestable called horse with the following attributes, data types, and constraints: ID, integer, not null, auto increment registered name, variable character - 15 characters barn name, variable character 20 characters, not null breed, variable character 20 characters age, int height, three numbers with one to the right of the decimal point start date, date, not null primary key is the ID field	create table horse ( -> ID INT NOT NULL AUTO_INCREMENT, -> registered_name VARCHAR(15), -> barn_name VARCHAR(20) NOT NULL, -> breed VARCHAR(20), -> age INT, -> height DECIMAL(2,1), -> PRIMARY KEY(ID) -> );
Create a table in database horsestable called boarder with the following attributes, data types, and constraints: ID, integer, not null, auto increment first name, variable character - 20 characters, not null last name, variable character 30 characters, not null address, variable character 30 characters, not null city, variable character 20 characters, not null state, character 2 characters zip, int, not null primary phone, character 10 characters, not null email address, variable character 30 characters primary key is the ID field	CREATE TABLE boarder( -> ID INT NOT NULL AUTO_INCREMENT, -> first_name VARCHAR(20) NOT NULL, -> last_name VARCHAR(30) NOT NULL, -> address VARCHAR(30) NOT NULL, -> city VARCHAR(20) NOT NULL, -> state CHAR(2), -> zip INT NOT NULL, -> primary_phone CHAR(10) NOT NULL, -> email_address VARCHAR(30), -> PRIMARY KEY(ID) -> );
Create a table in database horsestable called student with the following attributes, data types, and constraints: ID, integer, not null, auto increment first name, variable character - 20 characters, not null last name, variable character 30 characters, not null primary phone, character 10 characters, not null email address, variable character 30 characters primary key is the ID field	CREATE TABLE student( -> ID INT NOT NULL AUTO_INCREMENT, -> first_name VARCHAR(20) NOT NULL, -> last_name VARCHAR(30) NOT NULL, -> primary_phone CHAR(10) NOT NULL, -> email_address VARCHAR(30), -> PRIMARY KEY (ID) -> );
Create a table in database horsestable called grain with the following attributes, data types, and constraints: ID, integer, not null, auto increment manufacturer, variable character - 30 characters, not null product, variable character 30 characters, not null weight, integer Cost, decimal with 5 values, 2 to the right of the decimal point, not null primary key is the ID field	CREATE TABLE grain( -> ID INT NOT NULL AUTO_INCREMENT, -> manufacturer VARCHAR(30) NOT NULL, -> product VARCHAR(30) NOT NULL, -> weight INT, -> cost DECIMAL(5,2) NOT NULL, -> PRIMARY KEY (ID) -> );
Create a table in database horsestable called hay with the following attributes, data types, and constraints: ID, integer, not null, auto increment type, enumeration with values of 'Coastal','Timothy','Orchard','Alfalfa','Peanut','Bermuda','Oat','Barley' size, enumeration with values of 'Two string', 'Three string' Cost, decimal with 5 values, 2 to the right of the decimal point, not null Weight, integer, not null primary key is the ID field	CREATE TABLE hay( -> ID INT NOT NULL AUTO_INCREMENT, -> type ENUM('COASTAL', 'TIMOTHY', 'ORCHARD', 'ALFALFA', 'PEANUT', 'BERMUDA', 'OAT', 'BARLEY'), -> size ENUM('Two string', 'Three string'), -> Cost DECIMAL(5,2) NOT NULL, -> Weight INT NOT NULL, -> PRIMARY KEY (ID) -> );
Create a table in database horsestable called bedding with the following attributes, data types, and constraints: ID, integer, not null, auto increment type, enumeration with values of 'Pellets', 'Shavings', 'Straw' size, integer cost, decimal with 5 values, 2 to the right of the decimal point, not null primary key is the ID field	CREATE TABLE bedding( -> ID INT NOT NULL AUTO_INCREMENT, -> type ENUM('Pellets', 'Shavings', 'Straw'), -> size INT, -> cost DECIMAL(5,2) NOT NULL, -> PRIMARY KEY (ID) -> );
Create a table in database horsestable called staff with the following attributes, data types, and constraints: ID, integer, not null, auto increment first name, variable character - 20 characters, not null last name, variable character 30 characters, not null primary phone, character 10 characters, not null email address, variable character 30 characters type, enumeration with values of Trainer, Groom, Barn Staff, Maintenance primary key is the ID field	CREATE TABLE staff ( ID INT NOT NULL AUTO_INCREMENT, First_name VARCHAR(20) NOT NULL, Last_name VARCHAR(30) NOT NULL, Primary_phone CHAR(10) NOT NULL, Email_address VARCHAR(30), Type ENUM(Trainer, Groom, Barn Staff, Maintenance), PRIMARY KEY (ID) );
Create a table in database horsestable called stall with the following attributes, data types, and constraints: stall_id, integer, not null, auto increment stall_number, enumeration with values of 1,2,3,4,5,6,7,8,9,10 base_price, floating point, not null, 5 digits, 2 digits to the right of the decimal point primary key is the stall_id field	CREATE TABLE stall( -> stall_id INT NOT NULL AUTO_INCREMENT, -> stall_number ENUM('1,2,3,4,5,6,7,8,9,10'), -> base_price DECIMAL(5,2), -> PRIMARY KEY (stall_id) -> );
Create a table in database horsestable called pasture with the following attributes, data types, and constraints: pasture_id, integer, not null, auto increment pasture_number, enumeration with values of 1,2,3,4 base_price, floating point, not null, 5 digits, 2 digits to the right of the decimal point primary key is the pasture_id field	CREATE TABLE pasture( -> pasture_id INT NOT NULL AUTO_INCREMENT, -> pasture_number ENUM('1,2,3,4'), -> base_price DECIMAL(5,2), -> PRIMARY KEY (pasture_id) -> );

PART 2:

Tasks

DML Description	SQL Statement
Insert records into table horse given the following data relative to fields (registered_name, barn_name, breed, age, height) NOTE: if the registered name is NA then the horse does not have a registered name and this field should be eliminated from the insert statement: Babe's Blazing Pine, Blaze, Quarter Horse,11,15.3, July 1 2016 Sweet Peppy, Cruise, Paint,13,15.3, July 1 2016 Sagitario, Sag, Lusitano, 19, 15.3, July 1 2016 NA, Zico, Lusitano,17,15.3, January 1 2008 Independence Hill, Indy, Holsteiner,2,16, March 1 2017 NA, Kamen, Holsteiner,12,18, May 1 2018 NA, Thunder, Paint,15,16, September 1 2017 NA, Allie, Saddlebred,20,15.3, April 1 2018 NA, Jet, Egyptian Arab,12,15.3, August 1 2013 NA, Timothy, Quarter Horse Pony,5,10, April 1 2018 NA, Johnny, Thoroughbred,12,16, September 1 2017 NA, Carera, Holsteiner,3,17, March 1 2018
Insert records into table staff given the following data relative to fields (first_name, last_name, primary_phone, email, position): Martin, Brett, 4071234567, martinbrett@email.com, Maintenance Karin, Whiting, 4072345678, karinwhiting@email.com, Barn Vanessa, Talcott, 4073456789, vanessatalcott@email.com, Trainer Bruce, Patti, 4074567890, brucepatti@email.com, Trainer
Insert records into table hay given the following data relative to fields (type, size, cost, weight): Coastal, Two string, 45.00, 50 Timothy,Two string, 65.00, 50 Orchard,Three string, 35.00, 100 Alfalfa,Three string, 67.00, 100 Peanut,Two string, 35.00, 50 Bermuda,Three string, 30.00, 100 Oat,Two string, 40.00, 50 Barley,Two string, 50.00, 50
Insert records into table bedding given the following data relative to fields (type, size, cost): Pellets,30,6.50 Shavings,40,8.00 Straw,50,10.00
Insert records into table grain given the following data relative to fields (manufacturer, product, weight, cost): Nutrena,ProForce Fuel,50,19.25 Nutrena,ProForce XTN,50,23.99 Nutrena,ProForce Senior,50,24.99 Nutrena,ProForce Fiber,50,23.99
Insert records into table boarder given the following data relative to fields (first_name, last_name, address, city, state, zip, primary_phone, email): Karin, Whiting,1234 Street Name, Orlando,FL,32825,4071234678,karinwhiting@email.com Vanessa, Talcott,123 Road Name, Orlando,FL,32826,4072345678,vanessatalcott@email.com Bruce, Patti,2775 East Oscelosa Road, Geneva,FL,32820,4073912009,brucepatti@email.com Maria, Gonzalez,234 West Boulevard, Oviedo,FL,32821,4075557894,mariagonzalez@email.com Nancy, Smith,479 East Boulevard, Oviedo,FL,32821,4075697412,nancysmith@email.com Julia, Yancy,5795 Avenue Name, Sanford,FL,32751,4075670112,juliayancy@email.com Mystery, Owner,0000 Mystery Street, Mystery,FL,32751,4077044740,mystry@email.com Jennifer, Jumper,I-4 Corridor, Orlando,FL,32756,4079875412,jenniferjumper@email.com Alix, Kamen,Nightmare Before Christmas, Bithlo,FL,32666,4073578521,alixkamen@email.com
Insert records into table student given the following data relative to fields (first_name, last_name, primary_phone, email): Lauren, White, 4074589687, laurenwhiteg@email.com Lorisa, Grey, 4077894561, lorisagrey@email.com Rebecca, Black, 4070147567, rebeccablack@email.com Alice, Brett, 4070140026, alicebrett@email.com Heidi, Horse, 4077536987, heidihorse@email.com Misty, Lane, 4071090321, mistylane@email.com Deborah, Twohorse, 4078521470, debbytwohorse@email.com Catherine, Neighbor, 3215211478, catherinesparents@email.com
Insert records into table stall given the following data relative to fields (stall_number, base_price): 1, 150.00 2, 150.00 3, 150.00 4, 150.00 5, 150.00 6, 150.00 7, 150.00 8, 150.00 9, 150.00 10, 150.00
Insert records into table pasture given the following data relative to fields (pasture_number, base_price): 1, 75.00 2, 75.00 3, 150.00 4, 100.00