Microsoft SQL Server DDL dan DML dasar

1. Pertemuan 1 Microsoft SQL ServerDDL dan DML dasar

2. Istilah • SQL • Structured Query Language • MSSQL • Microsoft SQL Server

3. Metadata dan data • Metadata • Data tentang data. Berisi informasi mengenai struktur data. • Data • Data. Data mentah yang dapat diolah menjadi informasi.

4. DDL, DML, DCL • DDL (Data Definition Language) adalah bahasa SQL yang mendefinisikan data. • DML (Data Manipulation Language) adalah bahasa SQL untuk mengelola data. • DCL (Data Control Language) adalah bahasa SQL untuk mengontrol pemakai data.

5. Kebiasaan yang membantu • Jangan lupakan bahwa setiap syntax SQL selalu diakhiri dengan tanda titik koma. • Selalu ketikkan keyword SQL dalam huruf besar, dan non-keyword dalam huruf kecil. Contoh: • CREATE DATABASE kantor; • USE kantor; • CREATE TABLE pegawai { id INTEGER PRIMARY KEY, nama VARCHAR(100) NULL, tglLahir DATE NULL }; • SELECT * FROM pegawai;

6. Data Definition Language (DDL) • Adalah bahasa SQL yang mendefinisikan data. Antara lain terdiri dari: • CREATE TABLE untuk membuat tabel. • ALTER TABLE untuk mengubah tabel. • DROP TABLE untuk membuang tabel.

7. Syntax CREATE TABLE (dasar) CREATE TABLE namaTabel ( fieldPK tipeFieldPK PRIMARY KEY, field1 tipeField2 NULL/NOT NULL, field2 tipeField3 NULL/NOT NULL ); • Catatan: • Jika tabel tidak memiliki primary key, maka fieldPK tidak perlu disertakan. • NULL berarti boleh bernilai NULL (kosong). NOT NULL sebaliknya.

8. Syntax CREATE TABLE (dasar) CREATE TABLE namaTabel ( fieldPK tipeFieldPK NOT NULL, field1 tipeField2 NULL/NOT NULL, field2 tipeField3 NULL/NOT NULL, CONSTRAINT namaCons PRIMARY KEY (fieldPK) ); • Catatan: • CONSTRAINT dapat digunakan untuk menentukan Primary Key atau Foreign Key.

9. Praktek • Buka MSSQL Management Studio • Koneksi ke database (lokal) • Buka query baru: • Klik toolbar “New Query”, atau • File > New > Query with Current Connection • Ketikkan: • CREATE DATABASE kantor; • USE kantor; • Tekan F5

10. Praktek • Ketikkan: CREATE TABLE pegawai ( id INT NOT NULL, nama VARCHAR (100) NULL, tglLahir DATETIME NULL, CONSTRAINT pk_pegawai PRIMARY KEY (id) ); • Blok kode SQL di atas dan jalankan (F5)

11. Praktek • Pastikan keberhasilan Anda: • Cek apakah tampil “Command(s) completed successfully” • Ketikkan dan jalankan perintah: • SELECT * FROM pegawai; • Jika muncul tulisan id, nama, tglLahir di tab results di bagian bawah kode, maka sudah berhasil.

12. Syntax ALTER TABLE • Untuk menambah kolom • ALTER TABLE namaTabel ADD namaKolom tipeData; • Untuk membuang kolom • ALTER TABLE namaTabel DROP namaKolom; • Untuk mengubah tipe kolom • ALTER TABLE namaTabel ALTER COLUMN namaKolom tipeData;

13. Syntax DROP TABLE • DROP TABLE namaTabel;

14. Membuat FOREIGN KEY • Membuat relationship / hubungan antar tabel, dengan keyword CONSTRAINT: CREATE TABLE namaTabel ( fieldPK tipeData, field1 tipeData DEFAULT nilai, field2 tipeData, CONSTRAINT namaCons PRIMARY KEY (fieldPK), CONSTRAINT namaCons FOREIGN KEY fieldFK REFERENCES tabelRujukan(PKRujukan) );

15. PRAKTEK • Ketikkan dan eksekusi perintah SQL berikut: • CREATE TABLE penggajian ( • id INT, • idPegawai INT NOT NULL, • bulan DATETIME NOT NULL, • nominal INT DEFAULT 0, • CONSTRAINT pk_gaji PRIMARY KEY (id), • CONSTRAINT fk_gajiPegawai FOREIGN KEY (idPegawai) REFERENCES pegawai(id) • );

