Perintah SQL Database SQL Server (Administrasi Database)

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' Menghapus Foreign Key ALTER TABLE dokter DROP CONSTRAINT fk_dokjab 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; Semoga bermanfaat.