basis data i - ifa's | my lecturing weblog sub pokok bahasan structured query language (sql)...

34
1 Basis Data I Pertemuan Ke-10 (SQL – Part 1) Noor Ifada [email protected] 1

Upload: leduong

Post on 07-Mar-2019

226 views

Category:

Documents


0 download

TRANSCRIPT

Page 1: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

1

Basis Data I

Pertemuan Ke-10(SQL – Part 1)

Noor Ifada

[email protected]

1

Page 2: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

22

Sub Pokok Bahasan

Structured Query Language (SQL)

Tipe Data

Basisdata Tennis:CDM dan PDM

Constraint

Contoh Data

Data Definition Language (DDL)

Data Manipulation Language (DML)

2

Page 3: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

33

Structured Query Language (SQL)

Bahasa standar yang digunakan untuk memanipulasi basisdata

relasional

3

Terdiri dari:

Data Definition Language (DDL):

CREATE tables, indexes, views, Establish primary/foreign keys, DROP / ALTER tables ....

Data Manipulation Language (DML):

INSERT / UPDATE / DELETE, SELECT ....

Data Control Language (DCL):

COMMIT / ROLLBACK work, GRANT / REVOKE ....

Page 4: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

44

Tipe Data

Tipe Data NUMERIK

Tipe Data STRING

Tipe Data DATE & TIME

4

Page 5: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

5

BIT A bit-field, from 1 to 64 bits wide. (Prior to MySQL 5 BIT was functionally equivalent to TINYINT)

BIGINT Integer value, supports numbers from -9223372036854775808 to 9223372036854775807 (or 0 to 18446744073709551615 if UNSIGNED)

BOOLEAN (or BOOL)Boolean flag, either 0 or 1, used primarily for on/off flags

DECIMAL (or DEC)Floating point values with varying levels of precision

DOUBLEDouble-precision floating point values

FLOATSingle-precision floating point values

Tipe Data: Numerik

Page 6: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

6

INT (or INTEGER)Integer value, supports numbers from -2147483648 to 2147483647 (or 0 to 4294967295 if UNSIGNED)

MEDIUMINTInteger value, supports numbers from -8388608 to 8388607 (or 0 to 16777215 if UNSIGNED)

REAL4-byte floating point values

SMALLINTInteger value, supports numbers from -32768 to 32767 (or 0 to 65535 if UNSIGNED)

TINYINTInteger value, supports numbers from -128 to 127 (or 0 to 255 if UNSIGNED)

Tipe Data: Numerik (contd-2)

Page 7: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

7

CHAR Fixed-length string from 1 to 255 chars long. Its size must be specified at create time, or MySQL assumes CHAR(1)

ENUM Accepts one of a predefined set of up to 64K strings

LONGTEXT Same as TEXT, but with a maximum size of 4GB

MEDIUMTEXT Same as TEXT, but with a maximum size of 16K

SET Accepts zero or more of a predefined set of up to 64 strings

Tipe Data: String

Page 8: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

8

TEXT Variable-length text with a maximum size of 64K

TINYTEXT Same as TEXT, but with a maximum size of 255 bytes

VARCHAR Same as CHAR, but stores just the text. The size is a maximum, not a minimum

BLOB Blob with a maximum length of 64K

MEDIUMBLOB Blob with a maximum length of 16MB

LONGBLOB Blob with a maximum length of 4GB

TINYBLOB Blob with a maximum length of 255 bytes

Tipe Data: String (contd-2)

Page 9: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

9

DATE Date from 1000-01-01 to 9999-12-31 in the format YYYY-MM-DD

TIME Time in the format HH:MM:SS

YEARA 2 or 4 digit year, 2 digit years support a range of 70 (1970) to 69 (2069), 4 digit years support a range of 1901 to 2155

DATETIME A combination of DATE and TIME

TIMESTAMP Functionally equivalent to DATETIME (but with a smaller range)

Tipe Data: Date and Time

Page 10: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

1010

Contoh: CDM untuk Basisdata Tennis

10

Page 11: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

11

Contoh: PDM untuk Basisdata Tennis

Page 12: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

12

