Create a MySQL query to find the fname , lname and ship_instance id of all peopl
ID: 3696363 • Letter: C
Question
Create a MySQL query to find the fname, lname and ship_instance id of all people who do not have Viper certificaton but are assigned to at least one instance of a Viper class ship (this includes all variants of Viper class ships). Return a row for every ship/person combination.
Below you will find the schema assoicated to the Battlestar Galactica schema. This schema essentially keeps track of data for a fleet of space ships. bsg_people are the individuals on the ships. bsg_planets are planets that those people may have originally came from. bsg_cert is a list of certifications the people may have, it has a standard relationship table linking it to bsg_people. bsg_ship_assignment lists the ship instances individuals may be assigned to.
Ships are the most complex piece of this database. Ships are broken into classes which are fruther broken down into variants. So there is a Viper class of ships, but there are several variants, for example the Mk II and the MK VII, each variant having its own class ID but sharing the class name. Then there are ship instances. The ship instance has a class and an id as a composite key. Additionaly a ship can be based within another ship. In this cas the table references itself to indicate which ship instanced an instance is based in.
If you wish to use the database locally or see how it was made, you can use the following file to load it into a MySQL server instance: http://classes.engr.oregonstate.edu/eecs/winter2013/cs275-400/tester/BSG_Backup.sql
Explanation / Answer
SELECT bsg_people.fname, bsg_people.lname, bsg_ship_instance.id FROM bsg_people
INNER JOIN bsg_ship_instance ON bsg_ship_assignment.sid = bsg_ship_instance.id
INNER JOIN bsg_ship_assignment ON bsg_ship_assignment.pid = bsg_people.id
INNER JOIN bsg_ship_class ON bsg_ship_class.id = bsg_ship_instance.class
WHERE bsg_people.id NOT IN
(
SELECT bsg_cert_people.pid FROM bsg_cert_people
INNER JOIN bsg_cert ON bsg_cert_people.cid = bsg_cert.id
WHERE bsg_cert.title = 'Viper'
)
AND bsg_ship_class.name = 'viper'