Praktikum SQL (5)

select*from pasok;

select*from barang;

update pasok set jumlah_pasok ='500' where kode_pasok = 'pas005';

insert into pasok values ('pas003','brg001','sup003','02-11-2015',100);

insert into pasok values ('pas004','brg001','sup003','05-12-2015',150);

insert into pasok values ('pas005','brg002','sup003','12-21-2015',500);

insert into pasok values ('pas006','brg003','sup002','11-21-2016',500);

insert into pasok values ('pas007','brg002','sup002','07-25-2016',200);

insert into pasok values ('pas008','brg002','sup001','05-21-2016',80);

select kode_barang, Avg (jumlah_pasok) as jumlah

from pasok

group by kode_barang

select kode_barang, count (jumlah_pasok) as jumlah

from pasok

group by kode_barang

select kode_pasok, jumlah_pasok 

from pasok

order by jumlah_pasok desc

select kode_pasok, jumlah_pasok 

from pasok

order by jumlah_pasok asc

select kode_suplier,

count (jumlah_pasok) as cacah_pasok

from pasok

group by kode_suplier

having count (*)=3

select kode_barang,kode_suplier,jumlah_pasok

from pasok

where jumlah_pasok between 50 and 150

select kode_barang,kode_suplier,jumlah_pasok

from pasok

where jumlah_pasok in(50,100)

order by jumlah_pasok 

select*from suplier;

insert into suplier values ('sup004','aldi','112233','jalan mangga besar no 15');

insert into suplier values ('sup005','andika','778899','jalan anggur no 13');

insert into suplier values ('sup006','pahang','556644','jalan avocado no 14');

select*from suplier

where alamat_suplier like '%mangga%'

select*from suplier

where alamat_suplier not like '%mangga%'

Praktikum SQL (4)

create table Mahasiswa

(

NIM char(10),

NAMA varchar(15),

ALAMAT varchar(20),

KOTA varchar(15)

);

sp_columns Mahasiswa;

create table Matakuliah

(

KODE_MK char(6),

NAMA_MK varchar(30),

SEMESTER char(1),

SKS numeric(2)

);

alter table Matakuliah add constraint pk_kodeMK unique (KODE_MK);

alter table Mahasiswa add constraint pk_NIM unique (NIM);

alter table Mahasiswa alter column kodepos char(5);

alter table mahasiswa alter column alamat char(50);

sp_columns Matakuliah;

create table nilai

(

NIM char(10),

KODE_MK char(6),

NILAI numeric,

constraint fk_NIM foreign key (NIM) references Mahasiswa (NIM),

constraint fk_KODEMK foreign key (KODE_MK) references Matakuliah (KODE_MK)

);

sp_columns nilai;

select*from Mahasiswa;

insert into Mahasiswa values ('201391001','Alfa','Jl.Duri Kosambi No.10','Jakarta','12345');

insert into Mahasiswa values ('201391002','Betta','Jl.Malioboro No.10','Yogyakarta','23456');

insert into Mahasiswa values ('201391003','Charly',' ',' ',' ');

insert into Mahasiswa values ('201391004','Delta','Jl.Riau No.10','Bandung','34567');

insert into Mahasiswa values ('201391005','Echo',' ',' ',' ');

update nilai set NILAI  = '100'

where NIM = '201391001' and KODE_MK = 'MKI001';

select*from Matakuliah;

insert into Matakuliah values ('MKI001','SQL','4','2');

insert into Matakuliah values ('MKI002','Sistem Basis Data','5','2');

insert into Matakuliah values ('MKI003','Bahasa Pemograman','6','3');

insert into Matakuliah values ('MKI004','Perangcangan Basis Data','4','2');

insert into Matakuliah values ('MKI005','Perangkat Lunak Jaringan','3','2');

select*from nilai;

insert into nilai values ('201391001','MKI001','60');

insert into nilai values ('201391002','MKI002','80');

insert into nilai values ('201391003','MKI003','65');

insert into nilai values ('201391004','MKI004','80');

insert into nilai values ('201391005','MKI001','75');

insert into nilai values ('201391001','MKI002','50');

insert into nilai values ('201391002','MKI003','45');

insert into nilai values ('201391003','MKI004','90');

insert into nilai values ('201391004','MKI001','70');

select Mahasiswa.NAMA, Matakuliah.NAMA_MK from Mahasiswa,Matakuliah where NAMA_MK = 'SQL' and Mahasiswa.NIM = nilai.NIM and Matakuliah.KODE_MK = nilai.KODE_MK;

select nilai.NILAI from nilai where NILAI > '65';

Praktikum SQL (3)

Praktikum SQL (2)

CREATE TABLE barang (

kode_barang char(6),

satuan_barang varchar(20),

nama_barang varchar(25),

stok_barang numeric(4)

);

alter table barang add constraint pk_barang unique (kode_barang)

;

alter table barang alter column nama_barang char(50)

;

CREATE TABLE suplier (

kode_suplier char(6),

nama_suplier varchar(6),

tlp_suplier varchar(6),

);

alter table suplier add constraint pk_suplier unique (kode_suplier)

;

alter table suplier add alamat_suplier varchar(50)

;

alter table suplier drop column alamat_suplier

;

alter table suplier rename column tlp_suplier to telepeon_suplier

;

CREATE TABLE pasok (

kode_pasok char(10),

kode_barang char(6),

kode_suplier char(6),

tanggal_pasok date,

jumlah_pasok numeric,

constraint pk_pasok primary key (kode_pasok),

constraint fk_pasok_barang foreign key (kode_barang) references barang (kode_barang),

constraint fk_pasok_suplier foreign key (kode_suplier) references suplier (kode_suplier)

);

select *from barang;

select *from suplier;

select *from pasok;

sp_columns barang;

sp_columns suplier;

sp_columns pasok;