BASIS DATA

BASIS DATA LANJUT

(TEKS)

<p style="text-align: center;">DATA BASE JAPFA</p>
MASTER

create database japfa

use japfa

create table accounting(id_accounting  char(10) primary key not null,nama varchar(20),alamat varchar(20),telp char(20))

create table kep_produksi(id_kep_produksi char(10) primary key not null,nama varchar(20),alamat varchar(20),telp char(20))

create table produsen(id_produsen char(10) primary key not null,nama varchar(20),alamat varchar(20),telp char(20))

create table esxquder(id_esxquder char(10) primary key not null,nama varchar(20),alamat varchar(20),telp char(20))

create table cirkuler(id_cirkuler char(10) primary key not null,nama varchar(20),alamat varchar(20),telp char(20))

create table abm(id_abm char(10) primary key not null,nama varchar(20),alamat varchar(20),telp char(20))

create table qc(id_dc char(10) primary key not null,nama varchar(20),alamat varchar(20),telp char(20))

create table marketing(id_marketing char(10) primary key not null,nama varchar(20),alamat varchar(20),telp char(20))

create table delivery(id_delivery char(10) primary key not null,nama varchar(20),alamat varchar(20),telp char(20))

create table pembeli(kode_pembeli char(10) primary key not null,nama varchar(20),alamat varchar(20),telp char(20))

create table manager(id_manager char(10) primary key not null,nama varchar(20),alamat varchar(20),telp char(20))

create table direktur(id_direktur char(10) primary key not null,nama varchar(20),alamat varchar(20),telp char(20))

create table stok_khusus(kode_barang char(10) primary key not null,namabarang varchar(20),jenis char(10),jumlah int(5),namaABM varchar(20),namaDelivery varchar(10))

create table stok_biasa(kode_barang char(10) primary key not null,namabarang varchar(20),jenis char(10),jumlah int(5),namaABM varchar(20),namaDelivery varchar(10))

TRANSAKSI

create table pesanan(id_accounting char(10),kode_barang char(10),kode_pembeli char(10))

create table pembelian_bahan(kode_pembeli char(10),id_produsen char(10),tgl_pembelian date,namabahan varchar(20),jumlah_bahan int(10),tgl_bahan date)

create table produksi(kode_barang char(10),id_abm char(10))

create table pesanan_bahan(id_esxquder char(10),id_cirkuler char(10),id_abm char(10),kode_barang char(10))

create table ceking_satu(id_esxquder char(10),id_cirkuler char(10),id_abm char(10),kode_barang char(10),id_qc char(10))

create table ceking_dua(id_esxquder char(10),id_cirkuler char(10),id_abm char(10),kode_barang char(10),id_qc char(10),id_marketing char(10))

create table serah_barang(id_marketing char(10),id_delivery char(10),kode_barang char(10))

create table kirim_barang(id_delivery char(10),kode_barang char(10),kode_pembeli char(10),no_faktur char(10))

create table pembayaran(kode_pembeli char(10),kode_barang char(10),namabank varchar(10),id_accounting char(10))

create table laporan_penjualan(id_manager char(10),id_direktur char(10),kode_barang char(10),jumlah int(10),no_faktur char(10))

create table laporan_pembelian(id_manager char(10),id_direktur char(10),kode_barang char(10),jumlah int(10),no_faktur char(10))


MASTER

insert into accounting value("30011","malik","jl.surabaya.no:21","(031)8974329")

insert into accounting value("30012","santi","jl.jawa.no:18","(031)8971320")

insert into accounting value("30013","ike","jl.irian.no:01","(031)8974311")

insert into accounting value("30014","sutan siregar","jl.sultan agung.no:44","(031)8974333")

insert into accounting value("30015","nuri sahin","jl.surabaya.no:21","(031)8974309")

insert into kep_produksi value("40011","idam","jl.karang tiga.no:11","(031)8908309")

insert into kep_produksi value("40012","saiful","jl.tiga serangkai.no:33","(031)8974320")

insert into kep_produksi value("40013","ahmadi","jl.raya gubeng.no:12","(031)8974399")

insert into kep_produksi value("40014","tanu","jl.karang tekok.no:11","(031)8910309")

insert into kep_produksi value("40015","ketut","jl.raden wijaya.no:33","(031)8974220")

insert into kep_produksi value("40016","putu","jl.slamet raharjo.no:33","(031)8974559")

insert into produsen value("80011","junito","jl.karang asem.no:100","(031)8908123")

insert into produsen value("80012","agus","jl.sudirman.no:10","(031)8908123")

insert into produsen value("80013","hendra","jl.ahmad yani.no:110","(031)8902123")

insert into produsen value("80014","kevino","jl.kenangan.no:20","(031)8908118")

insert into produsen value("80015","salim","jl.untung surapati.no:26","(031)8908100")

insert into produsen value("80016","wayan","jl.karang asem.no:10","(031)8908123")

insert into produsen value("80017","komang","jl.sudirman.no:11","(031)8908143")

insert into produsen value("80018","nyoman","jl.ahmad yani.no:98","(031)8902723")

insert into produsen value("80019","stave","jl.kenangan.no:132","(031)8908218")

insert into produsen value("800110","kadek","jl.untung surapati.no:66","(031)8908190")

insert into esxquder value("50011","andre","jl.agus salim.no:15","(031)8908197")

insert into esxquder value("50012","azis indro","jl.kakak tua.no:19","(031)8908777")

insert into esxquder value("50013","anam","jl.madura.no:80","(031)8940197")

insert into esxquder value("50014","topan","jl.bali.no:187","(031)8908007")

insert into esxquder value("50015","ani","jl.sutan wijaya.no:15","(031)8908197")

insert into esxquder value("50016","frengki","jl.gubeng.no:15","(031)8908190")

insert into esxquder value("50017","jejen","jl.sajad.no:19","(031)8908770")

insert into esxquder value("50018","jujung","jl.sumenep.no:80","(031)8940192")

insert into esxquder value("50019","saleh","jl.bangkalan.no:187","(031)8908009")

insert into esxquder value("500110","wahid","jl.trunajaya.no:15","(031)8908193")

insert into esxquder value("500111","indra","jl.samarindah.no:15","(031)8908194")

insert into esxquder value("500112","bayu indro","jl.kutai.no:19","(031)8908771")

insert into esxquder value("500113","dijah","jl.borobudur.no:80","(031)8940193")

insert into esxquder value("500114","sari","jl.prambanan.no:187","(031)8908004")

insert into esxquder value("500115","suwi","jl.balikpapan.no:15","(031)8908195")

insert into esxquder value("500116","candra","jl.sumenep.no:503","(031)8946692")

insert into esxquder value("500117","reno","jl.bangkalan.no:504","(031)8909909")

insert into esxquder value("500118","suramik","jl.trunajaya.no:150","(031)8900493")

insert into esxquder value("500119","selamet endra","jl.samarindah.no:159","(031)8906094")

insert into esxquder value("500120","indro purnama","jl.kutai.no:191","(031)8908871")

insert into esxquder value("500121","dijah halim","jl.borobudur.no:802","(031)8947193")

insert into esxquder value("500122","denok","jl.prambanan.no:188","(031)8908204")

insert into esxquder value("500123","doni","jl.balikpapan.no:157","(031)8978195")

insert into cirkuler value("60011","retno","jl.kakap.no:201","(031)8908111")

insert into cirkuler value("60012","ratna","jl.tongkol.no:24","(031)8908112")

insert into cirkuler value("60013","galih","jl.salmon.no:20","(031)8908113")

insert into cirkuler value("60014","widi","jl.riau.no:41","(031)8908128")

insert into cirkuler value("60015","indah","jl.tengiri.no:28","(031)8908110")

insert into cirkuler value("60016","serli","jl.hiu.no:02","(031)8908122")

insert into cirkuler value("60017","wiwik","jl.paus.no:22","(031)8908119")

insert into cirkuler value("60018","laili","jl.cumi-cumi.no:67","(031)8908114")

insert into cirkuler value("60019","lia","jl.pati.no:49","(031)8908109")

insert into cirkuler value("60010","ayu","jl.pari.no:35","(031)8908199")

insert into cirkuler value("60024","ica","jl.putihan.no:89","(031)8908111")

insert into cirkuler value("60025","ratni","jl.teri.no:83","(031)8908167")

insert into cirkuler value("60026","erna","jl.lemuruh.no:42","(031)8908165")

insert into cirkuler value("60027","erni","jl.mas.no:809","(031)8908176")

insert into cirkuler value("60028","rendi","jl.bandeng.no:03","(031)8908186")

insert into cirkuler value("60029","muso ramli","jl.cumi-cumi.no:672","(031)8918114")

insert into cirkuler value("60030","jenakan euis","jl.pati.no:491","(031)8909109")

insert into cirkuler value("60031","ayu kartika","jl.pari.no:351","(031)8908299")

insert into cirkuler value("60032","ica poniman","jl.putihan.no:895","(031)8968111")

insert into cirkuler value("60020","ratni hasan","jl.teri.no:836","(031)8908157")

insert into cirkuler value("60021","erna husen","jl.lemuruh.no:426","(031)8908265")

insert into cirkuler value("60022","erni fani","jl.mas.no:869","(031)8978176")

insert into cirkuler value("60023","rendi kerun","jl.bandeng.no:193","(031)8909186")

insert into abm value("70011","zaenab","jl.mawar.no:133","(031)8909486")

insert into abm value("70012","halimah","jl.sentai.no:131","(031)8909486")

insert into abm value("70013","alex","jl.serampai.no:192","(031)8909486")

insert into abm value("70014","made","jl.merapi.no:196","(031)8909486")

insert into abm value("70015","dewi","jl.rinjani.no:144","(031)8909486")

insert into abm value("70016","zaenab abidin","jl.gunung agung.no:7","(031)8939486")

insert into abm value("70017","dewi ayuning","jl.tanggupan perahu.no:3","(031)8903486")

insert into abm value("70018","made dana","jl.salak.no:123","(031)8909446")

insert into abm value("70019","halimahtus","jl.jambu.no:163","(031)8909487")

insert into abm value("70020","suhron","jl.mente.no:153","(031)8909416")

insert into abm value("70021","kendi","jl.keningar.no:143","(031)8209486")

insert into abm value("70022","juni","jl.melati.no:137","(031)8904486")

insert into abm value("70023","seni","jl.mawar.no:131","(031)8959486")

insert into abm value("70024","zasin","jl.nangka.no:132","(031)8979486")

insert into abm value("70025","ali sanan","jl.nangka londoh.no:3","(031)8904486")

insert into qc value("90011","rudi","jl.melati.no:1","(031)8908116")

insert into qc value("90012","sofyan","jl.mawar.no:2","(031)8908117")

