please help with SQL interview question

Question

please help with SQL interview question

caojunhe24

Ten Centuries

Points: 1334
More actions
March 27, 2017 at 8:13 am

#319921

I had an interview on last Friday and messed up. Can you guys help me to answer these two questions. There was no computer at that time all I did was writing the code on the paper so might have some syntax errors.
1) using SINGLE delete statement to remove all duplicate records from the table STATE (pick max date)
code state date
1 'VA' 2012 --THIS SHOULD BE REMOVED
1 'VA' 2017
2 'MD' 2012
3 'CA' 2014
My code was :
DELETE
FROM STATE
WHERE Date NOT IN(SELECT MAX(Date) FROM STATE GROUP BY state)
2) display two more columns in the SINGLE SELECT statment USING table Emp ( Company_AVG &Department_AVG)
Emp_Name Salary Dept --(SHOULD DISPLAY TWO more columns Company_AVG Department_AVG)
'John' 10000 'BA'
'Mike' 15000 'IT'
'Jully' 18000 'IT'
'Ken' 12000 'HR'
My code was
select a.*,
sum(salary)/count(name) as com_avg,
b.Dept_avg
from emp a
join (select dept ,sum(salary)/count(name) as Dept_avg from emp group by dept)b
on a.dept = b.dept
group by a.name,a.salary,a.dept

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

(1) Use a ROW_NUMBER() clause to number the rows so that the row you wish to keep (or wish to delete) in each partition is numbered 1. Then write your DELETE statement accordingly, so that you delete either the rows with RowNo 1, or with RowNo greater than 1.

(2) To get the department average, use an OVER clause with your AVG function, partitioning by Dept. You shouldn't need a JOIN in your query.

John

caojunhe24 Ten Centuries Points: 1334 More actions · Answer 2

thanks john,

1) first question is resolved
delete e
from(
select *,seq =ROW_NUMBER() over(partition by code,state order by date desc) from #state
) e
where seq >1

but for the second one i still dont get it. can you please post your code here? we need department avg and the whole company avg in the single select statemnt.

caojunhe24 Ten Centuries Points: 1334 More actions · Answer 3

for the second one
-

select a.*,
(select avg(salary) from emp)as com_avg,
b.Dept_avg
from emp a
join (select dept ,avg(salary) as Dept_avg from emp group by dept)b
on a.dept = b.dept
group by a.name,a.salary,a.dept ,b.Dept_avg

i dont know how can i use over function here instead of join anyone have idea?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Yes, I understand what the requirement is. You asked for help to answer the questions. I'm not answering them for you - that would defeat the purpose of their existence.

For the second question, use AVG instead of dividing SUM by COUNT. For the Dept_avg column, use a PARTITION BY clause exactly the same way as you did for ROW_NUMBER. For com_avg, do the same thing, but you need to do a little trick with the argument of PARTITION BY. Since you can't use a constant (eg PARTITION BY 1), you need to use an expression that evaluates to a constant, such as PARTITION BY (SELECT NULL). Try that out, and post what you've tried if there's anything in particular you don't understand.

Edit - forget about the company average for now - just get the department average working. Once you've worked out how to use the OVER clause with AVG in exactly the same way as you did with ROW_NUMBER, you'll find it easy to add another line to your query for the company average, using the trick I mentioned above.

John

caojunhe24 Ten Centuries Points: 1334 More actions · Answer 5

Thanks John, it works and it's more easier!! You are awesome

select a.*,
avg(salary) over(partition by dept) as dept_avg,
avg(salary) over(partition by null) as com_avg
from emp a

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Yes, well done - I imagine that's what they were looking for. And I even learned something myself - I didn't realise you could use NULL instead of (SELECT NULL).

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

caojunhe24 - Monday, March 27, 2017 9:23 AM
Thanks John, it works and it's more easier!! You are awesome
select a.*,
avg(salary) over(partition by dept) as dept_avg,
avg(salary) over(partition by null) as com_avg
from emp a

You could use OVER() instead of OVER( PARTITION BY NULL).

John Mitchell-245523 - Monday, March 27, 2017 9:34 AM
Yes, well done - I imagine that's what they were looking for. And I even learned something myself - I didn't realise you could use NULL instead of (SELECT NULL).

You can use it when defining partitions but not when defining the order. Maybe that's the origin of your confusion.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

drew.allen SSC Guru Points: 76996 More actions · Answer 8

caojunhe24 - Monday, March 27, 2017 9:23 AM
Thanks John, it works and it's more easier!! You are awesome
select a.*,
avg(salary) over(partition by dept) as dept_avg,
avg(salary) over(partition by null) as com_avg
from emp a

I'm not sure about SQL2008, but in SQL2012 you do not need a PARTITION BY clause, but you do still need the OVER clause.

select a.*, avg(salary) over(partition by dept) as dept_avg, avg(salary) over() as com_avg from emp a

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA