Tutorial SQL Server - 3. SQL Statement

SQL statement dapat kita kategorikan menjadi 4 bagian yaitu : .:Ref Link:.

1. DML (Data Manipulation Language)
Yakni statement sql yang digunakan untuk memanipulasi data didalam database. Contoh statementnya : INSERT, UPDATE, DELETE, SELECT
2. DDL (Data Definition Language)
Yakni statement sql yang digunakan untuk membuat, mengubah & menghapus objek (table, view, trigger, dll) didalam database. Contoh statementnya : CREATE, ALTER, DROP
3. DCL (Data Control Language)
Yakni statement sql yang digunakan untuk mengontrol hak akses dalam database. Contoh statementnya : GRANT, REVOKE
4. TCL (Transactional Control Language)
Yakni statement sql yang digunakan untuk mengelola transaksi yang terjadi dalam database. Contoh statementnya : COMMIT, ROLLBACK

Pada kesempatan ini, kita akan mempelajari bagian statement DML. Untuk statement DDL sudah kita pelajari pada saat kita membuat, mengubah dan menghapus database dan tabel. Sebelum lanjut mempelajari statement DML berikut, pastikan database inventory anda sudah ada dan jalankan syntax berikut ini.

USE inventory;
GO

CREATE TABLE t_produk (
 kd_brng VARCHAR(20) NOT NULL PRIMARY KEY,
 nm_brng VARCHAR(100) NOT NULL,
 sts_brng BIT NOT NULL DEFAULT 1,
 desc_brng VARCHAR(250)
);
GO

CREATE TABLE t_order_h (
 kd_order VARCHAR(20) NOT NULL PRIMARY KEY,
 tgl_order DATE NOT NULL,
 tgl_input DATETIME NOT NULL DEFAULT GETDATE()
);
GO

CREATE TABLE t_order_d (
 id_data BIGINT NOT NULL PRIMARY KEY IDENTITY(0,1),
 kd_order VARCHAR(20) NOT NULL,
 kd_brng VARCHAR(20) NOT NULL,
 qty_brng NUMERIC(10,2) NOT NULL DEFAULT 0,
 CONSTRAINT FK_kd_order_ON_t_order_d_TO_t_order_h FOREIGN KEY (kd_order)
 REFERENCES t_order_h(kd_order),
 CONSTRAINT FK_kd_brng_ON_t_order_d_TO_t_produk FOREIGN KEY (kd_brng)
 REFERENCES t_produk(kd_brng)
);
GO

DML : Insert

Insert statement merupakan perintah untuk menyimpan record baru ke dalam tabel. Berikut contoh penggunaannya disertai dengan komentar sebagai penjelasan syntax tersebut. .:Ref Link:.

USE inventory;
GO
-- menyimpan 1 data
INSERT INTO t_produk VALUES ('p001','produk a',1,'keterangan produk a')
-- menyimpan 1 data dengan urutan kolom yg tidak sesuai dgn tabel
INSERT INTO t_produk (nm_brng,sts_brng,kd_brng,desc_brng) VALUES ('produk b',1,'P002','keterangan produk b')
-- menyimpan 1 data dgn kolom desc_brng tidak diisi krn allow null=true
INSERT INTO t_produk (kd_brng,nm_brng,sts_brng) VALUES ('p003','produk c',1)
-- menyimpan 1 data dgn kolom sts_brng bernilai false (hanya boleh true or false)
INSERT INTO t_produk (kd_brng,nm_brng,sts_brng) VALUES ('p004','produk d',0)
-- menyimpan 1 data dgn kolom sts_brng diisi otomatis dgn nilai default
INSERT INTO t_produk (kd_brng,nm_brng) VALUES ('p005','produk e')
-- menyimpan banyak data
INSERT INTO t_produk (kd_brng,nm_brng) VALUES ('p011','produk aa'),('p012','produk bb'),('p013','produk cc')
-- menyimpan banyak data dgn kolom bertipe date (tanggal)
INSERT INTO t_order_h (kd_order,tgl_order) VALUES ('or001','2013-01-02'),('or002','2013-07-22')
-- menyimpan banyak data dgn kolom bertipe angka dan bersifat auto increament
INSERT INTO t_order_d (kd_order,kd_brng) VALUES ('or001','p001'),('or001','p002'),('or001','p003')
-- menyimpan banyak data dgn kolom bertipe angka (numerik)
INSERT INTO t_order_d (kd_order,kd_brng,qty_brng) 
VALUES ('or002','p001',10),('or002','p002',20.03),('or002','p003',-123),('or002','p004',-123.21)