insert into qc value("90013","irma","jl.kebun jeruk.no:3","(031)8908108")

insert into qc value("90014","eko","jl.anggrek.no:4","(031)8908119")

insert into qc value("90015","runi","jl.bangkai.no:1","(031)8908116")

insert into qc value("90016","soyan","jl.seroja.no:2","(031)8908117")

insert into qc value("90017","ika","jl.kamboja.no:3","(031)8908108")

insert into qc value("90018","iko","jl.tujuh rupah.no:4","(031)8908119")

insert into qc value("90019","adi","jl.sucipto.no:1","(031)8908116")

insert into qc value("90020","sofan","jl.di.panjaitan.no:2","(031)8908117")

insert into qc value("90021","kenung kevin","jl.sudirman.no:1","(031)8908908")

insert into qc value("90022","eko anam","jl.ahmad yani.no:4","(031)8908149")

insert into qc value("90023","iko ruslan","jl.tujuh rupah.no:5","(031)8908319")

insert into qc value("90024","adi jamat","jl.sucipto.no:2","(031)8908126")

insert into qc value("90025","sofan jamal","jl.di.panjaitan.no:2","(031)8928117")

insert into qc value("90026","kenung remi","jl.sudirman.no:6","(031)8908138")

insert into qc value("90027","eko samlan","jl.ahmad yani.no:7","(031)8908219")

insert into marketing value("100011","serin","jl.nanas.no:29","(031)8908120")

insert into marketing value("100012","dwi","jl.anggur.no:30","(031)8908121")

insert into marketing value("100013","hadi","jl.jeruk.no:314","(031)8908322")

insert into marketing value("100014","putri kelan","jl.pepaya.no:324","(031)8908623")

insert into marketing value("100015","hadi made","jl.jeruk.no:313","(031)8908822")

insert into delivery value("110011","antok","jl.merpati.no:09","(031)8908189")

insert into delivery value("110012","dino","jl.jalak bali.no:10","(031)8908190")

insert into delivery value("110013","septian","jl.macan.no:11","(031)8908180")

insert into delivery value("110014","inggrid","jl.singa.no:12","(031)8908160")

insert into delivery value("110015","salman","jl.kancil.no:13","(031)8908110")

insert into delivery value("110016","fahrus","jl.menjangan.no:14","(031)8908167")

insert into delivery value("110017","seran","jl.merpati.no:15","(031)8908190")

insert into pembeli value("120011","wiji","jl.bangka.no:100","(031)8908180")

insert into pembeli value("120012","puji","jl.belitung.no:101","(031)8908181")

insert into pembeli value("120013","astuti","jl.jambi.no:102","(031)8908182")

insert into pembeli value("120014","windah","jl.batam.no:103","(031)8908183")

insert into pembeli value("120015","wili","jl.makasar.no:104","(031)8908184")

insert into pembeli value("120016","wiji santo","jl.bangka.no:198","(031)8908180")

insert into pembeli value("120017","puji laksanawati","jl.belitung.no:141","(031)8907181")

insert into pembeli value("120018","astuti ferina","jl.jambi.no:106","(031)8908172")

insert into pembeli value("120019","windah siska","jl.batam.no:107","(031)8908163")

insert into manager value("20011","davin","jl.kalimantan.no:111","(031)8908182")

insert into manager value("20012","lutfi","jl.sulawesi.no:112","(031)8908183")

insert into manager value("20013","dhona","jl.lombok.no:113","(031)8908184")

insert into stok_khusus value("z-100","karung elastis","super","100000","zaenab","antok")

insert into stok_khusus value("z-101","karung elastis","super","202000","halimah","dino")

insert into stok_khusus value("z-102","karung elastis","super","1000000","alex","septian")

insert into stok_khusus value("z-104","karung elastis","super","980001","made","salman")

insert into stok_khusus value("z-105","karung elastis","super","1000222","dewi","inggrid")

insert into stok_biasa value("B1-10","karung goni","biasa","2009000","zaenab","antok")

insert into stok_biasa value("B1-11","karung bulog","biasa","2008000","halimah","dino")

insert into stok_biasa value("B1-12","karung beras","biasa","2002000","alex","septian")

insert into stok_biasa value("B1-13","karung barang","biasa","2006000","made","salman")

insert into stok_biasa value("B1-15","karung goni 1","sedang","2009089","zaenab","antok")

insert into stok_biasa value("B1-16","karung bulog 3","biasa","9008000","halimah","dino")

insert into stok_biasa value("B1-17","karung beras 3","sedang","5002000","alex","septian")

insert into stok_biasa value("B1-18","karung barang 6","sedang","4006000","made","salman")

insert into direktur value("10011","muslimin","jl.sayang.no:18","(031)8908102")

insert into direktur value("10012","widiastuti","jl.cinta.no:22","(031)8908104")


TRANSAKSI

insert into pesanan value("30011","A-100","120011")

insert into pesanan value("30011","A-100","120011")

insert into pesanan value("30011","A-101","120011")

insert into pesanan value("30012","A-101","120011")

insert into pesanan value("30012","B-10","120012")

insert into pesanan value("30012","B-11","120012")

insert into pesanan value("30012","B-12","120012")

insert into pesanan value("30011","A-103","120013")

insert into pesanan value("30011","A-101","120013")

insert into pesanan value("30011","A-102","120013")

insert into pembelian_bahan value("120011","80011","2012/10/23","benang bol","100000","2011/02/02")

