SQL

• Basic Queries
• Aggregate Functions
• Helper Functions
• Plot for the Blog Post
• Sources
• References

!curl https://cdn.sqlitetutorial.net/wp-content/uploads/2018/03/chinook.zip -o database.zip
!unzip database.zip
!ls

  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  298k  100  298k    0     0   972k      0 --:--:-- --:--:-- --:--:--  972k
Archive:  database.zip
  inflating: chinook.db              
chinook.db  database.zip  sample_data

con = sqlite3.connect("chinook.db")
cursor = con.cursor()
with con:
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
    for table_name in cursor.fetchall():
        
        print(table_name[0])
        print(pd.read_sql_query(f"select * from {table_name[0]}", con).head(), "\n\n\n\n\n\n")

albums
   AlbumId                                  Title  ArtistId
0        1  For Those About To Rock We Salute You         1
1        2                      Balls to the Wall         2
2        3                      Restless and Wild         2
3        4                      Let There Be Rock         1
4        5                               Big Ones         3 






sqlite_sequence
          name   seq
0       genres    25
1  media_types     5
2      artists   275
3       albums   347
4       tracks  3503 






artists
   ArtistId               Name
0         1              AC/DC
1         2             Accept
2         3          Aerosmith
3         4  Alanis Morissette
4         5    Alice In Chains 






customers
   CustomerId  FirstName     LastName  \
0           1       Luís    Gonçalves   
1           2     Leonie       Köhler   
2           3   François     Tremblay   
3           4      Bjørn       Hansen   
4           5  František  Wichterlová   

                                            Company  \
0  Embraer - Empresa Brasileira de Aeronáutica S.A.   
1                                              None   
2                                              None   
3                                              None   
4                                  JetBrains s.r.o.   

                           Address                 City State         Country  \
0  Av. Brigadeiro Faria Lima, 2170  São José dos Campos    SP          Brazil   
1          Theodor-Heuss-Straße 34            Stuttgart  None         Germany   
2                1498 rue Bélanger             Montréal    QC          Canada   
3                 Ullevålsveien 14                 Oslo  None          Norway   
4                    Klanova 9/506               Prague  None  Czech Republic   

  PostalCode               Phone                 Fax  \
0  12227-000  +55 (12) 3923-5555  +55 (12) 3923-5566   
1      70174    +49 0711 2842222                None   
2    H2G 1A7   +1 (514) 721-4711                None   
3       0171     +47 22 44 22 22                None   
4      14700    +420 2 4172 5555    +420 2 4172 5555   

                      Email  SupportRepId  
0      luisg@embraer.com.br             3  
1     leonekohler@surfeu.de             5  
2       ftremblay@gmail.com             3  
3     bjorn.hansen@yahoo.no             4  
4  frantisekw@jetbrains.com             4   






employees
   EmployeeId LastName FirstName                Title  ReportsTo  \
0           1    Adams    Andrew      General Manager        NaN   
1           2  Edwards     Nancy        Sales Manager        1.0   
2           3  Peacock      Jane  Sales Support Agent        2.0   
3           4     Park  Margaret  Sales Support Agent        2.0   
4           5  Johnson     Steve  Sales Support Agent        2.0   

             BirthDate             HireDate              Address      City  \
0  1962-02-18 00:00:00  2002-08-14 00:00:00  11120 Jasper Ave NW  Edmonton   
1  1958-12-08 00:00:00  2002-05-01 00:00:00         825 8 Ave SW   Calgary   
2  1973-08-29 00:00:00  2002-04-01 00:00:00        1111 6 Ave SW   Calgary   
3  1947-09-19 00:00:00  2003-05-03 00:00:00     683 10 Street SW   Calgary   
4  1965-03-03 00:00:00  2003-10-17 00:00:00         7727B 41 Ave   Calgary   

  State Country PostalCode              Phone                Fax  \
0    AB  Canada    T5K 2N1  +1 (780) 428-9482  +1 (780) 428-3457   
1    AB  Canada    T2P 2T3  +1 (403) 262-3443  +1 (403) 262-3322   
2    AB  Canada    T2P 5M5  +1 (403) 262-3443  +1 (403) 262-6712   
3    AB  Canada    T2P 5G3  +1 (403) 263-4423  +1 (403) 263-4289   
4    AB  Canada    T3B 1Y7   1 (780) 836-9987   1 (780) 836-9543   

                      Email  
