6. Correlated Subquerries II


#1

Hi all i'm having an issue with this exercise. Instead of just clicking the get code button, i would like to try and figure this one out. In the exercise, they are asking to rename a column to flight_sequence_number however to me it looks like there is already a column named flight_sequence_number. Here is the assignment below.

It would also be interesting to order flights by giving them a sequence number based on time, by carrier.

For instance, assuming flight_id increments with each additional flight, we could use the following query to view flights by carrier, flight id, and sequence number:

SELECT carrier, id,
(SELECT COUNT(*)
FROM flights f
WHERE f.id < flights.id
AND f.carrier=flights.carrier) + 1
AS flight_sequence_number
FROM flights;


DIRECTIONS:

1.
Using the same pattern, write a query to view flights by origin, flight id, and sequence number. Alias the sequence number column as flight_sequence_number.


#2

@cflux,

With

select * from sqlite_master;

You get the full definition used::

type
name
tbl_name
rootpage

sql

table airports airports 2 CREATE TABLE airports ( id bigint, code text, site_number text, fac_type text, fac_use text, faa_region text, faa_dist text, city text, county text, state text, full_name text, own_type text, longitude double, latitude double, elevation integer, aero_cht text, cbd_dist integer, cbd_dir text, act_date text, cert text, fed_agree text, cust_intl text, c_ldg_rts text, joint_use text, mil_rts text, cntl_twr text, major text ) 
table flights flights 3 CREATE TABLE flights (id BIGINT PRIMARY KEY UNIQUE, carrier text, origin text, destination text, flight_num text, flight_time int, tail_num text, dep_time text, arr_time text, dep_delay int, arr_delay int, taxi_out int, taxi_in int, distance int, cancelled text, diverted text, dep_day_of_week varchar (255), dep_month varchar (255), dep_date text) 
index sqlite_autoindex_flights_1 flights 4

Database Schema
airports 644 rows

id bigint 
code text 
site_number text 
fac_type text 
fac_use text 
faa_region text 
faa_dist text 
city text 
county text 
state text 
full_name text 
own_type text 
longitude double 
latitude double 
elevation integer 
aero_cht text 
cbd_dist integer 
cbd_dir text 
act_date text 
cert text 
fed_agree text 
cust_intl text 
c_ldg_rts text 
joint_use text 
mil_rts text 
cntl_twr text 
major text

Database Schema
flights 499 rows

 id BIGINT 
carrier text 
origin text 
destination text 
flight_num text 
flight_time int 
tail_num text 
dep_time text 
arr_time text 
dep_delay int 
arr_delay int 
taxi_out int 
taxi_in int 
distance int 
cancelled text 
diverted text 
dep_day_of_week varchar (255) 
dep_month varchar (255) 
dep_date text

As you can see your assumption
it looks like there is already a column named flight_sequence_number
is not true.....


#3