Tabel String Processing Function. Fungsi Tujuan Contoh Hasil CONCAT (S1,S2,..) Berfungsi untuk

4.1

Tujuan

4.2

Alat & Bahan

4.3

Dasar Teori

Modul 4 :

SQL Built-In Function

Setelah mengikuti praktikum ini mahasiswa diharapkan dapat: 1. Mempraktekkan menampilkan data dari satu tabel dengan menggunakan numeric Built-in Function 2. Mempraktekkan menampilkan data dari satu tabel dengan menggunakan string processing Built-In Function 3. Mempraktekkan menampilkan data dari satu tabel dengan menggunakan date and time Builtin Function 4. Mempraktekkan menampilkan data dari satu tabel dengan menggunakan grouping function Alat & Bahan Yang digunakan adalah hardware perangkat PC beserta Kelengkapannya berjumlah 40 PC serta Software XAMPP yang telah terinstall pada masing-masing PC MySQL built-in function merupakan fungsi-fungsi yang telah didefinisikan oleh MySQL untuk dapat langsung dipergunakan dan memiliki kegunaan nya masing-masing. Terdapat beberapa jenis Built-in function, diantaranya adalah: 1. 2. 3. 4.

String Processing function Numeric function Date and time function Grouping function

4.3.1 String Processing Function String processing function adalah fungsi-fungsi yang sudah tersedia di MySQL digunakan untuk mengolah data-data dengan tipe data text atau string. Berikut adalah fungsi yang termasuk ke dalam string processing function: Fungsi CONCAT (S1,S2,…..) LENGTH(string) LCASE(string) atau LOWER(string)

UCASE (string) atau UPPER (string)

Tabel String Processing Function

Tujuan Berfungsi untuk menggabungkan antara string satu dengan string yang lain Berfungsi untuk menampilkan panjang karakter dari suatu string Berfungsi untuk mengubah string menjadi huruf kecil Berfungsi untuk mengubah string menjadi kuruf capital

Modul Praktikum Bahasa Query Terstruktur

Contoh SELECT CONCAT (‘My’,’S’,’QL’)

Hasil ‘MySQL’

SELECT LENGTH (‘MySQL’)

5

SELECT LOWER (‘MYSQL’)

‘mysql’

SELECT UPPER(‘mysql’)

‘MYSQL’

64

MID (string,position,length) atau SUBSTRING (string,position,length)

LEFT (string, length) RIGHT (string,length) LTRIM (string) RTRIM (string) LPAD(string, length, char)

RPAD (string, length,char)

RELPACE (String, old string, new string)

Berfungsi untuk menampilkan karakter dari suatu string dimulai dari suatu posisi dengan mendefinisikan jumlah /panjang karakter yang akan Ditampilkan Berfungsi untuk menampikan sejumlah karakter tertentu dimulai dari karakter paling kiri Berfungsi untuk menampilkan sejumlah karakter tertentu dimulai dari karakter paling kanan Berfungsi untuk menghilangkan spasi di dari kiri Berfungsi untuk menghilangkan spasi dari kanan Berfungsi untuk menambah karakter sehingga mencapai sejumlah tertentu sesuai dengan karakter yang diinginkan dari kiri. Berfungsi menambah karakter sehingga mencapai sejumlah tertentu sesuai dengan karakter yang diinginkan dari kanan. Berfungsi untuk menggantikan string tertentu dengan string yang lain

SELECT SUBSTRING(‘MySQL’,3,3)

‘SQL’

SELECT LEFT (‘MySQL’, 3)

‘MyS’

SELECT RIGHT (‘MySQL’,3)

‘SQL’

SELECT LTRIM (‘

‘MySQL’

MySQL’)

SELECT RTRIM (‘MySQL ‘)

‘MySQL’

SELECT LPAD (‘MySQL’, 7,’?‘)

??MySQL

SELECT RPAD(‘MySQL’7,’?’)

MySQL??

SELECT REPLACE (‘MySQL’,’Q’,’D’)

‘MySDL’

4.3.2 Numeric Function Numeric function adalah fungsi-fungsi yang sudah tersedia di MySQL digunakan untuk mengolah data-data dengan tipe data numerik. Berikut adalah fungsi yang termasuk ke dalam numeric function: Fungsi ABS (X)

