Over Load Alfi Salim

Disini saya akan memberikan beberapa contoh soal dan syntax pada sql server :

Contoh Soal Sebagai Berikut :

Toko Online XYZ akan merancang sistem informasi berbasis komputer untuk sistem e-commerce. Kebutuhannya adalah sebagai berikut :
Sistem yang dibangun bersifat B2B (Bisnis to Bisnis) contohnya adalah toko bagus dan FJB kaskus. Setiap produsen barang dapat menyimpan barang dagangannya di toko XYZ. Misalkan produsen sepatu Cibaduyut menjual barangnya di toko XYZ, data produsen yang harus dicatat adalah nama, username, email, alamat, no telepon. Setiap produsen dapat menyimpan banyak barang. Untuk setiap barang akan disimpan nama barang, ukuran, harga satuan, stok barang. Setiap barang dapat dibeli oleh banyak konsumen asalkan stok masih tersedia. Konsumen harus terdaftar dulu di toko online XYZ agar bisa membeli barang. Data konsumen yang disimpan antara lain nama dan kontak (terdiri atas no telepon dan email). Setiap pembelian yang dilakukan konsumen akan dicatat tanggal pembelian, kuantitas barang, total, bonus.

SOAL A ERD (Bobot 25%) :
1. Buatlah ERD dari kebutuhan sistem
2. Mapping ERD tersebut ke tabel

SOAL B DDL (Bobot 20%):
1. Buatlah seluruh tabel dengan format SQL disertai dengan constraint sebagai berikut :
- Username produsen harus bersifat uniq. Tidak boleh ada yang sama
- Id produsen harus dimulai dari P001 s/d P999, Id Barang harus dimulai dari B001 s/d B99
- Id Konsumen harus dimulai dari K001 s/d K999
2. Relasikan tabel tersebut satu sama lain dengan perintah alter dan add foreign key

SOAL C DML QUERY (Bobot 25%):
1. Tambahkan 2 data di tabel konsumen (bebas)
2. Ubah 1 data di tabel konsumen (bebas)
3. Hapus 1 data di tabel konsumen (bebas)
4. Tampilkan total pemasukan produsen A pada tanggal 2 agustus 2014
5. Tampilkan seluruh total belanja konsumen B ?
6. Siapakah produsen yang menjual barang ‘Monitor 14 inch’ kepada konsumen B ?
7. Pada tanggal berapa produk paling mahal terjual ?
8. Pada tanggal 3 Januari 2015 barang apa yang paling sedikit stok nya ?

SOAL D DDL TRIGGER + STORE PROCEDURE (40%)
1. Buatlah trigger yang akan aktif setelah insert data ke tabel barang yaitu untuk memeriksa jika stok 1 – 100 maka transaksi diterima dan jika stok <=1 | stok > 100 maka transaksi ditolak
2. Buatlah trigger yang akan aktif setelah insert data ke tabel pembelian yaitu untuk mengurangi stok di tabel barang secara otomatis , namun jika pembelian lebih besar dari jumlah stok, maka transaksi ditolak
3. Buatlah trigger yang akan aktif setelah insert data ke tabel pembelian dimana akan memeriksa ID Pembelian yang dimasukan harus dimulai dari ‘P’ contoh : P001. Jika salah, maka transaksi dibatalkan
4. Buatlah trigger yang akan aktif setelah delete data di tabel pembelian yaitu untuk menambah stok di tabel barang secara otomatis.
5. Buatlah trigger yang aktif di tabel pembelian jika :
- Total belanja > 1000000 , maka bonus payung cantik
- total belanja > 500000 && total belanja < 1000000, maka bonus cokelat manis
- total belanja > 100000 && total belanja < 500000, maka bonus kerupuk gurih
- total belanja < 100000, maka no bonus

SOAL E DDL View (Bonus : 10%)
1. buatlah view untuk menampilkan nama pembeli, nama penjual , nama produk , kuantitas dan total harga dari setiap pembelian
2. Tampilkan nama pembeli yang berasal dari view yang memiliki total pembelian tertinggi
3. Tampilkan nama pembeli dari view yang mendapatkan bonus payung cantik

Berikut adalah jawabannya :

//no 1

create database kuis_sp_dbi
use kuis_sp_dbi
create table produsen(
id_produsen char(5) primary key
constraint chk_id check(id_produsen like 'p%'),
nama varchar(50) not null
constraint chk_name unique(nama),
username varchar(50) not null,
email varchar(20) not null,
alamat varchar(50) not null,
no_tlp varchar(20) not null
)

create table barang(
id_barang char(5) primary key
constraint chk_id_barang check(id_barang like 'b%'),
nama varchar(50) not null,
ukuran char(3) not null,
harga int not null,
stok int not null
);

