Saturday, November 5, 2016

Simple Example How To Use Aggregate Function on Oracle SQL

Leave a Comment
Aggregate Function is an internal function of the Oracle Database to perform a calculation of the value. The values might have a data type of character, numeric or date. Aggregate functions typically used in conjunction with Group By syntax of grouping function.

There are several aggregate functions in Oracle DB, among other things:
AVG -> are used to calculate the average value of the collections of the data
MAX -> used to find the maximum value of the collections of the data
MIN -> used to find the minimum value of the collections of the data
COUNT -> used to count the number of rows from the collections of the data
SUM -> are used to calculate the total value of the collections of the data

If aggregate functions comes with some attributes, the attributes must be stated on Group By clause.

Suppose known tdata table as follows:

TGL Value
-----------------
10-JAN-15 70
11-FEB-15 80
18-JAN-15 70
18-JUL-15 60
10-FEB-15 90
15-FEB-15

If asked to show months along with the average value

MONTH AVG (VALUE)
-----------------------
JAN 70
FEB 85
JUL 60

it can be made the following statement:

SELECT TO_CHAR (date, 'MON') months, AVG (value)
FROM tdata
GROUP BY TO_CHAR (date, 'MON');
Read More

Sunday, October 30, 2016

Implementation Of DML Basic Function (Built In Function) On Oracle Database

Leave a Comment
Function is a special syntax in DML (Data Manipulation Language) queries to perform data processing in accordance with user needs. There are some ready-made function in Oracle Database. Here are some purposes of function in SQL query:
  • data manipulation
  • menerima argumen dan mengembalikan satu nilai
  • accept arguments and return a value
  • able to modified column data type
  • can perform nested functions
There are three categories of basic functions, which are character function, numeric functions and date function.

For example, there is a table named PRODUK as follows:

PRODID   PRODNAME           UNIT PRICE QTY
---------------------------------------------------------------------------------
P-001 Sabun mandi batang buah 7500 50
P-002 Sabun mandi cair botol 100ml 15000 21
P-003 Sabun cuci sachet 5 sachet 7500 70
P-004 Sabun cuci cair botol 500ml 12000 11
P-005 Sabun mandi kotak kotak 22000 4
P-006 Pelembut pakaian botol 1000ml 15000 7
P-007 Pewangi pakaian botol 1000ml 12000 5
P-008 Minyak goreng botol 1000ml 25000 26

Here are examples of character function implementation:
1. LOWER
Show all product names with a lowercase letter!

  • SELECT LOWER(nmproduk) FROM produk;

2. UPPER
Show all product names and units with capital letters!

  • SELECT UPPER(nmproduk) FROM produk;

3. INITCAP
Show all product names with a capital letter on the first word!

  • SELECT INITCAP(nmproduk) as "Awal" FROM produk;

4. CONCAT
Show product information with codes and product names are combined, for example:
P-001 : Sabun mandi batang

  • SELECT CONCAT(CONCAT(kdproduk,' : '),nmproduk) produk FROM produk;
  • SELECT kdproduk||' : '||nmproduk produk FROM produk;

5. SUBSTR
Show productid and first five words of the name of the product!

  • SELECT kdproduk, SUBSTR(nmproduk,1,5) prod FROM produk;

6. LENGTH
Show the product name and the number of words of each product name!

  • SELECT nmproduk, LENGTH(nmproduk) jml FROM produk;

7. INSTR
Show the character position number of the letter c found on each product name!

  • SELECT nmproduk, INSTR(nmproduk,'c') FROM produk;

8. LPAD
Show the product name and price of the products, set the total character is 7, for example: xxx7500!

  • SELECT nmproduk, LPAD(harga,7,'x') FROM produk;

9. RPAD
Show the product name and price of the products, set the total character is 7, for example: 7500xxx!

  • SELECT nmproduk, RPAD(harga,7,'x') FROM produk;

10. REPLACE
Show the productid and product name, replace character c with character g!

  • SELECT kdproduk, REPLACE(nmproduk,'c','g') FROM produk;
Read More

Tuesday, October 25, 2016

Basic User Role Management Example on Oracle Database

Leave a Comment
On previous post explained that before user able to manage objects, user must possessed some privileges. Minimum privileges for a user are connect and resource.  Every user in the Oracle Database will have one scheme. A scheme will have objects such as tables, views and procedures.  User can access other user-owned scheme if granted permissions or privileges. Some privileges can be grouped into a role.
Here will be given an example how to create role on Oracle Database. For example there are 3 user schemes (User1, User2, User3). User1 scheme has 1 table called T1 with id (Primary Key), nama and nilai attributes.

