Labsheet 7

Labsheet 7 – SQL Lengkap

1. CREATE DATABASE

CREATE DATABASE roaster_rs;
USE roaster_rs;

2. CREATE TABLE

Tabel spesialis

CREATE TABLE spesialis (
    kode_spesialis VARCHAR(5) PRIMARY KEY,
    jns_spesialis VARCHAR(50)
);

Tabel dokter

CREATE TABLE dokter (
    kode_dokter VARCHAR(5) PRIMARY KEY,
    nm_dokter VARCHAR(100),
    kode_spesialis VARCHAR(5),
    jk CHAR(1),
    alamat VARCHAR(100),
    telp VARCHAR(15),
    FOREIGN KEY (kode_spesialis) REFERENCES spesialis(kode_spesialis)
);

Tabel roaster

CREATE TABLE roaster (
    kode_dokter VARCHAR(5),
    hari VARCHAR(20),
    jam_awal TIME,
    jam_akhir TIME,
    FOREIGN KEY (kode_dokter) REFERENCES dokter(kode_dokter)
);

3. INSERT DATA

Insert tabel spesialis

INSERT INTO spesialis VALUES
('du', 'Dokter Umum'),
('jtg', 'Spesialis Jantung'),
('paru', 'Spesialis Paru'),
('ank', 'Spesialis Anak'),
('tht', 'Spesialis Telinga Hidung Tenggorokan');

Insert tabel dokter

INSERT INTO dokter VALUES
('D001','dr. Nuriana','du','P','Jl. M.Yamin No.12','08725667575'),
('D002','dr. Sapto Mulyadi, Sp.J','jtg','L','Jl. Wahid Hasyim RT.8','08872657656'),
('D003','dr. Deni Rianto','du','L','Jl. DR. Sutomo Gg.1','08276476565'),
('D004','dr. Henry Cahyadi, Sp.P','paru','L','Jl.A.Yani N0.98','08117675656'),
('D005','dr. Nanan Wijaya, Sp.A','ank','L','Jl. Kusuma Bangsa No.7','08116756545'),
('D006','dr. Sarah Yasmina, Sp.A','ank','P','Jl.M.Yamin No.34','08128766544'),
('D007','dr. Erick Jaya, Sp.THT','tht','L','Jl. DR.Sutomo Gg.5','08138767665'),
('D008','dr. Wayan Bhakti','du','L','Jl.Suryanata Gg.2','08259878767'),
('D009','dr. Ahmad H, Sp.P','paru','L','Jl. Kadrie Oening No76','08123665467'),
('D010','dr. Andini Anastasia','du','P','Jl.Wahid Hasyim No.145','08652454373');

Insert tabel roaster

INSERT INTO roaster VALUES
('D001','Senin','07:00','15:00'),
('D002','Senin','07:00','15:00'),
('D003','Senin','14:00','22:00'),

('D004','Selasa','07:00','15:00'),
('D005','Selasa','14:00','22:00'),
('D006','Selasa','07:00','15:00'),
('D007','Selasa','14:00','22:00'),

('D008','Rabu','07:00','15:00'),
('D009','Rabu','14:00','22:00'),

('D010','Kamis','07:00','15:00'),
('D001','Kamis','14:00','22:00'),
('D010','Kamis','07:00','15:00'),

('D004','Jumat','07:00','15:00'),
('D005','Jumat','14:00','22:00'),
('D003','Jumat','07:00','15:00'),
('D002','Jumat','14:00','22:00');

---

PENGGUNAAN VIEW

Membuat view vw_dokter

CREATE VIEW vw_dokter AS
SELECT kode_dokter, nm_dokter FROM dokter;

Menampilkan view

SELECT * FROM vw_dokter;

Membuat view dengan nama field berbeda

DROP VIEW vw_dokter;

CREATE VIEW vw_dokter(kd_dok, nm_dok) AS
SELECT kode_dokter, nm_dokter FROM dokter;

View lebih dari satu tabel

SELECT kode_dokter, nm_dokter, jns_spesialis
FROM dokter, spesialis
WHERE dokter.kode_spesialis = spesialis.kode_spesialis;

---

JAWABAN TUGAS VIEW

1. View vw_jk

CREATE VIEW vw_jk AS
SELECT 
    jk AS jenis_kelamin,
    COUNT(*) AS jumlah
FROM dokter
GROUP BY jk;

2. View jadwal_dokter

CREATE VIEW jadwal_dokter AS
SELECT
    d.nm_dokter AS nama_dokter,
    r.hari,
    r.jam_awal
FROM dokter d
JOIN roaster r ON d.kode_dokter = r.kode_dokter;

3. View jumlah dokter jaga per hari

CREATE VIEW vw_jumlah_dokter_hari AS
SELECT 
    hari,
    COUNT(kode_dokter) AS jumlah_dokter
FROM roaster
GROUP BY hari;