0    andrew@chinookcorp.com  
1     nancy@chinookcorp.com  
2      jane@chinookcorp.com  
3  margaret@chinookcorp.com  
4     steve@chinookcorp.com   






genres
   GenreId                Name
0        1                Rock
1        2                Jazz
2        3               Metal
3        4  Alternative & Punk
4        5       Rock And Roll 






invoices
   InvoiceId  CustomerId          InvoiceDate           BillingAddress  \
0          1           2  2009-01-01 00:00:00  Theodor-Heuss-Straße 34   
1          2           4  2009-01-02 00:00:00         Ullevålsveien 14   
2          3           8  2009-01-03 00:00:00          Grétrystraat 63   
3          4          14  2009-01-06 00:00:00           8210 111 ST NW   
4          5          23  2009-01-11 00:00:00          69 Salem Street   

  BillingCity BillingState BillingCountry BillingPostalCode  Total  
0   Stuttgart         None        Germany             70174   1.98  
1        Oslo         None         Norway              0171   3.96  
2    Brussels         None        Belgium              1000   5.94  
3    Edmonton           AB         Canada           T6G 2C7   8.91  
4      Boston           MA            USA              2113  13.86   






invoice_items
   InvoiceLineId  InvoiceId  TrackId  UnitPrice  Quantity
0              1          1        2       0.99         1
1              2          1        4       0.99         1
2              3          2        6       0.99         1
3              4          2        8       0.99         1
4              5          2       10       0.99         1 






media_types
   MediaTypeId                         Name
0            1              MPEG audio file
1            2     Protected AAC audio file
2            3  Protected MPEG-4 video file
3            4     Purchased AAC audio file
4            5               AAC audio file 






playlists
   PlaylistId        Name
0           1       Music
1           2      Movies
2           3    TV Shows
3           4  Audiobooks
4           5  90’s Music 






playlist_track
   PlaylistId  TrackId
0           1     3402
1           1     3389
2           1     3390
3           1     3391
4           1     3392 






tracks
   TrackId                                     Name  AlbumId  MediaTypeId  \
0        1  For Those About To Rock (We Salute You)        1            1   
1        2                        Balls to the Wall        2            2   
2        3                          Fast As a Shark        3            2   
3        4                        Restless and Wild        3            2   
4        5                     Princess of the Dawn        3            2   

   GenreId                                           Composer  Milliseconds  \
0        1          Angus Young, Malcolm Young, Brian Johnson        343719   
1        1                                               None        342562   
2        1  F. Baltes, S. Kaufman, U. Dirkscneider & W. Ho...        230619   
3        1  F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. D...        252051   
4        1                         Deaffy & R.A. Smith-Diesel        375418   

      Bytes  UnitPrice  
0  11170334       0.99  
1   5510424       0.99  
2   3990994       0.99  
3   4331779       0.99  
4   6290521       0.99   






sqlite_stat1
              tbl                                idx        stat
0          tracks               IFK_TrackMediaTypeId    3503 701
1          tracks                   IFK_TrackGenreId    3503 141
2          tracks                   IFK_TrackAlbumId     3503 11
3  playlist_track           IFK_PlaylistTrackTrackId      8715 3
4  playlist_track  sqlite_autoindex_playlist_track_1  8715 623 1 

Basic Queries

pd.read_sql_query("select * from employees limit 10", con)

	EmployeeId	LastName	FirstName	Title	ReportsTo	BirthDate	HireDate	Address	City	State	Country	PostalCode	Phone	Fax	Email