User Schemes: User1 User2 User3
Object: Tabel T1 (#id, nama, nilai)
CREATE TABLE t1(id NUMBER PRIMARY KEY, nama VARCHAR2(24), nilai NUMBER);

Here are the scenarios for creating User Role:
1. Login with SYSTEM user then create user1, user2, user3
CREATE USER user4 IDENTIFIED BY user4
DEFAULT TABLESPACE USERS
QUOTA UNLIMITED ON USERS;

2. Create ROLE namely MASUK, MENULIS, MEMBACA and BACA_TULIS
CREATE ROLE masuk;
CREATE ROLE menulis;
CREATE ROLE membaca;
CREATE ROLE baca_tulis;

3. Assign the required privileges to the ROLE
GRANT connect TO masuk;
GRANT insert, update, delete ON user1.t1 TO menulis;
GRANT select ON user1.t1 TO membaca;
GRANT masuk, menulis, membaca TO baca_tulis;

4. Assign ROLE baca_tulis to user1
GRANT baca_tulis TO user1;

5. Here is an example for granting System Privileges to a Role
CREATE ROLE intip;
GRANT select any table TO intip;
GRANT masuk, intip TO user2;
Login as user2, show data of T1 table owned by user1.

6. Here is an example for granting Object Privileges to a Role
CREATE ROLE atur_t1;
GRANT select, insert, update, delete ON user1.t1 TO atur_t1;
GRANT atur_t1 TO user3;
Login as user3, execute select/insert/update/delete on tabel T1 owned by user1

7. Create new role namely buat_table. Assign RESOURCE system privilege to that role (WITH ADMIN OPTION). Assign that role to user3.
Login as user3 then create T2 table (#kode, ket) on user2 scheme.
Fill some data to that table

8. Create user4 user by SYSTEM user.

9. Login as user3 then assign RESOURCE privilege to user4

Read More

Sunday, October 23, 2016

Basic User Privilege Management Example On Oracle Database

Leave a Comment
In case we will create user schemes namely MI123, Tamu1 and Tamu2. User MI123 have two tables namely T1 and T2. Here are some examples for managing user objects for those user schemes.
  1. Create new user scheme (MI123)
CREATE USER mi123 IDENTIFIED BY mi123;  
  1. Grant user privileges to user for login and create user!
GRANT CONNECT, RESOURCE TO mi123; GRANT CONNECT TO mi123; GRANT RESOURCE TO mi123;  
  1. Create 2 database objects (table T1 & table T2)
T1                                                   T2

ID        Nama                   KODE     NILAI     ID
-------------------                 ----------------------------------
1          Satu                      N1          70           1
2          Dua                      N2          80           1
3          Tiga                      N3          70           2
-------------------                    N4          90           3
                                      -------------------------------

CREATE TABLE T1(
ID NUMBER PRIMARY KEY,
NAMA VARCHAR2(24)
);

INSERT INTO T1(id, nama) VALUES(1, ‘Satu’);
INSERT INTO T1(id, nama) VALUES(2, ‘Dua’);
INSERT INTO T1(id, nama) VALUES(3, ‘Tiga’);
COMMIT;

CREATE TABLE T2(
KODE VARCHAR2(4),
NILAI NUMBER,
ID NUMBER,
CONSTRAINT fk1 FOREIGN KEY(ID) REFERENCES T1(ID)
);

INSERT INTO T2 VALUES(‘N1’, 70, 1);
INSERT INTO T2 VALUES(‘N2’, 80, 1);
INSERT INTO T2 VALUES(‘N3’, 70, 2);
INSERT INTO T2 VALUES(‘N4’, 90, 3);
COMMIT;
  1. Create new user namely Tamu1 and Tamu2!
CREATE USER Tamu1 IDENTIFIED BY tamu1; CREATE USER Tamu2 IDENTIFIED BY tamu2;  
  1. Create privilege to Tamu1 user for viewing T1 table!
GRANT SELECT ON mi123.T1 TO Tamu1;  
  1. Create privilege for inputing data (INSERT) on Tabel T1 to Tamu2 user!
GRANT INSERT ON mi123.t1 TO Tamu2;  
  1. Show data on T1 Table while login with Tamu1 user! Is it works?
SELECT * FROM mi123.T1; yes it works!  
  1. Show data on T1 Table while login with Tamu2 user! Is it works?
SELECT * FROM mi123.T1; No!   GRANT SELECT ON mi123.T2 TO Tamu1 WITH GRANT OPTION;  
  1. Login as Tamu1 user, type this sentence:
GRANT SELECT ON mi123.T2 To Tamu2;  
  1. Login as Tamu2 user, show the data of T2 table!
SQL> conn tamu2/tamu2
Connected.

SQL> select * from mi123.t2;

KODE   NILAI    ID
----       ---------- ------
N1           70          1
N2           80          1
N3           70          2
N4           90          3
Read More