Database with R and Spatial Data

Library

library(odbc)
## Warning: package 'odbc' was built under R version 4.0.5
library(DBI)
## Warning: package 'DBI' was built under R version 4.0.5
library(RSQLite)
library(kableExtra)
library(dplyr)
## 
## Attaching package: 'dplyr'
## The following object is masked from 'package:kableExtra':
## 
##     group_rows
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
chinook<-DBI::dbConnect(RSQLite::SQLite(), "chinook.db")

Retrieving Tables

Tabel-tabel yang akan digunakan pada percobaan mergin tabel adalah tabel albums,artists,playlists,playlist_track,tracks,customers,invoices.

albums = dbGetQuery(chinook, "SELECT * FROM albums")
artist = dbGetQuery(chinook, "SELECT * FROM artists")
playlist = dbGetQuery(chinook, "SELECT * FROM playlists")
playlist_track = dbGetQuery(chinook, "SELECT * FROM playlist_track")
tracks = dbGetQuery(chinook, "SELECT * FROM tracks")
customers = dbGetQuery(chinook, "SELECT * FROM customers")
invoices = dbGetQuery(chinook, "SELECT * FROM invoices")
Tabel albums
AlbumId Title ArtistId
1 For Those About To Rock We Salute You 1
2 Balls to the Wall 2
3 Restless and Wild 2
Tabel artists
ArtistId Name
1 AC/DC
2 Accept
3 Aerosmith
Tabel playlists
PlaylistId Name
1 Music
2 Movies
3 TV Shows
Tabel playlist tracks
PlaylistId TrackId
1 3402
1 3389
1 3390
Tabel tracks
TrackId Name AlbumId MediaTypeId GenreId Composer Milliseconds Bytes UnitPrice
1 For Those About To Rock (We Salute You) 1 1 1 Angus Young, Malcolm Young, Brian Johnson 343719 11170334 0.99
2 Balls to the Wall 2 2 1 NA 342562 5510424 0.99
3 Fast As a Shark 3 2 1 F. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman 230619 3990994 0.99
Tabel customers
CustomerId FirstName LastName Company Address City State Country PostalCode Phone Fax Email SupportRepId
1 Luís Gonçalves Embraer - Empresa Brasileira de Aeronáutica S.A. Av. Brigadeiro Faria Lima, 2170 São José dos Campos SP Brazil 12227-000 +55 (12) 3923-5555 +55 (12) 3923-5566 3
2 Leonie Köhler NA Theodor-Heuss-Straße 34 Stuttgart NA Germany 70174 +49 0711 2842222 NA 5
3 François Tremblay NA 1498 rue Bélanger Montréal QC Canada H2G 1A7 +1 (514) 721-4711 NA 3
Tabel invoices
InvoiceId CustomerId InvoiceDate BillingAddress BillingCity BillingState BillingCountry BillingPostalCode Total
1 2 2009-01-01 00:00:00 Theodor-Heuss-Straße 34 Stuttgart NA Germany 70174 1.98
2 4 2009-01-02 00:00:00 Ullevålsveien 14 Oslo NA Norway 0171 3.96
3 8 2009-01-03 00:00:00 Grétrystraat 63 Brussels NA Belgium 1000 5.94

Advance Query

Operations on NULL values

Berikut contoh query menggunakan Operations on NULL values

SELECT *
    FROM customers
    WHERE Fax IS NULL;
