SQL query assistance: Find the fname and lname of all people who are assigned to

Question

SQL query assistance:

Find the fname and lname of all people who are assigned to at least one instance of a Viper class ship (this includes all variants of Viper class ships). Return only a single row per person.

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.

image of the schema: http://classes.engr.oregonstate.edu/eecs/winter2013/cs275-400/tester/bsgSchema.html

bsg cert id INT(11) bsg cert people title VARCHAR(255) bsg people cid INT(11) id INT(110 finame VARCHAR(255) bsg planets Iname VARCHAR(255) homeworld INT(11) d INT(11) name VARCHAR (255) age INT(11) population BIGINT(20) anguage VARCHAR (255) capital VARCHAR(255) bsg ship class 1 id INT(11) name VARCHAR(1000) variant VARCHAR(1000) bsg ship assignment LOA INT(11) pid INT(11) capacity INT(11) cre cid INT(11) bsg ship instance id INT(11) bsg ship assignment is associated to bsg ship instance cl. using a composite key. cid references bsg ship instance.class class INT (11) and sid references bsg ship instance.id. based in id INT(11) bsg ship instance has a selfreferential composite foreign key which references its own composite primary key.

Explanation / Answer

This query retrieves the fname and lname by checking condition that whether the person is assigned to ship or not if that person is assigned to ship then it checks for the instance and then class which is viper or not. If viper then it will retrieves the list only one row per person because of group by clause.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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