0	1	Adams	Andrew	General Manager	NaN	1962-02-18 00:00:00	2002-08-14 00:00:00	11120 Jasper Ave NW	Edmonton	AB	Canada	T5K 2N1	+1 (780) 428-9482	+1 (780) 428-3457	andrew@chinookcorp.com
1	2	Edwards	Nancy	Sales Manager	1.0	1958-12-08 00:00:00	2002-05-01 00:00:00	825 8 Ave SW	Calgary	AB	Canada	T2P 2T3	+1 (403) 262-3443	+1 (403) 262-3322	nancy@chinookcorp.com
2	3	Peacock	Jane	Sales Support Agent	2.0	1973-08-29 00:00:00	2002-04-01 00:00:00	1111 6 Ave SW	Calgary	AB	Canada	T2P 5M5	+1 (403) 262-3443	+1 (403) 262-6712	jane@chinookcorp.com
3	4	Park	Margaret	Sales Support Agent	2.0	1947-09-19 00:00:00	2003-05-03 00:00:00	683 10 Street SW	Calgary	AB	Canada	T2P 5G3	+1 (403) 263-4423	+1 (403) 263-4289	margaret@chinookcorp.com
4	5	Johnson	Steve	Sales Support Agent	2.0	1965-03-03 00:00:00	2003-10-17 00:00:00	7727B 41 Ave	Calgary	AB	Canada	T3B 1Y7	1 (780) 836-9987	1 (780) 836-9543	steve@chinookcorp.com
5	6	Mitchell	Michael	IT Manager	1.0	1973-07-01 00:00:00	2003-10-17 00:00:00	5827 Bowness Road NW	Calgary	AB	Canada	T3B 0C5	+1 (403) 246-9887	+1 (403) 246-9899	michael@chinookcorp.com
6	7	King	Robert	IT Staff	6.0	1970-05-29 00:00:00	2004-01-02 00:00:00	590 Columbia Boulevard West	Lethbridge	AB	Canada	T1K 5N8	+1 (403) 456-9986	+1 (403) 456-8485	robert@chinookcorp.com
7	8	Callahan	Laura	IT Staff	6.0	1968-01-09 00:00:00	2004-03-04 00:00:00	923 7 ST NW	Lethbridge	AB	Canada	T1H 1Y8	+1 (403) 467-3351	+1 (403) 467-8772	laura@chinookcorp.com

query = """
select *, CASE
		WHEN EmployeeId > 1 THEN
			'short'
		WHEN EmployeeId > 5 THEN 'medium'
		ELSE
			'long'
		END category
         from employees limit 10
"""

pd.read_sql_query(query, con)

	EmployeeId	LastName	FirstName	Title	ReportsTo	BirthDate	HireDate	Address	City	State	Country	PostalCode	Phone	Fax	Email	category
0	1	Adams	Andrew	General Manager	NaN	1962-02-18 00:00:00	2002-08-14 00:00:00	11120 Jasper Ave NW	Edmonton	AB	Canada	T5K 2N1	+1 (780) 428-9482	+1 (780) 428-3457	andrew@chinookcorp.com	long
1	2	Edwards	Nancy	Sales Manager	1.0	1958-12-08 00:00:00	2002-05-01 00:00:00	825 8 Ave SW	Calgary	AB	Canada	T2P 2T3	+1 (403) 262-3443	+1 (403) 262-3322	nancy@chinookcorp.com	short
2	3	Peacock	Jane	Sales Support Agent	2.0	1973-08-29 00:00:00	2002-04-01 00:00:00	1111 6 Ave SW	Calgary	AB	Canada	T2P 5M5	+1 (403) 262-3443	+1 (403) 262-6712	jane@chinookcorp.com	short
3	4	Park	Margaret	Sales Support Agent	2.0	1947-09-19 00:00:00	2003-05-03 00:00:00	683 10 Street SW	Calgary	AB	Canada	T2P 5G3	+1 (403) 263-4423	+1 (403) 263-4289	margaret@chinookcorp.com	short
4	5	Johnson	Steve	Sales Support Agent	2.0	1965-03-03 00:00:00	2003-10-17 00:00:00	7727B 41 Ave	Calgary	AB	Canada	T3B 1Y7	1 (780) 836-9987	1 (780) 836-9543	steve@chinookcorp.com	short
5	6	Mitchell	Michael	IT Manager	1.0	1973-07-01 00:00:00	2003-10-17 00:00:00	5827 Bowness Road NW	Calgary	AB	Canada	T3B 0C5	+1 (403) 246-9887	+1 (403) 246-9899	michael@chinookcorp.com	short
6	7	King	Robert	IT Staff	6.0	1970-05-29 00:00:00	2004-01-02 00:00:00	590 Columbia Boulevard West	Lethbridge	AB	Canada	T1K 5N8	+1 (403) 456-9986	+1 (403) 456-8485	robert@chinookcorp.com	short
7	8	Callahan	Laura	IT Staff	6.0	1968-01-09 00:00:00	2004-03-04 00:00:00	923 7 ST NW	Lethbridge	AB	Canada	T1H 1Y8	+1 (403) 467-3351	+1 (403) 467-8772	laura@chinookcorp.com	short

pd.read_sql_query("select * from employees limit 10 offset 3", con)

	EmployeeId	LastName	FirstName	Title	ReportsTo	BirthDate	HireDate	Address	City	State	Country	PostalCode	Phone	Fax	Email