Displaying records 1 - 10
CustomerId FirstName LastName Company Address City State Country PostalCode Phone Fax Email SupportRepId
2 Leonie Köhler NA Theodor-Heuss-Straße 34 Stuttgart NA Germany 70174 +49 0711 2842222 NA 5
3 François Tremblay NA 1498 rue Bélanger Montréal QC Canada H2G 1A7 +1 (514) 721-4711 NA 3
4 Bjørn Hansen NA Ullevålsveien 14 Oslo NA Norway 0171 +47 22 44 22 22 NA 4
6 Helena Holý NA Rilská 3174/6 Prague NA Czech Republic 14300 +420 2 4177 0449 NA 5
7 Astrid Gruber NA Rotenturmstraße 4, 1010 Innere Stadt Vienne NA Austria 1010 +43 01 5134505 NA 5
8 Daan Peeters NA Grétrystraat 63 Brussels NA Belgium 1000 +32 02 219 03 03 NA 4
9 Kara Nielsen NA Sønder Boulevard 51 Copenhagen NA Denmark 1720 +453 3331 9991 NA 4
20 Dan Miller NA 541 Del Medio Avenue Mountain View CA USA 94040-111 +1 (650) 644-3358 NA 4
21 Kathy Chase NA 801 W 4th Street Reno NV USA 89503 +1 (775) 223-7665 NA 5
22 Heather Leacock NA 120 S Orange Ave Orlando FL USA 32801 +1 (407) 999-7788 NA 4

Nested Queries

Berikut contoh query menggunakan Nested Queries

SELECT *
    FROM customers
    WHERE CustomerId IN (SELECT CustomerId
        FROM invoices
        WHERE Total>5);
Displaying records 1 - 10
CustomerId FirstName LastName Company Address City State Country PostalCode Phone Fax Email SupportRepId
1 Luís Gonçalves Embraer - Empresa Brasileira de Aeronáutica S.A. Av. Brigadeiro Faria Lima, 2170 São José dos Campos SP Brazil 12227-000 +55 (12) 3923-5555 +55 (12) 3923-5566 3
2 Leonie Köhler NA Theodor-Heuss-Straße 34 Stuttgart NA Germany 70174 +49 0711 2842222 NA 5
3 François Tremblay NA 1498 rue Bélanger Montréal QC Canada H2G 1A7 +1 (514) 721-4711 NA 3
4 Bjørn Hansen NA Ullevålsveien 14 Oslo NA Norway 0171 +47 22 44 22 22 NA 4
5 František Wichterlová JetBrains s.r.o. Klanova 9/506 Prague NA Czech Republic 14700 +420 2 4172 5555 +420 2 4172 5555 4
6 Helena Holý NA Rilská 3174/6 Prague NA Czech Republic 14300 +420 2 4177 0449 NA 5
7 Astrid Gruber NA Rotenturmstraße 4, 1010 Innere Stadt Vienne NA Austria 1010 +43 01 5134505 NA 5
8 Daan Peeters NA Grétrystraat 63 Brussels NA Belgium 1000 +32 02 219 03 03 NA 4
9 Kara Nielsen NA Sønder Boulevard 51 Copenhagen NA Denmark 1720 +453 3331 9991 NA 4
10 Eduardo Martins Woodstock Discos Rua Dr. Falcão Filho, 155 São Paulo SP Brazil 01007-010 +55 (11) 3033-5446 +55 (11) 3033-4564 4

Correlated Nested Queries

Berikut contoh query menggunakan Correlated Nested Queries

SELECT DISTINCT customers.firstname
    FROM customers , invoices
    WHERE customers.CustomerId= invoices.CustomerId 
    And customers.City= "Prague"
    And invoices.BillingCity= "Prague" 
    ;
2 records
FirstName
František
Helena

EXISTS and NOT EXISTS Functions

Berikut contoh query menggunakan EXISTS

SELECT *
    FROM customers
    WHERE  EXISTS (SELECT CustomerId
        FROM invoices
        WHERE Total>5);
