Query Multitable di MySQL (Studi Kasus)

Di dalam konsep basis data, hampir sebagian besar permasalahan melibatkan relasi antar tabel di dalam proses querying. Query yang melibatkan relasi antar tabel atau multitabel ini biasanya terjadi di dalam model data relasional, atau yang menggunakan relational database management system (RDBMS).

Pada artikel ini akan saya coba untuk memberikan sebuah pembahasan bagaimana melakukan query multitable di MySQL, yang akan dituangkan ke dalam sebuah studi kasus. Studi kasus yang saya angkat adalah sebuah database Car Dealer, dengan 10 buah tabel di dalamnya beserta sejumlah record di setiap tabel. Selanjutnya dari database tersebut saya akan tentukan 10 buah pertanyaan yang nantinya akan dijawab melalui hasil query.

Menyiapkan Database

Untuk keperluan studi kasus ini, pertama kita siapkan databasenya. Buatlah sebuah database di MySQL dengan nama: dbcardealer.

Selanjutnya unduh file MySQL dump berikut ini

Apabila file tersebut diekstrak, maka akan diperoleh sebuah file cardealer_new.sql.

Langkah berikutnya silakan import file tersebut ke dalam database dbcardealer. Anda bisa melakukan hal ini melalui phpMyAdmin atau tool lainnya.

Struktur Tabel Database

Setelah proses import tabel dan record sukses, akan terbentuk 10 buah tabel sebagai berikut beserta relasi antar tabelnya.

Database tersebut merepresentasikan sebuah perusahaan yang menyediakan layanan penjualan mobil dan layanan purna jual berupa service mobil. Perusahaan tersebut hanya menerima service dari mobil yang pernah dijualnya.

Berikut ini adalah penjelasan masing-masing tabelnya:

• Tabel car: berisi data-data mobil yang sedang dijual atau pernah dijualnya
• Tabel customer: berisi data-data pelanggan yang pernah membeli mobil
• Tabel salesperson: berisi data para sales yang melayani penjualan mobil
• Tabel salesinvoice: berisi transaksi penjualan mobil
• Tabel parts: berisi data spare part yang disediakan oleh perusahaan untuk keperluan service
• Tabel service: berisi data jenis layanan service yang ditawarkan
• Tabel mechanic: berisi data tenaga mekanik yang akan menangani service
• Tabel serviceticket: berisi data transaksi service yang pernah ditangani perusahaan
• Tabel servicemechanic: berisi data tenaga mekanik yang pernah menangani service tertentu
• Tabel partsused: berisi data sparepart yang pernah digunakan pada penanganan service tertentu

Problem Statements

Selanjutnya dari database di atas, kita akan mendapatkan informasi-informasi sebagai berikut:

• Siapa nama customer yang paling sering melakukan service?
• Berapa besar total biaya jasa mekanik service untuk service ticket ID: 2?
• Berapa besar total biaya sparepart dari service ticket ID: 2?
• Berapa total profit dari penjualan sparepart dari service ticket ID: 2?
• Tampilkan 5 besar waktu (bulan dan tahun) terjadi service paling banyak!
• Siapa nama mekanik service yang paling banyak mendapat rate ‘Poor’ dari customer?
• Siapa nama customer yang paling banyak memberi rate ‘Poor’ pada mekaniknya?
• Tampilkan merek mobil yang jumlah servicenya lebih dari 20 kali!
• Berapa rata-rata lama penyelesaian waktu service (dalam hari) setiap bulannya selama tahun 2018?
• Buatlah rekap profit per bulan perusahaan dari hasil penjualan sparepart selama tahun 2018!

Solusi Permasalahan

Siapa nama customer yang paling sering melakukan service?

Untuk menjawab pertanyaan ini, kita akan membuat rekap jumlah berapa kali setiap customer pernah melakukan service. Dari data rekap ini nantinya kita sort secara descending. Costumer yang paling banyak melakukan service akan tampak pada data urutan pertama dari rekapnya.

