Sqllllllll puyeng

 --1. MEMBUAT DATABASE

CREATE DATABASE PMUK_nama

USE PMUK_nama

--2. MEMBUAT TABEL t_kampus

CREATE TABLE t_kampus (idkampus char(10) PRIMARY KEY, namakampus VARCHAR(50),alamat VARCHAR(100),

telepon VARCHAR(15))

--3. MEMBUAT TABEL t_simpanpinjam

CREATE TABLE t_simpanpinjam (idsimpanpinjam CHAR(10) PRIMARY KEY,jenistabungan VARCHAR(20))

--4. MEMBUAT TABLE t_anggota

CREATE TABLE t_anggota(idanggota char(10) PRIMARY KEY,idsimpanpinjam CHAR(10) FOREIGN KEY REFERENCES

t_simpanpinjam(idsimpanpinjam),nama VARCHAR(50),alamat VARCHAR(100),saldo INT)

--5. MEMBUAT TABEL t_tabunganheader

CREATE TABLE t_tabunganheader (notransaksi CHAR(10) PRIMARY KEY,tgl_transaksi DATE,idkampus CHAR(10)

FOREIGN KEY REFERENCES t_kampus(idkampus))

--6. MEMBUAT TABEL t_tabungandetail

CREATE TABLE t_tabungandetail (notransaksi CHAR(10) FOREIGN KEY REFERENCES t_tabunganheader(notransaksi),

idanggota CHAR(10) FOREIGN KEY REFERENCES t_anggota(idanggota),jenistransaksi VARCHAR(50),

jmltransaksi INT)

--7. INPUT DATA SETIAP TABEL

INSERT INTO t_kampus VALUES('CB-0001','Kramat','Jl. Abdul Mandar 20','021-3101662')

INSERT INTO t_kampus VALUES('CB-0002','Pondok Gede','Jl. Pondok Gede Raya','021-908765')

INSERT INTO t_kampus VALUES('CB-0003','Jakut','Jl. Cilincing','021-789654')

INSERT INTO t_simpanpinjam VALUES('TB-001','Taplus')

INSERT INTO t_simpanpinjam VALUES('TB-002','Griya')

INSERT INTO t_simpanpinjam VALUES('TB-003','Umroh')

INSERT INTO t_anggota VALUES('000-01','TB-001','Anan','Jakarta Pusat',500000)

INSERT INTO t_anggota VALUES('000-02','TB-002','Indah','Jakarta Utara',700000)

INSERT INTO t_anggota VALUES('000-03','TB-003','Fahri','Jakarta Barat',200000)

INSERT INTO t_tabunganheader VALUES ('0001','2021-08-04','CB-0001')

INSERT INTO t_tabunganheader VALUES ('0002','2021-08-11','CB-0002')

INSERT INTO t_tabunganheader VALUES ('0003','2021-08-15','CB-0003')

INSERT INTO t_tabungandetail VALUES('0001','000-01','S',500000)

INSERT INTO t_tabungandetail VALUES('0002','000-02','S',800000)

INSERT INTO t_tabungandetail VALUES('0003','000-03','T',300000)

SELECT * FROM t_kampus

SELECT * FROM t_simpanpinjam

SELECT * FROM t_tabunganheader

SELECT * FROM t_anggota

SELECT * FROM t_tabungandetail

--8. Buatlah script untuk menampilkan seluruh data dari t_anggota

    SELECT * FROM t_anggota

--9. Buatlah script untuk menampilkan data dari t_anggota dan t_kampus menggunakan

--inner join dan data yang muncul adalah semua data

    SELECT k.idkampus,k.namakampus,k.alamat,k.telepon,

    a.idanggota,a.idsimpanpinjam,a.nama,a.alamat,a.saldo FROM t_kampus as k

    INNER JOIN t_tabunganheader as th ON k.idkampus=th.idkampus

    INNER JOIN t_tabungandetail as td ON th.notransaksi=td.notransaksi

    INNER JOIN t_anggota as a ON td.idanggota=a.idanggota

   

