1.About your project/Day to Day Activities 2.Identifying duplicates and Deleting duplicates Ans : delete from emp where rowid not in (select max(rowid) from emp group by empid); Ans :delete from emp a where rowid not in (select max(rowid) from emp b where b.empid=a.empid); Ans : select department_id , count(department_id) from employees group by department_id having count(department_id) >1 3.2nd maximum salary Ans : with w as (select first_name , salary , dense_rank() over(order by salary desc) as rnk from employees ) select * from w where rnk=2; Ans : select * from (select first_name , salary , dense_rank() over(order by salary desc) as rnk from employees) where rnk = 2; Ans : select max(salary) from employees where salary <> ( select max(salary) from employees ); Department wise maximum salary Ans : with w as (select first_name , salary ,department_id, dense_rank() over(partition by department_id order by salary desc) as rnk from employees ) select * from w where rnk=1; 4. rownum and rowid diff Ans : Rownum : It just displays sequence number to the query output. It doesn't stored in database. it will work for = 1 and <= n in select stmnt. Rowid : For every row in a table , oracle creates unique 18 digits alpha numeric id which is stored in database.mainly used for performance improvement 5.case and decode diff Ans : Case : We can use relational operators. case is faster. can be assigned to a variable. case can be written independently or in select statement Decode : We cannot use relational operators. can't be assigned to a variable. Only it can be used in select statement. 6.write all the sql and plsql concepts you know 7.what is synonym and purpose? Ans : synonym - alternative name for an object we can create multiple synonym for an object we can create multiple synonym for a synonym if table is dropped then what abt synonym ? invaild - synonym translation is no longer valid is it possible to perform dml on synonym ? yes is it possible to create synonym with the same name of an object name ? yes Purpose : To access a particular db object by different user with different access create or replace synonym st1 for t1; create or replace synonym st2 for st1; select * from user_synonyms; drop synonym st1; sqlplus hr/admin create user omr identified by omr; create user tbm identified by tbm; create table t1(a number); create synonym omr.t1 for hr.t1; create synonym tbm.t1 for hr.t1; grant select on omr.t1 to omr; grant insert on tbm.t1 to tbm; connect omr/omr select * from t1; connect tbm/tbm insert into t1(a) values(1); 8.sequence and purpose Ans : It generates unique number automatically . mainly used to generate pk column values. create table t2( b number primary key, c varchar(5) ); create sequence t2sq; insert into t2(b,c) values(t2sq.nextval,'hi'); Don't execute currval after creating sequence. 9.diff between group fun and analytical fun Ans : Group functions : min / max / sum / avg / count Analytical functions : Rank / Dense_rank / Row_number / Lead / Lag / Listagg Group functions : It groups the rows and retrieves one row for each group Analytical functions : It retrieves one row for each row 10.11g features Ans : Listagg / interval / follows / compound trigger / virtual column / pivot / regexp_count 11.replace and translate diff Ans : Single row functions replace : we can change a word by word translate : we can change a letter by letter select replace('tamil book','tamil','english') from dual; select translate('zoho','zh','lg') from dual; 12.instr and substr diff with examples Ans : instr : 4 arguments - it displays position of a character substr : 3 arguments - it displays a particular string of a string 13.identify the no of 'a' in a string Ans : Balakrishnaa select regexp_count('Balakrishnaa','a') from dual; select length('select') - length(replace('select','e')) from dual; 14. what is view - why ? - functionality Ans : View contains a query - virtual table - memory can not be utilized. simple / complex view. (simple view - with check option / with read only). we can not create index on views. we cannot perform dml on with read only, complex view. dml possible on with check option and simple views. we can create trigger on views by using instead of(timing point). we can create view for non existing table by using force keyword 15.mview and refresh methods It is nothing but snap of a query result. mainly used for report generation. compelete / fast / force 16.constraints Ans : primary key => no null , no duplicate foreign key => yes null , yes duplicates unique => yes nulls , no duplicates not null => no nulls , yes duplicates check => conditional insert 17.delete and truncate diff Ans : both used to delete the records Delete : dml / high water mark will be at the end of the data block / where clause / non auto commit so we can rollback or commit. Truncate : ddl / high water mark will be at the top of data block / where not applicable / auto commit so tcl not applicable 18.nulls first and null last Ans : select * from employees order by department_id asc nulls first; select * from employees order by department_id desc nulls last; 19.null functions with real time scenario Ans : nvl / nvl2 / nullif / coalesce select coalesce(email,phno) from employees; 20.write a query with group by clause Ans : select department_id , max(salary) from employees group by department_id; 21.joins - diff - cartesian product inner join => matching records left outer join => matching of both , unmatching of left table right outer join => matching of both , unmatching of right table full outer join => matching and unmatching of both tables self join => joining table within itself cross join => cartesian poduct , no of rows in first table * no of rows in second table 22.why we are using gtt ? disadvantages Ans : The project ( application ) has many tab kind of events. for every event user may give input or delete some records. to manage this activities we are going for gtt. user from one session , may do dml but can not be visible to diff user 23.what is index ? functionality - types Ans : why ? to retrieve the data faster but not all the times decision maker - optimizer when ? whenever a column frequently called in where clause b*tree bitmap unique composite reverse key function based 24.procedure and functions diff 1.both are called as subprograms. both are used to implement a specific business logic. these are used to convert a business into technical. 2.procedure may or may not return a value and you can perform anything inside a procedure but for ddl we have to use execute immediate(dynamic sql). 3.whereas in function we can not perform dml and it must return a value. to make dml perform we can use pragma autonomous_transaction. we can not do ddl in function. 4. procedure can be executed in anonymous block or exec or execute but function only in select statement. 5.we can assign a function to a variable. 25.how many table u 've created? Ans : so many tables / product owner / tl / bu manager tbl_customer_details / tl_debit / tl_credit / tbl_pymnt / tl_pymnt_ref/ tbl_order_info / tl_trans_data / tbl_placed_orders / tl_reject_orders 26.how many subprograms u 've created? Ans : so many 15 programs 27.how exceptions handling in your project ? 28.recently created procedure/function/package ? 29.yesterday the query was running fast but not today why ? 30.what is cursor ? - attributes - real time example Ans : cursor - cursor is a private sql work area , it will fetch only one row at a time. compile time data fetching process implicit cursor found / notfound (select not applicable) / rowcount explicit cursor isopen / notfound / rowcount 31.diff between cursor and ref cursor - real time scenario Ans : 32.files handling concepts in oracle - which is faster ? Ans : Sql loader / External Tables / utl_file 33.write a query for delete and update with joins Ans : update emp set salary = (select salary from employees where employee_id = emp.employee_id); 34.insert values using select statement Ans : insert into emp select first_name , salary , manager_id from employees; 35.create table using select statement and structure ? Ans : create table emp as select first_name , salary , manager_id from employees where employee_id <= 109; create table emp as select first_name , salary , manager_id from employees where rownum = 2; 36.how take insert scripts using sql developer/toad Ans : Toad => query => select all data => export dataset => choose options 37.how to check the program error and debug ? 38.connect by level - hierarchycal query 39.collection types - where u used in your project 40.collection type as table column 41.mutating table error - hw to solve it ? 42.where u 've used trigger in ur project 43. :new ? :old ? 44.what is compound trigger 45.recently faced issue , ur contribution ? 46.how oracle works while executing the query Ans : 47.how to read explain plan Ans : sqlplus : set autotrace on; Explain plan for select * from employees; select dbms_xplan.display from dual; toad => ctl+e 48.function based index - issue - why ? Ans : Whenever a column called frequently with function in where clause. Avoid using virtual column and function based index - performance issue 49. any two 12c features Ans : Identity Columns Versioning 50.packages - advantages 51.authid definer and authid current user ? 52.have u worked in dts preparation ? Ans : Detailed Technical specification db developers will be defining everything about proc/fun parameters 53.write a query - who joined on tuesdays ? Ans : select first_name , salary , to_char(hire_date,'DAY') from employees where to_char(hire_date,'D') = 3; select first_name , salary , to_char(hire_date,'DAY') from employees where to_char(hire_date,'DY') = 'TUE'; 54.list all the alphabets - write a query Ans : select chr(64+level) from dual connect by level <= 26; 55.possible to create fk for the same table ? Ans : create table t11( a number primary key , b number); alter table t11 add constraint a_fk foreign key(b) references t11(a); select * from t11; insert into t11(a,b) values(4,3); 56. how to generate awr report ? - how to read it 57.query tuning - how do u do that ? 58.what is table fragmentation ? 59.explain plan - how to get it ? and how to read it ? 60.sample real time procedure and function 61.database connection strings - dba work 62.basic unix commands - 10 frequently used commands 63.what is olap ? Online Analytical Processings 64.what is oltp ? Online transaction Processings 65.what is subquery ? correlated sub query? 66. joins and sub query diff 67.with clause and sub query Ans : with s as ( select a.employee_id , a.first_name , (select b.department_name from departments b where b.department_id = a.department_id) as department_name from employees a ) select * from s; select a.employee_id , a.first_name , (select b.department_name from departments b where b.department_id = a.department_id) as department_name from employees a; 68.Inline view ? Ans : if sub query placed in from clause. select * from (select first_name , salary , dense_rank() over(order by salary desc) as rn from employees ) where rn=1; 69.who is getting the maximum salary ? Ans : select * from (select first_name , salary , dense_rank() over(order by salary desc) as rn from employees ) where rn=1; select first_name from employees where salary = (select max(salary) from employees); 70.dynamic sql ? declare a varchar(50) := 'first_name'; c varchar(50) := 'salary'; b employees.first_name%type; d employees.salary%type; begin select a into b from employees where salary = 24000; dbms_output.put_line(b); execute immediate 'select '||a||' from employees where salary = 24000' into b; execute immediate 'select '||c||' from employees where first_name = ''Neena''' into d; dbms_output.put_line(b); dbms_output.put_line(d); end; / 71.how to insert values in clob , blob and bfile ? 72. There is a column name Inv-128 how you print separately char alone and number alone? Ans : select regexp_replace('inv123','[a-z]') , regexp_replace('inv123','[0-9]') from dual; 73. insert - Last One year data into another table insert into emp select * from employees where employee_id in ( select employee_id from employees where hire_date between sysdate and add_months(sysdate,-12) ); 74. Get - Last 30 days data select * from emp where entry_date between sysdate and sysdate-30; 75. Month wise employees count ? select to_char(hire_date,'mon') , count(first_name) from employees group by to_char(hire_date,'mon'); 76. Which dep is having max no of employees ? select department_id , count(employee_id) from employees group by department_id having count(employee_id) = (select max( count(employee_id) ) from employees group by department_id); 77. Recently inserted record ? select * from employees where rowid = (select max(rowid) from employees); 78. Total salary by using plsql block declare i number := 0; begin for j in (select salary from employees) loop i := i+j.salary end loop; dbms_output.put_line(i); end; / 79. t1 a b c d ? if male then 10 if female then 20 101 sai m 10 102 amu f 20 103 ram m 10 104 sindhu f 20 105 raja m 10 update t1 set d = case when c='m' then 10 when c='f' then 20 end; 80. Change fname to lname and lname to fname update employees set first_name = last_name , last_name = first_name; 81. when parent key not found error will occur ? 82. when child record found error will occur ? 83. what is on delete cascade / on delete null ? 84. what is novalidate ? -- not to validate the existing data t1 a 1 1 2 create index idx on t1(a); alter table t1 add constraint a_pk primary key(a) novalidate; 85 . How to get no of records in a table ? select count(*) from employees; select num_rows from user_tables where table_name='EMPLOYEES';