Question 3: Find the people who do not have Viper Certification but are still as

Question

Question 3: Find the people who do not have Viper Certification but are still assigned to Viper class ship

Find the fname, lname, and ship_instance_id for all people who do not have Viper certification 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. Order your results by fname in ascending order.

Use the BSG database from the Quiz Description.

I have gotten this far but all of the output isn't correct. thank you for the help!

Explanation / Answer

Sql Query:
/* Getting only first name last name and ids*/
SELECT p.fname, p.lname, sk.id FROM bsg_people p LEFT JOIN bsg_cert_people cp ON cp.pid = p.id LEFT/*Using left join to join the tables*/
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

Order by p.fname;

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

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