--10. Buatlah view 4 tabel t_anggota, t_kampus, t_tabunganheader dan t_tabungandetail

--disimpan dengan nama view_tabungan

   

    CREATE VIEW view_tabungan AS

    SELECT k.idkampus,k.namakampus,a.idanggota,a.idsimpanpinjam,

    a.nama,a.saldo,th.notransaksi,th.tgl_transaksi,td.jenistransaksi,

    td.jmltransaksi FROM t_kampus as k

    INNER JOIN t_tabunganheader as th ON k.idkampus=th.idkampus

    INNER JOIN t_tabungandetail as td ON th.notransaksi=td.notransaksi

    INNER JOIN t_anggota as a ON td.idanggota=a.idanggota

--11. Tampilkan data dari view dengan kriteria saldo kurang dari 500.000

SELECT * FROM view_tabungan WHERE saldo < 500000

--12. Tampilkan data dari view dengan kriteria lebih dari 500.000 dan

--nama anggota diawali huruf i

SELECT * FROM view_tabungan WHERE saldo > 500000 AND nama like 'i%'

--13. Buatlah pengelompokan data menggunakan SELECT dan GROUP BY berdasarkan

--view_tabungan, dan pengelompokan berdasarkan nomor transaksi

SELECT notransaksi, SUM(saldo) as saldo,SUM(jmltransaksi)as jml

 FROM view_tabungan GROUP BY notransaksi  

--14. Hapus data pada tabel t_tabunganheader dan t_tabungandetail masing2

--1 record dengan kriteria notransaksi = 0001

    delete from t_tabunganheader where notransaksi = '0001'

    delete from t_tabungandetail where notransaksi = '0001'

--15. Buat trigger pada tabel t_tabungandetail untuk insert dengan no transaksi

--dan lakukan update pada tabel t_anggota, dengan ketentuan

--jika jenistransaksi = S maka saldo = saldo + jmltransaksi

--jika jenistransaksi = M maka saldo = saldo - jmltransaksi

GO

CREATE TRIGGER tg_update_saldo ON t_tabungandetail AFTER INSERT

AS BEGIN

    IF exists(SELECT jenistransaksi FROM inserted 

            where jenistransaksi = 'S')

    BEGIN 

        UPDATE t_anggota SET saldo = saldo + jmltransaksi

        FROM t_anggota JOIN inserted

        ON t_anggota.idanggota = inserted.idanggota

    END ELSE 

    BEGIN

        UPDATE t_anggota SET saldo = saldo - jmltransaksi

        FROM t_anggota JOIN inserted

        ON t_anggota.idanggota = inserted.idanggota

    end

end   

GO

SELECT * FROM t_tabungandetail

SELECT * FROM t_anggota

INSERT INTO t_tabungandetail VALUES('0003','000-03','S',75000)

--16. Buat store procedure untuk menambahkan data pada tabel t_tabunganheader

GO

CREATE PROCEDURE ps_tambah_header

    @notransaksi CHAR(10),

    @tgl_transaksi DATE,

    @idkampus CHAR(10)

AS BEGIN

    INSERT INTO t_tabunganheader VALUES(@notransaksi,

                    @tgl_transaksi,@idkampus)

    SELECT * FROM t_tabunganheader

END

GO 

--17. Buat store procedure untuk menambahkan data pada tabel t_tabungandetail

GO

CREATE PROCEDURE ps_tambah_detail

    @notransaksi CHAR(10),

    @idanggota CHAR(10),

    @jenistransaksi VARCHAR(50),

    @jmltransaksi INT

AS BEGIN

    INSERT INTO t_tabungandetail VALUES(@notransaksi,

                    @idanggota,@jenistransaksi,@jmltransaksi)

    SELECT * FROM t_tabungandetail

END

GO 

--18. Gunakan commit atau Rollback setelah melakukan penambahan data menggunakan store procedure

BEGIN TRAN

EXECUTE ps_tambah_detail '0004','000-01','T',50000

COMMIT

ROLLBACK

--19. Buatlah laporan transaksi pada ms excel dengan cara import data SQL