DML : Update

Update statement merupakan perintah untuk mengubah data dalam tabel. Secara sederhana, statement dari SQL update adalah sebagai berikut. .:Ref Link:.

UPDATE < nama tabel >
SET < nama kolom ditabel = nilai baru >
WHERE < kriteria pengupdatan >

Berikut contoh penggunaannya :

USE inventory;
GO
-- update terhadap 1 kolom dalam 1 record dengan 1 kriteria
UPDATE t_produk
SET nm_brng='produk a new'
WHERE kd_brng='p001';
GO
-- update terhadap bnyk kolom dalam 1 record dengan 1 kriteria
UPDATE t_produk
SET nm_brng='produk a new',sts_brng=0,desc_brng='keterangan produk a new'
WHERE kd_brng='p001';
GO
-- update terhadap 1 kolom dalam bnyk record dengan 1 kriteria
UPDATE t_produk
SET nm_brng += ' zzz'
WHERE kd_brng LIKE 'p%1';
GO
-- update terhadap bnyk kolom dalam bnyk record dengan 1 kriteria
UPDATE t_produk
SET nm_brng ='zzz',sts_brng=1
WHERE kd_brng LIKE 'p%1';
GO
-- update terhadap bnyk kolom dalam bnyk record dengan bnyk kriteria
UPDATE t_produk
SET nm_brng ='test multi kriteria',sts_brng=1
WHERE desc_brng IS NULL AND sts_brng=1;
GO

DML : Delete

Delete statement merupakan perintah untuk menghapus data dalam tabel. Secara sederhana, statement dari SQL delete adalah sebagai berikut. .:Ref Link:.

DELETE FROM < nama tabel >
WHERE < kriteria penghapusan >

Berikut contoh penggunaannya :

USE inventory;
GO
-- delete data dengan 1 kriteria
DELETE FROM t_produk
WHERE kd_brng='p005'
GO
-- delete data dengan multi kriteria
DELETE FROM t_order_d
WHERE kd_order LIKE '%2' AND qty_brng < 0
GO
-- delete seluruh data di table
DELETE FROM t_order_d
GO

DML : Select

Select statement merupakan perintah untuk mengambil data dalam tabel. Secara sederhana, statement dari SQL select adalah sebagai berikut. .:Ref Link:.

SELECT < nama kolom >
FROM < nama tabel >
[WHERE < kriteria pengambilan data >]
[GROUP BY < nama kolom >]
[HAVING < kriteria filter >]
[ORDER BY < nama kolom >]

Berikut contoh penggunaannya :

USE inventory;
GO
-- mengambil seluruh data dalam 1 table
SELECT * FROM t_produk
-- mengambil seluruh data dengan pengurutan [ASC atau DESC]
SELECT * FROM t_produk ORDER BY kd_brng DESC
-- mengambil kolom-kolom tertentu
SELECT kd_brng, nm_brng FROM t_produk
-- mengambil beberapa data utama (dalam contoh ini adalah 3)
SELECT TOP 3 * FROM t_produk ORDER BY kd_brng ASC --seluruh kolom
SELECT TOP 3 kd_brng,nm_brng FROM t_produk ORDER BY kd_brng ASC --beberapa kolom
-- mengambil data dengan menggabungkan data-data yang sama
SELECT nm_brng FROM t_produk --sebelum digabung
SELECT DISTINCT nm_brng FROM t_produk --setelah digabung
-- mengambil seluruh data dengan kriteria filter
SELECT * FROM t_produk WHERE kd_brng LIKE 'p0[1]%' --kriteria filter bertipe string
SELECT * FROM t_produk WHERE sts_brng = 0 --kriteria filter bertipe boolean
SELECT * FROM t_order_h WHERE tgl_order > '07/01/2013' --kriteria filter bertipe date
SELECT * FROM t_order_d WHERE qty_brng > 0 --kriteria filter bertipe numerik 