Displaying records 1 - 10
CustomerId FirstName LastName Company Address City State Country PostalCode Phone Fax Email SupportRepId
1 Luís Gonçalves Embraer - Empresa Brasileira de Aeronáutica S.A. Av. Brigadeiro Faria Lima, 2170 São José dos Campos SP Brazil 12227-000 +55 (12) 3923-5555 +55 (12) 3923-5566 3
2 Leonie Köhler NA Theodor-Heuss-Straße 34 Stuttgart NA Germany 70174 +49 0711 2842222 NA 5
3 François Tremblay NA 1498 rue Bélanger Montréal QC Canada H2G 1A7 +1 (514) 721-4711 NA 3
4 Bjørn Hansen NA Ullevålsveien 14 Oslo NA Norway 0171 +47 22 44 22 22 NA 4
5 František Wichterlová JetBrains s.r.o. Klanova 9/506 Prague NA Czech Republic 14700 +420 2 4172 5555 +420 2 4172 5555 4
6 Helena Holý NA Rilská 3174/6 Prague NA Czech Republic 14300 +420 2 4177 0449 NA 5
7 Astrid Gruber NA Rotenturmstraße 4, 1010 Innere Stadt Vienne NA Austria 1010 +43 01 5134505 NA 5
8 Daan Peeters NA Grétrystraat 63 Brussels NA Belgium 1000 +32 02 219 03 03 NA 4
9 Kara Nielsen NA Sønder Boulevard 51 Copenhagen NA Denmark 1720 +453 3331 9991 NA 4
10 Eduardo Martins Woodstock Discos Rua Dr. Falcão Filho, 155 São Paulo SP Brazil 01007-010 +55 (11) 3033-5446 +55 (11) 3033-4564 4

Berikut contoh query menggunakan NOT EXISTS

SELECT *
    FROM customers
    WHERE NOT EXISTS (SELECT CustomerId
        FROM invoices);
0 records
CustomerId FirstName LastName Company Address City State Country PostalCode Phone Fax Email SupportRepId

EXPLICIT SETS

Berikut contoh query menggunakan EXPLICIT SETS

SELECT firstname ,lastname
    FROM customers
    WHERE Country IN ('Brazil','Canada');
Displaying records 1 - 10
FirstName LastName
Luís Gonçalves
François Tremblay
Eduardo Martins
Alexandre Rocha
Roberto Almeida
Fernanda Ramos
Mark Philips
Jennifer Peterson
Robert Brown
Edward Francis

RENAMING Attribute

Berikut contoh query RENAMING Attribute

SELECT Name AS NAMA
    FROM playlists
    WHERE PlaylistId = 6;
1 records
NAMA
Audiobooks

Joined Relations

Berikut contoh query menggunakan Joined Relations

