All Abinitio Interview Questions

Download as docx, pdf, or txt
Download as docx, pdf, or txt
You are on page 1of 29

e

1) What is the difference between reformat and redefine format?

REFORMAT: Changes the format of records by dropping fields, or


by using DML expressions to add fields, combine fields, or
transform the data in the records.
It contains parameters like
Select_expr,
Count,
Transform,
Error group,
Log group,
Reject-threshold.

REDEFINED FORMAT: Copies records from its in port to its out port
without changing the values in the records. It doesn’t contain any
parameters.

By using RFT we can change the data-record’s format and also we can
filter transform modify the data in the records according to our
requirement where in RDFT we can change data-record’s format only

2) What is the difference between reformat and multi reformat?

a) MULTIREFORMAT: is used to reformat data on multiple flows. changes


the format of records flowing from 1 to 20 pairs of in and out ports by
dropping fields or by using DML expressions to add fields, combine
fields, or transform data in the records.
Multireformat is used to avoid deadlock.

3) What are the utilities of output table?


a) Utilities of output table are utility and API.

4) Explain about api& utility modes in output table?

API:
1) It loads the data one by one record by preparing insert statement for
each record
2) Api decreases the performance of it needs to insert one record at a time
3) Api will not disable the constraints where it is inserting the data
4) Api is easy to debug because it provides detailed error information for
the failure record

UTILITY:

1)It uses data base utilites for oracle,sql ldr and creates bulk insert
statemts at a time and executes them in one shot

2) utility Is faster than api it insert the data in bulk mode

3)it disables the constraints and loads the data one insertion done it will
enables the constriats and bad data will not indexed

4)utility wil not give the detailed information for error handling

5) How to convert 8 way multifile to 9 way multifile?


a) Input multifileGatherPartition componentoutput multifile.

6) What is layout and what are the two types of layouts?


a) It is the location of the serial file and multifile.

7) What is check point and phase?


a) A) checkpoint : A checkpoint is a point at which the Co>Operating
System saves all the information it would need to restore a job to
its state at that point. In case of failure, you can recover completed
phases of a job up to the last completed checkpoint.
b)
c) Phase:is a stage of a graph that runs to completion before the
start of the next phase.

8) How to copy multifile form different server to my server?


a)

9) How to get the 25 to 50 records from my input file?


Input file REFORMAT(nextinsequence())FilterByExpersionOutput
file
10) How can u do unit testing?
11) Have you interacted with EME?
a) yes
12) Then what r the commends u used to interace with EME?
a) Air project import
b) Air project export
c) Air sandbox run …..
13) How to test the dbc file by using m command?
a) m_db test dbc file name
14) How to generate the dml by using m command?
a)m_db gendml dbcfilename filename
15) How to replace the string in unix?
a) Sed ‘s/seek string/replace string/g’ file name
16) How to find my test.dat file ?
a) Find . –type -f test.dat
17) Have u written any scripts?
a)
18) How can u create the tag?
a) Air tag create <tag-name> <graph1> <graph2>….
19) Write the dml for 16.123(decimal) ?
a)
20) Can u please explain how u get requirements and how to start
project?
21) Do u know plan?
22) How many transfer functions can we write in reformat?
a) 20
23) What is code migration?
a) Transferring code from one environment to another environment is
called code migration.
24) What is ur scheduling tool?
a)Auto sys
25) How to run the script in unix?
a) Ksh script name or . / script name
26) What are the performance techniques you have used in your
project?
27) What is the recovery file?
a) While running the graph recovery file will create in run directory if
the graph successfully completed then recovery file will delete
automatically, if the graph fails it will remains in the run directory.
28) What are the memory based components?
a) Sort, sortwithin groups,Rollup,Scan,Join
29) How the sort component breaks the pipeline parallelism?

1. Tell me about ur background


2. You know the conduct it
3. You know the plans
4. If table have index, triggers & procedures and delete the table what
happen
5. How to convert rows into columns and columns into rows explain an
example
6. You know the vectors
7. In lookup what types of joins gives and explain it.
8. I have input file it contains bad data & how to bad data insert into
logfile
9. One file have number of columns like first name and another table
have number of columns like last name I want to display the first
name and last name horizontally like first name last name records.
10. In output table which interfaces we have used and explain
11. I have file, these file structure same as to the one table my
requirement how to load file data into table in oracle.