insert into pembelian_bahan value("120011","80011","2012/11/24","benang bol","300000","2011/01/02")

insert into pembelian_bahan value("120012","80012","2012/04/21","benang ulat","400000","2011/01/02")

insert into pembelian_bahan value("120012","80011","2012/07/27","benang ulat","500000","2011/01/02")

insert into pembelian_bahan value("120013","80012","2012/12/28","benang ulat","700000","2011/07/02")

insert into produksi value("A-100","70011")

insert into produksi value("A-103","70011")

insert into produksi value("B-10","70012")

insert into produksi value("B-11","70012")

insert into produksi value("B-12","70012")

insert into pesanan_bahan value("50011","60011","70011","A-100")

insert into pesanan_bahan value("50011","60012","70011","A-101")

insert into pesanan_bahan value("50012","60012","70013","A-102")

insert into pesanan_bahan value("50012","60012","70013","B-10")

insert into pesanan_bahan value("50013","60012","70012","B-11")

insert into ceking_satu value("50011","60011","70012","B-11","90011")

insert into ceking_satu value("50011","60011","70012","B-12","90011")

insert into ceking_satu value("50012","60012","70012","A-100","90012")

insert into ceking_satu value("50012","60012","70012","B-11","90012")

insert into ceking_satu value("50016","60012","70012","B-11","90013")

insert into ceking_dua value("50016","60012","70011","B-11","90013","100011")

insert into ceking_dua value("50016","60012","70012","B-11","90013","100012")

insert into ceking_dua value("50012","60011","70012","A-102","90011","100011")

insert into ceking_dua value("50012","60011","70011","B-103","90011","100012")

insert into ceking_dua value("50012","60013","70012","B-104","90012","100011")

insert into serah_barang value("100011","110011","A-102")

insert into serah_barang value("100011","110011","A-102")

insert into serah_barang value("100012","110012","A-103")

insert into serah_barang value("100012","110012","A-104")

insert into serah_barang value("100012","110012","B-11")

insert into kirim_barang value("110012","B-11","120011","CZ-100")

insert into kirim_barang value("110011","B-11","120011","DZ-100")

insert into kirim_barang value("110011","B-11","120012","DZ-130")

insert into kirim_barang value("110012","A-101","120012","DZ-100")

insert into kirim_barang value("110011","A-102","120012","CZ-130")

insert into pembayaran value("120011","A-102","BNI","30011")

insert into pembayaran value("120012","A-102","BRI","30011")

insert into pembayaran value("120012","B-12","BNI","30012")

insert into pembayaran value("120011","B-13","BNI","30012")

insert into pembayaran value("120012","B-14","BCA","30012")

insert into laporan_penjualan value("20011","10011","A-100","120000","CZ-100")

insert into laporan_penjualan value("20012","10012","A-102","140000","DZ-103")

insert into laporan_penjualan value("20013","10011","B-10","150000000","100-QK")

insert into laporan_pembelian value("20011","10011","B-14","160001000","FZ-106")

insert into laporan_pembelian value("20012","10011","B-16","190000000","FZ-104")

insert into laporan_pembelian value("20013","10012","A-100","1600200000","EZ-107")

insert into laporan_pembelian value("20014","10011","A-101","1600400000","EZ-105")

== 1. Menampilkan Data Pada Tabel Accounting

select * from accounting

== 2. Menampilkan Data Pada Tabel Kep_produksi

select * from kep_produksi

== 3. Menampilkan Data Pada Tabel Produsen

select * from Produsen

== 4. Menampilkan Data Pada Tabel Esxquder

select * from esxquder

== 5. Menampilkan Data Pada Tabel Cirkuler

select * from cirkuler

== 6. Menampilkan Data Pada Tabel ABM

select * from ABM

== 7. Menampilkan Data Pada Tabel Qc

select * from qc

== 8. Menampilkan Data Pada Tabel Marketing

select * from marketing

== 9. Menampilkan Data Pada Tabel Delivery

select * from delivery

== 10. Menampilkan Data Pada Tabel Pembeli

select * from Pembeli

== 11. Menampilkan Data Pada Tabel Manager

select * from manager

== 12. Menampilkan Data Pada Tabel Direktur

select * from direktur

== 13. Menampilkan Data Pada Tabel Stok Khusus

select * from stok_khusus

== 14. Menampilkan Data Pada Tabel Stok Biasa

select * from stok_biasa

== 15. Menampilkan Data Pada Tabel Pesanan

select * from pesanan

== 16. Menampilkan Data Pada Tabel Pembelian Bahan

select * from pembelian_bahan

== 17. Menampilkan Data Pada Tabel Produksi

select * from produksi

== 18. Menampilkan Data Pada Tabel Pesanan Bahan

select * from pesanan_bahan

== 19. Menampilkan Data Pada Tabel Ceking Satu

select * from ceking_satu

== 20. Menampilkan Data Pada Tabel Ceking Dua

select * from ceking_dua

== 21. Menampilkan Data Pada Tabel Serah Barang

select * from serah_barang

== 22. Menampilkan Data Pada Tabel laporan Kirim Barang

select * from kirim_barang

== 22. Menampilkan Data Pada Tabel laporan Pembayaran

select * from pembayaran

== 22. Menampilkan Data Pada Tabel laporan Laporan Penjualan

select * from laporan_penjualan

== 22. Menampilkan Data Pada Tabel laporan Pembelian

select * from laporan_pembelian

C. Proses Menampilkan  Beberapa Kolom Dari Tabel