Data rekap akan kita dapatkan dengan memberikan query pada tabel: customer, dan serviceticket. Tabel customer untuk mendapatkan data nama customernya, dan tabel serviceticket untuk melihat data customer yang pernah melakukan service. Kedua tabel ini berelasi pada field costumerID (lihat gambar relasi antar tabel)

Adapun querynya adalah sebagai berikut.

SELECT customer.LastName, count(*) as JumService
FROM customer, serviceticket
WHERE customer.CustomerID = serviceticket.CustomerID
GROUP BY customer.CustomerID
ORDER BY JumService DESC

Untuk membuat rekap jumlah berapa kali service per customer, caranya cukup menggunakan function count(*) yang nantinya digroup berdasarkan customerID nya. Di sini customerID dijadikan acuan dalam grouping karena sifatnya yang unik dari setiap customer.

Hasil dari query di atas adalah sebagai berikut

Berdasarkan hasil query tersebut, tampaklah informasi bahwa yang paling sering melakukan service adalah customer dengan nama Eko.

Berapa besar total biaya jasa mekanik service untuk service ticket ID: 2?

Untuk menjawab pertanyaan tersebut, terlebih dahulu kita tentukan tabel mana kita akan bekerja. Tabel yang akan dipilih untuk diberikan query adalah tabel: service, dan servicemechanic. Tabel service digunakan untuk mendapatkan data tarif service per jam nya, dan tabel servicemechanic untuk mendapatkan data jenis service apa saja yang dilakukan pada service ticket ID = 2. Dalam hal ini pada sebuah service ticket ID dapat dimungkinkan dilakukan beberapa layanan service yang dilakukan oleh beberapa mekanik yang berbeda. Kedua tabel ini berelasi melalui kolom ServiceID.

Setelah didapatkan data layanan service apa saja yang dilakukan pada service ticket ID = 2, dan tarif service perjamnya, selanjutnya tinggal dikalikan dan akhirnya dijumlahkan dengan SUM() untuk mendapatkan total tarifnya. Berikut ini querynya.

SELECT SUM(service.HourlyRate * servicemechanic.Hours) as TotalFee
FROM service, servicemechanic
WHERE service.ServiceID = servicemechanic.ServiceID 
		  AND servicemechanic.ServiceTicketID = '2'

Hasil dari query di atas diperoleh IDR 5.130.000

Berapa besar total biaya sparepart dari service ticket ID: 2?

Untuk menjawab pertanyaan ini, teknik penyelesaiannya mirip dengan pertanyaan sebelumnya. Hanya perbedaannya kita akan mencari data sparepart apa saja yang diperlukan pada service ticket ID = 2, kemudian melookup harga jualnya ke konsumen. Tabel yang akan digunakan untuk query adalah: partsused, dan parts. Tabel partsused nantinya untuk mendapatkan data sparepart yang digunakan pada service ticket ID tertentu, dan tabel parts untuk mendapatkan harga jual sparepartnya (kolom RetailPrice). Selanjutnya harga jual sparepart per buah ini dikalikan dengan banyaknya sparepart yang digunakan (kolom NumberUsed). Terakhir tinggal dijumlah total dengan SUM().

Antara tabel partsused dengan tabel parts berelasi melalui kolom PartsID.

Query yang diberikan untuk menjawab pertanyaan di atas adalah sebagai berikut.

SELECT SUM(partsused.NumberUsed * parts.RetailPrice) as TotalCost
FROM partsused, parts
WHERE partsused.PartsID = parts.PartsID 
		  AND partsused.ServiceTicketID = '2'

Hasil dari query di atas diperoleh jawaban IDR 6.240.000

Berapa total profit dari penjualan sparepart dari service ticket ID: 2?

Untuk menjawab pertanyaan ini, cukup memodifikasi dari query sebelumnya. Profit dari setiap penjualan sparepart dapat diperoleh dengan mencari selisih antara harga jual sparepart (RetailPrice) dengan harga beli sparepart (PurchasePrice) kemudian dikalikan dengan banyaknya quantity sparepartnya.

Query yang diberikan adalah sebagai berikut.

