Program Studi: Manajemen Bisnis Telekomunikasi & Informatika Mata Kuliah: Data Management Oleh: Yudi Priyadi
(Structure of The Database Language)
Fakultas Ekonomi dan Bisnis
Program Studi:
Dosen:
School Economic and Business
MANAJEMEN BISNIS TELEKOMUNIKASI & INFORMATIKA
Yudi Priyadi, M.T.
Telkom University
Learning Objectives Students are able to explain the Structured Query Language when converting a physical table, Students are able to apply the results of the modeling diagram E-R into a physical table in accordance with the relationship formed when making modeling, Students are able to perform analysis based on design modeling diagram E-R to be converted into a physical table, Students are able to evaluate the current implementation to decide the truth of E-R diagram design into a physical table in a database using SQL structure.
2
Creating the great business leaders
Fakultas Ekonomi dan Bisnis
Program Studi:
Dosen:
School Economic and Business
MANAJEMEN BISNIS TELEKOMUNIKASI & INFORMATIKA
Yudi Priyadi, M.T.
Telkom University
SQL :
1. Data Definition Language (DDL) >>> create, alter, & drop. 2. Data Manipulation Language (DML), >>> Insert, Select, Update, & Delete 3. Data Control Language (DCL), >>> grant & revoke.
Source: Y.Priyadi. 2014. Kolaborasi SQL & ERD Dalam Implementasi Database (ISBN:978-979-29-2195-3), Andi Publisher
Example: create database cobacoba; use cobacoba; drop database cobacoba; create table tb1 ( nip int primary key, nama varchar(50), alamat varchar(50) not null ); go; insert into tb1 values (13711028,'yudi priyadi','Soekarno-Hatta Bandung'); select * from tb1;
3
Creating the great business leaders
Fakultas Ekonomi dan Bisnis
Program Studi:
Dosen:
School Economic and Business
MANAJEMEN BISNIS TELEKOMUNIKASI & INFORMATIKA
Yudi Priyadi, M.T.
Telkom University
create table dokter ( nidok char(7) primary key, nm_dok varchar(30) not null, almt_dok(50) not null )
create table pasien ( kode_psn char(5) primary key, nm_psn varchar(30) not null, almt_psn varchar(50) null, umur integer null )
create table penyakit ( kode_pkt char(7) primary key, nm_pkt varchar(50) not null, jenis_pkt varchar(75) not null, ruang varchar(15) not null, jam_prtek datetime not null, nidok char(7) foreign key references dokter(nidok) on update cascade on delete cascade ) 4
Creating the great business leaders
Fakultas Ekonomi dan Bisnis
Program Studi:
Dosen:
School Economic and Business
MANAJEMEN BISNIS TELEKOMUNIKASI & INFORMATIKA
Yudi Priyadi, M.T.
Telkom University
Try to convert…. now
Source: Y.Priyadi. 2014. Kolaborasi SQL & ERD Dalam Implementasi Database (ISBN:978-979-29-2195-3), Andi Publisher
Source: Y.Priyadi. 2014. Kolaborasi SQL & ERD Dalam Implementasi Database (ISBN:978-979-29-2195-3), Andi Publisher
5
Creating the great business leaders
Fakultas Ekonomi dan Bisnis
Program Studi:
Dosen:
School Economic and Business
MANAJEMEN BISNIS TELEKOMUNIKASI & INFORMATIKA
Yudi Priyadi, M.T.
Telkom University
How about thizzz……?
Example:… try to discuss alter table pasien add status char(7), pekerjaan char(15)
alter table pasien drop pekerjaan alter table pasien drop constraint pk_kodpsn
drop table nama tabel use rumah_sakit; drop table pasien;
drop table dokter; drop table penyakit; Source: Y.Priyadi. 2014. Kolaborasi SQL & ERD Dalam Implementasi Database (ISBN:978-979-29-2195-3), Andi Publisher
To be continued….. 6
Creating the great business leaders
Fakultas Ekonomi dan Bisnis
Program Studi:
Dosen:
School Economic and Business
MANAJEMEN BISNIS TELEKOMUNIKASI & INFORMATIKA
Yudi Priyadi, M.T.
Telkom University
Case >>> review Database name: datanya_kita Table name : sumberdata Record :
Give an explanation for each of these SQL syntax. Example: 1. select nama, alamat from sumberdata; 2. Update sumberdata set nama =’pramoedya’, alamat=’Graha Batu Karang 11-B’ where id = 15; 3. Delete from sumberdata where id = 11; 4. grant insert, delete on dokter to yudi; 5. revoke update on pasien from yudi, pram; 6. Etc....... (plizzzz take a look @ our case study) Source: Y.Priyadi. 2014. Kolaborasi SQL & ERD Dalam Implementasi Database (ISBN:978-979-29-2195-3), Andi Publisher
7
Creating the great business leaders
Fakultas Ekonomi dan Bisnis
Program Studi:
Dosen:
School Economic and Business
MANAJEMEN BISNIS TELEKOMUNIKASI & INFORMATIKA
Yudi Priyadi, M.T.
Telkom University
mathematical operators = (Equals) > (Greater Than) < (Less Than) >= (Greater Than or Equal To) <= (Less Than or Equal To) <> (Not Equal To)
Exploration SQL Commands Logical operators
Aggregate Functions avg (expression) count (expression) max (expression) min (expression) sum (expression)
select namabarang, hargabarang, jumlahbarang from sumberdata where hargabarang > 250 and jumlahbarang < 10;
select nama, alamat, kota from domisili where kota not in (‘jakarta’,’bandung’);
select avg(honor) from karyawan;
Select nama, tgllahir from sumberdata where tgllahir between ‘1971-06-22’ And ‘1973-06-11’;
select * from mahasiswa order by nama
select nama, alamat, kota from domisili where kota like ‘b%’ ;
select namakaryawan, honor, penghasilan = honor * 0.15 from karyawan; Source: Y.Priyadi. 2014. Kolaborasi SQL & ERD Dalam Implementasi Database (ISBN:978-979-29-2195-3), Andi Publisher
8
Creating the great business leaders
Fakultas Ekonomi dan Bisnis
Program Studi:
Dosen:
School Economic and Business
MANAJEMEN BISNIS TELEKOMUNIKASI & INFORMATIKA
Yudi Priyadi, M.T.
Telkom University
Relation Table
Plizzz… write your SQL syntax n’ discuss together now…..
Source: Y.Priyadi. 2014. Kolaborasi SQL & ERD Dalam Implementasi Database (ISBN:978-979-29-2195-3), Andi Publisher
9
Creating the great business leaders