Untuk penggunaan keyword like, ada beberapa patern yang bisa kita gunakan. Berikut paternnya. .:Ref Link:.

Wildcard character	Description	Example
%	Any string of zero or more characters.	WHERE title LIKE '%computer%' finds all book titles with the word 'computer' anywhere in the book title.
_ (underscore)	Any single character.	WHERE au_fname LIKE '_ean' finds all four-letter first names that end with ean (Dean, Sean, and so on).
[ ]	Any single character within the specified range ([a-f]) or set ([abcdef]).	WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen and starting with any single character between C and P, for example Carsen, Larsen, Karsen, and so on. In range searches, the characters included in the range may vary depending on the sorting rules of the collation.
[^]	Any single character not within the specified range ([^a-f]) or set ([^abcdef]).	WHERE au_lname LIKE 'de[^l]%' all author last names starting with de and where the following letter is not l.

Untuk mengambil data dari beberapa tabel, gunakan keyword Inner Join, Left Join atau Right Join. Berikut contoh syntax-nya :

-- menggunakan inner join
SELECT t_order_d.kd_brng, t_produk.nm_brng, t_order_d.qty_brng
FROM t_order_d INNER JOIN t_produk 
ON t_order_d.kd_brng=t_produk.kd_brng
-- menggunakan left join
SELECT t_order_d.kd_brng, t_produk.nm_brng, t_order_d.qty_brng
FROM t_order_d LEFT JOIN t_produk 
ON t_order_d.kd_brng=t_produk.kd_brng
-- menggunakan right join
SELECT t_order_d.kd_brng, t_produk.nm_brng, t_order_d.qty_brng
FROM t_order_d RIGHT JOIN t_produk 
ON t_order_d.kd_brng=t_produk.kd_brng
-- untuk menyingkat penulisan & menggunakan alias
SELECT a.kd_brng AS [Kode Barang], b.nm_brng AS [Nama Barang], a.qty_brng AS [Qty Barang]
FROM t_order_d a INNER JOIN t_produk b 
ON a.kd_brng=b.kd_brng

Berikut ini adalah syntax yang digunakan untuk mengecek object didalam database.

-- untuk mengecek database
USE master;
GO
IF EXISTS (SELECT * FROM SYSDATABASES WHERE name='inventory')
PRINT 'Database Inventory Ada'
ELSE
PRINT 'Database Inventory Tidak Ada';
GO

-- untuk mengecek table
USE inventory;
GO
IF EXISTS (
    SELECT * FROM sys.objects WHERE object_id = object_id(N't_produk')
    AND
    type in (N'U')
)
PRINT 'Tabel Produk Ada'
ELSE
PRINT 'Tabel Produk Tidak Ada';
GO
-- atau cara lain
IF EXISTS (SELECT * FROM SYS.TABLES WHERE NAME = N't_produk')
PRINT 'Tabel Produk Ada'
ELSE
PRINT 'Table Produk Tidak Ada';
GO

-- untuk mengecek foreign key
USE inventory;
GO
IF EXISTS (
    SELECT * FROM sys.objects WHERE object_id = object_id(N'FK_kd_brng_ON_t_order_d_TO_t_produk')
    AND
    type in (N'F')
)
PRINT 'Foreign Key Kode Barang di Tabel Order Detail ke Tabel Produk Ada'
ELSE
PRINT 'Foreign Key Kode Barang di Tabel Order Detail ke Tabel Produk Tidak Ada';
GO

/*
Untuk referensi type object_id lainnya dlm SQL, silahkan kunjungi link berikut :
http://msdn.microsoft.com/en-us/library/ms177596.aspx
*/