Kontrak Kuliah
Menggunakan FungsiFungsi SQL Edi Sugiarto, S.Kom, M.Kom
1
Fungsi-Fungsi SQL • Terdapat dua kategori fungsi dalam SQL – Single-Row : fungsi ini menerima satu baris sebagai operand. – Multiple-Row : fungsi ini menerima lebih dari satu operand. • Contoh fungsi yang termasuk dalam multiplerow antaralain : Group By, Sum, Max, Min, Count, dan AVG.
2
• Fungsi-fungsi single-row terbagi menjadi – Fungsi Character / String – Fungsi Date – Fungsi Numeric – Fungsi General
3
• Fungsi Karakter/String – Merupakan fungsi yang berorientasi pada string. Fungsi
Hasil
Lower(‘MySQL Database’)
mysql database
Upper(‘MySQL Database’)
MYSQL DATABASE
Concat(‘MySQL’,’ Database’)
MySQL Database
Substr(‘MySQL Database’,3,3)
SQL
Left(‘MySQL Database’,5)
MySQL
Right(‘MySQL Database’,5)
Abase
Length(‘MySQL Database’)
14
Instr(‘MySQL Database’,’Q’)
4
4
Fungsi
Hasil
Ascii(‘a’)
97
Char(65)
A
Format(300000000,2)
300,000,000.00
Lpad(‘udinus’,8,’*’)
**udinus
Rpad(‘udinus’,8,’*’)
udinus**
Repeat(‘x’,10)
xxxxxxxxxx
Ltrim(‘
udinus
udinus’)
Rtrim(‘udinus
‘)
udinus
Reverse(‘udinus’)
sunidu
Replace(‘FIK’,’K’,’Komputer’)
FIKomputer
Insert(‘sunshine’,1,3,’rain or ‘)
rain or shine
Trim(‘ udinus ‘)
udinus
Find_in_set(‘AB’,’A,B,AB,O’)
3 5
• Fungsi Numeric – Merupakan fungsi yang berorientasi pada nilai numerik. Fungsi
Hasil
Truncate(45.928,2)
45.92
Round(45.928,2)
45.93
Mod(10,3)
1
Sqrt(625)
25
Sign(45.928)
1
Floor(45.928)
45
Ceiling(45.928)
46
Abs(-45.928)
45.928
6
Fungsi
Hasil
Cos(0)
1
Sin(0)
0
Tan(0)
0
Log(1)
0
Pi()
3.141593
• Contoh:
7
• Fungsi Tanggal dan Waktu Fungsi
Keterangan
Current_date atau Curdate()
Menampilkan tanggal sekarang
Current_time atau Curtime()
Menampilkan waktu sekarang
Current_timestamp()
Menampilkan tanggal dan waktu sekarang
Now()
Menampilkan tanggal dan waktu sekarang
Month()
Menghasilkan angka nomor urut bulan
Monthname()
Menampilkan nama bulan
Dayofmonth()
Menampilakn nomor urut tanggal
To_days()
Menghasilkan jumlah hari
Year()
Menghasilkan angka tahun 8
Fungsi
Keterangan
Dayname()
Menghasilkan nama hari
Hour()
Menghasilkan angka jam
Minute()
Menghasilkan angka menit
Second()
Menghasilkan angka detik
Weekday()
Menghasilkan angka urut dalam satu minggu
Date_add()
Menghasilkan penambahan tanggal
Datediff()
Menghasilkan pengurangan tanggal
Date_format()
Menghasilkan format tanggal
9
• Contoh1 :
• Contoh2 :
10
• Contoh3 :
• Contoh4:
11
• Fungsi General – Merupakan fungsi yang tidak termasuk dalam fungsi string, numeric dan tanggal – Fungsi tersebut antaralain: 1.Fungsi Encode • Digunakan untuk mengkonversi string ke binary string. • Bentuk umum : Encode (str,password) 12
– Contoh :
– Dimana str adalah string yang akan dikonversi. – Password adalah kata kunci untuk perintah konversi tersebut. 13
2. Fungsi Decode • Digunakan untuk mengembalikan nilai string yang telah dilakukan proses encode • Bentuk umum : Decode (str,password)
– Contoh :
14
Lebih lanjut dengan SELECT • Menggunakan perintah LIMIT – Digunakan untuk membatasi jumlah record yang akan ditampilkan. – Contoh1 : masih menggunakan tabel employee pada pertemuan sebelumnya. Tampilkan 3 record data pada tabel employee.
15
– Contoh2: tampilkan 5 record pada tabel employee dihitung dari posisi record ke 2.
16
• Menggunakan option BINARY agar data case-sensitive – Dengan menambahkan option BINARY maka pencarian data akan bersifat case-sensitiv. – Contoh :
17
• Menuliskan ekspresi pada kolom – Contoh1: akan ditampilkan nama dan gaji pegawai setelah gaji dinaikkan 5% dari gaji semula.
18
• Memberi nama lain terhadap kolom – Contoh1: akan menampilkan nama dan gaji pegawai setelah dinaikkan 5% dari gaji semula dengan perubahan nama kolom.
19
• Ekspresi berkondisi – Digunakan untuk mengubah keluaran yang berbeda dengan data asalnya. – Syntax : Case
When then End 20
– Contoh :ditampilakn empNo, empName dan job dengan ekspresi berkondisi sbb:
21
• Ekspresi Berkondisi – Selain menggunakan case, perintah lain untuk ekspresi berkondisi dapat menggunakan perintah IF. – Syntax: IF (<ekspresi>,,)
22
– Contoh :ditampilkan empNo, empName dan status dengan kondisi jika job adalah president tampilkan status ‘Leadership’, selain itu tampilkan status ‘Staff’.
23
– Latihan : • Untuk latihan buatlah query pada tabel employee untuk menentukan grade gaji berdasarkan tabel berikut: Gaji
Grade
>=10000000
6
>=7500000 dan <10000000
5
>=5000000 dan < 7500000
4
>=3000000 dan < 5000000
3
>=1000000 dan < 3000000
2
<1000000
1 24
– Sehingga hasilnya sbb:
25
Terima kasih
26
Latihan Tulislah perintah SQL untuk menyelesaikan latihan berikut : 1.Buatlah tabel dosen dan struktur tabelnya sbb: Nama Field
Type
Keterangan
Nama_dos
Primary key, tidak boleh kosong Int(4) , unsigned, zerofill, auto_increment Varchar(30) Tidak boleh kosong
Alamat_dos
Varchar(50)
Tgl_masuk
date
Kode_dos
27
2. Tambahkan field kota char(20) yang ditempatkan setelah field alamat_dos 3. Masukkan record-record berikut ke tabel dosen sbb: Kode_ dos
Nama_dos
Alamat_dos
Kota_dos
Tgl_masuk
1
Agung Susanto
Jl. A. Yani No 20
Semarang
10-02-1990
2
Mila Estianti
Jl. Ranggalawe No 4
Semarang
13-10-1998
3
Dini Nurisanti
Perum Korpri Blok E4
Solo
20-09-1990
4
Sugeng Purnomo
Jl. Kemuning Raya
Demak
25-11-1992
5
Rahmadi
Jl. Dahlia No 5
Kudus
18-06-2010
28
4. Ubah data alamat, kota dan tanggal masuk dosen yang memiliki nama depan “sugeng” menjadi – – –
Alamat Kota Tgl_masuk
= “Perum. Majapahit no 5” = “Semarang” = 21-09-1994
5. Tampilkan semua data dosen dimulai record ke 3 hingga 5, untuk nama tampilkan dalam huruf kapital 6. Tampilkan nama_dos, kota_dos dan lama_kerja seluruh dosen.
29
7. Dari tabel dosen tampilkan data dosen dalam format sbb Kode_dos
Nama_Dos
Kota_dos
Tgl_masuk
• Ketentuan – Tampilkan tgl_masuk dalam format d M Y – Untuk kota_dos tampilkan » “SMG” jika kota “Semarang” » “SLO” jika kota “Solo” » “DMK” jika kota “Demak” » “KDS” jika kota “Kudus” 30
8. Buatlah tabel krs dengan struktur tabel sbb: Nama Field
Type
Keterangan
Kode_kul Nim
Char(7) Char (10)
Primary Key, Tidak boleh kosong Primary Key, Tidak Boleh Kosong
Nilai
Int
9. Isikan record-record berikut pada tabel krs ‘A21-101’,’A21.2001.00234’,80 ‘A21-101’,’A21.2001.00243’,60 ‘A21-202’,’A21.2002.00451’,65 ‘A21-203’,’A21.2002.00451’,45 ‘A21-203’,’A21.2002.00651’,85 ‘A21-203’,’A21.2002.00532’,90 ‘A21-203’,’A21.2004.00720’,87
31
10. Tampilkan semua data krs untuk nim angkatan 2001 menggunakan fungsi substr() 11. Dari tabel krs tampilkan data nilai angka dan nilai huruf dengan format sbb Kode_kul
NIM
Nilai Angka
– Ketentuan nilai huruf adalah : • Nilai 0..49 adalah “E” • Nilai 50..59 adalah “D” • Nilai 60..69 adalah “C” • Nilai 70..84 adalah “B” • Nilai 85..100 adalah “A”
Nilai Huruf
32
