SQLearning(UPDATE,DELETE,JOIN,IN,etc)

/*LINK .bak :https://www.dropbox.com/s/3id5mtbsjxhocmz/create.bak?dl=0
RightClick Database>Restore Database>Device>Source>Device>Add>open.bak>ok*/

-- USE SBD
INSERT INTO MsPaymentType VALUES
('PT001','Kredit'),
('PT002','Cash'),
('PT003','Debit')

INSERT INTO MsShipmentType VALUES
('ST001','Express','100000'),
('ST002','Regular','50000'),
('ST003','Economy','25000')

INSERT INTO MsCity VALUES
('CT001','Jakarta'),
('CT002','Bandung'),
('CT003','Bekasi'),
('CT004','Kerawang'),
('CT005','Purwakarta'),
('CT006','Cibinong'),
('CT007','Sukabumi'),
('CT008','Semarang'),
('CT009','Klaten'),
('CT010','Kudus'),
('CT011','Cilacap'),
('CT012','Surabaya'),
('CT013','Gresik'),
('CT014','Blitar'),
('CT015','Kediri'),
('CT016','Malang'),
('CT017','Tangerang'),
('CT018','Serang'),
('CT019','Cilegon'),
('CT020','Rangkas Bitung'),
('CT021','Menteng'),
('CT022','Yogyakarta'),
('CT023','Wates'),
('CT024','Pandeglang'),
('CT025','Wonosari')

INSERT INTO MsDestination VALUES
('DT001','CT001','CT002','10000'),
('DT002','CT002','CT003','20000'),
('DT003','CT004','CT005','30000'),
('DT004','CT006','CT007','40000'),
('DT005','CT008','CT009','50000'),
('DT006','CT010','CT011','60000'),
('DT007','CT012','CT013','70000'),
('DT008','CT014','CT015','80000'),
('DT009','CT016','CT017','90000'),
('DT010','CT018','CT019','15000'),
('DT011','CT020','CT021','16000'),
('DT012','CT022','CT003','17000'),
('DT013','CT024','CT025','18000'),
('DT014','CT001','CT003','19000'),
('DT015','CT005','CT007','20000'),
('DT016','CT009','CT010','22000'),
('DT017','CT013','CT015','23000'),
('DT018','CT017','CT019','24000'),
('DT019','CT021','CT023','25000'),
('DT020','CT025','CT002','30000'),
('DT021','CT024','CT003','32000'),
('DT022','CT023','CT004','33000'),
('DT023','CT022','CT005','34000'),
('DT024','CT018','CT006','35000'),
('DT025','CT011','CT007','45000')

INSERT INTO MsStaff VALUES
('SF001','Jean Karunadewi Wahab','1990-05-11','Female','Wahidin Raya Street No. 1','Jean@gmail.com',5000000),
('SF002','Netty Setiawan','1992-01-15','Female','Juanda Raya Street No. 19','Netty@yahoo.com',6000000),
('SF003','Edward Tabianto','1987-05-18','Male','Gatot Subroto Street No. 18','Edward@ymail.com',7000000),
('SF004','Kelvin Chandra','1990-05-12','Male','Achmad Yani Street No 9','Kelvin@yahoo.com',8000000),
('SF005','Leo Andika','1980-03-1','Male','Gunung Sahari Raya Street','Leo@gmail.com',9000000),
('SF006','Febrian Jiuwira','1982-02-21','Male','Purnawarman Street No. 99','Febrian@ymail.com',10000000),
('SF007','Robert Trisnadi','1991-03-1','Male','Letnan Sari Street No. 8','Robert@yahoo.com',11000000),
('SF008','Yara Kanigara','1990-01-12','Male','Kalibata Street No. 7','Yara@gmail.com',12000000),
('SF009','Handy Tedja Sukmana','1988-09-11','Male','Selamat Jaya Street No. 5','Handy@ymail.com',13000000),
('SF010','Faustina','1987-05-12','Female','Kebun Raya Street No. 9','Faustina@ymail.com',14000000)