Column value constraints:PLAYER(SEX) IN {'M','F'}MATCHES(WON) IN {0,1,2,3}MATCHES(WON) IN {0,1,2,3}COMMITTEE_MEMBERS(POSITION) IN {'Chairman', 'Secretary', 'Treasurer', 'General member'}PLAYERS (JOINED) >= 1970PENALTIES (PAYMENT_DATE) >= '1970-01-01'PENALTIES (AMOUNT) > 0.00COMMITTEE_MEMBERS (BEGIN_DATE) > '1990-01-01'

 Row level constraints: PLAYERS: YEAR (BIRTH_DATE) <= JOINED COMMITTEE_MEMBERS: END_DATE >= BEGIN_DATE

Contoh: Constraints dlm Basisdata Tennis

Page 13: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

13S1 Teknik Informatika - Unijoyo

Players

Contoh: Data dlm Basisdata Tennis

Page 14: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

14S1 Teknik Informatika - Unijoyo

Teams Matches

Penalties

Contoh: Data dlm Basisdata Tennis (contd-2)

Page 15: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

15S1 Teknik Informatika - Unijoyo

Committee_members

Contoh: Data dlm Basisdata Tennis (contd-3)

Page 16: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

1616

Data Definition Language (DDL)

CREATE/DROP basisdataCREATE tabelDROP tabelRENAME tabelALTER tabel

16

Page 17: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

17

CREATE DATABASE db_name

Contoh 1: Buatlah basisdata baru dengan nama tennis

CREATE DATABASE tennis

DROP DATABASE db_name

Contoh 2: Drop basisdata tennis dari sistem

DROP DATABASE tennis

DDL: Create/Drop (Basisdata)

Page 18: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

18

CREATE TABLE tbl_name (column_name data_type [DEFAULT expr] [column _constraint] , …[table_constraint] );

DDL: Create/Drop/Rename (Tabel)

DROP TABLE tbl_name [, tbl_name]

Contoh 3: Drop seluruh tabel dalam basisdata tennis!

DROP TABLE COMMITTEE_MEMBERS;DROP TABLE PENALTIES;DROP TABLE MATCHES;DROP TABLE TEAMS;DROP TABLE PLAYERS;

RENAME TABLE tbl_name TO new_tbl_name[, tbl_name2 TO new_tbl_name2] ...

Contoh 4: Gantilah nama tabel Teams menjadi Groups!

RENAME TABLE Teams TO Groups

Page 19: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

19

Contoh 5: Buat tabel Players!

SQL:

CREATE TABLE PLAYERS(PLAYERNO SMALLINT NOT NULL, NAME CHAR(15) NOT NULL, INITIALS CHAR(3) NOT NULL, BIRTH_DATE DATE , SEX CHAR(1) NOT NULL, JOINED SMALLINT NOT NULL, STREET CHAR(15) NOT NULL, HOUSENO CHAR(4) , POSTCODE CHAR(6) , TOWN CHAR(10) NOT NULL, PHONENO CHAR(10) , LEAGUENO CHAR(4) , PRIMARY KEY (PLAYERNO) );

DDL: Create (Tabel)

Contoh 6: Buat tabel Committee_Members!

SQL:CREATE TABLE COMMITTEE_MEMBERS(PLAYERNO SMALLINT NOT NULL, BEGIN_DATE DATE NOT NULL, END_DATE DATE , POSITION CHAR(20) , PRIMARY KEY (PLAYERNO, BEGIN_DATE) ); 

Page 20: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

20

Contoh 7: Buat tabel Players!

CREATE TABLE PLAYERS(PLAYERNO SMALLINT NOT NULL, NAME CHAR(15) NOT NULL, INITIALS CHAR(3) NOT NULL, BIRTH_DATE DATE , SEX CHAR(1) NOT NULL, JOINED SMALLINT NOT NULL, STREET CHAR(15) NOT NULL, HOUSENO CHAR(4) , POSTCODE CHAR(6) , TOWN CHAR(10) NOT NULL, PHONENO CHAR(10) , LEAGUENO CHAR(4) , PRIMARY KEY (PLAYERNO) , CHECK (PLAYERNO >= 1) , CHECK (SEX IN ('M', 'F')) , CHECK (JOINED >= 1970) , CHECK (YEAR(BIRTH_DATE)<= JOINED) );

