Formulasi Query Dengan SQL

	Nama                        : Anton Rahardjo
	Kelas                         : MIF K44
	NPM                          : 10402498
	Mata Kuliah            : Sistem Basis Data
	RELASI / TABEL RUJUKAN
	Produk_Motul
	Kode	Nama_Barang	Jenis_Oli	Harga
	1001	MULTI POWER 15W50	mobil bensin	73,500
	1002	MULTI POWER 10W40	mobil bensin	88,000
	1003	TEKMA MEGA X 15W40	mobil disel	71,500
	1004	H-TECH 5W30	mobil bensin	112,000
	1005	HD-X 80w90	gardan	109,000
	1006	ESTER 5100	mesin motor	85,000
	1007	GOLD 3100	mesin motor	50,000
	1008	H-TECH 4T	mesin motor	65,000
	1009	300 V SERIES	racing mobil	330,000
	1010	800 2T RACING	racing motor	250,000
	1. Pernyataan SELECT
		SELECT Kode,Nama_Barang,Harga
	Kode	Nama_Barang	Harga
	1001	MULTI POWER 15W50	73,500
	1002	MULTI POWER 10W40	88,000
	1003	TEKMA MEGA X 15W40	71,500
	1004	H-TECH 5W30	112,000
	1005	HD-X 80w90	109,000
	1006	ESTER 5100	85,000
	1007	GOLD 3100	50,000
	1008	H-TECH 4T	65,000
	1009	300 V SERIES	330,000
	1010	800 2T RACING	250,000
	2. Pernyataan DISTINCT
		SELECT DISTINK Jenis_Oli
		FROM Produk_Motul
			Jenis_Oli
			mobil bensin
			mobil disel
			gardan
			mesin motor
			racing mobil
			racing motor
	3. Klausa WHERE
		SELECT DISTINCT Nama_Barang
		FROM Produk_Motul
		WHERE =Jenis_Oli
			Nama_Barang	Jenis_Oli
			MULTI POWER 15W50	mobil bensin
			MULTI POWER 10W40	mobil bensin
			H-TECH 5W30	mobil bensin
	4. Kondisi Like
		Select*
		FROM Produk_Motul
		WHERE Jenis_Oli LIKE '% g'
	Kode	Nama_Barang	Jenis_Oli	Harga
	1005	HD-X 80w90	gardan	109,000
	5. Operator BETWEEN … AND…
		Select*
		FROM Produk_Motul
		Where Harga BETWEEN  45.000 AND 100.000
	Kode	Nama_Barang	Jenis_Oli	Harga
	1001	MULTI POWER 15W50	mobil bensin	73,500
	1002	MULTI POWER 10W40	mobil bensin	88,000
	1003	TEKMA MEGA X 15W40	mobil disel	71,500
	1006	ESTER 5100	mesin motor	85,000
	1007	GOLD 3100	mesin motor	50,000
	1008	H-TECH 4T	mesin motor	65,000
		Select*
		FROM Produk_Motul
		Where Harga NOT BETWEEN  45.000 AND 100.000
	Kode	Nama_Barang	Jenis_Oli	Harga
	1004	H-TECH 5W30	mobil bensin	112,000
	1005	HD-X 80w90	gardan	109,000
	1009	300 V SERIES	racing mobil	330,000
	1010	800 2T RACING	racing motor	250,000
	6. Klausa ORDER BY
		Select*
		FROM Produk_Motul
		Order By Harga
	Kode	Nama_Barang	Jenis_Oli	Harga
	1007	GOLD 3100	mesin motor	50,000
	1008	H-TECH 4T	mesin motor	65,000
	1003	TEKMA MEGA X 15W40	mobil disel	71,500
	1001	MULTI POWER 15W50	mobil bensin	73,500
	1006	ESTER 5100	mesin motor	85,000
	1002	MULTI POWER 10W40	mobil bensin	88,000
	1005	HD-X 80w90	gardan	109,000
	1004	H-TECH 5W30	mobil bensin	112,000
	1010	800 2T RACING	racing motor	250,000
	1009	300 V SERIES	racing mobil	330,000
	7. ALIAS
		Select Nama_Barang AS Nama_Oli
		FROM Produk_Motul
	Kode	Nama_Oli	Jenis_Oli	Harga
	1007	GOLD 3100	mesin motor	50,000
	1008	H-TECH 4T	mesin motor	65,000
	8. Pernyataan UPDATE
		UPDATE Produk_Motul
		Set Nama_Barang = 'SILVER 3100'
		Where Nama_Barang = 'GOLD 3100'
	Kode	Nama_Barang	Jenis_Oli	Harga
	1007	GOLD 3100	mesin motor	50,000
	1008	H-TECH 4T	mesin motor	65,000
	1003	TEKMA MEGA X 15W40	mobil disel	71,500
	1001	MULTI POWER 15W50	mobil bensin	73,500
	Kode	Nama_Barang	Jenis_Oli	Harga
	1007	SILVER 3100	mesin motor	50,000
	1008	H-TECH 4T	mesin motor	65,000
	1003	TEKMA MEGA X 15W40	mobil disel	71,500
	1001	MULTI POWER 15W50	mobil bensin	73,500
	9. Pernyataan DELETE
		Delete From Produk_Motul
		Where Nama_Barang='H-TECH 4T'
	Kode	Nama_Barang	Jenis_Oli	Harga
	1007	SILVER 3100	mesin motor	50,000
	1008	H-TECH 4T	mesin motor	65,000
	1003	TEKMA MEGA X 15W40	mobil disel	71,500
	1001	MULTI POWER 15W50	mobil bensin	73,500
	Kode	Nama_Barang	Jenis_Oli	Harga
	1007	SILVER 3100	mesin motor	50,000
	1003	TEKMA MEGA X 15W40	mobil disel	71,500
	1001	MULTI POWER 15W50	mobil bensin	73,500
	10. Pernyataan INSERT INTO
		Insert Into Produk_Motul
		Values ('1011','MULTI GRADE','mobil tempur','125.000')
	Kode	Nama_Barang	Jenis_Oli	Harga
	1011	MULTI GRADE	mobil tempur	125,000
	1001	MULTI POWER 15W50	mobil bensin	73,500
	1002	MULTI POWER 10W40	mobil bensin	88,000
	1003	TEKMA MEGA X 15W40	mobil disel	71,500
	1004	H-TECH 5W30	mobil bensin	112,000
	1005	HD-X 80w90	gardan	109,000
	1006	ESTER 5100	mesin motor	85,000
	1007	GOLD 3100	mesin motor	50,000
	1008	H-TECH 4T	mesin motor	65,000
	1009	300 V SERIES	racing mobil	330,000
	1010	800 2T RACING	racing motor	250,000
	11.JOIN
	Barang
	Kode	Nama_Barang	Kode_Jenis
	1001	MULTI POWER 15W50	111
	1003	TEKMA MEGA X 15W40	112
	1004	H-TECH 5W30	111
	1005	HD-X 80w90	113
	1006	ESTER 5100	114
		Jenis
		Kode_Jenis	Jenis_Oli
		111	mobil bensin
		112	mobil disel
		113	gardan
		114	mesin motor
		Select Jenis.Jenis_Oli,Barang.Nama_Barang
		From Jenis,Barang,
		Where Kode_ Jenis.Kode_Jenis =  Barang.jenis
	Hasil
		Jenis_Oli	Nama_Barang
		mobil bensin	MULTI POWER 15W50
		mobil disel	TEKMA MEGA X 15W40
		mobil bensin	H-TECH 5W30
		gardan	HD-X 80w90
		mesin motor	ESTER 5100
	11. a. INNER JOIN
		Select Jenis.Jenis_Oli,Barang.Nama_Barang
		From Jenis Inner Join Barang
		On Jenis.Kode_Jenis = Barang,Kode_Jenis
		Jenis_Oli	Nama_Barang
		mobil bensin	MULTI POWER 15W50
		mobil disel	TEKMA MEGA X 15W40
		mobil bensin	H-TECH 5W30
		gardan	HD-X 80w90
		mesin motor	ESTER 5100
	11.b.  LEFT JOIN
		Select Jenis.Jenis_Oli,Barang.Nama_Barang
		From Jenis Left Join Barang
		On Jenis.Kode_Jenis = Barang,Kode_Jenis
		Jenis_Oli	Nama_Barang
		mobil bensin	MULTI POWER 15W50
		mobil disel	TEKMA MEGA X 15W40
		mobil bensin	H-TECH 5W30
		gardan	HD-X 80w90
		mesin motor	ESTER 5100
		mesin motor	ESTER 5101
		racing mobil
	11.c.  RIGHT JOIN
		Select Jenis.Jenis_Oli,Barang.Nama_Barang
		From Jenis Right Join Barang
		On Jenis.Kode_Jenis = Barang,Kode_Jenis
		Jenis_Oli	Nama_Barang
		mobil bensin	MULTI POWER 15W50
		mobil disel	TEKMA MEGA X 15W40
		mobil bensin	H-TECH 5W30
		gardan	HD-X 80w90
		mesin motor	ESTER 5100
		mesin motor	ESTER 5101
			300V SERIES
	12. UNION & UNION ALL
	TABEL 1
	Barang1
	Kode	Nama_Barang	Kode_Jenis
	1001	MULTI POWER 15W50	111
	1003	TEKMA MEGA X 15W40	112
	1004	H-TECH 5W30	111
	1005	HD-X 80w90	113
	1006	ESTER 5100	114
	TABEL 2
	Barang2
	Kode	Nama_Barang	Kode_Jenis
	1003	TEKMA MEGA X 15W40	mobil disel
	1004	H-TECH 5W30	mobil bensin
	1005	HD-X 80w90	gardan
	1006	ESTER 5100	mesin motor
	1007	GOLD 3100	mesin motor
	1008	H-TECH 4T	mesin motor
	1009	300 V SERIES	racing mobil
		Select Nama_Barang From Barang1		Select Nama_Barang From Barang1
		UNION		UNION ALL
		Select Nama_Barang From Barang2		Select Nama_Barang From Barang2
	Hasil
		Nama_Barang		Nama_Barang
		MULTI POWER 15W50		MULTI POWER 15W50
		TEKMA MEGA X 15W40		TEKMA MEGA X 15W40
		H-TECH 5W30		H-TECH 5W30
		HD-X 80w90		HD-X 80w90
		ESTER 5100		ESTER 5100
		GOLD 3100		TEKMA MEGA X 15W40
		H-TECH 4T		H-TECH 5W30
		300 V SERIES		HD-X 80w90
				ESTER 5100
				GOLD 3100
				H-TECH 4T
				300 V SERIES