1. Explain your recent developed graph?


2. How do you checkout a graph in Unix?
3. How to check the versions of a particular object
4. How to see the lock objects for a particular user
5. Word count for a multifile
6. My graph will be input  RFMT  Output file, I got 100 error records I
want to get 50th error record how can I get it?
7. I want to run a graph twice in same session how can you do that
8. How to kill the running graph
9. How to delete recovery files
10. How do you use M_cleanup
11. What is m_kill
12. What is driving port..
13. What is ramp limit?
14. I want to get 20 to 30 records in a file
15. How do I check whether graph is failed
16. What is $#
17. What is NVL Function in Oracle Give me an example
18. Like NVL Function in Oracle, Is there any other function which
we can use in ABINITIO ?
19. What is decode?
20. How to write the main in unix?

Tell me about u and your project where worked?

How to know the depth of the multifile system

I have 100 files with same dml how can you read these all?

Air tag creating command

What is the use of generic graph?are u created any generic graph?

How to run the generic graph from the produc tion?

How to run the graph from the production?

SIT and UAT testing’s who will do? Your team or some other team ? ones
completed what the status, if failed what the status?

You run the grah your getting some error? Your getting some bad data in
source file getting error how you can fix it?

What is your scheduler?

Did you write any scripts in jill

Can you explain each and every step how you going to schedule the job in
autosys ?

In the year end how you people are going to complete your deliverables

What is the use of departition components?

Which are the objects are present in the tag?


Did you attend any training internally?

I have a file and table I have data in the file when the new record comes it
needs to check its therein the table?

Explain about NVL function in oracle how its works?

What Is the shell script? what are the uses of shell scripting in abinitio?

How you can able to see only selected record from by using the view data?

Ab initio questions
1.What is a queue?
2.What is cursor in queue ?How it is implemented?
3.What is the use of Normalize component?
4.What is conditional dml?How to implement it ?
5.What is dynamic dml ?
6.What are block compressed files?Explain about ICFF.
7.What are the best practices you follow while developing a efficient graph?
8.What are different types of parallelism in ab>initio?
9.What is the difference between checkpoints and phases?
10.Do you know Meta programming?
11.What is dynamic Lookup ?
12.What is the difference between m_rollback and m_kill?
13.How to do checkin and checkout in unix command prompt?
14.How to create multifile ?
15.What are the objects created during the execution of the graph ?
16.After a graph has failed , how it is restarted from beginning or last
checkpoint ?
17.How huge amount of data (in 100 millions) is loaded using table
components in Ab initio efficiently ?
18.What all m commands you have worked on ?
19.What is PDL?How PDL enhances the ability of Ab initio?
20.How to determine the MAX_CORE value of a component?
21.How to determine the memory allocation for a graph?
22.What are micrographs?
23.What are the air commands you have worked on ?
24.How will you use a single xfr file in multiple graphs?
25.How multiple instances of a single graph runs parallely?
Non Ab initio questions
1.How to identify the process id of a process?
2.What are fact tables and dimension tables?Give example.
3.How to find second highest salary in a table without using rownum or rank
in sql?
4. How do you rate yourself in unix?
5.How to determine the CPU utilisation for a running process?

1. Tell me about ur self


2. U know the plans explain it
3. Which scheduling tolls you used explain it
4. U know the lookup
5. Explain Lookup vs join
6. Explain Output index vs output indexes
7. Tell me the different parallelism techniques explain it one example
8. Explain Serial vs multifile
9. How to delete 30 days old files
10. Explain Rownum vs rowid
11. Explain Limit or ramp
12. How to test dbc file
13. How to generates dml for file
14. How to show the particular object versions
15. U know the air & m-commands
16. Lookup which type of join gives how does it work
17. Tell me about your project
18. Explain about outbond process.
19. I have tow header files in file after that body records are there
how to separate body records from that file.
20. Tell me about Reformat.
21. How will you do the validations.
22. Difference between broadcast and replicate.
23. Which and all components you know.
24. What is your end loading table.
25. About dedupsort.
26. Tell me about Vectors
27. What is the use of join how many joins parameters of join.
28. What is the difference between join and lookup
29. Which case will prefer lookup.
30. Difference between where clause and have clause.
31. I am done with my interview. There is any questions from your
side.

1)tell me about ur self?

2)tell me about ur project?

3)what are ur roles and responsibilities in your project?

4)how to test a dbc file?

5)how to checkin object with out using gde?

6)how to delete 10th record by using unix command?

7)i/p is

I want to get output like 1 2 3

8)can we give multiple inputs to reformat?

9)i am having one script ,under that sub script is also there..how to call
parameters to inner script from outer script?

10)what is the difference between update table and output table?

11)how to merge the two files using unix command?

1)tell me about ur self ,project ,experience and roles and responsibilities?

2)what is the difference between FBE and RFMT?

3)explain about ur resently worked graph?


4)whats ur requirement in that?

5)what is the difference between Partition by key and partition by round


robin?

6)do u know wrapper script?

7)what is the order of execution of graph parameters?

8)how to delete duplicates from table by using sql query?

9)i am having emps and different locations …I want to get the files based on
location?how?

10)how to load the xl files ?

11)difference between lookup and dynamic lookup?

12)how to get one field from look up table?tell me the function?

First round:

1)tell me in brief about your self?

2)tell me about ur project and roles and responsibilities?

3)what r the components u r aware of?

Ans :components are

Datasets components:input file,out file,lookup file,intermediate


file,read multiples files,write multiples files

Database components:input table,output table,truncate table,update


table,runsql,joinwithdb

departition components : gather,concatenate,merge,interleave

partition components : partition by key, partition by round robin, partition by


expression, parttion by percentage,partition by range

transform components: filter by expression, reformat, dedup sort, rollup,


scan, join,
4)what r the componets of data base?

Ans: data base components are input table,output table,truncate


table,update table,runsql,joinwithdb

5)what is the difference between api& utility?

Ans: API mode, the insertion of data into database follows all the
constraints of database.
Utility mode, that disable the constraints & insert the data into
database.

6)i/p is

My question is how to generate the rank group wise using scan & without
using scan?

Ans: To generate the rank group wise by using scan and reformat

7)in0 have 10 records

In1 have 12 records


In that there may be duplicates and may be or may not matched records.
And my output should be 14 records and unused0 have 1 record and
unused1 have1 record..

So what type of join it is?

Ans: join type is inner join

8)i/p is

Emp month sal

A jan 100

A feb 500

B jan 700

C feb 500

I want to find out the second max salary emp wise?

Ans:to find the second max salary employee wise we can use scan and
generate the rank and select the rank=2

9)i/p

I have 10 records in my input file i.e 1,2,3,…10

In lookup 1-5 matched records.

Question is I want 6-10 records in the output ?


Second round:

1)do you know continuous graphs? Continuous components?

Ans: continuous graph is not a regular graph

2)what are the internet folder components u r aware of?

Ans:In internet folder components we have sftp from,sftp to,send message

3)how to send a mail to a list of receptions?

Ans:

4) how to delete the duplicates by using join?

Ans: To delete the duplicates by using join is dedup sort

5)what is parallelism?

Ans:

6)explain the three types of parellellism?

Ans: There are following three types of parallelism in AbInitio:


- data parallelism
- component parallelism
- pipeline parallelism

Data Paralleliism
==================
It is used when underlying data ( notice, I am not saying file )
is partitioned and each of these partitions is processed independently

Component Parallelism
======================

It is used when more than one components are working in


parallel in the same graph.

Pipeline Parallelism
====================
It is used when a receiving component starts processing received data
without having to wait for the last record in the data stream
7)i have one scenario:

I have 100 records in i/p file

if I run the graph first time 25 records should have to go to the output file

if I run the graph again 26-50 records should have to go

same for the 4 th run my output file have to be 100 records how?

8)what are the lookup functions u r aware of?

Ans:lookup functions we have lookup first,lookup last,lookup count,lookup


match,lookup nth

9)how to run the graph with out using gde?

Ans:

10)how to import the graph with out using gde?

Ans:

Third round:

1)do u know multifiles? Then what is multifile?

Ans: Multiple data f.iles combines as one file that’s file is called multifile
system.

Here depth is 2 or more.

It contain one control file and 2 or more data files

2)i have 87 records I want to devide those in 10 in each output file how can
u do ? if done how many records ur last output file contains?

Ans :using the write multiple files we can done

By using the partition by round robin we can have the block size in this
block size we have 10

Last output file we have 7records


3)what is layout?

Ans:

4)how to convert 4 way to 2way

Ans:

5)can u please explain about departition components?

Ans:departition components we have gather,concatenate,merge,interleave

6)what is the difference between sort and sort with in groups?

Ans: Sort: sort sorts and merge records you can use sort to order records
before you send them to

Order records before you send them to a component that requires grouped
or sorted records

Sort with in groups: sort within groups refines the sorting of records already
sorted according to one key specifier

7) I am having 4 fields in my flat file

I want to get 3 field how?

8)what is the command to find 30 days old files?

9)how to modify the time stamp of a file?

10)what is the difference between filter by expression and reformat?

Ans: Filter by expression :it filter the records based on the expression
based on the expression

Reformat: reformat is used to add fields, modify fields, drop fields,


combine field to create variables to apply the business rules

Difference is filter by expression deselect port is there and reformat


deselect port is not there
11)i know we cant capture the records by using reformat but I want to
capture by using reformat how?

12)what is the difference between output index and output indexs

Ans: if we want to pass one record to only one port then output index can
used

if we want to pass one record to multiple ports then output indexs can
used

13)what is commit point?

14)how to delete the duplicates by using sql?

15)what is the difference between fixed length dml and delemateddml?

Ans:

First round:

1)tell me breaf about ur self?

2)tell me about ur project and roles and responsibilities?

3)what r the components u r aware of?

4)what r the componets of bata base?

5)what is the difference between api& utility?

6)i/p is

2
2

My question is how to generate the rank group wise by using scan with out
using scan?

7)in0 have 10 records

In1 have 12 records

Inthat there may be duplicates and may be or maynot matched records. And
my out put should be 14 records and unused0 have 1 record and unused1
have1 record..

So what type of join it is?

8)i/p is

Emp month sal

A jan 100

A feb 500

B jan 700

C feb 500

I want to find out the second max salemp wise?

9)i/p

I have 10 records in my input file i.e 1,2,3,…10


In lookup 1-5 matched records.

Question is I want 6-10 records in the output ?

Second round:

1)do you know continuous graphs? Contininuouscomponets?

2)what are the internet folder components u r aware of?

3)how to send a mail to a list of receptions?

4)how to delete the duplicates by using join?

5)what is parallelism?

6)explain the three types of parellellism?

7)i have one scenario:

I have 100 records in i/p file

if I run the graph first time 25 records should have to go to the output file

if I run the graph again 26-50 records should have to go

same for the 4 th run my output file have to be 100 records how?

8)what are the lookup functions u r aware of?

9)how to run the graph with out using gde?

10)how to import the graph with out using gde?

Third round:

1)do u know multifiles? Then what is multifile?

2)i have 87 records I want to devide those in 10 in each output file how can
u do ? if done how many records ur last output file contains?
3)what is layout?

4)how to convert 4 way to 2way

5)can u please explain about departition components?

6)what is the difference between sort and sort with in groups?

7) I am having 4 fields in my flat file

I want to get 3 field how?

8)what is the command to find 30 days old files?

9)how to modify the time stamp of a file?

10)what is the difference between filter by expression and reformat?

11)i know we cant capture the records by using reformat but I want to
capture by using reformat how?

12)what is the difference between output index and output indexes?

13)what is commit point?

14)how to delete the duplicates by using sql?

15)what is the difference between fixed length dml and delemateddml?

Tell me about your project

I have 100 records in a file in output is also serial file but I want output as
for every 10 records I need to print the 10 records count. How we can
achieve in abinitio.

Difference between output index and output indexes?

I have 3 countries of data for to separate partion by expression will give


better performance or output indexes better performance?

What is $1,$?,$2,?

What is where class explain

Difference between partion by key and round robin?


Explain about your project and rolls and responsibility?

Explain the different parallisum techniques?

What is multifile? Why we need to choose?

Difference between scan and rollup?

Difference between gather and merge?

How separate the data for conditional dml

How many people are working in your team?

Why r u suppose to change the company?

Difference between output index and output index

What is join explain types

What is driving port?

I have input file I don’t have indicter first is header and last tailer. How
your will separate the body records?

How to find the 30 days old files?

What is plan?

Usually how we can change the file permission?

What is nvl function?

1. How do you select maximum marks from each subject in student


table?
Select subject,max(marks) from stud group by subject;
2. How do you select top 2 maximum marks from each subject?
Select subject,marks,rnk from(select dense_rank()over(partition by
subject order by marks desc)as rnk,subject,marks from stud)where
rnk<=2;
3. How to find out the Second Highest Salary in SQL?
Select * from emp where sal in(select max(sal)from emp where
sal<(select max(sal)from emp));
Or
Select max(sal) from emp where sal<(select max(sal)from emp);

4. I have Dept No, Dept Name, and Salary I want to display second
highest salary from each department’s wise write the query In SQL?
Select deptno,dname,sal,rnk from (select dense_rank()over(order by
sal desc)as rnk,deptno,dname,sal from emp)where rnk=2;
5. Delete the duplicates by using distinct?
Delete from emp where rowid not in(select max(rowid)from emp group
by empno);

6. Want to display the first three lines in a file which including header?
Select * from (select rownum as rno,emp.* from emp)where rno<=3;

7. How do you select maximum marks from each subject in student


table?
Select subject,max(marks)from stud group by subject;

8. How do you select top 2 maximum marks from each subject?


Select subject,marks,rnk from(select dense_rank()over(partition by
subject order by marks desc)as rnk,subject,marks from stud)where
rnk<=2;

9. 1.Find sum of trans_amt department wise from the table ?


Scenario-1:

cust_id trans_amt
1001 10
1001 20
1001 30
1002 5
1002 40
1002 50
1003 20
1003 30
Select cust_id,sum(trans_amt)from table group by cust_id;
10. There are two input files .. file1--2000 record, file2 300 records.
I want get 2 columns from the second file?
Select column1,column2 from table2;

11. Table1 have 10000 records, table 2 have 3 records matching


with table1. How to write sql query and how to desig abi grap?
Select * from table1 a,table2 b where
a.common_column=b.common_column;
Or
By using look up
Or
By using join

12. How to fetch duplicate records from the table?


select count(*),empno from emp group by empno having count(*)>1;

13. What is diff between union and union all?


Union removes duplicates. And all recrods from first table
+unmatched records from second table
Union all can not rempve duplicates and all records from first table+all
records from second table

14. Is union all and full outer join are same?


no
15. N th highest salary?

16. How we remove duplicate rows ?

Delete from emp where rowid not in(select max(rowid )from emp
group by empno);
17. How to print unique rows from table
Select distinct* form emp;
Or select unique * from emp;

18. Oracle - There are 3 tables having channel id , channel name in


one table , date id ,date in other table and fact table having date id,
channel id and name - need to find for year how many channels - was
able to ans partially explained the logic not the exact sql query
19. Input file with today's insert and update and target fact table
with billion records. CDC on 5 keys. Approach to update and insert the
fact using ABI and SQL.
20. How do replace NULL values with default values in SQL? (DB2)
Update emp set comm=nvl(comm,0);
21. Most complex SQL you have written.

22. Left outer, right outer, Inner join?


Left outer join: matched records+unmatched from left table
Select * from emp e left outer join dept d on(e.deptno=d.deptno);
Or select * from emp e ,dept d where e.deptno=d.deptno(+);
Right outer join: matched records + unmatched from right table
Inner join:only matched records

23. How to delete duplicate rows from table


24. Difference between delete and truncate
Delete: delete is a dml command
After delete you can rollback
Where condition is used in delete
Truncate:it is used to delete all the data from the table
It is a ddl command
We cannot roll back after using this command.
Where condition is not used in truncate command
25. what is the use of SQL?
1.sql is a power ful data manipulation language
2. it is common for all dbms
3. it is 4 th generation language
4.it is single statement specific language
5.it is database universal language
6.it is non procedurel language
26. I want to display from 50 70 th records
Select * from(select rownum as rno,emp.* from emp)where rno
between 50 and 70;

27. How to find out the Second Highest Salary in SQL?


28. What is the Primary Key?
Primary key is a key which is not null, which is to be unique,which can
not be change it is called primary key
29. What is the Difference b/w Primary key & Unique key?
Primary key does not allows null values and duplicate values
Where as unique key allows null values but not allows duplicate values

30. What is Index?


Index is used for to reduce query processing time i.e increases
performance
Index is required for the most frequently used columns in the
database.
Index also occupies memory.
The column on which index is created is known as key column
Create index i1 on emp(deptno);
31. What is the difference b/w Truncate and delete?
32. What is view?
View is a virtual object or imaginary object
The main concept of view is to hide part of the table
To provide security for data
To define complex quries
4 types
1.simple vies
2.complex view
3.forced view
4.materialized view
33. Have 10 Records How To Get Row Wise In Sql?
Select rownum,table.* from table;
34. Describe how you would ensure that database object definitions
(Tables, Indices, Constraints, Triggers, Users, Logins, Connection
Options, and Server Options etc) are consistent and repeatable
between multiple database instances (i.e.: a test and production copy
of a database).
35. How would you find out whether a SQL query is using the indices
you expect?
36. What are primary keys and foreign keys?
In a foreign key reference, a link is created between two tables when
the column or columns that hold the primary key value for one table
are referenced by the column or columns in another table. This column
becomes a foreign key in the second table.
37. How do you truncate a table?
Truncate table tablename;
38. What is the name of the SQL language used in SQL Server
stored procedures?
Pl sql
39. How to fetch duplicate recods from the table?
40. How comfortable you are with oracle and give me rating out of
5?
3
41. Write down the where clause order of execution in oracle?
Selectfromwheregroupbyhavingorderby
42. Write the syntax for all type of Joins?
43. Write any SQL to delete duplicates from given table?
44. how to find the 2nd highest salary from every department
45. how to generate rank & dense rank in oracle
select dense_rank()over (order by sal desc)as rnk,emp.* from emp;
select rank()over (order by sal desc)as rnk,emp.* from emp;

46. Why we use the Data ware house?

47. I want 3rd max salary?

Select * from emp where sal in(select max(sal)from emp where


sal<(select max(sal)from emp where sal<(select max(sal)from
emp )));
48. I want Top 3 salaries?
49. Select * from emp where sal >=(select max(sal)from emp where
sal<(select max(sal)from emp where sal<(select max(sal)from
emp )));
Or
Select * from (select dense_rank()over(order by sal desc)as rnk,emp.*
from emp)where rnk<=3;

50. How to find out the Second Highest Salary in SQL?


51. I have Dept No, Dept Name, Salary I want second Highest
salary in Different departments write the query In SQL?
52. Left outer, right outer, Inner join?
53. Delete the duplicates query?
54. I want to display from 50 70 th records in SQL?
55. Write any SQL to delete duplicates from given table?
56. Tell me the order of execution of order n where n group by n
having clauses in oracle
57. Display unique jobs from EMP table.
58. List the details of the emps in asc order of their salaries.
Select * from emp order by sal ;
59. Display all the unique job groups in the descending order
Select unique job from emp order by job desc;
60. Display all the details of all ‘Mgrs’
Select * from emp where empno in(select mgr from emp);
61. List the emps who joined before 1981.
Select * from emp where hiredate<’31-dec-1981’;
62. List the Empno, Ename, Sal, Daily Sal of all Employees in the
ASC order of AnnSal.
Select empno,ename,sal,sal/30 as daily_sal from emp order by
sal*12 ;
63. Display the empno , ename, job, hiredate, exp of all Mgrs
Select
empno,ename,job,hiredate,months_between(sysdate,hiredate)/12 as
exp where empno in(select mgr from emp);
64. List the empno, ename, sal, exp of all emps working for Mgr
7839.
65. Display the details of the emps whose Comm. Is more than their
sal.
66. List the emps in the asc order of Designations ?
67. List the emps along with their exp and daily sal is more than
Rs.100?
68. List the emps who are either ‘CLERK’ or ‘ANALYST’ in the desc
order?
69. List the emps who joined on 1May81,31Dec81, 17Dec81,
19Jan80 in asc order of seniority?
70. List the emps who are working for the deptno 10 or 20?
71. List the emps who are joined in the year 1981?
72. List the emps who are joined in the month of Aug 1980?
73. List the emps whose annul sal ranging from 22000 and 45000?
74. List the emps those are having five characters in their names?
75. List the enames those are starting with ‘s’ and with fire
characters?
76. List the emps those are having four chars and third char must be
‘r’?
77. List the 5 character names starting with ‘s’ and ending with ‘h’?
78. Select * from emp where hiredate like ‘%JAN%’ ?
79. List the emps who joined in the month of which second character
is ‘a’?
80. List the emps whose sal is 4 digit number ending with zero?
81. List the emps whose names having a character set ‘ll’ together?
82. List the emps those who joined in 80’s?
83. List the emps who does not belong to deptno 20?
84. List all the emps except ‘president’ & ‘Mgr’ in asc order of
salaries?
85. List the emps whose empno not starting with digit 78?
86. List the emps who joined in any year but not belongs to the
month of March?
87. List the emps who joined in any year but not belongs to the
month of March?
88. List all the clerks of deptno 20?
89. List the emps of deptno 30 or10 joined in the year 1981?
90. Display the details of ‘Smith’?
91. List the total information of emp table along with dname and loc
of all the emps working under ‘Accounting’ & ‘Research’ in the asc
deptno?
92. List the empno, ename, sal, dname of all the ‘Mgrs’ and ‘Analyst’
working in NEWYORK, DALLAS with an exp more than 7 years without
receiving the Comma Asc order of Loc?
93. List the empno, ename, sal, dname, loc, deptno, job of all emps
working at CHICAGO or working for ACCOUNTING dept wit ann sal >
28000, but the sal should not be = 3000 or 2800 who doesn’t belongs
to the Mgr and whose no is having a digit ‘7’ or ‘8’ in 3rd position in
the asc order of deptno and desc order of job?
94. Display the total information of the emps along with grades in
the asc order?
95. List all the grade2 and grade 3 emps?
96. Display all grade 4,5 Analyst and Mgr?
97. List the empno, ename, sal, dname, grade, exp, ann sal of emps
working for dept 20 or 10?
98. List all the information of emps with loc and the grade of all the
emps belong to the grade ranges from 2 to 4 working at the dept
those are not starting with char set ‘OP’ and not ending with ‘S’ with
the design having a char ‘a’ any where joined in the year 81 but not in
the month of Mar or Sep and sal not end with ‘00’ in the asc order of
grades?
99. List the details of the emps whose salaries more than the
employee BLAKE?
100. Difference between truncate and delete, drop?
102. Difference between DDL and DML commands?
103. Different types of Clauses in oracle?
Where clause
Having
From
Order by
Group by

104. Difference between where clause and having Clause?


105. What is composite primary key?
106. Is it possibility to create primary key on two fields?
no
107. Difference between rownum and rowid?
108. Different types of Aggregate functions?
109. How to find maximum salary of employee from a table?
110. How to find nth highest salary of employee from a table?
111. How to find maximum salary fromeach group in a table?
112. Display the records from nth (10) to n+5(15) records from a table?
113. How to find maximum salary from each group in a table and display
the records whose max salary greater than 2500?
114. How to find duplicate records from a table and display the records
which are repeated thrice or more?
115. What are the performance techniques from database side?
116. How to add a new column to existing table?
Alter table tablename add column datatype
117. How to update a record in a table?
Update table set empno=1000 where enme=
118. How to create a new table from existing table without data and with
data?
Create table tablename as select * from emp ;
Create table tablename as select * from emp where1=2;

119. Explain about procedures?


120. What is the difference between join, union?
121. What is the difference between view and materialized view?
122. What is the difference between full outer join and union all?
123. My Input like
U1,O1,I1

U1,O1,I2

U1,O1,l3

U1,O2,I1

U2, O5,I1

U2, O5,I2

U2, O6, 12

U2,O7,I4

124. how to see the duplicate records in a table


125. Oracle: how the where clause is executed in oracle
Selectfromwhere
126. Which version are you using? What are the latest features in 11G?
127. How to referential check constraint in your table
128. Write a sql statement using left outer join?
129. How to delete duplicates in oracle?
130. How to delete duplicate records in emp ?
131. What is NVL Function in Oracle Give me an example?
132. Like NVL Function in Oracle, Is there any other function which we can
use in ABINITIO?
133. How many types of joins in oracle?
134. table1 have 10000 records, table 2 have 3 records matching with
table1.. how to write sql
query and how to desigabigrap?
135.is union and full outer join are equal?
136. nth highest salary?
137. Oracle - There are 3 tables having channel id , channel name in one
table , date id ,date in
other table and fact table having dateid,channel id and name - need to find
for year how many
channels - was able to and partially explained the logic not the exact sql
query?
138. How do replace NULL values with default values in SQL?
139. Most complex SQL you have written?
140. what is the use of SQL?
141. how to generate rank &densce rank in oracle?

You might also like