DDL: Create (Tabel) (contd-2)

Contoh 8: Buat tabel Committee_Members!

SQL:CREATE TABLE COMMITTEE_MEMBERS(PLAYERNO SMALLINT NOT NULL, BEGIN_DATE DATE NOT NULL, END_DATE DATE , POSITION CHAR(20) , PRIMARY KEY (PLAYERNO, BEGIN_DATE) , FOREIGN KEY (PLAYERNO) REFERENCES PLAYERS (PLAYERNO) , CHECK (POSITION IN ('Chairman', 'Secretary','Treasurer', 'General member')) , CHECK (BEGIN_DATE >= '1990-01-01'), CHECK (END_DATE >= BEGIN_DATE) );

Page 21: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

21

ALTER TABLE tbl_namealter_specification [, alter_specification] ...

alter_specification: ADD CHANGE & MODIFY DROP RENAME

DDL: Alter (Tabel)

Page 22: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

22

Contoh 9: Tambahkan satu kolom bernama TYPE di dalam tabel TEAMS. Kolom TYPE ini untuk memberikan identifikasi tim Pria dan Wanita! SQL:

ALTER TABLE TEAMSADD COLUMN TYPE CHAR (1);

ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]

ADD [COLUMN] (col_name column_definition,...)

DDL: Alter (Tabel) - Add

Contoh 10: Dengan adanya kolom TYPE, maka (misalkan) tim 2 sebagai tim Pria harus di-update. SQL:

UPDATE TEAMSSET TYPE = 'M'WHERE TEAMNO = 2;

Page 23: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

23

Contoh 11: Tambahkan panjang karakter kolom TOWN dari 10 menjadi 20! 

ALTER TABLE PLAYERSMODIFY COLUMN TOWN CHAR (20);

CHANGE [COLUMN] old_col_name new_col_namecolumn_definition [FIRST|AFTER col_name]

MODIFY [COLUMN] col_name column_definition [FIRST |AFTER col_name]

DDL: Alter (Tabel) – Change & Modify

Page 24: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

24

Contoh 12: Hapuskan kolom TYPE dari tabel TEAMS! SQL:

ALTER TABLE TEAMSDROP COLUMN TYPE

DROP [COLUMN] col_nameDROP PRIMARY KEYDROP FOREIGN KEY fk_symbol

DDL: Alter (Tabel) – Drop & Rename

Contoh 13: Ubah nama tabel Teams menjadi Groups!

SQL :ALTER TABLE Teams RENAME Groups

RENAME [TO] new_tbl_name

Page 25: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

25

INSERT / UPDATE / DELETE, SELECT

Klausa-klausa SELECT: FROM --> menentukan tabel(-tabel)

sumber WHERE --> memilih baris(-baris)

yang memenuhi kondisi (-kondisi) GROUP BY --> menggabungkan

baris(-baris) yang kolomnya memiliki nilai yang sama

HAVING --> memilih grup yang memenuhi kondisi tertentu

SELECT --> memilih kolom(-kolom) ORDER BY --> mengurutkan baris-

baris berdasarkan nilai-nilai yang ada dalam kolom(-kolom)

Data Manipulation Language (DML)

Page 26: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

26

Contoh 14: Tampilkan seluruh data penalti.

SQL:SELECT *FROM Penalties

Hasil:PAYMENTNO PLAYERNO PAYMENT_DATE AMOUNT 1 6 1980-12-08 100.00 2 44 1981-05-05 75.00 3 27 1983-09-10 100.00

4 104 1984-12-08 50.00 5 44 1980-12-08 25.00 6 8 1980-12-08 25.00 7 44 1982-12-30 30.00 8 27 1984-11-12 75.00

DML: Klausa Select

Contoh 15: Tampilkan nomor pembayaran, nomor pemain dan jumlah dari masing-masing penalti

SQL:

SELECT paymentno, playerno,amount

FROM Penalties

Hasil:PAYMENTNO PLAYERNO AMOUNT

1 6 100.00 2 44 75.00 3 27 100.00 4 104 50.00 5 44 25.00 6 8 25.00 7 44 30.00 8 27 75.00

Page 27: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

27

