Home » SQL & PL/SQL » SQL & PL/SQL » Interview questions
Interview questions [message #429223] Mon, 02 November 2009 10:37 Go to next message
narayanan.v
Messages: 29
Registered: September 2005
Junior Member
1) We have emp table, We need to reterive the records with name starting with letter 'N'? . We have index defined on Name column.

I told two options for this using LIKE 'N%' or using SUBSTR(name,1,1) = 'N'. Apart from this is there any other better way of doing this.

2) We have table with student id and 5 subjects as columns. We need to reterive the records of those students who cleared atleast 3 papers (pass mark 35).

create table student (name varchar2(50),sub1 number(3),sub2 number(3),sub3 number(3),sub4 number(3),sub5 number(3));

insert into student values ('A',46,56,32,78,91);
insert into student values ('B',16,38,92,25,81);
insert into student values ('B',86,88,12,25,34);
insert into student values ('B',86,88,62,85,94);


3) We have sales table. Where we have sales person name,the amount of sales he made. We need to get the no of records for each range. we have 5 ranges (1 Lak to 2 Laks,3 Laks - 4 Laks,5 Laks to 6 Laks,7 Lak - 8 Laks.

create table sales (name varchar2(10),sales number);
insert into sales values ('A',343234);
insert into sales values ('A',443234);
insert into sales values ('A',485634);
insert into sales values ('A',185634);
insert into sales values ('A',585634);
insert into sales values ('A',434634);
Re: Interview questions [message #429224 is a reply to message #429223] Mon, 02 November 2009 10:42 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1) If the only things you are allowed to do is SELECT, the first option is the best one.

2) What is your query?
hint: you can use CASE

3) What is your query? And what is a Lak?
hint: if you divide by a Lak, you have the values in Laks and can group by Lak units.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: Interview questions [message #429227 is a reply to message #429224] Mon, 02 November 2009 10:55 Go to previous messageGo to next message
narayanan.v
Messages: 29
Registered: September 2005
Junior Member
For my question 2,please consider the below insert queries to generate the data. Basically with my conditions, we need to get only A,B and D as out put. As they are the one who have scored more than 35 marks in atleast 3 subjects,where as C has done that only for 2 subjects. So I need the query which will give the output as A,B and D.

insert into student values ('A',46,56,32,78,91);
insert into student values ('B',16,38,92,25,81);
insert into student values ('C',86,88,12,25,34);
insert into student values ('D',86,88,62,85,94);

For my question 3, I need the query which will the give the output like this.
Range Count
100000-200000 1
200001-300000 1
300001-400000 3
400001-500000 0
500001-600000 0
600001-700000 0
800001-900000 0



Note :-
1 Lak means hundred thousand (100,000)
Re: Interview questions [message #429228 is a reply to message #429227] Mon, 02 November 2009 11:08 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I know what you must get, what I want is you finding the queries by yourself and posting them.

Regards
Michel
Re: Interview questions [message #429231 is a reply to message #429228] Mon, 02 November 2009 11:25 Go to previous messageGo to next message
narayanan.v
Messages: 29
Registered: September 2005
Junior Member
Hi,
If I know the answer, Why should I post in this forum itself ?. For my second question I answered that we can use case statement like the following query.

select name from (
select
name,
case
when sub1 > 35 then 1
else 0
end
+
case
when sub2 > 34 then 1
else 0
end
+
case
when sub3 > 34 then 1
else 0
end
+
case
when sub4 > 34 then 1
else 0
end
+
case
when sub5 > 34 then 1
else 0
end passed_subjects
from student)
where passed_Subjects >= 3
/
But interviewer asked for the better query,so i want to know is there any better way of writing it. For the third one also i suggested to have case statment in sql query,once again interviewer asked for better query. So if you can help me it will be greatfull.
Re: Interview questions [message #429235 is a reply to message #429231] Mon, 02 November 2009 11:55 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Do it and repost.

Regards
Michel
Re: Interview questions [message #429312 is a reply to message #429231] Tue, 03 November 2009 03:39 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
If I know the answer, Why should I post in this forum itself ?.

Because we'll help you to work the answer out for yourself, but (certainly for simple problems) we'd rather not do it for you. you'l learn much more by working it out than by copying our answers

As these are pretty clearly homework questions, that should be of some importance to you - we aren't going to be there in the exam.

For the second one, I'd use the SUM aggregate function grouped by Student, and a CASE statement that returned 1 if a particular row was a pass, and 0 if the row was a fail.
Previous Topic: Writing file from random position using UTL_FILE
Next Topic: Queue with built-in datatype
Goto Forum:
  


Current Time: Tue Sep 27 12:44:03 CDT 2016

Total time taken to generate the page: 0.13159 seconds