SELECT id_accounting , nama FROM accounting

SELECT id_kep_produksi , nama FROM kep_produksi

SELECT id_produsen , nama FROM produsen

SELECT id_esxquder , nama FROM esxquder

SELECT id_cirkuler, nama , alamat FROM cirkuler

SELECT id_ABM, nama FROM ABM

SELECT id_dc , nama FROM qc

SELECT id_marketing , nama FROM marketing

SELECT id_delivery , nama, alamat FROM delivery

SELECT kode_pembeli, nama FROM pembeli

SELECT id_manager , nama FROM manager

SELECT id_direktur , nama , alamat FROM direktur

SELECT kode_barang, namabarang , jenis FROM stok_khusus

SELECT kode_barang, namabarang , jumlah FROM stok_biasa

SELECT id_accounting, kode_barang,kode_pembeli FROM pesanan

SELECT kode_pembeli, id_produsen , tgl_pembelian FROM pembelian_bahan

SELECT kode_barang, id_abm FROM produksi

SELECT id_esxquder,id_cirkuler, id_abm FROM ceking_satu

D.            Menampilkan data berdasarkan kondisi Dalam Tabel

SELECT id_accounting , nama , alamat ,telp From accounting WHERE id_accounting ="30011"

SELECT id_produsen , nama , alamat , telp FROM produsen WHERE id_produsen = "80011"

E. Ubah(Update) data table

UPDATE cirkuler SET nama="retno dowel" WHERE id_cirkuler ="60011"

UPDATE pembeli SET alamat="Jl.watu bunjul oke" WHERE kode_pembeli="120014"

UPDATE direktur SET telp="081336434008" WHERE id_direktur="10011"

F. Hapus(Delete) data table

DELETE FROM stok_biasa WHERE kode_barang="B1-10"

DELETE FROM qc WHERE id_dc="90014"

DELETE FROM marketing WHERE id_marketing="100011"

I. Melihat Database

show databases;

J.Perintah menggunakan database yang telah dibuat

Use japfa

K. Perintah melihat table apa saja yang ada pada database :

Show tables;

L. Perintah melihat deskripsi table/melihat kolom apa saja beserta type, length yang ada pada table :

desc delivery;

desc ceking_dua;

M. Perintah menambah field pada table :

alter table produksi add nama varchar(15);

alter table serah_barang add telp varchar(20);

alter table laporan_penjualan add Website varchar(15);

O. Perintah mengganti nama field pada table :

alter table manager change nama nama_manager varchar(22);

alter table ABM change telp telp_ABM char(20);

P. Perintah mengubah ukuran (length) dari suatu field :

alter table pesanan modify kode_barang varchar(25);

alter table kirim_barang modify kode_pembeli int (20);

Q. Perintah menghapus field pada table :

alter table stok_khusus drop namabarang;

T. Perintah memanggil isi tabel yang dikelompokkan berdasarkan field tertentu:

select * from manager group by id_manager;

select * from pembayaran group by kode_pembeli;

U. Perintah memanggil isi tabel yang diurutkan secara ascending descending

select * from esxquder order by nama desc;

select * from kep_produksi order by nama desc;

V. Perintah mencari nilai maksimum pada suatu field pada tabel tertentu :

select max(jumlah) from stok_khusus;

select max(jumlah) from laporan_pembelian;

W. Perintah mencari nilai minimum pada suatu field pada tabel tertentu :

select min(jumlah) from laporan_penjualan;

select min(jumlah) from stok_biasa;

X. Perintah mencari banyaknya record/data dalam sebuah field pada sebuah tabel :

select count(jumlah) from laporan_penjualan;

select count(jumlah) from laporan_pembelian;

Y. Perintah menjumlahkan isi dari field:

select sum(jumlah) from stok_khusus;

select sum(jumlah) from stok_biasa;

Z. Perintah mencari rata2 nilai isi dari suatu field :

select sum(jumlah) from laporan_pembelian;

Z. Perintah menentukan standard deviasi isi dari suatu field:

select std(jumlah) from laporan_penjualan;

select std(jumlah) from laporan_pembelian;


create Database Rental

