Question

List all cities that cannot be reached from Seattle through a direct flight nor with one stop (i.e., with any two flights that go through an intermediate city). Warning: this query might take a while to execute. We will learn about how to speed this up in lecture. Name the output column city. Order the output ascending by city.

Answer 1

Here , We are given a Table named FLIGHT (let),

And asked to write a query which will output the names of the cities which cannot be reached through a direct or indirect flight . And also to name it as City and then display the output in ascending order.

    Example Table:                     

FLIGHT

Serial no	source_city	destination_city
1	Seattle	Moscow
2	frankfurt	London
3	seattle	silicon valley
4	washington DC	paris
5	Paris	frankfurt
6	Moscow	washington DC

now, according to the given statement, we need to first get city names which cannot be traveled through the direct or indirect flight through Seattle hence for that the SQL query is :

SELECT   destination_city   FROM flight AS   City WHERE   source_city < > Seattle.

After we execute the above query the OUTPUT will be as shown below screenshot :

Now, we need to make the displayed output as ordered in ascending order :

for that the previous query needs some modification as below :

SELECT * FROM City (SELECT   destination_city   FROM flight AS   City WHERE   source_city < > Seattle) GROUP BY ASC.

for OUTPUT of the above query please refer the below screenshot :

SO, the above output is the desired output .