Tabel Numeric Function

Tujuan Berfungsi untuk menghasilkan nilai mutlak dari suatu nilai X


Contoh SELECT ABS(-32)

Hasil 32 65

CEILING(X) Berfungsi Untuk menghasilkan nilai pembulatan ke atas FLOOR(X) Berfungsi untuk menghasilkan nilai pembulatan ke bawah MOD(n,m) Berfungsi untuk menghitung sisa hasil bagi DIV(n,m) Berfungsi untuk menghitung hasil bagi POW(n,m) Berfungsi untuk menghitung hasil pangkat SQRT(n) Berfungsi untukmenghitung hasil akar pangkat dua

SELECT CEILING(1.23) SELECT FLOOR(1.23) SELECT MOD(27,5) SELECT DIV(27,5) SELECT POW(2,2) SELECT SQRT(16)

2 1 2 5 4 4

4.3.3 Date Function Date function adalah fungsi-fungsi yang sudah tersedia di MySQL digunakan untuk mengolah datadata dengan tipe data tanggal. Berikut adalah fungsi yang termasuk ke dalam date function: Fungsi Curdate() atau curtime() atau now() ADDATE(date, INTERVAL days)

SUBDATE(date, INTERVAL days)

DAYNAME(date) DAYOFMONTH(date) DAYOFWEEK (date)

DAYOFYEAR(date) Menampilkan

Tabel Date Function

Tujuan Menampilkan tanggal saat ini

Menampilkan tanggal ditambah dengan interval waktu tambahan Menampilkan tanggal dikurangi dengan interval waktu tambahan Menampilkan nama hari pada tanggal tertentu Menampilkan tanggal (range dari 1 sampai dengan 31) Menampilkan indeks hari dalam satu minggu (range dari 1 sampai dengan 7) Menampilkan indeks hari dalam satu tahun (range dari satu sampai dengan 366)


Contoh SELECT NOW() SELECT ADDATE(‘2008-0102’, interval 31 day)

Hasil 2013-0520 19:48:35 ‘2007-1201’

SELECT ADDATE(‘2008-0102’, interval 31 day)

‘2007-1201’

