SQLearning(VIEW, SUBSTRING, CHARINDEX,etc)

-- SQL LEARNING, (VIEW,LEN,SUBSTRING,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

/*1. Create a view named ‘ViewBonus’ to display BonusId (obtained from CustomerId
by replacing the first 2 characters with ‘BN’) and CustomerName where CustomerName
is greater than 10 characters.
(create view, stuff, len)*/

SELECT  *
FROM MsCustomer

DROP VIEW ViewBonus

CREATE VIEW ViewBonus AS
SELECT BonusId= STUFF(CustomerId,1,2,'BN'), CustomerName
FROM msCustomer
WHERE LEN(CustomerName)>10

SELECT *
FROM ViewBonus

/*2. Create a view named ‘ViewCustomerData’ to display Name (obtained from the first
name of CustomerName), Address (obtained from CustomerAddress) and Phone (obtained from
CustomerPhone) where CustomerName is more than one word.
(create view, substring, charindex)
*/

SELECT  *
FROM MsCustomer

CREATE VIEW ViewCustomerData AS
SELECT Name=SUBSTRING(CustomerName,1,CHARINDEX(' ',CustomerName,0)),[Address]=CustomerAddress, Phone=CustomerPhone
FROM MsCustomer
WHERE CHARINDEX(' ',CustomerName,0)>0

SELECT *
FROM ViewCustomerData

/*3. Create a view named ‘ViewTreatment’ to display TreatmentName, TreatmentTypeName,
Price (obtained from Price by adding ‘Rp. ’ in front of it) where TreatmentTypeName is
‘Hair Treatment’ and Price is between 450000 and 800000.
(create view, cast, between)
*/

SELECT MTT.TreatmentTypeId,TreatmentId,TreatmentTypeName,TreatmentName,Price
FROM MsTreatmentType MTT,MsTreatment MT
WHERE MTT.TreatmentTypeId=MT.TreatmentTypeId

CREATE VIEW ViewTreatment AS
SELECT TreatmentName,TreatmentTypeName,Price='Rp. '+CAST(Price AS VARCHAR)
FROM MsTreatmentType MTT,MsTreatment MT
WHERE MTT.TreatmentTypeId=MT.TreatmentTypeId AND PRICE BETWEEN 450000 AND 800000 AND TreatmentTypeName='Hair Treatment'

SELECT *
FROM ViewTreatment

/*4. Create a view named ‘ViewTransaction’ to display StaffName, CustomerName, TransactionDate
(obtained from TransactionDate with format ‘dd Mon yyyy’), and PaymentType where the transaction
 happened between 21 and 25 and PaymentType is ‘Credit’.
(create view, convert, day, between)
*/

SELECT *
FROM HeaderSalonServices,MsStaff,MsCustomer

CREATE VIEW ViewTransaction AS
SELECT StaffName,CustomerName,TransactionDate=CONVERT(VARCHAR,TransactionDate,103), PaymentType
FROM MsStaff MS,MsCustomer MC, HeaderSalonServices HSS
WHERE MS.StaffId=HSS.StaffId AND MC.CustomerId=HSS.CustomerId AND DAY(TransactionDate) BETWEEN 21 AND 25 AND PaymentType='Credit'

SELECT *
FROM ViewTransaction

/*5. Create a view named ‘ViewBonusCustomer’ to display BonusId (obtained from CustomerId by
replacing ‘CU’ with ‘BN’), Name (obtained from the last name of Customer with lowercase format),
Day (obtained from name of the day when the transaction happened), and TransactionDate (obtained
from TransactionDate with format ‘mm/dd/yyyy’) where CustomerName is more than one word and Name
contains ‘a’ character.
(create view, replace, lower, substring, charindex, len, datename, convert, like)
*/

SELECT *
FROM HeaderSalonServices,MsCustomer

CREATE VIEW ViewBonusCustomer AS
SELECT BonusId=REPLACE(MC.CustomerId,'CU','BN'),Name=LOWER(SUBSTRING(CustomerName,CHARINDEX(' ',CustomerName)+1,LEN(CustomerName))),
    [DAY]=DATENAME(WEEKDAY,TransactionDate), TransactionDate=CONVERT(VARCHAR,TransactionDate,101)
FROM HeaderSalonServices HSS, MsCustomer MC
WHERE HSS.CustomerId=MC.CustomerId AND SUBSTRING(CustomerName,CHARINDEX(' ',CustomerName)+1,LEN(CustomerName)) LIKE '%a%'AND CHARINDEX(' ',CustomerName)>0

SELECT *
FROM ViewBonusCustomer

/*6. Create a view named ‘ViewTransactionByLivia’ to display TransactionId, Date (obtained from
TransactionDate with format ‘Mon dd, yyyy’), and TreatmentName where Staff that handle the
transaction named ‘Livia Ashianti’ and date of transaction was occurred on 21.
(create view, convert, like, day)
*/

SELECT *
FROM HeaderSalonServices, DetailSalonServices, MsStaff, MsTreatment

CREATE VIEW ViewTransactionByLivia AS
SELECT HSS.TransactionId, [DATE]=CONVERT(VARCHAR,TransactionDate,107), TreatmentName
FROM HeaderSalonServices HSS, DetailSalonServices DSS, MsStaff MS, MsTreatment MT
WHERE StaffName LIKE 'Livia Ashianti' AND DAY(TransactionDate)=21 AND HSS.TransactionId=DSS.TransactionId AND HSS.StaffId=MS.StaffId AND DSS.TreatmentId=MT.TreatmentId

SELECT *
FROM ViewTransactionByLivia

/*7. Changing the view named ‘ViewCustomerData’ becomes displaying ID (obtained from the last 3
digit characters of CustomerId), Name (obtained from CustomerName), Address (obtained from
CustomerAddress), and Phone (obtained from CustomerPhone) where CustomerName is more than one word.
(alter, right, charindex)
*/

SELECT *
FROM ViewCustomerData

ALTER VIEW ViewCustomerData AS
SELECT ID=RIGHT(CustomerId,3),Name=CustomerName,[Address]=CustomerAddress,Phone=CustomerPhone
FROM MsCustomer
WHERE CHARINDEX(' ',CustomerName)>1

SELECT *
FROM ViewCustomerData

/*8.1 Create a view named ‘ViewCustomer’ to display CustomerId, CustomerName and CustomerGender.
Then add the data to ViewCustomer with the following specifications:
  8.2 Then display all data on MsCustomer table.
*/

SELECT *
FROM MsCustomer

CREATE VIEW ViewCustomer AS
SELECT CustomerId,CustomerName,CustomerGender
FROM MsCustomer

--Direct Insert
INSERT INTO MsCustomer(CustomerId,CustomerName,CustomerGender) VALUES ('CU006','Cristian','Male')
--View Insert
INSERT INTO ViewCustomer SELECT 'CU007','Alexander Kevin','Male'

SELECT *
FROM MsCustomer

/*9. Delete data in view ‘ViewCustomerData’ that has ID ‘005’.
Then display all data from MsCustomer table.*/

SELECT *
FROM ViewCustomerData

DELETE FROM ViewCustomerData
WHERE ID='005'

SELECT *
FROM MsCustomer

/*10.   Delete the view ‘ViewCustomerData’. */

DROP VIEW ViewCustomerData