Materi Praktik MYSQL CREATE, READ, UPDATE, DELETE (CRUD)

CREATE, READ, UPDATE, DELETE (CRUD)

CREATE

- CREATE DATABASE

- CREATE TABLE

- MEMBUAT DATA / RECORD YANG BARU

create database <namadatabase>;

===========================================

(1).

create database indomaret;

===========================================

secara umum ada 5 jenis variable umum

1. CHARACTER

- ciri-ciri diawali dan diakhiri dengan tanda petik

contoh: "1234abce", '1234abcde'

- maximal digit 255 digit

1+1 = 2

"1" + "1" = "11"

1+"11"

2. number

3. date

- YYYYMMDD - 20201231

4.logical

- .t. (true) atau .f. (false)

5. memo

- mampu menampung lebih dari 255 digit

mysql (type data)

========

1. varchar

- digunakan untuk menyimpan lebih dari 1 digit character

2. char

- untuk menyimpan 1 digit

3. double

create table <namatabel>

(

field 1 fieldtype(fieldlength) not null default "",

field2 fieldtype(fieldlength) not null defaultt "",

field 3 double not null default "0",

primary key (field1)

) engine=myisam;

===============================================

(2).

create table pelanggan

(

nolanggan varchar(12) not null default "",

nama varchar(50) not null default "",

jk char(1) not null default "L",

nohp varchar(20) not null default "",

primary key (nolanggan)

) engine=myisam;

===============================================

perintah membuat record

insert into <namatabel>

(field1,field2,field3,...)

values

(value1,value2,value3,...);

untuk menambahkan data ada 2 metode

a. one-to-one

b. one-to-many

============================================================

(3)

a. one-to-one

insert into pelanggan

(nolanggan,nama,jk,nohp)

values

("12345","joni","L","0811");

b. one-to-many

insert into pelanggan

(nolanggan,nama,jk,nohp)

values

("12346","Yana","P","0822"),

("12347","Yuli","P","0711");

=============================================================

4. perintah update

update <namatabel> set

field1=value1,field2=value2,field3=value3

where <kriteria>

============================================================

(4)

update pelanggan set

nama="Joni Nowo Darsono",

nohp="0855"

where nolanggan="12345";

============================================================

5. hapus data

delete from <namatabel>

where <kriteria>;

contoh:

delete from buku; maka seluruh isi buku dihapus

============================================================

(5)

delete from pelanggan

where nolanggan="12347";

============================================================

(6)

create table barang

(

kdbrg varchar(5) not null default "",

nmbrg varchar(50) not null default "",

unit double not null default "0",

hrgbeli double not null default "0",

hrgjual double not null default "0",

primary key (kdbrg)

) engine=myisam;

============================================================

(7)

insert into barang

(kdbrg,nmbrg,unit,hrgbeli,hrgjual)

values

("10001","buku tulis",10,10000,12500),

("10002","pensil 2b",15,5000,7500),

("10003","kalkulator",5,40000,42500);

============================================================

(8)

create table order1

(

noorder varchar(12) not null default "",

nolanggan varchar(12) not null default "",

jumtotal double not null default "0",

pembayaran double not null default "0",

sisa double not null default "0",

primary key (noorder)

) engine=myisam;

============================================================

(9)

insert into order1

(noorder,nolanggan)

values

("20180105001","12345"),

("20180105005","12347"),

("20180105006","12345");

============================================================

(10)

create table order1_list

(

noorder varchar(12) not null default "",

kdbrg varchar(5) not null default "",

unit double not null default "0",

hrgjual double not null default "0",

primary key (noorder,kdbrg)

) engine=myisam;

============================================================

(11)

insert into order1_list

(noorder,kdbrg,unit,hrgjual)

values

("20180105001","10001",2,12500),

("20180105001","10002",1,7500),

("20180105006","10003",1,42500),

("20180105005","10002",2,7500),

("20180105005","10003",1,42500);

