This is an individual assignment. All work must be your own. You should not look at any other student's work (in whole or in part, on paper or on screen), nor allow anyone else to look at yours, during the course of this assignment. Nor may you submit the work of software programs [or hardware devices] as your own.

You may turn in hardcopy (typed, submitted during class time on the day before the due date) or upload to Canvas. Typed, in a plain text, PDF or MS Word document.

(Typed homework, in all cases. DO NOT submit a photograph of handwritten scribbles.)

Put your own name at the top of your own homework answers.

Consider the following relations:

Shuttle(shuttleID: integer, shuttlename: string, launchdate: date, enddate: date, currentlocation: string, massKg: integer)
Flight(flightID: integer, shuttleID: integer, plannedlaunchdate: date, actuallaunchdate: date, returndate: date, landingsite: string, commanderID: integer)
FlewOn(flightID: integer, astroID: integer, role: string);
Astronaut(astroID: integer, lastname: string, firstname: string, birthdate: date, deathdate: date, countryBorn: string, organization: integer)
Organization (orgID: integer, name: string, nickname: string, foundingDate: date, pub_or_priv: string, annualBudget: integer, headquartersCity: string, headquartersCountry: string);

The relations capture some basic information about the USA space shuttle program and astronauts. For more information about this, the "13 Minutes" podcast series by the BBC World Service, could be a place to start. (Be aware that the episodes about the Challenger are sad. But engineers need to face our failures, not ignoring them in favor of our successes.)

shuttleID in Flight is a foreign key referencing Shuttle.shuttleID.
commanderID in Flight is a foreign key referencing Astronaut.astroID. (A shuttle flight will have a half-dozen or more astronauts flying it, but only one is the commander.)
flightID in FlewOn is a foreign key referencing Flight.flightID; astroID in FlewOn is a foreign key referencing Astronaut.astroID.
organization in Astronaut is a foreign key referencing Organization.orgID.

countryBorn, in Astronaut, is the name of the country the astronaut was born in. (A German astronaut might fly for ESA, an astronaut might be born elsewhere and fly for the USA, etc.)
pub_or_priv: Most agencies organizing space flights are public. However, in recent years, a few private companies have flown ships in space. Values in this field: "public" or "private". annualBudget is given in millions of dollars.

You may make the simplifying assumption that comparisons on "date" or "timestamp" fields will work with basic =, <, >, etc, boolean operators. This is not the case in many DBMS products; for real work you would need to consult the documentation for your particular DBMS software.

Write the following queries in SQL.

1. Find the first and last names of astronauts that work for private organizations.
2. Find the latest (most recent) founding date of an organization.
3. Find the last and first names of astronauts, and the flight identifiers, where the astronaut flew on the shuttle flight in the role of "mission specialist" and also works for the organization named "European Space Agency".
4. Find the flight identifiers of flights, and the last and first names of astronauts, where the astronaut worked for the agency nicknamed "JAXA" and flew on the space shuttle flight. (JAXA is the Japanese space agency.)
5. Find the names and founding dates of all organizations whose names end in "Agency", or that have "Space" in the middle of the name anywhere, but "Space" should not be at the very start or very end of the name. (UN Office for Outer Space Affairs list of government space agencies.)
6. For each country an astronaut has been born in, list the name of the country and the last (most recent) date an astronaut was born there.
7. For each organization, list the organization nickname, how many astronauts work for the organization, and how many countries those astronauts were born in. Only list information for organizations that have more than two astronauts working for them.
8. Find the landing site and return date for the shuttle flight with the latest (last) return flight date. (Hint: There may be more than one shuttle site landed on on that date.) Show the output alphabetized by landing site.
9. For each astronaut, list how many flights they flew on, including only flights with an planned launch date before 2000. Only include astronauts that flew more than five flights.
10. Find the first and last names of astronauts who commanded a space shuttle flight that landed at "Kennedy SLF", and who worked for an organization named "National Aeronautics and Space Administration". Show the output sorted by the last name of the astronaut.
11. Find and list pairs of astronaut names, first and last for both, where the astronauts work for the same organization but were born in different countries.
12. List space shuttle names and the actual launch dates of the flights the space shuttles flew. Also list the first and last names of the astronauts who commanded the flights and the nicknames of the organizations those commanders work for. Only include flights with actual launch dates after 2000. Output the information sorted by the flight identifiers of the shuttle flights.