Contoh 16: Dapatkan nomor, nama, jenis kelamin dan tanggal lahir dari masing-masing pemain pria yang lahir setelah tahun 1970.

SELECT playerno, name, sex, birth_dateFROM PlayersWHERE sex = ‘M’ AND Year(birth_date) > 1970

Contoh 17: Dapatkan nomor pemain yang pernah terkena penalti lebih besar dari 25!

SELECT playernoFROM PenaltiesWHERE amount > 25

DML: Klausa Where

Page 28: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

28

Contoh 18: Buat daftar nomor pembayaran dan nomor pemain untuk masing-masing penalti; urutkan hasil berdasarkan nomor pemain dan nomor pembayaran untuk masing-masing pemain.

SQL:SELECT paymentno, playernoFROM PenaltiesORDER BY playerno, paymentno

atau: ORDER BY 2, 1

Hasil:PAYMENTNO PLAYERNO 1 6 6 8 3 27 8 27 2 44 5 44 7 44 4 104

DML: Klausa Order By

Page 29: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

29

Contoh 19: Cari nomor dari masing-masing pemain yang telah melakukan lebih dari 1 penalti yang besarnya lebih dari 25.

SQL:SELECT playernoFROM PenaltiesWHERE amount > 25GROUP BY playernoHAVING COUNT(*) > 1ORDER BY playerno

DML: Klausa Select (lengkap)

Page 30: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

30

Contoh 20: Tampilkan nomor tim dan nama kapten dari masing-masing timSQL:

SELECT TEAMNO, NAMEFROM TEAMS, PLAYERSWHERE TEAMS.PLAYERNO = PLAYERS.PLAYERNO

DML: Klausa Select (lebih dari 1 tabel)

Page 31: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

31

Contoh 21: Dari data penalti, tampilkan nomor pembayaran, jumlah penalti, nomor pemain dan nama serta inisial pemain tersebut!SQL:

SELECT PAYMENTNO, PENALTIES.PLAYERNO, AMOUNT, NAME, INITIALSFROM PENALTIES, PLAYERSWHERE PENALTIES.PLAYERNO = PLAYERS.PLAYERNO

Contoh 22: Cari nama dan inisial pemain yang telah bermain di dalam pertandingan !

SQL:

SELECT DISTINCT P.NAME, P.INITIALSFROM PLAYERS AS P, MATCHES AS MWHERE P.PLAYERNO = M.PLAYERNO

DML: Klausa Select (lebih dari 1 tabel) (contd-2)

Page 32: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

32

INSERT INTO <table specification>[<column list>]

VALUES (<expression> [{,<expression>}. . . ])

Contoh 23: Sebuah tim baru dengan pemain nomor 100 sebagai kaptennya telah bergabung di dalam liga. Tim ketiga ini akan bermain di dalam divisi ketiga.

SQL:

INSERT INTO TEAMS (TEAMNO, PLAYERNO, DIVISION)VALUES (3,100,'third')

atau:

INSERT INTO TEAMS VALUES (3,100,'third')

DML: Insert data ke dalam Tabel

Page 33: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

33

UPDATE <table specification>SET <column name = expression>[ WHERE <condition> ]

Contoh 24: Keluarga Parmenter telah pindah rumah. Sekarang mereka tinggal di Palmer Street nomor 83, kota Inglewood, kode pos 1234UU. Nomor telepon mereka yang baru belum diketahui.SQL:

UPDATE PLAYERSSET STREET = 'Palmer Street', HOUSENO = '83',

TOWN = 'Inglewood', POSTCODE = '1234UU',PHONENO = NULL

WHERE NAME = 'Parmenter'

Contoh 25: Naikkan jumlah penalti sebanyak 5%!

SQL:

UPDATE PENALTIESSET AMOUNT = AMOUNT * 1.05

DML: Update data dalam Tabel

Page 34: Basis Data I - Ifa's | My lecturing weblog Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data Data Definition Language

34

DELETEFROM <table specification>[ WHERE <condition> ]

Contoh 26: Hapus seluruh data penalti yang dilakukan oleh pemain nomor 44 pada tahun 1980!

Query:

DELETEFROM PENALTIESWHERE PLAYERNO = 44

AND YEAR(PAYMENT_DATE) = 1980

DML: Delete data dalam Tabel