SQLearning(JOIN,Aggregate,Subquery, ETC)

--DROP DATABASE OOVEO_Salon
/*
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
*/

/*⦁   Display TreatmentTypeName, TreatmentName and Price where TreatmentTypeName contains the word ‘hair’ or starts with word
‘nail’ and the price is less than 100000.
(join, like)*/

SELECT TreatmentTypeName,TreatmentName,Price
FROM MsTreatment MT JOIN MsTreatmentType MTT ON MTT.TreatmentTypeId=MT.TreatmentTypeId
WHERE Price<100000 AND (TreatmentTypeName LIKE '%Hair%' OR TreatmentTypeName LIKE 'Nail%')

/*⦁   Display StaffName and StaffEmail (obtained from the first character of StaffName add with the last name of StaffName
 and ended with the word ‘@oosalon.com’ with lowercase format) where the transaction happened on Thursday and the repeated
 data is shown once.
(distinct, lower, left, reverse, charindex, join, datename, weekday, like)*/

SELECT DISTINCT StaffName, StaffEmail=LOWER(LEFT(StaffName,1)+REVERSE(LEFT(REVERSE(StaffName),CHARINDEX(' ',REVERSE(StaffName))-1))+'@oosalon.com')
FROM MsStaff MS JOIN HeaderSalonServices HSS ON HSS.StaffId=MS.StaffId
WHERE DATENAME(weekday,TransactionDate) LIKE 'Thursday'

/*⦁   Dispaly New Transaction ID (obtained from TransactionId by replacing the word ‘TR’ to ‘Trans’), Old Transaction ID
(obtained from TransactionID), TransactionDate, StaffName, and CustomerName where the difference of transaction date with
24th December 2012 is less or equal to 2 days.
(replace, join, datediff, day)*/

SELECT [NEW TRANSACTION ID]=REPLACE(TransactionId,'TR','Trans'),[OLD Transcation ID]=TransactionId,TransactionDate,StaffName,CustomerName
FROM MsStaff MS JOIN HeaderSalonServices HSS ON HSS.StaffId=MS.StaffId
    JOIN MsCustomer MC ON HSS.CustomerId=MC.CustomerId
WHERE DATEDIFF(DAY,TransactionDate,'2012-12-24')<=2


/*⦁   Dispaly New Transaction Date (obtained from TransactionDate add with 5 days), Old Transaction Date (obtained from
TransactionDate) and CustomerName where transaction happened not on date 20.
(dateadd, day, join, datepart)*/

SELECT [NEW TRANSACTION DATE]=DATEADD(DAY,5,TransactionDate),[OLD TRANSACTION DATE]=TransactionDate,CustomerName
FROM MsCustomer MC JOIN HeaderSalonServices HSS ON HSS.CustomerId=MC.CustomerId
WHERE DATEPART(DAY,TransactionDate)!=20

/*⦁   Display Day (obtained from name of the day from TransactionDate), CustomerName and TreatmentName where StaffPosition
 starts with the word ‘TOP’ or StaffGender is ‘Female’. Then sort the data based on CustomerName with ascending format.
(datename, weekday, join, in, like, order by)*/

SELECT [DAY]=DATENAME(WEEKDAY,TransactionDate),CustomerName,TreatmentName
FROM MsCustomer MC JOIN HeaderSalonServices HSS ON MC.CustomerId=HSS.CustomerId
    JOIN MsStaff MS ON HSS.StaffId=MS.StaffId
    JOIN DetailSalonServices DSS ON DSS.TransactionId=HSS.TransactionId
    JOIN MsTreatment MT ON MT.TreatmentId=DSS.TreatmentId
WHERE StaffPosition LIKE 'TOP%' OR StaffGender IN('Female')
ORDER BY CustomerName ASC

/*⦁   Display the first data of CustomerId, CustomerName, TransactionId, and Total Treatment (obtained from the amount of
Treatment per transaction). And sort the data based on Total Treatment with format descending.
(top, count, join, group by, order by)*/

SELECT TOP(1) MC.CustomerId,CustomerName,HSS.TransactionId,[Total Treatment]=COUNT(TreatmentID)
FROM MsCustomer MC JOIN HeaderSalonServices HSS ON HSS.CustomerId=MC.CustomerId
    JOIN DetailSalonServices DSS ON DSS.TransactionId=HSS.TransactionId
