SQLearning(AVG, HAVING, GROUP BY, etc)

--LINK SOAL : https://www.dropbox.com/s/cniinmajd8zm6vd/O1-T0206-DF01-06-Edited.docx?dl=0

/* SQL INI DIBUAT MENGUNAKAN SQL SERVER MANAGEMENT STUDIO(SSMS) 2014
SSMS 2014 TIDAK mensupport COMPUTE sehingga mengunakan metode alternatif subquery
Untuk dapat menjalankan COMPUTE diperlukan SSMS 2008/2005
*/

/*
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.    Display Maximum Price (obtained from the highest Price), Minimum Price (obtained from the
lowest Price) and Average Price (obtained from the average of Price that is rounded with format
2 digits number after coma).(max, min, cast, round, avg)*/

SELECT [Maximum Price]=MAX(Price),[Minimum Price]=MIN(Price),[Average Price]=CAST(ROUND(AVG(Price),0)AS NUMERIC(11,2))
FROM MsTreatment


/*2.    Display StaffPosition, Gender (obtained from the first character of StaffGender), and Average
 Salary (obtained from the average of StaffSalary that is rounded with format 2 digits number after
 coma and starts with the word ‘Rp. ’).(left, cast, avg, group by)*/

 SELECT StaffPosition, Gender=LEFT(StaffGender,1),[Average Salary]='Rp. '+CAST(CAST(ROUND(AVG(StaffSalary),0)AS NUMERIC(11,2))AS VARCHAR)
 FROM MsStaff
 GROUP BY StaffPosition,StaffGender

/*3.    Display Transaction Date (obtained from TransactionDate with format ‘Mon dd, yyyy’), and Total
Transaction per Day (obtained from the amount of transaction per day). Then display the average of Total
Transaction per Day.(convert, count, group by, compute, avg)*/
 -- COMPUTE TIDAK BERLAKU DI SQL 2012++, HANYA BERLAKU DI SQL 2008,2005

 SELECT [TRANSACTION DATE]=CONVERT(VARCHAR,TransactionDate,107), [Total TRANSACTION per DAY]=COUNT(TransactionDate)
 FROM HeaderSalonServices
 GROUP BY TransactionDate
 --Jika mengunakan COMPUTE Ganti 'GROUP BY TransactionDate' dengan
/*
ORDER BY Transaction Date
COMPUTE AVG([Total Transaction per Day]);
*/

 SELECT [avg]=AVG(temp)
 FROM (
    SELECT COUNT(TransactionDate)AS temp--dipangil di avg atas
    FROM HeaderSalonServices
    GROUP BY TransactionDate
 )NamaViewSementara--view sementara asal isi aja


/*4.    Display CustomerGender (obtained from CustomerGender with uppercase format), and Total Transaction
(obtained from the amount of transaction that based on the customer’s gender). Then, display the sum of
Total Transaction.(upper, count, group by, compute, sum)*/
 -- COMPUTE TIDAK BERLAKU DI SQL 2012++, HANYA BERLAKU DI SQL 2008,2005

SELECT CustomerGender=UPPER(CustomerGender), [Total TRANSACTION]=COUNT(TransactionId)
FROM MsCustomer MC, HeaderSalonServices HSS
WHERE MC.CustomerId=HSS.CustomerId
GROUP BY CustomerGender
--Jika mengunakan COMPUTE Ganti GROUP BY Customer Gender dengan
/*
ORDER BY CustomerGender
COMPUTE SUM([TotalTransaction]);
*/

SELECT [SUM]=SUM([Total TRANSACTION])
FROM (
    SELECT [Total TRANSACTION]=COUNT(TransactionId)
    FROM MsCustomer MC, HeaderSalonServices HSS
    WHERE MC.CustomerId=HSS.CustomerId
    GROUP BY CustomerGender
)temp

/*5.    Display TreatmentTypeName, Total Transaction (obtained from the amount of transaction) then sort
the data by Total Transaction in descending format.(count, group by, order by)*/

SELECT TreatmentTypeName, [Total TRANSACTION]=COUNT(TransactionId)
FROM MsTreatmentType MTT, MsTreatment MT,DetailSalonServices DSS
WHERE MTT.TreatmentTypeId=MT.TreatmentTypeId AND DSS.TreatmentId=Mt.TreatmentId
GROUP BY TreatmentTypeName
ORDER BY [Total TRANSACTION]DESC

/*6.    Display Date (obtained from the date of transaction with format ‘dd Mon yy’) and Revenue per Day
(obtained from the sum of Price and starts with the word ‘Rp. ’) where Revenue per Day is between 1000000
and 5000000.(convert, cast, sum, group by, having, between)*/

SELECT [DATE]=CONVERT(VARCHAR,TransactionDate,103),[Revenue per DAY]='Rp. '+CAST(SUM(Price)AS VARCHAR)
FROM HeaderSalonServices HSS,MsTreatment MT, DetailSalonServices DSS
WHERE HSS.TransactionId=DSS.TransactionId AND DSS.TreatmentId=MT.TreatmentId
GROUP BY TransactionDate
HAVING SUM(Price) BETWEEN 1000000 AND 5000000

