Dipagi ini saya sempatkan untuk berbagi sedikit
tentang Perintah Database SQL Server,
karena sedikit banyaknya Perintah Database SQL Server ini akan
berguna, khususnya bagi pemula yang ingin membuat program berbasis Database menggunakan SQL
Server.
Berikut ini perintah-perintah SQL pada Database SQL
Server. Materi pelatihan berupa contoh-contoh perintah SQL
dasar mulai dari CREATE, SELECT, UPDATE, DELETE, sampai
PROCEDURE dan TRIGGER.
Membuat Database
CREATE DATABASE rumahsakit
Menghapus Database
DROP DATABASE rumahsakit
Menggunakan Database
USE rumahsakit
Membuat Tabel
CREATE TABLE dokter(
nip nvarchar(50) NOT NULL,
nama nvarchar(200) NULL,
alamat text NULL,
tgllahir datetime NULL,
tunjangan float NULL,
PRIMARY KEY (nip))
CREATE TABLE jabatan(
idjabatan nvarchar(50) NOT NULL PRIMARY KEY,
namajabatan nvarchar(200) NULL)
Menambah Kolom pada Tabel
ALTER TABLE dokter ADD idjabatan nvarchar(50) NULL
Menambah Data
INSERT INTO dokter (nip, nama, alamat, tgllahir, tunjangan) VALUES ('222', 'Dr.
Dewi', 'Solo', '1983-02-20', 1500000);
Mengupdate Data
UPDATE dokter SET nip = '333', nama = 'Dr. Asri', alamat = 'Jogja', tgllahir =
'1984-03-14', tunjangan = 2000000 WHERE nip = '444'
Menghapus Data
DELETE FROM dokter WHERE nip = '111'
Memfilter Data dengan Klausa WHERE
SELECT * FROM dokter WHERE nip = '111'
SELECT * FROM dokter WHERE nama = 'Dr. Andi Irmawan'
SELECT * FROM dokter WHERE nama LIKE '%Andi%'
SELECT * FROM dokter WHERE tunjangan = 1000000
SELECT * FROM dokter WHERE tunjangan <= 1000000
SELECT * FROM dokter WHERE tunjangan <> 1000000
SELECT * FROM dokter WHERE tunjangan IS NULL
SELECT * FROM dokter WHERE NOT tunjangan IS NULL
SELECT * FROM dokter WHERE tunjangan > 10000 AND alamat LIKE '%Yogya%'
SELECT * FROM dokter WHERE tgllahir > '1980-12-27'
Fungsi Menambah Tanggal atau Mengurangi Tanggal dengan Hari, Bulan, atau
Tahun
SELECT nip,DATEADD(day,30,TglLahir) AS Kadaluarsa FROM dokter
SELECT nip,DATEADD(day,-30,TglLahir) AS Kadaluarsa FROM dokter
SELECT nip,nama,DATEADD(year,60,TglLahir) AS Pensiun FROM dokter
SELECT nip,nama,tgllahir FROM dokter WHERE GETDATE() >
DATEADD(day,30,tgllahir)
Menggabungkan Dua Buah Tabel dengan Penggabungan Biasa
SELECT * FROM dokter, jabatan WHERE dokter.idjabatan = jabatan.idjabatan
Menggabungkan Dua Buah Tabel dengan JOIN
SELECT * FROM dokter JOIN jabatan ON dokter.idjabatan = jabatan.idjabatan
Menggabungkan Dua Buah Tabel dengan LEFT JOIN
SELECT * FROM dokter LEFT JOIN jabatan ON dokter.idjabatan = jabatan.idjabatan
Menggabungkan Dua Buah Tabel dengan RIGHT JOIN
SELECT * FROM dokter RIGHT JOIN jabatan ON dokter.idjabatan = jabatan.idjabatan
Menggabungkan Dua Buah Tabel dengan FULL JOIN
SELECT * FROM dokter FULL JOIN jabatan ON dokter.idjabatan = jabatan.idjabatan
Menghapus Kolom dalam suatu Tabel
ALTER TABLE dokter DROP COLUMN idjabatan
Membuat Tabel dengan Foreign Key
CREATE TABLE dokter(
nip nvarchar(50) NOT NULL,
nama nvarchar(200) NULL,
alamat text NULL,
tgllahir datetime NULL,
tunjangan float NULL,
PRIMARY KEY (nip) ,
CONSTRAINT fk_dokjab FOREIGN KEY (idjabatan) REFERENCES jabatan(idjabatan))
Mengubah kolom Tabel dan menambahkan Foreign Key setelah Tabel dibuat.
ALTER TABLE dokter ADD idjabatan nvarchar(50)
ALTER TABLE dokter ADD CONSTRAINT fk_dokjab FOREIGN KEY (idjabatan) REFERENCES
jabatan(idjabatan)
Mengetes Foreign Key sudah Bekerja atau Belum
UPDATE dokter SET idjabatan = 'KEP' WHERE nip = '111'
UPDATE dokter SET idjabatan = 'MANN' WHERE nip = '222'
UPDATE dokter SET idjabatan = NULL WHERE nip = '111'
UPDATE jabatan SET idjabatan = 'KPL' WHERE idjabatan = 'KEP'
UPDATE jabatan SET idjabatan = 'STF' WHERE idjabatan = 'STA'
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the FOREIGN KEY constraint
"fk_dokjab". The conflict occurred in database
"rumahsakit", table "dbo.jabatan", column 'idjabatan'.
The statement has been terminated.
Menghapus Foreign Key
ALTER TABLE dokter DROP CONSTRAINT fk_dokjab
Menambah Foreign Key dengan On Update Cascade
ALTER TABLE dokter ADD CONSTRAINT fk_dokjab FOREIGN KEY (idjabatan) REFERENCES
jabatan(idjabatan) ON UPDATE CASCADE
Mengetes Foreign Key dengan Cascade Update
UPDATE jabatan SET idjabatan = 'KPL' WHERE idjabatan = 'KEP'
Menghapus Foreign Key
ALTER TABLE dokter DROP CONSTRAINT fk_dokjab
Menambah Foreign Key dengan On Delete Cascade
ALTER TABLE dokter ADD CONSTRAINT fk_dokjab FOREIGN KEY (idjabatan) REFERENCES
jabatan(idjabatan) ON DELETE CASCADE
Mengetes Foreign Key dengan Cascade Delete
DELETE jabatan WHERE idjabatan = 'KPL'
Menambah Foreign Key dengan Cascade Delete dan Cascade Update
ALTER TABLE dokter ADD CONSTRAINT fk_dokjab FOREIGN KEY (idjabatan) REFERENCES
jabatan(idjabatan) ON UPDATE CASCADE ON DELETE CASCADE
Mengetes Foreign Key dengan Cascade Delete dan Cascade Update
UPDATE jabatan SET idjabatan = 'KPL' WHERE idjabatan = 'KEP'
DELETE jabatan WHERE idjabatan = 'KPL'
Membuat Tabel Baru Untuk Mempraktekkan Gabungan Join 3 Tabel
CREATE TABLE spesialis(
idspesialis nvarchar(50) NOT NULL PRIMARY KEY,
namaspesialis nvarchar(200) NULL)
Menambahkan Kolom pada Tabel
ALTER TABLE dokter ADD idspesialis nvarchar(50) NULL
Gabungan Join 3 Tabel dengan Cara Sederhana
SELECT * FROM dokter, jabatan, spesialis WHERE dokter.idjabatan =
jabatan.idjabatan AND dokter.idspesialis = spesialis.idspesialis
Join Penggabungan 3 Tabel dengan JOIN
SELECT * FROM dokter JOIN jabatan ON dokter.idjabatan = jabatan.idjabatan JOIN
spesialis ON dokter.idspesialis = spesialis.idspesialis
Menghapus Kolom pada suatu Tabel
ALTER TABLE dokter DELETE COLUMN idspesialis
Mengubah Tipe data dari suatu Kolom pada Tabel
ALTER TABLE dokter ALTER COLUMN idspesialis nvarchar(50) NULL
Agregat Penjumlahan, Hitung Baris, Maksimum, Minimum, dan Rata-rata
SELECT SUM(tunjangan), MIN(tunjangan), MAX(tunjangan), COUNT(nip),
AVG(tunjangan) FROM dokter
Memberi Nama Kolom dengan Klausa AS
SELECT SUM(tunjangan) as total FROM dokter
Menjumlahkan Tunjangan Per Jabatan
SELECT idjabatan, SUM(tunjangan) as total FROM dokter GROUP BY idjabatan
Menampilkan Tunjangan dan Total Semua Tunjangan Dokter dengan SubQuery
SELECT nip, tunjangan, (SELECT SUM(tunjangan) as total FROM dokter WHERE
idjabatan = d.idjabatan) FROM dokter d
Menampilkan Tunjangan dan Persentase terhadap Total Semua Tunjangan Dokter
dengan SubQuery
SELECT nip, (tunjangan/(SELECT SUM(tunjangan) FROM dokter WHERE idjabatan =
d.idjabatan))*100 as persentase FROM dokter d
Menampilkan Dokter dengan Persentase terhadap Total Semua Tunjangan Dokter
yang lebih dari 30% per Jabatan dengan SubQuery
SELECT nip FROM dokter d WHERE (tunjangan/(SELECT SUM(tunjangan) FROM dokter
WHERE idjabatan = d.idjabatan))*100 > 30
Menampilkan Beberapa Dokter dengan Klausa IN
SELECT nip, nama FROM dokter d WHERE nip IN (‘001’,’002’,’003’)
Menampilkan Dokter dengan Tunjangan Terbesar dengan IN
SELECT nip, nama FROM dokter d WHERE nip IN (SELECT TOP 3 * FROM dokter ORDER
BY tunjangan DESC)
Daftar Semua Teman dokter sama spesialis dari 2 orang dokter spesialis yg
mempunyai tunjangan terbesar
SELECT nip, nama FROM dokter WHERE idspesialis IN (SELECT TOP 2 idspesialis
FROM dokter ORDER BY tunjangan DESC)
SELECT nip, nama, (tunjangan / (SELECT SUM(tunjangan) FROM dokter)) * 100 AS
persentase, tunjangan FROM dokter
SELECT nip, nama, (tunjangan / (SELECT SUM(tunjangan) FROM dokter)) * 100 AS
persentase, tunjangan FROM dokter WHERE ((tunjangan / (SELECT SUM(tunjangan)
FROM dokter)) * 100) > 30
Contoh lain SubQuery Berlapis-lapis
SELECT * FROM (SELECT nip, nama, (tunjangan/(SELECT SUM(tunjangan) FROM
dokter)) * 100 AS persentase, tunjangan FROM dokter) d WHERE d.persentase >
30
SELECT nip, nama, (tunjangan / (SELECT SUM(tunjangan) FROM dokter) WHERE
idspesialis = d.idspesialis) * 100 AS persentase, tunjangan FROM dokter d
Mengkopi suatu Tabel ke Tabel lain atau Memasukkan hasil Query menjadi
sebuat Tabel Temporary
SELECT *
INTO backupdokter
FROM dokter
SELECT * FROM backupdokter
DROP TABLE backupdokter
Membuat Tabel untuk Backup Data
CREATE TABLE backupdokter(
id int NOT NULL PRIMARY KEY IDENTITY,
waktu datetime NULL,
nip nvarchar(50) NULL,
nama nvarchar(200) NULL,
alamat text NULL,
tgllahir datetime NULL,
tunjangan float NULL)
Mengkopi Tabel ke Tabel Backup atau Memasukkan hasil sebuah Query ke Tabel
Backup
INSERT INTO backupdokter (waktu, nip, nama, alamat, tgllahir, tunjangan)
SELECT GETDATE(), nip, nama, alamat, tgllahir, tunjangan
FROM dokter
SELECT * FROM backupdokter
Membuat Stored Procedure
CREATE PROCEDURE sp_totaltunjangan
AS
SELECT SUM(tunjangan) AS total
FROM dokter
Menjalankan Stored Procedure
EXECUTE sp_totaltunjangan
Membuat Stored Procedure dengan Parameter Masukan
CREATE PROCEDURE sp_totaljabatan
@idjab nvarchar(50)
AS
SELECT SUM(tunjangan) AS total
FROM dokter
WHERE idjabatan = @idjab
Menjalankan Stored Procedure dengan Parameter Input
EXECUTE sp_totaljabatan 'KEP'
Membuat Stored Procedure dengan Parameter Input dan Output
CREATE PROCEDURE tertinggiterendah
@idjab varchar(10),
@tunjtertinggi float OUTPUT,
@tunjterendah float OUTPUT
AS
SELECT @tunjtertinggi=(SELECT MAX(tunjangan) FROM dokter WHERE idjabatan
= @idjab)
SELECT @tunjterendah=(SELECT MIN(tunjangan) FROM dokter WHERE idjabatan =
@idjab)
Menyiapkan Parameter dan Memanggil Stored Procedure yang Menggunakan
Parameter Input Output
----
DECLARE @idjabcari varchar(10);
DECLARE @hsltertinggi float;
DECLARE @hslterendah float;
SET @idjabcari='MAN'
EXECUTE tertinggiterendah @idjab=@idjabcari, @tunjtertinggi=@hsltertinggi
OUTPUT, @tunjterendah=@hslterendah OUTPUT
SELECT @hsltertinggi AS "tertinggi", @hslterendah AS
"terendah"
---
Menambah Kolom pada Tabel untuk mempraktekkan Trigger
ALTER TABLE jabatan ADD totaltunjangan float NULL;
Mengetes perintah SUM
SELECT SUM(tunjangan) FROM dokter WHERE idjabatan = ‘MAN’
Mengetes perintah SubQuery untuk Mengupdate Semua Baris dalam Suatu Tabel
UPDATE jabatan SET totaltunjangan = (SELECT SUM(tunjangan) FROM dokter d WHERE
d.idjabatan = jabatan.idjabatan)
SELECT * FROM jabatan
Membuat Trigger untuk Mengupdate Total Tunjangan ketika Ada Tunjangan
Diubah, Ditambah atau Dihapus
CREATE TRIGGER updatetotaltunjangan
ON dokter
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
UPDATE jabatan SET totaltunjangan = (SELECT SUM(tunjangan) FROM dokter d WHERE
d.idjabatan = jabatan.idjabatan)
END;
Mengubah kolom bertipe TEXT menjadi bertipe NVARCHAR agar bisa dipakai di
Trigger
ALTER TABLE dokter ALTER COLUMN alamat nvarchar(1000) NULL
ALTER TABLE backupdokter ALTER COLUMN alamat nvarchar(1000) NULL
Membuat Trigger untuk Membackup Data yang Dihapus (Deleted)
CREATE TRIGGER backupdelete
ON dokter
AFTER DELETE
AS
BEGIN
INSERT INTO backupdokter (waktu, nip, nama, alamat, tgllahir, tunjangan)
SELECT GETDATE(), nip, nama, alamat, tgllahir, tunjangan
FROM deleted;
END;
Mengetes Trigger untuk Membackup Data yang Dihapus (Deleted)
SELECT * FROM dokter;
DELETE FROM dokter WHERE nip='444';
SELECT * FROM backupdokter;
Mengembalikan Data Dokter yang Telah Dihapus
INSERT INTO dokter (nip, nama, alamat, tgllahir, tunjangan) SELECT TOP 1 nip,
nama, alamat, tgllahir, tunjangan FROM backupdokter WHERE nip='444' ORDER BY
waktu DESC
Membuat Trigger untuk Membackup Data yang Diedit (Updated)
CREATE TRIGGER backupupdate
ON dokter
AFTER UPDATE
AS
BEGIN
INSERT INTO backupdokter (waktu, nip, nama, alamat, tgllahir, tunjangan)
SELECT GETDATE(), nip, nama, alamat, tgllahir, tunjangan
FROM updated;
END;
Membuat Trigger untuk Membackup Data yang Ditambahkan (Inserted)
CREATE TRIGGER backupinsert
ON dokter
AFTER INSERT
AS
BEGIN
INSERT INTO backupdokter (waktu, nip, nama, alamat, tgllahir, tunjangan)
SELECT GETDATE(), nip, nama, alamat, tgllahir, tunjangan
FROM inserted;
END;