SELECT * FROM customers  CROSS JOIN invoices 
WHERE customers.CustomerId= invoices.CustomerId
Displaying records 1 - 10
CustomerId FirstName LastName Company Address City State Country PostalCode Phone Fax Email SupportRepId InvoiceId CustomerId InvoiceDate BillingAddress BillingCity BillingState BillingCountry BillingPostalCode Total
1 Luís Gonçalves Embraer - Empresa Brasileira de Aeronáutica S.A. Av. Brigadeiro Faria Lima, 2170 São José dos Campos SP Brazil 12227-000 +55 (12) 3923-5555 +55 (12) 3923-5566 3 98 1 2010-03-11 00:00:00 Av. Brigadeiro Faria Lima, 2170 São José dos Campos SP Brazil 12227-000 3.98
1 Luís Gonçalves Embraer - Empresa Brasileira de Aeronáutica S.A. Av. Brigadeiro Faria Lima, 2170 São José dos Campos SP Brazil 12227-000 +55 (12) 3923-5555 +55 (12) 3923-5566 3 121 1 2010-06-13 00:00:00 Av. Brigadeiro Faria Lima, 2170 São José dos Campos SP Brazil 12227-000 3.96
1 Luís Gonçalves Embraer - Empresa Brasileira de Aeronáutica S.A. Av. Brigadeiro Faria Lima, 2170 São José dos Campos SP Brazil 12227-000 +55 (12) 3923-5555 +55 (12) 3923-5566 3 143 1 2010-09-15 00:00:00 Av. Brigadeiro Faria Lima, 2170 São José dos Campos SP Brazil 12227-000 5.94
1 Luís Gonçalves Embraer - Empresa Brasileira de Aeronáutica S.A. Av. Brigadeiro Faria Lima, 2170 São José dos Campos SP Brazil 12227-000 +55 (12) 3923-5555 +55 (12) 3923-5566 3 195 1 2011-05-06 00:00:00 Av. Brigadeiro Faria Lima, 2170 São José dos Campos SP Brazil 12227-000 0.99
1 Luís Gonçalves Embraer - Empresa Brasileira de Aeronáutica S.A. Av. Brigadeiro Faria Lima, 2170 São José dos Campos SP Brazil 12227-000 +55 (12) 3923-5555 +55 (12) 3923-5566 3 316 1 2012-10-27 00:00:00 Av. Brigadeiro Faria Lima, 2170 São José dos Campos SP Brazil 12227-000 1.98
1 Luís Gonçalves Embraer - Empresa Brasileira de Aeronáutica S.A. Av. Brigadeiro Faria Lima, 2170 São José dos Campos SP Brazil 12227-000 +55 (12) 3923-5555 +55 (12) 3923-5566 3 327 1 2012-12-07 00:00:00 Av. Brigadeiro Faria Lima, 2170 São José dos Campos SP Brazil 12227-000 13.86
1 Luís Gonçalves Embraer - Empresa Brasileira de Aeronáutica S.A. Av. Brigadeiro Faria Lima, 2170 São José dos Campos SP Brazil 12227-000 +55 (12) 3923-5555 +55 (12) 3923-5566 3 382 1 2013-08-07 00:00:00 Av. Brigadeiro Faria Lima, 2170 São José dos Campos SP Brazil 12227-000 8.91
2 Leonie Köhler NA Theodor-Heuss-Straße 34 Stuttgart NA Germany 70174 +49 0711 2842222 NA 5 1 2 2009-01-01 00:00:00 Theodor-Heuss-Straße 34 Stuttgart NA Germany 70174 1.98
2 Leonie Köhler NA Theodor-Heuss-Straße 34 Stuttgart NA Germany 70174 +49 0711 2842222 NA 5 12 2 2009-02-11 00:00:00 Theodor-Heuss-Straße 34 Stuttgart NA Germany 70174 13.86
2 Leonie Köhler NA Theodor-Heuss-Straße 34 Stuttgart NA Germany 70174 +49 0711 2842222 NA 5 67 2 2009-10-12 00:00:00 Theodor-Heuss-Straße 34 Stuttgart NA Germany 70174 8.91

Grouping

Berikut contoh query menggunakan Grouping

SELECT BillingCity, COUNT(*), SUM(Total) AS Total_invo, AVG(Total) AS average_invo 
FROM Invoices
GROUP BY BillingCity;
Displaying records 1 - 10
BillingCity COUNT(*) Total_invo average_invo
Amsterdam 7 40.62 5.802857
Bangalore 6 36.64 6.106667
Berlin 14 75.24 5.374286
Bordeaux 7 39.62 5.660000
Boston 7 37.62 5.374286
Brasília 7 37.62 5.374286
Brussels 7 37.62 5.374286
Budapest 7 45.62 6.517143
Buenos Aires 7 37.62 5.374286
Chicago 7 43.62 6.231429

Having Clause

Berikut contoh query menggunakan Having Clause

SELECT BillingCity, COUNT(*), SUM(Total) AS Total_invo, AVG(Total) AS average_invo 
FROM Invoices
GROUP BY BillingCity;
HAVING COUNT(*)>7
Displaying records 1 - 10
BillingCity COUNT(*) Total_invo average_invo
Amsterdam 7 40.62 5.802857
Bangalore 6 36.64 6.106667
Berlin 14 75.24 5.374286
Bordeaux 7 39.62 5.660000
Boston 7 37.62 5.374286
Brasília 7 37.62 5.374286
Brussels 7 37.62 5.374286
Budapest 7 45.62 6.517143
Buenos Aires 7 37.62 5.374286
Chicago 7 43.62 6.231429