Basis Data Lanjut: Bahan 2 - Query – MS SQL SERVER (Transact SQL)

Query – MS SQL SERVER (Transact SQL)

A. Membuat Tabel dengan Perintah SQL

Cara lain selain cara visual untuk membuat table pada SQL SERVER kita dapat 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.

B. Merubah Struktur Table menggunakan Perintah SQL

- Menambah Kolom Pada Tabel

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);

Kolom yang akan dijadikan sebagai primary key harus dalam keadaan not null (tidak boleh kosong).

- Menambah Constraint Primary Key Pada Kolom Suatu Tabel

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;

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

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’);

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	Penjelasan
=	Sama
<>	Tidak sama
<	Lebih kecil
<=	Lebih kecil atau sama
>	Lebih besar
>=	Lebih besar atau sama
Like	Dapat digunakan dengan simbol %(wildcard)
In	Dapat digunakan menggantikan relasi OR
Between	Diantara dua kondisi
Not Between	Tidak diantara dua kondisi
Null	Bernilai kosong
Not Null	Tidak bernilai kosong
&	Untuk memasukan variabel
&&	Untuk memasukan variabel dan nilai disimpan untuk query berikutnya

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.

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 NOT 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

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’;

Contoh:

1. Buatlah sebuah database baru dengan nama DBKaryawan

2. Buatkanlah beberapa tabel pada database yang baru dibuat. Tabel-tabelnya antara lain :

• tbl_karyawan

Kolom-kolomnya :

NIK int PRIMARY_KEY,

Nama varchar(50) null,

Alamat varchar(50) null,

Kota vachar(50) null

• tbl_gaji

Kolom-kolomnya :

Id_gaji int auto_increment PRIMARY_KEY,

NIK int FOREIGN_KEY,

Periode varchar(20) not_null,

Jumlah_Gaji int not_null

3. Buatlah relasi antara NIK di tabel tbl_karyawan dengan NIK di tabel tbl_gaji, berikan atribut “cascade on update” dan “cascade on delete”

4. Gunakan perintah INSERT untuk meng-inputkan data kedalam table tbl_karyawan dan tbl_gaji yang telah dibuat

tbl_karyawan

NIK	Nama	Alamat	Kota
10001	Alex	Jl. Banda No. 10	Bandung
10002	Budi	Jl. Merdeka No. 92	Jakarta
10003	Rudi	Jl. Surya Sumantri No. 61	Bandung
10004	Rini	Jl. Aceh No. 1	Bandung
10005	Nina	Jl. Sudriman No. 76	Jakata

tbl_gaji

Id_gaji	NIK	Periode	Jumlah_Gaji
1	10001	Maret	5.000.000
2	10003	April	3.000.000
3	10004	Maret	4.500.000
4	10003	Januari	4.000.000
5	10001	April	3.250.000
6	10002	Mei	2.750.000

6. Ubah data karyawan dengan menggunakan perintah UPDATE, ubah nama karyawan dengan NIK 10002 pada tabel tbl_karyawan menjadi nama Robert

7. Ubah alamat dan kota pada tabel tbl_karyawan dengan NIK 10005 menjadi alamat Jl. Tubagus No. 8 dan kota Surabaya

8. Hapus data karyawan dengan NIK 10005 menggunakan perintah DELETE

9. Tampilkan semua data karyawan dan jumlah gaji pada periode maret dengan menggunakan perintah SELECT

USE [DBKaryawan]

create table tbl_karyawan

(

NIK int,

Nama varchar(50) null,

Alamat varchar(50) null,

Kota varchar(50) null

CONSTRAINT PK_tbl_karyawan PRIMARY KEY CLUSTERED (NIK)

)

create table tbl_GAJI

(

Id_gaji int identity,

NIK int FOREIGN KEY (NIK) REFERENCES tbl_karyawan (NIK) on update cascade on delete cascade,

Periode varchar(20) not null,

Jumlah_Gaji int not null

CONSTRAINT PK_tbl_GAJI PRIMARY KEY CLUSTERED (Id_gaji)

)

insert into tbl_karyawan

values(10001, 'Alex', 'Jl. Banda No. 10', 'Bandung')

insert into tbl_karyawan

values(10002, 'Budi', 'Jl. Merdeka No. 92', 'Jakarta')

insert into tbl_karyawan

values(10003, 'Rudi', 'Jl. Surya Sumantri No. 61', 'Bandung')

insert into tbl_karyawan

values(10004, 'Rini', 'Jl. Aceh No. 1', 'Bandung')

insert into tbl_karyawan

values(10005, 'Nina', 'Jl. Sudriman No. 76', 'Jakarta')