SISTEM INFORMASI APOTIK

Membuat database bernama: “apotik”

mysql> create database apotik; Query OK, 1 row affected (0.00 sec)

Memanggil database “apotik” untuk mengecek bahwa pembuatan database “apotik” berhasil

mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | apotik             | | mysql              | | test               | +--------------------+ 4 rows in set (0.00 sec)

Menggunakan database “apotik” untuk mulai pembuatan manipulasi tabel dan record

mysql> use apotik; Database changed

               

Membuat tabel “pegawai” dengan field [id_peg, nama_peg, jk, alamat_peg, tlp_peg] dan memberi primary key [id_peg]

mysql> create table pegawai(id_peg varchar(3) NOT NULL,nama_peg varchar(25),jk enum('L','P'),alamat_peg varchar(30),tlp_peg varchar(20),primary key(id_peg)); Query OK, 0 rows affected (0.08 sec)

Membuat tabel “obat” dengan field [id_obat, nama_obat, jenis_obat, harga_jual] dan memberi primary key [id_obat]

mysql> create table obat(id_obat varchar(3) NOT NULL,nama_obat varchar(25),jenis_obat varchar(25),harga_jual varchar(7),primary key(id_obat)); Query OK, 0 rows affected (0.47 sec)

Membuat tabel “supplier” dengan field [id_supplier, nama_supplier, alamat_supplier, tlp_supplier] dan memberi primary key [id_supplier]

mysql> create table supplier(id_supplier varchar(3) NOT NULL,nama_supplier varchar(25),alamat_supplier varchar(30),tlp_supplier varchar(20),primary key(id_supplier)); Query OK, 0 rows affected (0.20 sec)

Membuat tabel “customer” dengan field [id_customer, nama_customer, alamat_customer, tlp_customer] dan memberi primary key [id_customer]

mysql> create table customer(id_customer varchar(3) NOT NULL,nama_customer varchar(25),alamat_customer varchar(30),tlp_customer varchar(20),primary key(id_customer)); Query OK, 0 rows affected (0.14 sec)

Membuat tabel “penjualan_obat” dengan field [id_trsJual, id_customer, nama_customer, alamat_customer, tgl_jual, id_obat, nama_obat, jenis_obat] dan memberi primary key [id_trsJual]

mysql> create table penjualan_obat(id_trsJual varchar(3) NOT NULL,id_customer varchar(3),nama_customer varchar(25),alamat_customer varchar(30),tgl_jual date,id_obat   varchar(3),nama_obat varchar(25),jenis_obat varchar(25),primary key(id_trsJual)); Query OK, 0 rows affected (0.14 sec)

Membuat tabel “pembelian_obat” dengan field [id_trsBeli, id_obat, nama_obat, alamat_obat, tgl_beli, id_supplier, nama_supplier, alamat_supplier] dan memberi primary key [id_trsBeli]

mysql> create table pembelian_obat(id_trsBeli varchar(3) NOT NULL,id_obat varchar(3),nama_obat varchar(25),jenis_obat varchar(25),tgl_beli date,id_supplier varchar(3),nama_supplier varchar(25), alamat_supplier varchar(30),primary key(id_trsBeli)); Query OK, 0 rows affected (0.09 sec)

Mengisi data pada tabel “pegawai”

mysql> insert into pegawai(id_peg,nama_peg,jk,alamat_peg,tlp_peg) values('P10','Ani','P','Sidoarjo','085789893045'); Query OK, 1 row affected (0.03 sec) mysql> insert into pegawai(id_peg,nama_peg,jk,alamat_peg,tlp_peg) values('P20','Dani','L','Surabaya','085653217690'); Query OK, 1 row affected (0.05 sec) mysql> insert into pegawai(id_peg,nama_peg,jk,alamat_peg,tlp_peg) values('P30','Zahra','P','Surabaya','08987065433'); Query OK, 1 row affected (0.08 sec) mysql> insert into pegawai(id_peg,nama_peg,jk,alamat_peg,tlp_peg) values('P40','Agus','L','Jakarta','083865534213'); Query OK, 1 row affected (0.06 sec) mysql> insert into pegawai(id_peg,nama_peg,jk,alamat_peg,tlp_peg) values('P50','Nayla','P','Malang','087899896507'); Query OK, 1 row affected (0.05 sec)

Mengisi data pada tabel “obat”

mysql> insert into obat(id_obat,nama_obat,jenis_obat,harga_jual) values('O01','tolak angin','sirup','6500'); Query OK, 1 row affected (0.05 sec) mysql> insert into obat(id_obat,nama_obat,jenis_obat,harga_jual) values('O02','bodrex','tablet','20500'); Query OK, 1 row affected (0.05 sec) mysql> insert into obat(id_obat,nama_obat,jenis_obat,harga_jual) values('O03','minyak telon','oles','15000'); Query OK, 1 row affected (0.05 sec) mysql> insert into obat(id_obat,nama_obat,jenis_obat,harga_jual) values('O04','mixagrip','tablet','3000'); Query OK, 1 row affected (0.05 sec) mysql> insert into obat(id_obat,nama_obat,jenis_obat,harga_jual) values('O05','extrajoss','serbuk','2500'); Query OK, 1 row affected (0.05 sec)

Mengisi data pada tabel “supplier”

mysql> insert into supplier (id_supplier,nama_supplier,alamat_supplier,tlp_supplier) values('S01','CV.Mandiri','Malang','085678904332'); Query OK, 1 row affected (0.05 sec) mysql> insert into supplier (id_supplier,nama_supplier,alamat_supplier,tlp_supplier) values('S02','CV.Sentosa','Bekasi','085765784531'); Query OK, 1 row affected (0.05 sec) mysql> insert into supplier (id_supplier,nama_supplier,alamat_supplier,tlp_supplier) values('S03','CV.Makmur Jaya','Surabaya','083845769090'); Query OK, 1 row affected (0.03 sec) mysql> insert into supplier (id_supplier,nama_supplier,alamat_supplier,tlp_supplier) values('S04','CV.Abadi Selalu','Jakarta','08980076453'); Query OK, 1 row affected (0.05 sec) mysql> insert into supplier (id_supplier,nama_supplier,alamat_supplier,tlp_supplier) values('S05','CV.Cahya Sukses','Jakarta','081970553223'); Query OK, 1 row affected (0.03 sec)

Mengisi data pada tabel “customer”

mysql> insert into customer (id_customer,nama_customer,alamat_customer,tlp_customer) values('C01','Agus Permana','Batu','085678789090'); Query OK, 1 row affected (0.06 sec) mysql> insert into customer (id_customer,nama_customer,alamat_customer,tlp_customer) values('C02','RS.Villate','Malang','083821234589'); Query OK, 1 row affected (0.05 sec) mysql> insert into customer (id_customer,nama_customer,alamat_customer,tlp_customer) values('C03','RS.Siti Hajar','Jember','081978564231'); Query OK, 1 row affected (0.05 sec) mysql> insert into customer (id_customer,nama_customer,alamat_customer,tlp_customer) values('C04','Siti Jazilah','Jember','081987896545'); Query OK, 1 row affected (0.05 sec) mysql> insert into customer (id_customer,nama_customer,alamat_customer,tlp_customer) values('C05','PT.Bintang Lima','Malang','085608064233'); Query OK, 1 row affected (0.03 sec)

Mengisi data pada tabel “penjualan_obat”

