Home » SQL & PL/SQL » SQL & PL/SQL » Interview questions
| Interview questions [message #429223] |
Mon, 02 November 2009 10:37  |
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 #429227 is a reply to message #429224] |
Mon, 02 November 2009 10:55  |
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 #429231 is a reply to message #429228] |
Mon, 02 November 2009 11:25 |
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 #429312 is a reply to message #429231] |
Tue, 03 November 2009 03:39 |
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.
|
|
|
|
Goto Forum:
Current Time: Sat Dec 14 12:41:42 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
