I was asked for this in interview...?? how to solve this..

Hi groups, I got this question in interview...
I have 3 table,
(1)select * from Mark_Details
(2)select * from Mark
(3)select * from Student
(1)select * from Mark_Details
--
StudId Markid Marks
--
A1 1 75
A1 2 70
A1 3 80
A1 4 85
A1 5 90
A2 1 70
A2 2 75
A2 3 80
A2 4 90
A2 5 80
(2)select * from Mark
--
Markid markname
--
1 Maths
2 Science
3 Social
4 English
5 Tamil
(3)select * from Student
--
StudId studname
--
A1 Selva
A2 Akbar
----
--
Is there any way to get the output given below...I tried subqueries but not
got it so for..is it possbile.?
----
--
Studid Studname Maths science Social English Tamil
----
--
A1 Selva 75 70 80 85 90
A2 Akbar 70 75 80 90 80
----
--
please suggest me...
MahesMahesh Kumar.R wrote:
> Hi groups, I got this question in interview...
> I have 3 table,
> (1)select * from Mark_Details
> (2)select * from Mark
> (3)select * from Student
> (1)select * from Mark_Details
> --
> StudId Markid Marks
> --
> A1 1 75
> A1 2 70
> A1 3 80
> A1 4 85
> A1 5 90
> A2 1 70
> A2 2 75
> A2 3 80
> A2 4 90
> A2 5 80
> (2)select * from Mark
> --
> Markid markname
> --
> 1 Maths
> 2 Science
> 3 Social
> 4 English
> 5 Tamil
> (3)select * from Student
> --
> StudId studname
> --
> A1 Selva
> A2 Akbar
> ----
--
> Is there any way to get the output given below...I tried subqueries but n
ot got it so for..is it possbile.?
> ----
--
> Studid Studname Maths science Social English Tamil
> ----
--
> A1 Selva 75 70 80 85 90
> A2 Akbar 70 75 80 90 80
> ----
--
> please suggest me...
> Mahes
I hope they gave you a better spec than you have here - like DDL
including primary and foreign keys and constraints. Here are two
solutions, obviously with some assumptions about the keys in your
example tables.
In SQL Server 2000:
SELECT S.studid, S.studname,
SUM(CASE WHEN M.markname = 'maths' THEN marks END) AS maths,
SUM(CASE WHEN M.markname = 'science' THEN marks END) AS science,
SUM(CASE WHEN M.markname = 'social' THEN marks END) AS social,
SUM(CASE WHEN M.markname = 'english' THEN marks END) AS english,
SUM(CASE WHEN M.markname = 'tamil' THEN marks END) AS tamil
FROM mark_details AS D
JOIN stud AS S
ON D.studid = S.studid
JOIN mark AS M
ON D.markid = M.markid
GROUP BY S.studid, S.studname ;
In SQL Server 2005:
WITH student_marks (studid, studname, markname, marks)
AS (
SELECT S.studid, S.studname, M.markname, D.marks
FROM mark_details AS D
JOIN stud AS S
ON D.studid = S.studid
JOIN mark AS M
ON D.markid = M.markid
)
SELECT
D.studid,
D.studname,
P.maths,
P.science,
P.social,
P.english,
P.tamil
FROM
student_marks AS D
PIVOT (
SUM(marks)
FOR markname IN ([maths],[science],[social],[english],[t
amil])
) AS P
Both of these are untested.
David Portas
SQL Server MVP
--|||Like David mentioned, these specs are incomplete. I'd like to add that even
with DDL and constraint info, there are a number of techniques that will
work with for the test data provided yet won't produce the desired results
when other complexities are added. For example, should students that take
only some or none of the courses be included?
Asking the right questions can do more to demonstrate your knowledge than
providing a correct answer. At a minimum, it's a good idea to list any
assumptions made and/or provide alternative solutions when the provided
information is incomplete.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mahesh Kumar.R" <maheshkumar@.sninform.com> wrote in message
news:uzZm0MZ9FHA.2640@.tk2msftngp13.phx.gbl...
Hi groups, I got this question in interview...
I have 3 table,
(1)select * from Mark_Details
(2)select * from Mark
(3)select * from Student
(1)select * from Mark_Details
--
StudId Markid Marks
--
A1 1 75
A1 2 70
A1 3 80
A1 4 85
A1 5 90
A2 1 70
A2 2 75
A2 3 80
A2 4 90
A2 5 80
(2)select * from Mark
--
Markid markname
--
1 Maths
2 Science
3 Social
4 English
5 Tamil
(3)select * from Student
--
StudId studname
--
A1 Selva
A2 Akbar
----
--
Is there any way to get the output given below...I tried subqueries but not
got it so for..is it possbile.?
----
--
Studid Studname Maths science Social English Tamil
----
--
A1 Selva 75 70 80 85 90
A2 Akbar 70 75 80 90 80
----
--
please suggest me...
Mahes|||I think a dynamic query can work this things out
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Mahesh Kumar.R" wrote:

