In [1]:

df_1 = _deepnote_execute_sql('SELECT * FROM "Invoice"', 'SQL_5C2E9A9B_B591_4FC4_ADD5_829E

B3188444')
df_1

Out[1]:

InvoiceId CustomerId InvoiceDate BillingAddress BillingCity BillingState BillingCountry BillingPostalCode Total

Theodor-Heuss-
0 1 2 2009-01-01 Stuttgart None Germany 70174 1.98
Straße 34

1 2 4 2009-01-02 Ullevålsveien 14 Oslo None Norway 0171 3.96

2 3 8 2009-01-03 Grétrystraat 63 Brussels None Belgium 1000 5.94

3 4 14 2009-01-06 8210 111 ST NW Edmonton AB Canada T6G 2C7 8.91

4 5 23 2009-01-11 69 Salem Street Boston MA USA 2113 13.86

... ... ... ... ... ... ... ... ... ...

319 N. Frances
407 408 25 2013-12-05 Madison WI USA 53703 3.96
Street

796 Dundas Street

408 409 29 2013-12-06 Toronto ON Canada M6J 1V1 5.94
West

Rua dos
Campeões
409 410 35 2013-12-09 Porto None Portugal None 8.91
Europeus de
Viena, 4350

410 411 44 2013-12-14 Porthaninkatu 9 Helsinki None Finland 00530 13.86

12,Community
411 412 58 2013-12-22 Delhi None India 110017 1.99
Centre

412 rows × 9 columns

In [2]:

df_2 = _deepnote_execute_sql('SELECT "InvoiceDate", "BillingCountry" FROM "Invoice"', 'SQ

L_5C2E9A9B_B591_4FC4_ADD5_829EB3188444')
df_2
Out[2]:

InvoiceDate BillingCountry

0 2009-01-01 Germany

1 2009-01-02 Norway

2 2009-01-03 Belgium

3 2009-01-06 Canada

4 2009-01-11 USA

... ... ...

407 2013-12-05 USA

408 2013-12-06 Canada

409 2013-12-09 Portugal

410 2013-12-14 Finland

411 2013-12-22 India

412 rows × 2 columns

In [3]:
df_3 = _deepnote_execute_sql('SELECT * FROM "Invoice" WHERE "BillingCountry" = \' Germany
\' ', 'SQL_5C2E9A9B_B591_4FC4_ADD5_829EB3188444')
df_3
Out[3]:

InvoiceId CustomerId InvoiceDate BillingAddress BillingCity BillingState BillingCountry BillingPostalCode Total

Theodor-Heuss-
0 1 2 2009-01-01 Stuttgart None Germany 70174 1.98
Straße 34

1 6 37 2009-01-19 Berger Straße 10 Frankfurt None Germany 60316 0.99

Barbarossastraße
2 7 38 2009-02-01 Berlin None Germany 10779 1.98
19

Theodor-Heuss-
3 12 2 2009-02-11 Stuttgart None Germany 70174 13.86
Straße 34

4 29 36 2009-05-05 Tauentzienstraße 8 Berlin None Germany 10789 1.98

Barbarossastraße
5 30 38 2009-05-06 Berlin None Germany 10779 3.96
19

6 40 36 2009-06-15 Tauentzienstraße 8 Berlin None Germany 10789 13.86

Barbarossastraße
7 52 38 2009-08-08 Berlin None Germany 10779 5.94
19

Theodor-Heuss-
8 67 2 2009-10-12 Stuttgart None Germany 70174 8.91
Straße 34

9 95 36 2010-02-13 Tauentzienstraße 8 Berlin None Germany 10789 8.91

Barbarossastraße
10 104 38 2010-03-29 Berlin None Germany 10779 0.99
19

11 127 37 2010-07-13 Berger Straße 10 Frankfurt None Germany 60316 1.98

12 138 37 2010-08-23 Berger Straße 10 Frankfurt None Germany 60316 13.86

13 193 37 2011-04-23 Berger Straße 10 Frankfurt None Germany 60316 14.91

Theodor-Heuss-
14 196 2 2011-05-19 Stuttgart None Germany 70174 1.98
Straße 34

Theodor-Heuss-
15 219 2 2011-08-21 Stuttgart None Germany 70174 3.96
Straße 34

16 224 36 2011-09-20 Tauentzienstraße 8 Berlin None Germany 10789 1.98

Barbarossastraße
17 225 38 2011-09-20 Berlin None Germany 10779 1.98
19

Barbarossastraße
18 236 38 2011-10-31 Berlin None Germany 10779 13.86
19

Theodor-Heuss-
19 241 2 2011-11-23 Stuttgart None Germany 70174 5.94
Straße 34

20 247 36 2011-12-23 Tauentzienstraße 8 Berlin None Germany 10789 3.96

21 269 36 2012-03-26 Tauentzienstraße 8 Berlin None Germany 10789 5.94

Barbarossastraße
22 291 38 2012-06-30 Berlin None Germany 10779 8.91
19

Theodor-Heuss-
23 293 2 2012-07-13 Stuttgart None Germany 70174 0.99
Straße 34

24 321 36 2012-11-14 Tauentzienstraße 8 Berlin None Germany 10789 0.99

25 322 37 2012-11-27 Berger Straße 10 Frankfurt None Germany 60316 1.98

26 345 37 2013-03-01 Berger Straße 10 Frankfurt None Germany 60316 3.96

27 367 37 2013-06-03 Berger Straße 10 Frankfurt None Germany 60316 5.94

In [5]:
df_4 = _deepnote_execute_sql('SELECT COUNT(1) AS Jumlah FROM "Invoice" WHERE "BillingCoun
try" = \' Germany\' ', 'SQL_5C2E9A9B_B591_4FC4_ADD5_829EB3188444')
df_4
Out[5]:

jumlah

0 28

In [8]:
df_5 = _deepnote_execute_sql('SELECT DISTINCT("BillingCountry"), COUNT(1) AS "TotalBought
" \n FROM "Invoice"\n GROUP BY "BillingCountry"', 'SQL_5C2E9A9B_B591_4FC4_ADD5_829EB3188444
')
df_5
Out[8]:

BillingCountry TotalBought

0 Australia 7

1 Denmark 7

2 Netherlands 7

3 Belgium 7

4 India 13

5 Chile 7

6 Canada 56

7 Czech Republic 14

8 Norway 7

9 Sweden 7

10 United Kingdom 21

11 France 35

12 Spain 7

13 Argentina 7

14 Austria 7

15 Poland 7

16 Brazil 35

17 Italy 7

18 Hungary 7

19 Finland 7

20 Germany 28

21 Ireland 7

22 USA 91

23 Portugal 14

In [11]:
df_6 = _deepnote_execute_sql('SELECT DISTINCT("BillingCountry"), COUNT(1) AS "TotalBought
" \n FROM "Invoice"\n GROUP BY "BillingCountry"\n ORDER BY "TotalBought" DESC\n LIMIT 5', 'SQ
L_5C2E9A9B_B591_4FC4_ADD5_829EB3188444')
df_6
Out[11]:

BillingCountry TotalBought

0 USA 91

1 Canada 56
1 Canada 56
BillingCountry TotalBought
2 Brazil 35

3 France 35

4 Germany 28

In [15]:
df_7 = _deepnote_execute_sql('SELECT "BillingCountry", COUNT(1) FROM "Invoice" WHERE "Bil
lingCountry" = \' Sweden\' OR "BillingCountry" = \' France\' GROUP BY "BillingCountry"', '
SQL_5C2E9A9B_B591_4FC4_ADD5_829EB3188444')
df_7

Out[15]:

BillingCountry count

0 Sweden 7

1 France 35

In [16]:
df_8 = _deepnote_execute_sql('SELECT * FROM "Invoice"\n JOIN "Customer" ON "Customer"."Cus
tomerId" = "Invoice"."CustomerId"', 'SQL_5C2E9A9B_B591_4FC4_ADD5_829EB3188444')
df_8
Out[16]:

InvoiceId CustomerId InvoiceDate BillingAddress BillingCity BillingState BillingCountry BillingPostalCode Total CustomerId

Theodor-
0 1 2 2009-01-01 Heuss-Straße Stuttgart None Germany 70174 1.98
34

Ullevålsveien
1 2 4 2009-01-02 Oslo None Norway 0171 3.96
14

Grétrystraat
2 3 8 2009-01-03 Brussels None Belgium 1000 5.94
63

8210 111 ST
3 4 14 2009-01-06 Edmonton AB Canada T6G 2C7 8.91
NW

69 Salem
4 5 23 2009-01-11 Boston MA USA 2113 13.86
Street

... ... ... ... ... ... ... ... ... ...

319 N.
407 408 25 2013-12-05 Frances Madison WI USA 53703 3.96
Street

796 Dundas
408 409 29 2013-12-06 Toronto ON Canada M6J 1V1 5.94
Street West

Rua dos
Campeões
409 410 35 2013-12-09 Porto None Portugal None 8.91
Europeus de
Viena, 4350

Porthaninkatu
410 411 44 2013-12-14 Helsinki None Finland 00530 13.86
9

12,Community
411 412 58 2013-12-22 Delhi None India 110017 1.99
Centre

412 rows × 22 columns

In [18]:
df_9 = _deepnote_execute_sql('SELECT "Invoice"."CustomerId", "FirstName", "LastName", "To
tal" FROM "Invoice"\n JOIN "Customer" ON "Customer"."CustomerId" = "Invoice"."CustomerId"'
, 'SQL_5C2E9A9B_B591_4FC4_ADD5_829EB3188444')
df_9
Out[18]:

CustomerId FirstName LastName Total

0 2 Leonie Köhler 1.98

1 4 Bjørn Hansen 3.96

2 8 Daan Peeters 5.94

3 14 Mark Philips 8.91

4 23 John Gordon 13.86

... ... ... ... ...

407 25 Victor Stevens 3.96

408 29 Robert Brown 5.94

409 35 Madalena Sampaio 8.91

410 44 Terhi Hämäläinen 13.86

411 58 Manoj Pareek 1.99

412 rows × 4 columns

In [21]:
df_10 = _deepnote_execute_sql('SELECT * FROM "Invoice" \n JOIN "Customer" ON "Invoice"."Cu
stomerId" = "Customer"."CustomerId"\n WHERE "Total" >= 10 \n ORDER BY "Total" DESC', 'SQL_5
C2E9A9B_B591_4FC4_ADD5_829EB3188444')
df_10
Out[21]:

InvoiceId CustomerId InvoiceDate BillingAddress BillingCity BillingState BillingCountry BillingPostalCode Total CustomerId

Czech
0 404 6 2013-11-13 Rilská 3174/6 Prague None 14300 25.86
Republic

2211 W Berry Fort

1 299 26 2012-08-05 TX USA 76110 23.86 26
Street Worth

3 Chatham
2 194 46 2011-04-28 Dublin Dublin Ireland None 21.86 46
Street

Erzsébet krt.
3 96 45 2010-02-18 Budapest None Hungary H-1073 21.86 45
58.

319 N.
4 201 25 2011-05-29 Frances Madison WI USA 53703 18.86 25
Street

... ... ... ... ... ... ... ... ... ... ...

194A Chain
59 376 31 2013-07-12 Halifax NS Canada B3S 1C5 13.86 31
Lake Drive

Rua Dr.
60 383 10 2013-08-12 Falcão Filho, São Paulo SP Brazil 01007-010 13.86 10
155

Salt Lake
61 311 28 2012-09-28 302 S 700 E UT USA 84102 11.94 28
City

Rua da
62 312 34 2012-10-01 Lisbon None Portugal None 10.91 34
Assunção 53
 Assunção 53
InvoiceId CustomerId InvoiceDate BillingAddress BillingCity BillingState BillingCountry BillingPostalCode Total CustomerId

1 Microsoft
63 298 17 2012-07-31 Redmond WA USA 98052-8300 10.91 17
Way

64 rows × 22 columns

Data Filter LIKE

WHERE = case sensitive

Syntax => SELECT ... WHERE LIKE '...'

In [5]:
df_12 = _deepnote_execute_sql('SELECT * FROM "Artist" WHERE "Name" LIKE \' Led%\' ', 'SQL_
5C2E9A9B_B591_4FC4_ADD5_829EB3188444')
df_12
Out[5]:

ArtistId Name

0 22 Led Zeppelin

In [4]:
df_11 = _deepnote_execute_sql('SELECT * FROM "Artist" WHERE LOWER("Name") LIKE LOWER(\' L
ED%\' )', 'SQL_5C2E9A9B_B591_4FC4_ADD5_829EB3188444')
df_11
Out[4]:

ArtistId Name

0 22 Led Zeppelin

Pilih semua Track yang dimiliki band black sabbath

In [22]:
df_13 = _deepnote_execute_sql('SELECT "Artist"."Name", "Track"."Name" FROM "Artist"\n JOIN
"Album" ON "Album"."ArtistId" = "Artist"."ArtistId"\n JOIN "Track" ON "Track"."AlbumId" =
"Album"."AlbumId"\n WHERE UPPER("Artist"."Name") = UPPER(\' black sabbath\' )', 'SQL_5C2E9A
9B_B591_4FC4_ADD5_829EB3188444')
df_13
Out[22]:

Name Name

0 Black Sabbath Black Sabbath

1 Black Sabbath The Wizard

2 Black Sabbath Behind The Wall Of Sleep

3 Black Sabbath N.I.B.

4 Black Sabbath Evil Woman

5 Black Sabbath Sleeping Village

6 Black Sabbath Warning

7 Black Sabbath Wheels Of Confusion / The Straightener

8 Black Sabbath Tomorrow's Dream

 8 Black Sabbath Tomorrow's Dream
Name Name
9 Black Sabbath Changes

10 Black Sabbath FX

11 Black Sabbath Supernaut

12 Black Sabbath Snowblind

13 Black Sabbath Cornucopia

14 Black Sabbath Laguna Sunrise

15 Black Sabbath St. Vitus Dance

16 Black Sabbath Under The Sun/Every Day Comes and Goes

In [23]:
df_18 = _deepnote_execute_sql('SELECT "Artist"."Name", "Track"."Name" FROM "Artist"\n JOIN
"Album" ON "Album"."ArtistId" = "Artist"."ArtistId"\n JOIN "Track" ON "Track"."AlbumId" =
"Album"."AlbumId"\n WHERE UPPER("Artist"."Name") LIKE UPPER(\' %black%\' )', 'SQL_5C2E9A9B_
B591_4FC4_ADD5_829EB3188444')
df_18
Out[23]:

Name Name

0 Black Label Society Intro/ Low Down

1 Black Label Society 13 Years Of Grief

2 Black Label Society Stronger Than Death

3 Black Label Society All For You

4 Black Label Society Super Terrorizer

5 Black Label Society Phoney Smile Fake Hellos

6 Black Label Society Lost My Better Half

7 Black Label Society Bored To Tears

8 Black Label Society A.N.D.R.O.T.A.Z.

9 Black Label Society Born To Booze

10 Black Label Society World Of Trouble

11 Black Label Society No More Tears

12 Black Label Society The Begining... At Last

13 Black Label Society Heart Of Gold

14 Black Label Society Snowblind

15 Black Label Society Like A Bird

16 Black Label Society Blood In The Wall

17 Black Label Society The Beginning...At Last

18 Black Sabbath Black Sabbath

19 Black Sabbath The Wizard

20 Black Sabbath Behind The Wall Of Sleep

21 Black Sabbath N.I.B.

22 Black Sabbath Evil Woman

23 Black Sabbath Sleeping Village

24 Black Sabbath Warning

25 Black Sabbath Wheels Of Confusion / The Straightener

26 Black Sabbath Tomorrow's Dream

27 Black Sabbath Changes

28 Black Sabbath FX
28 Black Sabbath FX
Name Name
29 Black Sabbath Supernaut

30 Black Sabbath Snowblind

31 Black Sabbath Cornucopia

32 Black Sabbath Laguna Sunrise

33 Black Sabbath St. Vitus Dance

34 Black Sabbath Under The Sun/Every Day Comes and Goes

35 The Black Crowes Midnight From The Inside Out

36 The Black Crowes Sting Me

37 The Black Crowes Thick & Thin

38 The Black Crowes Greasy Grass River

39 The Black Crowes Sometimes Salvation

40 The Black Crowes Cursed Diamonds

41 The Black Crowes Miracle To Me

42 The Black Crowes Wiser Time

43 The Black Crowes Girl From A Pawnshop

44 The Black Crowes Cosmic Fiend

45 The Black Crowes Black Moon Creeping

46 The Black Crowes High Head Blues

47 The Black Crowes Title Song

48 The Black Crowes She Talks To Angels

49 The Black Crowes Twice As Hard

50 The Black Crowes Lickin'

51 The Black Crowes Soul Singing

52 The Black Crowes Hard To Handle

53 The Black Crowes Remedy

INNER JOIN

In [26]:
df_17 = _deepnote_execute_sql('SELECT "cust"."name", "ords"."product" FROM "cust" \n JOIN
"ords" ON "cust"."id" = "ords"."cust_id"', 'SQL_5C2E9A9B_B591_4FC4_ADD5_829EB3188444')
df_17
Out[26]:

name product

0 A TV

1 A REMOTE

2 B BATU

3 D KUDA

4 E BONEKA

LEFT JOIN

In [29]:
df_18 = _deepnote_execute_sql('SELECT "cust"."name", "ords"."product" FROM "cust" \n LEFT
JOIN "ords" ON "cust"."id" = "ords"."cust_id"\n WHERE "ords"."id" IS NULL', 'SQL_5C2E9A9B_
B591_4FC4_ADD5_829EB3188444')
df_18
Out[29]:

name product

0 C None

RIGHT JOIN

In [31]:
df_19 = _deepnote_execute_sql('SELECT "cust"."name", "ords"."product" FROM "cust" \n RIGH
T JOIN "ords" ON "cust"."id" = "ords"."cust_id"', 'SQL_5C2E9A9B_B591_4FC4_ADD5_829EB31884
44')
df_19
Out[31]:

name product

0 A TV

1 A REMOTE

2 B BATU

3 D KUDA

4 E BONEKA

FULL OUTER JOIN

In [32]:
df_20 = _deepnote_execute_sql('SELECT "cust"."name", "ords"."product" FROM "cust" \n FULL
OUTER JOIN "ords" ON "cust"."id" = "ords"."cust_id"', 'SQL_5C2E9A9B_B591_4FC4_ADD5_829EB3
188444')
df_20
Out[32]:

name product

0 A TV

1 A REMOTE

2 B BATU

3 D KUDA

4 E BONEKA

5 C None

In [34]:
df_21 = _deepnote_execute_sql('SELECT "cust"."name", "ords"."product" FROM "cust" \n CROS
S JOIN "ords"', 'SQL_5C2E9A9B_B591_4FC4_ADD5_829EB3188444')
df_21
Out[34]:

name product

0 A TV

1 A REMOTE

2 A BATU

3 A KUDA

4 A BONEKA
 5 name
B TV
product

6 B REMOTE

7 B BATU

8 B KUDA

9 B BONEKA

10 C TV

11 C REMOTE

12 C BATU

13 C KUDA

14 C BONEKA

15 D TV

16 D REMOTE

17 D BATU

18 D KUDA

19 D BONEKA

20 E TV

21 E REMOTE

22 E BATU

23 E KUDA

24 E BONEKA

IN

In [38]:
df_14 = _deepnote_execute_sql('SELECT * FROM "Artist" WHERE "Name" = \' AC/DC\' OR "Name"
= \' O Rappa\' OR "Name" = \' Kid Abelha\' ', 'SQL_5C2E9A9B_B591_4FC4_ADD5_829EB3188444')
df_14
Out[38]:

ArtistId Name

0 1 AC/DC

1 36 O Rappa

2 44 Kid Abelha

In [39]:
df_22 = _deepnote_execute_sql('SELECT * FROM "Artist" WHERE "Name" IN (\' AC/DC\' , \' O Ra
ppa\' , \' Kid Abelha\' )', 'SQL_5C2E9A9B_B591_4FC4_ADD5_829EB3188444')
df_22
Out[39]:

ArtistId Name

0 1 AC/DC

1 36 O Rappa

2 44 Kid Abelha

In [45]:

df_22 = _deepnote_execute_sql('SELECT "h1"."name" "orang1", "h2"."relation", "h2"."name"

"orang2" FROM "human" "h1" \n JOIN "human" "h2" \n ON "h2"."human_id" = "h1"."id"', 'SQL_5
C2E9A9B_B591_4FC4_ADD5_829EB3188444')
df_22

Out[45]:

orang1 relation orang2

0 ali adik agus

Created in Deepnote