Rumus 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
[WHERE]
Contohnya:
SELECT AVG (SKS) FROM Mata_kuliah

• COUNT

ü  Untuk menghitung jumlah baris dalam tabel
Sintak :
SELECT COUNT nama_kolom FROM nama_tabel
[WHERE]
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
[WHERE]
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
[WHERE = KONDISI]
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

o   LOWER  mengubah huruf besar ke huruf kecil
o   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

o   LTRIM  memotong karakter  spasi di kiri string
o   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

o   LEFT  mengambil beberapa karakter dari kiri
o   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”
VV….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);

v  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.
v  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)

v  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.

v  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

v  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.

v  Melihat struktur Tabel
Syntax :

sp_columns nama table;

v  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

v  Menghapus tabel
Syntax :

DROP TABEL nama_tabel

v  Integritas data
Integritas data adalah dan 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

v   PRIMARY KEY
Primary Key constraint untuk menjag integritas data
Syntax :
Membuat Primary Key

CREATE TABEL nama_tabel (nama_field tipedata CONSTRAINT pk_namatabel PRIMARY KEY,..)

v  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.

v  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.










v  FOREIGN KEY
FOREIGN KEY adalah kolom pada sebuah tabel yang menunjukan bahwa kolomtersebut adalah primary key pada tabel lain.
Tabel Mahasiswa

NRP	Nama		Alamat	Kota	Propinsi	Telpon
98111761	Aan Isnaini		Jl.Raya  Sepanjang 80 Gondanglegi	Malang	Jatim	0341877769

Tabel Mata_kuliah

Kode_mata_kuliah	Nama_kuliah	SKS
TKK-04	Sistem Database	3

Tabel ambil_kuliah                                                            foreignKey

Kode_mata_kuliah	NRP	Nilai_Uts	Nilai_Uas	Grade
TKK-04	98111761	90	98	A

pada tabel diatas ,recort (TKK-04.98111761)pada tabel ambil_kuliah tidak dapat di hapus karena referensi dari TKK-04(field_kode_mata_kuliah ditabel mata_kuliah ),98111761(field nrp di tabel mahasiswa) masih ada.

Penulisan constraint foreignkey adalah :

CONSTRAINT fk_nama_constraint  FOREIGN KEY field REFERENCES nama_tabel(field)

v  DEFAUL
Nilai Defaul sebuah kolom dapat disuplai oleh system,jika pemakai tidak memberikan nilai pada field tersebut.DEFAUL tidak berlaku untuk kolom dengan tipe data timestamp atau field yang mempunyai property IDENTITY.

v  IDENTITY
System menciptakan nomor urut secara otomatis pada setiap memasukkan record baru.

Penggunaan :

NamaKolom Tipe IDENTITY(No_awal_incremen)

Setiap terjadi INSERT ,maka kolom akan mendapatkan nomor urut,yang dimulai dari No_awal,dan setiap record berikutnya ditambahkan nilai inkremen(default adalah 1)

CREATE TABLE pendaftaran(no_daftar int IDENTITY (100,2),nama char(20))

Pada awalnya no_daftar adalah 100,untuk setiap recortbaru berikutnya,no_daftar akan nilai 102,104,106 dan seterusnya.

v  Drop constraint
DROP digunakan untuk menghapus constraint yang ada
Contoh :

ALTER TABELnama_tabel DROP fk_nama_constraint

v  Mengisi data pada tabel
Syntax :

INSERT  INTO   nama_tabel   (nama_field1,    nama_field2,     nama_field3)VALUES (data1,data2,data3);

v  Meng-copy data dari tabel lain
Suatu kolom pada tabel dapat diisi dengan data yang sama dengan kolom tabel yang lain dengan syarat type data dan panjang field kedua table tersebut sama.
Syntax :

INSERT  INTO nama_tabe_ tujuan    (field1,   field2,    field3)  SELECT Field _sumber 1,field_sumber2,field_sumber3)FROM nama_tabel_sumber;

Jika 2 buah tabel mempunyai struktur yang sama maka syntax di atas daoat disingkat menjadi :

INSERT INTO Tabel_tujuan SELECT*FROMTabel_asal

QUEL & QBE
Query language dari DBMS relational INGRES,(dikembangkan University of California)dengan menggunakan system operasi unix.
Bahasa ini di kembangkan secara berdiri sendiri dedengan menuliskan intruksi ke prosesor QUEL(disisispkan dalam bahasa pemrograman C)
Dalam bahasa c,intruksi QUEL dimulai dengan tanda ##,sehingga dapat di terima oleh prosesor.
QUEL di bagi atas :
1.      DDL
o   CREATE :membuat table
Contoh :
CREATEMHS (NPM=TEXT (20),ALAMAT=TEXT(30),TGL_LAHIR =DATE)
o   INDEX :membuat indeks
Contoh :
Membuat indeks dari table MHS berdasarkan NPM dengan nama MHS_INDEX
INDEX ON MHS IS MHS _INDEX (NPM);
o   DEFINE VIEW :membuat view
Contoh :
DEFINE VIEW MHS_VIEW(NPM=MHS.NPM,NAMA_MHS = MHS.NAMA)
o   DESTROY : menghapus table,indeks,view
Contoh :
DESTROY MHS_VIEW
o   MODIFY : merubah struktur table / indeks
Contoh :
MODIFY MHS TO UNIQUER ON NPM
2.      DML
o      RETRIEVE : menampilkan data
Contoh :
§  Menampilkan nama matakuliah yang bersks 2
RETRIEVE(MTKULIAH.NAMA_MK)WHERE MTKULIAH.SKS=2
§  Menampilkan data semua mahasiswa
RETRIEVE(MHS.ALL)
§  Menampilkan nama mahasiswa dank ode mata kuliah untuk mempunyai nilai uas lebih dari 80
RETRIEVE (MHS.NAMA, NILAI.KD_MK)WHERE MHS.NPM = NILAI.NPM AND NILAI.NIL_UAS>80

o      REPLACE :mengubah data
Contoh :
Ubah nilai uas menjadi 50 dengan mahasiswa dengan npm”30100143”dan kode matakuliah”KK018”
REPLACE NILAI (NIL_UAS =50)WHERE NPM =’30100143’and KD_MK =’KK018’

o      DELETE :menghapus record
Contoh :
Hapus data mahasiswa yang bernama’LINA’
DELETE MHS WHERE NAMA =’LINA’

o      APPEND :masukan record baru
Contoh :

APPEND TO MTKULIAH(KD_MK =’KK022’,NAMA_MK =’SIM’,SKS =3)