Modul Vb

1 Tutorial SQL Muhammad Wali Tutorial SQL Muhammad Wali waly147258369@gmail.com Pembahasan dalam buku ini mencakup beberapa Latihan dengan menggunakan SQL Server 2000. Latihan 3 Jalankan SQL server 2000 dengan cara memilih menu Start | All Programs | Microsoft SQL Server | Query Analizer Modul dapat digunakan, dimodifikasi dan disebarkan secara bebas untuk tujuan bukan komersial (nonprofit), dengan syarat tidak menghapus atau merubah atribut penulis dan pernyataan copyright yang disertakan dalam setiap dokumen. Tidak diperbolehkan melakukan penulisan ulang, kecuali mendapatkan ijin terlebih dahulu dari Penulis Bab 3 2 Tutorial SQL Muhammad Wali Sehingga akan muncul tampilan sebagai berikut ; Selanjutnya klik OK 3 Tutorial SQL Muhammad Wali Buat Database dengan nama “Latihan 3” Selanjtnya ketikkan sintaks berikut untuk membuat tabel create table ms_student( Student_id char(5) primary key not null, Student_name varchar (30), Student_address varchar (50), Student_phone varchar (13), Student_email varchar (50), Student_gender varchar (6), Student_birth datetime, ); create table ms_tutor( Tutor_id char (5) primary key not null, Tutor_name varchar (30), Tutor_address varchar (50), Tutor_phone varchar (13), Tutor_email varchar (50), Tutor_gender varchar (6), Tutor_birth datetime, ); 4 Tutorial SQL Muhammad Wali create table ms_program( Program_id char (5) primary key not null, Program_name varchar(50), Fee numeric, Program_duration varchar (20), ); create table ms_grade( grade_id char(1) primary key not null, nilai varchar(7), discount varchar(4), ); create table tr_jadwal ( Jadwal_id char (5) primary key not null, Day_ varchar(10), Time_ varchar(50), Program_id char (5) foreign key references ms_program(Program_id) on update cascade on delete cascade, Room char (3), tutor_id char(5) foreign key references ms_tutor(tutor_id) on update cascade on delete cascade, ); 5 Tutorial SQL Muhammad Wali create table tr_regist ( regist_id char (6) primary key not null, Student_id char(5) foreign key references ms_student(student_id) on update cascade on delete cascade, Jadwal_id char(5) foreign key references tr_jadwal(jadwal_id) on update cascade on delete cascade, grade_id char(1) foreign key references ms_grade(grade_id) on update cascade on delete cascade, ); create table tr_payment( payment_id char(5) primary key not null, regist_id char(6) foreign key references tr_regist(regist_id) on update cascade on delete cascade, Total_payment numeric, Payment_date datetime, ); create table trh_absensi( absensi_id char (2) primary key not null, jadwal_id char (5) foreign key references tr_jadwal(jadwal_id) on update cascade on delete cascade, Pertemuan1_date datetime, Pertemuan2_date datetime, Pertemuan3_date datetime, Pertemuan4_date datetime, Pertemuan5_date datetime, Pertemuan6_date datetime, 6 Tutorial SQL Muhammad Wali Pertemuan7_date datetime, Pertemuan8_date datetime, Pertemuan9_date datetime, Pertemuan10_date datetime, Pertemuan11_date datetime, Pertemuan12_date datetime, ); create table trd_absensi( absensi_id char (2) , student_id char (5) , Pertemuan_1 varchar(5), Pertemuan_2 varchar(5), Pertemuan_3 varchar(5), Pertemuan_4 varchar(5), Pertemuan_5 varchar(5), Pertemuan_6 varchar(5), Pertemuan_7 varchar(5), Pertemuan_8 varchar(5), Pertemuan_9 varchar(5), Pertemuan_10 varchar(5), Pertemuan_11 varchar(5), Pertemuan_12 varchar(5), primary key(absensi_id,student_id) );); 7 Tutorial SQL Muhammad Wali Setelah selesai membuat tabel sekarang kita memasukkan data, berikut sintaks ya : INSERT INTO ms_student VALUES ('80692','Yusmaneli','haji senen','085920735341','lily_siswani@yahoo.com','Female','11/03/1989') INSERT INTO ms_student VALUES ('80862','Stella clarissa','ks tubun','081808887933','stellycious@yahoo.com','female','09/08/1988') INSERT INTO ms_student VALUES ('82205','Agustino','jembatan tiga','08180834234','tino_she@yahoo.com','male','07/08/1988'); INSERT INTO ms_student VALUES ('82312','Imelda putri','duta indah','08569463434','ineldhaputri@yahoo.co.id','female','12/07/1988'); INSERT INTO ms_student VALUES ('85131','Titis annisa astrini','pasar minggu','08563434423','annisa_titis@yahoo.com','female','06/12/1987'); INSERT INTO ms_student VALUES ('81234','Hendri Tella','Aceh Besar','0219886765','hendri@yahoo.com','male','04/04/1989'); INSERT INTO ms_student VALUES ('84321','Lily Annisa Clarissa','Rawa belong','081765342509','bunga_lili@yahoo.com','female','10/02/1987'); create table tr_result( Result_id char (5) primary key not null, program_id char (5) foreign key references ms_program(program_id) on update cascade on delete cascade, student_id char (5) foreign key references ms_student(student_id) on update cascade on delete cascade, Written_Test varchar (4), Oral_Test varchar (4), Note varchar (10), ); 8 Tutorial SQL Muhammad Wali INSERT INTO ms_student VALUES ('87078','Kevin Pratama','Jeruk Purut','02198765432','tama_boy@gmail.com','male','12/12/1986'); INSERT INTO ms_student VALUES ('83421','Juliana','kebun jeruk','081834534443','ana_pink@gmail.com','female','02/02/1988'); INSERT INTO ms_student VALUES ('88139','Windika','gang u','0878343434','Windika@yahoo.com','male','02/07/1986'); __________________________________________________________________________ INSERT INTO ms_tutor VALUES ('D1908','muhammad wali','Haji rabu','0818666673','Robert@yahoo.com','Male','10/09/1970'); INSERT INTO ms_tutor VALUES ('D1989','Alexandra','Harmoni','0812624343','alex_chen@yahoo.com','Male','08/05/1973'); INSERT INTO ms_tutor VALUES ('D0708','stefania angelina','jl.anggrek','0813453434','angel_cute@yahoo.com','Female','10/12/1969'); INSERT INTO ms_tutor VALUES ('D1507','Fauzan','serpong','08783435534','zan@yahoo.com','Male','08/07/1972'); INSERT INTO ms_tutor VALUES ('D0612','sabrina','Lebak bulus','0859345344','rina@yahoo.com','Female','11/06/1977'); __________________________________________________________________________ INSERT INTO ms_program VALUES ('PC001','conversation',1500000,'12'); INSERT INTO ms_program VALUES ('PP001','toefl preparation',2000000,'12'); INSERT INTO ms_program VALUES ('PG001','general english',1000000,'12'); ___________________________________________________________________________ INSERT INTO ms_grade VALUES ('A','85-100','10%'); 9 Tutorial SQL Muhammad Wali INSERT INTO ms_grade VALUES ('B','75-84','5%'); INSERT INTO ms_grade VALUES ('C','0-74','0%'); __________________________________________________________________________ INSERT INTO tr_jadwal VALUES ('J0001','mon','13.00-15.00','PC001','101','D1908'); INSERT INTO tr_jadwal VALUES ('J0002','mon','15.00-17.00','PG001','105','D0612'); INSERT INTO tr_jadwal VALUES ('J0003','tue','09.00-11.00','PP001','101','D1989'); INSERT INTO tr_jadwal VALUES ('J0004','wed','13.00-15.00','PG001','103','D0612'); INSERT INTO tr_jadwal VALUES ('J0005','wed','15.00-17.00','PP001','104','D1989'); INSERT INTO tr_jadwal VALUES ('J0006','thu','09.00-11.00','PG001','102','D0612'); INSERT INTO tr_jadwal VALUES ('J0007','fri','15.00-17.00','PC001','101','D1908'); INSERT INTO tr_jadwal VALUES ('J0008','tue','13.00-15.00','PG001','104','D0612'); __________________________________________________________________________ INSERT INTO tr_regist VALUES ('reg001','80692','J0005','B'); INSERT INTO tr_regist VALUES ('reg002','80862','J0002','A'); INSERT INTO tr_regist VALUES ('reg003','82205','J0001','B'); INSERT INTO tr_regist VALUES ('reg004','82312','J0003','B'); INSERT INTO tr_regist VALUES ('reg005','85131','J0005','C'); INSERT INTO tr_regist VALUES ('reg006','81234','J0004','B'); INSERT INTO tr_regist VALUES ('reg007','84321','J0007','C'); INSERT INTO tr_regist VALUES ('reg008','87078','J0008','C'); 10 Tutorial SQL Muhammad Wali INSERT INTO tr_regist VALUES ('reg009','83421','J0001','C'); INSERT INTO tr_regist VALUES ('reg010','88139','J0005','B'); __________________________________________________________________________ INSERT INTO tr_payment VALUES ('PO101','reg001',1900000,'1/3/2008'); INSERT INTO tr_payment VALUES ('PO102','reg002',900000,'1/10/2008'); INSERT INTO tr_payment VALUES ('PO103','reg003',1425000,'1/11/2008'); INSERT INTO tr_payment VALUES ('PO104','reg004',1900000,'2/2/2008'); INSERT INTO tr_payment VALUES ('PO105','reg005',2000000,'2/5/2008'); INSERT INTO tr_payment VALUES ('PO106','reg006',950000,'2/11/2008'); INSERT INTO tr_payment VALUES ('PO107','reg007',1500000,'1/8/2008'); INSERT INTO tr_payment VALUES ('PO108','reg008',1000000,'2/8/2008'); INSERT INTO tr_payment VALUES ('PO109','reg009',1500000,'1/9/2008'); INSERT INTO tr_payment VALUES ('PO110','reg010',1900000,'2/12/2008'); __________________________________________________________________________ INSERT INTO trh_absensi VALUES ('01','J0001','03/03/2008','03/10/2008','03/17/2008','03/24/2008','03/31/2008','04/07/2008','04/ 14/2008','04/21/2008','04/28/2008','05/05/2008','05/12/2008','05/19/2008'); INSERT INTO trh_absensi VALUES ('02','J0002','03/03/2008','03/10/2008','03/17/2008','03/24/2008','03/31/2008','04/07/2008','04/ 14/2008','04/21/2008','04/28/2008','05/05/2008','05/12/2008','05/19/2008'); 11 Tutorial SQL Muhammad Wali INSERT INTO trh_absensi VALUES ('03','J0003','03/04/2008','03/11/2008','03/18/2008','03/25/2008','04/01/2008','04/08/2008','04/ 15/2008','04/22/2008','04/29/2008','05/06/2008','05/13/2008','05/27/2008'); INSERT INTO trh_absensi VALUES ('04','J0004','03/05/2008','03/12/2008','03/19/2008','03/26/2008','04/02/2008','04/09/2008','04/ 16/2008','04/23/2008','04/30/2008','05/07/2008','05/14/2008','05/21/2008'); INSERT INTO trh_absensi VALUES ('05','J0005','03/05/2008','03/12/2008','03/19/2008','03/26/2008','04/02/2008','04/09/2008','04/ 16/2008','04/23/2008','04/30/2008','05/07/2008','05/14/2008','05/21/2008'); INSERT INTO trh_absensi VALUES ('06','J0006','03/06/2008','03/13/2008','03/27/2008','04/03/2008','04/10/2008','04/17/2008','04/ 24/2008','05/08/2008','05/15/2008','05/22/2008','05/29/2008','06/05/2008'); INSERT INTO trh_absensi VALUES ('07','J0007','03/14/2008','03/28/2008','04/04/2008','04/11/2008','04/18/2008','04/25/2008','05/ 02/2008','05/09/2008','05/16/2008','05/23/2008','05/30/2008','06/06/2008'); INSERT INTO trh_absensi VALUES ('08','J0008','03/04/2008','03/11/2008','03/18/2008','03/25/2008','04/01/2008','04/08/2008','04/ 15/2008','04/22/2008','04/29/2008','05/06/2008','05/13/2008','05/27/2008'); __________________________________________________________________________ INSERT INTO trd_absensi VALUES ('01','80692','hadir','hadir','hadir','hadir','Izin','hadir','hadir','hadir','hadir','hadir','hadir','hadir'); INSERT INTO trd_absensi VALUES ('02','80862','hadir','hadir','hadir','Izin','hadir','hadir','hadir','hadir','hadir','Izin','hadir','hadir'); INSERT INTO trd_absensi VALUES ('03','82205','hadir','hadir','hadir','hadir','hadir','hadir','hadir','Alpha','hadir','hadir','hadir','hadir') ; 12 Tutorial SQL Muhammad Wali INSERT INTO trd_absensi VALUES ('04','82312','hadir','hadir','hadir','hadir','hadir','hadir','hadir','hadir','hadir','hadir','hadir','hadir'); INSERT INTO trd_absensi VALUES ('05','85131','hadir','Izin','hadir','hadir','hadir','hadir','hadir','hadir','hadir','hadir','hadir','hadir'); INSERT INTO trd_absensi VALUES ('06','81234','hadir','hadir','hadir','hadir','Alpha','hadir','Alpha','Alpha','hadir','hadir','aLpha','had ir'); INSERT INTO trd_absensi VALUES ('07','84321','hadir','hadir','hadir','hadir','hadir','Izin','hadir','hadir','hadir','hadir','hadir','hadir'); INSERT INTO trd_absensi VALUES ('08','87078','hadir','hadir','hadir','hadir','hadir','hadir','hadir','hadir','hadir','hadir','hadir','hadir'); INSERT INTO trd_absensi VALUES ('09','83421','hadir','hadir','Alpha','hadir','hadir','hadir','Alpha','hadir','hadir','hadir','Izin','hadir') ; INSERT INTO trd_absensi VALUES ('10','88139','hadir','hadir','hadir','hadir','hadir','Alpha','hadir','hadir','Izin','hadir','hadir','hadir'); __________________________________________________________________________ INSERT INTO tr_result VALUES ('RE001','PP001','80692','NULL','B','GOOD'); INSERT INTO tr_result VALUES ('RE002','PG001','80862','c','B','AVERAGE'); INSERT INTO tr_result VALUES ('RE003','PC001','82205','B','A','GOOD'); INSERT INTO tr_result VALUES ('RE004','PP001','82312','C','C','NULL'); INSERT INTO tr_result VALUES ('RE005','PP001','85131','B','NULL','GOOD'); INSERT INTO tr_result VALUES ('RE006','PG001','81234','C','C','NULL'); INSERT INTO tr_result VALUES ('RE007','PC001','84321','NULL','A','EXCELLENT'); 13 Tutorial SQL Muhammad Wali INSERT INTO tr_result VALUES ('RE008','PG001','87078','A','C','NULL'); INSERT INTO tr_result VALUES ('RE009','PC001','83421','A','b','GOOD'); INSERT INTO tr_result VALUES ('RE010','PP001','88139','B','C','AVERAGE'); __________________________________________________________________________ Jika belum pernah menjalankan, sebaiknya Anda ketik dan jalankan terlebih dahulu sebelum melangkah pada contoh kasus selanjutnya. Sebagai pemanasan awal, ketiklah beberapa sintaks data manipulation di bawah ini pada menu Query Analyzer. Jika selesai, coba jalankan dan lihat bagaimana hasilnya. Select * From ms_student Select * From ms_tutor Select * From ms_program Select * From ms_grade Select * From tr_jadwal Select * From tr_payment Select * From trh_absensi Select * From trd_absensi Select * From tr_result Pada Bab sebelumnya kita telah membuat beberapa tabel untuk setiap contoh. Pada Bab ini kita berlatih berbagai sintaks SQL DML. Beberapa contoh sintaks sebaiknya anda lakukan langsung di komputer adalah : 1. Menampilkan seluruh data student SELECT Student_id,Student_name,Student_address, Student_Phone,Student_email,Student_gender, 14 Tutorial SQL Muhammad Wali Student_birth,datetime FROM ms_student; Atau SELECT * FROM ms_student; Tanda * artinya menampilkan seluruh data (baris dan kolom) pada suatu tabel. 2. Kegunaan DISTINC Tampilkan id program dari jadwal tersedia SELECT DISTINCT Program_id FROM tr_jadwal; 15 Tutorial SQL Muhammad Wali Fungsi DISTINCT adalah untuk menghilangkan baaris yang duplikat. Coba Anda ketik sintaks berikut dan jalankan pada komputer anda. Perbedaan apa yang anda lihat SELECT Program_id FROM tr_jadwal 3. Field yang dapat dikalkulasikan Istilah asingnya adalah calculated, computed atau derived field.  Tampilkan daftar pembayaran, dengan menampilkan payment_id,regist_id dan total_payment + 100 SELECT payment_id, regist_id, total_payment + 100 FROM tr_payment  Tampilkan daftar pembayaran, dengan menampilkan payment_id,regist_id dan 10 % total_payment SELECT payment_id, regist_id, 0,1 * total_payment FROM tr_payment 16 Tutorial SQL Muhammad Wali 4. Penggunaan Perbandingan sebagai pencarian dalam kondisi Tampilkan kode payment, kode registrasi, dan jumlah pembayaran yang lebih dari 1.000.000. SELECT payment_id,regist_id,total_payment FROM tr_payment WHERE total_payment > 1000000 Ini adalah contoh perbandingan biasa, bukan majemuk, yang tidak mengandung unsur OR atau AND 17 Tutorial SQL Muhammad Wali 5. Penggunaan perbandingan Majemuk sebagai pencarian dalam kondisi Tampilkan seluruh alamat student yang tinggal di Aceh Besar atau Sabang. SELECT * FROM ms_student WHERE student_address = 'Aceh Besar' OR student_address='Sabang'; Tampilkan seluruh alamat student yang tinggal di Aceh Besar dan Sabang. SELECT * FROM ms_student WHERE student_address = 'Aceh Besar' AND student_address='Sabang'; Bagaimana hasilnya silakan anda coba? 6. Penggunaan BETWEEN sebagai pencarian dalam kondisi Tampilkan kode payment, kode registrasi, dan jumlah pembayaran yang lebih besar dari 1.000.000 dan kurang 2.000.000 SELECT payment_id, regist_id, total_payment FROM tr_payment WHERE BETWEEN 1000000 AND 2000000 Atau SELECT payment_id, regist_id, total_payment FROM tr_payment WHERE total_payment > 1000000 AND Total_payment < 2000000 Silakan lihat hasilnya..... 18 Tutorial SQL Muhammad Wali 7. Mengurutkan Satu Kolom Tampilkan kode siswa, nama siswa, dan grade siswa dengan urutan nama siswa dari kecil ke besar SELECT s.Student_id, s.Student_name, r.grade_id FROM ms_student S, tr_regist R WHERE s.Student_id=r.Student_id ORDER BY Student_name ASC; 8. Penggunaan COUNT (*) Tampilkan banyaknya total pembayaran yang lebih dari 1500000 SELECT COUNT (*) AS Banyaknya_Pembayaran FROM tr_payment WHERE Total_payment > 1500000; 19 Tutorial SQL Muhammad Wali 9. Penggunaan SUM dan COUNT Tampilkan kode pembayaran , kode regist,count dan Sum dari total pembayaran SELECT COUNT (Total_Payment) AS Jumlah_payment, SUM (Total_payment) AS Totalpayment FROM tr_payment; 20 Tutorial SQL Muhammad Wali 10. Penggunaan MIN,MAX,AVG Tampilkan minimal payment, maksimal payment, rata-rata payment SELECT MIN (total_payment) AS min_payment, MAX (total_payment) AS max_payment, AVG (total_payment) AS avg_payment FROM tr_payment; 11. Penggunaan GROUP BY Tampilkan jadwal_id berdasarkan program SELECT program_id, COUNT (program_id) AS mycount FROM tr_jadwal GROUP BY program_id; 21 Tutorial SQL Muhammad Wali Fungsi GROUP BY adalah untuk mendapatkan subtotal dengan ketentuan SELECT harus berupa single valued per group dan clause SELECT hanya dapat mengandung nama kolom, fungsi aggregat, konstanta, dan ekspresi (dapat terdiri atas kombinasi nama, kolom, fungsi aggregat dan konstanta) 12. Penggunaan ALL Tampilkan payment_id yang total paymentnya lebih besar dari total payment dengan regist_id reg003 dan reg002. SELECT * FROM tr_payment WHERE Total_payment > ANY (SELECT total_payment FROM tr_payment WHERE regist_id IN (‘reg003’,’reg002’)) 13. Join Sederhana Tampilkan jadwal_id, day_,time_,j.program-id, and progra_name. SELECT jadwal_id, j.program_id, program_name FROM tr_jadwal j,ms_program p WHERE j.program_id=p.program_id; 22 Tutorial SQL Muhammad Wali 14. Penggunaan UNION, dan INTERSECT Tampilkan seluruh student_id yang telah aktif atau baru mendaftar (SELECT student_id FROM ms_student WHERE student_id IS NOT NULL) UNION (SELECT student_id FROM tr_regist WHERE student_id IS NOT NULL); Tampilkan seluruh student_id yang telah aktif dan telah membayar SELECT s.student_id FROM ms_student s,tr_regist r WHERE s.student_id=r.student_id 23 Tutorial SQL Muhammad Wali 15. Penggunaan INSERT, UPDATE, dan DELETE  INSERT-DEFAULT Masukkan baris baru ke dalam tabel ms_student untuk kolom-kolom tertentu INSERT INTO ms_student1 Values (‘92999’,’Ziauddin’,’Jl. Takengon – Bireun’,NULL,NULL,’Male’,’29-Sep-1986’);  UPDATE Ubahlah seluruh alamat siswa pada tabel ms_student1 menjadi ‘Jl. Cut Mutia’ UPDATE ms_student1 SET student_address = ‘Jl. Cut Mutia’; Ubahlah alamat menjadi ‘Jl. Cut Mutia’ untuk student_id 90999 UPDATE ms_student1 SET student_address = ‘Jl.Cut Mutia’ WHERE student_id = ‘90999’; Ubahlah alamat menjadi ‘Jl.Cut Mutia’ dan Nama menjadi ‘Lala’ untuk student_id 90999. UPDATE ms_student1 SET student_address = ‘Jl.Cut Mutia’, student_name = ‘Lala’ WHERE student_id = ‘90999’;  DELETE Hapus seluruh ms_student1 yang ada hubunganya dengan nomor siswa 91999 DELETE FROM ms_student1 WHERE ms_student=’91999’; Hapus seluruh baris ms_student1 DELETE FROM ms_student1; 24 Tutorial SQL Muhammad Wali Anda bisa menggunakan sintax tersebut pada Oracle, SQL Server dan lain-lain. Selamat mencoba. Referensi : http://Desaincek.wordpress.com 25 Tutorial SQL Muhammad Wali