Data Manipulation Language

1. Data Manipulation Language Basis Data 2

2. DML

3. Data Manipulation Language • Padaaplikasi database, konsep CRUD: • Create  Insert • Read (Retrieve)  Select • Update (Edit)  Update • Delete  Delete • samasepertikonseppada programming untuk user interface, • Misalnyajugauntuk user-permission level aplikasi (user-group/role)

4. Select • SELECT • Untukmendapatkanbaris-baris record darisuatutabelataubeberapatabelsekaligusdengankondisitertentu • SELECT-INTO digunakanuntukmembuattabelbaruhasildari query Select select * into ProductTemp from Products • UNION digunakanuntukmenggabungkan 2 query dengandefinisikolom yang sama • DISTINCT digunakanuntukmencegahduplikasisesuaikolom

5. Select • SELECT dengankolomkalkulasi USE AdventureWorks2008R2; GO SELECT p.Name AS ProductName, NonDiscountSales= (OrderQty * UnitPrice), Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount) FROM Production.Product AS p INNER JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID ORDER BY ProductName DESC; GO

6. Select • SELECT dengansubquery USE AdventureWorks2008R2; GO SELECT DISTINCT Name FROM Production.Product AS p WHERE EXISTS (SELECT * FROM Production.ProductModel AS pm WHERE p.ProductModelID = pm.ProductModelID AND pm.Name LIKE 'Long-Sleeve Logo Jersey%'); GO SELECT DISTINCT p.LastName, p.FirstNameFROM Person.Person AS p JOIN HumanResources.Employee AS e ON e.BusinessEntityID = p.BusinessEntityID WHERE 5000.00 IN (SELECT Bonus FROM Sales.SalesPerson AS sp WHERE e.BusinessEntityID = sp.BusinessEntityID); GO

7. Select • SELECT denganmenggunakan ROW_NUMBER (ISO SQL:2003) SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY ProductID ASC) AS row_number, ProductID,SaleDate,SalePrice FROM SalesHistory ) foo WHERE row_number <= 200 • Bisadigunakansebagailimit

8. Select • Baca lebihlanjut: • http://msdn.microsoft.com/en-us/library/ms187731.aspx • Baca jugatentang JOIN

9. Insert • Insert • Untukmenambahbarispadasuatutabel • Menambahkan data langsungisinya (urutdengankolomnya) • INSERT INTO table_nameVALUES (value1, value2, value3,...) • Menambahkan data dimanaisisesuaikolomnya (bisatidakurutdengankolomnya, atauhanyakolomtertentusaja) • INSERT INTO table_name (column1, column2, column3,...)VALUES (value1, value2, value3,...)

10. Insert • Menambahkanbarispadasuatutabelmelaluiseleksi INSERT california_authors (au_id, au_lname, au_fname) SELECT au_id, au_lname, au_fname FROM authors WHERE State = 'CA‘ • Baca lebihlanjutlagi: http://msdn.microsoft.com/en-us/library/aa933206(SQL.80).aspx

11. Delete • Delete • Digunakanuntukmenghapus record atauhimpunan record (sesuaikondisi yang ditentukan) USE AdventureWorks2008R2; GO DELETE FROM Production.ProductCostHistory WHERE StandardCost > 1000.00; GO

12. Delete: Subquery • SQL-2003 Standard subquery USE AdventureWorks2008R2; GO DELETE FROM Sales.SalesPersonQuotaHistory WHERE BusinessEntityID IN (SELECT BusinessEntityID FROM Sales.SalesPerson WHERE SalesYTD > 2500000.00); GO • Transact-SQL extension USE AdventureWorks2008R2; GO DELETE FROM Sales.SalesPersonQuotaHistory FROM Sales.SalesPersonQuotaHistory AS spqh INNER JOIN Sales.SalesPerson AS spON spqh.BusinessEntityID = sp.BusinessEntityID WHERE sp.SalesYTD > 2500000.00; GO

13. Baca lebihlanjut • http://msdn.microsoft.com/en-us/library/ms189835.aspx

14. Update • Update • Digunakanuntukmengubah record atauhimpunan record (sesuaikondisi yang ditentukan) UPDATE authors SET state = 'PC', city = 'Bay City' WHERE state = 'CA' AND city = 'Oakland'

15. Baca lebihlanjut • http://msdn.microsoft.com/en-us/library/aa260662(SQL.80).aspx

16. Constraint

17. KonsepDasar Constraint • Entity Integrity • memastikantidakadaentitasygsama (duplikasi record/row) dalamsatutabel • Primary Key constraint • Referential Integrity • Memastikan record tidakdapatdihapus, apabiladigunakan di tabel lain • Foreign Key constraint • Entity dan Referential Integrity bersamaanmembentuk key integrity

18. KonsepDasar Constraint • Domain Integrity • memastikanisidarisuatukolomterbatassesuaitipe, format, danrentang yang dimungkinkan • CHECK, UNIQUE, DEFAULT constraint • Contoh: • NamaproduktidakbolehNULL. • Namaprodukharusunik (unique). • Tanggalpemesanantidakbolehhariesok. • Jumlahpemesananprodukharuslebihdarinol (0). • User-Defined Integrity • Digunakanuntukbusiness-rule yang tidakmasuk di entity, domain, referential integrity

