Home / Expert Answers / Computer Science / hello-i-need-help-with-these-sql-queries-i-also-ask-that-the-output-relation-cardinality-is-accurat-pa516

(Solved): Hello I need help with these sql queries. I also ask that the output relation cardinality is accurat ...



Hello I need help with these sql queries. I also ask that the output relation cardinality is accurate to each question of the assignment.

student submitted image, transcription available below
student submitted image, transcription available below
In this homework, you will write several SQL queries on a relational flights database. The data in this database is abridged from the [Bureau of Transportation Statistics]. The database consists of four tables regarding a subset of flights and all data except for the capacity and price columns are real. FLIGHTS (fid, month_id, day_of_month, day_of_week_id, carrier_id, flight_num, origin_city, origin_state, dest_city, dest_state, departure_delay, taxi_out, arrival_delay, cancelled, actual_time, distance, capacity, price) CARRIERS (cid, name) MONTHS (mid, month) WEEKDAYS (did, day_of_week) Use the same database schema and data from Homework 3. Writing SQL QUERIES (120 points) In the questions below flights include canceled flights as well, unless otherwise noted. For each question, add a comment to each query indicating the question number and the number of rows your query returns. 1. (20 points) List the names of carriers that operate flights from Seattle to San Francisco, CA. Return each carrier's name only once. Use a nested query to answer this question. Name the output column 'carrier'. [Output relation cardinality: 4] 2. (15 points) Express the same query as above but do so without using a nested query. Again, name the output column 'carrier'. [Output relation cardinality: 4] 3. (15 points) List the weekdays (day_of_week in table weekdays) with at least 180000 flights per day. Use a nested query to answer this question. [Output relation cardinality: 3 rows] 4. (15 points) Retrieve the carrier ids where the difference between the minimum and maximum flight prices for that carrier is less than . Use a query in the FROM clause to answer this question. [Output relation cardinality: 18] 5. (15 points) Retrieve the carrier ids, the minimum and maximum flight prices where the difference between the minimum and maximum flight prices for that carrier is less than . Do not use any nested queries to answer this question. [Output relation cardinality: 18 ] 6. (15 points) List the weekdays (day_of_week in table weekdays) where the difference between the minimum and maximum flight prices for that day is less than . Use a nested query to answer this question. [Output relation cardinality: 3] 7. (15 points) List the carrier names of all carriers that do not operate any flights. [Output relation cardinality: 1574 ] 8. (10 points) Retrieve the flight numbers of all flights operated by 'Hawaiian Airlines Inc.' and that fly on Mondays. [Output relation cardinality: 286] Programming style To encourage good SQL programming style please follow these two simple style rules: - Give explicit names to all tables referenced in the 'FROM' clause. For instance, instead of writing: SELECT * FROM flights, carriers WHERE carrier_id = cid write SELECT * FROM flights , carriers c WHERE f.carrier_id = c c cid (notice the aliases ) - Similarly, reference to all attributes must be qualified by the table name. Instead of writing: SELECT * FROM flights WHERE fid write SELECT * FROM flights WHERE .fid


We have an Answer from Expert

View Expert Answer

Expert Answer



We have an Answer from Expert

Buy This Answer $5

Place Order

We Provide Services Across The Globe