/*7.    Display ID (obtained from TreatmentTypeID by replacing the word ‘TT0’ into ‘Treatment Type ’),
TreatmentTypeName dan Total Treatment per Type (obtained from the amount of TreatmentID that is ended with
the word ‘ Treatment’) where Total Treatment per Type is greater than 5. Then sort the data by Total Treatment
per Type with descending format.(replace, cast, count, group by, having, order by)*/

SELECT ID=REPLACE(MT.TreatmentTypeId,'TT0','Treatment Type '),TreatmentTypeName, [Total Treatment per TYPE]=COUNT(MT.TreatmentTypeId)
FROM MsTreatment MT, MsTreatmentType MTT
WHERE MT.TreatmentTypeId=MTT.TreatmentTypeId
GROUP BY MT.TreatmentTypeId, TreatmentTypeName
HAVING COUNT(MT.TreatmentTypeId)>5
ORDER BY [Total Treatment per TYPE] DESC

/*8.    Display StaffName (obtained from the first name of StaffName), TransactionID and Total Treatment
per Transaction (obtained from the amount of treatment per transaction). Then, display the highest Total
Treatment per Transaction.(left, charindex, count, group by, compute, max)*/

SELECT StaffName=LEFT(StaffName,CHARINDEX(' ',StaffName)-1), HSS.TransactionId, [Total Treatment per TRANSACTION]= COUNT(HSS.TransactionId)
FROM MsStaff MS, MsTreatment MT, HeaderSalonServices HSS, DetailSalonServices DSS
WHERE MS.StaffId=HSS.StaffId AND HSS.TransactionId=DSS.TransactionId AND DSS.TreatmentId=MT.TreatmentId
GROUP BY HSS.TransactionId, StaffName

SELECT [MAX]= MAX([Total Treatment per TRANSACTION])
FROM (
    SELECT [Total Treatment per TRANSACTION]= COUNT(TransactionId)
    FROM DetailSalonServices
    GROUP BY(TransactionId)
)temp

/*9.    Display TransactionDate, CustomerName, TreatmentName and Price where transaction happened on
Thursday and StaffName starts with the word ‘Ryan’. Then show the sum of Price based on TransactionDate
and CustomerName.(datename, weekday, like, order by, compute by, sum)*/

SELECT TransactionDate, CustomerName, TreatmentName, Price
FROM MsStaff MS, MsCustomer MC, HeaderSalonServices HSS, DetailSalonServices DSS, MsTreatment MT
WHERE MS.StaffId=HSS.StaffId AND MC.CustomerId=HSS.CustomerId
    AND HSS.TransactionId=DSS.TransactionId AND DSS.TreatmentId=MT.TreatmentId
    AND DATENAME(weekday,TransactionDate)='Thursday' AND StaffName LIKE 'Ryan%'
ORDER BY CustomerName

SELECT [SUM]=SUM(Price)
FROM(
    SELECT Price
    FROM MsStaff MS, MsCustomer MC, HeaderSalonServices HSS, DetailSalonServices DSS, MsTreatment MT
    WHERE MS.StaffId=HSS.StaffId AND MC.CustomerId=HSS.CustomerId
        AND HSS.TransactionId=DSS.TransactionId AND DSS.TreatmentId=MT.TreatmentId
        AND DATENAME(weekday,TransactionDate)='Thursday' AND StaffName LIKE 'Ryan%'
)temp


/*10.   Display TransactionDate, CustomerName and TotalPrice (obtained from the total Price) where transaction
happened after date 20. Then display the grand TotalPrice and minimum TotalPrice based on date of transaction.
Then sort the data based on date of transaction.(sum, day, group by, order by, compute by, min)*/

SELECT TransactionDate,CustomerName,TotalPrice=SUM(Price)
FROM MsCustomer MC, HeaderSalonServices HSS, DetailSalonServices DSS, MsTreatment MT
WHERE MC.CustomerId=HSS.CustomerId AND HSS.TransactionId=DSS.TransactionId AND DSS.TreatmentId=MT.TreatmentId AND DAY(TransactionDate)>20
GROUP BY TransactionDate,CustomerName
ORDER BY TransactionDate

SELECT [SUM]=SUM(TotalPrice), [MIN]=MIN(TotalPrice)
FROM (
    SELECT TotalPrice=SUM(Price)
    FROM MsCustomer MC, HeaderSalonServices HSS, DetailSalonServices DSS, MsTreatment MT
    WHERE MC.CustomerId=HSS.CustomerId AND HSS.TransactionId=DSS.TransactionId
        AND DSS.TreatmentId=MT.TreatmentId AND DAY(TransactionDate)>20
    GROUP BY TransactionDate,CustomerName
)temp