CREATE TABLE EXPLORER$($

$$ ExplorerID$$ smallint not null,

$$ ExplrName$$ VARCHAR$(30)$ NOT NULL,

$$ Age$$ smallint,

$$ FromEarth$$ tinyint,

$$ Military$$ tinyint,

$$ CONSTRAINT Explorer$\_$pk PRIMARY KEY$($ExplorerID$)$

$)$;

$$

CREATE TABLE RESOURCE$($

$$ ResourceID INT auto$\_$increment,

$$ Category$$ varchar$(20)$ CHECK $($Category IN$(\text{'}$Gold$\text{'},$'Medicine','Rare Mineral','New Weapon','Technology','Other'$)),$

$$ DateOfMission$$ date,

$$ ExplorerID$$ smallint,$$

$$ CONSTRAINT ResourceID$\_$pk PRIMARY KEY$($ResourceID$),$

$$ constraint mission$\_$fk FOREIGN KEY$($ExplorerID$)$ REFERENCES EXPLORER$($ExplorerID$)$ ON DELETE CASCADE

$)$;

$$

CREATE TABLE VESSEL$($

$$ VesselID$$ SMALLINT NOT NULL,

$$ VesselName VARCHAR$(30),$

$$ NumOfLaserCannons$$ smallint,

$$ VesselType$$ varchar$(20),$

$$ CONSTRAINT Vessel$\_$pk PRIMARY KEY$($VesselID$)$

$)$;

$$

