Computer Science University of Brawijaya Putra Pandu Adikara

Computer Science, University of Brawijaya Putra Pandu Adikara, S. Kom Data Manipulation Language Basis Data 2

DML

Data Manipulation Language v Pada aplikasi database, konsep CRUD: § § Create Read (Retrieve) Update (Edit) Delete Insert Select Update Delete § sama seperti konsep pada programming untuk user interface, • Misalnya juga untuk user-permission level aplikasi (user-group/role)

Select v SELECT § Untuk mendapatkan baris-baris record dari suatu tabel atau beberapa tabel sekaligus dengan kondisi tertentu v SELECT-INTO digunakan untuk membuat tabel baru hasil dari query Select select * into Product. Temp from Products v UNION digunakan untuk menggabungkan 2 query dengan definisi kolom yang sama v DISTINCT digunakan untuk mencegah duplikasi sesuai kolom

Select v SELECT dengan kolom kalkulasi USE Adventure. Works 2008 R 2; GO SELECT p. Name AS Product. Name, Non. Discount. Sales = (Order. Qty * Unit. Price), Discounts = ((Order. Qty * Unit. Price) * Unit. Price. Discount) FROM Production. Product AS p INNER JOIN Sales. Order. Detail AS sod ON p. Product. ID = sod. Product. ID ORDER BY Product. Name DESC; GO

Select v SELECT dengan subquery USE Adventure. Works 2008 R 2; GO SELECT DISTINCT Name FROM Production. Product AS p WHERE EXISTS (SELECT * FROM Production. Product. Model AS pm WHERE p. Product. Model. ID = pm. Product. Model. ID AND pm. Name LIKE 'Long. Sleeve Logo Jersey%'); GO SELECT DISTINCT p. Last. Name, p. First. Name FROM Person AS p JOIN Human. Resources. Employee AS e ON e. Business. Entity. ID = p. Business. Entity. ID WHERE 5000. 00 IN (SELECT Bonus FROM Sales. Person AS sp WHERE e. Business. Entity. ID = sp. Business. Entity. ID); GO

Select v SELECT dengan menggunakan ROW_NUMBER (ISO SQL: 2003) SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY Product. ID ASC) AS row_number, Product. ID, Sale. Date, Sale. Price FROM Sales. History ) foo WHERE row_number <= 200 v Bisa digunakan sebagai limit

Select v Baca lebih lanjut: § http: //msdn. microsoft. com/en-us/library/ms 187731. aspx v Baca juga tentang JOIN

Insert v Insert § Untuk menambah baris pada suatu tabel v Menambahkan data langsung isinya (urut dengan kolomnya) § INSERT INTO table_name VALUES (value 1, value 2, value 3, . . . ) v Menambahkan data dimana isi sesuai kolomnya (bisa tidak urut dengan kolomnya, atau hanya kolom tertentu saja) § INSERT INTO table_name (column 1, column 2, column 3, . . . ) VALUES (value 1, value 2, value 3, . . . )

Insert v Menambahkan baris pada suatu tabel melalui seleksi INSERT california_authors (au_id, au_lname, au_fname) SELECT au_id, au_lname, au_fname FROM authors WHERE State = 'CA‘ v Baca lebih lanjut lagi: http: //msdn. microsoft. com/enus/library/aa 933206(SQL. 80). aspx

Delete v Delete § Digunakan untuk menghapus record atau himpunan record (sesuai kondisi yang ditentukan) USE Adventure. Works 2008 R 2; GO DELETE FROM Production. Product. Cost. History WHERE Standard. Cost > 1000. 00; GO

Delete: Subquery v SQL-2003 Standard subquery USE Adventure. Works 2008 R 2; GO DELETE FROM Sales. Person. Quota. History WHERE Business. Entity. ID IN (SELECT Business. Entity. ID FROM Sales. Person WHERE Sales. YTD > 2500000. 00); GO v Transact-SQL extension USE Adventure. Works 2008 R 2; GO DELETE FROM Sales. Person. Quota. History AS spqh INNER JOIN Sales. Person AS sp ON spqh. Business. Entity. ID = sp. Business. Entity. ID WHERE sp. Sales. YTD > 2500000. 00; GO

v Baca lebih lanjut § http: //msdn. microsoft. com/en-us/library/ms 189835. aspx

Update v Update § Digunakan untuk mengubah record atau himpunan record (sesuai kondisi yang ditentukan) UPDATE authors SET state = 'PC', city = 'Bay City' WHERE state = 'CA' AND city = 'Oakland'

v Baca lebih lanjut § http: //msdn. microsoft. com/enus/library/aa 260662(SQL. 80). aspx

Constraint

Konsep Dasar Constraint v Entity Integrity § memastikan tidak ada entitas yg sama (duplikasi record/row) dalam satu tabel • Primary Key constraint v Referential Integrity § Memastikan record tidak dapat dihapus, apabila digunakan di tabel lain • Foreign Key constraint § Entity dan Referential Integrity bersamaan membentuk key integrity

Konsep Dasar Constraint v Domain Integrity § memastikan isi dari suatu kolom terbatas sesuai tipe, format, dan rentang yang dimungkinkan • CHECK, UNIQUE, DEFAULT constraint • Contoh: – – Nama produk tidak boleh NULL. Nama produk harus unik (unique). Tanggal pemesanan tidak boleh hari esok. Jumlah pemesanan produk harus lebih dari nol (0). v User-Defined Integrity § Digunakan untuk business-rule yang tidak masuk di entity, domain, referential integrity

Referential Integrity (lanjutan) v Referential Integrity Constraints § Mendefinisikan suatu aksi yang dilakukan apabila user menghapus atau memperbaharui primary key (dalam relationship primary key-foreign key) § Digunakan untuk menjaga konsistensi baris-baris data antara dua tabel yang saling mempunyai relasi

