A. Rangkuman
Materi
a. Join
rb1=>
select product.product_name,price_product.price from product left join
price_product on product.product_id=price_product.product_id;
product_name | price
-------------------------+--------
monitor acer 14 inch |
450000
monitor acer 14 inch | 460000
monitor acer 14 inch | 475000
monitor acer 20 inch | 655000
monitor acer 20 inch | 555000
monitor acer 20 inch | 575000
monitor samsung 21 inch | 335000
monitor samsung 21 inch | 350000
monitor samsung 21 inch | 360000
modem sierra 101 | 450000
modem sierra 101 | 420000
modem sierra 101 | 120000
modem sierra 102 | 120000
modem sierra 102 | 150000
modem sierra 102 | 170000
modem huawei AC123 | 170000
modem huawei AC123 | 180000
printer canon mp999 | 180000
printer canon mp999 | 190000
printer canon mp278 | 110000
printer canon mp278 | 120000
modem sierra 103 |
(22
rows)
B. fungsi
string
a. left
rb1=>
select product_name ,left(product_name,5) as hasilpotongkiri from product;
product_name | hasilpotongkiri
-------------------------+-----------------
monitor acer 14 inch | monit
monitor acer 20 inch | monit
modem sierra 101 | modem
modem sierra 102 | modem
modem sierra 103 | modem
modem huawei AC123 | modem
printer canon mp278 | print
monitor samsung 21 inch | monit
printer canon mp999 | print
(9 rows)
b. right
rb1=>
select product_name ,right(product_name,4) as hasilpotongkanan from product;
product_name | hasilpotongkanan
-------------------------+------------------
monitor acer 14 inch | inch
monitor acer 20 inch | inch
modem sierra 101 |
101
modem sierra 102 |
102
modem sierra 103 |
103
modem huawei AC123 | C123
printer canon mp278 | p278
monitor samsung 21 inch | inch
printer canon mp999 | p999
(9 rows)
c. lower
rb1=>
select product_name ,lower(product_name) as hasillower from product;
product_name |
hasillower
-------------------------+-------------------------
monitor acer 14 inch | monitor acer 14 inch
monitor acer 20 inch | monitor acer 20 inch
modem sierra 101 | modem sierra 101
modem sierra 102 | modem sierra 102
modem sierra 103 | modem sierra 103
modem huawei AC123 | modem huawei ac123
printer canon mp278 | printer canon mp278
monitor samsung 21 inch | monitor samsung 21
inch
printer canon mp999 | printer canon mp999
(9 rows)
d. upper
rb1=>
select product_name ,upper(product_name) as hasilupper from product;
product_name |
hasilupper
-------------------------+-------------------------
monitor acer 14 inch | MONITOR ACER 14 INCH
monitor acer 20 inch | MONITOR ACER 20 INCH
modem sierra 101 | MODEM SIERRA 101
modem sierra 102 | MODEM SIERRA 102
modem sierra 103 | MODEM SIERRA 103
modem huawei AC123 | MODEM HUAWEI AC123
printer canon mp278 | PRINTER CANON MP278
monitor samsung 21 inch | MONITOR SAMSUNG 21
INCH
printer canon mp999 | PRINTER CANON MP999
(9 rows)
e. substring
rb1=>
select product_name ,substring(product_name,10) as hasilsubstring from product;
product_name | hasilsubstring
-------------------------+----------------
monitor acer 14 inch | cer 14 inch
monitor acer 20 inch | cer 20 inch
modem sierra 101 | rra 101
modem sierra 102 | rra 102
modem sierra 103 | rra 103
modem huawei AC123 | wei AC123
printer canon mp278 | anon mp278
monitor samsung 21 inch | amsung 21 inch
printer canon mp999 | anon mp999
(9 rows)
rb1=>
select product_name ,substring(product_name,2,5) as hasilsubstring from
product;
product_name | hasilsubstring
-------------------------+----------------
monitor acer 14 inch | onito
monitor acer 20 inch | onito
modem sierra 101 | odem
modem sierra 102 | odem
modem sierra 103 | odem
modem huawei AC123 | odem
printer canon mp278 | rinte
monitor samsung 21 inch | onito
printer canon mp999 | rinte
(9 rows)
f. ascii
rb1=>
select product_name ,substring(product_name,0,2) as
hasilsubstring,ascii(substring(product_name,0,2)) as hasilascii from product;
product_name | hasilsubstring | hasilascii
-------------------------+----------------+------------
monitor acer 14 inch | m
| 109
monitor acer 20 inch | m | 109
modem sierra 101 | m | 109
modem sierra 102 | m | 109
modem sierra 103 | m | 109
modem huawei AC123 | m | 109
printer canon mp278 | p | 112
monitor samsung 21 inch | m | 109
printer canon mp999 | p | 112
(9 rows)
g. concat
rb1=>
select product_id,concat(stock)from price_product where product_id='pr1';
product_id | concat
------------+--------
pr1
| 40
pr1
| 60
pr1
| 60
(3 rows)
h. group
concat array
rb1=>
select product_id,array_to_string (array(select price from price_product where
product_id='pr1'order by date),',')as groupconcat from price_product where
product_id='pr1'order by date),',')as groupconcat from price_product where
product_id='pr1' group by product_id;
product_id | groupconcat
------------+----------------------
pr1
| 450000,460000,475000
(1 row)
C. fungsi
date
a. current
date
rb1=>
select current_date;
date
------------
2013-10-09
(1 row)
b. current
Time
rb1=>
select current_time;
timetz
--------------------
15:56:45.025454+07
(1 row)
c. current
times tamp
rb1=>
select current_timestamp;
now
-------------------------------
2013-10-09 15:58:04.497006+07
(1 row)
d. now
rb1=>
select now();
now
-------------------------------
2013-10-09 15:58:14.453479+07
(1 row)
Latihan
.
1. tampilkan semua data
product(product_id,product_name,price)
rb1=> select
price_product.product_id,product.product_name,price_product.price from product
left join price_product on product.product_id=price_product.product_id;
product_id | product_name | price
------------+-------------------------+--------
pr1
| monitor acer 14 inch | 450000
pr1
| monitor acer 14 inch | 460000
pr1
| monitor acer 14 inch | 475000
pr2
| monitor acer 20 inch | 655000
pr2
| monitor acer 20 inch | 555000
pr2
| monitor acer 20 inch | 575000
pr3
| monitor samsung 21 inch | 335000
pr3
| monitor samsung 21 inch | 350000
pr3
| monitor samsung 21 inch | 360000
pr4
| modem sierra 101 | 450000
pr4
| modem sierra 101 | 420000
pr4
| modem sierra 101 | 120000
pr5
| modem sierra 102 | 120000
pr5
| modem sierra 102 | 150000
pr5
| modem sierra 102 | 170000
pr7
| modem huawei AC123 | 170000
pr7
| modem huawei AC123 | 180000
pr8
| printer canon mp999 | 180000
pr8
| printer canon mp999 | 190000
pr9
| printer canon mp278 | 110000
pr9 | printer canon mp278 | 120000
| modem sierra 103 |
(22 rows)
2. tampilkan harga product dengan
merek modem sierra
rb1=> select
price_product.product_id,product.product_name,price_product.price from product
left join price_product on product.product_id=price_product.product_id
where product_name='modem sierra
101';
product_id |
product_name | price
------------+------------------+--------
pr4
| modem sierra 101 | 450000
pr4
| modem sierra 101 | 420000
pr4
| modem sierra 101 | 120000
(3 rows)
rb1=> select
price_product.product_id,product.product_name,price_product.price from product
left join price_product on product.product_id=price_product.product_id
where product_name='modem sierra
102;
product_id |
product_name | price
------------+------------------+--------
pr5
| modem sierra 102 | 120000
pr5
| modem sierra 102 | 150000
pr5
| modem sierra 102 | 170000
(3 rows)
3.tampilkan nama product ,stok,
harga dan tanggal >150000
rb1=> select
product.product_name,price_product.stock,price_product.price,price_product.date
from product left join price_product on product.product_id=price_product.
price_product.product_id where
price>150000;
product_name | stock | price |
date
-------------------------+-------+--------+------------
monitor acer 14 inch |
40 | 450000 | 2012-04-04
monitor acer 14 inch |
60 | 460000 | 2012-05-14
monitor acer 14 inch |
60 | 475000 | 2012-05-20
monitor acer 20 inch |
20 | 655000 | 2012-04-05
monitor acer 20 inch |
80 | 555000 | 2012-05-05
monitor acer 20 inch |
30 | 575000 | 2012-05-24
monitor samsung 21 inch | 110 | 335000 | 2012-04-24
monitor samsung 21 inch | 110 | 350000 | 2012-05-03
monitor samsung 21 inch | 110 | 360000 | 2012-06-04
modem sierra 101 |
10 | 450000 | 2012-04-01
modem sierra 101 |
40 | 420000 | 2012-06-01
modem sierra 102 |
10 | 170000 | 2012-07-14
modem huawei AC123 |
10 | 170000 | 2012-07-14
modem huawei AC123 |
5 | 180000 | 2012-08-24
printer canon mp999 |
15 | 180000 | 2012-07-17
printer canon mp999 |
55 | 190000 | 2012-08-17
(16 rows)
4. tampilkan semua nama barang yang
mempunyai harga <400000
rb1=> select
product.product_name,price_product.price from product left join price_product
on product.product_id=price_product.product_id where price<400000;
product_name | price
-------------------------+--------
monitor samsung 21 inch | 335000
monitor samsung 21 inch | 350000
monitor samsung 21 inch | 360000
modem sierra 101 | 120000
modem sierra 102 | 120000
modem sierra 102 | 150000
modem sierra 102 | 170000
modem huawei AC123 | 170000
modem huawei AC123 | 180000
printer canon mp999 | 180000
printer canon mp999 | 190000
printer canon mp278 | 110000
printer canon mp278 | 120000
(13 rows)
Latihan..
1.
rb1=> select
product_id,date,stock from price_product where product_id='pr1';
product_id |
date | stock
------------+------------+-------
pr1
| 2012-04-04 | 40
pr1
| 2012-05-14 | 60
pr1
| 2012-05-20 | 60
(3 rows)
2.
rb1=> select
product.product_name,price_product.date,price_product.stock from product left
join price_product on product.product_id=price_product.product_id where
product_name='monitor acer 14
inch';
product_name | date
| stock
----------------------+------------+-------
monitor acer 14 inch | 2012-04-04 | 40
monitor acer 14 inch | 2012-05-14 | 60
monitor acer 14 inch | 2012-05-20 | 60
(3 rows)
3.
rb1=> select
product_name,array_to_string (array(select price from price_product where
product_id='pr1'order by date),',')as stok from product where
product_name='monitor acer 14 inch' group by product_id;
product_name | stok
----------------------+----------------------
monitor acer 14 inch | 450000,460000,475000
(1 row)
4.
rb1=> select
product_name,array_to_string(array(select date from price_product where
product_id='pr8' order by date),',')as tanggal,array_to_string (array(select
price from price_product where product_id='pr8'order by date),',')as stok from
product where product_name='printer canon mp999' group by product_id;
product_name | tanggal |
stok
---------------------+-----------------------+---------------
printer canon mp999 | 2012-07-17,2012-08-17 |
180000,190000
(1 row)