create table Film(kd_film char(6) Primary Key Not NULL,judul_film varchar(15),stok char(3),kd_jns_film char(3),harga_sewa char (6))
 create table Gol_Film(kd_gol_film char(10) Primary Key Not NULL,Nm_gol_film varchar(20))
 create table Pelanggan(kd_plg char(3) Primary Key Not NULL,Nm_plg varchar(15),ID_kota varchar(5),jns_ID varchar(5),tgl_daftar date,point char(2))
 create table Pinjam_2(kd_trs char(5)Primary Key Not NULL,kd_film char(5),tarif char(6),jml int (3),Total char(6))
 create table Pinjam(kd_trs char(5)Primary Key Not NULL,kd_plg char(3),tgl_pnjam date ,tot_jml_pnjam char(3),tot_biaya_pnjam char(6))
 create table Jns_Film(kd_jns_film char (3)Primary Key Not NULL ,nm_jns_film varchar (15))
 insert into Film value('F01','kuntilanak','G01','3','V','1500')
 insert into film value('F02','kuntibapak','G01','2','D','2000')
 insert into film value('F03','lenteramerah','G01','5','D','2000')
 insert into film value('F04','oscar','G06','2','V','1000')
 insert into film value('F05','transformer','G07','2','V','1000')
 insert into film value('F06','X-Men','G03','3','V','3000')
 insert into film value('F07','Titanic','G02','5','D','3000')
 insert into film value('F08','MrBean','G04','1','V','1000')
 insert into film value('F09','upin ipin','G06','3','D','1000')
 insert into film value('F10','jury','G05','8','D','2000')
 insert into Gol_Film value('G01','horor')
 insert into Gol_Film value('G02','romantic')
 insert into Gol_Film value('G03','action')
 insert into Gol_Film value('G04','komedi')
 insert into Gol_Film value('G05','drama')
 insert into Gol_Film value('G06','kartun')
 insert into Gol_Film value('G07','war')
 insert into pelanggan value('P01','surya','DPS','KTP','2009-06-02','4')
 insert into pelanggan value('P02','dewi','TBN','SIMA','2009-04-10','10')
 insert into pelanggan value('P03','ina','TBN','SIMC','2009-10-22','7')
 insert into pelanggan value('P04','agus','NGR','SIMC','2009-06-21','0')
 insert into pelanggan value('FO5','joko','GIR','KTP','2009-05-02','0')
 insert into Pinjam_ value('TOO1','F01','1500','1','1500')
 insert into Pinjam_2 value('T001','F03','2000','1','2000')
 insert into Pinjam_2 value('T002','F01','1500','1','1500')
 insert into Pinjam_2 value('T003','F02','2000','1','2000')
 insert into Pinjam_2 value('T003','F10','2000','1','2000')
 insert into Pinjam_2 value('T003','F07','3000','1','3000')
 insert into Pinjam_2 value('T004','F03','2000','1','2000')
 insert into Pinjam_2 value('T005','F02','2000','1','2000')
 insert into pinjam value('T001','P01','2009-06-10','2','3500')
 insert into pinjam value('T002','P02','2009-04-10','1','1500')
 insert into pinjam value('T003','P03','2009-10-22','3','7000')
 insert into pinjam value('T004','P04','2009-05-20','1','2000')
 insert into pinjam value('T005','P05','2009-10-28','1','2000')
 insert into jns_film value('V','VCD')
 insert into jns_film value('D','DVD')
 insert into jns_film value('L','Laser Disk')


<strong>(DATA)</strong>

/*
 SQLyog - Free MySQL GUI v5.19
 Host - 5.1.30-community : Database - rental_1111100690
 *********************************************************************
 Server version : 5.1.30-community
 */

SET NAMES utf8;

SET SQL_MODE='';

create database if not exists `rental_1111100690`;

USE `rental_1111100690`;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';

/*Table structure for table `film` */

DROP TABLE IF EXISTS `film`;

