Create four tables called PET_2017, SKU_2017, RETAIL_ORDER_2017 and ORDER_ITEM_2017 in your test database using the CREATE and INSERT Statements below.

-- ------------------------------------------------------------------------

Pet_2017 Table

CREATE TABLE IF NOT EXISTS `PET_2017` (

`Name` varchar(16) NOT NULL DEFAULT '',

`Owner` varchar(32) DEFAULT NULL,

`Species` varchar(16) DEFAULT NULL,

`Gender` char(1) DEFAULT NULL,

`BirthDate` char(10) DEFAULT NULL,

`DeathDate` char(10) DEFAULT NULL,

PRIMARY KEY (`Name`)

) ENGINE=MyISAM;

INSERT INTO `PET_2017` (`Name`, `Owner`, `Species`, `Gender`, `BirthDate`, `DeathDate`) VALUES

('Fluffy', 'Harold', 'cat', 'f', '1993-02-04', NULL),

('Claws', 'Gwen', 'cat', 'm', '1994-03-17', NULL),

('Buffy', 'Harold', 'dog', 'f', '1989-05-13', NULL),

('Fang', 'Benny', 'dog', 'm', '1990-08-27', NULL),

('Bowser', 'Diane', 'dog', 'm', '1979-08-31', '1995-07-29'),

('Chirpy', 'Gwen', 'bird', 'f', '1998-09-11', NULL),

('Whistler', 'Gwen', 'bird', NULL, '1997-12-09', NULL),

('Slim', 'Benny', 'snake', 'm', '1996-04-29', NULL),

('Puffball', 'Diane', 'hamster', 'f', '1999-03-30', NULL);

-- ------------------------------------------------------------------------

SKU_2017 Table

DROP TABLE IF EXISTS SKU_2017;

CREATE TABLE SKU_2017 (

SKU CHAR(6),

SKUDescriptionVARCHAR(32),

DepartmentVARCHAR(16),

BuyerVARCHAR(16),

PRIMARY KEY (SKU)

);

INSERT INTO SKU_2017 VALUES

('100100','Std Scuba Tank, Yellow','Water Sports','Pete Hansen'),

('100200','Std Scuba Tank, Magenta','Water Sports','Pete Hansen'),

('101100','Dive Mask, Small, Clear','Water Sports','Nancy Myers'),

('101200','Dive Mask, Medium, Clear','Water Sports','Nancy Myers'),

('201000','Half-dome Tent','Camping','Cindy Lo'),

('202000','Half-dome Tent Footprint','Camping','Cindy Lo'),

('301000','Light Fly Climbing Harness','Camping','Jerry Martin'),

('302000','Locking carabiner, oval','Camping','Jerry Martin');

-- ------------------------------------------------------------------------

RETAIL_ORDER_2017 Table

DROP TABLE IF EXISTS RETAIL_ORDER_2017;

CREATE TABLE RETAIL_ORDER_2017 (

OrderNumberCHAR(4),

StoreNumberCHAR(2),

StoreZipCHAR(5),

OrderMonthVARCHAR(12),

OrderYearCHAR(4),

OrderTotalINT,

PRIMARY KEY (OrderNumber)

);

INSERT INTO RETAIL_ORDER_2017 VALUES

('1000', '10','98110', 'December', '2003', 445),

('2000', '20','02355', 'December', '2003', 310),

('3000', '10','98110', 'January', '2004', 480);

-- ------------------------------------------------------------------------

ORDER_ITEM_2017 Table

DROP TABLE IF EXISTS ORDER_ITEM_2017;

CREATE TABLE ORDER_ITEM_2017 (

OrderNumber VARCHAR(4),

SKU CHAR(6),

Quantity INT,

Price DOUBLE (16,2),

ExtendedPrice DOUBLE (16,2)

);

INSERT INTO ORDER_ITEM_2017 VALUES

('3000','100200',1,300,300),

('2000','101100',4,50,200),

('3000','101100',2,50,100),

('2000','100200',2,50,100),

('3000','100200',1,50,50),

('1000', '202000',1,130,130);

(Note: ExtendedPrice is Quantity*price)

Write MySQL queries for the following questions. For each Question, write the sql query, execute it on phpmyadmin and copy/paste the SQL code and the table output from running the query next to the English language query below. The first one is done as example and shown below in the picture. Answer the questions in the picture.

1. Show all the data from the table SKU 2017 SELECT FROM SKU 2017 F Cey Deletie 100100 Std Scuba Tnk Yello Waner Spoets Pete Hansen Water Spoets Petc Hansen Water Sperts Corv Delst 101100 Dive Mask, Smaill Clear Ede Ede Dikk 201000 Halfdorne Tent Ha Dela 202000 Half-dome Tent Nancy Myers r Dtlaf 101200 Dive Mask, Mlim Clear Water Sports Nancy Myers Camping Cindy Lo r EdCoex Delete 301000 Ligt Fly Clmbing Serry Hamess Edt Co Delete 302000 Locking carabte,oval C spag Jetty 2. Show only the Department and Buyer data from SKU 2017 3. Show all data from SKU_2017 for the 'Water Sports 4. Show all data from SKU 2017 for SKU> 200000 5. Show SKUDescription and Department from SKU 2017 for 'Camping department. 6. SKUDescription and Buyer from SKU 2017 for 'Camping 7. Show all the data from the ORDER ITEM 2017 table in the ascending order of OrderNumber 8. Show all the data from the ORDER ITEM 2017 table in the ascending order of OrderNumber and within that ascending order of Price. 9. Show all data from SKU 2017 where the department is Water Sports' and the buyer is 'Nancy Myers 10. Show all data from SKU_2017 where the buyer is 'Nancy Myers' or 'Cindy Lo 11. Show all data from SKU_2017 where the buyer is 'Nancy Myers' or 'Cindy Lo' or Jerry Martin 12. Show all data from SKU 2017 where the buyer is someone other than 'Nancy Myers' or 'Cindy Lo' or Jerry