Question

Using SQL and the following info

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!

select fname,lname from bsg_people p,bsg_ship_assignment a,bsg_ship_instance i,bsg_ship_class c where p.id=a.pid and a.sid=i.id and i.class=c.id and c.name='Viper' group by a.pid;


CREATE TABLE `bsg_cert` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

CREATE TABLE `bsg_cert_people` (
  `cid` int(11) NOT NULL DEFAULT '0',
  `pid` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`cid`,`pid`),
  KEY `pid` (`pid`),
  CONSTRAINT `bsg_cert_people_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `bsg_cert` (`id`),
  CONSTRAINT `bsg_cert_people_ibfk_2` FOREIGN KEY (`pid`) REFERENCES `bsg_people` (`id`)
) ENGINE=InnoDB

CREATE TABLE `bsg_people` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fname` varchar(255) NOT NULL,
  `lname` varchar(255) DEFAULT NULL,
  `homeworld` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `homeworld` (`homeworld`),
  CONSTRAINT `bsg_people_ibfk_1` FOREIGN KEY (`homeworld`) REFERENCES `bsg_planets` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB

CREATE TABLE `bsg_planets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `population` bigint(20) DEFAULT NULL,
  `language` varchar(255) DEFAULT NULL,
  `capital` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB

Answer 1

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 fname;