TUGAS 2 MODUL 5

TUGAS MODUL 5

Table employee_join

Syntax:

SELECT * FROM employee_join;

Data pada table employee_join

Table job

Syntax:

SELECT * FROM job;

Data pada table job

1.      Buatlah function untuk menampilkan gabungan first_name dan last_name dengan bentuk “last_name, first_name”.

Syntax:

DELIMITER // CREATE FUNCTION revName( in_fist_name VARCHAR(15), in_last_name VARCHAR(15)) RETURNS VARCHAR(35) DETERMINISTIC READS SQL DATA BEGIN      RETURN CONCAT(in_fist_name,', ',in_last_name); END// DELIMITER ;

Function revName untuk memuncul kan first_name dan last_name

Syntax:

SELECT revName(first_name, last_name) FROM employee_join;

Hasil dari trigger revName

2.      Buatlah procedure untuk menampilkan job_description dari masukan sebuah id employee.

DELIMITER $$ CREATE PROCEDURE empJob(IN id_emp VARCHAR(11)) BEGIN      SELECT employee_join.first_name, employee_join.last_name, job.title FROM employee_join      JOIN job ON employee_join.job_id = job.job_id WHERE employee_join.id LIKE id_emp; END $$ DELIMITER ;

Procedure empJob untuk menampilan job_description

Syntax:

CALL empJob(2);

Tampilan pemanggilan procedure pada id=2

Review SMBD

RANCANGAN STRUKTUR TABEL DATABASE YG DIGUNAKAN (DLM BENTUK 3NF)

PRIMARY KEY & FOREIGN KEY

PERINTAH SQL

1.create database perpustakaan;

2. membuat table;

·         create table user (user_namevarchar(20) primary key not null,password varchar(20) not null,user char(1) default ‘5’check(level_user=‘1’ or level_user=‘2’ or level_user=‘3’ or level_user=‘4’ or level_user=‘5’));

·         create table anggota (kd_anggota char(6) primary key not null,nama_anggota varchar(50) not null,alamat varchar(50) not null,telepon varchar(12),jenis_anggota varchar(20));

·         create table jenis_buku (kd_jenis_buku char(4) primary key not null,nama_jenis_buku varchar(30) not null);

·         create table bidang  (kd_bidang char(4) primary key not null,nama_bidang varchar(30) not null);

·         create table penerbit (kd_penerbit char(4) primary key not null,nama_penerbit varchar(30) not null,alamat varchar(50) not null,kd_kecamatan char(6) not null,telepon varchar(12)not null,email varchar(20));

·         create table propinsi (kd_propinsi char(2)  primary key not null,nama_propinsi varchar(30) not null);

·         create table kabupaten (kd_kabupaten char(6) primary key not null,nama_kabupaten varchar(30) not null,kd_propinsi char(2) not null,foreign key(kd_propinsi) references propinsi on update cascade on delete cascade);

·         create table kecamatan (kd_kecamatan char(6) primary key not null,nama_kecamatan varchar(30) not null,kd_kabupaten char(6) not null,foreign key(kd_kabupaten) references kabupaten on update cascade on delete cascade);

·         create table buku (kd_buku char(10) primary key not null,judul_buku varchar(35) not null,kd_jenis char(4) not null,kd_bidang char(4) not null,kd_penulis char(4) not null,kd_penerbit char(4) not null,jumlah int(10) not null,tahun char(4) not null,jumlah_halaman mediumint(4) not null,edisi char(1) not null,cetakan_ke char(1) not null,status char(1) default ‘1’ check(status=’1’ or status=’2’),foreign key(kode_jenis) references jenis on update cascade on delete cascade,foreign key(kode_bidang) references bidang on update cascade on delete cascade,foreign key(kode_penulis) references penulis on update cascade on delete cascade,foreign key(kode_penerbit) references penerbit on update cascade on delete cascade);

·         create table penulis (kd_penulis char(4) primary key not null,nama_penulis varchar(30) not null,alamat varchar(50) not null,kd_kecamatan char(6) not null,telepon varchar(12),email varchar(20),foreign key(kode_kecamatan) references kecamatan on update cascade on delete cascade);

·         create table pinjam (Kd_pinjam char (6) primary key not null,kd_anggota char(6) not null,kd_buku char(10) not null,tanggal_pinjam date not null,tanggal _kembali date not null,foreign key(kd_anggota) references anggota on update cascade on delete cascade,foreign key(kd_buku) references buku on update cascade on delete cascade,primary key(kd_anggota, kd_buku, tanggal_pinjam);

·         create table kembali (Kd_pinjam char(6),Kd_kembali char(6),kd_anggota char(6) not null,kd_buku char(10) not null,tanggal_pinjam date not null,tanggal_ kembali date not null,foreign key(kd_anggota, kd_buku, tanggal_pinjam) references pinjam on update cascade on delete cascadeprimary key(kd_anggota, kd_buku, tanggal_pinjam);

·         create table bayar_denda (kd_anggota char(6) not null,kd_buku char(10) not null,

tanggal_pinjam date not null,tanggal_kembali date not null,

jumlah_denda numeric(9),foreign key(kd_anggota, kd_buku, tanggal_pinjam) references kembali on update cascade on delete cascade primary key(kd_anggota, kd_buku, tanggal_pinjam);

·         create table bayar_hilang (kd_pinjam char(6) not null,kd_anggota char(10) not null,

kd_buku char(6),tanggal_pinjam date not null,tanggal_bayar  date not null,jumlah  numeric(9), foreign key(kd_pinjam,kd_anggota, kd_buku, tanggal_pinjam) references kembali on update cascade on delete cascade primary key(kd_anggota, kd_buku, tanggal_pinjam);

3. select sum(jumlah) as jumlahsemuabuku from buku;

4. select judul_buku, nama_penerbit from penerbit left join buku on

buku.kode_penerbit=penerbit.kode_penerbit left join pinjam on buku.kode_buku=pinjam.kode_buku order by tanggal_pinjam;

5. select* from anggota left join pinjam on anggota.kode_anggota=pinjam.kode_anggota where pinjam.kode_anggota=not null order by tanggal_pinjam;

6. select anggota.kode_anggota,nama_anggota, alamat, kode_kecamatan, telepon, email, tgl_mulai_anggota, jenis_anggota, status_anggota from anggota left join bayar_denda on anggota.kode_anggota=bayar_denda.kode_anggota where bayar_denda.kode_anggota=not null order by nama_anggota;

7. select anggota.kode_anggota,nama_anggota, alamat, kode_kecamatan, telepon, email, tgl_mulai_anggota, jenis_anggota, status_anggota from anggota left join bayar_hilang on anggota.kode_anggota=bayar_hilang.kode_anggota where bayar_hilang.kode_anggota=not null order by nama_anggota;

8. select anggota.kode_anggota,nama_anggota, alamat, kode_kecamatan, telepon, email, tgl_mulai_anggota, jenis_anggota, status_anggota from anggota left join bayar_rusak on anggota.kode_anggota=bayar_rusak.kode_anggota where bayar_rusak.kode_anggota=not null order by nama_anggota;