PART 1: ALREADY COMPLETED. NEED PART 2 Tasks Query Description SQL Statement Cre

Question

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;

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)

    -> );

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)

    -> );

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)

    -> );

ID, integer, not null, auto increment

type, enumeration with values of 'Pellets', 'Shavings', 'Straw'

size, integer

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

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)

);

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)

    -> );

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

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

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

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

Pellets,30,6.50

Shavings,40,8.00

Straw,50,10.00

Nutrena,ProForce Fuel,50,19.25

Nutrena,ProForce XTN,50,23.99

Nutrena,ProForce Senior,50,24.99

Nutrena,ProForce Fiber,50,23.99

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

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

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)

    -> );

Explanation / Answer

Answers:-

1. Here INSERT IGNORE used to prevent insertion of NULL value where the column has a NOT NULL constraint.

INSERT IGNORE INTO horse(registered_name, barn_name, breed, age, height)

VALUES(Babe's Blazing Pine, Blaze, Quarter Horse,11,15.3),

(Sweet Peppy, Cruise, Paint,13,15.3),

(Sagitario, Sag, Lusitano, 19, 15.3),

(NA, Zico, Lusitano,17,15.3),

(Independence Hill, Indy, Holsteiner,2,16),

(NA, Kamen, Holsteiner,12,18),

(NA, Thunder, Paint,15,16),

(NA, Allie, Saddlebred,20,15.3),

(NA, Jet, Egyptian Arab,12,15.3),

(NA, Timothy, Quarter Horse Pony,5,10),

(NA, Johnny, Thoroughbred,12,16),

(NA, Carera, Holsteiner,3,17);

2. INSERT INTO staff(first_name, last_name, primary_phone, email, position)

VALUES(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);

3: INSERT INTO hay(type, size, cost, weight)

VALUES(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);

4. INSERT INTO bedding(type, size, cost)

VALUES(Pellets,30,6.50),

(Shavings,40,8.00),

(Straw,50,10.00);

5. INSERT INTO grain(manufacturer, product, weight, cost)

VALUES(Nutrena,ProForce Fuel,50,19.25),

(Nutrena,ProForce XTN,50,23.99),

(Nutrena,ProForce Senior,50,24.99),

(Nutrena,ProForce Fiber,50,23.99);

6. INSERT INTO boarder(first_name, last_name, address, city, state, zip, primary_phone, email)

VALUES(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);

7. INSERT INTO student(first_name, last_name, primary_phone, email)

VALUES(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);

8. INSERT INTO stall(stall_number, base_price)

VALUES(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);

9. INSERT INTO pasture(pasture_number, base_price)

VALUES(1, 75.00),
(2, 75.00),
(3, 150.00),
(4, 100.00);

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.