questions for a sql test

Question

Post reply

questions for a sql test

jjradha

SSChasing Mays

Points: 617
More actions
May 1, 2008 at 5:09 am

#189969

/*===================================*\
SQL Server Test 01
Marcelo Miorelli 01/MAY/2008
\*===================================*/
create table emp(
empid int,
empname nvarchar(50),
sal money,
deptid int)
create table dept(
deptid int,
deptname nvarchar(50)
)
insert into emp values (1,'mark',10,1)
insert into emp values (2,'john',15,2)
insert into emp values (3,'anna',8,1)
insert into emp values (4,'emma',9,1)
insert into emp values (4,'jo',9,null)
insert into dept values (1,'dept 1')
insert into dept values (2,'dept 2')
insert into dept values (3,'dept 3')
/*=======================================*\
if the employee dept is null write no dept
\*=======================================*/
select e.empname,isnull(d.deptname,'no dept')
from emp e
left join dept d on e.deptid = d.deptid
/*=========================================*\
employees whose salary is higher than the averge of them dept
\*=========================================*/
select e.empname, e.sal,e.deptid
from emp e
where e.sal > (select avg(sal)
from emp z
where z.deptid = e.deptid)
select avg(sal)
from emp z
where z.deptid =1
/*==========================================*\
depts that have no employees in
\*==========================================*/
SELECT d.deptname
from dept d
where not exists ( select * from emp e where e.deptid = d.deptid)

Viewing 3 posts - 1 through 2 (of 2 total)

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

RyanRandall SSChampion Points: 13623 More actions · Answer 1

Looks good to me, marcelo.

One typo - jo's empid should be 5 rather than 4

All the solutions are how I would (and did :)) do it, depending on the exact requirements. For example, I might write the 2nd request (something) like this since this returns the the average as a bonus (and has the same query plan)...

select * from (select *, (select avg(sal) from emp where deptid = a.deptid) avg from emp a) a where sal > avg

Good one! 🙂

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 2

Although the last problem is correct, I can tell you that whoever wrote the test is probably looking for the outer join solution instead of the where not exists solution. If it were me, I'd write the NOT EXISTS, NOT IN, and OUTER JOIN solutions and explain the advantages and disadvantages of each along with a bit of performance testing and some discussion on resource usage.

Yep... I know... not required... but going the extra mile is gonna impress them.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)