create table konsumen(
id_konsumen char(5) primary key
constraint chk_id_konsumen check(id_konsumen like 'k%'),
nama varchar(50) not null,
no_telepon varchar(20) not null,
email varchar(20)
);
create table pembelian(
id_pembelian char(5) primary key,
id_produsen char(5),
id_konsumen char(5),
id_barang char(5),
tanggal datetime,
kuantitas int,
total int,
bonus varchar(50)
);

//no .2
alter table pembelian add foreign key (id_produsen) references produsen(id_produsen);
alter table pembelian add foreign key (id_konsumen) references konsumen(id_konsumen);
alter table pembelian add foreign key (id_barang) references barang(id_barang);

//SOAL C
//no 1
insert into konsumen values('k001','Andi','1234','andi@gmail.com');
insert into konsumen values('k002','Budi','1234','budi@gmail.com');
insert into produsen values('p001','Charly','charly','charly@gmail.com','Jakarta','123');
insert into barang values('b002','Shampo','XL',50000,10);
insert into barang values('b003','Magic Jar','XXL',180000,20);
insert into pembelian values('p001','p001','k001','b001','2014-10-10','2',100000,'');
insert into pembelian values('p002','p001','k002','b002','2014-10-10','1',180000,'')
insert into pembelian values('p004','p001','k002','b003','2014-08-10','1',180000,'')
select * from barang
//no 4
select sum(B.total) from produsen A, pembelian B
where B.id_produsen = A.id_produsen AND B.tanggal='2014-10-10'

//no 5
select sum(B.total) from konsumen A, pembelian B
where B.id_konsumen = A.id_konsumen AND
A.nama = 'Andi'

//no 6
select A.nama from produsen A, pembelian B, barang C, konsumen D
where A.id_produsen = B.id_produsen AND B.id_barang = C.id_barang
AND B.id_konsumen = D.id_konsumen AND C.nama = 'Shampo'

//no.7
select A.tanggal from pembelian A,barang B
where A.id_barang = B.id_barang AND
B.harga IN (select max(harga) from barang)

//no 8
select nama from barang where stok = (
select min(A.stok) from barang A, pembelian B
where A.id_barang = B.id_barang AND B.tanggal = '2014-10-10')

//D
//no. 1
drop trigger tr_1
create trigger tr_1
ON barang
AFTER INSERT
AS
BEGIN
declare @stok int
select @stok = stok from inserted
IF @stok BETWEEN 1 AND 100
BEGIN
PRINT 'Transaksi Berhasil'
END
ELSE
BEGIN
ROLLBACK TRANSACTION
print 'Transaksi Gagal'
END

END
GO
//no 2

drop trigger tr_2
create trigger tr_2
ON pembelian
AFTER INSERT
AS
BEGIN
declare @stok int, @qty int, @id_barang char(5)
select @qty = kuantitas from inserted
select @id_barang = id_barang from inserted
select @stok = stok from barang where id_barang = @id_barang
IF @stok >= @qty
BEGIn
print 'Transaksi berhasil'
update barang set stok = stok - @qty where id_barang = @id_barang
END
ELSE
BEGIN
ROLLBACK TRANSACTION
END
END
GO

//no 3

drop trigger tr_3
create trigger tr_3
ON pembelian
AFTER INSERT
AS
BEGIN
declare @id_pembelian char(5)
select @id_pembelian = id_pembelian from inserted
IF @id_pembelian not like 'p%'
ROLLBACK TRANSACTION
END
GO

// no 4

drop trigger tr_4
create trigger tr_4
ON pembelian
AFTER DELETE
AS
BEGIN
declare @stok int, @qty int, @id_barang char(5)
select @qty = kuantitas from deleted
select @id_barang = id_barang from deleted
update barang set stok = stok + @qty where id_barang = @id_barang
END
GO

// no 5

drop trigger tr_5
create trigger tr_5
ON pembelian
AFTER INSERT
AS
BEGIN
declare @total int, @id_pembelian char(5)
select @total = total from inserted
select @id_pembelian = id_pembelian from inserted
IF @total > 1000000
update pembelian set bonus = 'Payung Cantik' where id_pembelian = @id_pembelian
ELSE IF @total > 500000 AND @total < 1000000
update pembelian set bonus = 'Cokelat Manis' where id_pembelian = @id_pembelian

ELSE IF @total > 100000 AND @total < 500000
update pembelian set bonus = 'Kerupuk Gurih' where id_pembelian = @id_pembelian

ELSE
update pembelian set bonus = 'No Bonus' where id_pembelian = @id_pembelian

END
GO

//cek :
insert into barang values('b004','Mouse','M',60000,100);
insert into pembelian values('p005','p001','k002','b003','2014-08-10','2',360000,'')

select * from pembelian