SQLearning(IN, EXISTS, Alias Subquery, etc)

/*SQL LEARNING (IN, EXISTS, Alias Subquery, ETC)

LINK SOAL : https://www.dropbox.com/s/v06snxk4ixi2sm7/O1-T0206-DF01-07-Edited.docx?dl=0
LINK CREATE+INSERT : https://www.dropbox.com/s/iw9w7xb5sx6n6mt/Create%20%2B%20Insert.sql?dl=0

QUERY INI DIBUAT MENGUNAKAN SQL SERVER MANAGEMENT STUDIO(SSMS) 2014*/

/*1.    Display TreatmentId and TreatmentName where TreatmentId is ‘TM001’ or ‘TM002’.
(in)*/

SELECT TreatmentId, TreatmentName
FROM MsTreatment
WHERE TreatmentId IN('TM001','TM002')--Di gunakan untuk menghemat waktu

/*2.    Display TreatmentName and Price where TreatmentTypeName is not ‘Hair Treatment’
 or ‘Message / Spa’.(in, not in)*/

SELECT TreatmentName, Price
FROM MsTreatment MT
WHERE TreatmentTypeId IN(
    SELECT TreatmentTypeId
    FROM MsTreatmentType MTT
    WHERE MTT.TreatmentTypeId=MT.TreatmentTypeId
        AND TreatmentTypeName NOT IN('Hair Treatment','Message / Spa')
    )

/*3.    Display CustomerName, CustomerPhone and CustomerAddress where the Customer had done the
transaction on Friday and CustomerName is more than 8 characters.(in, datename, weekday,len)*/

SELECT CustomerName,CustomerPhone,CustomerAddress
FROM MsCustomer MC
WHERE CustomerId IN(--digunakan untuk penghubung tabel dan persyaratan
    SELECT CustomerId
    FROM HeaderSalonServices HSS
    WHERE HSS.CustomerId=MC.CustomerId
        AND DATENAME(weekday,TransactionDate)='Friday'
    )
    AND LEN(CustomerName)>8

/*4.    Display TreatmentTypeName, TreatmentName and Price where CustomerName contains the word
‘Putra’ and the transaction was happened on date 22.(in, like, day)*/

SELECT *
FROM MsTreatment MT
WHERE TreatmentId IN(
    SELECT TreatmentId
    FROM HeaderSalonServices HSS, MsCustomer MC, DetailSalonServices DSS
    WHERE  HSS.CustomerId=MC.CustomerId AND HSS.TransactionId=DSS.TransactionId AND DSS.TreatmentId=MT.TreatmentId
        AND CustomerName LIKE '%Putra%'
        AND DAY(TransactionDate)=22
    )
/*5.    Display StaffName, CustomerName and TransactionDate (obtained from TransactionDate with format
‘Mon dd, yyyy’) where the last digit of TreatmentID is even.(convert, exists, right)*/

SELECT StaffName,CustomerName,TransactionDate=CONVERT(VARCHAR,TransactionDate,107)
FROM MsStaff MS, MsCustomer MC, HeaderSalonServices HSS
WHERE EXISTS (
    SELECT *
    FROM DetailSalonServices DSS
    WHERE DSS.TransactionId=HSS.TransactionId AND RIGHT(TreatmentId,1)%2=0
    )   --Hanya Untuk Mengecek, Return True/False,
        --Biasa mengunakan EXISTS Untuk mengecek data yang tidak muncul (dalam kasus ini TreatmentID)
    AND MC.CustomerId=HSS.CustomerId AND HSS.StaffId=MS.StaffId

/*6.    Display CustomerName, CustomerPhone and CustomerAddress where the Customer has been served
by Staff whose length of the staff’s name is odd number.(exists, len)*/

SELECT CustomerName,CustomerPhone, CustomerAddress
FROM MsCustomer MC
WHERE EXISTS(
    SELECT *
    FROM MsStaff MS, HeaderSalonServices HSS
    WHERE MS.StaffId=HSS.StaffId AND HSS.CustomerId=MC.CustomerId
        AND LEN(StaffName)%2=1
    )

/*7.    Display ID (obtained from the last 3 digit characters StaffId) and Name (obtained from the
middle name of StaffName) where the Staff hasn’t served the Customer with ‘Male’ gender and StaffName
contains 3 words or more.(right, substring, charindex, len, exists, in, not like, like)*/

SELECT ID=RIGHT(StaffID,3),
    Name=SUBSTRING(SUBSTRING(StaffName,CHARINDEX(' ',StaffName)+1,LEN(StaffName)),
        1,CHARINDEX(' ',SUBSTRING(StaffName,CHARINDEX(' ',StaffName)+1,LEN(StaffName))))
        -- MIS Alexander Kevin W -> SUBSTRING(StaffName,CHARINDEX(' ',StaffName)+1,LEN(StaffName))
        -- Dapet 'Kevin W' ->substring lagi SUBSTRING(Hasil Susbtring1,1(mulai dari index1),CHARINDEX Hasil Substring1)
        -- Dapet Kevin, Yang hanya 2 nama, saat substring ke dua return NULL karena char index 0
FROM MsStaff MS
WHERE EXISTS(
    SELECT *
    FROM MsCustomer MC, HeaderSalonServices HSS
    WHERE MC.CustomerId=HSS.CustomerId AND MS.StaffId=HSS.StaffId
        AND CustomerGender NOT LIKE 'Male'
    )
    AND StaffId IN (
        SELECT StaffId
        FROM HeaderSalonServices HSS
        WHERE HSS.StaffId=MS.StaffId
    )
    AND StaffName LIKE '% % %'

/*8.    Display TreatmentTypeName, TreatmentName and Price where Price is greater than the average
of all Price.(alias subquery, avg)*/

SELECT TreatmentTypeName,TreatmentName,Price
FROM MsTreatment MT, MsTreatmentType MTT,(--ALIAS SUBQUERY
    SELECT [avgR]=AVG(price)--Row
    FROM MsTreatment
    )avgO--Object
WHERE Price>avgO.avgR AND MT.TreatmentTypeId=MTT.TreatmentTypeId

/*9.    Display StaffName, StaffPosition and StaffSalary where StaffSalary is the highest or lowest salary.
(alias subquery, max, min)*/

SELECT StaffName,StaffPosition,StaffSalary
FROM MsStaff, (
    SELECT [maxSalary]=MAX(StaffSalary), [minSalary]=MIN(StaffSalary)
    FROM MsStaff
    )DATA
WHERE StaffSalary=DATA.maxSalary OR StaffSalary=DATA.minSalary

/*10.   Display CustomerName, CustomerPhone, CustomerAddress and Count Treatment (obtained from the amount of
Treatment that is done per transaction) where Count Treatment is the highest Count Treatment.
(count, alias subquery, max)*/

SELECT CustomerName, CustomerPhone,CustomerGender,[COUNT Treatment]=COUNT(TreatmentId)
FROM MsCustomer MC,HeaderSalonServices HSS ,DetailSalonServices DSS,(
            SELECT maks=MAX(jumlahTransaksi.totalTransactionPerTreatment)
            FROM(
                SELECT totalTransactionPerTreatment=COUNT(TreatmentId)
                FROM DetailSalonServices
                GROUP BY TransactionID
                )jumlahTransaksi
            )jumlahTransaksiPerOrang
WHERE MC.CustomerId=HSS.CustomerId AND HSS.TransactionId=DSS.TransactionId
GROUP BY HSS.TransactionId,CustomerName,CustomerPhone,CustomerGender,maks
HAVING COUNT(TreatmentId)=jumlahTransaksiPerOrang.maks