Query Penjualan

-- PERTEMUAN 1 - DDL --

//Berdasarkan relasi antar tabel di atas, Buatlah perintah untuk mendefinisikan atau membuat tabel (definisikan PRIMARY KEY, FOREIGN KEY (jika ada) dan sifat-sifat yang lainnya (NULL atau NOT NULL)
CREATE DATABASE PENJUALAN0276
ON(
NAME = PENJUALAN0276_dat,
FILENAME = 'R:\Materi Kuliah\Semester 4 - Genap\Perancangan Basis Data\Database dan Query Tugas\dbPENJUALAN0276.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5)
LOG ON(
NAME = PENJUALAN0276_log,
FILENAME = 'R:\Materi Kuliah\Semester 4 - Genap\Perancangan Basis Data\Database dan Query Tugas\dbPENJUALAN0276.Ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB)

USE PENJUALAN0276

CREATE TABLE pelanggan(
kd_pelanggan CHAR(5) PRIMARY KEY NOT NULL,
nama_pelanggan VARCHAR(100) NOT NULL,
alamat VARCHAR(100) NOT NULL,
no_telpon VARCHAR(12))

CREATE TABLE kasir(
kd_kasir CHAR(5) PRIMARY KEY NOT NULL,
nama_kasir VARCHAR(100) NOT NULL,
password VARCHAR(30) NOT NULL)

CREATE TABLE barang(
kode_barang CHAR(5) PRIMARY KEY NOT NULL,
nama_barang VARCHAR(100) NOT NULL,
harga_satuan NUMERIC(18,0) NOT NULL)

CREATE TABLE nota(
no_nota CHAR(5) PRIMARY KEY NOT NULL,
kd_pelanggan CHAR(5) FOREIGN KEY REFERENCES pelanggan(kd_pelanggan) NOT NULL,
kd_kasir CHAR(5) FOREIGN KEY REFERENCES kasir(kd_kasir) NOT NULL)

CREATE TABLE detail_nota(
kd_nota CHAR(5) FOREIGN KEY REFERENCES nota(no_nota) NOT NULL,
kd_barang CHAR(5) NOT NULL,
jumlah_barang INT NOT NULL,
tgl_transaksi datetime NOT NULL)

ALTER TABLE detail_nota DROP COLUMN tgl_transaksi //Hapus FIELD tgl_transaksi dari tabel detail nota

ALTER TABLE nota ADD tgl_nota datetime NOT NULL //Tambahkan FIELD tgl_nota pada tabel Nota

ALTER TABLE detail_nota ADD harga_barang NUMERIC(18,0) NOT NULL //Tambahkan FIELD harga_barang pada detail_nota

ALTER TABLE detail_nota  ADD FOREIGN KEY (kd_barang) REFERENCES barang(kode_barang) //Tambahkan FOREIGN KEY pada FIELD kd_barang tabel detailnota mengacu pada kode_barang di tabel barang

ALTER TABLE kasir ALTER COLUMN nama_kasir VARCHAR(50) //Ubah tipe DATA nama_kasir menjadi VARCHAR(50)

//Isikan DATA berikut pada tabel yang sudah dibuat
INSERT INTO barang VALUES ('B0001','Rinso 50 Gram',3000)
INSERT INTO barang VALUES ('B0002','Nestle 300 ml',2500)
INSERT INTO barang VALUES ('B0003','Taro 450 Gram',8500)
INSERT INTO barang VALUES ('B0004','Bolpen Snowman',1500)
INSERT INTO barang VALUES ('B0005','Gulaku 1Kg',20000)

INSERT INTO kasir VALUES ('K0001','Karno','abc123')
INSERT INTO kasir VALUES ('K0002','Mirna','jesica123')

INSERT INTO pelanggan (kd_pelanggan,nama_pelanggan,alamat,no_telpon) VALUES ('P0001','Aprilia','Bantul','0812000111')
INSERT INTO pelanggan (kd_pelanggan,nama_pelanggan,alamat,no_telpon) VALUES ('P0002','Zulkarnain','Yogyakarta','')
INSERT INTO pelanggan (kd_pelanggan,nama_pelanggan,alamat,no_telpon) VALUES ('P0003','Andika','Sleman','')
INSERT INTO pelanggan (kd_pelanggan,nama_pelanggan,alamat,no_telpon) VALUES ('P0004','Parno','Yogyakarta','0856222999')

SELECT *FROM barang
SELECT *FROM detail_nota
SELECT *FROM kasir
SELECT *FROM nota
SELECT *FROM pelanggan

-- PERTEMUAN 2 - DML --

*1*
USE PENJUALAN0276

*2*
ALTER TABLE kasir ADD Level_Kasir CHAR(2)

*3*
ALTER TABLE barang ADD Stok INT

INSERT INTO kasir VALUES ('K0003','Mirnawati rahmasari','mirna5678','')
INSERT INTO kasir VALUES ('K0004','Endang sari sukarno','endang123','')
INSERT INTO kasir VALUES ('K0005','Edi rahmawan','edi99','')

INSERT INTO pelanggan VALUES ('P0005','Indah','Yogya',NULL)
INSERT INTO pelanggan VALUES ('P0006','Raihan eka', 'Sleman',NULL)
INSERT INTO pelanggan VALUES ('P0007','Ekasari','Yogya', '876265')

INSERT INTO barang VALUES ('B0006','Rinso 100 gram',5000,'')
INSERT INTO barang VALUES ('B0007','Detergen cair Rinso 100 ml',6000,'')
INSERT INTO barang VALUES ('B0008','Gulaku stick',1000,'')

INSERT INTO nota VALUES('N0001','P0001','K0001','2019-03-03 10:15:00')
INSERT INTO nota VALUES('N0002','P0002','K0002','2019-03-04 10:10:00')
INSERT INTO nota VALUES('N0003','P0004','K0002','2019-03-04 10:13:00')
INSERT INTO nota VALUES('N0004','P0001','K0004','2019-03-05 11:15:00')
INSERT INTO nota VALUES('N0005','P0004','K0001','2019-03-05 15:20:00')
INSERT INTO nota VALUES('N0006','P0002','K0004','2019-03-05 15:25:00')
INSERT INTO nota VALUES('N0007','P0003','K0002','2019-03-06 12:03:00')

INSERT INTO detail_nota VALUES('N0001','B0001',3,2850)
INSERT INTO detail_nota VALUES('N0001','B0002',2,2500)
INSERT INTO detail_nota VALUES('N0001','B0005',1,18500)
INSERT INTO detail_nota VALUES('N0002','B0007',1,6000)
INSERT INTO detail_nota VALUES('N0003','B0003',2,8450)
INSERT INTO detail_nota VALUES('N0003','B0004',10,1500)
INSERT INTO detail_nota VALUES('N0004','B0001',3,2800)
INSERT INTO detail_nota VALUES('N0004','B0008',10,1000)
INSERT INTO detail_nota VALUES('N0004','B0003',1,8500)
INSERT INTO detail_nota VALUES('N0005','B0003',5,8500)
INSERT INTO detail_nota VALUES('N0006','B0007',1,6000)
INSERT INTO detail_nota VALUES('N0006','B0002',1,2500)
INSERT INTO detail_nota VALUES('N0006','B0005',1,20000)
INSERT INTO detail_nota VALUES('N0007','B0003',4,8500)

*5*
UPDATE kasir SET Level_Kasir = 'AK' WHERE kd_kasir = 'K0001'

*6*
UPDATE kasir SET level_Kasir = 'SK' WHERE kd_kasir <> 'K0001'

*7*
UPDATE barang SET stok = 100

*8*
UPDATE kasir SET password = 'jogja0912' WHERE kd_kasir = 'K0001'

*9*
UPDATE pelanggan SET nama_pelanggan = 'andika zulkarnain', alamat = 'Jl.Nusa indah 101 Concat Depok Sleman', no_telpon = 0819243666 WHERE kd_pelanggan = 'P0002'

*10*
UPDATE barang SET harga_satuan = 21500 WHERE kode_barang = 'B0005'

*11*
DELETE FROM pelanggan WHERE kd_pelanggan = 'P0007'

*12*
UPDATE pelanggan SET alamat = 'yogyakarta' WHERE alamat ='yogya'

*13*
SELECT kd_kasir, nama_kasir FROM kasir WHERE nama_kasir LIKE '%mirna%'

*14*
SELECT kd_pelanggan, nama_pelanggan, no_telpon FROM pelanggan WHERE no_telpon IS NOT NULL

*15*
SELECT DISTINCT(alamat) FROM pelanggan

*16*
SELECT *FROM barang ORDER BY nama_barang ASC

*17*
SELECT kode_barang, nama_barang, harga_satuan FROM barang WHERE harga_satuan > 3000

*18*
SELECT kode_barang, nama_barang, harga_satuan FROM barang WHERE nama_barang LIKE '%rinso%'

*19*
SELECT SUM(stok) FROM barang

*20*
SELECT n.no_nota, n.tgl_nota, p.nama_pelanggan
FROM nota n JOIN pelanggan p
ON n.kd_pelanggan = p.kd_pelanggan
WHERE p.kd_pelanggan = 'P0001' OR p.kd_pelanggan = 'P0004'

*21*
SELECT b.kode_barang, b.nama_barang, SUM(d.jumlah_barang) AS jumlah_terjual
FROM barang b JOIN detail_nota d
ON b.kode_barang = d.kd_barang
GROUP BY b.kode_barang, b.nama_barang

*22*
SELECT p.nama_pelanggan, p.kd_pelanggan, COUNT(n.no_nota) AS jumlah_transaksi
FROM pelanggan p JOIN nota n
ON p.kd_pelanggan = n.kd_pelanggan
GROUP BY p.nama_pelanggan, p.kd_pelanggan
ORDER BY COUNT(n.no_nota) DESC

*23*
SELECT n.no_nota, n.tgl_nota, p.nama_pelanggan, COUNT(n.no_nota) AS total_transaksi, SUM(d.jumlah_barang) AS total_item
FROM nota n JOIN pelanggan p
ON n.kd_pelanggan = p.kd_pelanggan
JOIN detail_nota d
ON n.no_nota = d.kd_nota
GROUP BY n.no_nota, n.tgl_nota, p.nama_pelanggan

*24*
SELECT n.no_nota, n.tgl_nota, p.nama_pelanggan, COUNT(n.no_nota) AS total_transaksi
FROM nota n JOIN pelanggan p
ON n.kd_pelanggan = p.kd_pelanggan
WHERE n.kd_pelanggan = 'P0002'
GROUP BY n.no_nota, n.tgl_nota, p.nama_pelanggan

*25*
SELECT n.no_nota, b.kode_barang, b.nama_barang, d.jumlah_barang, d.harga_barang, SUM(d.harga_barang*d.jumlah_barang) AS subtotal
FROM nota n JOIN detail_nota d
ON n.no_nota = d.kd_nota
JOIN barang b
ON d.kd_barang = b.kode_barang
GROUP BY n.no_nota, b.kode_barang, b.nama_barang, d.jumlah_barang, d.harga_barang

*26*
SELECT *
FROM detail_nota d JOIN nota n
ON d.kd_nota = n.no_nota
WHERE n.tgl_nota BETWEEN '2019-03-05' AND '2019-03-10'

*27*
SELECT k.kd_kasir, k.nama_kasir, SUM(d.harga_barang*d.jumlah_barang) AS total_uang
FROM kasir k JOIN nota n
ON k.kd_kasir = n.kd_kasir
JOIN detail_nota d
ON n.no_nota = d.kd_nota
GROUP BY k.kd_kasir, k.nama_kasir

*28*
SELECT k.kd_kasir, k.nama_kasir, COUNT(n.no_nota) AS jumlah_transaksi, COUNT(n.no_nota)*250 AS bonus
FROM kasir k JOIN nota n
ON k.kd_kasir = n.kd_kasir
GROUP BY k.kd_kasir, k.nama_kasir

*29*
SELECT n.no_nota, n.tgl_nota, k.kd_kasir, k.nama_kasir, p.kd_pelanggan, p.nama_pelanggan, b.kode_barang, b.nama_barang, d.jumlah_barang, d.harga_barang,
SUM(d.jumlah_barang*d.harga_barang)
FROM nota n JOIN kasir k
ON n.kd_kasir = k.kd_kasir
JOIN pelanggan p
ON p.kd_pelanggan = n.kd_pelanggan
JOIN detail_nota d
ON d.kd_nota = n.no_nota
JOIN barang b
ON b.kode_barang = d.kd_barang
GROUP BY n.no_nota, n.tgl_nota, k.kd_kasir, k.nama_kasir, p.kd_pelanggan, p.nama_pelanggan, b.kode_barang, b.nama_barang, d.jumlah_barang, d.harga_barang

*30*
SELECT b.kode_barang, b.nama_barang, b.harga_satuan, SUM(d.jumlah_barang) AS terjual, b.stok-SUM(d.jumlah_barang) AS tersedia
FROM barang b JOIN detail_nota d
ON b.kode_barang = d.kd_barang
GROUP BY b.kode_barang, b.nama_barang, b.harga_satuan, b.stok

-- Pertemuan 3 - VIEW dan Stored Procedure --

--1. Buat View DaftarHargaBarang  untuk menampilkan Kode barang, nama barang, harga barang --
CREATE VIEW VDaftarHargaBarang AS SELECT kode_barang, nama_barang, harga_satuan FROM barang

SELECT * FROM VDaftarHargaBarang

-- 2. Simpan query no 26 ke view VTampilBonusKasir --
CREATE VIEW VTampilBonusKasir AS
SELECT *
FROM detail_nota d JOIN nota n
ON d.kd_nota = n.no_nota
WHERE n.tgl_nota BETWEEN '2019-03-05' AND '2019-03-10'

SELECT * FROM VTampilBonusKasir

-- 3. Simpan query no 27 ke view VTampilPenjualan --
CREATE VIEW VTampilPenjualan AS
SELECT k.kd_kasir, k.nama_kasir, SUM(d.harga_barang*d.jumlah_barang) AS total_uang
FROM kasir k JOIN nota n
ON k.kd_kasir = n.kd_kasir
JOIN detail_nota d
ON n.no_nota = d.kd_nota
GROUP BY k.kd_kasir, k.nama_kasir

SELECT * FROM VTampilPenjualan

-- 4. Simpan query no 28 ke view VDaftarBarang --
CREATE VIEW VDaftarBarang AS
SELECT k.kd_kasir, k.nama_kasir, COUNT(n.no_nota) AS jumlah_transaksi, COUNT(n.no_nota)*250 AS bonus
FROM kasir k JOIN nota n
ON k.kd_kasir = n.kd_kasir
GROUP BY k.kd_kasir, k.nama_kasir

SELECT * FROM VDaftarbarang

-- 5. Simpan query no 16 ke sebuah stored procedure, mencari data barang berdasarkan kemiripan nama yang diinputkan --
CREATE PROC SpDataBarang
AS BEGIN
SELECT
    *
    FROM
    barang
    ORDER BY
    nama_barang ASC
END

EXEC SpDataBarang

-- 6. Buat stored procedure untuk menampilkan penjualan berdasarkan no_nota yang diinputkan (gunakan query no 27) --

-- 7. Buat stored procedure untuk menampilkan penjualan berdasarkan tanggal awal dan tanggal akhir yang diinputkan (gunakan query no 27) --

-- 8. Buat stored procedure untuk insert nota --
CREATE PROC InsertNota (@no_nota CHAR(5),
@kd_pel CHAR(5), @kd_kas CHAR(5), @tgl_not datetime)
AS
INSERT INTO kasir(no_nota, kd_pelanggan, kd_kasir, tgl_nota)
VALUES (@no_nota, @kd_pel, @kd_kas, @tgl_not)

-- 9. Buat stored procedure untuk insert detail_nota yang sekaligus mengurangi nilai stok dibarang --
CREATE PROC Insert_Detail_Nota
(
@kd_nota CHAR(5),
@kd_barang CHAR(5),
@jumlah_barang INT,
@harga_barang NUMERIC(18,0),
@StatementType nvarchar(20) = ''
)
AS
BEGIN
IF @StatementType = 'Insert'
BEGIN
INSERT INTO detail_nota (kd_nota, kd_barang, jumlah_barang, harga_barang)
VALUES(@kd_nota, @kd_barang, @jumlah_barang, @harga_barang)
END
IF @StatementType = 'Update'
BEGIN
UPDATE detail_nota SET
jumlah_barang = @jumlah_barang
WHERE kd_nota = @kd_nota
END
END

EXEC Insert_Detail_Nota 'N0006','B0005','9','5000','Update';

SELECT *FROM detail_nota

-- 10. Buat stored procedure untuk mengubah password kasir lama dengan password kasir baru (inputan : kode, pwd_lama, pwd_baru) --
CREATE PROC SpGantiPassKasir
 @kd_kasir CHAR(5),
 @pOldPassword VARCHAR(50),
 @pNewPassword VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;

UPDATE dbo.kasir
    SET st01Password = HASHBYTES('md5', @pNewPassword)
    WHERE st01Guid = @pGuid
    AND st01Password = HASHBYTES('md5', @pOldPassword);

IF @@ROWCOUNT = 0
    RETURN -1;

  RETURN 0;
END
GO

SELECT *FROM kasir