SQL Tingkat Lanjut 1

Fungsi-fungsi dalam SQL 1) Fungsi Agregaris/Agregates  AVG : Menghitung rata-rata dari suatu kolom tertentu yang telah didefinisikan dalam perintah select. Sintak : SELECT AVG nama_kolom FROM nama_tabel Contohnya: SELECT AVG (SKS) FROM Mata_kuliah  COUNT : Untuk menghitung jumlah baris dalam tabel Sintak : SELECT COUNT nama_kolom FROM nama_tabel Contoh : SELECT COUNT (*) FROM mahasiswa SELECT COUNT (SKS) FROM Mata_kuliah  MAX : Untuk mengetahui nilai terbesar dari sebuah kolom tertentu dalam perintah select Sintak : SELECT MAX (SKS) FROM Mata_kuliah  MIN : Untuk mengetahui nilai terkecil dari sebuah kolom tertentu dalam perintah select Sintak : SELECT MIN nama_kolom FROM nama_tabel Contoh : SELECT MIN (SKS) FROM Mata_kuliah  SUM : Untuk menjumlah suatu kolom tertentu yang telah didefinisikan dalam perintah select. Sintak : SELECT SUM (nama_kolom)FROM nama_tabel Contoh : SELECT SUM (SKS) From Mata_kuliah 2) Fungsi Waktu  GETDATE : Untuk menampilkan tanggal sekarang Contoh : SELECT GATEDATE()  DATEADD : Untuk menambah jumlah hari ,tanggal atau tahun yang ditentukan Sintak : DATEADD(datepart,jumlah,tanggal) Contoh : SELECT DATEADD(day,10,getdate()) SELECT DATEADD(Month,10,getdate()) SELECT DATEADD(year,10,getdate())  DATEDIFF : Untuk mengetahui rentan waktu (bias hari,bulan,tahun) Sintak : DATEDIFF (datepart,tanggal_awal,tanggal_akhir) Contoh : SELECT DATEDIFF(month,’01/01/2007’,’01/01/2008)  DATENAME : Untuk menampilkan nama hari,bulan atau tahun Sintak : DATENAME(detepart,tanggal) Contoh : SELECT DATENAME(month.getdate()) AS’Bulan sekarang’ 3) Fungsi Aritmatika  SQRT : Untuk mencari akar dari nilai tertentu. Contoh : SELECT SQRT (100)  CEILING : Untuk pembulatan ke atas dari angka decimal Contoh : SELECT CEILING(25.25) Keterangan : Akan menghasilkan nilai 26  FLOOR : Untuk pembulatan ke bawah dari angka decimal Contoh : SELECT FLOOR (25.25) Keterangan : Akan menghasilkan nilai 25 4) Fungsi Karakter  LOWER dan UPPER LOWER mengubah huruf besar ke huruf kecil UPPER mengubah huruf kecil ke huruf besar Contoh : SELECT LOWER (nama)FROM mahasiswa SELECT UPPER(nama)FROM mahasiswa  LEN : Untuk menghitung jumlah karakter Contoh : SELECT nama,LEN(nama)FROM mahasiswa  LTRIM dan RTRIM LTRIM memotong karakter spasi di kiri string RTRIM memotong karakter spasi di kanan string Contoh : SELECT LTRIM (‘Sistem Basis Data’) SELECT RTRIM (‘Sistem Basis Data‘) Keterangan : Akan menghasilkan “Sistem Basis Data”  SUBSTRING : Mengambil beberapa karakter dari sebuah deretan string Contoh : SELECT SUBSTRING(‘Sistem Basis Data’,8,5) Keterangan : Mengambil string dari karakter ke8 sebanyak 5 karakter Hasilnya adalah “Basis”  LEFT dan RIGHT LEFT mengambil beberapa karakter dari kiri RIGHT mengambil beberapa karakter dari kanan Contoh : SELECT LEFT(‘Sistem Basis Data’,6) Keterangan : mengambil setring sebanyak 6 karakter dari sebelah kiri. Hasilnya adalah “system” PREDIKAT COMPARISON Perbandingan dua nilai dengan syarat type data yang dibandingkan harus sama = ‘‘sama dengan’’ <> ’’tidak sama dengan” < ”lebih kecil” > “lebih besar” <= “lebih kecil dan sama dengan” >= “lebih besar dan sama dengan” BETWEEN Perbandingan untuk mengecak apakah suatu nilai berada dalam range tertentu atau tidak Syntax :…..BETWEEN……AND…..NOT BETWEEN….AND…. Contoh ; Menampilkan data nilai pada range 80 dan 100 SELECT * FROM Nilai WHERE NI_Angka BETWEEN 80 AND 100 IN In digunakan untuk melakukan pengecekan apakah suatu nilai tersebut dalam suatu himpunan. Syntax :IN(..) IN SELECT…. Contoh ; SELECT * FROM pelajar a WHERE a.No_Induk IN(SELECT b.No_Induk FROM nilai b); LIKE/NOT LIKE Untuk membandingkandata dengan pola / struktur tertentu, untuk satu karakter dipakai ( _ ) dan string ( %) Syntax : …..Like …..NOT Like Contoh ; SELECT *FROM pelajar WHERE nama LIKE ‘We%’; IS NULL/IS NOT NULL Untuk membandingkan suatu nilai dengan null Syntax :..IS NULL ..IS NOT NULL Contoh ; SELECT * FROM pelajar WHERE kelas IS NULL EXIST Digunakan untuk pengecekan apakah suatu query memiliki suatu hasil atau tidak. Syntax :…….WHERE EXIST (SELECT…) Contoh : SELECT * FROM pelajar a WHERE EXIST(SELECT b.No_Induk FROM nilai bWHERE a.No_Induk = b.No_Induk); Data Definition Language (DDL) Tabel dalam data Base adalah komponen utama yang membentuk database itu sendiri. Baris (Row) dan kolom (Column) mengidentifikasikan data (Record). Cara untuk mengatur dan memanipulasi data pada database dilakukan dengan bantuan interuksi SQL seperti SELECT, INSERT, UPDATE, DELETE. Intruksi untuk membuat ,mengubah,dan mennghapus table dilakukan melalui DDL yaitu dengan CREATE TABEL, ALTER TABEL, DROP TABEL. Tipe Data yang digunakan SQL Sever Char(n) Mendefinisikan string sepanjang n karakter.Bila n tidak didefinisikan maka panjang karakter adalah 1. Varchar(n) Mendefinisikan setring sepanjang variable n. Binary(n) Untuk menyimpan bit patter seperti heksadecimal. Contoh: 0x0fa9008e Datetime Mendefinisikan tanggal,menyimpan tahun,bulan,hari jam,menit,detik dan seperseribu detik(milliseconds).nilai tanggal sampai dengan 31 desember 9999 Text Menyimpan text sampai dengan2 GB.Text disebut juga dengan binary large objects(BLOBs) Image Mendefinisikan binary data untuk menyimpan image seperti GIF,JPG,TIFF,dll Money Bilangan pecahan dengan 4 angka di belakang koma,Digunakan untuk perhitungan moneter Smallint Sama dengan int,membutuhkan 50% memory yang di tempati int Int Mendefinisikan integer, bilangan bulat yang menampung angka sebanyak 4 byte Float(n) Mendefinisikan angka pecahan (floating point).Nilai n adalah jumlah angka yang dapat di tampung sysname Real(n) Sama dengan float namun mempunyai memory 50% dari float Small datetime Sama dengan datetime hanya dengan presisilebih kecil dimana satuan waktu terkecil adalah menit dan nilai tanggal sampai dengan6 juni 2079 Numeric(n,p) Mendefinisikan angka pecahan baik fixed decimal ataupun floating point.Nilai n adalah jumlah Bytestotal dan p adalah presisi angka di belakang koma.Numeric analog dengan DECIMAL(n,p) Unicode character String Unicode adalah karakter internasional yang menampung 16 bit per karakter.Unicode digunakan oleh bahasa non latin,misalnya Jepang, Jerman, Thai, dll. Untuk melihat semua type data yang ada dalam Ms.SQL Server adalah dengan menggunakan perintah stored procedure sp_datatype_info. Mendefinisikan data baru. Type data yang digunakan dari type data yang sudah ada. Syntax secara umum untuk membuat tipe data baru : USE nama_database SP ADDTYPE nama tipe data baru,tipe data SQL Syntax secara umum untuk menghapus tipe data baru USE nbama-database SP DROPTYPE nama tipe data baru Perintah-perintah yang berhubungan dengan table : INTRUKSI KETERANGAN CREATE TABEL : Membuat Tabel SP_COLUMN : Memeriksa struktur suatu table ALTER TABEL : Mengubah struktur table DROP TABEL : Menghapus table SELECT : Memilih Query table INSERT : Menyisipkan baris data ke table UPDATE : Mengubah data pada table DELETE : Menghapus data pada suatu tabel Membuat Tabel Syntax : CREATE TABEL nama-tabel(nama_field1 tipedata batasan, nama_field2 tipedata batasan,…..) Batasan (constraint)= yang mengikat atribut apakah sebagai Primary Key .Fpreign Key, Unique, Not Null,dll. Melihat struktur Tabel Syntax : sp_columns nama table; Memodifikasi dan menambah field pada tabel Memodifikasi sebuah field pada tabel Syntax : ALTER TABEL nama_tabel ALTER COLUMN nama_field tipe_data(panjang) constraint Menambah sebuah field pada table Syntax : ALTER TABEL nama_tabel ADD nama_field _baru tipe_data(panjang)constraint Menghapus tabel Syntax : DROP TABEL nama_tabel Integritas data Integritas data adalah kebenaran data yang disimpan. integritas data tetap di jaga melalui beberapa cara antara lain :  Validasi field secara individual  Varifikasi satu field melalui field yang lainnya  Validasi data dari satu table ke table lainnya  Verifikasi bahwa transaksi berjalan secara sukses dari awal sampai akhir Beberapa jenis integritas data 1) Integritas Entitas Dalam table setiap harus mempunyai identitas yang unik yang disebut dengan Primary Key. Hubungan antara primary key dan foregn key menyatakan apakah sebuah baris table dapat diubah atau dihapus. Batasan :  PRYMARY KEY  UNIQUE KEY  UNIQUE INDEX  IDENTITY 2) Integritas Domain Menyatakan bahwa nilai data sesuai dengan kenyataannya.system dalam hal ini melakukan CHECK sesuai dengan aturan (rule)yang berlaku. Batasan dan implementasi : - DEFAULT - CHECK - FOREIGN KEY - Tipe data, default, rules 3) Integritas Refrensial Menyetakan relasi antara table bahwa hubungan foreign key dengan primary key.sebuah foreign key tidak dapat dimasukan kedalam sebuah table bila primary key tidak atau belum ada. Batasan dan implementasi : - CHECK - FOREIGN KEY - TRIGGERS DAN STORE PROCEDURE 4) Integritas melalui aturan yang didefinisikan sendiri Sesuai dengan aturan logika dan bisnis yang ada maka pemakai dapat menetapkan batasan dan aturan dari data yang disimpan. Batasan dan implementasi : -Batasan melalui table dan kolom pada saat pembuatan table -FOREIGN KEY -TRIGGERS DAN STORE PROCEDURE PRIMARY KEY Primary Key constraint untuk menjaga integritas data Syntax : Membuat Primary Key CREATE TABEL nama_tabel (nama_field tipedata CONSTRAINT pk_namatabel PRIMARY KEY,..) UNIQUE UNIQUE digunakan untuk menjamin bahwa nilaikolom adalah tunggal tidak mempunyai duplikat. Primary Key dan Unique keduanya adalah kunci kata yang mempunyai arti yang sama. CHECK CHECK digunakan untuk menjamin bahwa nilai kolom adalah dalam ruang niali tertentu.Misalnya nilai mata kuliah dibatasi tidak boleh melebihi nilai 100. Operator logika AND,OR.dan NOT dapat disertakan dalam pembuatan batasan ini. FOREIGN KEY FOREIGN KEY adalah kolom pada sebuah tabel yang menunjukan bahwa kolom tersebut adalah primary key pada tabel lain.