16. CATATAN • Baris CONSTRAINT tidak harus ada pada suatu tabel. Bisa saja suatu tabel tidak memiliki PRIMARY KEY maupun FOREIGN KEY. • Bisa juga suatu tabel memiliki dua FOREIGN KEY tanpa PRIMARY KEY • Suatu tabel juga bisa memiliki FOREIGN KEY yang merujuk pada dirinya sendiri.

17. PENGINGAT • PRIMARY KEY: Merupakan field kunci yang mewakili field-field lain dalam tabel tersebut. Bersifat unik: tidak boleh ada nilai PRIMARY KEY yang sama dalam satu tabel. • PRIMARY KEY dapat berjumlah 0, 1, 2, 3 atau berapa saja.

18. PENGINGAT • FOREIGN KEY merupakan suatu field yang merujuk pada nilai pada tabel lain. Misalkan pada tabel penggajian terdapat FK idPegawai, nilai yang disimpan pada field ini cukup PK dari tabel pegawai saja. Field lain dalam tabel pegawai, misalkan nama, tglLahir, dan alamat, dapat diambil dengan menggunakan JOIN. • Field rujukan harus memiliki tipe yang sama dengan field yang dirujuk.

19. Membuat field UNIQUE CREATE TABLE login ( fieldPK tipeData PRIMARY KEY, field1 tipeData UNIQUE, field2 tipeData ); • Atau CREATE TABLE login ( fieldPK INT, field1 tipeData, field2 tipeData, CONSTRAINT namaCons PRIMARY KEY (fieldPK), CONSTRAINT namaCons UNIQUE (fieldUnique) );

20. PRAKTEK CREATE TABLE login ( id INT PRIMARY KEY, username VARCHAR(30) UNIQUE, password VARCHAR(50) ); • Atau CREATE TABLE login ( id INT, username VARCHAR(30), password VARCHAR(50), CONSTRAINT pk_login PRIMARY KEY (id), CONSTRAINT uq_login UNIQUE (username) );

21. CATATAN • Field UNIQUE merupakan field yang tidak boleh memiliki nilai yang sama. • Field UNIQUE dapat juga digunakan sebagai FOREIGN KEY.

22. IDENTITY • IDENTITY merupakan field yang otomatis bertambah setiap kali suatu record pada database ditambahkan. • Field dengan IDENTITY biasanya adalah field PRIMARY KEY. • Untuk mendefinisikan field IDENTITY, saat membuat tabel, tambahkan keyword IDENTITY pada field yang diinginkan.

23. PRAKTEK DROP TABLE login; CREATE TABLE login ( id INT PRIMARY KEY IDENTITY, username VARCHAR(30), password VARCHAR(50) );

24. CATATAN • Field dengan IDENTITY harus berupa INT • Tanpa parameter, penomoran field IDENTITY akan dimulai dari 1 dan bertambah 1. • IDENTITY(10,5) berarti penomoran dimulai dari angka 10, dan bertambah 5 setiap recordnya.

25. Data Manipulation Language • Adalah bahasa SQL yang digunakan untuk mengelola data. Yang termasuk DML antara lain: • SELECT untuk menampilkan data • INSERT untuk menambahkan data • UPDATE untuk mengubah data • DELETE untuk menghapus data

26. SELECT • SELECT merupakan perintah SQL yang paling sering dipakai, dan sekaligus paling kompleks. Topik terkait dengan SELECT: • Memilih field, keyword AS • WHERE, AND, OR, LIKE, IN, BETWEEN • JOIN (LEFT, RIGHT, FULL) • TOP dan DISTINCT • ORDER BY • COUNT, SUM, GROUP BY dan HAVING • UNION

27. SELECT • Menampilkan semua field: • SELECT * FROM pegawai; • Menampilkan field nama dan tglLahir saja • SELECT nama, tglLahir FROM pegawai; • Menampilkan field dengan alias • SELECT id AS kode, nama AS sebutan

28. SELECT • Menyaring data (semua diawali SELECT * FROM pegawai): • Menyaring tanggal lahir: • WHERE tglLahir > '2000-01-01'; • WHERE YEAR(tglLahir) > 2000; • Menyaring nama • WHERE nama <> 'Fandi'; • Digabung: • WHERE nama = 'Fandi' AND YEAR(tglLahir)>1800;

29. SELECT • Menyaring data (semua diawali SELECT * FROM pegawai): • Menyaring tanggal lahir: • WHERE YEAR(tglLahir)<1900 OR YEAR(tglLahir)>2000; • WHERE YEAR(tglLahir) NOT BETWEEN 1900 AND 2000; • Semua yang namanya ada huruf f: • WHERE nama LIKE '%f%';

