SQL: Difficult Solutions

creating/MOdifying tables

Write the SQL statement(s) to create each of the requested tables. For each question, the table that your code should produce is shown.

Screen Shot 2019-01-16 at 11.34.11 AM.png

Create a table of cities, attractions, and flights in alphabetical order by city.
‘Baltimore’ | ‘Boston’ | ‘Baltimore Aquarium’
‘Chicago’ | ‘Baltimore’ | ‘Navy Pier’
‘New York’ | ‘ Chicago’ | ‘Central Park’
‘Los Angeles’ | ‘New York’ | ‘Hollywood’
‘San Francisco’ | ‘New York’ | ‘Golden Gate Bridge’

select a.city, b.attraction, a.flight_to
    from Flights as a, Attractions as b
    where a.city = b.city
;

Create a table of all paths to Chicago involving two flights or less and the number of flights.
‘Chicago’ | 0
‘New York, Chicago’ | 1
‘Los Angeles, New York, Chicago’ | 2
‘San Francisco, Los Angeles, Chicago’ | 2

create table paths(path, num_flights, last_location);
insert into paths(path, num_flights, last_location) select city, 0, city
    from Flights;
insert into paths(path, num_flights, last_location) select path || flight_to, num_flights + 1, flight_to
    from paths, Flights
    where city = last_location;
insert into paths(path, num_flights, last_location) select path || flight_to, num_flights + 1, flight_to
    from paths, Flights
    where city = last_location;

create table paths_to_chicago as select path, num_flights
    from paths
    where last_flight = ‘Chicago’
;