{"id":914,"date":"2026-03-04T17:53:50","date_gmt":"2026-03-04T21:53:50","guid":{"rendered":"http:\/\/stephendavies.org\/cpsc350\/?p=914"},"modified":"2026-03-05T13:40:50","modified_gmt":"2026-03-05T17:40:50","slug":"spot-checking-assignment-4","status":"publish","type":"post","link":"http:\/\/stephendavies.org\/cpsc350\/?p=914","title":{"rendered":"Spot checking assignment #4"},"content":{"rendered":"<p>Here are some checks to confirm that you did <a href=\"http:\/\/stephendavies.org\/cpsc350\/assignment4.html\">assignment #4<\/a> correctly:<\/p>\n<ol>\n<li>You should have exactly <b>42 pilots<\/b> in your database (this includes the five from <a href=\"http:\/\/stephendavies.org\/cpsc350\/assignment2.html\">assignment #2<\/a>).\n<p><!--\nwith pilots as (select distinct pilot from trips), copilots as (select distinct\ncopilot as pilot from trips) select pilot from pilots union select pilot from\ncopilots;\n--><\/p>\n<ul>\n<li>Exactly <b>nine<\/b> of these pilots should be certified to fly the <b>Millenium Falcon<\/b>.<\/li>\n<p>    <!-- with p as (select distinct pilot from trips where shipmodel='Millenium Falcon'), c as (select distinct copilot from trips where shipmodel='Millenium Falcon') select pilot as crew_member from p union select copilot as crew_member from c; --><\/p>\n<li><b>Four<\/b> pilots should be certified to fly a colonial <b>Viper<\/b>.<\/li>\n<p>    <!-- select distinct pilot from trips where shipmake='BSG' and\n         shipmodel='Viper'; --><\/p>\n<li>Only <b>two<\/b> pilots should be certified on the <b>U.S.S. Enterprise<\/b>.<\/li>\n<p>    <!-- select distinct pilot from trips where shipmake='ST' and shipmodel\n         like '%Enterprise%'; --><\/p>\n<\/ul>\n<\/li>\n<li>There should be exactly <b>ten<\/b> regularly scheduled flights servicing planet <b>Vulcan<\/b>, either departing from, or arriving at, that planet.<\/li>\n<p><!-- select * from flightplans where origin='Vulcan';\n     select * from flightplans where destination='Vulcan'; --><\/p>\n<li>The <b>earliest flight<\/b> (where &#8220;earliest&#8221; means &#8220;the earliest date&#8221;) to <b>Centauri Prime<\/b> in your database should have had exactly 51 passengers, while the <b>latest<\/b> flight to this planet has only 5 so far.<\/li>\n<p><!--\nSELECT s.type, COUNT(*) AS cnt\n FROM sales s\n JOIN trips t\n   ON s.spaceline   = t.spaceline\n  AND s.flightnum   = t.flightnum\n  AND s.flight_date = t.date\n JOIN flightplans f\n   ON f.spaceline = t.spaceline\n  AND f.flightnum = t.flightnum\n WHERE f.destination = 'Centauri Prime'\n   AND t.date = '2026-02-24'\n GROUP BY s.type;\n+----------+-----+\n| type     | cnt |\n+----------+-----+\n| purchase |  58 |\n| refund   |   7 |\n+----------+-----+\n--><\/p>\n<p><!-- select flight_date, type, count(*) from sales where spaceline='Space Transport' and flightnum=741 and flight_date in('2021-10-06','2021-11-10') group by flight_date, type; --><\/p>\n<li>Exactly <b>one<\/b> of the scheduled flights should be sold out. The other <b>600<\/b> scheduled flights should have (or did have, at the time) tickets still available for purchase. (Note that this 600 figure includes the five flights scheduled in <a href=\"http:\/\/stephendavies.org\/cpsc350\/assignment2.html\">assignment #2<\/a>.) <\/li>\n<\/ol>\n<p><!--\nSELECT COUNT(*) AS sold_out_trips FROM (\n  SELECT\n      t.spaceline,\n      t.flightnum,\n      t.date,\n      SUM(CASE WHEN s.type = 'purchase' THEN 1\n        WHEN s.type = 'refund' THEN -1 END) AS passengers,\n      sh.capacity\n  FROM trips t\n  JOIN capacities sh\n    ON sh.shipmake  = t.shipmake\n   AND sh.shipmodel = t.shipmodel\n  JOIN sales s\n    ON s.spaceline   = t.spaceline\n   AND s.flightnum   = t.flightnum\n   AND s.flight_date = t.date\n  GROUP BY t.spaceline, t.flightnum, t.date, sh.capacity\n  HAVING passengers >= sh.capacity ) AS sold_out;\n--><\/p>\n<hr\/>\n<p>If all these check out, I think it&#8217;s pretty darn likely that your bulk import was successful.<\/p>\n<p><!-- select date,numPassengers from scheduledFlight as s join dailyFlight as d on s.airline=d.airline and s.flightnum=d.flightnum where destination='Centauri Prime' order by date; --><\/p>\n<p><!-- select s.date,d.airline,d.flightnum,a.capacity,s.numPassengers from aircraftType as a, dailyFlight as d, scheduledFlight as s where a.make=d.aircraftMake and a.model=d.aircraftModel and d.airline=s.airline and d.flightNum=s.flightNum and numPassengers=capacity;\n --><\/p>\n<p><!-- vim:textwidth=99999\n--><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Here are some checks to confirm that you did assignment #4 correctly: You should have exactly 42 pilots in your database (this includes the five from assignment #2). Exactly nine of these pilots should be certified to fly the Millenium Falcon. Four pilots should be certified to fly a colonial Viper. Only two pilots should [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_links_to":"","_links_to_target":""},"categories":[1],"tags":[],"class_list":["post-914","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"http:\/\/stephendavies.org\/cpsc350\/index.php?rest_route=\/wp\/v2\/posts\/914","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/stephendavies.org\/cpsc350\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/stephendavies.org\/cpsc350\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/stephendavies.org\/cpsc350\/index.php?rest_route=\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"http:\/\/stephendavies.org\/cpsc350\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=914"}],"version-history":[{"count":5,"href":"http:\/\/stephendavies.org\/cpsc350\/index.php?rest_route=\/wp\/v2\/posts\/914\/revisions"}],"predecessor-version":[{"id":919,"href":"http:\/\/stephendavies.org\/cpsc350\/index.php?rest_route=\/wp\/v2\/posts\/914\/revisions\/919"}],"wp:attachment":[{"href":"http:\/\/stephendavies.org\/cpsc350\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=914"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/stephendavies.org\/cpsc350\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=914"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/stephendavies.org\/cpsc350\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=914"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}