SQLearning(LOGIN, GRANT, REVOKE, ETC)

/*
CREATE DATABASE OOVEO_Salon
GO
USE OOVEO_Salon
*/

-- Create MsCustomer
CREATE TABLE MsCustomer(
    CustomerId CHAR(5) PRIMARY KEY,
    CustomerName VARCHAR(50),
    CustomerGender VARCHAR(10),
    CustomerPhone VARCHAR(13),
    CustomerAddress VARCHAR(100),
    CONSTRAINT cekIDCust CHECK(CustomerId LIKE 'CU[0-9][0-9][0-9]')
)

-- Create MsStaff
CREATE TABLE MsStaff(
    StaffId CHAR(5) PRIMARY KEY,
    StaffName VARCHAR(50),
    StaffGender VARCHAR(10),
    StaffPhone VARCHAR(13),
    StaffAddress VARCHAR(100),
    StaffSalary NUMERIC(11,2),
    StaffPosition VARCHAR(20),
    CONSTRAINT cekIDStaff CHECK(StaffId LIKE 'SF[0-9][0-9][0-9]')
)

-- Create MsTreatmentType
CREATE TABLE MsTreatmentType(
    TreatmentTypeId CHAR(5) PRIMARY KEY,
    TreatmentTypeName VARCHAR(50),
    CONSTRAINT cekIDTType CHECK(TreatmentTypeId LIKE 'TT[0-9][0-9][0-9]')
)

-- Create MsTreatment
CREATE TABLE MsTreatment(
    TreatmentId CHAR(5) PRIMARY KEY,
    TreatmentTypeId CHAR(5) REFERENCES MsTreatmentType ON UPDATE CASCADE ON DELETE CASCADE,
    TreatmentName VARCHAR(50),
    Price NUMERIC(11,2),
    CONSTRAINT cekIDTreat CHECK(TreatmentId LIKE 'TM[0-9][0-9][0-9]')
)

-- Create HeaderSalonServices
CREATE TABLE HeaderSalonServices(
    TransactionId CHAR(5) PRIMARY KEY,
    CustomerId CHAR(5) REFERENCES MsCustomer ON UPDATE CASCADE ON DELETE CASCADE,
    StaffId CHAR(5) REFERENCES MsStaff ON UPDATE CASCADE ON DELETE CASCADE,
    TransactionDate DATE,
    PaymentType VARCHAR(20),
    CONSTRAINT cekIDTrans CHECK(TransactionId LIKE 'TR[0-9][0-9][0-9]')
)

-- Create DetailSalonServices
CREATE TABLE DetailSalonServices(
    TransactionId CHAR(5) REFERENCES HeaderSalonServices ON UPDATE CASCADE ON DELETE CASCADE,
    TreatmentId CHAR(5) REFERENCES MsTreatment ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY(TransactionId, TreatmentId)
)

-- Insert Data
INSERT INTO MsCustomer VALUES
('CU001', 'Franky', 'Male', '08566543338', 'Daan mogot baru Street no 6'),
('CU002', 'Ernalia Dewi', 'Female', '085264782135', 'Tanjung Duren Street no 185'),
('CU003', 'Elysia Chen', 'Female', '085754206611', 'Kebon Jeruk Street no 120'),
('CU004', 'Brando Kartawijaya', 'Male', '081170225561', 'Greenvil Street no 88'),
('CU005', 'Andy Putra', 'Male', '087751321421', 'Sunter Street no 42')

INSERT INTO MsStaff VALUES
('SF001', 'Dian Felita Tanoto', 'Female', '085265442222', 'Palmerah Street no 56', 15000000, 'Top Stylist'),
('SF002', 'Mellisa Pratiwi', 'Female', '085755552011', 'Kebon Jeruk Street no 151', 10000000, 'Top Stylist'),
('SF003', 'Livia Ashianti', 'Female', '085218542222', 'Kebon Jeruk Street no 19', 7000000, 'Stylist'),
('SF004', 'Indra Saswita', 'Male', '085564223311', 'Sunter Street no 91', 7000000, 'Stylist'),
('SF005', 'Ryan Nixon Salim', 'Male', '085710255522', 'Kebon Jeruk Street no 123', 3000000, 'Stylist')

INSERT INTO MsTreatmentType VALUES
('TT001', 'Hair Treatment'),
('TT002', 'Message / Spa'),
('TT003', 'Beauty Care'),
('TT004', 'Nail Treatment'),
('TT005', 'Body Treatment')

