ani isnaini k.

Selasa, 08 Oktober 2013

Syntax Basis Data (Insert,Create,Delete,Update)


Tabel jenis produk


ani@siswa-desktop:~$ sudo su postgres
[sudo] password for ani:
postgres@siswa-desktop:/home/ani$ psql -U postgres dbkoperasi
psql (9.1.9)
Type "help" for help.


dbkoperasi=# CREATE TABLE jenis_produk (
id integer primary key,
nama varchar (20)
);

dbkoperasi=# \d jenis_produk
Table "public.jenis_produk"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer | not null
nama | character varying(20) |
Indexes:
"jenis_produk_pkey" PRIMARY KEY, btree (id)


dbkoperasi=# INSERT INTO jenis_produk VALUES (1, 'Elektronik');
INSERT 0 1
dbkoperasi=# INSERT INTO jenis_produk VALUES (2, 'Furniture');
INSERT 0 1
dbkoperasi=# INSERT INTO jenis_produk VALUES (3, 'Makanan');
INSERT 0 1
dbkoperasi=# INSERT INTO jenis_produk VALUES (4, 'Minuman');
INSERT 0 1

dbkoperasi=# SELECT *FROM jenis_produk;
id | nama
----+------------
1 | Elektronik
2 | Furniture
3 | Makanan
4 | Minuman
(4 rows)

2. Table Produk

dbkoperasi=# CREATE TABLE produk(
id serial primary key,
kode varchar(10),
nama varchar (45),
stok integer,
min_stok integer,
harga double precision,
jenis_produk_id integer references jenis_produk(id)
);


Table "public.produk"
Column | Type | Modifiers
-----------------+-----------------------+-----------------------------------------------------
id | integer | not null default nextval('produk_id_seq'::regclass)
kode | character varying(10) |
nama | character varying(45) |
stok | integer |
min_stok | integer |
harga | double precision |
jenis_produk_id | integer |


dbkoperasi=# INSERT INTO produk VALUES (1,'TV01','Televisi 21 inch',20,3,1200000,1);
INSERT 0 1
dbkoperasi=# INSERT INTO produk VALUES (2,'K001','Kulkas 2 Pintu',10,2,3150000,1);
INSERT 0 1
dbkoperasi=# INSERT INTO produk VALUES (3,'K002','Kulkas Mini',15,3,750000,1);INSERT 0 1
dbkoperasi=# INSERT INTO produk VALUES (4,'M001','Sofa Tamu',8,1,3600000,2);
INSERT 0 1
dbkoperasi=# INSERT INTO produk VALUES (5,'M002','Meja Makan',1,3,850000,2);
INSERT 0 1
dbkoperasi=# INSERT INTO produk VALUES (6,'MI01','Mie Instan',100,20,1250,3);
INSERT 0 1
dbkoperasi=# INSERT INTO produk VALUES (7,'TE01','Teh Kotak',10,20,4000,4);
INSERT 0 1

dbkoperasi=# SELECT * from produk;
id | kode | nama | stok | min_stok | harga | jenis_produk_id
----+------+------------------+------+----------+---------+-----------------
1 | TV01 | Televisi 21 inch | 20 | 3 | 1200000 |
2 | K001 | Kulkas 2 Pintu | 10 | 2 | 3150000 | 1
3 | K002 | Kulkas Mini | 15 | 3 | 750000 | 1
4 | M001 | Sofa Tamu | 8 | 1 | 3600000 | 2
5 | M002 | Meja Makan | 1 | 3 | 850000 | 2
6 | MI01 | Mie Instan | 100 | 20 | 1250 | 3
7 | TE01 | Teh Kotak | 10 | 20 | 4000 | 4
(7 rows)


** Untuk Merubah Isi Tabel

dbkoperasi=# UPDATE produk SET kode='S001' where id = 4;
UPDATE 1
dbkoperasi=# UPDATE produk SET kode='M001' where id = 5;
UPDATE 1