SELECT (DAYNAME(CURDATE())

Monday

SELECT DAYOFMONTH(‘2007-0203’)

03

SELECT DAYOFWEEK(‘200702-03’)

7

SELECT DAYOFYEAR(‘200702-03’)

34

66

Tujuan Menampilkan bulan dari suatu tanggal MONTHNAME(date) Menampilkan nama bulan dari suatu tanggal YEAR(date) Menampilkan tahun dari suatu tanggal STR_TO_DATE(string, date format) Mengubah string menjadi format tanggal TIMESTAMPDIFF(year/month/day, Menghitung date1, date2) selisih antara dua tanggal dengan output berupa tahun MONTH(date)

Fungsi

Contoh SELECT MONTH(‘2008-0203’)

2

SELECT YEAR(‘2008-02-03’)

2008

SELECT MONTHNAME(‘2008-02-03’)

Hasil

February

SELECT ‘2016-05STR_TO_DATE(’01,5,2016’, 01 ’%d,%m,%Y) SELECT 1 TIMESTAMPDIFF(year,’201602-01’,’2015-01-01’)

4.3.4 Grouping function Grouping function adalah fungsi-fungsi yang sudah tersedia di MySQL digunakan untuk mengolah data-data dan khusus untuk dapat dipergunakan pada saat menggunakan klausa GROUP BY. Berikut adalah fungsi yang termasuk ke dalam grouping function: Fungsi Avg(nama_kolom) Count(nama_kolom) Min(nama_kolom) Max(nama_kolom) Sum(nama_kolom)

Tabel Grouping Function

Deskripsi Menghasilkan nilai rata-rata kolom Menghasilkan jumlah baris data dalam suatu kolom Menghasilkan nilai terkecil dari suatu kolom Menghasilkan nilai terbesar dari suatu kolom Menghasilkan jumlah dari suatu kolom

Diketahui terdapat tabel Pelanggan sebagai berikut: Id_Pelanggan

P0001 P0002 P0003 P0004

Nama

Novita Putri Wulandari Pujiharti Andre Terdata

Data Pelanggan

Alamat

Jl. Kebon Kalapa No.12 Bandung Jl. Pegadaian No.25 Bandung Jl. Kewadanaan No. 14 Jakarta Jl. Pasir Kaliki No. 104A Bandung

Pekerjaan

No_ktp

No_telp

Wiraswasta

3451237890124567

0227451234

Wiraswasta

1231237874354567

0214456789

Dosen

Wiraswasta

3451237874354582 3451237874300000

0223456789 0222445356

Contoh:

Berdasarkan tabel diatas, tampilkan jumlah pelanggan yang memiliki pekerjaan wiraswasta. Modul Praktikum Bahasa Query Terstruktur

67

Perintah untuk menampilkan data sesuai dengan permasalahan diatas dapat dilakukan dengan perintah sebagai berikut: SELECT COUNT(pekerjaan) FROM pelanggan WHERE pekerjaan=’Wiraswasta’;

Sehingga akan menampilkan data sebagai berikut:

Contoh Tampilan Hasil Selecting Data Menggunakan Grouping Function COUNT(pekerjaan) 3

Contoh Lain:

Berdasarkan tabel pelanggan, tampilkan jumlah pelanggan dari setiap pekerjaan.

Perintah untuk menampilkan data sesuai dengan permasalahan diatas dapat dilakukan dengan perintah sebagai berikut: SELECT pekerjaan, COUNT(pekerjaan) FROM pelanggan GROUP BY pekerjaan;

Sehingga akan menampilkan data sebagai berikut:

Contoh Tampilan Hasil Selecting Data Menggunakan Grouping Function dan Klausa GROUP BY Pekerjaan Wiraswasta Dosen

COUNT(pekerjaan) 3 1

SELECT [*][nama_kolom1, nama_kolom2,….] FROM nama_tabel [WHERE kondisi_data_yang_ditampilkan] [GROUP BY group] [HAVING kondisi_group] [ORDER BY nama_kolom_yg_diurutkan];

4.4

Latihan

Lengkapilah Bahasa query di kolom sebelah kanan yang sudah disediakan sesuai dengan petunjuk soal di kolom sebelah kiri. 4.4.1 Latihan 1 Berikut adalah ER Diagram dan Diagram relasi antar tabel untuk studi kasus Ananda Brownis Penjualan:


68

Berikut adalah data (isi tabel) yang ada dalam studi kasus Ananda Brownis Penjualan: Tabel Jenis_kue


69

Kue

Outlet

Penjualan

Detail_jual


70

Akun

Jurnal_penjualan Modul Praktikum Bahasa Query Terstruktur

71

INSTRUKSI Tampilkan data akun sehingga menghasilkan luaran sebagai berikut:

QUERY SELECT CONCAT( kode_akun,' ', nama_akun) as akun from akun

2.

Tampilkan nama outlet beserta dengan jumlah karakter dari nama outlet tersebut sehingga menghasilkan luaran:

SELECT nama_outlet, LENGTH(nama_outlet)as 'Panjang Karakter' FROM outlet

3.

Tampilkan nama kue sehingga menghasilkan luaran:

SELECT UPPER (nama_kue) as 'Nama Kue' FROM kue

1.


72

4.

Tampilkan nama kue dan lima karakter paling kiri dari nama kue sehingga menghasilkan luaran:

SELECT nama_kue,LEFT(nama_kue,5)AS 'Lima Huruf Pertama' FROM kue

5.

Tampilkan nama kue dan 4 karakter paling kanan dari nama kue sehingga menghasilkan luaran:

SELECT nama_kue,right(nama_kue,4)AS 'Lima Huruf Terakhir' FROM kue

6.

Tampilkan nama kue dan nama kue setelah penggantian nama yang mengandung kata Brownies menjadi Brodol seperti luaran sebagai berikut:

SELECT nama_kue, replace(nama_kue,'Brownies', 'Brodol') as 'Nama Baru' from kue


73

7.

Tampilkan tanggal penjualan, dan periode penjualan sehingga luaran sebagai berikut

SELECT tgl_jual, concat( monthname(tgl_jual) ,' ',year(tgl_jual)) as periode FROM penjualan

8.

Tampilkan tanggal penjualan dan tanggal setelah tujuh hari dari penjualan sehingga memberikan luaran:

SELECT tgl_jual, adddate(tgl_jual, interval 7 DAY) as 'Tujuh Hari Setelah Penjualan' from penjualan

4.4.2 Latihan 2 Berikut terdapat ER Diagram Toko Sukses Selalu.


74

Diagram Relasi Antar Tabel Toko Elektronik Sukses Selalu PK

PK

id_pelanggan no_ktp nama alamat no_telp pekerjaan

pelanggan char(12) char(16) varchar(25) varchar(50) text varchar(20)

id_pegawai no_ktp nama alamat no_telp posisi

char(10) char(16) varchar(25) varchar(50) text varchar(20)

pegawai

PK FK FK

id_nota tgl_nota total id_pelanggan id_pegawai

nota_penjualan char(5) date integer(10) char(12) char(10)

PK|FK id_bayar tgl_bayar jumlah_bayar sisa_piutang FK id_nota FK id_pegawai

pembayaran char(5) da te

integer(10) integer(10) char(5) char(10)

PK|FK id_nota PK|FK id_barang jumlah subtotal

detail_penjualan char(5) char(50) integer(5) integer(11)

PK|FK id_nota PK|FK id_barang PK|FK id_retur jumlah subtotal

detail_retur char(5) char(50) char(5) integer(5) integer(11)

PK|FK id_retur tgl_retur total_retur PK

id_trans tgl_trans jml_trans

transaksi char(5) da te

integer(10)

Data dalam Database Toko Sukses Selalu Pelanggan

id_pelanggan 1111111111 8924234423 8935353535 8911223344

no_ktp

3372026404830000 3372026404832420 3372026404112233

nama Pelanggan Tidak Terdaftar Jeko Boy Domino

PK|FK no_akun PK|FK id_trans posisi_dr_cr nominal

PK

FK

id_barang nama harga stok id_kategori

PK

id_kategori nama_kategori keterangan

PK

no_akun nm_akun header_akun

barang char(50) varchar(25) integer(8) integer(11) char(3) kategori_barang char(3) varchar(25) text

retur char(5)

date

integer(10)

Jurnal char(5) char(5) varchar(6) integer

FK

alamat

no_telp

Jl.Bojong Soang Bandung Jl.Sukabirus Bandung Jl.Moch.Toha Bandung

022734412234 098987654321 02254321

Akun char(5) varchar(100) char(5)

pekerjaan

swasta mahasiswa wirausaha

Pegawai


75

id_pegawai 201301001 201301002 201302001 201302009 Kategori_barang id_kategori ELK BZR SFL GPK GMK PSG

Barang

no_ktp 3372026404830000 3372026404777777 3372026404987654 3372026404135792

nama_kategori Elektronik Bazar Softline Grocery Perawatan Kecantikan Grocery Makanan Minuman Pasar Segar

id_barang UA-22F5000 KLV-24EX430 24LN4100 32LN5100 DH-6330P ES-FL8605-WW NA-F852 MUG-ZDK MUG-SHO MUG-ANI KNWTPUMP KC-105 STELLA70GR HITLEMON ATTSOFT ATTCMAX INDOMIE UHT125ML UHT1000ML

Nota_penjualan id_nota 86856 86857 86858 86859 86860 86861 86862

nama Jeko Budi Wawan Wati

Alamat Jl.Bojong Soang Bandung Jl.Sukapura Bandung Jl.Sukabirus Bandung Jl. Telekomunikasi Bandung

total 45675 52800 38500 3492000 8817300 2863600 297000

posisi kasir kasir kasir supervisor

Keterangan Elektronik Perlengkapan Rumah Tangga Pakaian Siap Pakai Perawatan dan kecantikan Makanan dan Minuman Sayur, buah dan daging

Nama LED TV Samsung 22 inch LED TV SONY 24 Inch LED TV LG 24 Inch LED TV LG 32 Inch HOME THEATHER LG WASHING MACHINE SHARP 6KG WASHING MACHINE PANASONIC 8.5KG MUG ZODIAK MUG SHIO MUG ANIMAL KENMASTER WATER PUMP KOMPOR MINI PORTABLE STELLA ALL IN ONE 70 GR HIT AEROSOL LEMON 600ML ATTACK SOFTENER 800GR ATTACK CLEAN MAXIMIZER 800GR INDOMIE ALL VARIAN ULTRAMILK 125ML ULTRAMILK 1000ML

tgl_nota 2013-05-01 2013-05-01 2013-05-01 2013-05-01 2013-06-01 2013-07-01 2013-07-01

no_telp 022734412234 08988776655 089899999 14000

id_pelanggan 1111111111 8924234423 8924234423 8935353535 8911223344 8924234423 8924234423


harga 1900000 1999600 2036900 3492000 2461700 2863600 2590000 19900 19900 19900 27500 124900 7200 24150 14325 14325 1500 1800 11350

stok 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100

id_kategori ELK ELK ELK ELK ELK ELK ELK BZR BZR BZR BZR BZR BZR BZR BZR BZR GMK GMK GMK

id_pegawai 201301001 201301001 201301002 201301002 201301002 201301002 201302001

76

Detail_penjualan

id_nota 86856 86856 86856 86857 86857 86858 86858 86858 86859 86860 86860 86860 86861 86862 86862

Transaksi

id_trans BYR1 BYR2 BYR3 BYR4 BYR5 BYR6 BYR7 BYR8 BYR9 BYR10 BYR11 RTR1 RTR2

Pembayaran id_bayar BYR1 BYR2 BYR3 BYR4 BYR5 BYR6 BYR7 BYR8 BYR9 BYR10 BYR11

id_barang STELLA70GR HITLEMON ATTSOFT HITLEMON ATTSOFT MUG-ZDK INDOMIE UHT125ML 32LN5100 32LN5100 DH-6330P ES-FL8605-WW ES-FL8605-WW INDOMIE UHT125ML

tgl_trans 2013-05-01 2013-05-01 2013-05-01 2013-05-01 2013-06-01 2013-06-01 2013-07-01 2013-08-01 2013-09-01 2013-07-01 2013-07-01 2013-05-01 2013-07-02

jumlah 1 1 1 1 2 1 10 2 1 1 1 1 1 90 90

subtotal 7200 24150 14325 24150 28650 19900 15000 3600 3492000 3492000 2461700 2863600 2863600 135000 162000

jml_trans 45675 52800 38500 3000000 492000 4408650 2204325 2000000 204325 363600 47000 10500 43650

sisa_piutang 0 0 0 492000 0 47000 2204325 204325 0 2500000 250000

id_nota 86856 86857 86858 86859 86859 86860 86860 86860 86860 86861 86862

id_pegawai 201301001 201301001 201301002 201301002 201301001 201301002 201301001 201301001 201301001 201301002 201302001


77

retur

Id_retur RTR1 RTR2

Detail_retur id_nota 86858 86862 86857

Akun

no_akun 1 11 111 112 113 114 115 116 2 21 211 3 311 3112 4 41 411 412 413 414 5 511 512 513 514

jurnal

no_akun 111 411 111 411 111

id_barang INDOMIE INDOMIE HITLEMON

id_retur RTR1 RTR2 RTR2

jumlah 7 13 1

nm_akun Aktiva Aktiva Lancar Kas Piutang Dagang Persediaan Barang Dagangan Sewa Dibayar Dimuka (10/12) Asuransi Dibayar Dimuka (09/12) Perlengkapan Hutang Hutang Lancar Utang Dagang Modal Modal Tn X Prive Tn. X Pendapatan Pendapatan Usaha Penjualan Harga Pokok Penjualan Retur Penjualan Potongan Penjualan Beban Beban Listrik Beban Air Beban Telepon Beban Gaji

id_trans BYR1 BYR1 BYR2 BYR2 BYR3

posisi_dr_cr debet kredit debet kredit debet

subtotal 10500 19500 24150

header_akun

nominal 45675 45675 52800 52800 38500


1 11 11 11 11 11 11 2 21 3 311 4 41 41 41 41 5 5 5 5

78

411 111 112 411 111 112 111 112 411 111 112 111 112 111 112 111 112 411 111 112 411 413 111 413 111

1.

2.

BYR3 BYR4 BYR4 BYR4 BYR5 BYR5 BYR6 BYR6 BYR6 BYR7 BYR7 BYR8 BYR8 BYR9 BYR9 BYR10 BYR10 BYR10 BYR11 BYR11 BYR11 RTR1 RTR1 RTR2 RTR2

kredit debet debet kredit debet kredit debet debet kredit debet kredit debet kredit debet kredit debet debet kredit debet debet kredit debet kredit debet kredit

38500 3000000 492000 3492000 492000 492000 4408650 4408650 8817300 2204325 2204325 2000000 2000000 204325 204325 363600 2500000 2863600 47000 250000 297000 10500 10500 43650 43650

INSTRUKSI Tampilkan id barang dan nama barang menjadi satu kolom dengan nama kolom ‘Barang’. Seperti hasil di bawah ini

QUERY

Tampilkan id barang dan nama barang menjadi satu kolom dengan nama kolom ‘Barang’ dan hitung jumlah karakter untuk kolom tersebut dengan nama kolom ‘Total_karakter’. Data yang ditampilkan adalah data dengan total karakter lebih dari 20.


79

3.

Tampilkan id pegawai dan nama pegawai menjadi satu kolom dengan nama ‘Pegawai’ dan ditampilkan dengan seluruh huruf berupa kapital.

4.

Tampilkan id barang, nama barang menjadi satu kolom dengan nama kolom ‘Barang’ dan kolom harga barang dengan tampilan seluruh karakter kapital, khusus yang ditampilkan adalah barang yang termasuk ke dalam kode barang ‘ELK’ yang memiliki harga antara 2 juta sampai dengan 4 juta

5.

Tampilkan tanggal transaksi penjualan dan total penjualan yang terjadi pada dari tanggal 1 Mei 2013 sampai dengan 1 Juni 2013 dengan total penjualan lebih dari 50ribu rupiah. Keluaran sebagai berikut (output huruf kapital dan nama kolom harus sesuai dengan luaran sebagai berikut).

6.

Tampilkan total penjualan tertinggi, total penjualan terendah, dan rata-rata total penjualan yang ada dalam tabel nota penjualan dengan luaran harus sesuai dengan tabel sebagai berikut (nama kolom harus sama) .

7.

Tampilkan id_barang dan nama barang menjadi satu kolom dengan nama kolom ‘Barang’. Data ‘Barang’ yang ditampilkan adalah barang dengan harga termurah yang memiliki nama mengandung kata ‘LED TV’ sehingga menghasilkan luaran sebagai berikut (penulisan nama kolom harus sama dan menggunakan huruf kapital).


80

8.

Tampilkan jumlah akun yang memiliki header akun ‘11’. Luaran adalah sebagai berikut.

9.

Tampilkan nomor nota, tanggal nota dan tanggal nota ditambah dengan 3 hari sebagai asumsi bahwa tanggal tersebut adalah batas waktu maksimal untuk retur barang. Namun data yang ditampilkan adalah yang tanggal maksimal retur barang nya kurang dari 30 Juni 2013 dan ditangani oleh id pegawai ‘201301002’.

10. Tampilkan tanggal nota, dan hitung selisih antara tanggal 14 september 2016 dengan tanggal nota pada tabel nota_penjualan sehingga menghasilkan luarah selisih dalam tahun seperti tabel berikut.

11. Tampilkan tanggal nota, dan hitung selisih antara tanggal 14 september 2016 dengan tanggal nota pada tabel nota_penjualan sehingga menghasilkan luaran selisih dalam tahun dan bulan seperti tabel berikut.

12. Tampilkan tanggal nota, dan hitung selisih antara tanggal 14 september 2016 dengan tanggal nota pada tabel nota_penjualan sehingga menghasilkan luaran selisih dalam tahun dan bulan khusus yang selisihnya lebih dari 3 tahun dua bulan seperti tabel berikut.


81

Tabel String Processing Function. Fungsi Tujuan Contoh Hasil CONCAT (S1,S2,..) Berfungsi untuk

Recommend Documents