Find the fname, lname and ship_instance id of all people who do not have Viper c

Question

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.

Battlestar Schema

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.

bsg cert INT bsg cert people title VARCHAR(255) INT INT fname VARCHAR( 25 Inam e VARCHAR(255) homeworld INT age INT INT bsg_planets INT name VARCHAR(255) populaton BIGINT(20) anguage VARCHAR (255) capital VARCHAR(2 bsg_ship_class INT name VARCHAR (1000) VAROHAR(1000) lbsg_ship_assignment LOA INT INT INT sid INT ew_capacity INT bsg_ship_instance INT bsg ship_assignment is associated to bsg_ship_instance using a composite key. cid references bsg_ship_instance.dass and sid references bsg_ship_instance.id. ass INT operatonal TINYINT(1) INT based in_lass INT (11) bsg ship_instance has a safrefere composite foreign key which references its own composi mary key

Explanation / Answer

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?

SELECT p.fname, p.lname, sk.id FROM bsg_people p LEFT JOIN bsg_cert_people cp ON cp.pid = p.id LEFT
JOIN bsg_cert c ON c.id = cp.cid AND c.title = 'viper' JOIN bsg_ship_assignment ps ON ps.pid = p.id
JOIN bsg_ship_instance sk ON sk.id = ps.sid AND sk.class = ps.cid JOIN bsg_ship_class k ON k.id = sk.class WHERE k.name = 'viper'AND c.id IS NULL;

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

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