Tabel Kartu Diskon
dbkoperasi=# CREATE TABLE kartu_diskon (
dbkoperasi(# id serial primary key,
dbkoperasi(# nama varchar (45),
dbkoperasi(# diskon double precision,
dbkoperasi(# iuran double precision
dbkoperasi(# );

dbkoperasi=# INSERT INTO kartu_diskon VALUES (1,'Bronze',0.025,125000,'BZ')
dbkoperasi-# ;
INSERT 0 1
dbkoperasi=# INSERT INTO kartu_diskon VALUES (2,'Silver',0.1,175000,'SL')
;
INSERT 0 1
dbkoperasi=# INSERT INTO kartu_diskon VALUES (3,'Gold',0.2,250000,'GL')
;
INSERT 0 1
dbkoperasi=# INSERT INTO kartu_diskon VALUES (4,'Platinum',0.3,400000,'PL')
;
INSERT 0 1
dbkoperasi=# select *from kartu_diskon;
id | nama | diskon | iuran | kode
----+----------+--------+--------+------
1 | Bronze | 0.025 | 125000 | BZ
2 | Silver | 0.1 | 175000 | SL
3 | Gold | 0.2 | 250000 | GL
4 | Platinum | 0.3 | 400000 | PL
(4 rows)

Tabel Customer

dbkoperasi=# CREATE TABLE customer(
id serial primary key,
nama varchar(45) not null,
alamat varchar (100) not null,
telepon varchar (45),
email varchar (45),
tgl_lahir date,
kartu_diskon_id integer references kartu_diskon(id)
);


dbkoperasi=# INSERT INTO customer VALUES (1,'Rosalie Naurah','Depok','0219546587','rosalie@yahoo.com','2005-03-08',4);
INSERT 0 1
dbkoperasi=# SELECT * from customer;dbkoperasi=# INSERT INTO customer VALUES (2,'Alissa Khairunnisa','Jakarta','029879887','alissa.gmail.com','2005-11-16',2);
INSERT 0 1
dbkoperasi=# INSERT INTO customer VALUES (3,'Defghi Muhammad','Kebumen','0659894','defghi.gmail.com','2001-06-10',2);
INSERT 0 1
dbkoperasi=# SELECT * from customer;dbkoperasi=# INSERT INTO customer VALUES (4,'Faiz Fikri','Semarang','03698524','faiz@hotmail.com','2009-03-13',4);
INSERT 0 1
dbkoperasi=# INSERT INTO customer VALUES (5,'Iffa Nisrina','Kebumen','014899544','iffa@gmail.com','2004-08-17',1);
INSERT 0 1
dbkoperasi=# INSERT INTO customer VALUES (6,'Rahmi Maulida','Medan','036995544','rahmi@nurulfikri.com','2006-08-10',2);
INSERT 0 1


dbkoperasi=# SELECT * from customer; id | nama | alamat | telepon | email | tgl_lahir | kartu_diskon_id
----+--------------------+----------+------------+----------------------+------------+-----------------
1 | Rosalie Naurah | Depok | 0219546587 | rosalie@yahoo.com | 2005-03-08 | 4
2 | Alissa Khairunnisa | Jakarta | 029879887 | alissa.gmail.com | 2005-11-16 | 2
3 | Defghi Muhammad | Kebumen | 0659894 | defghi.gmail.com | 2001-06-10 | 2
4 | Faiz Fikri | Semarang | 03698524 | faiz@hotmail.com | 2009-03-13 | 4
5 | Iffa Nisrina | Kebumen | 014899544 | iffa@gmail.com | 2004-08-17 | 1
6 | Rahmi Maulida | Medan | 036995544 | rahmi@nurulfikri.com | 2006-08-10 | 2
(6 rows)

** menghapus isi tabel

dbkoperasi=# DELETE FROM customer WHERE id =1;
DELETE 1


** Merubah isi tabel


dbkoperasi=# alter table kartu_diskon add column kode varchar (10);
ALTER TABLE

Tabel Pesanan


dbkoperasi=# CREATE TABLE pesanan(
id serial primary key,
nomor varchar(10) not null,
total double precision,
tanggal date,
customer_id integer references customer(id)
);

dbkoperasi=# \d pesanan

Table "public.pesanan"
Column | Type | Modifiers
-------------+-----------------------+------------------------------------------------------
id | integer | not null default nextval('pesanan_id_seq'::regclass)
nomor | character varying(10) | not null
total | double precision |
tanggal | date |
customer_id | integer |



Tabel Pesanan Produk


dbkoperasi=# CREATE TABLE pesanan_produk (
pesanan_id integer references pesanan(id),
produk_id integer references produk(id),
jumlah integer,
harga double precision
);
CREATE TABLE

dbkoperasi=# \d pesanan_produk
Table "public.pesanan_produk"
Column | Type | Modifiers
------------+------------------+-----------
pesanan_id | integer |
produk_id | integer |
jumlah | integer |
harga | double precision |


Tabel Pembayaran

dbkoperasi=# CREATE TABLE pembayaran (
pesanan_id integer references pesanan(id),
jumlah double precision,
ke integer
dbkoperasi(# );
CREATE TABLE


dbkoperasi=# \d pembayaran
Table "public.pembayaran"
Column | Type | Modifiers
------------+------------------+-----------
pesanan_id | integer |
jumlah | double precision |
ke | integer |



dbkoperasi=# select id,nama,kartu_diskon_id from customer;
id | nama | kartu_diskon_id
----+--------------------+-----------------
1 | Rosalie Naurah | 4
2 | Alissa Khairunnisa | 2
3 | Defghi Muhammad | 2
4 | Faiz Fikri | 4
5 | Iffa Nisrina | 1
6 | Rahmi Maulida | 2
(6 rows)
Diposting oleh di

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)