Advanced SQL Query

Pendahuluan

Database yang digunakan dalam materi advanced query adalah Northwind. Hal pertama yang dilakukan adalah membuat koneksi ke database tersebut dengan menggunakan dbConnect pada package DBI, kemudian membaca database tersebut menggunakan RSQLite::SQLite().

nw <- DBI::dbConnect(RSQLite::SQLite(), "C:/sqlite/db/Northwind_large.sqlite")

Untuk melihat tabel-tabel di dalam database Northwind dapat menggunakan RSQLite::dbListTables. Terlihat bahwa di dalam database Northwind terdiri dari 14 tabel mulai dari tabel Category sampai dengan tabel Territory.

RSQLite::dbListTables(nw)
 [1] "Category"             "Customer"             "CustomerCustomerDemo"
 [4] "CustomerDemographic"  "Employee"             "EmployeeTerritory"   
 [7] "Order"                "OrderDetail"          "Product"             
[10] "ProductDetails_V"     "Region"               "Shipper"             
[13] "Supplier"             "Territory"

Berikut adalah ERD dari database Northwind yang digunakan

ERD Database Northwind

Advanced SQL Query

Selanjutnya, menggunakan database Northwind tersebut, akan dilakukan beberapa teknik advanced SQL Query untuk mendapatkan output yang sesuai.

Operations on NULL Values

Menampilkan nama perusahaan (CompanyName) dan kota (City) yang tidak memiliki nomer fax (Fax nya adalah NULL).

SELECT Id, CompanyName, City, Fax
    FROM Customer
    WHERE Fax IS NULL;

Nested Queries

Menampilkan nama perusahaan (CompanyName) yang menjadi supplier dari produk Chef Anton’s Gumbo Mix.

SELECT CompanyName
    FROM Supplier
    WHERE Id IN (SELECT SupplierId
            FROM Product
            WHERE ProductName ="Chef Anton's Gumbo Mix");

Correlated Nested Queries

Menampilkan kategori produk (CategoryName) dan deskripsi kategori (Description) dari produk yang sudah tidak dijaul/diskontinyu (Discontinued=1).

SELECT C.CategoryName, C.Description
    FROM Product as P, Category as C
    WHERE P.CategoryId = C.Id
      AND P.Discontinued = 1;

EXISTS Functions

Menampilkan nama perusahaan (CompanyName) dana alamat (Address) yang menjadi supplier produk dalam kategori Meat/Poultry.

SELECT S.CompanyName, S.Address
    FROM Supplier AS S
        WHERE EXISTS (SELECT P.SupplierId FROM Product AS P, Category AS C 
                      WHERE C.CategoryName='Meat/Poultry' AND P.CategoryId=C.Id AND S.Id=P.SupplierId);

NOT EXISTS Functions

Menampikan nama produk (ProductName) dan harga per unit (UnitPrice) dengan Id supplier selain 1.

SELECT P.ProductName, P.UnitPrice
    FROM Product AS P
        WHERE NOT EXISTS (SELECT * FROM Supplier AS S WHERE S.Id = 1 AND P.SupplierId=S.Id);

EXPLICIT SETS

Menampilkan nama produk (ProductName), harga per unit (UnitPrice), identitas supplier (SupplierId) dengan id suppiler 1 atau 3.

SELECT ProductName, UnitPrice, SupplierId
    FROM Product
        WHERE SupplierId IN (1,3);

RENAMING Attribute

Mengganti CompanyName menjadi Perusahaan dan ContactName menjadi Nama dari tabel Customer dimana Country bernama di Germany, Mexico dan UK.

SELECT CompanyName as Perusahaan, ContactName as Nama
    FROM Customer
        WHERE Country IN ("Germany", "Mexico", "UK");

Joined Relations

Menampilkan nama pegawai (LastName, FirstName), jabatan (Title), dan identitas wilayah (TerritoryId) dari tabel Employee dan EmployeeTerritory.

SELECT E.LastName, E.FirstName, E.Title, ET.TerritoryId
    FROM Employee as E, EmployeeTerritory as ET
        WHERE E.Id = ET.EmployeeId;

Aggregate Function

Menampilkan jumlah produk dan rata-rata unit price dari product dengan category id yang sama

SELECT CategoryId, COUNT(*) AS Jumlah_Product, AVG(UnitPrice) AS RataRata_Harga
    FROM Product GROUP BY CategoryId;

Grouping

Menampilkan jumlah produk pada masing-masing supplier.

SELECT SupplierId, COUNT(*) AS Jumlah_Product
    FROM Product GROUP BY SupplierId;

HAVING Clause

Menampilkan category yang jumlah produknya lebih dari 10.

SELECT CategoryId, COUNT(*) AS Jumlah_Product, AVG(UnitPrice) AS RataRata_Harga
    FROM Product GROUP BY CategoryId
    HAVING Jumlah_Product >10;