project class syllabus prepared on 20.06.2018 ================= * do's and dont's new mobile number/email id must be polite in interview dont be scared and nerveous dont be fast while answering dont discuss anything with anyone dont share the company / ctc / other info to strangers dont inform anyone that you are going for an interview dont take your notes while going for an interview dont represent greens anywhere dont assume anything / no negative thoughts dont use my name / dinesh sir name resume always same as naukri keep add / remove keywords in your profile to get more calls post interview questions once you done check about jobs on daily basis be active in project group post relavant job openings in group inform me once you got shortlisted * how to prepare a resume ? peoplesoft.hireit.com google - plsql resume for experienced candidate domains - banking / telecom / logistics / insurance / healthcare / software 1 yr college project + 1 banking project 2 yrs 1 banking project + 1 other domain 3 yrs 1 banking project + 1 other domain 4 yrs 1 banking project + 2 other domains resume must be unique dont copy irrelavant stuff. mention what you know 3 years exp - performance tuning stuff must should be uploaded after my review * how to schedule a interview ? * what are the things you have to tell and ask? * screening * product based / service based company * it infrastructure - reporting heirarchy * how to face the interview? tell about yourself - only about you sql 10 mins plsql 10 mins project 10 mins manager 20 mins hr 10 mins * sdlc project overview 5 phases analysis (gathering business requirements) - brd or dfs document - by BA designing (cdm/ldm/pdm) - by SA development (procedures/functions/packages/triggers/views) *dts document - by Developers *unit testing (test case document) testing *bug fixing - java + oracle *production deployment maintanance *production support l1/l2/l3 *bug fix / change requests * support project overview * db structure - connection strings * data dictionary tables * sql/plsql refresh session * sample interview questions * advance sql/plsql * 11g/12c features listagg virtual column compound trigger regexp_substr/regexp_instr/regexp_replace interval partitioning follows trigger pivot / unpivot * primary key / unique - unique index * multi table insert * drop with purge * toad short cuts * on delete cascade / on delete null * no validate * listagg/rollup/cube * regexp * default parameter * authid definer/authid current_user * constant variable * sample program for refcursor * collection as out in function * trigger - do not allow insert on sat sun * parameterized cursor * returning into * for update / no wait / where current of * mini project * performance tuning about optimizer nested loop join hash join sortmerge join index index scans index range scan skip scan table access by rowid table access full bind variable how query works execution plan / explain plan query tuning plsql tuning awr report table partitioning hints List of software companies microfocus ramco systems beyontec 3i tech indus software information dynamics banca sella msc technology sundaram infotech sterling software qic einstean scio prodapt ford fourace fis css corp tcs cts ibm acceture aspire igate amazon ntt data virtusa polaris capgemini infosys wipro hexaware socite general kumaran systems Interview Questions =============== tools to be installed sql developer toad sqlplus 1.About your project/Day to Day Activities 2.Identifying duplicates and Deleting duplicates 3.2nd maximum salary 4.rownum and rowid diff 5.case and decode diff 6.write all the sql and plsql concepts you know 7.what is synonym and purpose? 8.sequence and purpose 9.diff between group fun and analytical fun 10.11g features 11.replace and translate diff 12.instr and substr diff with examples 13.identify the no of 'a' in a string 14. what is view - why ? - functionality 15.mview and refresh methods 16.constraints 17.delete and truncate diff 18.nulls first and null last 19.null functions with real time scenario 20.write a query with group by clause 21.joins - diff - cartesian product 22.why we are using gtt ? disadvantages 23.what is index ? functionality - types 24.procedure and functions diff 25.how many table u 've created? 26.how many subprograms u 've created? 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 31.diff between cursor and ref cursor - real time scenario 32.files handling concepts in oracle - which is faster ? 33.write a query for delete and update with joins 34.insert values using select statement 35.create table using select statement 36.how take insert scripts using sql developer/toad 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 47.how to read explain plan 48.function based index - issue - why ? 49. any two 12c features 50.packages - advantages 51.authid definer and authid current user ? 52.have u worked in dts preparation ? 53.write a query - who joined on tuesdays ? 54.list all the alphabets - write a query 55.possible to create fk for the same table ? 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 68.Inline view ? 69.who is getting the maximum salary ? 70.dynamic sql ? 71.how to insert values in clob , blob and bfile ? 72.cumulative salary