SELECT SUM(partsused.NumberUsed * (parts.RetailPrice - parts.PurchasePrice)) as TotalProfit
FROM partsused, parts
WHERE partsused.PartsID = parts.PartsID 
		  AND partsused.ServiceTicketID = '2'

Hasil query diperoleh total profitnya adalah IDR 1.722.000

Tampilkan 5 besar waktu (bulan dan tahun) terjadinya transaksi service paling banyak!

Untuk menjawab pertanyaan ini, kita hanya cukup menggunakan satu tabel saja untuk bekerja, yaitu tabel serviceticket. Dalam tabel ini terdapat data kapan transaksi service dilakukan (kolom DateReceived). Dalam hal ini kita akan membuat rekap jumlah transaksi berdasarkan tahun dan bulannya. Untuk waktu nanti kita akan susun dalam format YYYY-MM. Selanjutnya rekap data akan disort secara descending dan hanya akan ditampilkan 5 terbesar saja.

SELECT CONCAT(YEAR(serviceticket.DateReceived), "-", MONTH(serviceticket.DateReceived)) AS yearmonth, count(*) as Jumlah
FROM serviceticket
GROUP BY yearmonth
ORDER BY Jumlah DESC
LIMIT 0, 5

Function CONCAT() digunakan untuk menggabungkan string YEAR, “-“, dan MONTH sehingga menjadi format YYYY-MM. Adapun perintah LIMIT 0, 5 digunakan untuk membatasi tampilan 5 data saja dimulai dari data pertama (index ke-0).

Hasil query akan diperoleh jawaban sebagai berikut.

Siapa nama mekanik service yang paling banyak mendapat rate ‘Poor’ dari customer?

Pertanyaan di atas dapat dijawab dengan memberikan query yang melibatkan tabel: mechanic, dan servicemechanic. Tabel mechanic untuk mendapatkan nama mekaniknya, dan tabel servicemechanic untuk mendapatkan data transaksi service yang pernah dilakukan. Data pada tabel servicemechanic ini nantinya kita filter hanya yang mendapat rate ‘Poor’ saja. Selanjutnya kita buat rekap jumlah rate ‘Poor’ untuk setiap mechanic berdasarkan MechanicID dan terakhir kita sort descending.

Adapun kedua tabel berelasi pada kolom MechanicID.

Query yang kita berikan adalah adalah sebagai berikut.

SELECT mechanic.LastName, count(*) as JumPoor
FROM mechanic, servicemechanic
WHERE mechanic.MechanicID = servicemechanic.MechanicID
      AND servicemechanic.Rate = 'Poor'
GROUP BY mechanic.MechanicID
ORDER BY JumPoor DESC

Output dari query di atas adalah

Berdasarkan hasil tersebut, tampak bahwa mekanik bernama Amir lah yang paling banyak mendapat rate ‘Poor’.

Siapa nama customer yang paling banyak memberi rate ‘Poor’ pada mekaniknya?

Pertanyaan ini merupakan pengembangan dari pertanyaan sebelumnya. Tabel yang dipilih untuk menjawab pertanyaan ini adalah: customer, servicemechanic, dan serviceticket. Tabel customer untuk mendapatkan data nama customer. Tabel servicemechanic untuk mendapatkan data mekanik yang mendapat rate ‘Poor’. Serta tabel serviceticket untuk merelasikan antara data service dengan data costumer. Tabel customer dan serviceticket berelasi melalui kolom CustomerID. Adapun tabel serviceticket dan servicemechanic berelasi melalui kolom ServiceTicketID.

Data hasil relasi ketiga tabel di atas nantinya akan dihitung rekapnya berdasarkan customernya, dan selanjutnya disorting secara descending untuk mendapatkan nama customer yang paling banyak memberikan rate ‘Poor’ kepada mekaniknya.

Query SQL yang diberikan adalah sebagai berikut.

SELECT customer.LastName, count(*) as JumRatePoor
FROM customer, servicemechanic, serviceticket
WHERE customer.CustomerID = serviceticket.CustomerID
      AND servicemechanic.ServiceTicketID = serviceticket.ServiceTicketID
      AND servicemechanic.Rate = 'Poor'