> Hi groups, I got this question in interview...
> I have 3 table,
> (1)select * from Mark_Details
> (2)select * from Mark
> (3)select * from Student
> (1)select * from Mark_Details
> --
> StudId Markid Marks
> --
> A1 1 75
> A1 2 70
> A1 3 80
> A1 4 85
> A1 5 90
> A2 1 70
> A2 2 75
> A2 3 80
> A2 4 90
> A2 5 80
> (2)select * from Mark
> --
> Markid markname
> --
> 1 Maths
> 2 Science
> 3 Social
> 4 English
> 5 Tamil
> (3)select * from Student
> --
> StudId studname
> --
> A1 Selva
> A2 Akbar
> ----
--
> Is there any way to get the output given below...I tried subqueries but n
ot got it so for..is it possbile.?
> ----
--
> Studid Studname Maths science Social English Tamil
> ----
--
> A1 Selva 75 70 80 85 90
> A2 Akbar 70 75 80 90 80
> ----
--
> please suggest me...
> Mahes
>|||For SQL Server 2005, this should work. No subqueries or dynamic SQL needed.
:)
-- Prep tables
create table dbo.marks
(
studentID char(2),
subjectID tinyint,
score tinyint
)
go
create table dbo.subjects
(
subjectID tinyint,
subjectName varchar(15)
)
go
create table dbo.students
(
studentID char(2),
studentName varchar(15)
)
go
-- Spin up data
insert into dbo.marks values('A1','1','75')
insert into dbo.marks values('A1','2','70')
insert into dbo.marks values('A1','3','80')
insert into dbo.marks values('A1','4','85')
insert into dbo.marks values('A1','5','90')
insert into dbo.marks values('A2','1','70')
insert into dbo.marks values('A2','2','75')
insert into dbo.marks values('A2','3','80')
insert into dbo.marks values('A2','4','90')
insert into dbo.marks values('A2','5','80')
insert into dbo.subjects values(1,'Maths')
insert into dbo.subjects values(2,'Science')
insert into dbo.subjects values(3,'Social')
insert into dbo.subjects values(4,'English')
insert into dbo.subjects values(5,'Tamil')
insert into dbo.students values('A1','Selva')
insert into dbo.students values('A2','Akbar')
go
-- solution
with m(studID,studName,subject,score) as
(
select s.studentID,s.StudentName,b.subjectName,m.score
from dbo.students s
join dbo.marks m on s.studentID = m.studentID
join dbo.subjects b on m.subjectID = b.subjectID
)
select StudID,StudName,Maths,Science,Social,Eng
lish,Tamil
from m
pivot
(
max(score)
for subject in ([Maths],[Science],[Social],[English],[T
amil])
) p
Thanks!
Kent|||Mahesh Kumar.R wrote:
> Hi groups, I got this question in interview...
> I have 3 table,
> (1)select * from Mark_Details
> (2)select * from Mark
> (3)select * from Student
> (1)select * from Mark_Details
> --
> StudId Markid Marks
> --
> A1 1 75
> A1 2 70
> A1 3 80
> A1 4 85
> A1 5 90
> A2 1 70
> A2 2 75
> A2 3 80
> A2 4 90
> A2 5 80
> (2)select * from Mark
> --
> Markid markname
> --
> 1 Maths
> 2 Science
> 3 Social
> 4 English
> 5 Tamil
> (3)select * from Student
> --
> StudId studname
> --
> A1 Selva
> A2 Akbar
> ----
--
> Is there any way to get the output given below...I tried subqueries but n
ot got it so for..is it possbile.?
> ----
--
> Studid Studname Maths science Social English Tamil
> ----
--
> A1 Selva 75 70 80 85 90
> A2 Akbar 70 75 80 90 80
> ----
--
> please suggest me...
Sheesh! Why don't they just ask "Do you know what pivot query is"?
Regarding vocabulary, it looks like SQL area seriously lacks one. In
the other thread I see people pointlessy competing piling up
subqueries, istead of just saying "Look, that is just interval coalesce
problem. Look it up in the book ..."
Admittedly there is no such a book yet. I'm writing the one!|||Well Thanks for all your inputs :)..I feel complete now for asking MORE
......
In simple, How to convert ('N' rows ) into ('N' Columns )...I mean 'n' is
dynamic.so i'm not going to give this time mark=Maths..etc..
Mahes.~
"Mikito Harakiri" <mikharakiri_nospaum@.yahoo.com> wrote in message
news:1133408783.342538.244800@.g43g2000cwa.googlegroups.com...
> Mahesh Kumar.R wrote:
> ----
--
not got it so for..is it possbile.?
> ----
--
> ----
--
> ----
--
> Sheesh! Why don't they just ask "Do you know what pivot query is"?
> Regarding vocabulary, it looks like SQL area seriously lacks one. In
> the other thread I see people pointlessy competing piling up
> subqueries, istead of just saying "Look, that is just interval coalesce
> problem. Look it up in the book ..."
> Admittedly there is no such a book yet. I'm writing the one!
>|||Mahesh Kumar.R wrote:
> Well Thanks for all your inputs :)..I feel complete now for asking MORE
> ......
> In simple, How to convert ('N' rows ) into ('N' Columns )...I mean 'n' is
> dynamic.so i'm not going to give this time mark=Maths..etc..
> Mahes.~
>
http://www.aspfaq.com/show.asp?id=2462
David Portas
SQL Server MVP
--|||finally I learned a concept called " CROSS TAB REPORTS in SQL "..thanks for
all..
Mahes
http://spaces.msn.com/members/cyberiafreak
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1133442541.843844.176260@.g47g2000cwa.googlegroups.com...
> Mahesh Kumar.R wrote:
is
> http://www.aspfaq.com/show.asp?id=2462
> --
> David Portas
> SQL Server MVP
> --
>sql