ALTER TABLE VESSEL ADD CONSTRAINT check$\_$Vessel$\_$type CHECK $($VesselType IN$(\text{'}$Interceptor$-$class','Excelsior$-$class','Galaxy$-$class','Defiant$-$class','Nebula$-$class'$))$;

$$

CREATE TABLE RESERVATION$($

$$ ReservationNum INT$$ AUTO$\_$INCREMENT,

$$ CheckOutDate$$ DATE,

$$ CheckInDate$$ DATE,

$$ ReturnDueDate$$ DATE,

$$ ExplorerID$$ smallint,

$$ VesselNum$$ smallint,$$

$$ CONSTRAINT reservation$\_$pk PRIMARY KEY$($ReservationNum$),$

$$ CONSTRAINT reservation$\_$fk$1$ FOREIGN KEY $($ExplorerID$)$ REFERENCES EXPLORER$($ExplorerID$)$ ON DELETE CASCADE,

$$ CONSTRAINT reservation$\_$fk$2$ FOREIGN KEY $($VesselNum$)$ REFERENCES VESSEL$($VesselID$)$ ON DELETE CASCADE$$

$)$ ;

$$

##EXPLORER rows

INSERT INTO EXPLORER$($ExplorerID$,$ ExplrName, Age,FromEarth, Military$)$ VALUES $(1,$'Chris Aldrin',$41,0,0)$;

INSERT INTO EXPLORER$($ExplorerID$,$ ExplrName, Age,FromEarth, Military$)$ VALUES $(2,$'Peggy Glenn',$30,0,0)$;

INSERT INTO EXPLORER$($ExplorerID$,$ ExplrName, Age,FromEarth, Military$)$ VALUES $(3,$'Buzz Armstrong',$38,1,1)$;

INSERT INTO EXPLORER$($ExplorerID$,$ ExplrName, Age,FromEarth, Military$)$ VALUES $(4,$'Alan Gagarin',$24,0,0)$;

INSERT INTO EXPLORER$($ExplorerID$,$ ExplrName, Age,FromEarth, Military$)$ VALUES $(5,$'Michael Hadfield',$40,0,1)$;

INSERT INTO EXPLORER$($ExplorerID$,$ ExplrName, Age,FromEarth, Military$)$ VALUES $(6,$'Mae Ride',$37,1,0)$;

INSERT INTO EXPLORER$($ExplorerID$,$ ExplrName, Age,FromEarth, Military$)$ VALUES $(7,$'Yuri Whitson',$51,0,0)$;

INSERT INTO EXPLORER$($ExplorerID$,$ ExplrName, Age,FromEarth, Military$)$ VALUES $(8,$'John Jemison',$43,0,0)$;

INSERT INTO EXPLORER$($ExplorerID$,$ ExplrName, Age,FromEarth, Military$)$ VALUES $(9,$'Neil Shepard',$26,1,0)$;

INSERT INTO EXPLORER$($ExplorerID$,$ ExplrName, Age,FromEarth, Military$)$ VALUES $(10,$'Sally Collins',$34,1,1)$;

$$

##VESSEL rows

INSERT INTO VESSEL$($VesselID$,$ VesselName, NumOfLaserCannons,VesselType$)$ VALUES $(1000,$'Prometheus',$18,$'Excelsior$-$class'$)$;

INSERT INTO VESSEL$($VesselID$,$ VesselName, NumOfLaserCannons,VesselType$)$ VALUES $(1001,$'Exeter',$36,$'Interceptor$-$class'$)$;

INSERT INTO VESSEL$($VesselID$,$ VesselName, NumOfLaserCannons,VesselType$)$ VALUES $(1002,$'Rotarran',$34,$'Galaxy$-$class'$)$;

INSERT INTO VESSEL$($VesselID$,$ VesselName,

NumOfLaserCannons,VesselType$)$ VALUES $(1003,$'Venture',$40,$'Nebula$-$class'$)$;

INSERT INTO VESSEL$($VesselID$,$ VesselName, NumOfLaserCannons,VesselType$)$ VALUES $(1004,$'Valiant',$46,$'Galaxy$-$class'$)$;

INSERT INTO VESSEL$($VesselID$,$ VesselName, NumOfLaserCannons,VesselType$)$ VALUES $(1005,\text{'}$D$\text{'}\text{'}$deridex$\text{'},30,$'Excelsior$-$class'$)$;

INSERT INTO VESSEL$($VesselID$,$ VesselName, NumOfLaserCannons,VesselType$)$ VALUES $(1006,$'Bajoran',$40,$'Nebula$-$class'$)$;

INSERT INTO VESSEL$($VesselID$,$ VesselName, NumOfLaserCannons,VesselType$)$ VALUES $(1007,$'Maquis Raider',$18,$'Defiant$-$class'$)$;

INSERT INTO VESSEL$($VesselID$,$ VesselName, NumOfLaserCannons,VesselType$)$ VALUES $(1008,$'Enterprise',$12,$'Interceptor$-$class'$)$;

INSERT INTO VESSEL$($VesselID$,$ VesselName, NumOfLaserCannons,VesselType$)$ VALUES $(1009,$'Negh''Var',$18,$'Galaxy$-$class'$)$;

INSERT INTO VESSEL$($VesselID$,$ VesselName, NumOfLaserCannons,VesselType$)$ VALUES $(1010,$'Lexington',$33,$'Nebula$-$class'$)$;

$$

$4.$ Write a single query that will list the only Explorer who did NOT travel on a Galaxy$-$class vessel. The result set should have $1$ row and display the $$ExplrName$$ column only. Hint: Your query can use subqueries$/$ table joins$/$ or both.

##Paste below: $1)$ copy your SQL code out of mySQL WB $2)$ screenshot of the result set displayed

SQL QUERY CODE $($to display the result set$)$:

RESULT SET SCREENSHOT:

$5.$ Write a single query that will list the Explorer ID and Name of the Explorers who reserved a Galaxy$-$class but never reserved an Interceptor$-$class. The result set should have $2$ rows. Hint: Nested subqueries

##Paste below: $1)$ copy your SQL code out of mySQL WB $2)$ screenshot of the result set displayed

SQL QUERY CODE $($to display the result set$)$:

RESULT SET SCREENSHOT: