OBIEE TRAINING: June 2012

Browse the source file folder server-> Oracle_Business_Intelligence -> setup.exe. Click on setup.exe file which you will give the screen like below:
Before Installing, make sure that your hardware and memory of the system is enough to install OBIEE 10g. If you want know the hardware requirement of the system refer the below document
http://docs.oracle.com/cd/E10415_01/doc/bi.1013/e10417.pdf
At least of 2GB disk memory is needed to continue the installation.

Browse the disk and select the folder that where you want in install otherwise leave it to the default location if the memory is available in the drive.

In the 3rd screen select the installation type that which you want to install. If you want to install all the components of OBIEE then select ‘Complete’ and click ‘Next button.

By passing the above screen we will get the below screen there we have to give JDK location and Administrator password of the OC4JAdmin and click ‘Next’ Button. Please remember the password of OC4JAdmin, because it will be used when you will open the enterprise manager.

In the next screen just click ‘Next’ Button

From the below screen we can choose the language which you want to see the presentation error message. By default the language is ‘English’

By passing the above step we will get the summary of what are all components going to be installed. Just click ‘Next’ Button

By passing the above step the installation of OBIEE components will start. Wait for some time because it is time taken process.

Wait with the above screen until you will get 100% finished and the ‘Next’ button will be enabled. It will come the below screen

By clicking the ‘Next’ button you will come to the below which is giving the summary of installation. From below window you can learn how to start OC4J by manually and automatically.

By clicking ‘Next’ with the above screen you have come to the end of the installation which will be asked for restart your computer. Just restart your system to set the component. After restarted your system check all the components are installed in your machine in ‘All Programs’.

Report with Union

have a report with UNION of 3 criteria and the result i am getting is one of the columns is a union of 3 columns like Age Group,Gender and Race and here the problem is i need to show them in order Race first, Age Group, Gender now it is showing in sorting order even though i cleared sort on all the columns 65+,75+,85+, Asain, Black,Female,Hispanic,Male instead i want the column to show me Asian, Black,Hispanic, 65+,75+,85+,Female, male also in that table i want to make the table line thicker after race category and Age Group

1) In each of your three separate queries, add another column.

2) Create BINS according to how you want the final report be sorted: Asian = '1', Black = '2', etc...

3) Sort on this column in the combined query.

Since each query will have the proper number assigned to the group, the report will sort as you wish...

Aging report

How to create ageing report by using following columns in below provided format.Pleaselet me know the solution.

Columns:Customer,Debit,Credit,Days past due

Format:
Days(0 to 180) |Days(180-365) | Days(>365)|

Customer|Credit|Debit|Credit|Debit|Credit|Debit|
abc | 3344 |12000 |400 |36000 |100 |12000 |
xyz | 1340 |12300 |400 |3600 |300 |11000 |

case when cast(Days as int) <180 then 'Days(0-180 days)'
when cast(Days as int) >= 180 and Days < 365 then 'Days(180-365 days)'
when cast(Days as int) >365 then 'Days(>365 days)'
end

Physical layer

1) I would like to know in which cases we will use ' select object(s) only ' , ' object(s) and direct joins ' and ' object(s) and all joins ' of physical layer to join tables, since i am new to this field ( 2 month's ) please can any one explain me...

select object(s) only
If u select the tables dim1 dim 2,fact1 from physical layer and u need to know the join conditions between those selected tables then we ll go with above option
object(s) and direct joins
Select tables and click on "object(s) and direct joins" u ll direct joins between the tables
object(s) and all joins
For this we will get all the joins associated in physical layer

2) When we import the tables to the physical layer and after that there are few changes to the tables like adding or deleting columns. Do those changes reflect in the physical layer tables as well or do we have to import the modified table again to the physical layer and delete the previous table

After importing tables in physical layer if you modify those tables in database those changes will not be reflected in physical layer. You don’t have to import tables again if you add only one or more columns. You can simply create physical column in rpd with the same name and data type as of database. In the same way if you delete any column in database table then you can delete that column from RPD.

3) Is Fact table is necessary in Physical Layer. I don't have any fact. i have only 5 Dimensions. How can join the tables in the physical layer.

No join in physical layer . Drag and drop 5 dim tables from physical layer to bmm layer and create a dummy table say fact and join 5 dim tables to dummy table then drag 5 dim tables from bmm layer to presentation layer. Create reports

4) I am trying to import tables from database to the physical layer. when we select objects to add to physical layer which boxes to we need to check below. I know we need to check 'Tables' and 'Keys'. Other than these two do we check any other box

that two box (tables and keys) are sufficient for import tables to repository.

5) When I try to do a foreign key join in Physical layer a message pops up saying ' A matching table key doesnt exist in table. Do you want to create one'. Sould I just say 'yes' to it. Any idea why this message is coming?

f you want create manually a foreign key, you should click 'Yes'. That message appears when OBIEE can't establish an automatic relationship between keys of tables you are joined.

6) After I have created all business model and the presentaion layer,the table structure in the database is changed.
In order to synchronize the physical layer with the database do i need to create a new physical layer again?or is there any other way I can do it.

n rpd,
go to Tools > Utilities > Update Physical Layer
click on Execute...

give the source name and execute.

anyway, keep backup before doing this..

The wizard does not add columns or tables to the repository that exist in the back-end database but not in the repository. Additionally, the Wizard doesn't update column Key assignments. It checks that there is a column in the repository that matches the column in the database, and then, if the values don't match, the wizard updates the type and length of the column in the repository.

I want the wizard to add the columns to the table in the physical layer and remove the column from the table if the column doesn't exist in the database.

7) I 've created a duplicate table in physical layer , When i check view data on duplicate, getting an error. (Table or view doesn't exist). How to get out of it.

1 When you create a duplicate in rpd, it wll create a new table having #1 in the end. For eg: if I create a duplicate of Sales then in rpd it will create as Sales#1 and when u do a update count or view data on duplicated table it wll run on "sales#1" and since sales#1 will not be there in ur db it will throw an errror that table or view does not exist. So, better to create a alias and use it instead of duplicate.

2 When you duplicate a table, you create a new physical table with a new name. If this table is involved in a query, the SQL FROM clause will list this table. If the table does not exist in the database, then an error will occur.
Creating an alias creates a copy of the table in metadata that will be referenced in SQL with a new alias name. The alias name in SQL will be derived from the ID given to the alias in the metadata.

The important point is that you are not creating an object in the metadata that requires you to create a new object in the physical database. If you duplicate a table in metadata, then that new table must map to a separate table in the physical database. If it doesn't exist, you will need to create it or you will get a SQL error

8) I have 5 dimension tables. I need to take 5 reports individually for those tables. Do i need to create dummy table in the physical layer for fact? Please suggest me...

9) i have two tables in physical layer , and two tables are different and dont have any joining condition . All i want is i want to see those two tables data indvidually in Answers .

You can create a dummy table and make joins in BMM layer.
Because you need to have a fact and dimension join condition, otherwise you will get an error.

10) Physical Layer Naming Convention

_F for Fact
_D for Dimensions
All in UPPER case

For Alias use CLOSING DATE (W_DAY_D)

11) physical layer have some data. but i want to decrease the data in the physical layer without delete. how u can do. how many ways are there to do this process?

Create opaque view in physical layer so that restrict the data in view itself

12) How to I update the table in the physical layer in the OBI admin tool when:

1. the data in the underlying database table changes (meaning the records/rows are added or deleted in the database)
2. the fields in the underlying database table change (meaning columns are added or deleted)
3. the size of the field changes (meaning the type or length of the columns change)
4. only the table names/column names in the underlying database change.

In which of the above cases do I have to import again through the connection pool. If I have to import again do I have to create physical joins in the physical layer and complex joins in the BMM layer and mapping to the new table as LTS and any expressions in the column mappings again?

1. the data in the underlying database table changes
(meaning the records/rows are added or deleted in the
database):

You don't need to take any action here unless you are concerned with query caching. In that case use 'event polling table'. Lookup documentation for event polling table.

2. the fields in the underlying database table change
(meaning columns are added or deleted)

You will need to manually add those in physical layer and propogate to business and presentation layer.

3. the size of the field changes (meaning the type or
length of the columns change)

Go to utilities - update physical layer and select the affected presentation layer.

4. only the table names/column names in the
underlying database change.
You will need to manually change those in physical layer. No need to and propogate to business and presentation layer ans its is automatically taken care of.

In which of the above cases do I have to import again
through the connection pool. If I have to import
again do I have to create physical joins in the
physical layer and complex joins in the BMM layer and
mapping to the new table as LTS and any expressions
in the column mappings again?

No need in any case to import manually.

if i create a new column of a table in physical layer of a table.This new cloumn is not show in database.How can i synchronize with database ???

he physical layer only refers to what already exist in the DB.

If you have a (new) column in a table in your DB, then you can create a new physical column in the respective physical table in physical layer so BI server knows about it.

However, it is not possible to create a column in your database by just creating a physical column in physical layer.
2 After creating the column in the database, simply import the column(s) using 'File > Import Database..'. This will bring up the Import screen. Navigated thru Database, Schema, and Table to select the Column(s) you need. After you have selected the column(s) click on 'Import'.

What difference does it make in defining the keys in the physical layer in the following two ways.

I have a fact table with the following columns
PERKEY
PRODKEY
MKTKEY
UNITS
DOLLARS

As the name suggests perkey, prodkey and mrkkey are the keys in the table.

Approcah 1:
Select the table.
Right click and in properties of the table click on keys tab and
select new key and
check all the three columns.

Approach 2:

Select the table
Right click and in properties of the table click on keys tab and
select new key and
check perkey only
again select a new key
check PRODKEY only
again slect a new key
check matkey.
In this way you will find the three keys in the properties

Approach 1: will give u just one key which is actually combination of 3 cols from the table acting as composite here. Like country, state, city ...all these 3 cols will together form a foreign key in fact and will be used together during join conditions

Approach 2: will give u 3 keys each based on 3 individual cols and are used seperately using join conditions.

One more way is to auto create keys at the time of join definition which will give u a key based on number of cols used in join conditions.

Duplicate table and Alias table in the physical layer

> aliases are created to break the circular joins (which are not supported by obiee)
-> and, as per apps standards no operation can be done on base tables which are imported directly to physical layer. Only aliases are joined and will be used in BMM layer
-> useful to re-use the table more than one time , best example: W_DAY_D in apps rpd
2 Have you gone through the link saichand provided?It is well explained in that thread.Duplicate will carry all joins of original table.Alias will not carry any joins.Moreover if you create any column in original table.It will automatically reflect in alias whereas in duplicate it wont.
Why is it necessary to create a view in the physical layer when we can create the table or a view in the database?
Does it affect performance in any case?
The view in the physical layer is not transparent to the database.. it's opaque.

This view (rpd) is isolated with the database..
if we want we can deploy this view in database.

But, all time we may not have necessary permissions to deploy this view or to create this view manually in db. Customer may not accept this all time.
At that time we go for view creating in physical layer of rpd.

There's always performance issue using view. Using direct tables, execution time will be less...

when we are having very complex reports, we go for writing views in physical layer... but remember, by writing views in rpd, it may not support the ad-hoc query, which is one of the objectives of Oracle BI.

I make one View of 10 tables , and import this view IN PHYSICAL LAYER . Is it necessory to make fact table in Business Model and Mapping layer?.

create two individual logical tables in bmm layer with your view as only source. Now set Complex joins to make a logical table as fact.
In this way you avoid creating aliases and self-joins.

Diffence between physical layer and BMM layer

The Administration Tool is a Windows application that allows the Oracle BI Administrator to create and edit repositories.

Physical layer: Represents the physical structure of the data sources to which the Oracle BI Server submits queries.
ie, Connection Pool-contains information about the connection to the database.
You can specify the Joins between the tables and you can change the Data Types of the Columns.

BM & M layer: Represents the logical structure of the information in the repository.
ie, Uou can create Dimension, Hierarchies, Measures, Calculations, Aggregations and Time Series

Presentation layer: Represents the presentation structure of the repository. This layer allows you to present a view different from the Business Model and Mapping layer to the users.

2 The physical layer is the layer of the Logical Business Model where you define the physical data model of your data source. It contains information about the physical data sources.

The most common way to create the schema in the Physical layer is by importing metadata (table, view, …) from databases and other data sources. If you import metadata, many of the properties are configured automatically based on the information gathered during the import process. You can also define other attributes of the physical data source, such as join relationships, that might not exist in the data source metadata.

In the Business Model

In the business model, OBI recommends using complex logical joins instead of foreign key logical joins.

When Complex joins are used in the business layer then they are really acting as placeholders. They allow the OBI Server to decide on which are the best joins define in the Physical Layer to satisfy the request.

In a Dimension, you may have multiple Logical Table sources that all join to one fact table. The OBI Server will then decide the best joins to use to meet the requirements.

I already have oracle physical table in Physical layer imported last month. But the physical table definition changed . It has 3 extra columns. How do I get those 3 columns in physical layer in RPD? Do I have to import the table again?

you can create columns physically or can import.
While importing select only those columns to be imported...
It'll import only new (selected) columns into physical tables..
2 Instead of importing it again to physical layer ...just create those three columns with same name as in "database" and "data type " too....click on view data check whether u are able to get the newly added columns data or not

When we import the tables to the physical layer and after that there are few changes to the tables like adding or deleting columns. Do those changes reflect in the physical layer tables as well or do we have to import the modified table again to the physical layer and delete the previous table

When we import the tables to the physical layer and after that there are few changes to the tables like adding or deleting columns. Do those changes reflect in the physical layer tables as well or do we have to import the modified table again to the physical layer and delete the previous table

Display percentage on stacked bar graph

Can anyone please tell me as to how to display percentages on a stacked bar chart. All the time it displays actual values.
For example ..


	18%

3000 | | | | |

70%

2000 | | | | |

12%

1000 | | | | |

Q1 Q2

I hope I could depict it.

Thanks

Answers

1) Did you try altering the PCXML file?
http://obiee101.blogspot.com/2008/01/obiee-xy-and-data-in-mouse-over-label.html

Regards
John
http://obiee101.blogspot.com

2) The one in {OracleBI}\web\app\res\s_oracle10\popbin. Did you restart the presentation server and javahost?

regards

John
http://obiee101.blogspot.com

How do we Create report with Single table

To report on a single table: just drag/drop the table twice from the physical layer to the busines model layer. Then define a complex join (just drag/drop a line) between tablex and tablex#1. Define a key on the dimension table (the white colored folder).

You can then put one of the tables in a presentation folder and report.

TimestampDiff and TimestampADD( Difference of two dates) in OBIEE

How to get a difference between two dates (in terms ) of days,weeks,months what every it may be

The below formula gives you no.of days between day date and current_DateDays difference:
TIMESTAMPDIFF(SQL_TSI_DAY, Time."Day Date",CURRENT_DATE)

The below formula gives you no.of months day date and current_Date
Months difference:
TIMESTAMPDIFF(SQL_TSI_MONTH, Time."Day Date",CURRENT_DATE)The below formula adds months to day date columnToadd 12 months to a date column:
TIMESTAMPADD(SQL_TSI_MONTH, 12,Time."Day Date")

similarly we can write the formulas using different intervals based on the date format in the column
Here are the intervals :
SQL_TSI_SECOND,
SQL_TSI_MINUTE,
SQL_TSI_HOUR,
SQL_TSI_DAY,
SQL_TSI_WEEK,
SQL_TSI_MONTH,
SQL_TSI_QUARTER,
SQL_TSI_YEAR.

Degenerate Dimension in OBIEE

A degenerate dimension (DD) acts as a dimension key in the fact table, however does not join to a corresponding dimension table because all its interesting attributes have already been placed in other analytic dimensions.

Sometimes people want to refer to degenerate dimensions as textual acts, however they're not facts since the fact table's primary key often consists of the DD combined with one or more additional dimension foreign keys.
Degenerate dimensions commonly occur when the fact table's grain is a single transaction.

How to Implement in OBIEE :

Refer: http://obieetalk.com/oracle-bi-ee-101341-%E2%80%93-modeling-degenerate-dimensions-%E2%80%93-fact-attributes

Reference:http://www.kimballgroup.com/html/designtipsPDF/DesignTips2003/KimballDT46AnotherLook.pdf

Real Time Questions

Real Time Questions

XML Transformation

Filter Transformation

Using Debugger

repository creation

Informatica Admin Track

Oracle Queries

SQL QUERIES

1) Display the details of all employees

SQL>Select * from emp;

2) Display the depart information from department table

SQL>select * from dept;

3) Display the name and job for all the employees

SQL>select ename,job from emp;

4) Display the name and salary for all the employees

SQL>select ename,sal from emp;

5) Display the employee no and totalsalary for all the employees

SQL>select empno,ename,sal,comm, sal+nvl(comm,0) as"total salary" from

emp

6) Display the employee name and annual salary for all employees.

SQL>select ename, 12*(sal+nvl(comm,0)) as "annual Sal" from emp

7) Display the names of all the employees who are working in depart number 10.

SQL>select emame from emp where deptno=10;

8) Display the names of all the employees who are working as clerks and

drawing a salary more than 3000.

SQL>select ename from emp where job='CLERK' and sal>3000;

9) Display the employee number and name who are earning comm.

SQL>select empno,ename from emp where comm is not null;

10) Display the employee number and name who do not earn any comm.

SQL>select empno,ename from emp where comm is null;

11) Display the names of employees who are working as clerks,salesman or

analyst and drawing a salary more than 3000.

SQL>select ename from emp where job='CLERK' OR JOB='SALESMAN'

OR JOB='ANALYST' AND SAL>3000;

12) Display the names of the employees who are working in the company for

the past 5 years;

SQL>select ename from emp where to_char(sysdate,'YYYY')-to_char(hiredate,'YYYY')>=5;

13) Display the list of employees who have joined the company before

30-JUN-90 or after 31-DEC-90.

a)select ename from emp where hiredate < '30-JUN-1990' or hiredate >

'31-DEC-90';

14) Display current Date.

SQL>select sysdate from dual;

15) Display the list of all users in your database(use catalog table).

SQL>select username from all_users;

16) Display the names of all tables from current user;

SQL>select tname from tab;

17) Display the name of the current user.

SQL>show user

18) Display the names of employees working in depart number 10 or 20 or 40

or employees working as

CLERKS,SALESMAN or ANALYST.

SQL>select ename from emp where deptno in(10,20,40) or job

in('CLERKS','SALESMAN','ANALYST');

19) Display the names of employees whose name starts with alaphabet S.

SQL>select ename from emp where ename like 'S%';

20) Display the Employee names for employees whose name ends with alaphabet S.

SQL>select ename from emp where ename like '%S';

21) Display the names of employees whose names have second alphabet A in

their names.

SQL>select ename from emp where ename like '_A%';

22) select the names of the employee whose names is exactly five characters

in length.

SQL>select ename from emp where length(ename)=5;

23) Display the names of the employee who are not working as MANAGERS.

SQL>select ename from emp where job not in('MANAGER');

24) Display the names of the employee who are not working as SALESMAN OR

CLERK OR ANALYST.

SQL>select ename from emp where job not

in('SALESMAN','CLERK','ANALYST');

25) Display all rows from emp table.The system should wait after every

screen full of informaction.

SQL>set pause on

26) Display the total number of employee working in the company.

SQL>select count(*) from emp;

27) Display the total salary beiging paid to all employees.

SQL>select sum(sal) from emp;

28) Display the maximum salary from emp table.

SQL>select max(sal) from emp;

29) Display the minimum salary from emp table.

SQL>select min(sal) from emp;

30) Display the average salary from emp table.

SQL>select avg(sal) from emp;

31) Display the maximum salary being paid to CLERK.

SQL>select max(sal) from emp where job='CLERK';

32) Display the maximum salary being paid to depart number 20.

SQL>select max(sal) from emp where deptno=20;

33) Display the minimum salary being paid to any SALESMAN.

SQL>select min(sal) from emp where job='SALESMAN';

34) Display the average salary drawn by MANAGERS.

SQL>select avg(sal) from emp where job='MANAGER';

35) Display the total salary drawn by ANALYST working in depart number 40.

SQL>select sum(sal) from emp where job='ANALYST' and deptno=40;

36) Display the names of the employee in order of salary i.e the name of

the employee earning lowest salary should salary appear first.

SQL>select ename from emp order by sal;

37) Display the names of the employee in descending order of salary.

a)select ename from emp order by sal desc;

38) Display the names of the employee in order of employee name.

a)select ename from emp order by ename;

39) Display empno,ename,deptno,sal sort the output first base on name and

within name by deptno and with in deptno by sal.

SQL>select empno,ename,deptno,sal from emp order by

40) Display the name of the employee along with their annual salary(sal*12).The name of the employee earning highest annual salary should apper first.

SQL>select ename,sal*12 from emp order by sal desc;

41) Display name,salary,hra,pf,da,total salary for each employee. The

output should be in the order of total salary,hra 15% of salary,da 10% of salary,pf 5%

salary,total salary will be(salary+hra+da)-pf.

SQL>select ename,sal,sal/100*15 as hra,sal/100*5 as pf,sal/100*10 as

da, sal+sal/100*15+sal/100*10-sal/100*5 as total from emp;

42) Display depart numbers and total number of employees working in each

department.

SQL>select deptno,count(deptno)from emp group by deptno;

43) Display the various jobs and total number of employees within each job

group.

SQL>select job,count(job)from emp group by job;

44) Display the depart numbers and total salary for each department.

SQL>select deptno,sum(sal) from emp group by deptno;

45) Display the depart numbers and max salary for each department.

SQL>select deptno,max(sal) from emp group by deptno;

46) Display the various jobs and total salary for each job

SQL>select job,sum(sal) from emp group by job;

47) Display the various jobs and total salary for each job

SQL>select job,min(sal) from emp group by job;

48) Display the depart numbers with more than three employees in each dept.

SQL>select deptno,count(deptno) from emp group by deptno having

count(*)>3;

49) Display the various jobs along with total salary for each of the jobs

where total salary is greater than 40000.

SQL>select job,sum(sal) from emp group by job having sum(sal)>40000;

50) Display the various jobs along with total number of employees in each

job.The output should contain only those jobs with more than three employees.

SQL>select job,count(empno) from emp group by job having count(job)>3

51) Display the name of the empployee who earns highest salary.

SQL>select ename from emp where sal=(select max(sal) from emp);

52) Display the employee number and name for employee working as clerk and

earning highest salary among clerks.

SQL>select empno,ename from emp where where job='CLERK'

and sal=(select max(sal) from emp where job='CLERK');

53) Display the names of salesman who earns a salary more than the highest

salary of any clerk.

SQL>select ename,sal from emp where job='SALESMAN' and sal>(select

max(sal) from emp

where job='CLERK');

54) Display the names of clerks who earn a salary more than the lowest

salary of any salesman.

SQL>select ename from emp where job='CLERK' and sal>(select min(sal)

from emp

where job='SALESMAN');

Display the names of employees who earn a salary more than that of

Jones or that of salary grether than that of scott.

SQL>select ename,sal from emp where sal>

(select sal from emp where ename='JONES')and sal> (select sal from emp

where ename='SCOTT');

55) Display the names of the employees who earn highest salary in their

respective departments.

SQL>select ename,sal,deptno from emp where sal in(select max(sal) from

emp group by deptno);

56) Display the names of the employees who earn highest salaries in their

respective job groups.

SQL>select ename,sal,job from emp where sal in(select max(sal) from emp

group by job)

57) Display the employee names who are working in accounting department.

SQL>select ename from emp where deptno=(select deptno from dept where

dname='ACCOUNTING')

58) Display the employee names who are working in Chicago.

SQL>select ename from emp where deptno=(select deptno from dept where

LOC='CHICAGO')

59) Display the Job groups having total salary greater than the maximum

salary for managers.

SQL>SELECT JOB,SUM(SAL) FROM EMP GROUP BY JOB HAVING SUM(SAL)>(SELECT

MAX(SAL) FROM EMP WHERE JOB='MANAGER');

60) Display the names of employees from department number 10 with salary

grether than that of any employee working in other department.

SQL>select ename from emp where deptno=10 and sal>any(select sal from

emp where deptno not in 10).

61) Display the names of the employees from department number 10 with

salary greater than that of all employee working in other departments.

SQL>select ename from emp where deptno=10 and sal>all(select sal from

emp where deptno not in 10).

62) Display the names of the employees in Uppercase.

SQL>select upper(ename)from emp

63) Display the names of the employees in Lowecase.

SQL>select lower(ename)from emp

64) Display the names of the employees in Propercase.

SQL>select initcap(ename)from emp;

65) Display the length of Your name using appropriate function.

SQL>select length('name') from dual

66) Display the length of all the employee names.

SQL>select length(ename) from emp;

67) select name of the employee concatenate with employee number.

SQL>select ename||empno from emp;

68) User appropriate function and extract 3 characters starting from 2

characters from the following string 'Oracle'. i.e the out put should be 'ac'.

SQL>select substr('oracle',3,2) from dual

69) Find the First occurance of character 'a' from the following string i.e

'Computer Maintenance Corporation'.

SQL>SELECT INSTR('Computer Maintenance Corporation','a',1) FROM DUAL

70) Replace every occurance of alphabhet A with B in the string Allens(use

translate function)

SQL>select translate('Allens','A','B') from dual

71) Display the informaction from emp table.Where job manager is found it

should be displayed as boos(Use replace function).

SQL>select replace(JOB,'MANAGER','BOSS') FROM EMP;

72) Display empno,ename,deptno from emp table.Instead of display department

numbers display the related department name(Use decode function).

SQL>select empno,ename,decode(deptno,10,'ACCOUNTING',20,'RESEARCH',30,'SALES',40,'OPRATIONS') from emp;

73) Display your age in days.

SQL>select to_date(sysdate)-to_date('10-sep-77')from dual

74) Display your age in months.

SQL>select months_between(sysdate,'10-sep-77') from dual

75) Display the current date as 15th Augest Friday Nineteen Ninety Saven.

SQL>select to_char(sysdate,'ddth Month day year') from dual

76) Display the following output for each row from emp table.

scott has joined the company on wednesday 13th August ninten nintey.

SQL>select ENAME||' HAS JOINED THE COMPANY ON '||to_char(HIREDATE,'day

ddth Month year') from EMP;

77) Find the date for nearest saturday after current date.

SQL>SELECT NEXT_DAY(SYSDATE,'SATURDAY')FROM DUAL;

78) Display current time.

SQL>select to_char(sysdate,'hh:MM:ss') from dual.

79) Display the date three months Before the current date.

SQL>select add_months(sysdate,3) from dual;

80) Display the common jobs from department number 10 and 20.

SQL>select job from emp where deptno=10 and job in(select job from emp

where deptno=20);

81) Display the jobs found in department 10 and 20 Eliminate duplicate jobs.

SQL>select distinct(job) from emp where deptno=10 or deptno=20

(or)

SQL>select distinct(job) from emp where deptno in(10,20);

82) Display the jobs which are unique to department 10.

SQL>select distinct(job) from emp where deptno=10

83) Display the details of those who do not have any person working under them.

SQL>select e.ename from emp,emp e where emp.mgr=e.empno group by

e.ename having count(*)=1;

84) Display the details of those employees who are in sales department and

grade is 3.

SQL>select * from emp where deptno=(select deptno from dept where

dname='SALES')and sal between(select losal from salgrade where grade=3)and

(select hisal from salgrade where grade=3);

85) Display those who are not managers and who are managers any one.

i)display the managers names

SQL>select distinct(m.ename) from emp e,emp m where m.empno=e.mgr;

ii)display the who are not managers

SQL>select ename from emp where ename not in(select distinct(m.ename)

from emp e,emp m where m.empno=e.mgr);

86) Display those employee whose name contains not less than 4 characters.

SQL>select ename from emp where length(ename)>4;

87) Display those department whose name start with "S" while the location

name ends with "K".

SQL>select dname from dept where dname like 'S%' and loc like '%K';

88) Display those employees whose manager name is JONES.

SQL>select p.ename from emp e,emp p where e.empno=p.mgr and

e.ename='JONES';

89) Display those employees whose salary is more than 3000 after giving 20%

increment.

SQL>select ename,sal from emp where (sal+sal*.2)>3000;

90) Display all employees while their dept names;

SQL>select ename,dname from emp,dept where emp.deptno=dept.deptno

91) Display ename who are working in sales dept.

SQL>select ename from emp where deptno=(select deptno from dept where

dname='SALES');

92) Display employee name,deptname,salary and comm for those sal in between

2000 to 5000 while location is chicago.

SQL>select ename,dname,sal,comm from emp,dept where sal between 2000

and 5000

and loc='CHICAGO' and emp.deptno=dept.deptno;

93)Display those employees whose salary greter than his manager salary.

SQL>select p.ename from emp e,emp p where e.empno=p.mgr and p.sal>e.sal

94) Display those employees who are working in the same dept where his

manager is work.

SQL>select p.ename from emp e,emp p where e.empno=p.mgr and

p.deptno=e.deptno;

95) Display those employees who are not working under any manager.

SQL>select ename from emp where mgr is null

96) Display grade and employees name for the dept no 10 or 30 but grade is

not 4 while joined the company before 31-dec-82.

SQL>select ename,grade from emp,salgrade where sal between losal and

hisal and deptno in(10,30) and grade<>4 and hiredate<'31-DEC-82';

97) Update the salary of each employee by 10% increment who are not

eligiblw for commission.

SQL>update emp set sal=sal+sal*10/100 where comm is null;

98) SELECT those employee who joined the company before 31-dec-82 while

their dept location is newyork or Chicago.

SQL>SELECT EMPNO,ENAME,HIREDATE,DNAME,LOC FROM EMP,DEPT

WHERE (EMP.DEPTNO=DEPT.DEPTNO)AND

HIREDATE <'31-DEC-82' AND DEPT.LOC IN('CHICAGO','NEW YORK');

99) DISPLAY EMPLOYEE NAME,JOB,DEPARTMENT,LOCATION FOR ALL WHO ARE WORKING

AS MANAGER?

SQL>select ename,JOB,DNAME,LOCATION from emp,DEPT where mgr is not

null;

100) DISPLAY THOSE EMPLOYEES WHOSE MANAGER NAME IS JONES? --

[AND ALSO DISPLAY THEIR MANAGER NAME]?

SQL> SELECT P.ENAME FROM EMP E, EMP P WHERE E.EMPNO=P.MGR AND

E.ENAME='JONES';

101) Display name and salary of ford if his salary is equal to hisal of his

grade

a)select ename,sal,grade from emp,salgrade where sal between losal and

hisal

and ename ='FORD' AND HISAL=SAL;

102) Display employee name,job,depart name ,manager name,his grade and make

out an under department wise?

SQL>SELECT E.ENAME,E.JOB,DNAME,EMP.ENAME,GRADE FROM EMP,EMP

E,SALGRADE,DEPT

WHERE EMP.SAL BETWEEN LOSAL AND HISAL AND EMP.EMPNO=E.MGR

AND EMP.DEPTNO=DEPT.DEPTNO ORDER BY DNAME

103) List out all employees name,job,salary,grade and depart name for every

one in the company except 'CLERK'.Sort on salary display the highest salary?

SQL>SELECT ENAME,JOB,DNAME,SAL,GRADE FROM EMP,SALGRADE,DEPT WHERE

SAL BETWEEN LOSAL AND HISAL AND EMP.DEPTNO=DEPT.DEPTNO AND JOB

NOT IN('CLERK')ORDER BY SAL ASC;

104) Display the employee name,job and his manager.Display also employee who

are without manager?

SQL>select e.ename,e.job,eMP.ename AS Manager from emp,emp e where

emp.empno(+)=e.mgr

105) Find out the top 5 earners of company?

SQL>SELECT DISTINCT SAL FROM EMP E WHERE 5>=(SELECT COUNT(DISTINCT SAL)

FROM

EMP A WHERE A.SAL>=E.SAL)ORDER BY SAL DESC;

106) Display name of those employee who are getting the highest salary?

SQL>select ename from emp where sal=(select max(sal) from emp);

107) Display those employee whose salary is equal to average of maximum and

minimum?

SQL>select ename from emp where sal=(select max(sal)+min(sal)/2 from

emp);

108) Select count of employee in each department where count greater than 3?

SQL>select count(*) from emp group by deptno having count(deptno)>3

109) Display dname where at least 3 are working and display only department

name?

SQL>select distinct d.dname from dept d,emp e where d.deptno=e.deptno

and 3>any

(select count(deptno) from emp group by deptno)

110) Display name of those managers name whose salary is more than average

salary of his company?

SQL>SELECT E.ENAME,EMP.ENAME FROM EMP,EMP E

WHERE EMP.EMPNO=E.MGR AND E.SAL>(SELECT AVG(SAL) FROM EMP);

111)Display those managers name whose salary is more than average salary of

his employee?

SQL>SELECT DISTINCT EMP.ENAME FROM EMP,EMP E WHERE

E.SAL <(SELECT AVG(EMP.SAL) FROM EMP

WHERE EMP.EMPNO=E.MGR GROUP BY EMP.ENAME) AND

EMP.EMPNO=E.MGR;

112) Display employee name,sal,comm and net pay for those employee

whose net pay is greter than or equal to any other employee salary of

the company?

SQL>select ename,sal,comm,sal+nvl(comm,0) as NetPay from emp

where sal+nvl(comm,0) >any (select sal from emp)

113) Display all employees names with total sal of company with each

employee name?

SQL>SELECT ENAME,(SELECT SUM(SAL) FROM EMP) FROM EMP;

114) Find out last 5(least)earners of the company.?

SQL>SELECT DISTINCT SAL FROM EMP E WHERE

5>=(SELECT COUNT(DISTINCT SAL) FROM EMP A WHERE

A.SAL<=E.SAL)

ORDER BY SAL DESC;

115) Find out the number of employees whose salary is greater than their

manager salary?

SQL>SELECT E.ENAME FROM EMP ,EMP E WHERE EMP.EMPNO=E.MGR

AND EMP.SAL

116) Display those department where no employee working?

SQL>select dname from emp,dept where emp.deptno not in(emp.deptno)

117) Display those employee whose salary is ODD value?

SQL>select * from emp where sal<0;

118) Display those employee whose salary contains alleast 3 digits?

SQL>select * from emp where length(sal)>=3;

119) Display those employee who joined in the company in the month of Dec?

SQL>select ename from emp where to_char(hiredate,'MON')='DEC';

120) Display those employees whose name contains "A"?

SQL>select ename from emp where instr(ename,'A')>0;

SQL>select ename from emp where ename like('%A%');

121) Display those employee whose deptno is available in salary?

SQL>select emp.ename from emp, emp e where emp.sal=e.deptno;

122) Display those employee whose first 2 characters from hiredate -last 2

characters of salary?

SQL>select ename,SUBSTR(hiredate,1,2)||ENAME||substr(sal,-2,2) from emp

123) Display those employee whose 10% of salary is equal to the year of

joining?

SQL>select ename from emp where to_char(hiredate,'YY')=sal*0.1;

124) Display those employee who are working in sales or research?

SQL>SELECT ENAME FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE

DNAME IN('SALES','RESEARCH'));

125) Display the grade of jones?

SQL>SELECT ENAME,GRADE FROM EMP,SALGRADE

WHERE SAL BETWEEN LOSAL AND HISAL AND Ename='JONES';

126) Display those employees who joined the company before 15 of the month?

a)select ename from emp where to_char(hiredate,'DD')<15;

127) Display those employee who has joined before 15th of the month.

a)select ename from emp where to_char(hiredate,'DD')<15;

128) Delete those records where no of employees in a particular department

is less than 3.

SQL>delete from emp where deptno=(select deptno from emp

group by deptno having count(deptno)<3);

129) Display the name of the department where no employee working.

SQL> SELECT E.ENAME,E.JOB,M.ENAME,M.JOB FROM EMP E,EMP M

WHERE E.MGR=M.EMPNO

130) Display those employees who are working as manager.

SQL>SELECT M.ENAME MANAGER FROM EMP M ,EMP E

WHERE E.MGR=M.EMPNO GROUP BY M.ENAME

131) Display those employees whose grade is equal to any number of sal but

not equal to first number of sal?

SQL> SELECT ENAME,GRADE FROM EMP,SALGRADE

WHERE GRADE NOT IN(SELECT SUBSTR(SAL,0,1)FROM EMP)

132) Print the details of all the employees who are Sub-ordinate to BLAKE?

SQL>select emp.ename from emp, emp e where emp.mgr=e.empno and

e.ename='BLAKE';

133) Display employee name and his salary whose salary is greater than

highest average of department number?

SQL>SELECT SAL FROM EMP WHERE SAL>(SELECT MAX(AVG(SAL)) FROM EMP

GROUP BY DEPTNO);

134) Display the 10th record of emp table(without using rowid)

SQL>SELECT * FROM EMP WHERE ROWNUM<11

MINUS

SELECT * FROM EMP WHERE ROWNUM<10

135) Display the half of the ename's in upper case and remaining lowercase?

SQL>SELECT

SUBSTR(LOWER(ENAME),1,3)||SUBSTR(UPPER(ENAME),3,LENGTH(ENAME))

FROM EMP;

136) Display the 10th record of emp table without using group by and rowid?

SQL>SELECT * FROM EMP WHERE ROWNUM<11

MINUS

SELECT * FROM EMP WHERE ROWNUM<10

Delete the 10th record of emp table.

SQL>DELETE FROM EMP WHERE EMPNO=(SELECT EMPNO FROM EMP WHERE ROWNUM<11

MINUS

SELECT EMPNO FROM EMP WHERE ROWNUM<10)

137) Create a copy of emp table;

SQL>create table new_table as select * from emp where 1=2;

138) Select ename if ename exists more than once.

SQL>select ename from emp e group by ename having count(*)>1;

139) Display all enames in reverse order?(SMITH:HTIMS).

SQL>SELECT REVERSE(ENAME)FROM EMP;

140) Display those employee whose joining of month and grade is equal.

SQL>SELECT ENAME FROM EMP WHERE SAL BETWEEN

(SELECT LOSAL FROM SALGRADE WHERE

GRADE=TO_CHAR(HIREDATE,'MM')) AND

(SELECT HISAL FROM SALGRADE WHERE

GRADE=TO_CHAR(HIREDATE,'MM'));

141) Display those employee whose joining DATE is available in deptno.

SQL>SELECT ENAME FROM EMP WHERE TO_CHAR(HIREDATE,'DD')=DEPTNO

142) Display those employees name as follows

A ALLEN

B BLAKE

SQL> SELECT SUBSTR(ENAME,1,1),ENAME FROM EMP;

143) List out the employees ename,sal,PF(20% OF SAL) from emp;

SQL>SELECT ENAME,SAL,SAL*.2 AS PF FROM EMP;

144) Create table emp with only one column empno;

SQL>Create table emp as select empno from emp where 1=2;

145) Add this column to emp table ename vrachar2(20).

SQL>alter table emp add(ename varchar2(20));

146) Oops I forgot give the primary key constraint. Add in now.

SQL>alter table emp add primary key(empno);

147) Now increase the length of ename column to 30 characters.

SQL>alter table emp modify(ename varchar2(30));

148) Add salary column to emp table.

SQL>alter table emp add(sal number(10));

149) I want to give a validation saying that salary cannot be greater 10,000

(note give a name to this constraint)

SQL>alter table emp add constraint chk_001 check(sal<=10000)

150) For the time being I have decided that I will not impose this validation.My boss has agreed to pay more than 10,000.

SQL>again alter the table or drop constraint with alter table emp drop constraint chk_001 (or)Disable the constraint by using alter table emp modify constraint chk_001 disable;

151) My boss has changed his mind. Now he doesn't want to pay more than

10,000.so revoke that salary constraint.

SQL>alter table emp modify constraint chk_001 enable;

152) Add column called as mgr to your emp table;

SQL>alter table emp add(mgr number(5));

153) Oh! This column should be related to empno. Give a command to add this

constraint.

SQL>ALTER TABLE EMP ADD CONSTRAINT MGR_DEPT FOREIGN KEY(MGR) REFERENCES

EMP(EMPNO)

154) Add deptno column to your emp table;

SQL>alter table emp add(deptno number(5));

155) This deptno column should be related to deptno column of dept table;

SQL>alter table emp add constraint dept_001 foreign key(deptno)

reference dept(deptno)

[deptno should be primary key]

156) Give the command to add the constraint.

SQL>alter table

157) Create table called as newemp. Using single command create this table

as well as get data into this table(use create table as);

SQL>create table newemp as select * from emp;

SQL>Create table called as newemp. This table should contain only

empno,ename,dname.

SQL>create table newemp as select empno,ename,dname from emp,dept where

1=2;

158) Delete the rows of employees who are working in the company for more

than 2 years.

SQL>delete from emp where (sysdate-hiredate)/365>2;

159) Provide a commission(10% Comm Of Sal) to employees who are not earning

any commission.

SQL>select sal*0.1 from emp where comm is null

160) If any employee has commission his commission should be incremented by

10% of his salary.

SQL>update emp set comm=sal*.1 where comm is not null;

161) Display employee name and department name for each employee.

SQL>select empno,dname from emp,dept where emp.deptno=dept.deptno

162)Display employee number,name and location of the department in which he

is working.

SQL>select empno,ename,loc,dname from emp,dept where

emp.deptno=dept.deptno;

163) Display ename,dname even if there are no employees working in a

particular department(use outer join).

SQL>select ename,dname from emp,dept where emp.deptno=dept.deptno(+)

164) Display employee name and his manager name.

SQL>select p.ename,e.ename from emp e,emp p where e.empno=p.mgr;

165) Display the department name and total number of employees in each

department.

SQL>select dname,count(ename) from emp,dept where

emp.deptno=dept.deptno group by dname;

166)Display the department name along with total salary in each department.

SQL>select dname,sum(sal) from emp,dept where emp.deptno=dept.deptno

group by dname;

167) Display itemname and total sales amount for each item.

SQL>select itemname,sum(amount) from item group by itemname;

168) Write a Query To Delete The Repeted Rows from emp table;

SQL>Delete from emp where rowid not in(select min(rowid)from emp group

by ename)

169) TO DISPLAY 5 TO 7 ROWS FROM A TABLE

SQL>select ename from emp

where rowid in(select rowid from emp where rownum<=7

minus

select rowid from empi where rownum<5)

170) DISPLAY TOP N ROWS FROM TABLE?

SQL>SELECT * FROM

(SELECT * FROM EMP ORDER BY ENAME DESC)

WHERE ROWNUM <10;

171) DISPLAY TOP 3 SALARIES FROM EMP;

SQL>SELECT SAL FROM ( SELECT * FROM EMP ORDER BY SAL DESC )

WHERE ROWNUM <4

172) DISPLAY 9th FROM THE EMP TABLE?

SQL>SELECT ENAME FROM EMP

WHERE ROWID=(SELECT ROWID FROM EMP WHERE ROWNUM<=10

MINUS

SELECT ROWID FROM EMP WHERE ROWNUM <10)

select second max salary from emp;

select max(sal) fromemp where sal<(select max(sal) from emp);

------******------

Popular Posts