mysql> insert into penjualan_obat (id_trsJual,id_customer,nama_customer,alamat_customer, tgl_jual,id_obat,nama_obat,jenis_obat) values ('J01','C03','RS.Siti Hajar','Jember', '2012-09-02','O04','mixagrip','tablet'); Query OK, 1 row affected (0.03 sec) mysql> insert into penjualan_obat (id_trsJual,id_customer,nama_customer,alamat_customer, tgl_jual,id_obat,nama_obat,jenis_obat) values ('J02','C05','PT.Bintang Lima','Malang', '2012-03-11','O04','mixagrip','tablet'); Query OK, 1 row affected (0.05 sec) mysql> insert into penjualan_obat (id_trsJual,id_customer,nama_customer,alamat_customer, tgl_jual,id_obat,nama_obat,jenis_obat) values ('J03','C01','Agus Permana','Batu', '2013-01-13','O01','tolak angin','sirup'); Query OK, 1 row affected (0.05 sec) mysql> insert into penjualan_obat (id_trsJual,id_customer,nama_customer,alamat_customer, tgl_jual,id_obat,nama_obat,jenis_obat) values ('J04','C04','Siti Jazilah','Jember', '2013-02-21','O05','extrajoss','serbuk'); Query OK, 1 row affected (0.05 sec) mysql> insert into penjualan_obat (id_trsJual,id_customer,nama_customer,alamat_customer, tgl_jual,id_obat,nama_obat,jenis_obat) values ('J05','C02','RS.Villate','Malang', '2013-03-03','O03','minyak telon','oles'); Query OK, 1 row affected (0.05 sec)

Mengisi data pada tabel “pembelian_obat”

mysql> insert into pembelian_obat (id_trsBeli,id_obat,nama_obat,jenis_obat,tgl_beli, id_supplier,nama_supplier,alamat_supplier) values('B01','O03','minyak telon','oles','2012-04-03', 'S04','CV.Abadi Selalu','Jakarta'); Query OK, 1 row affected (0.05 sec) mysql> insert into pembelian_obat (id_trsBeli,id_obat,nama_obat,jenis_obat,tgl_beli, id_supplier,nama_supplier,alamat_supplier) values('B02','O04','mixagrip','tablet','2012-05-12', 'S02','CV.Sentosa','Bekasi'); Query OK, 1 row affected (0.05 sec) mysql> insert into pembelian_obat (id_trsBeli,id_obat,nama_obat,jenis_obat,tgl_beli, id_supplier,nama_supplier,alamat_supplier) values('B03','O04','mixagrip','tablet','2012-08-10', 'S01','CV.Mandiri','Malang'); Query OK, 1 row affected (0.05 sec) mysql> insert into pembelian_obat (id_trsBeli,id_obat,nama_obat,jenis_obat,tgl_beli, id_supplier,nama_supplier,alamat_supplier) values('B04','O01','tolak angin','sirup','2012-10-20', 'S04','CV.Abadi Selalu','Jakarta'); Query OK, 1 row affected (0.03 sec) mysql> insert into pembelian_obat (id_trsBeli,id_obat,nama_obat,jenis_obat,tgl_beli, id_supplier,nama_supplier,alamat_supplier) values('B05','O02','bodrex','tablet','2012-12-05','S03','CV.Makmur Jaya','Surabaya'); Query OK, 1 row affected (0.03 sec)

Menampilkan nama dan jenis obat pada tabel obat dan Menampilkan nama pegawai dan alamat pegawai pada tabel pegawai

mysql> select nama_obat,jenis_obat,harga_jual from obat; +--------------+------------+------------+ | nama_obat    | jenis_obat | harga_jual | +--------------+------------+------------+ | tolak angin  | sirup      | 6500       | | bodrex       | tablet     | 20500      | | minyak telon | oles       | 15000      | | mixagrip     | tablet     | 3000       | | extrajoss    | serbuk     | 2500       | +--------------+------------+------------+ 5 rows in set (0.00 sec) mysql> select nama_peg,alamat_peg from pegawai; +----------+------------+ | nama_peg | alamat_peg | +----------+------------+ | Ani      | Sidoarjo   | | Dani     | Surabaya   | | Zahra    | Surabaya   | | Agus     | Jakarta    | | Nayla    | Malang     | +----------+------------+ 5 rows in set (0.03 sec)

Menambah data supplier pada table supplier dengan INSERT