INSERT INTO MsTreatment VALUES
('TM001', 'TT001','Cutting by Stylist', 150000),
('TM002', 'TT001','Cutting by Top Stylist', 450000),
('TM003', 'TT001','Cutting Pony', 50000),
('TM004', 'TT001','Blow', 90000),
('TM005', 'TT001','Coloring', 480000),
('TM006', 'TT001','Highlight', 320000),
('TM007', 'TT001','Japanese Perm', 700000),
('TM008', 'TT001','Digital Perm', 1100000),
('TM009', 'TT001','Special Perm', 1100000),
('TM010', 'TT001','Rebonding Treatment', 1100000),
('TM011', 'TT002','Creambath', 150000),
('TM012', 'TT002','Hair Spa', 250000),
('TM013', 'TT002','Hair Mask', 250000),
('TM014', 'TT002','Hand Spa Reflexy', 200000),
('TM015', 'TT002','Reflexy', 250000),
('TM016', 'TT002','Back Theraphy Massage', 300000),
('TM017', 'TT003','Make Up', 500000),
('TM018', 'TT003','Make Up Wedding', 5000000),
('TM019', 'TT003','Facial', 300000),
('TM020', 'TT004','Manicure', 80000),
('TM021', 'TT004','Pedicure', 100000),
('TM022', 'TT004','Nail Extension', 250000),
('TM023', 'TT004','Nail Acrylic Infill', 340000),
('TM024', 'TT005','Japanese Treatment', 350000),
('TM025', 'TT005','Scalp Treatment', 250000),
('TM026', 'TT005','Crystal Treatment', 400000)

INSERT INTO HeaderSalonServices VALUES
('TR001', 'CU001', 'SF004', '2012/12/20', 'Credit'),
('TR002', 'CU002', 'SF005', '2012/12/20', 'Credit'),
('TR003', 'CU003', 'SF003', '2012/12/20', 'Cash'),
('TR004', 'CU004', 'SF005', '2012/12/20', 'Debit'),
('TR005', 'CU005', 'SF003', '2012/12/21', 'Debit'),
('TR006', 'CU001', 'SF005', '2012/12/21', 'Credit'),
('TR007', 'CU002', 'SF001', '2012/12/22', 'Cash'),
('TR008', 'CU003', 'SF002', '2012/12/22', 'Credit'),
('TR009', 'CU005', 'SF004', '2012/12/22', 'Debit')

INSERT INTO DetailSalonServices VALUES
('TR001', 'TM001'),
('TR001', 'TM005'),
('TR002', 'TM010'),
('TR002', 'TM015'),
('TR003', 'TM025'),
('TR003', 'TM009'),
('TR004', 'TM001'),
('TR004', 'TM006'),
('TR004', 'TM015'),
('TR004', 'TM016'),
('TR005', 'TM016'),
('TR006', 'TM006'),
('TR006', 'TM015'),
('TR007', 'TM002'),
('TR007', 'TM005'),
('TR008', 'TM002'),
('TR008', 'TM006'),
('TR009', 'TM005'),
('TR009', 'TM006')

-- Display Data
SELECT * FROM MsCustomer
SELECT * FROM MsStaff
SELECT * FROM MsTreatment
SELECT * FROM MsTreatmentType
SELECT * FROM HeaderSalonServices
SELECT * FROM DetailSalonServices

/*
sp_msforeachtable'DROP TABLE ?'
sp_msforeachtable'SELECT * FROM ?'

DROP TABLE DetailSalonServices
DROP TABLE HeaderSalonServices
DROP TABLE MsTreatment
DROP TABLE MsTreatmentType
DROP TABLE MsCustomer
DROP TABLE MsStaff

DELETE FROM DetailSalonServices
DELETE FROM HeaderSalonServices
DELETE FROM MsTreatment
DELETE FROM MsTreatmentType
DELETE FROM MsCustomer
DELETE FROM MsStaff
*/


/*1.    Create a login named ‘ManagerUser’ that has
password ‘manager’.(create login)*/

CREATE LOGIN ManageUser WITH PASSWORD='Manager'

/*2.    Create a database user named ‘Manager’ for
login ‘ManagerUser’.(create user)*/

CREATE USER Manager FOR LOGIN ManageUser

/*3.    Create a login named ‘EmployeeUser’ that has
password ‘employee’(create login)*/

CREATE LOGIN EmployeeUser WITH PASSWORD='employee'

/*4.    Create a database user named ‘Employee’ for
login ‘EmployeeUser’.(create user)*/

CREATE USER Employee FOR LOGIN EmployeeUser

/*5.    Give all permission (select, insert, update,
delete, etc) to public towards MsTreatment table.
(grant all)*/

GRANT ALL ON MsTreatment TO PUBLIC

/*6.    Give the permissions to ‘Manager’ to do insert,
update, and delete towards MsStaff table and ‘Manager’
can also give the permission on MsStaff table to the
other users.(grant)*/

GRANT INSERT,UPDATE,DELETE ON MsStaff TO Manager WITH GRANT OPTION

/*7.    Give the permission to ‘Manager’ to do select
towards MsTreatment table.(grant)*/

GRANT SELECT ON MsTreatment TO Manager

/*8.    Delete all permissions on MsTreatment table
from public.(revoke)*/

REVOKE ALL ON MsTreatment FROM PUBLIC

/*9.    Delete the permission to select on MsTreatment
table from ’Manager’.(revoke)*/

REVOKE SELECT ON MsTreatment FROM Manager