Postgresql order By, Where, Between, Like

Tampilkan data produk yang stoknya 10 dan 20

dbkoperasi=# select *from produk where stok in (10,20);

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

7 | TE01 | Teh Kotak | 10 | 20 | 4000 | 4

(3 rows)

Tampilkan data produk yang harganya kurang dari 1jt

tetapi lebih dari 500rb

dbkoperasi=# select *from produk where harga >500000 and harga <1000000;

id | kode | nama | stok | min_stok | harga | jenis_produk_id

----+------+-------------+------+----------+--------+-----------------

3 | K002 | Kulkas Mini | 15 | 3 | 750000 | 1

5 | M002 | Meja Makan | 1 | 3 | 850000 | 2

(2 rows)

Tampilkan data produk yang harus segera ditambah

stoknya

dbkoperasi=# select nama,stok,min_stok from produk where stok < min_stok;

nama | stok | min_stok

------------+------+----------

Meja Makan | 1 | 3

Teh Kotak | 10 | 20

(2 rows)

dbkoperasi=# select nama,alamat,gender from customer where alamat <> 'Jakarta'and gender='P';

nama | alamat | gender

----------------+---------+--------

Rosalie Naurah | Depok | P

Iffa Nisrina | Kebumen | P

Rahmi Maulida | Medan | P

(3 rows)

dbkoperasi=# select nama,tgl_lahir from customer order by tgl_lahir desc;

nama | tgl_lahir

--------------------+------------

Faiz Fikri | 2009-03-13

Rahmi Maulida | 2006-08-10

Alissa Khairunnisa | 2005-11-16

Rosalie Naurah | 2005-03-08

Iffa Nisrina | 2004-08-17

Defghi Muhammad | 2001-06-10

(6 rows)

select *from customer where id in (2,4,6);

id | nama | alamat | telepon | email | tgl_lahir | kartu_diskon_id

----+--------------------+----------+-----------+----------------------+------------+-----------------

2 | Alissa Khairunnisa | Jakarta | 029879887 | alissa.gmail.com | 2005-11-16 | 2

4 | Faiz Fikri | Semarang | 03698524 | faiz@hotmail.com | 2009-03-13 | 4

6 | Rahmi Maulida | Medan | 036995544 | rahmi@nurulfikri.com | 2006-08-10 | 2

(3 rows)

dbkoperasi=# select *from customer where alamat in ('Jakarta','Medan');

id | nama | alamat | telepon | email | tgl_lahir | kartu_diskon_id

----+--------------------+---------+-----------+----------------------+------------+-----------------

2 | Alissa Khairunnisa | Jakarta | 029879887 | alissa.gmail.com | 2005-11-16 | 2

6 | Rahmi Maulida | Medan | 036995544 | rahmi@nurulfikri.com | 2006-08-10 | 2

(2 rows)

dbkoperasi=# select *from produk where stok >= 10 and stok <=100;

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

6 | MI01 | Mie Instan | 100 | 20 | 1250 | 3

7 | TE01 | Teh Kotak | 10 | 20 | 4000 | 4

(5 rows)

dbkoperasi=# select *from produk where stok between 10 and 100;

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

6 | MI01 | Mie Instan | 100 | 20 | 1250 | 3

7 | TE01 | Teh Kotak | 10 | 20 | 4000 | 4

(5 rows)

dbkoperasi=# select id,nama,tgl_lahir from customer where date_part ('year',tgl_lahir) between 2000 and 2013;

id | nama | tgl_lahir

----+--------------------+------------

1 | Rosalie Naurah | 2005-03-08

2 | Alissa Khairunnisa | 2005-11-16

3 | Defghi Muhammad | 2001-06-10

4 | Faiz Fikri | 2009-03-13

5 | Iffa Nisrina | 2004-08-17

6 | Rahmi Maulida | 2006-08-10

(6 rows)

dbkoperasi=# select id,nama,tgl_lahir from customer where date_part ('year',tgl_lahir) between 2000 and 2005;

id | nama | tgl_lahir

----+--------------------+------------

1 | Rosalie Naurah | 2005-03-08

2 | Alissa Khairunnisa | 2005-11-16

3 | Defghi Muhammad | 2001-06-10

5 | Iffa Nisrina | 2004-08-17

(4 rows)

dbkoperasi=# select id,nama,tgl_lahir from customer where date_part ('year',tgl_lahir) =2005;

id | nama | tgl_lahir

----+--------------------+------------

1 | Rosalie Naurah | 2005-03-08

2 | Alissa Khairunnisa | 2005-11-16

(2 rows)

dbkoperasi=# select id,nama,tgl_lahir from customer where date_part ('month',tgl_lahir) =11;

id | nama | tgl_lahir

----+--------------------+------------

2 | Alissa Khairunnisa | 2005-11-16

(1 row)

dbkoperasi=# select id,nama,tgl_lahir from customer where date_part ('month',tgl_lahir) =10;

id | nama | tgl_lahir

----+------+-----------

(0 rows)

dbkoperasi=# select nama,tgl_lahir,age(current_date,tgl_lahir) from customer order by tgl_lahir desc;

nama | tgl_lahir | age

--------------------+------------+-------------------------

Faiz Fikri | 2009-03-13 | 4 years 6 mons 26 days

Rahmi Maulida | 2006-08-10 | 7 years 1 mon 29 days

Alissa Khairunnisa | 2005-11-16 | 7 years 10 mons 22 days

Rosalie Naurah | 2005-03-08 | 8 years 7 mons

Iffa Nisrina | 2004-08-17 | 9 years 1 mon 22 days

Defghi Muhammad | 2001-06-10 | 12 years 3 mons 28 days

(6 rows)

dbkoperasi=# select id,kode,nama from produk

where kode like 'K%' or kode like 'M%';

id | kode | nama

----+------+----------------

2 | K001 | Kulkas 2 Pintu

3 | K002 | Kulkas Mini

4 | M001 | Sofa Tamu

5 | M002 | Meja Makan

6 | MI01 | Mie Instan

(5 rows)

dbkoperasi=# select id,kode,nama from produk

where nama ilike '%kulkas%';

id | kode | nama

----+------+----------------

2 | K001 | Kulkas 2 Pintu

3 | K002 | Kulkas Mini

(2 rows)

dbkoperasi=# select id,nama from customer

where nama ilike '%SA%';

id | nama

----+--------------------

1 | Rosalie Naurah

2 | Alissa Khairunnisa

(2 rows)