mysql> insert into supplier (id_supplier,nama_supplier,alamat_supplier,tlp_supplier) values('S06','CV.Agung Perkasa','Bojonegoro','08996754867'); Query OK, 1 row affected (0.05 sec) mysql> select*from supplier; +-------------+------------------+-----------------+--------------+ | id_supplier | nama_supplier    | alamat_supplier | tlp_supplier | +-------------+------------------+-----------------+--------------+ | S01         | CV.Mandiri       | Malang          | 085678904332 | | S02         | CV.Sentosa       | Bekasi          | 085765784531 | | S03         | CV.Makmur Jaya   | Surabaya        | 083845769090 | | S04         | CV.Abadi Selalu  | Jakarta         | 08980076453  | | S05         | CV.Cahya Sukses  | Jakarta         | 081970553223 | | S06         | CV.Agung Perkasa | Bojonegoro      | 08996754867  | +-------------+------------------+-----------------+--------------+ 6 rows in set (0.00 sec)

Mengubah  data nama_supplier=CV.Sentosa menjadi nama_supplier=CV.Sejahtera yang memilki id_supplier=S02 dengan UPDATE

mysql> update supplier set nama_supplier='CV.Agung Perkasa' where id_supplier='S02'; Query OK, 1 row affected (0.06 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> select*from supplier; +-------------+------------------+-----------------+--------------+ | id_supplier | nama_supplier    | alamat_supplier | tlp_supplier | +-------------+------------------+-----------------+--------------+ | S01         | CV.Mandiri       | Malang          | 085678904332 | | S02         | CV.Agung Perkasa | Bekasi          | 085765784531 | | S03         | CV.Makmur Jaya   | Surabaya        | 083845769090 | | S04         | CV.Abadi Selalu  | Jakarta         | 08980076453  | | S05         | CV.Cahya Sukses  | Jakarta         | 081970553223 | | S06         | CV.Agung Perkasa | Bojonegoro      | 08996754867  | +-------------+------------------+-----------------+--------------+ 6 rows in set (0.02 sec)

Melihat struktur table pegawai secara lebih detail dengan DESC

mysql> DESC pegawai; +------------+---------------+------+-----+---------+-------+ | Field      | Type          | Null | Key | Default | Extra | +------------+---------------+------+-----+---------+-------+ | id_peg     | varchar(3)    | NO   | PRI |         |       | | nama_peg   | varchar(25)   | YES  |     | NULL    |       | | jk         | enum('L','P') | YES  |     | NULL    |       | | alamat_peg | varchar(30)   | YES  |     | NULL    |       | | tlp_peg    | varchar(20)   | YES  |     | NULL    |       | +------------+---------------+------+-----+---------+-------+ 5 rows in set (0.13 sec)

Menghapus  data  dari tabel supplier yang memiliki alamat_supplier=bojonegoro dengan DELETE

mysql> delete from supplier where alamat_supplier='Bojonegoro'; Query OK, 1 row affected (0.05 sec) mysql> select*from supplier; +-------------+------------------+-----------------+--------------+ | id_supplier | nama_supplier    | alamat_supplier | tlp_supplier | +-------------+------------------+-----------------+--------------+ | S01         | CV.Mandiri       | Malang          | 085678904332 | | S02         | CV.Agung Perkasa | Bekasi          | 085765784531 | | S03         | CV.Makmur Jaya   | Surabaya        | 083845769090 | | S04         | CV.Abadi Selalu  | Jakarta         | 08980076453  | | S05         | CV.Cahya Sukses  | Jakarta         | 081970553223 | +-------------+------------------+-----------------+--------------+ 5 rows in set (0.00 sec)

Mengubah nama tabel “obat” menjadi “stok_obat” dengan ALTER TABLE lalu menampilkan tabelnya

mysql> alter table obat rename to stok_obat; Query OK, 0 rows affected (0.05 sec) mysql> select*from stok_obat; +---------+--------------+------------+------------+ | id_obat | nama_obat    | jenis_obat | harga_jual | +---------+--------------+------------+------------+ | O01     | tolak angin  | sirup      | 6500       | | O02     | bodrex       | tablet     | 20500      | | O03     | minyak telon | oles       | 15000      | | O04     | mixagrip     | tablet     | 3000       | | O05     | extrajoss    | serbuk     | 2500       | +---------+--------------+------------+------------+ 5 rows in set (0.00 sec)

Menampilkan data id_customer, nama_customer, dan alamat customer dari tabel customer yang memiliki alamat_customer=Malang dengan WHERE

mysql> select id_customer,nama_customer,alamat_customer from customer where alamat_customer='Malang'; +-------------+-----------------+-----------------+ | id_customer | nama_customer   | alamat_customer | +-------------+-----------------+-----------------+ | C02         | RS.Villate      | Malang          | | C05         | PT.Bintang Lima | Malang          | +-------------+-----------------+-----------------+ 2 rows in set (0.03 sec)

Menampilkan semua data tabel pegawai yang ber-gender P (perempuan) dengan LIKE

mysql> select*from pegawai where jk like '%P%'; +--------+----------+------+------------+--------------+ | id_peg | nama_peg | jk   | alamat_peg | tlp_peg      | +--------+----------+------+------------+--------------+ | P10    | Ani      | P    | Sidoarjo   | 085789893045 | | P30    | Zahra    | P    | Surabaya   | 08987065433  | | P50    | Nayla    | P    | Malang     | 087899896507 | +--------+----------+------+------------+--------------+ 3 rows in set (0.00 sec)

Menampilkan data id_peg, nama_peg, alamat_peg dari tabel pegawai yang ber-gender “P” dan memiliki alamat “Surabaya” dengan WHERE dan LIKE

mysql> select id_peg,nama_peg,jk,alamat_peg from pegawai where jk='P' && alamat_peg like '%Surabaya'; +--------+----------+------+------------+ | id_peg | nama_peg | jk   | alamat_peg | +--------+----------+------+------------+ | P30    | Zahra    | P    | Surabaya   | +--------+----------+------+------------+ 1 row in set (0.00 sec)

Menampilkan data tabel “stok_obat” secara urut berdasarkan nama_obat dengan perintah ORDER BY

mysql> select*from stok_obat order by nama_obat; +---------+--------------+------------+------------+ | id_obat | nama_obat    | jenis_obat | harga_jual | +---------+--------------+------------+------------+ | O02     | bodrex       | tablet     | 20500      | | O05     | extrajoss    | serbuk     | 2500       | | O03     | minyak telon | oles       | 15000      | | O04     | mixagrip     | tablet     | 3000       | | O01     | tolak angin  | sirup      | 6500       | +---------+--------------+------------+------------+ 5 rows in set (0.00 sec)

Menampilkan data tabel “supplier” secara urut berdasarkan nama_supplier dengan perintah ORDER BY DESC

mysql> select*from supplier order by nama_supplier desc; +-------------+------------------+-----------------+--------------+ | id_supplier | nama_supplier    | alamat_supplier | tlp_supplier | +-------------+------------------+-----------------+--------------+ | S01         | CV.Mandiri       | Malang          | 085678904332 | | S03         | CV.Makmur Jaya   | Surabaya        | 083845769090 | | S05         | CV.Cahya Sukses  | Jakarta         | 081970553223 | | S02         | CV.Agung Perkasa | Bekasi          | 085765784531 | | S04         | CV.Abadi Selalu  | Jakarta         | 08980076453  | +-------------+------------------+-----------------+--------------+ 5 rows in set (0.05 sec)

Menampilkan 3 record data pertama dari tabel “pembelian_obat” dengan field [id_trsBeli, nama_obat, jenis_obat, tgl_beli, nama_supplier, alamat_supplier] secara urut berdasarkan alamat_supplier dengan LIMIT

mysql> select id_trsBeli,nama_obat,jenis_obat,tgl_beli,nama_supplier, alamat_supplier from pembelian_obat order by alamat_supplier limit 0,3; +------------+--------------+------------+------------+-----------------+-----------------+ | id_trsBeli | nama_obat    | jenis_obat | tgl_beli   | nama_supplier   | alamat_supplier | +------------+--------------+------------+------------+-----------------+-----------------+ | B02        | mixagrip     | tablet     | 2012-05-12 | CV.Sentosa      | Bekasi          | | B01        | minyak telon | oles       | 2012-04-03 | CV.Abadi Selalu | Jakarta         | | B04        | tolak angin  | sirup      | 2012-10-20 | CV.Abadi Selalu | Jakarta         | +------------+--------------+------------+------------+-----------------+-----------------+ 3 rows in set (0.00 sec)

Menampilkan jumlah record yang ada di tabel “penjualan_obat” dengan COUNT

mysql> select count(*)from penjualan_obat; +----------+ | count(*) | +----------+ |        5 | +----------+ 1 row in set (0.34 sec)

Menggabungkan kolom alamat_customer dan tlp_customer pada tabel “customer” menjadi sebuah kolom dengan CONCAT

mysql> select nama_customer,CONCAT(alamat_customer,' ',tlp_customer)from customer; +-----------------+------------------------------------------+ | nama_customer   | CONCAT(alamat_customer,' ',tlp_customer)   | +-----------------+------------------------------------------+ | Agus Permana    | Batu 085678789090                        | | RS.Villate      | Malang 083821234589                      | | RS.Siti Hajar   | Jember 081978564231                      | | Siti Jazilah    | Jember 081987896545                      | | PT.Bintang Lima | Malang 085608064233                        | +-----------------+------------------------------------------+ 5 rows in set (0.00 sec)

Menghitung panjang suatu string dengan LENGTH

mysql> select length('Siti Jazilah'); +------------------------+ | length('Siti Jazilah') | +------------------------+ |                     12 | +------------------------+ 1 row in set (0.03 sec)

Menghilangkan spasi di awal string (kiri) dengan LTRIM

mysql> select ltrim(' Agus Permana'); +------------------------+ | ltrim(' Agus Permana') | +------------------------+ | Agus Permana           | +------------------------+ 1 row in set (0.00 sec)

Mengganti suatu string dengan string yang lain dengan REPLACE

mysql> select replace ('Jakarta','a','%'); +-----------------------------+ | replace ('Jakarta','a','%') | +-----------------------------+ | J%k%rt%                     | +-----------------------------+ 1 row in set (0.00 sec)

Mengambil nilai terbesar dari suatu ekspresi (query) dengan MAX

mysql> select max(harga_jual)from stok_obat; +-----------------+ | max(harga_jual) | +-----------------+ | 6500            | +-----------------+ 1 row in set (0.33 sec)

Mengubah string menjadi huruf capital dengan UPPER

mysql> select upper('bodrex'); +-----------------+ | upper('bodrex') | +-----------------+ | BODREX          |                    +-----------------+ 1 row in set (0.00 sec)

Menggabungkan 2 tabel pembelian_obat dan penjualan_obat

mysql> select pembelian_obat.nama_obat,pembelian_obat.nama_supplier, pembelian_obat.tgl_beli,penjualan_obat.nama_customer, penjualan_obat.tgl_jual from pembelian_obat,penjualan_obat where pembelian_obat.id_obat=penjualan_obat.id_obat; +--------------+-----------------+------------+-----------------+------------+ | nama_obat    | nama_supplier   | tgl_beli   | nama_customer   | tgl_jual   | +--------------+-----------------+------------+-----------------+------------+ | mixagrip     | CV.Sentosa      | 2012-05-12 | RS.Siti Hajar   | 2012-09-02 | | mixagrip     | CV.Mandiri      | 2012-08-10 | RS.Siti Hajar   | 2012-09-02 | | mixagrip     | CV.Sentosa      | 2012-05-12 | PT.Bintang Lima | 2012-03-11 | | mixagrip     | CV.Mandiri      | 2012-08-10 | PT.Bintang Lima | 2012-03-11 | | tolak angin  | CV.Abadi Selalu | 2012-10-20 | Agus Permana    | 2013-01-13 | | minyak telon | CV.Abadi Selalu | 2012-04-03 | RS.Villate        | 2013-03-03 | +--------------+-----------------+------------+-----------------+------------+ 6 rows in set (0.01 sec)