SQLearning(UTS)

--SQLearning(UTS) Kasus
--LINK SOAL Hal1 :https://www.dropbox.com/s/1xxvx7pf0qxg51b/Kuis1.pdf?dl=0
--Link SOAL Hal2 :https://www.dropbox.com/s/tktwb19ejayze7f/Kuis2.pdf?dl=0

CREATE
DATABASE UTS
GO
USE UTS

--Kasus
--Buat Tabel

CREATE TABLE MsCustomer (
    KdCUstomer CHAR(5) PRIMARY KEY NOT NULL,
    NamaCustomer VARCHAR(30),
    AlamatCustomer CHAR(50),
    CONSTRAINT cekKd CHECK(
        KdCustomer LIKE 'CU[0-9][0-9][0-9]'
    )
)

CREATE TABLE TransPenjualan(
    KdTrans CHAR(5) PRIMARY KEY NOT NULL,
    KdBuku CHAR(5) NOT NULL,
    NIK CHAR(5) NOT NULL,
    KdCustomer CHAR(5),
    TglTrans DATE,
    Qty INTEGER,
    CONSTRAINT cekKdTrans CHECK(
    LEN(KdTrans)=5
    ),
    CONSTRAINT cekFK FOREIGN KEY (KdCustomer) REFERENCES MsCustomer(KdCustomer)
)

-- Hapus Tabel MsCustomer

ALTER TABLE TransPenjualan
DROP CONSTRAINT cekFK

DROP TABLE MsCustomer

--buat view
CREATE VIEW TrJual AS(
    SELECT *
    FROM TransPenjualan
    WHERE MONTH(TglTrans) BETWEEN 1 AND 8
)

SELECT *
FROM TrJual

-- tampilkan kode buku, kode penerbit, judul, harga >300000

SELECT KdBuku,KdPenerbit,Judul, Harga
FROM MsBuku
WHERE HARGA>300000

--berikan ijin yudi select insert ke tabel pejualan
GRANT SELECT,INSERT ON TransPenjualan TO Yudi