30. INSERT • Lengkap dengan nama field: • INSERT INTO pegawai (id, nama, tglLahir) VALUES (1, 'Fandi', '2000-12-20'); • Disingkat, dengan syarat semua field harus diisi dan sesuai urutan: • INSERT INTO pegawai VALUES (1, 'Fandi', '2000-12-20'); • Hati-hati: PRIMARY KEY CONSTRAINT dan UNIQUE

31. UPDATE • Mengubah nama si Fandi: • UPDATE pegawai SET nama='Fandi Susanto' WHERE id=1; • Mengubah nama dan tglLahir sekaligus: • UPDATE pegawai SET nama='Fandi Susanto S.Si.', tglLahir='1900-01-01' WHERE id=1; • Pada kebanyakan kasus, kondisi dalam WHERE selalu berhubungan dengan PRIMARY KEY.

32. DELETE • Menghapus data juga biasanya menggunakan PRIMARY KEY sebagai kondisi di dalam WHERE. • DELETE FROM pegawai WHERE id=1;

33. DEMIKIANLAH • Terima kasih atas perhatiannya • Harap dikuasai: • CREATE DATABASE, USE DATABASE • CREATE TABLE • DROP TABLE • SELECT (Yang diformat bold) • INSERT, UPDATE, DELETE

35. SELECT • Menyaring data (semua diawali SELECT * FROM pegawai): • Semua yang namanya disebutkan: • WHERE nama IN ('Fandi', 'Andi', 'Budi');

36. SELECT • Menampilkan pembayaran gaji si Fandi: • SELECT * FROM pegawai, penggajian WHERE pegawai.id = penggajian.idPegawai AND pegawai.nama = 'Fandi'; • SELECT * FROM pegawai AS p, penggajian AS g WHERE p.id=g.idPegawai AND p.nama = 'Fandi'; • SELECT * FROM pegawai p, penggajian g WHERE p.id=g.idPegawai AND p.nama = 'Fandi';

37. SELECT • Menampilkan pembayaran gaji si Fandi dengan INNER JOIN: • SELECT * FROM pegawai p INNER JOIN penggajian g ON p.id=g.idPegawai WHERE p.nama = 'Fandi';

38. SELECT • Menampilkan pembayaran gaji semua orang, diurut berdasarkan nama dan bulan (diurut dari bulan terbaru): • SELECT p.nama, g.bulan, g.nominal FROM pegawai p INNER JOIN penggajian g ON p.id=g.idPegawai ORDER BY nama ASC, bulan DESC;

39. Tentang JOIN • INNER JOIN: • Data ada di tabel kiri dan kanan sekaligus. • LEFT JOIN: • Data yang ada di tabel kiri tampil semua meski di tabel kanan tidak ada data. • RIGHT JOIN: • Data yang ada di tabel kanan tampil semua meski di tabel kiri tidak ada data. • FULL JOIN: • Data tabel kiri dan kanan tampil semua.

40. TOP dan DISTINCT • Menampilkan dua pembayaran gaji terakhir saja • SELECT TOP 2 * FROM penggajian; • Menampilkan tahun-tahun yang berbeda dari tglLahir: • SELECT DISTINCT YEAR(tglLahir) FROM pegawai;

41. COUNT • Menampilkan banyak orang yang belakangnya berakhir dengan 'di': • SELECT COUNT(nama) AS jmlOrang FROM pegawai WHERE nama LIKE '%di';

42. SUM • Menampilkan total gaji si Fandi di tahun 2011: • SELECT SUM(g.nominal) AS totalGaji FROM pegawai p INNER JOIN penggajian g ON p.id=g.idPegawai WHERE p.nama='Fandi' AND YEAR(g.bulan) = 2011;

43. GROUP BY • Menampilkan total gaji para pegawai di tahun 2011: • SELECT p.nama, SUM(g.nominal) AS totalGaji FROM pegawai p INNER JOIN penggajian g ON p.id=g.idPegawai AND YEAR(g.bulan) = 2011 GROUP BY p.nama;

44. HAVING • Menampilkan total gaji para pegawai di tahun 2011: • SELECT p.nama, SUM(g.nominal) AS totalGaji FROM pegawai p INNER JOIN penggajian g ON p.id=g.idPegawai AND YEAR(g.bulan) = 2011 GROUP BY p.nama HAVING SUM(g.nominal) >=10000000;

45. UNION • Jarang digunakan. UNION digunakan untuk menggabungkan hasil dari dua buah SELECT. Hasil tipe data kedua SELECT harus sama. Contoh: • SELECT * FROM pegawai UNION SELECT 1, 'BOSS', '2012-12-12';