DML = Data Manipulation Language (Insert, Update, Delete, Select)
DDL = Data Definition Language (Create, Alter, Drop)
char vs varchar
Char : Jumlah karakter tetap dengan ukuran maksimal 8000 karakter
Varchar Jumlah karakter variabel dengan ukuran maksimal 8000 karakter
ex:
char(5) -> a_ _ _ _(fixed length)
varchar(5) -> a
varchar vs nvarchar
varchar -> untuk karakter biasa
nvarchar -> untuk karakter khusus
NVarchar : Jumlah karakter variabel dengan ukuran maksimal 4000 karakter
Server OS Client Server
- MySQL
- SQLServer (Microsoft)
- Oracle/PLSQL
- DB2 (IBM)
- Informix
- Sybase
File Server
- DBase
- Foxbase/Pro
- MSAccess
SQL Server
- Store Procedure
- Function
- Trigger
- Maintenance Plan
- etc
Langkah-langkah untuk membuat database
1.Klik kanan pada bagian Database, pilih menu New Database …
2.Isi pada bagian Database name : misal KARYAWANUKM. Secara default, akan dibuatkan dua buah file KaryawanUKM.mdf (master data file) dan KaryawanUKM_log.ldf (log data file).
3.Beberapa bisa kita atur : FileType (jenis file Data atau Log), Initial Size (ukuran awal file data), Autogrowth (pengaturan pertumbuhan data), Path (letak file data). Biarkan jika ingin menggunakan default.
4.Tekan OK, jika database akan dibuat oleh system.
Merancang Tabel, Field (Colomn), Primary Key
Setelah Database dibentuk, maka langkah pertama adalah membuat table. Tabel ini digunakan untuk menyimpan data-data yang sejenis misal data karyawan disimpan pada table tbkaryawan, data gaji karyawan disimpan di tbgaji.
Langkah-langkah untuk membuat tbkaryawan :
1.Klik kanan pada Tables, yang terdapat pada database KaryawanUKM, pilih menu New Table.
2.Buat Colomn Name (Field), dengan Data Type yang cocok.
3.Tambahkan Primary Key pada NIP, dengan menekan tombol Primary Key
4.Setelah semua colomn dibuat, simpan table dengan nama tbkaryawan
5.Ulangi dengan cara yang sama untuk membuat table tbgaji (memiliki 2 primary key : NIP dan PERIODE, tekan tombol Shift dan mouse bagian kiri untuk memilihnya baru tekan tombol PK).
Membuat Relasi Antar Tabel (Database Diagram)
Untuk menjaga integritas data, antar table perlu dibuat relasi antar table. Salah satu table yang menjadi masternya harus memiliki Primary Key, sedangkan table detilnya akan dibuatkan secara otomatis Foreign Key.
Langkah-langkah pembuatan Database Diagram, yaitu :
1.Pada Database diagrams, klik kanan pilih menu New Diagram... (jika tidak bisa dibuat diagramnya, pastikan role anda sebagai NTAutority, pilih di menu Database-Properties)
2.Kemudian pilih table-tabel yang akan dibuat relasinya.
3.Lakukan drag pada Primary Key (disini NIP yang ada di tbKaryawan), geser hingga NIP yang ada di tbgaji. Tekan OK untuk membuat relasinya.
Detach dan Attach Database
Jika suatu waktu anda ingin memindahkan Database pada komputer lain, dapat dilakukan dengan melepas database (Detach Database) tsb pada server ini. Kemudian salin file .mdf dan .ldf tsb ke server lain. Setelah itu lakukan Attach Database (menarik file data tsb untuk dikelola oleh server).
Membuat Tabel Dengan Perintah SQL
Selain dengan cara visual yang sudah disediakan oleh Microsoft SQL Server untuk mempermudah pembuatan tabel, cara lainnya adalah dengan menggunakan perintah SQL.
Sebagai latihan kita akan mencoba membuat tabel Mahasiswa dengan dengan menggunakan perintah SQL.
Langkah-langkah pembuatan tabel adalah sebagai berikut :
•Buka Enterprise Manager.
•Buka Database yang sudah dibuat, misal dbperkuliahan.
•Klik di Menu Tools> Klik New Query
•Setelah Query Analyzer terbuka, masukan perintah SQL untuk membuat tabel Mahasiswa seperti dibawah ini.
create table Mahasiswa (
NIM char(8),
Tahun_Ajaran char(4),
Kode_Jurusan char(5),
Nama varchar(50),
Tempat_Lahir varchar(50),
Tanggal_Lahir datetime,
Alamat varchar(150),
Kota varchar(50),
No_Telepon varchar(12)
);
•Klik(Parse Query atau Ctrl+F5) untuk mengecek penulisan perintah query, jika tidak ada pesan kesalahan klik (Execute Query atau F5) untuk mengeksekusi perintah query. Apabila tampil pesan “The Command(s) completed succesfully” maka tabel sudah berhasil dibuat.
Merubah Struktur Tabel Dengan Perintah SQL
- Menambah Kolom Pada Tabel
Melakukan perubahan struktur tabel seperti penambahan kolom Status_Mahasiswa pada tabel mahasiswa, perintah query menambah kolom adalah :
alter table Mahasiswa add Status_Mahasiswa char(12);
- Merubah Type Data Kolom Pada Tabel
Perintah query untuk mengubah type data suatu tabel (misal mengubah tipe data Status_Mahasiswa dari char(12) menjadi varchar(12)) yaitu:
alter table Mahasiswa alter column Status_Mahasiswa varchar(12);
- Menghapus Kolom Pada Tabel
Jika karena suatu kolom tidak diperlukan dan akan dihilangkan dari suatu tabel dikarenakan sesuatu hal maka perintah query untuk menghapus kolom tersebut yaitu:
alter table Mahasiswa drop column Status_Mahasiswa;
- Menambah Constraint Primary Key Pada Kolom
Perintah query untuk menambah constraint Primary Key pada kolom suatu tabel adalah:
alter table Mahasiswa add constraint pk_Mahasiswa primary key(NIM);
Perlu diketahui bahwa kolom yang akan dijadikan sebagai primary key harus dalam keadaan not null (tidak boleh kosong).
- Menambah Constraint Primary Key Pada Kolom Suatu Tabel
Perlu diketahui bahwa kolom yang akan dijadikan sebagai primary key harus dalam keadaan not null (tidak boleh kosong). Jadi jika kolom yang akan dijadikan primary key tidak dalam keadaan not null (boleh kosong) harus di ubah menjadi not null.
alter table Mahasiswa alter column NIM char(8) not null;
Perintah query untuk menambah constraint Primary Key pada kolom suatu tabel adalah:
alter table Mahasiswa add constraint pk_Mahasiswa primary key(NIM);
- Menambah Constraint Foreign Key Pada Kolom Suatu Tabel
Misalkan, tabel Mahasiswa mereferensi ke tabel jurusan pada kolom Kode_Jurusan.
Maka perintah query untuk menghubungkannya adalah :
alter table Mahasiswa add constraint fk_Mahasiswa_ref_jurusan foreign key (Kode_Jurusan) references Jurusan(Kode_Jurusan);
- Menambah Constraint Pada Kolom Suatu Tabel
Perintah query untuk menghapus constraint adalah :
alter table Mahasiswa drop constraint fk_Mahasiswa_ref_jurusan;
Menghapus Tabel Dengan Perintah SQL
Perintah query untuk menghapus suatu tabel adalah:
drop table Mahasiswa;
DML(Data Manipulation Language)
DML merupakan perintah yang sering dipergunakan dalam Transaksi SQL oleh database developer.
Perintah-perintah SQL yang tergolong DML antara lain :
1. INSERT
2. SELECT
3. UPDATE
4. DELETE
Sebagai latihan kita akan menggunakan tabel Jurusan dan Mahasiswa.
1. INSERT
Perintah insert dipergunakan untuk memasukan data kedalam tabel. Perintahnya adalah :
Insert into Mahasiswa (NIM, Tahun_Ajaran, Kode_Jurusan, Nama, Tempat_Lahir, Tanggal_Lahir, Alamat, Kota,No_Telepon)
values (‘10113025’, ‘2005’, ‘LB’, ’Dina Rukmantara’, ‘Bandung’ ,’08-20-1983’, ‘Jl. Sari Jadi’, ‘Bandung’, ‘081322442269’);
Catatan :
Dalam proses input matakuliah, jika tabel mahasiswa mereferensi ke tabel jurusan maka tabel jurusan harus memiliki data jurusan yang akan dipergunakan/diinputkan dalam tabel mahasiswa.
Perintah insert pada kolom-kolom tabel yang sudah default, data dapat dituliskan langsung sesuai dengan urutan kolom tanpa perlu menuliskan nama kolomnya seperti perintah dibawah :
Insert into Mahasiswa values (‘10113025’, ‘2005’, ‘LB’, ’Dina Rukmantara’, ‘Bandung’ ,’08-20-1983’, ‘Jl. Sari Jadi’, ‘Bandung’, ‘081322442269’);
Untuk memasukan hanya pada kolom-kolom tertentu bisa menggunakan perintah berikut :
Insert into Mahasiswa (NIM, Tahun_Ajaran, Kode_Jurusan, Nama)
values (‘10113025’, ‘2005’, ‘LB’, ’Dina Rukmantara’);
2. SELECT
Perintah ini adalah perintah yang paling sering dipergunakan karena kegunaannya adalah untuk membaca (query) isi tabel.
Select * from Mahasiswa ;
Perintah diatas apabila dieksekusi akan menampilkan seluruh data dalam tabel Mahasiswa.
Untuk menampilkan data-data tertentu dari satu atau beberapa tabel kita bisa menggunakan perintah WHERE yang berfungsi sebagai syarat kondisi dari data yang akan ditampilkan. Contoh:
Select * from Mahasiswa where Tahun_Ajaran = ‘2005’;
Data Mahasiswa yang akan muncul adalah Mahasiswa yang masuk pada tahun ajaran 2005.
MSSQL mengediakan sejumlah operator relasi untuk membuat suatu kondisi yang diinginkan.
OPERATOR “>=” DAN “<=”
Dua kondisi, lebih besar sama dengan dan lebih kecil sama dengan.
Select * from Mahasiswa where Tahun_Ajaran>=2001 and Tahun_Ajaran<=2005;
LIKE
Untuk menyatakan suatu kondisi pencarian pada suatu kolom yang mengandung sebagian karakter yang akan ditampillkan.
Select * from Mahasiswa where Nama Like ‘Dina%’;
Perintah diatas digunakan untuk menampilkan data Mahasiswa yang memiliki nama awal nya Dian. %(Wildcard) untuk menyatakan karakter yang diabaikan.
IN
Perintah untuk menampilkan data yang memiliki nilai sama dengan nilai-nilai yang sudah ditentukan.
Select * from Mahasiswa where Tahun_Ajaran in (2005,2006);
BETWEEN
Perintah untuk menampilkan data yang memiliki nilai diantara dua nilai yang akan dicari.
Select * from Mahasiswa where Tahun_Ajaran Between 2001 and 2006;
Kebalikan dari between adalah not between.
Select * from Mahasiswa where Tahun_Ajaran Between 2001 and 2006;
NULL dan NOT NULL
Null menyatakan suatu nilai Null atau kosong, sedangkan Not Null menyatakan nilai Not Null atau tidak kosong .
Select * from Mahasiswa where No_Telepon IS NOT NULL;
AND
Operator AND berfungsi untuk menentukan bahwa semua kondisi harus dipenuhi untuk menampilkan hasil query.
Select * from Mahasiswa
where Tahun_Ajaran = ‘2005’ and Kode_Jurusan = ‘LB’;
Data yang akan ditampilkan hasil dari perintah diatas adalah yang memiliki Tahun Ajaran = 2005 dan Kode Jurusan = LB
OR
Operator OR berfungsi untuk menentukan bahwa salah satu kondisi harus dipenuhi.
Select * from Mahasiswa
where Tahun_Ajaran = ‘2005’ or Kode_Jurusan = ‘LB’;
GROUP BY
Dipergunakan untuk mengelompokan data.
Select kode_jurusan,count(NIM) from Mahasiswa group by kode_jurusan;
Count untuk menghitung jumlah barus dalam per group.
HAVING
Dipergunakan sebagai kondisi atau syarat yang digunakan setelah group by.
select kode_jurusan,count(NIM) from Mahasiswa
group by kode_jurusan
having count(NIM)>1;
ORDER BY
Operator ORDER BY dipergunakan untuk mengurutkan data yang secara default urutannya menaik atau Ascending (ASC). Selain bisa mengurutkan secara Ascending, kita juga bisa mengurutkan data secara menurun atau Descending (DESC).
Select * from Mahasiswa
where Tahun_Ajaran = ‘2005’ and Kode_Jurusan = ‘LB’ order by NIM asc;
DISTINCT
Ada kalanya, hasil query menampilkan row-row dengan nilai yang sama. Jika nilai yang sama ingin ditampilkan hanya satu kali, maka kita bisa menggunakan operator DISTINCT.
Select distinct(Kode_Jurusan) from Mahasiswa;
SELECT dari Beberapa Tabel
Sebelumnya kita sudah melakukan perintah select dengan satu tabel. Sekarang kita akan mencoba perintah select dengan tabel lebih dari satu.
Dalam salah satu kasus kita akan menampilkan nama jurusan berdasarkan data kode jurusan yang terdapat dalam tabel Mahasiswa. Dalam tabel mahasiswa tidak terdapat nama jurusan, untuk memperoleh nama jurusan kita perlu melakukan pengambilan atau penggabungan data dengan tabel Jurusan dengan kolom kode jurusan sebagai kunci.
Select Mahasiswa.NIM, Mahasiswa.Nama, Jurusan.Nama_Jurusan from Mahasiswa,Jurusan where Mahasiswa.kode_jurusan = Jurusan.kode_jurusan;
Dalam perintah diatas bisa dipergunakan perintah alias untuk menggantikan nama objek sebenarnya. Seperti contoh dibawah ini :
Select Mhs.NIM, Mhs.Nama, Jrs.Nama_Jurusan from Mahasiswa Mhs,Jurusan Jrs where Mhs.kode_jurusan = Jrs.kode_jurusan;
Perintah diatas mengaliaskan nama tabel Mahasiswa menjadi Mhs dan Jurusan menjadi Jrs. Selain mengaliaskan nama tabel kita juga bisa mengaliaskan kolom dengan menggunakan perintah AS.
Select Mhs.NIM, Mhs.Nama, Jrs.Nama_Jurusan,
Mhs.Tempat_Lahir+’, ’+cast(Mhs.Tanggal_Lahir as varchar) AS TTL
from Mahasiswa Mhs,Jurusan Jrs
where Mhs.kode_jurusan = Jrs.kode_jurusan;
dalam perintah diatas terdapat perintah cast yang berfungsi untuk mengkonversi tanggal lahir dengan tipe data datetime menjadi varchar agar dapat di gabungkan dengan kolom tempat lahir yang sebelumnya bertipe varchar menjadi sebuah kolom yang nama aliasnya TTL.
3. UPDATE
Perintah update dipergunakan untuk melakukan perubahan data yang sudah disimpan. Perintahnya adalah :
Update Mahasiswa set Nama = ‘Aming Surya Praja’ where NIM =’10113025’;
4. DELETE
Perintah DELETE dipergunakan untuk menghapus data dalam tabel.
Delete from Mahasiswa where NIM =’10113025’;
CREATE TABLE tbl_karyawan
(
NIK int,
Nama varchar(50),
Alamat varchar(50),
Kota varchar(50)
CONSTRAINT NIK_PK PRIMARY KEY (NIK)
)
CREATE TABLE tbl_gaji
(
Id_gaji int identity,
NIK int,
Periode varchar(20) not null,
Jumlah_Gaji int not null,
CONSTRAINT IdGaji_PK PRIMARY KEY (Id_gaji),
CONSTRAINT NIK_FK FOREIGN KEY (NIK) REFERENCES tbl_karyawan (NIK)
)
INSERT INTO tbl_karyawan (NIK,Nama,Alamat,Kota)
VALUES (10001,'Alex','Jl. Banda No. 10','Bandung')
INSERT INTO tbl_karyawan (NIK,Nama,Alamat,Kota)
VALUES (10002,'Budi','Jl. Merdeka No. 92','Jakarta')
INSERT INTO tbl_karyawan (NIK,Nama,Alamat,Kota)
VALUES (10003,'Rudi','Jl. Surya Sumantri No. 61','Bandung')
INSERT INTO tbl_karyawan (NIK,Nama,Alamat,Kota)
VALUES (10004,'Rini','Jl. Aceh No. 1','Bandung')
INSERT INTO tbl_karyawan (NIK,Nama,Alamat,Kota)
VALUES (10005,'Nina','JJl. Sudriman No. 76','Jakata')
INSERT INTO tbl_gaji (NIK,Periode,Jumlah_Gaji)
VALUES (10001,'Maret',5000000)
INSERT INTO tbl_gaji (NIK,Periode,Jumlah_Gaji)
VALUES (10003,'April',3000000)
INSERT INTO tbl_gaji (NIK,Periode,Jumlah_Gaji)
VALUES (10004,'Maret',4500000)
INSERT INTO tbl_gaji (NIK,Periode,Jumlah_Gaji)
VALUES (10003,'Januari',4000000)
INSERT INTO tbl_gaji (NIK,Periode,Jumlah_Gaji)
VALUES (10001,'April',3250000)
INSERT INTO tbl_gaji (NIK,Periode,Jumlah_Gaji)
VALUES (10002,'Mei',2750000)
UPDATE tbl_karyawan
SET Nama = 'Robert'
WHERE NIK = 10002
UPDATE tbl_karyawan
SET Alamat = 'Jl. Tubagus No. 8',Kota = 'Surabaya'
WHERE NIK = 10005
DELETE FROM tbl_karyawan
WHERE NIK = 10005
SELECT k.NIK,k.Nama,k.Alamat,k.Kota,g.Jumlah_Gaji
FROM tbl_karyawan k
JOIN tbl_gaji g on k.NIK = g.NIK
WHERE Periode = Maret
quiz 01:
create database db_stan
use db_stan
create table tbl_provinsi
(
kode_provinsi varchar(5),
nama_provinsi varchar(50)
CONSTRAINT kodeProvinsi_PK PRIMARY KEY (kode_provinsi)
)
create table tbl_bank
(
kode_bank varchar(7),
nama_bank varchar(50)
CONSTRAINT kodeBank_PK PRIMARY KEY (kode_bank)
)
create table tbl_formulir
(
no_formulir int identity,
nama_lengkap varchar(50),
tgl_lahir datetime,
kode_provinsi varchar(5),
jenis_kelamin varchar(10),
tinggi_badan int,
tahun_lulus int,
total_nilai int,
jumlah_pelajaran int,
kondisi_mata varchar(30),
kode_bank varchar(7)
CONSTRAINT noFormulir_PK PRIMARY KEY (no_formulir),
CONSTRAINT kodeProvinsi_FK FOREIGN KEY (kode_provinsi) REFERENCES tbl_provinsi (kode_provinsi),
CONSTRAINT kodeBank_FK FOREIGN KEY (kode_bank) REFERENCES tbl_bank (kode_bank
)
insert into tbl_provinsi(kode_provinsi,nama_provinsi)
values(10001,'Jawa Barat')
insert into provinsi(kode_provinsi,nama_provinsi)
values(10002,'Jawa Tengah')
insert into provinsi(kode_provinsi,nama_provinsi)
values(10003,'Jawa Timur')
insert into provinsi(kode_provinsi,nama_provinsi)
values(10004,'Kalimantan Barat')
insert into provinsi(kode_provinsi,nama_provinsi)
values(10005,'Sumatera Barat')
insert into tbl_bank(kode_bank,nama_bank)
values(1,'Mandiri')
insert into bank(kode_bank,nama_bank)
values(2,'BRI')
insert into bank(kode_bank,nama_bank)
values(3,'BNI')
insert into bank(kode_bank,nama_bank)
values(4,'BCA')
insert into bank(kode_bank,nama_bank)
values(5,'NISP')
insert into tbl_formulir(nama_lengkap,tgl_lahir,kode_provinsi,jenis_kelamin,tinggi_badan,tahun_lulus,total_nilai,jumlah_pelajaran,kondisi_mata,kode_bank)
values('william',25/09/1990,10001,'Laki-laki',165,2011,1889,144,'silinder',4)
insert into tbl_formulir(nama_lengkap,tgl_lahir,kode_provinsi,jenis_kelamin,tinggi_badan,tahun_lulus,total_nilai,jumlah_pelajaran,kondisi_mata,kode_bank)
values('david',13/01/1989,10005,'Laki-laki',170,2011,1839,144,'normal',1)
insert into tbl_formulir(nama_lengkap,tgl_lahir,kode_provinsi,jenis_kelamin,tinggi_badan,tahun_lulus,total_nilai,jumlah_pelajaran,kondisi_mata,kode_bank)
values('richard',17/08/1945,10003,'Laki-laki',180,2011,1989,144,'minus',3)
insert into tbl_formulir(nama_lengkap,tgl_lahir,kode_provinsi,jenis_kelamin,tinggi_badan,tahun_lulus,total_nilai,jumlah_pelajaran,kondisi_mata,kode_bank)
values('lukas',25/08/1991,10004,'Laki-laki',170,2011,1899,144,'minus',5)
insert into tbl_formulir(nama_lengkap,tgl_lahir,kode_provinsi,jenis_kelamin,tinggi_badan,tahun_lulus,total_nilai,jumlah_pelajaran,kondisi_mata,kode_bank)
values('syella',20/01/1989,10002,'Perempuan',165,2011,1789,144,'normal',2)
quiz 02:
SELECT p.KodePelanggan,p.NamaPelanggan,f.TglFj,f.TotalFaktur
from FJ f join Pelanggan p on (f.KodePelanggan = p.KodePelanggan)
where f.TglFj between '6/1/2005' and '6/30/2005 23:59:59'
SELECT top 5 f.KodePelanggan,p.NamaPelanggan,sum(f.TotalFaktur)
from FJ f join Pelanggan p on (f.KodePelanggan = p.KodePelanggan)
where f.TglFj between '6/1/2005' and '6/30/2005 23:59:59'
group by f.KodePelanggan, p.NamaPelanggan
order by sum(f.TotalFaktur) desc
create table PelangganPilihan
(
KodePelanggan nvarchar(10),
cek bit,
CONSTRAINT PelangganPilihan_PK PRIMARY KEY (KodePelanggan),
CONSTRAINT KodePelanggan_FK FOREIGN KEY (KodePelanggan) REFERENCES Pelanggan(KodePelanggan)
)
insert into PelangganPilihan(KodePelanggan,cek)
values('C-00001',1)
insert into PelangganPilihan(KodePelanggan,cek)
values('C-00002',0)
select p.*
from PelangganPilihan pp join Pelanggan p on (pp.KodePelanggan = p.KodePelanggan)
where pp.cek = 1
SELECT *
from FJ f join Pelanggan p on (f.KodePelanggan = p.KodePelanggan)
join PelangganPilihan pp on (pp.KodePelanggan = p.KodePelanggan)
Tidak ada komentar:
Posting Komentar