============================================================

menampilkan tabel induk : order1_list

noorder,nolanggan,nama,kdbrg,nmbrg,unit,hrgjual

maka untuk menjalankan select

select order1_list.noorder,

order1.nolanggan,

pelanggan.nama,

order1_list.kdbrg,

barang.nmbrg,

order1_list.unit,

order1_list.hrgjual

from order1_list

left join order1 on order1.noorder=order1_list.noorder

left join pelanggan on pelanggan.nolanggan=order1.nolanggan

left join barang on barang.kdbrg=order1_list.kdbrg

order by order1_list.noorder,order1_list.kdbrg;

12. menampilkan data

select * / field1,field2,...

from tabel1

left join tabel2 on tabel1.field1=tabel2.field1

where kondisi

order by fieldx;

============================================================

(12).

select * from pelanggan

============================================================

(13).

select nolanggan,

nama,

nohp

from pelanggan order by nama;

============================================================

(14).

select pelanggan.nolanggan,

pelanggan.nama,

if(pelanggan.jk="L","LAKI-LAKI","PEREMPUAN") as JenisKelamin

from pelanggan;

============================================================

excel = concatenate(text1,text2,...)

mysql=concat(text1,text2)

left(var,n)

============================================================

(15).

select nolanggan,

concat(left(nama,1),jk) as kode

from pelanggan;

============================================================

(16).

select order1.noorder,

order1.nolanggan,

pelanggan.nama

from order1

left join pelanggan on pelanggan.nolanggan=order1.nolanggan;

============================================================

(17).

select order1_list.noorder,

order1_list.noorder,

pelanggan.nama,

order1_list.kdbrg,

barang.nmbrg,

order1_list.unit,

order1_list.hrgjual,

(order1_list.unit*order1_list.hrgjual) as total

from order1_list

left join order1 on order1.noorder=order1_list.noorder

left join pelanggan on pelanggan.nolanggan=order1.nolanggan

left join barang on barang.kdbrg=order1_list.kdbrg;

============================================================

(18).

update barang set

hrgjual=hrgjual+((20/100)*hrgjual);

============================================================

(19).

select order1_list.noorder,

order1.nolanggan,

(order1_list.unit*order1_list.hrgjual) as total,

if((order1_list.unit*order1_list.hrgjual)>1000000,

(10/100)*(order1_list.unit*order1_list.hrgjual),0) as diskon,

(order1_list.unit*order1_list.hrgjual)-if((order1_list.unit*order1_list.hrgjual)>1000000,

(10/100)*(order1_list.unit*order1_list.hrgjual),0) as terima

from order1_list

left join order1 on order1.noorder=order1_list.noorder;

============================================================

soal

-----

1) tuliskan perintah untuk membuat database

dengan nama dbcoba

2) tuliskan perintah untuk membuat tabel dengan

struktur sbb:

nama tabel: mhs

---------------------------------------------------------

field field field

name type length

---------------------------------------------------------

nim varchar 12

nama varchar 50

jk char 1

nilai1 double

nilai2 double

nilai3 double

--------------------------------------------------------

index: nim

---------------------------------------------------------

3) tuliskan perintah untuk menambahkan data berikut ini

ke dalam tabel mhs

---------------------------------------------------------

nim nama jk nilai1 nilai2 nilai3

---------------------------------------------------------

0101 joni L 50 75 65

0107 yuli P 75 70 75

0106 jaka L 65 65 60

---------------------------------------------------------

a. record #1 ditambahkan dengan one-to-one

b. Record #2 dan #3 ditambahkan dengan one-to-many

4) tuliskan perintah untuk mengubah nilai2 mhs

dengan nama joni, menjadi 80

5) tuliskan perintah untuk menaikkan kolom / field

nilai1 sebesar 20% seluruh mhs

diketahui nilai adalah 60, didalam 60 tsb

ada kenaikan nilai lama sebesar 20% berapa nilai