CREATE TABLE `film` (
 `kd_film` char(3) DEFAULT NULL,
 `judul_film` varchar(20) DEFAULT NULL,
 `kd_gol_film` char(3) DEFAULT NULL,
 `stok` varchar(3) DEFAULT NULL,
 `kd_jns_film` char(3) DEFAULT NULL,
 `harga_sewa` int(20) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `film` */

insert into `film` (`kd_film`,`judul_film`,`kd_gol_film`,`stok`,`kd_jns_film`,`harga_sewa`) values ('F01','KUNTILANAK','G01','3','V',1500);
 insert into `film` (`kd_film`,`judul_film`,`kd_gol_film`,`stok`,`kd_jns_film`,`harga_sewa`) values ('F02','KUNTILBAPAK','G01','2','D',2000);
 insert into `film` (`kd_film`,`judul_film`,`kd_gol_film`,`stok`,`kd_jns_film`,`harga_sewa`) values ('F03','LENTERA MERAH','G01','5','D',2000);
 insert into `film` (`kd_film`,`judul_film`,`kd_gol_film`,`stok`,`kd_jns_film`,`harga_sewa`) values ('F04','OSCAR','G06','2','V',1000);
 insert into `film` (`kd_film`,`judul_film`,`kd_gol_film`,`stok`,`kd_jns_film`,`harga_sewa`) values ('F05','TRANSFORMER','G07','2','D',10000);
 insert into `film` (`kd_film`,`judul_film`,`kd_gol_film`,`stok`,`kd_jns_film`,`harga_sewa`) values ('F06','X-MAN','G03','3','V',3000);
 insert into `film` (`kd_film`,`judul_film`,`kd_gol_film`,`stok`,`kd_jns_film`,`harga_sewa`) values ('F07','TITANIC','G02','5','D',3000);
 insert into `film` (`kd_film`,`judul_film`,`kd_gol_film`,`stok`,`kd_jns_film`,`harga_sewa`) values ('F08','MRBEAN','G04','1','V',1000);
 insert into `film` (`kd_film`,`judul_film`,`kd_gol_film`,`stok`,`kd_jns_film`,`harga_sewa`) values ('F09','UPIN IPIN','G06','3','D',1000);
 insert into `film` (`kd_film`,`judul_film`,`kd_gol_film`,`stok`,`kd_jns_film`,`harga_sewa`) values ('F10','JURY','G05','8','D',2000);
 insert into `film` (`kd_film`,`judul_film`,`kd_gol_film`,`stok`,`kd_jns_film`,`harga_sewa`) values ('F01','kuntilanak','G01','3','V',1500);
 insert into `film` (`kd_film`,`judul_film`,`kd_gol_film`,`stok`,`kd_jns_film`,`harga_sewa`) values ('F02','kuntibapak','G01','2','D',2000);
 insert into `film` (`kd_film`,`judul_film`,`kd_gol_film`,`stok`,`kd_jns_film`,`harga_sewa`) values ('F03','lenteramerah','G01','5','D',2000);
 insert into `film` (`kd_film`,`judul_film`,`kd_gol_film`,`stok`,`kd_jns_film`,`harga_sewa`) values ('F04','oscar','G06','2','V',1000);
 insert into `film` (`kd_film`,`judul_film`,`kd_gol_film`,`stok`,`kd_jns_film`,`harga_sewa`) values ('F05','transformer','G07','2','V',1000);
 insert into `film` (`kd_film`,`judul_film`,`kd_gol_film`,`stok`,`kd_jns_film`,`harga_sewa`) values ('F06','X-Men','G03','3','V',3000);
 insert into `film` (`kd_film`,`judul_film`,`kd_gol_film`,`stok`,`kd_jns_film`,`harga_sewa`) values ('F07','Titanic','G02','5','D',3000);
 insert into `film` (`kd_film`,`judul_film`,`kd_gol_film`,`stok`,`kd_jns_film`,`harga_sewa`) values ('F08','MrBean','G04','1','V',1000);
 insert into `film` (`kd_film`,`judul_film`,`kd_gol_film`,`stok`,`kd_jns_film`,`harga_sewa`) values ('F09','upin ipin','G06','3','D',1000);
 insert into `film` (`kd_film`,`judul_film`,`kd_gol_film`,`stok`,`kd_jns_film`,`harga_sewa`) values ('F10','jury','G05','8','D',2000);

/*Table structure for table `gol_film` */

DROP TABLE IF EXISTS `gol_film`;

CREATE TABLE `gol_film` (
 `kd_gol_film` char(3) DEFAULT NULL,
 `nm_gol_film` varchar(30) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `gol_film` */

insert into `gol_film` (`kd_gol_film`,`nm_gol_film`) values ('G01','HOROR');
 insert into `gol_film` (`kd_gol_film`,`nm_gol_film`) values ('G02','ROMANTIC');
 insert into `gol_film` (`kd_gol_film`,`nm_gol_film`) values ('G03','ACTION');
 insert into `gol_film` (`kd_gol_film`,`nm_gol_film`) values ('G04','KOMEDI');
 insert into `gol_film` (`kd_gol_film`,`nm_gol_film`) values ('G05','DRAMA');
 insert into `gol_film` (`kd_gol_film`,`nm_gol_film`) values ('G06','KARTUN');
 insert into `gol_film` (`kd_gol_film`,`nm_gol_film`) values ('G07','WAR');
 insert into `gol_film` (`kd_gol_film`,`nm_gol_film`) values ('G07','WAR');
 insert into `gol_film` (`kd_gol_film`,`nm_gol_film`) values ('G01','horor');
 insert into `gol_film` (`kd_gol_film`,`nm_gol_film`) values ('G02','romantic');
 insert into `gol_film` (`kd_gol_film`,`nm_gol_film`) values ('G03','action');
 insert into `gol_film` (`kd_gol_film`,`nm_gol_film`) values ('G07','war');

/*Table structure for table `jenis_film` */

DROP TABLE IF EXISTS `jenis_film`;

CREATE TABLE `jenis_film` (
 `kd_jns_film` char(5) DEFAULT NULL,
 `nama_jns_film` varchar(10) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `jenis_film` */

insert into `jenis_film` (`kd_jns_film`,`nama_jns_film`) values ('V','VCD');
 insert into `jenis_film` (`kd_jns_film`,`nama_jns_film`) values ('D','DVD');
 insert into `jenis_film` (`kd_jns_film`,`nama_jns_film`) values ('L','LASER DISK');

/*Table structure for table `pelanggan` */

DROP TABLE IF EXISTS `pelanggan`;

CREATE TABLE `pelanggan` (
 `kd_plg` char(3) DEFAULT NULL,
 `nm_plg` varchar(20) DEFAULT NULL,
 `ID_kota` char(3) DEFAULT NULL,
 `jns_ID` varchar(3) DEFAULT NULL,
 `tgl_daftar` date DEFAULT NULL,
 `point` int(10) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `pelanggan` */

insert into `pelanggan` (`kd_plg`,`nm_plg`,`ID_kota`,`jns_ID`,`tgl_daftar`,`point`) values ('P01','SURYA','DPS','KTP','2009-06-02',2);
 insert into `pelanggan` (`kd_plg`,`nm_plg`,`ID_kota`,`jns_ID`,`tgl_daftar`,`point`) values ('P02','DEWI','TBN','SIM','2009-04-10',10);
 insert into `pelanggan` (`kd_plg`,`nm_plg`,`ID_kota`,`jns_ID`,`tgl_daftar`,`point`) values ('P03','INA','TBN','SIM','2009-10-22',7);
 insert into `pelanggan` (`kd_plg`,`nm_plg`,`ID_kota`,`jns_ID`,`tgl_daftar`,`point`) values ('P04','AGUS','NGR','SIM','2009-06-21',0);
 insert into `pelanggan` (`kd_plg`,`nm_plg`,`ID_kota`,`jns_ID`,`tgl_daftar`,`point`) values ('P05','JOKO','GIR','KTP','2009-05-02',0);
 insert into `pelanggan` (`kd_plg`,`nm_plg`,`ID_kota`,`jns_ID`,`tgl_daftar`,`point`) values ('P01','surya','DPS','KTP','2009-06-02',4);
 insert into `pelanggan` (`kd_plg`,`nm_plg`,`ID_kota`,`jns_ID`,`tgl_daftar`,`point`) values ('P02','dewi','TBN','SIM','2009-04-10',10);
 insert into `pelanggan` (`kd_plg`,`nm_plg`,`ID_kota`,`jns_ID`,`tgl_daftar`,`point`) values ('P03','ina','TBN','SIM','2009-10-22',7);
 insert into `pelanggan` (`kd_plg`,`nm_plg`,`ID_kota`,`jns_ID`,`tgl_daftar`,`point`) values ('P04','agus','NGR','SIM','2009-06-21',0);
 insert into `pelanggan` (`kd_plg`,`nm_plg`,`ID_kota`,`jns_ID`,`tgl_daftar`,`point`) values ('FO5','joko','GIR','KTP','2009-05-02',0);

/*Table structure for table `pinjam` */

DROP TABLE IF EXISTS `pinjam`;

CREATE TABLE `pinjam` (
 `kd_trs` char(5) DEFAULT NULL,
 `kd_plg` varchar(3) DEFAULT NULL,
 `tgl_pinjam` date DEFAULT NULL,
 `tot_jml_pinjam` int(10) DEFAULT NULL,
 `tot_biaya_pinjam` int(30) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `pinjam` */

insert into `pinjam` (`kd_trs`,`kd_plg`,`tgl_pinjam`,`tot_jml_pinjam`,`tot_biaya_pinjam`) values ('T001','P01','2009-06-02',2,3500);
 insert into `pinjam` (`kd_trs`,`kd_plg`,`tgl_pinjam`,`tot_jml_pinjam`,`tot_biaya_pinjam`) values ('T002','P02','2009-04-10',1,1500);
 insert into `pinjam` (`kd_trs`,`kd_plg`,`tgl_pinjam`,`tot_jml_pinjam`,`tot_biaya_pinjam`) values ('T003','P03','2009-10-22',3,7000);
 insert into `pinjam` (`kd_trs`,`kd_plg`,`tgl_pinjam`,`tot_jml_pinjam`,`tot_biaya_pinjam`) values ('T004','P02','2009-05-20',1,2000);
 insert into `pinjam` (`kd_trs`,`kd_plg`,`tgl_pinjam`,`tot_jml_pinjam`,`tot_biaya_pinjam`) values ('T005','P03','2009-10-28',1,2000);
 insert into `pinjam` (`kd_trs`,`kd_plg`,`tgl_pinjam`,`tot_jml_pinjam`,`tot_biaya_pinjam`) values ('T001','P01','2009-06-10',2,3500);
 insert into `pinjam` (`kd_trs`,`kd_plg`,`tgl_pinjam`,`tot_jml_pinjam`,`tot_biaya_pinjam`) values ('T002','P02','2009-04-10',1,1500);
 insert into `pinjam` (`kd_trs`,`kd_plg`,`tgl_pinjam`,`tot_jml_pinjam`,`tot_biaya_pinjam`) values ('T003','P03','2009-10-22',3,7000);
 insert into `pinjam` (`kd_trs`,`kd_plg`,`tgl_pinjam`,`tot_jml_pinjam`,`tot_biaya_pinjam`) values ('T005,','P05','2009-10-28',1,2000);

/*Table structure for table `pinjam_2` */

DROP TABLE IF EXISTS `pinjam_2`;

CREATE TABLE `pinjam_2` (
 `kd_trs` char(4) DEFAULT NULL,
 `kd_film` char(3) DEFAULT NULL,
 `tarof` int(3) DEFAULT NULL,
 `jml` int(2) DEFAULT NULL,
 `total` int(10) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `pinjam_2` */

insert into `pinjam_2` (`kd_trs`,`kd_film`,`tarof`,`jml`,`total`) values ('T005','F03',2000,1,2000);

/*Table structure for table `pinjam_dua` */

DROP TABLE IF EXISTS `pinjam_dua`;

CREATE TABLE `pinjam_dua` (
 `kd_trs` char(5) DEFAULT NULL,
 `kd_film` varchar(5) DEFAULT NULL,
 `tarif` int(30) DEFAULT NULL,
 `jml` int(10) DEFAULT NULL,
 `total` int(30) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `pinjam_dua` */

insert into `pinjam_dua` (`kd_trs`,`kd_film`,`tarif`,`jml`,`total`) values ('T001','F01',1500,1,1500);
 insert into `pinjam_dua` (`kd_trs`,`kd_film`,`tarif`,`jml`,`total`) values ('T01','F03',3000,1,2000);
 insert into `pinjam_dua` (`kd_trs`,`kd_film`,`tarif`,`jml`,`total`) values ('T002','F01',1500,1,1500);
 insert into `pinjam_dua` (`kd_trs`,`kd_film`,`tarif`,`jml`,`total`) values ('T003','F02',2000,1,2000);
 insert into `pinjam_dua` (`kd_trs`,`kd_film`,`tarif`,`jml`,`total`) values ('T003','F10',2000,1,2000);
 insert into `pinjam_dua` (`kd_trs`,`kd_film`,`tarif`,`jml`,`total`) values ('T03','F07',2000,1,2000);
 insert into `pinjam_dua` (`kd_trs`,`kd_film`,`tarif`,`jml`,`total`) values ('T004','F03',2000,1,2000);
 insert into `pinjam_dua` (`kd_trs`,`kd_film`,`tarif`,`jml`,`total`) values ('T005','F03',2000,1,2000);

SET SQL_MODE=@OLD_SQL_MODE;
 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s