here are a few questions off the top of my head...
1. give a quick outline of the sql to remove all but one "duplicate" row answer: there are many ways, it depends on what you want done with the non-key values bonus marks: the interviewee says "duplicates aren't possible if the proper constraints have been defined" but then goes on to explain how to do it anyway demerit marks: the interviewee fails to say "first, take a backup"
2. can you write a single query to get the top 3 salesmen per region answer: yes, of course bonus marks: the interviewee offers to write the query right there and then demerit marks: any hint of creating a temporary table or using a cursor
3. describe the sequence of execution of query clauses answer: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY bonus marks: the interviewee asks what difference it makes demerit marks: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY (obviously did not understand the question)
4. should every table have an incremental integer primary key answer: no bonus marks: the interviewee describes which tables don't need one demerit marks: puzzled look suggesting this scenario has never crossed the interviewee's mind
5. how would you do a full outer join? answer: with FULL OUTER JOIN syntax bonus marks: the interviewee gives the LEFT OUTER UNION RIGHT OUTER alternative demerit marks: the interviewee asks what a full outer join is
Hm... oracle provides several sample schema. one is HR. over this schema you can define lot of reports.
for example: display first name, last name, salary, and hire date category.
- up to one year
- up to three years
- more than three years
nice selector are analytical function, for example LAG
for example display, how many days is between hire date of two employees
simple select you can tell more about new person than whole HR department and academic questions
Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.