0	4	Park	Margaret	Sales Support Agent	2	1947-09-19 00:00:00	2003-05-03 00:00:00	683 10 Street SW	Calgary	AB	Canada	T2P 5G3	+1 (403) 263-4423	+1 (403) 263-4289	margaret@chinookcorp.com
1	5	Johnson	Steve	Sales Support Agent	2	1965-03-03 00:00:00	2003-10-17 00:00:00	7727B 41 Ave	Calgary	AB	Canada	T3B 1Y7	1 (780) 836-9987	1 (780) 836-9543	steve@chinookcorp.com
2	6	Mitchell	Michael	IT Manager	1	1973-07-01 00:00:00	2003-10-17 00:00:00	5827 Bowness Road NW	Calgary	AB	Canada	T3B 0C5	+1 (403) 246-9887	+1 (403) 246-9899	michael@chinookcorp.com
3	7	King	Robert	IT Staff	6	1970-05-29 00:00:00	2004-01-02 00:00:00	590 Columbia Boulevard West	Lethbridge	AB	Canada	T1K 5N8	+1 (403) 456-9986	+1 (403) 456-8485	robert@chinookcorp.com
4	8	Callahan	Laura	IT Staff	6	1968-01-09 00:00:00	2004-03-04 00:00:00	923 7 ST NW	Lethbridge	AB	Canada	T1H 1Y8	+1 (403) 467-3351	+1 (403) 467-8772	laura@chinookcorp.com

pd.read_sql_query("select * from artists", con, index_col='ArtistId')

	Name
ArtistId
1	AC/DC
2	Accept
3	Aerosmith
4	Alanis Morissette
5	Alice In Chains
...	...
271	Mela Tenenbaum, Pro Musica Prague & Richard Kapp
272	Emerson String Quartet
273	C. Monteverdi, Nigel Rogers - Chiaroscuro; Lon...
274	Nash Ensemble
275	Philip Glass Ensemble

275 rows × 1 columns

pd.read_sql_query("select * from artists inner join albums on artists.ArtistId = albums.ArtistId", con)

	ArtistId	Name	AlbumId	Title	ArtistId
0	1	AC/DC	1	For Those About To Rock We Salute You	1
1	2	Accept	2	Balls to the Wall	2
2	2	Accept	3	Restless and Wild	2
3	1	AC/DC	4	Let There Be Rock	1
4	3	Aerosmith	5	Big Ones	3
...	...	...	...	...	...
342	226	Eugene Ormandy	343	Respighi:Pines of Rome	226
343	272	Emerson String Quartet	344	Schubert: The Late String Quartets & String Qu...	272
344	273	C. Monteverdi, Nigel Rogers - Chiaroscuro; Lon...	345	Monteverdi: L'Orfeo	273
345	274	Nash Ensemble	346	Mozart: Chamber Music	274
346	275	Philip Glass Ensemble	347	Koyaanisqatsi (Soundtrack from the Motion Pict...	275

347 rows × 5 columns

pd.read_sql_query("select * from media_types where Name like '5AAC%'", con)

	MediaTypeId	Name

pd.read_sql_query("select * from playlists where name like '_usic'", con)

	PlaylistId	Name
0	1	Music
1	8	Music

Aggregate Functions

COUNT() SUM() MAX() MIN() AVG()

Helper Functions

Plot for the Blog Post

Sources

• Hello This is a markdown page (missing reference)
• http://openbook.rheinwerk-verlag.de/python/33_003.html#u33.3.1
• https://www.sqlitetutorial.net/sqlite-left-join/

• https://realpython.com/python-sqlite-sqlalchemy/

• https://docs.snowflake.net/manuals/sql-reference-commands.html

• https://www.geeksforgeeks.org/sql-tutorial/

• https://github.com/harelba/q

• https://github.com/kennethreitz-archive/records

• https://github.com/simonw/csvs-to-sqlite

• https://github.com/prestodb/presto

• https://sqlzoo.net/

• https://www.sqlstyle.guide/

• https://twitter.com/CMastication/status/1289919608309329921

• https://blazingsql.com/

• https://duckdb.org/

• https://github.com/nackjicholson/aiosql

• https://github.com/sql-machine-learning/sqlflow

• https://github.com/ibis-project/ibis

• https://gist.github.com/kdev33/97951cb3b9dd1459898a93b5702ed4cb

References