Referential Integrity (lanjutan) v Referential Integrity Constraints § NO ACTION • aksi default bila tidak didefinisikan, bila ada penghapusan/ pengubahan maka akan dibatalkan dan error akan dibangkitkan § CASCADE • Semua baris foreign key akan dihapus sesuai baris primary key yang dihapus § SET NULL • Semua baris foreign key, nilainya akan berubah menjadi NULL. • Syaratnya kolom foreign key harus memperbolehkan nilai NULL § SET DEFAULT • Semua baris foreign key, nilainya akan berubah menjadi nilai default • Syaratnya kolom foreign key harus mempunyai nilai default

Contoh CREATE TABLE Products ( Product. ID TINYINT, Product. Description VARCHAR(100), CONSTRAINT pk_Product. ID PRIMARY KEY (Product. ID) ) CREATE TABLE Sales. History ( Sale. ID int IDENTITY(1, 1) NOT NULL, Product. ID TINYINT, Sale. Date datetime NULL, Sale. Price money NULL, CONSTRAINT pk_Sale. ID PRIMARY KEY (Sale. ID) CONSTRAINT fk_Sales. History. Product. ID FOREIGN KEY (Product. ID) REFERENCES Products(Product. ID) ON DELETE CASCADE ON UPDATE CASCADE )

Contoh INSERT INTO Products (Product. ID, Product. Description) SELECT 1, 'Big. Screen' UNION ALL SELECT 2, 'Computer' UNION ALL SELECT 3, 'Pool. Table' GO DECLARE @i SMALLINT SET @i = 1 WHILE (@i <=100) BEGIN INSERT INTO Sales. History(Product. ID, Sale. Date, Sale. Price) VALUES(1, DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57)) INSERT INTO Sales. History(Product. ID, Sale. Date, Sale. Price) VALUES(2, DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13)) INSERT INTO Sales. History(Product. ID, Sale. Date, Sale. Price) VALUES(3, DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29)) SET @i = @i + 1 END

Contoh v Apa yang terjadi bila pada tabel Products, baris dengan Product. ID=1 diganti menjadi Product. ID=4? v Apa yang terjadi bila pada tabel Products, baris dengan Product. ID=4 dihapus? v Bila ON DELETE CASCADE diubah menjadi ON DELETE SET NULL atau SET DEFAULT atau NO ACTION apa yang terjadi?

Domain Integrity: Unique Constraint v Unique constraint menggunakan index untuk memastikan suatu kolom (atau himpunan kolom) tidak mempunyai isi yang sama (terduplikasi) § Column constraint CREATE TABLE Products_2 ( Product. ID int PRIMARY KEY, Product. Name nvarchar (40) Constraint IX_Product. Name UNIQUE ) § Table constraint CREATE TABLE Products_2 ( Product. ID int PRIMARY KEY, Product. Name nvarchar (40), CONSTRAINT IX_Product. Name UNIQUE(Product. Name) )

Domain Integrity: Unique Constraint v Untuk menambahkan constraint menggunakan alter table: CREATE TABLE Products_2 ( Product. ID int PRIMARY KEY, Product. Name nvarchar (40) ) ALTER TABLE Products_2 ADD CONSTRAINT IX_Product. Name UNIQUE (Product. Name) v Contoh error ketika ada pelanggaran constraint Server: Msg 1505, Level 16, State 1, Line 1 CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 2. Most significant primary key is 'Hamburger'. Server: Msg 1750, Level 16, State 1, Line 1 Could not create constraint. See previous errors. The statement has been terminated.

Domain Integrity: Check Constraint v Check Constraint berisi ekspresi yang akan dievaluasi ketika menambah atau mengubah suatu record § Jika evaluasi ekspresi bernilai false, maka record tidak akan disimpan dalam database § Membuat check constraint sama seperti dlm membuat klausa WHERE § Dapat menggunakan operator (<, >, <=, >=, <>, =) serta BETWEEN, IN, LIKE, NULL dan kombinasi AND, OR v Terdiri dari dua bagian: § Name nama dari constraint § Predicate kondisi aktual yang digunakan dalam pengecekan

Domain Integrity: Check Constraint v Column constraint CREATE TABLE Products_2 ( Product. ID int PRIMARY KEY, Unit. Price money CHECK(Unit. Price > 0 AND Unit. Price < 100) ) v Table constraint CREATE TABLE Customers_2 ( Customer. ID int, Phone varchar(24), Fax varchar(24), CONSTRAINT CK_Phone. Or. Fax CHECK(Fax IS NOT NULL OR PHONE IS NOT NULL) )

Batasan Check Constraint v Ada batasan dalam check constraint: § Tidak bisa mereferensi/merujuk ke row yang berbeda dalam satu tabel § Tidak bisa mereferensi/merujuk ke kolom pada tabel yang berbeda

Domain Integrity: Default Constraint v Default constraint digunakan sebagai nilai default pada suatu kolom ketika dalam statement INSERT isi nilainya tidak ditetapkan CREATE TABLE Orders_2 ( Order. ID int IDENTITY NOT NULL , Employee. ID int NOT NULL , Order. Date datetime NULL DEFAULT(GETDATE()), Freight money NULL DEFAULT (0) CHECK(Freight >= 0), Ship. Address nvarchar (60) NULL DEFAULT('NO SHIPPING ADDRESS'), Entered. By nvarchar (60) NOT NULL DEFAULT(SUSER_SNAME()) ) INSERT INTO Orders_2 (Employee. ID, Freight) VALUES(1, NULL) ? ? ?