SQL Time calculation queries

To display Current date:

SELECT SYSDATE FROM DUAL;

To display Yesterday's date and Tomorrow's date (Adding or subtracting days):

SELECT SYSDATE-1 YESTERDAY,SYSDATE+1 TOMORROW FROM DUAL;

To display first day of month :

SELECT TRUNC(SYSDATE,'MONTH')FirstDayMonth FROM DUAL;

To display first day of year:

SELECT TRUNC(SYSDATE,'YEAR')FirstDayYear FROM DUAL;

To display first day of week:

SELECT TRUNC(SYSDATE,'iw')FirstDayWeek FROM DUAL;

To display today's day name:

SELECT TO_CHAR(SYSDATE,'DAY')TODAY FROM DUAL;

To display current month name:

SELECT TO_CHAR(SYSDATE,'MONTH')MONTH FROM DUAL;

SELECT TO_CHAR(SYSDATE,'MON')MON FROM DUAL;

SQL Time calculation queries

To display Current date:

SELECT SYSDATE FROM DUAL;

To display Yesterday's date and Tomorrow's date (Adding or subtracting days):

SELECT SYSDATE-1 YESTERDAY,SYSDATE+1 TOMORROW FROM DUAL;

To display first day of month :

SELECT TRUNC(SYSDATE,'MONTH')FirstDayMonth FROM DUAL;

To display first day of year:

SELECT TRUNC(SYSDATE,'YEAR')FirstDayYear FROM DUAL;

To display first day of week:

SELECT TRUNC(SYSDATE,'iw')FirstDayWeek FROM DUAL;

To display today's day name:

SELECT TO_CHAR(SYSDATE,'DAY')TODAY FROM DUAL;

To display current month name:

SELECT TO_CHAR(SYSDATE,'MONTH')MONTH FROM DUAL;

SELECT TO_CHAR(SYSDATE,'MON')MON FROM DUAL;

SQL Naming rules

we have to follow below rules.

i) Each name must begins with an Alphabetic character.

ii) Valid character set  is     a-z,A-Z,0-9 , @, #, $ and _

iii) Names are not case sensitive.

iv) Already existed names are not allowed.

v) Predefined keywords are not allowed as names.

vi) Blank spaces with in a name are not allowed.

SQL Naming rules

we have to follow below rules.

i) Each name must begins with an Alphabetic character.

ii) Valid character set  is     a-z,A-Z,0-9 , @, #, $ and _

iii) Names are not case sensitive.

iv) Already existed names are not allowed.

v) Predefined keywords are not allowed as names.

vi) Blank spaces with in a name are not allowed.

Types of SQL Commands

1) DDL (data definition language)  commands:

Used to create or change or delete the data base objects
a) CREATE b) ALTER C) DROP

2) DML  (data manipulation language ) commands:

Used to enter new data/update old data/delete unwanted data.
a) INSERT b)UPDATE c) DELETE

3) DCL ( data control  language) commands:

Used to provide or cancel permissions on the data base objects
a) GRANT b) REVOKE

NOTE:-  These  commands are used by DBA only.

4) TCL  ( Transaction control language ) commands:

Used to save or cancel user transactions.
a) COMMIT b) ROLLBACK c)SAVEPOINT


SELECT:-  Used to display the required data from the specified tables.

Types of SQL Commands

1) DDL (data definition language)  commands:

Used to create or change or delete the data base objects
a) CREATE b) ALTER C) DROP

2) DML  (data manipulation language ) commands:

Used to enter new data/update old data/delete unwanted data.
a) INSERT b)UPDATE c) DELETE

3) DCL ( data control  language) commands:

Used to provide or cancel permissions on the data base objects
a) GRANT b) REVOKE

NOTE:-  These  commands are used by DBA only.

4) TCL  ( Transaction control language ) commands:

Used to save or cancel user transactions.
a) COMMIT b) ROLLBACK c)SAVEPOINT


SELECT:-  Used to display the required data from the specified tables.

Difference between SQL and PLSQL

S No	SQL	PLSQL
1	STRUCTURED QUERY LANGUAGE	PROCEDURAL LANGUAGE/SQL
2	It is a collection of predefined commands programs.	It is a collection of user defined commands programs
3	One query is executed at a time as a block.	collection of queries can be executed
4	each query terminated with semi colon(;)	each block terminated with END;
5	queries are not case sensitive	not case sensitive

Difference between SQL and PLSQL

S No	SQL	PLSQL
1	STRUCTURED QUERY LANGUAGE	PROCEDURAL LANGUAGE/SQL
2	It is a collection of predefined commands programs.	It is a collection of user defined commands programs
3	One query is executed at a time as a block.	collection of queries can be executed
4	each query terminated with semi colon(;)	each block terminated with END;
5	queries are not case sensitive	not case sensitive

Creating simple rpd basic

Creating simple rpd basic

Step 1:Open OBIEE Administration Tool.

Step 2:Goto File > New Repository or Press Ctrl and N

Step 3:Give RPD Name..

Select RPD Location, (Where you want to save the RPD.)
and give RPD password.

Click Next

Step 4: Select Connection type.

here I am selecting OCI10g/11g. (Database is installed in local system.)

Give Data Source Name:

localhost:1521/orclnew

Note: localhost is hostname.
1521 is port number of oracle db installed in local system.
orclnew is db instance name.

now give user name and password of schema for which you want to create RPD.

click Next

Step 5:Import the tables or views.

Step 6:Give the physical joins in Physical Layer.

Step 7:Creating Business Model in BMM layer.

right click on BMM layer > New BMM Model >
give name of BM Model > Click ok

Drag and Drop the tables from physical layer to BMM Model.

Step 8:Check the joins.

if not, give the joins.

Step 8: Rename table name, column names as per your Project Naming conventions.
use Rename wizard to rename.

Step 9: Create Subject area in Presentation layer.Right click on presentation layer > New Subject area
Give the name > click ok.

Step 10:Drag and Drop the tables you want in presentation layer.

Step 11:Click save.
click yes for Global consistency check.

Watch the below video

 

Creating simple rpd basic

Creating simple rpd basic

Step 1:Open OBIEE Administration Tool.

Step 2:Goto File > New Repository or Press Ctrl and N

Step 3:Give RPD Name..

Select RPD Location, (Where you want to save the RPD.)
and give RPD password.

Click Next

Step 4: Select Connection type.

here I am selecting OCI10g/11g. (Database is installed in local system.)

Give Data Source Name:

localhost:1521/orclnew

Note: localhost is hostname.
1521 is port number of oracle db installed in local system.
orclnew is db instance name.

now give user name and password of schema for which you want to create RPD.

click Next

Step 5:Import the tables or views.

Step 6:Give the physical joins in Physical Layer.

Step 7:Creating Business Model in BMM layer.

right click on BMM layer > New BMM Model >
give name of BM Model > Click ok

Drag and Drop the tables from physical layer to BMM Model.

Step 8:Check the joins.

if not, give the joins.

Step 8: Rename table name, column names as per your Project Naming conventions.
use Rename wizard to rename.

Step 9: Create Subject area in Presentation layer.Right click on presentation layer > New Subject area
Give the name > click ok.

Step 10:Drag and Drop the tables you want in presentation layer.

Step 11:Click save.
click yes for Global consistency check.

Watch the below video