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

I thought this would be simple...

I had originally thought a full outer join would solve this, but alas I'm a roadblock. Perhaps it's because it's a Friday!

The goal is to create the most efficient query that will display results of the daily calls and sales as
this:

Date EmpID Calls Sales
7/1/2006 1 20 5
7/1/2006 2 25 7
7/1/2006 3 NULL 1
7/1/2006 4 10 NULL

The problem is a simple full outer join ends up ignoring EmpID 3 who has no Calls in t1, but I still want that row displayed in the results. Any ideas? TIA

create table t1 (Date smalldatetime, EmpID int, Calls int)

create table t2 (Date smalldatetime, EmpID int, Sales int)

insert into t1
values ('7/1/2006', 1, 20)

insert into t1
values ('7/1/2006', 2, 25)

insert into t1
values ('7/1/2006', 4, 10)

insert into t2
values ('7/1/2006', 1, 5)

insert into t2
values ('7/1/2006', 2, 7)

insert into t2
values ('7/1/2006', 3, 1)

what about this?

select coalesce(t1.date,t2.date) as date,coalesce(t1.empid,t2.empid) as empid,calls,sales
from t1 full outer join t2 on t1.empid =t2.empid

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Thanks for the response.

The problem I was running into is I need to include both EmpID and Date in the full outer join since the results need to be report only for a single date. So, when I exec this it does not return EmpID 3...

select coalesce(t1.date,t2.date) as date,coalesce(t1.empid,t2.empid) as empid,calls,sales
from t1 full outer join t2 on t1.empid =t2.empid and t1.date = t2.date

Any thoughts? TIA

|||

are you sure?

I get this back with both queries

2006-07-01 00:00:00 1 20 5
2006-07-01 00:00:00 2 25 7
2006-07-01 00:00:00 3 NULL 1
2006-07-01 00:00:00 4 10 NULL

|||

You're right!

I was doing this...

select coalesce(t1.date,t2.date) as date,coalesce(t1.empid,t2.empid) as empid,calls,sales
from t1 full outer join t2 on t1.empid =t2.empid and t1.date = t2.date where t1.date = '7/1/2006'

When I should have had the WHERE clause this...

select coalesce(t1.date,t2.date) as date,coalesce(t1.empid,t2.empid) as empid,calls,sales
from t1 full outer join t2 on t1.empid =t2.empid and t1.date = t2.date where t1.date = '7/1/2006' or t2.date = '7/1/2006'

That seems to work. Thanks!

I need some design help (time series analysis)

Hi,
Here is a fictious example that models my real life problem that I'm trying
to solve.
Lets say we have a bunch of baseball hitters and every day we give them an
hour to hit as many 'out of the park' home runs as they can hit. Now we want
to track home many they hit each day and watch whether their totals are
going up or down over time.
My fictious table design (as I see it) would be (with data)
PlayerId TotalHits Date
1 8 1/1/6
2 6 1/1/6
3 10 1/1/6
4 4 1/1/6
1 9 1/2/6
2 8 1/2/6
3 15 1/2/6
4 1 1/2/6
...
This may be poor design, please tell me if it is.
Anyway, from this design I could do a 'select playerid, sum(totalhits) from
xxx group by playerId' to see who has the most hits and look at each of the
top X hitters and see if they're in a incremental pattern or decreasing
pattern or just steady but high hits count type pattern.
Instead of that manual method I would like to figure a way to have the
system automatically tell me whose hit count is increasing over time and
whose are decreasing. and be able to break it down by week. month
quarter...
I'm guessing this might be analysis services in sql server, I've never used
them so I don't know. I run sql server 2000 btw.
If you have articles or references please point me in the right direction.
thanksHi,
the design looks ok, simple and straight forward, if you don=B4t want to
store more information here, you should be fine with that. Sure AS
could help you in some cases, but this should be just a simple query
here. Lets try to find a suggestion which will fit your needs. What do
you want to see at the end of the day, a list with playids only ? The
sums of their hits ? The difference between nother time period ? Which
time period do you want to investigate ? Try to give some sample
resultset that we can suggest something as an query for you.
HTH, Jens Suessmeyer
--
http://www.sqlserver2005.de
--|||"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1151300420.928006.36970@.m73g2000cwd.googlegroups.com...
Hi,
the design looks ok, simple and straight forward, if you don´t want to
store more information here, you should be fine with that. Sure AS
could help you in some cases, but this should be just a simple query
here. Lets try to find a suggestion which will fit your needs. What do
you want to see at the end of the day, a list with playids only ? The
sums of their hits ? The difference between nother time period ? Which
time period do you want to investigate ? Try to give some sample
resultset that we can suggest something as an query for you.
HTH, Jens Suessmeyer
--
http://www.sqlserver2005.de
--
Ultimately I would like to see which players hit counts are on the rise and
which are on the decline. And yes I would like to be able to adjust the time
frames to analyze. So to start out I would like to see the query which would
show me the player id's whose hit counts are on the rise over the entire
time period stored in the database.
I can sort of see how the query could group by week (or month) but I don't
see how it can show me counts which are on the rise.
Thanks for your help.