INSERT INTO HeaderShipment VALUES
('SH001','SF001','DT001','PT001','ST003','Shirley Halim Ng','Taman Ratu Street No. 2','5','2011-11-1'),
('SH002','SF002','DT002','PT002','ST002','Tri Fennia Lesmana','Kopi Street No. 9','1','2014-10-3'),
('SH003','SF003','DT003','PT003','ST001','Merianti','Setiabudi Raya Street No. 8','3','2014-10-2'),
('SH004','SF004','DT004','PT001','ST001','Sukianti','Kemanggisan Street No. 7','4','2014-11-5'),
('SH005','SF005','DT005','PT002','ST003','Ellys','TPI Sari Street No. 6','5','2014-11-2'),
('SH006','SF006','DT006','PT001','ST003','Kenrick Satrio',' Muara Karang Street No. 5','7','2014-11-2'),
('SH007','SF007','DT007','PT002','ST002','Andri','Taman Ancol Street No. 3','8','2014-11-9'),
('SH008','SF008','DT008','PT003','ST001','Hadi Setiawan','Tangerang Jaya Street No. 3','1','2014-11-3'),
('SH009','SF009','DT009','PT001','ST001','Denny','Teluk Gong Street No. 2','1','2014-11-29'),
('SH010','SF010','DT025','PT001','ST001','Erick Kurniawan','Taman Ratu Street No. 10','2','2014-10-15'),
('SH011','SF001','DT024','PT002','ST002','Vincent','Kerayaan Jaya Street No. 9','3','2014-9-13'),
('SH012','SF002','DT023','PT001','ST001','Antony Budianto','Solo Makmur Street No. 3','11','2014-9-19'),
('SH013','SF003','DT022','PT001','ST002','Kharisma Pribadi','Sukabumi Street No. 1','15','2014-9-10'),
('SH014','SF004','DT021','PT002','ST002','Yoedi Hariadi Kurniawan','Bekasi Jati Street No. 2','35','2014-8-5'),
('SH015','SF005','DT020','PT002','ST003','Michael Reynaldo Phangtriastu','Sari Ratu Street No. 3','25','2014-8-1'),
('SH016','SF006','DT011','PT003','ST003','Calvindoro Satyagraha','Daan Mogot Street No. 1','100','2014-8-15'),
('SH017','SF007','DT013','PT003','ST003','Tata Panca Putra','Alam Sutra Street No. 2','7','2014-7-3'),
('SH018','SF001','DT015','PT002','ST002','Harris Kristanto','Singkawang Street No. 3','11','2014-7-8'),
('SH019','SF001','DT017','PT002','ST002','Joni Pohan','Setia Jaya Street No. 1','15','2014-7-5'),
('SH020','SF002','DT019','PT003','ST001','Arie Purnama','Sudirman Street No. 2','25','2014-11-30')

/*Insert these following data!
(insert, getdate)
SH021   SF005   DT001   PT001   ST001   Indra   Kapuas Street No. 12    3   Current Date
*/

INSERT INTO HeaderShipment
VALUES('SH021','SF005','DT001','PT001','ST001','Indra','Kapuas Street No.12',3,GETDATE())

SELECT *
FROM HeaderShipment

/*Insert this following data!
(insert, round, rand)
SF011   Effendy     1992-10-1   Male    Tanggerang City Street no 88    Effendy@yahoo.com   Obtained from random result with range between 3000000 and 5000000
*/

INSERT INTO MsStaff
VALUES ('SF011','Effendy','1992/10/1','Male','Tanggerang City Street No 88','Effendy@yahoo.com',ROUND(RAND()*(5000000-3000000)+3000000,2))

SELECT *
FROM MsStaff

/*Change StaffGender by replacing the gender with the first character of StaffGender on MsStaff table. Then display all data on MsStaff table.
(update, left)
*/

UPDATE MsStaff
SET StaffGender=LEFT(StaffGender,1)

SELECT *
FROM MsStaff

/*Change the StaffEmail into ‘Edward@binus.edu’ and StaffSalary into the current StaffSalary added by 800000 on MsStaff table for every staff
whose id is ‘SF003’. Then display all data on MsStaff table.
(update, like)
*/

UPDATE MsStaff
SET StaffSalary=StaffSalary+800000, StaffEmail='Edward@binus.edu'
WHERE StaffID LIKE 'SF003'

SELECT *
FROM MsStaff

/*Change the StaffName into the first name of StaffName on MsStaff table for every staff who had handled the shipment on 30th day. Then display
all data on MsStaff table.
(update, substring, charindex, in, day)
*/

UPDATE MsStaff
SET StaffName=SUBSTRING(StaffName,1,CHARINDEX(' ',StaffName)-1)
WHERE StaffID IN(
    SELECT StaffID
    FROM HeaderShipment
    WHERE DAY(ShipmentDate)=30
)

SELECT *
FROM MsStaff

/*Change StaffName by adding ‘Ms. ’ in front of it on MsStaff table for every staff with id ‘SF001’,’SF002’,’SF010’. Then display all data on MsStaff table.
(update, in)
*/

UPDATE MsStaff
SET StaffName='Ms. '+StaffName
WHERE StaffID IN('SF001','SF002','SF010')

SELECT * FROM
MsStaff

/*Change ReceiverAddress into ‘Bogor Raya Street No. 3’ on HeaderShipment table for every shipment that been served by staff whose name is ‘Kelvin Chandra’ and
the transaction occurred on 5th day. Then display all data on HeaderShipment table.
(update, in, like, day)
*/

UPDATE HeaderShipment
SET ReceiverAddress='Bogor Raya Street No. 3'
WHERE DAY(ShipmentDate)=5 AND
    StaffID IN (
        SELECT StaffID
        FROM MsStaff
        WHERE StaffName LIKE 'Kelvin Chandra'
    )

SELECT *
FROM HeaderShipment

/*Delete data on HeaderShipment table for every transaction which handled by the staff whose name has two words. Then display all data on HeaderShipment table.
(delete, join, like, not like)
*/

SELECT *
FROM MsStaff

DELETE HS FROM HeaderShipment HS JOIN MsStaff MS ON MS.StaffID=HS.StaffID
WHERE StaffName LIKE '% %' AND StaffName NOT LIKE '% % %'

SELECT *
FROM HeaderShipment

/*Delete data on MsDestination table for every destination that the length of CityFromName (obtained from CityName of CityFrom) is lower than 10 characters.
Then display all data on MsDestination table.
(delete, len)
*/

SELECT *
FROM MsDestination

SELECT *
FROM MsCity

DELETE FROM MsDestination
WHERE CityFromID IN(
    SELECT CityID
    FROM MsCity
    WHERE LEN(CityName)<10
)

SELECT *
FROM MsDestination