sebelumnya?

60=x+((20/100)*x)

6) tuliskan perintah untuk menurunkan nilai3 sebesar

20% untuk seluruh mhs yg berjenis kelamin laki-laki

(karena tawuran)

7) sama seperti kasus nomor 6, tanpa menggunakan

perintah WHERE.

8) hapus seluruh data (idak usah dicoba)

SOAL UJIAN

FAKULTAS EKONOMI

UNIVERSITAS SRIWIJAYA

Mata Kuliah	:	PENGANTAR APLIKASI KOMPUTER
Sifat Ujian	:	TUTUP BUKU
Waktu	:	-
Penguji	:	Tim Pengantar Aplikasi Komputer
Nama Dosen Pengajar	:	Aryanto

a. Tuliskan perintah SQL untuk membuat database dengan nama dbujian2021. (Point: 5)

...........................................................................................................................................................

b. Tuliskan perintah SQL untuk membuat tabel dbmsbarang dengan struktur dibawah ini. (Index berdasarkan field: KDBRG) (Point: 10)

Field_Name	Field_type	Field_Length
KDBRG	CHAR	1
NMBRG	VARCHAR	25
HARGA	DOUBLE

...........................................................................................................................................................

c. Tuliskan perintah SQL untuk menambahkan data dibawah ini ke dalam tabel dbmsbarang. (point: 10)

KDBRG	NMBRG	HARGA
A	AMANAH	1000
B	TABLIGH	1500
C	FATHONAH	2000
D	SIDDIQ	2500

...........................................................................................................................................................

d. Tuliskan perintah SQL untuk membuat tabel dbtransaksi dengan struktur dibawah ini. (Index berdasarkan field: NOTRAN dan KDBRG) (point: 10)

Field_Name	Field_type	Field_Length
NOTRAN	VARCHAR	5
KDBRG	CHAR	1
UNITJUAL	DOUBLE
TYPEBAYAR	CHAR	1
TGTRAN	DATE

...........................................................................................................................................................

e. Tuliskan perintah SQL untuk menambahkan data dibawah ini ke dalam tabel dbtransaksi. (point: 10)

NOTRAN	KDBRG	UNITJUAL	TYPEBAYAR	TGTRAN
10001	A	5	C	28/01/2010
10002	B	10	O	15/03/2010
10003	C	5	R	01/04/2010
10004	D	6	B	02/04/2010

...........................................................................................................................................................

f. Tuliskan perintah SQL untuk menampilkan informasi dibawah ini. (Point: 30)

notran	Kdbrg	nmbrg	unitjual	TYPEBAYAR	Ketbayar	TGTRAN
10001	A	AMANAH	5	C	CASH	28/01/2010
10002	B	TABLIGH	10	O	OUTSTANDING	15/03/2010
10003	C	FATHONAH	5	R	CREDITCARD	01/04/2010
10004	D	SIDDIQ	6	B	BANK	02/04/2010

...........................................................................................................................................................

Keterangan:

Lembar soal ini di print / di cetak