GROUP BY MC.CustomerId,CustomerName,HSS.TransactionId
ORDER BY [Total Treatment] DESC

/*⦁   Display CustomerId, TransactionId, CustomerName, and Total Price (obtained from the sum of Price per transaction)
where Total Price is greater of the average Total Price. Then sort the data based on Total Price with descending format.
(sum, join, avg, sum, group by, alias subquery, having, order by)*/

SELECT MC.CustomerId,HSS.TransactionId,CustomerName,[Total Price]=SUM(Price)
FROM MsCustomer MC JOIN HeaderSalonServices HSS ON HSS.CustomerId=MC.CustomerId
            JOIN DetailSalonServices DSS ON DSS.TransactionId=HSS.TransactionId
            JOIN MsTreatment MT ON MT.TreatmentId=DSS.TreatmentId,
    (SELECT average=AVG(subquery.[Total Price])
     FROM(SELECT MC.CustomerId,HSS.TransactionId,CustomerName,[Total Price]=SUM(Price)
          FROM MsCustomer MC JOIN HeaderSalonServices HSS ON HSS.CustomerId=MC.CustomerId
            JOIN DetailSalonServices DSS ON DSS.TransactionId=HSS.TransactionId
            JOIN MsTreatment MT ON MT.TreatmentId=DSS.TreatmentId
          GROUP BY MC.CustomerId,HSS.TransactionId,CustomerName
         )subquery
    )mainquery
GROUP BY MC.CustomerId,HSS.TransactionId,CustomerName
HAVING SUM(PRICE)>AVG(mainquery.average)
ORDER BY [Total Price] DESC

/*⦁   Display Name (obtained from StaffName that starts with the word ‘Mr. ’), StaffPosition, and StaffSalary where StaffGender
is ‘Male’. Then combine with Name (obtained from StaffName that starts with ‘Ms. ’), StaffPosition, and StaffSalary where
StaffGender is ‘Female’. Then sort the data based on Name and StaffPosition with ascending format.
(union, order by)*/

SELECT Name='Mr. '+StaffName,StaffPosition,StaffSalary
FROM MsStaff
WHERE StaffGender='Male'
UNION
SELECT Name='Ms. '+StaffName,StaffPosition,StaffSalary
FROM MsStaff
WHERE StaffGender='Female'

/*⦁   Display TreatmentName, Price (obtained from Price that starts with the word ‘Rp. ’), and Status is ‘Maximum Price’ where
Price is the highest Price from the price list. Then combine with TreatmentName, Price (obtained from Price that starts with
the word ‘Rp. ’), and Status is ‘Minimum Price’ where Price is the lowest Price from the price list.
(cast, max, alias subquery, union, min)*/

SELECT TreatmentName, Price='Rp. '+CAST(Price AS VARCHAR), [STATUS]=('Maximum Price')
FROM MsTreatment,
    (SELECT Maks=MAX(Price)
    FROM MsTreatment)maximumObject
WHERE Price=maximumObject.Maks
UNION
SELECT TreatmentName, Price='Rp. '+CAST(Price AS VARCHAR), [STATUS]=('Minumum Price')
FROM MsTreatment,
    (SELECT Mini=MIN(Price)
     FROM MsTreatment
    )minimumObject
WHERE Price=minimumObject.Mini

/*⦁   Display Longest Name of Staff and Customer (obtained from CustomerName), Length of Name (obtained from the length of
CustomerName), and Status is ‘Customer’ where Length of Name is the longest. Then combine with Longest Name of Staff and
Customer (obtained from StaffName), Length of Name (obtained from the length of StaffName), and Status is ‘Staff’ where
Length of Name is the longest.
(len, max, alias subquery, union)*/

SELECT [Longest Name OF Staff AND Customer]=CustomerName,[LENGTH OF Name]=LEN(CustomerName),[STATUS]=('Customer')
FROM MsCustomer,
    (SELECT maks=MAX(LEN(CustomerName))
     FROM MsCustomer
    )maksimumQuery
WHERE LEN(CustomerName)=maksimumQuery.maks
UNION
SELECT [Longest Name OF Staff AND Customer]=StaffName,[LENGTH OF Name]=LEN(StaffName),[STATUS]=('Staff')
FROM MsStaff,
    (SELECT maks=MAX(LEN(StaffName))
     FROM MsStaff
    )maksimumQuery
WHERE LEN(StaffName)=maksimumQuery.maks