Non-Correlated Subqueries III - A few questions


#1

I'm having some difficulties understanding the code and there is insufficient explaining in the exercise.

SELECT a.dep_month,
       a.dep_day_of_week,
       AVG(a.flight_count) AS average_flights
  FROM (
        SELECT dep_month,
              dep_day_of_week,
               dep_date,
               COUNT(*) AS flight_count
          FROM flights
         GROUP BY 1,2,3
       ) a
 GROUP BY 1,2
 ORDER BY 1,2;

1. Where does the "flight_count" on line 3 come from? I cannot find it in the columns, and so I do not understand what it does.
2. Why is there an "a" in the code? What does it do?
3. How does Group By really work? I've never fully understood it, the definition is confusing. And in this case, what does "1, 2, 3" represent?

Thanks in advance.


Questions regarding SUBQUERIES III
Non-Correlated Subqueries III
#2

@janders23,
Have you done the course
https://www.codecademy.com/learn/learn-sql

Have you ever used a Relational-Database ??
Your answer: .....


Non-Correlated Subqueries III
#3

@janders23,

+operating-system - - - - - - - - - - -+
|                                      |
|      +RDBMS - - - - - - - - - - +    |
|      | your SELECT-statements   |    |
|      | on =columns=             |    |
|      |  of 1 or more =tables=   |    |
|      |   which are held         |    |
|      |   in 1 or more files     |    |
|      + - - - - - || - - - - - - +    |
+ - - - - - - - - -|| - - - - - - - - -+
                   ||
+file-system - - - ||- - - - +
|  disk sectors/block-size   |
|                            |
+ - - - - - - - - - - - - - -+

From
http://stackoverflow.com/questions/4811744/what-is-the-difference-between-a-relational-and-non-relational-database
and
https://en.wikipedia.org/wiki/Relational_database
https://en.wikipedia.org/wiki/Edgar_F._Codd
https://en.wikipedia.org/wiki/Codd%27s_12_rules


6. Correlated Subqueries II
#4

@janders23,
If you have a query like

SELECT a.dep_month,
       a.dep_day_of_week,
       AVG(a.flight_count) AS average_flights
  FROM (
        SELECT dep_month,
              dep_day_of_week,
               dep_date,
               COUNT(*) AS flight_count
          FROM flights
         GROUP BY 1,2,3
       ) a

you have to visualize it like

   [ outer-query ]
   from
     [inner-query] as a

With the inner-query you create a =virtual-table=
in the [outer-query]
you can refer to this [inner-query] as a ( question-2 is answered )

=============================================================

A =table= consists of 1 or more ROW's
the DBMS keeps track of those ROW's by giving each =row= a unique ROW-ID

+--------+-------------------
| ROW-ID |  ........
+--------+-------------------

Each ROW consists of the ROW-ID and 1 or more COLumns

+--------+----------------------------
| ROW-ID | COL | COL | COL | .......
+--------+----------------------------

In your [inner-query] you use

        SELECT dep_month,
               dep_day_of_week,
               dep_date,
               COUNT(*) AS flight_count
          FROM flights

the DBMS will create a virtual =table=
collecting DATA out of the FLIGHTS =table=
using ALL ROW's (as you are not using a WHERE clause )
from each ROW picking up data
from the COLumns
dep_month ( for the DBMS it is (1) for your =display= it is as dep_month )
dep_day_of_week ( for the DBMS it is (2) for your =display= it is as dep_day_of_week )_
dep_date ( for the DBMS it is (3) for your =display= it is as dep_date )
then with COUNT(*) you create a virtual COLumn
which is a counter, counting every selected ROW
displaying it as an =integer= and with your as flight_count
the COL would be displayed as flight_count
for the DBMS it would be (4)

              for-display                 DBMS
        SELECT dep_month,                  (1)
               dep_day_of_week,            (2)
               dep_date,                   (3)
               COUNT(*) AS flight_count    (4)
        FROM flights

#5

@janders23,

=group by=
If you use

        SELECT dep_month,
               dep_day_of_week,
               dep_date,
               COUNT(*) AS flight_count
          FROM flights
         GROUP BY 1,2,3

the DBMS will create a data-bucket per group-column-Value
in this case as you are grouping on 3 columns
Visualized

Januari  <<Januari-group

  Monday  << Monday-group =within= Januari-group
     Jan-1 << date-group =within= Monday-group =within= Januari-group
     Jan-1
     Jan-8
     Jan-15
     Jan-22
     Jan-22
     Jan-29

  Tuesday
     Jan-2
     Jan-9
     Jan-16
     Jan-23
     Jan-30

  Wednesday
     Jan-3
     Jan-10
     Jan-10
     Jan-17
     Jan-24
     Jan-31

  Thursday
     Jan-4
     Jan-11
     Jan-18
     Jan-18
     Jan-25
     Jan-25

  Friday
     Jan-5
     Jan-12
     Jan-12
     Jan-19
     Jan-26

  Saturday
     Jan-6
     Jan-13
     Jan-20
     Jan-20
     Jan-27
     Jan-27

  Sunday
     Jan-7
     Jan-7
     Jan-14
     Jan-21
     Jan-28
     Jan-28

Februar
    .....
       .....
       .....

#6

Realize the Example query was missing the Friday only where =. So it should have been:
SELECT a.dep_month,
a.dep_day_of_week,
AVG(a.flight_count) AS average_flights
FROM (
SELECT dep_month,
dep_day_of_week,
dep_date,
COUNT(*) AS flight_count
FROM flights
GROUP BY 1,2,3
) a WHERE a.dep_day_of_week = 'Friday'
GROUP BY 1,2
ORDER BY 1,2;


#7

even I am having a trouble using this code
SELECT a.dep_date,
a.dep_month,
a.dep_day_of_week,
AVG(a.flight_distance) AS average_distance
FROM (
SELECT dep_month,
dep_day_of_week,
dep_date,
COUNT(*) AS flight_distance
FROM flights
GROUP BY 1,2,3
) a
GROUP BY 1,2
ORDER BY 1,2;

Its giving that "Use the select statement to view the table "
did i missed something int ht above code , plz help me to find out that


#8

Thank you for your explanations. I'm still confused by the a. How can it be named 'a' without using AS?


#9

@merendis,
Here some discussions on the table alias topic
https://technet.microsoft.com/en-us/library/ms187455(v=sql.105).aspx
http://stackoverflow.com/questions/2454951/why-would-you-use-as-when-aliasing-a-sql-table
and be aware you are working in a SQLite environment.....


#10