Latihan UAS pemrograman SQL

/*buat database terlebih dahulu*/
create database pelanggan5
use pelanggan5
/*tabel barang*/
create table barang
(
kode_barang char(6) not null primary key,
nama_barang varchar (25) not null,
satuan varchar (20),
stok int
);
sp_columns barang
insert into barang values ('elk-01','rice cooker','buah','20')
insert into barang values ('elk-02','lemari es','unit','8')
insert into barang values ('elk-03','televisi','unit','30')
insert into barang values ('elk-04','radio','buah','35')

select* from barang

/*tabel pelanggan*/
create table pelanggan7
(
kode_pelanggan char (6) not null primary key,
nama varchar (30) not null,
alamat varchar (30),
kota varchar(15),
telepon varchar (15)
);
sp_columns pelanggan7
insert into pelanggan7 values ('j-0001','toko karisma','jln cimandiri 25','depok','(021)-367868')
insert into pelanggan7 values ('j-0002','toko ayu','jln cimone 35','tangerang','(021)-467889')
insert into pelanggan7 values ('b-0001','toko warna','jln def 30','jakarta','(021)-234678')
insert into pelanggan7 values ('b-0002','toko surya','jln abc 309','bandung','(022)-356789')
select* from pelanggan7

/*tabel pembelian*/
create table pembelian
(
kode_pembelian char(10) not null,
kode_barang char (6),
kode_pelanggan char (6),
tgl_pembelian datetime,
jum_pembelian int,
constraint pk_pembelian primary key (kode_pembelian,kode_barang,kode_pelanggan),
constraint fk_pembelian_barang foreign key (kode_barang) references barang (kode_barang),
constraint fk_pembelian_pelanggan foreign key (kode_pelanggan) references pelanggan7 (kode_pelanggan))

insert into pembelian  values ('bel-e001','elk-01','j-0001','20-may-02','3')
insert into pembelian  values ('bel-e002','elk-01','j-0001','21-may-02','4')
insert into pembelian  values ('bel-e003','elk-01','j-0002','20-may-02','2')
insert into pembelian  values ('bel-e004','elk-01','b-0001','20-may-02','2')
insert into pembelian  values ('bel-e006','elk-02','j-0001','24-jan-02','1')
insert into pembelian  values ('bel-e007','elk-01','j-0002','24-jan-02','1')
sp_columns pembelian
select*from pembelian

/*UNION*/

/*Query 1*/
select kode_pelanggan
from pembelian
Where kode_pelanggan = 'J-0001'
group by kode_Pelanggan

/*Query 2*/
select kode_Pelanggan
from pembelian
Where Kode_pelanggan = 'J-0002'
group by Kode_Pelanggan

/*Query 3*/
select kode_Pelanggan
from pembelian
Where Kode_pelanggan in ('B-0001', 'B-0002')
group by Kode_Pelanggan

/*penggabungan Query 1, 2, dan 3 dengan menggunakan Union*/
select Kode_Pelanggan
from pembelian
Where Kode_pelanggan = 'J-0001'
group by  Kode_Pelanggan
UNION
select Kode_Pelanggan
from pembelian
Where Kode_pelanggan = 'J-0002'
group by  Kode_Pelanggan
UNION
select Kode_Pelanggan
from pembelian
Where Kode_pelanggan in ('B-0001', 'B-0002')
group by  Kode_Pelanggan
order by kode_pelanggan desc

/*DISTINCT --> menghilangkan data yang nilainya ganda*/
select distinct kode_pembelian, tgl_pembelian, jum_pembelian
from pembelian

/*JOIN*/
Select barang.nama_barang,pembelian.tgl_pembelian, pembelian.jum_pembelian
from barang join pembelian
on  barang.kode_barang=pembelian.kode_barang




/*Tampilkan nama pelanggan, tanggal berapa terjadi pembelian dan berapa jumlah pembelian*/

select pelanggan7.nama,barang.nama_barang,pembelian.jum_pembelian, pembelian.tgl_pembelian
from barang join pembelian
on barang.kode_barang=pembelian.kode_barang
join pelanggan7
on pelanggan7.kode_pelanggan=pembelian.kode_pelanggan

/*Tampilkan nama barang, nama pelanggan, tanggal berapa terjadi pembelian dan berapa jumlah pembelian barang*/
 select barang.nama_barang, pelanggan7.nama, pembelian.tgl_pembelian, pembelian.jum_pembelian
from barang join pelanggan7 join pembelian
on barang.kode_barang=pembelian.kode_barang and pelanggan7.kode_pelanggan=pembelian.kode_pelanggan

/*mencari nilai minimum*/
SELECT kode_barang as kd_brg, min(jum_pembelian) as cacah_beli
from pembelian
group by kode_barang

/*mencari nilai maksimum*/
SELECT kode_barang as kd_brg, max(jum_pembelian) as cacah_beli
from pembelian
group by kode_barang

/*mencari nilai rata-rata*/
SELECT kode_barang as kd_brg, avg(jum_pembelian) as cacah_beli
from pembelian
group by kode_barang

/*mencari jumlah nilai*/
SELECT kode_barang as kd_brg, sum(jum_pembelian) as cacah_beli
from pembelian
group by kode_barang

/*mencari cacah nilai*/
SELECT kode_barang as kd_brg, count(jum_pembelian) as cacah_beli
from pembelian
group by kode_barang

/*HAVING*/
select KODE_pelanggan,count(JUM_PEMBELIAN) as CACAH_PEMBELIAN
from   pembelian
group by kode_pelanggan
Having count(*)=3

/*Tampilkan kode barang yang mempunyai jumlah pembelian 2*/
select kode_barang from pembelian
where jum_pembelian = 2

/*Tampilkan kode pelanggan dan kode barang yang jumlah pembeliannya kurang atau sama dengan 5*/
select kode_pelanggan, kode_barang from pembelian
where jum_pembelian <= 5

/*Tampilkan kode barang dan kode pelanggan yang jumlah pembeliannya antara 1 dan 3 (1 dan 3 termasuk di dalamnya*/
select kode_barang, kode_pelanggan, jum_pembelian from pembelian
Where JUM_PEMBELIAN between 1 and 3

/*like/not like*/
select * from barang
where nama_barang not like '%le%'

/*Tampilkan seluruh transaksi pembelian (tanggal pembelian dan jumlah pembelian)*/
Select tgl_pembelian, jum_pembelian from  pembelian

/*Tampilkan jumlah pembelian rata-rata pada transaksi pembelian*/
Select avg(jum_pembelian) as rata2_pembelian
from pembelian

/*Tampilkan transaksi pembelian yang jumlahnya dibawah rata-rata*/
Select tgl_pembelian, jum_pembelian from  pembelian
where jum_pembelian <(Select avg(jum_pembelian) as rata2_pembelian
from pembelian)

/*tampilkan pelanggan yang paling besar pembeliannya*/
select kode_pelanggan from pembelian
where jum_pembelian > (select max(jum_pembelian) as jumlah from pembelian)

/*ViEW*/

/*buat view dengan nama v_barang untuk menampilkan nama barang dan stok barang*/
 create view v_barang as
 select nama_barang, stok
from barang

/*tampilkan semua data*/
select* from v_barang

/*tampilkan nama barang dari view v_barang*/
select nama_barang from v_barang

/* hapus v_barang*/
drop view v_barang

/*Buatlah view dengan nama V_PELANGGAN yang menampilkan nama pelanggan,alamat dan telepon. Pelanggan yang ditampilkan adalah yang berasal dari kota Jakarta.*/
create view v_pelanggan as
select nama,alamat,telepon
from pelanggan7

select * from v_pelanggan