19. Referential Integrity (lanjutan) • Referential Integrity Constraints • Mendefinisikansuatuaksi yang dilakukanapabila user menghapusataumemperbaharui primary key (dalam relationship primary key-foreign key) • Digunakanuntukmenjagakonsistensibaris-baris data antaraduatabel yang salingmempunyairelasi

20. Referential Integrity (lanjutan) • Referential Integrity Constraints • NO ACTION • aksi default bilatidakdidefinisikan, bilaadapenghapusan/ pengubahanmakaakandibatalkandan error akandibangkitkan • CASCADE • Semuabaris foreign key akandihapussesuaibaris primary key yang dihapus • SET NULL • Semuabaris foreign key, nilainyaakanberubahmenjadiNULL. • Syaratnyakolom foreign key harusmemperbolehkannilai NULL • SET DEFAULT • Semuabaris foreign key, nilainyaakanberubahmenjadinilai default • Syaratnyakolom foreign key harusmempunyainilai default

21. Contoh CREATE TABLE Products ( ProductID TINYINT, ProductDescription VARCHAR(100), CONSTRAINTpk_ProductIDPRIMARY KEY (ProductID) ) CREATE TABLESalesHistory ( SaleIDint IDENTITY(1,1) NOT NULL, ProductID TINYINT, SaleDatedatetime NULL, SalePrice money NULL, CONSTRAINTpk_SaleID PRIMARY KEY (SaleID) CONSTRAINTfk_SalesHistoryProductIDFOREIGN KEY (ProductID) REFERENCES Products(ProductID) ON DELETE CASCADE ON UPDATE CASCADE )

22. Contoh INSERT INTO Products (ProductID, ProductDescription) SELECT 1, 'BigScreen' UNION ALL SELECT 2, 'Computer' UNION ALL SELECT 3, 'PoolTable' GO DECLARE @i SMALLINT SET @i = 1 WHILE (@i <=100) BEGIN INSERT INTO SalesHistory(ProductID, SaleDate, SalePrice) VALUES(1, DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57)) INSERT INTO SalesHistory(ProductID, SaleDate, SalePrice) VALUES(2, DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13)) INSERT INTO SalesHistory(ProductID, SaleDate, SalePrice) VALUES(3, DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29)) SET @i = @i + 1 END

23. Contoh • Apa yang terjadibilapadatabel Products, barisdenganProductID=1 digantimenjadiProductID=4? • Apa yang terjadibilapadatabel Products, barisdenganProductID=4 dihapus? • Bila ON DELETE CASCADE diubahmenjadi ON DELETE SET NULL atau SET DEFAULT atau NO ACTION apa yang terjadi?

24. Domain Integrity: Unique Constraint • Unique constraint menggunakan index untukmemastikansuatukolom (atauhimpunankolom) tidakmempunyaiisi yang sama (terduplikasi) • Column constraint CREATETABLE Products_2 ( ProductIDintPRIMARY KEY, ProductNamenvarchar (40) ConstraintIX_ProductNameUNIQUE ) • Table constraint CREATETABLE Products_2 ( ProductIDintPRIMARY KEY, ProductNamenvarchar (40), CONSTRAINTIX_ProductName UNIQUE(ProductName) )

25. Domain Integrity: Unique Constraint • Untukmenambahkanconstraint menggunakan alter table: CREATETABLE Products_2 ( ProductIDintPRIMARY KEY, ProductNamenvarchar (40) ) ALTERTABLEProducts_2 ADDCONSTRAINTIX_ProductNameUNIQUE (ProductName) • Contoh error ketikaadapelanggaran 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.

26. Domain Integrity: Check Constraint • Check Constraint berisiekspresi yang akandievaluasiketikamenambahataumengubahsuatu record • Jikaevaluasiekspresibernilai false, maka record tidakakandisimpandalam database • Membuat check constraint samasepertidlmmembuatklausa WHERE • Dapatmenggunakan operator (<,>,<=,>=,<>,=) serta BETWEEN, IN, LIKE, NULL dankombinasi AND, OR • Terdiridariduabagian: • Name  namadari constraint • Predicate  kondisiaktual yang digunakandalampengecekan

27. Domain Integrity: Check Constraint • Column constraint CREATETABLE Products_2 ( ProductIDintPRIMARY KEY, UnitPrice money CHECK(UnitPrice > 0 AND UnitPrice < 100) ) • Table constraint CREATE TABLE Customers_2 ( CustomerIDint, Phone varchar(24), Fax varchar(24), CONSTRAINTCK_PhoneOrFaxCHECK(Fax IS NOT NULL OR PHONE IS NOT NULL) )

28. Batasan Check Constraint • Ada batasandalam check constraint: • Tidakbisamereferensi/merujukke row yang berbedadalamsatutabel • Tidakbisamereferensi/merujukkekolompadatabel yang berbeda

29. Domain Integrity: Default Constraint • Default constraint digunakansebagainilai default padasuatukolomketikadalam statement INSERT isinilainyatidakditetapkan CREATE TABLE Orders_2 ( OrderIDintIDENTITY NOT NULL , EmployeeIDintNOT NULL , OrderDatedatetimeNULLDEFAULT(GETDATE()), Freight money NULL DEFAULT (0) CHECK(Freight >= 0), ShipAddressnvarchar (60) NULL DEFAULT('NO SHIPPING ADDRESS'), EnteredBynvarchar (60) NOT NULL DEFAULT(SUSER_SNAME()) ) INSERT INTO Orders_2 (EmployeeID, Freight) VALUES(1, NULL) ???