Kemudian jawaban anda ditulis dengan tangan (jangan lupa tulis nim, nama dan ttd (tandatangan)

Kemudian photo / scan jawaban anda

Hasil photo / scan tersebut jadikan 1 file PDF

Simpan dengan nama file: 04_aplikom_nim_nama_yyyyddmm

Yyyyddmm adalah tanggal ujian contoh: 20210208

Kemudian upload pada tempat yang telah disediakan.

JAWABAN UJIAN:

create database dbujian2021

create table dbmsbarang

(

KDBRG char(1) not null default "",

NMBRG varchar(25) not null default "",

HARGA double not null default "0",

primary key (KDBRG)

) engine=myisam;

insert into dbmsbarang

(KDBRG,NMBRG,HARGA)

values

("A","AMANAH","1000"),

("B","TABLIGH","1500"),

("C","FATHONAH","2000"),

("D","SIDDIQ","2500");

create table dbtransaksi

(

NOTRAN varchar(5) not null default "",

KDBRG char(1) not null default "",

UNITJUAL double not null default "0",

TYPEBAYAR char(1) not null default "",

TGTRAN date not null default "0",

primary key(NOTRAN,KDBRG)

) engine=myisam;

insert into dbtransaksi

(NOTRAN,KDBRG,UNITJUAL,TYPEBAYAR,TGTRAN)

values

("10001","A","5","C","28/01/2010"),

("10002","B","10","O","15/03/2010"),

("10003","C","5","R","01/04/2010"),

("10004","D","6","B","02/04/2010");

select dbtransaksi.NOTRAN,

dbtransaksi.KDBRG,

dbmsbarang.KDBRG,

dbmsbarang.NMBRG,

dbtransaksi.UNITJUAL,

dbtransaksi.TYPEBAYAR,

if(dbtransaksi.TYPEBAYAR="C","CASH","O","OUTSTANDING","R","CREDITCARD","BANK") as Ketbayar,

dbtransaksi.TGTRAN

from dbmsbarang

left join dbmsbarang on dbmsbarang.KDBRG=.dbtransaksi.KDBRG;

create database dbujian2021

create table dbmsbarang

(

KDBRG char(1) not null default "",

NMBRG varchar(25) not null default "",

HARGA double not null default "0",

primary key (KDBRG)

) engine=myisam;

insert into dbmsbarang

(KDBRG,NMBRG,HARGA)

values

("A","AMANAH","1000"),

("B","TABLIGH","1500"),

("C","FATHONAH","2000"),

("D","SIDDIQ","2500");

create table dbtransaksi

(

NOTRAN varchar(5) not null default "",

KDBRG char(1) not null default "",

UNITJUAL double not null default "0",

TYPEBAYAR char(1) not null default "",

TGTRAN date not null default "0",

primary key(NOTRAN,KDBRG)

) engine=myisam;

insert into dbtransaksi

(NOTRAN,KDBRG,UNITJUAL,TYPEBAYAR,TGTRAN)

values

("10001","A","5","C","2010/01/28"),

("10002","B","10","O","2010/03/15"),

("10003","C","5","R","2010/04/01"),

("10004","D","6","B","2010/04/02");

select dbtransaksi.NOTRAN,

dbtransaksi.KDBRG,

dbmsbarang.NMBRG,

dbtransaksi.UNITJUAL,

dbtransaksi.TYPEBAYAR,

Case when dbtransaksi.TYPEBAYAR="C" then "CASH"

when dbtransaksi.TYPEBAYAR="O" then "OUTSTANDING"

when dbtransaksi.TYPEBAYAR="R" then "CREDITCARD"

when dbtransaksi.TYPEBAYAR="B" then "BANK"

end as Ketbayar,

dbtransaksi.TGTRAN

from dbtransaksi

left join dbmsbarang on dbmsbarang.KDBRG=dbtransaksi.KDBRG;

Materi Praktik MYSQL CREATE, READ, UPDATE, DELETE (CRUD) - Contoh Praktik MYSQL

Featured

Memperkaya CV Anda dengan 20+ Hobi yang Tepat

Makna Simbol dari Lambang Negara, Sejarah, dan Dasar Hukum Negara

MAKALAH AKUNTANSI PERUSAHAAN DAGANG

Contoh Essai Motivation Letter/ Esai Motivasi Beasiswa tentang Gambaran Motivasi, Tujuan Hidup dan Jenjang Karir

MENYAJIKAN GAGASAN MELALUI ARTIKEL

OMNIBUS LAW “CIPTA KERJA” : KONSEP DAN PENGARUHNYA BAGI INDONESIA

Materi Praktik MYSQL CREATE, READ, UPDATE, DELETE (CRUD) - Contoh Praktik MYSQL

You might like

Featured

Memperkaya CV Anda dengan 20+ Hobi yang Tepat