GROUP BY customer.CustomerID
ORDER BY JumRatePoor DESC

Hasil dari query tersebut adalah:

Berdasarkan hasil di atas, tampak bahwa customer yang paling banyak memberikan rate Poor adalah Eko.

Tampilkan merek mobil yang jumlah servicenya lebih dari 20 kali!

Tabel yang akan dipilih untuk menjawab pertanyaan ini adalah: car, dan serviceticket. Tabel car untuk mendapatkan merek mobilnya (kolom MAKE), dan tabel serviceticket untuk menghitung jumlah service yang pernah terjadi. Tabel car dan serviceticket berelasi melalui kolom CarID.

Adapun untuk membatasi tampilan hasil query yaitu hanya yang jumlah servicenya lebih dari 20 kali, kita akan gunakan HAVING. Filter dengan HAVING ini dilakukan karena jumlah service ini dihitung melalui rekap dari pengunaan aggregate function COUNT().

Berikut ini adalah query SQL nya:

SELECT car.Make, count(*) as JumService
FROM car, serviceticket
WHERE car.CarID = serviceticket.CarID
GROUP BY car.Make
HAVING JumService > 20
ORDER BY JumService DESC

Sehingga diperoleh informasi merek mobil yang pernah diservice lebih dari 20 kali adalah sbb:

Berapa rata-rata lama penyelesaian waktu service (dalam hari) setiap bulannya selama tahun 2018?

Jawaban dari pertanyaan ini cukup diperoleh dari tabel serviceticket. Hal yang perlu dicari terlebih dahulu adalah lamanya durasi antara tanggal mulai service dilakukan sampai dengan tanggal diserahkannya kembali mobil ke customer. Untuk menghitung durasi dari dua tanggal ini dapat menggunakan function DATEDIFF(). Selanjutnya durasi ini nantinya akan dicari reratanya dengan AVG() dikelompokkan berdasarkan angka bulan dari tahun 2018.

Adapun bentuk query SQL untuk implementasi ide di atas adalah sbb:

SELECT MONTH(serviceticket.DateReceived) as Bulan, AVG(DATEDIFF(serviceticket.DateReturnedToCustomer, serviceticket.DateReceived)) as AvgService
FROM serviceticket
WHERE YEAR(serviceticket.DateReceived) = '2018'
GROUP BY Bulan
ORDER BY Bulan ASC

Akhirnya diperoleh hasil rerata durasi lama service tiap bulan di tahun 2018 sbb:

Buatlah rekap profit per bulan perusahaan dari hasil penjualan sparepart selama tahun 2018!

Untuk mendapatkan besar total profit penjualan sparepart tiap bulannya adalah dengan menghitung profit per item sparepart yaitu mencari selisih harga jual (RetailPrice) dengan harga beli (PurchasePrice) dari tabel parts. Selanjutnya profit per item sparepart ini dikalikan dengan besar quantity nya dari kolom NumberUsed dari tabel partsused untuk mendapatkan total profit dari satu buah item. Total profit per item ini nantinya dijumlah total di setiap bulannya dengan SUM() dikelompokkan berdasarkan bulannya. Waktu pelaksanaan service diambil dari tabel serviceticket. Sehingga untuk menjawab pertanyaan ini dibutuhkan tiga tabel, yaitu: serviceticket, parts, dan partsused.

Antara tabel serviceticket dan partsused berelasi melalui kolom ServiceTicketID, dan antara tabel parts dan partsused berelasi melalui kolom PartsID.

Berikut ini adalah query SQL nya:

SELECT MONTH(serviceticket.DateReceived) as Bulan, SUM(partsused.NumberUsed * (parts.RetailPrice-parts.PurchasePrice)) as TotalProfit
FROM serviceticket, parts, partsused
WHERE serviceticket.ServiceTicketID = partsused.ServiceTicketID
      AND parts.PartsID = partsused.PartsID
      AND YEAR(serviceticket.DateReceived) = '2018'
GROUP BY Bulan
ORDER BY Bulan ASC

dan.. akhirnya didapatlah total profit penjualan sparepart per bulan selama tahun 2018.