BAHASA KUERI KOMERSIAL STRUCTURED QUERY LANGUAGE SQL SQL
BAHASA KUERI KOMERSIAL
STRUCTURED QUERY LANGUAGE (SQL) SQL dipublikasikan oleh E. F. CODD (1970) mengenai model relational. Kemudian pada tahun 1974, D. Chamberlin dan R. F. Boyce mengembangkan bahasa query untuk memanipulasi dan mengekstraksi data dari basisdata relational. Sasaran SQL q q Menciptakan basis data dan struktur relasi Melakukan menajemen data tingkat dasar Membentuk query sederhana dan kompleks Melakukan tugas-tugas dengan seminimal mungkin memakai struktur dan sintaks perintah relatif mudah dipelajari
Jenis SQL : v Interactive SQL data dalam SQL jenis ini selalu berubah dengan cepat seiring dengan penggunaannya yang sering / interaktif. v Static SQL data dalam SQL ini selalu tetap dalam periode waktu yang cukup lama. Biasanya berisi data-data yang mengalami waktu perubahan yang cukup lama. v Dynamic SQL data dalam jenis SQL ini berubah dalam periode waktu tertentu.
Subdivisi SQL v v v DDL (Data Definition Language) digunakan untuk mendefinisikan struktur atau skema basis data DML (Data Manipulation Language) digunakan untuk manajemen data dalam basis data DCL ( Data Control Language) berhubungan dengan pengaturan hak akses dan wewenang.
PENGELOMPOKAN STATEMEN SQL
PENGELOMPOKAN STATEMEN SQL 1. Data Definition Language (DDL) CREATE DATABASE DROP DATABASE CREATE TABEL DROP TABEL CREATE INDEX DROP INDEX CREATE VIEW DROP VIEW ALTER TABLE 2. Data Manipulation Language (DML) INSERT, SELECT, UPDATE, DELETE
3. Data Access GRANT , REVOKE 4. Data Integrity RECOVER TABLE 5. Auxiliary Select Into Outfile (UNLOAD), LOAD, RENAME TABLE
D D L v CREATE 1. Pembuatan Database Nama Database adalah yang dapat mewakili suatu kejadian dapat berupa nama organisasi atau perusahaan. Sintaks : CREATE DATABASE nama_database Contoh : Buat database dengan nama latihan CREATE DATABASE latihan
2. Pembuatan Tabel Sintaks : CREATE TABLE nama_table ( nama_kolom 1 tipe_data_kolom 1, nama_kolom 2, tipe_data_kolom 2, …. ) Contoh : Buat struktur tabel dengan nama tabel MHS dengan data NPM char(8), NAMA char(25), ALAMAT char(30) CREATE TABLE MHS (NPM char(8) not null, NAMA char(25) notnull, ALAMAT char(30) notnull)
3. Pembuatan Index Sintaks : CREATE [UNIQUE] INDEX nama_index ON nama_table (nama_kolom) ; Contoh : Buat index data mahasiswa berdasarkan NPM dengan nama MHSIDX Dimana NPM tidak boleh sama CREATE UNIQUE INDEX MHSIDX ON MHS(NPM)
4. Pembuatan View Sintaks : CREATE VIEW nama_view [ (nama_kolom 1, …. ) ] AS SELECT statement [WITH CHECK OPTION] ; Contoh : Buat view dengan nama MHSVIEW yang berisi semua data mahasiswa CREATE VIEW MHSVIEW AS SELECT * FROM MHS
v DROP (MENGHAPUS) 1. Menghapus Database Sintaks : DROP DATABASE nama_db ; 2. Menghapus Tabel Sintaks : DROP TABLE nama_table ; 3. Menghapus Index Sintaks : DROP INDEX nama_index ; 4. Menhapus View Sintaks : DROP VIEW nama_view ; Contoh : DROP DATABASE Mahasiswa; DROP TABLE MHS; DROP INDEX MHSIDX; DROP VIEW MHSVIEW;
v ALTER TABLE (MERUBAH STRUKTUR TABEL) Sintaks : ALTER TABLE nama_tabel ADD nama_kolom jenis_kolom [FIRST | AFTER nama_kolom] CHANGE [COLUMN] oldnama newnama MODIFY nama_kolom jenis kolom, DROP nama_kolom RENAME namabaru_tabel
Contoh : 1. Tambahkan kolom JKEL dengan panjang 1 char pada tabel MHS ALTER TABLE MHS ADD JKEL char(1); 2. Ubah panjang kolom MTKULIAH menjadi 30 char ALTER TABLE MKUL MODIFY COLUMN MTKULIAH char(30); 3. Hapus kolom JKEL dari data table MHS ALTER TABLE MHS DROP JKEL;
D M L v INSERT INTO Nama_tabel [(nama_kolom 1, …)] VALUES (data 1, data 2, , , ) Contoh : Masukan data matakuliah Berkas Akses dengan kode KK 222 dan besarnya 2 INSERT MKUL VALUES(“KK 222”, ”Berkas Akses”, 2);
v UPDATE Sintaks : UPDATE nama_tabel SET nama_kolom = value_1 WHERE kondisi ; Contoh : Ubah alamat menjadi “Depok” untuk mahasiswa yang memiliki NPM “ 50409486” UPDATE MHS SET ALAMAT=”Depok” WHERE NPM=” 50409486”;
v DELETE Sintaks : DELETE FROM nama_table WHERE kondisi Contoh : Hapus data nilai matakuliah “KK 021” bagi mahasiswa yang mempunyai NPM “ 10109832” DELETE FROM NILAI WHERE NPM=” 10109832” AND KDMK=”KK 021”
Tabel dibawah ini untuk mengerjakan Select (tampilan) dari SQL Tabel Nilai Tabel Mahasiswa NPM 10109832 10109126 32105500 41200525 50409486 21109353 10109001 21108002 NAMA_MHS Nurhayati Astuti Budi Prananigrum Pipit Quraish Fintri Julizar NPM ALAMAT_MHS Jakarta Depok Bogor Bekasi Bogor Depok Jakarta 10109832 10109126 32105500 41200525 21109353 50409486 NO_MK KK 021 KD 132 KK 021 KU 122 KD 132 Tabel Mata. Kuliah KD_MK KK 021 KD 132 KU 122 NAMA_MK Sistem Basis Data Sistem Informasi Manajemen Pancasila SKS 2 3 2 MID FINAL 60 70 55 90 75 80 75 90 40 80 75 0
v SELECT Sintaks : SELECT [DISTINCT | ALL] nama_kolom FROM nama_tabel [ WHERE condition ] [ GROUP BY column_list ] [HAVING condition ] [ ORDER BY column_list [ASC | DESC]]
Contoh : a. Tampilkan semua data mahasiswa SELECT NPM, NAMA, ALAMAT FROM MAHASISWA; Atau SELECT * FROM MAHASISWA; Hasil: NPM NAMA_MHS ALAMAT_MHS 10109832 10109126 31209500 41209525 Nurhayati Astuti Budi Prananingrum Jakarta Depok Bogor
b. Tampilkan Mata Kuliah yang SKSnya 2 Select NAMA_MK from matakuliah Where sks = 2 Hasil : NAMA_MK Sistem Basis Data Pancasila
c. Tampilkan semua data nilai dimana nilai MID lebih besar sama dengan 60 atau nilai finalnya lebih besar 75. Hasilnya : NPM 10109832 10109126 41209525 21109353 NO. MK MID KK 021 KD 132 KU 122 60 70 90 75 FINAL 75 90 80 75
Manipulasi string : LIKE, NOT LIKE, %, __ Contoh : 1. SELECT npm, nama FROM mahasiswa NPM WHERE nama LIKE ‘%a% 10109832 Hasil : NAMA_MHS Nurhayati 41200525 Prananigrum 21109353 Quraish 21108002 Julizar 2. SELECT npm, nama FROM mahasiswa WHERE nama NOT LIKE ‘%a% Hasil NPM 10109126 32105500 50409486 10109001 ALAMAT_MHS Jakarta Bogor Jakarta NAMA_MHS ALAMAT_MHS Astuti Budi Pipit Fintri Jakarta Depok Bekasi Depok
3. SELECT * FROM nilai WHERE npm LIKE ‘___09%’ 4. SELECT * FROM nilai WHERE npm[4, 5]=‘ 09’ NPM NO_MK MID FINAL 10109832 10109126 21109353 50409486 KK 021 KD 132 KU 122 KD 132 60 70 75 80 75 90 75 0
5. SELECT DISTINCT alamat_mhs FROM MAHASISWA 6. SELECT * FROM matakuliah ORDER BY sks KD_MK NAMA_MK SKS ALAMAT_MHS KK 021 KU 122 KD 132 Sistem Basis Data Pancasila Sistem Informasi Manajemen 2 2 3 Jakarta Depok Bogor Bekasi
DATA ACCESS v GRANT Sintaks : GRANT hak_akses ON nama_db TO nama_pemakai [IDENTIFIED BY] [PASSWORD] ‘Password’ [WITH GRANT OPTION] ; Contoh : Berikan hak akses kepada Adi untuk menampikan nilai final test pada tabel Nilai. GRANT SELECT (FINAL) ON NILAI TO ADI
v REVOKE Sintaks : REVOKE hak_akses ON nama_db FROM nama_pemakai ; REVOKE hak_akses ON nama_tabel FROM nama_pemakai ; Contoh : Tarik kembali dari Adi hak akses untuk menampilkan nilai final test REVOKE SELECT (FINAL) ON NILAI FROM ADI
AUXILIARY SELECT … INTO OUTFILE ‘filename’ Sintaks ini digunakan untuk mengekspor data dari tabel ke file lain. Sintaks : SELECT … INTO OUTFILE ‘Nama File’ [FIELDS | COLUMNS] [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] ]
Contoh : Ubah semua data mahasiswa ke bentuk ASCII dan disimpan ke file teks di directory/home/adi dengan pemisah antar kolom ‘|’ SELECT * FROM MAHASISWA INTO OUTFILE “/home/adi/teks” FIELDS TERMINATED BY “ ”; Atau UNLOAD TO “/home/adi/teks. txt” SELECT * FROM MAHASISWA; delimiter “|”
LOAD Sintaks Kueri ini digunakan untuk mengimpor data dari file lain ke tabel. Sintaks : LOAD DATA LOCAL INFILE “ nama_path” INTO TABLE nama_tabel [ nama_kolom] ; [FIELDS | COLUMNS] [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] ]
Contoh : Memasukkan data dari file teks yang berada pada direktori “/home/adi” ke dalam tabel MHS_2. Dimana pemisah antara kolom dalam file teks adalah tab (t) : LOAD DATA LOCAL INFILE FROM ‘/home/adi/teks’ INTO MHS_2 FILELDS TERMINATED BY ‘|t’; Atau LOAD FROM “/home/adi/teks. txt” delimiter “|” INSERT INTO mhs_2
FUNGSI AGGREGATE COUNT digunakan untuk menghitung jumlah record. Menghitung jumlah record mahasiswa dari tabel MAHASISWA SELECT COUNT(*) FROM MAHASISWA hasil : 8 SUM digunakan untuk menghitung total dari kolom yang mempunyai tipe data numerik. SELECT SUM(SKS) AS ‘TOTAL SKS’ FROM MATAKULIAH hasil : TOTAL SKS 7
AVG digunakan untuk menghitung rata-rata dari data-data dalam sebuah kolom. SELECT AVG(FINAL) AS ‘FINAL’ FROM Nilai hasil : 60 MIN digunakan untuk menghitung nilai minimal dalam sebuah kolom. SELECT MIN(FINAL) FROM Nilai hasil : 40 MAX digunakan untuk menghitung nilai maksismum dalam sebuah kolom SELECT MAX(MID) FROM Nilai hasil : 90
1. Tampilkan nilai final tertinggi SELECT MAX( final) FROM 'Nilai' 2. Tampilkan nilai final terendah SELECT MIN( final) FROM 'Nilai‘; SELECT MIN(final), MAX(final) FROM nilai 3. Tampilkan rerata dari nilai MID dan FINAL SELECT AVG(mid), AVG(final) FROM mhs 4. Menampilkan nama yang mengandung hurus a kecil SELECT npm, nm_mhs FROM 'mhs' where nm_mhs LIKE '%a%' SELECT npm, nm_mhs FROM `mhs` WHERE nm_mhs COLLATE Latin 1_bin LIKE '%a%';
5. Tampilkan npm, kd_mk, mid untuk nilai mid antara 70 sd 90 SELECT npm, kd_mk, mid FROM `nilai` WHERE mid <91 AND mid >69 atau SELECT npm, kd_mk, mid FROM `nilai` WHERE mid BETWEEN 70 AND 90 6. Tampilkan npm, nm_mhs, kd_mk, final untuk semua mahasiswa SELECT nilai. NPM, nm_mhs, kd_mk, final FROM mhs, nilai WHERE mhs. NPM = nilai. NPM 7. Tampilkan npm, nm_mhs, nama_mk, mid, final untuk semua mahasiswa SELECT nilai. NPM, nm_mhs, nama_mk, mid, final FROM mhs, nilai, matkul WHERE mhs. NPM = nilai. NPM AND matkul. kd_mk = nilai. kd_mk
8. Tampilkan nm_mhs, nama_mk, mid, final untuk semua mahasiswa yang nilai mid>60 dan final>60 SELECT nm_mhs, nama_mk, mid, final FROM mhs, matkul, nilai WHERE mhs. NPM = nilai. NPM AND matkul. kd_mk = nilai. kd_mk AND mid>60 and final>60 9. Tambahkan kolom FAKULTAS pada tabel Mahasiswa ALTER TABLE `mhs` ADD `Fak` VARCHAR(50) NOT NULL; 10. Tambahkan data mahasiswa dari mhs 1. txt LOAD DATA LOCAL INFILE “D: /MHS 1. TXT” INTO TABLE MHS FIELDS TERMINATED BY “, ”; 11. Ubah nilai dari fak dari ketentuan, ambil 1 digit ke 3 dari NPM Jika : ni 1 ainya 1 maka fak=FIKTI ni 1 ainya 2 maka fak=FE ni 1 ainya 3 maka fak=FTSP ni 1 ainya 4 maka fak=FTI ni 1 ainya 5 maka fak=FPSI ni 1 ainya 6 maka fak=FSAS UPDATE mhs SET fak='FIKTI' WHERE npm LIKE '__1%'; dst Atau UPDATE mhs SET fak=’FIKTI’ WHERE MID( NPM, 3, 1 )=’ 1’ dst
12. Hitung dan tampilkan jumlah mahasiswa per fakultas. SELECT ‘FAKULTAS’, fak, count(fak) FROM mhs GROUP BY fak;
- Slides: 37