SQL Join technique

Question

I'm beyond stuck with some SQL questions on my Bsc. CS revision exam paper - I know you all dislike answering academic questions but this is my last resort (and revision for Monday's exam) and I have no idea how to go about it.

I have 2 tables:

Department (deptId: string, deptName: string, managerId: string) 
Employee (empId: string, empName: string, jobName: string, salary: integer, deptId: string)

and 2 queries I need to run:

1) Display the name and the id of the department that has the largest number of employees

2) Display the names of the departments whose employees' average salary is at least 40000

I believe I need to use join and having here but those are things I can't quite wrap my big head around.

So my question is, how would I write these queries? I don't need an answer, per se, but an explanatory method to know how to achieve the objective. Again, this is not academic work but revision for a final exam.

First, how would you, for each department, find the number of employees ? — P. Camilleri, May 09 '14 at 15:05
In your question, "Department" and "Employee" are _tables_ , not databases. — joop, May 09 '14 at 15:06
They are indeed, my bad - I've been staring at this stuff all day :p To find the number of employees in each department I would run `select count(*) from employee group by deptid` but that doesn't get me the `deptname`, of course - which is a problem . — PsychoMantis, May 09 '14 at 15:10
@Timmy: could you also return deptid in the SELECT list of that query? And what if you also assigned an alias to the COUNT(*), could you wrap that query in parens and then run a SELECT against that like it were a table? — spencer7593, May 09 '14 at 15:15
sure - `select count(*), deptid from employee group by deptid` — PsychoMantis, May 09 '14 at 15:17
@Timmy wouldn't it be better if we moved this discussion to chat ? — P. Camilleri, May 09 '14 at 15:18
Definitely, I wasn't even aware of chat before now - thanks! — PsychoMantis, May 09 '14 at 15:19

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

1

Use Rank and just determine who is = 1. For simplicity, try creating a sub query: group by Department ID and get a count of employees.

2 - yes, you need having

Select
    d.deptid, d.deptName, Average(e.salary)
from Department as d
inner join Employee as e
on d.deptId = e.deptId
group by d.deptid, d.deptName
having Average(e.salary) >= 40000

edited Jun 20 '20 at 09:12

Community

1
1

answered May 09 '14 at 15:18

JeffO

7,957
3
44
53

score 1 · Accepted Answer · answered May 09 '14 at 15:21

The fisrt thing you must clear out is what and how the 2 tables are connected with?

If you deep in the table structure:

Department 
(deptId: string, deptName: string, managerId: string) 

Employee
 (empId: string, empName: string, jobName: string, salary: integer, deptId: string)

you can see that deptId is the common field thus the joining key for those tables. (Probably primary key for Department and foreign key for emmployee)

If indeed is a primary key then been a string is the best datatype selection.

Now you can move on on make the join query

I want give the exact solution for your problem I just giving you the syntax for a join statement:

Select * from Table1 join Table2 on Table1.samefield=Table2.samefield  where='condition'

P. Camilleri · Answer 3 · 2014-05-09T15:39:03.257

First of all, you want to have a table combining infos from both Department and Employee. This is the classical usecase for a INNER JOIN between these tables.

Which columns are you going to select from these tables (e.g. do you really need manager id ?) ?
What's going to be your ON clause ?

At this point, you get a table with all employees, and their respective departments.

After that, you can use this table and group by, count(), order by and select top 1 for your first question / group by, having and avg() for your second.

Bonus : Now you may wonder, what happens if two departments have same number of employees ?

score 0 · Answer 4 · answered May 09 '14 at 15:19

Start by Joining your two tables.

Look at the result of that and see what you need to add. For the first one think of ordering by something maybe couple that with selecting a certain amount of results. For the second one you are going to have to add something to that join. Perhaps a subquery to aggregate etc.

SQL is a beautiful language for trial and error. It is fast and gives you immediate results. Try SQL Fiddle if you don't have an environment.

score 0 · Answer 5 · answered May 09 '14 at 15:20

/*, managerId VARCHAR(100)*/


DECLARE @Department TABLE (deptId VARCHAR(100), deptName VARCHAR(100)) 
DECLARE @Employee TABLE (empId VARCHAR(100), empName VARCHAR(100), jobName VARCHAR(100), salary INT, deptId  VARCHAR(100)) 

INSERT INTO @Department (deptId, deptName) VALUES ('IT','Tech')
INSERT INTO @Department (deptId, deptName) VALUES ('ST','Stores')

INSERT INTO @Employee (empId, empName, salary, deptId) VALUES ('A','AAA',10000,'IT')
INSERT INTO @Employee (empId, empName, salary, deptId) VALUES ('B','AAA',20000,'IT')
INSERT INTO @Employee (empId, empName, salary, deptId) VALUES ('C','AAA',30000,'ST')
INSERT INTO @Employee (empId, empName, salary, deptId) VALUES ('D','AAA',220000,'ST')
INSERT INTO @Employee (empId, empName, salary, deptId) VALUES ('E','AAA',12000,'ST')


/*Department with the largest number of employees*/
SELECT  
    TOP 1 DP.deptId, DP.deptName, COUNT(EE.deptId) AS NoEmployees
FROM    
    @Department AS DP 

    INNER JOIN @Employee AS EE
    ON DP.deptId = EE.deptId

GROUP BY
    DP.deptId , DP.deptName
ORDER BY
    COUNT(EE.deptId) DESC

/*Department with the largest number of employees*/
SELECT  
    DP.deptId, DP.deptName, AVG(EE.salary) AS NoEmployees
FROM    
    @Department AS DP 
    INNER JOIN @Employee AS EE
    ON DP.deptId = EE.deptId

GROUP BY
    DP.deptId , DP.deptName
HAVING
     AVG(EE.salary)>40000
ORDER BY
    COUNT(EE.deptId) DESC

Very few explainations, and OP didn't ask for an explicit query, but rather for indications — P. Camilleri, May 09 '14 at 15:21
Quite clearly, the answer is very well commented and does clearly show what each section does. At BSc level, understanding of the above code should be considered a minimum. If one scrawls code, then I get it. The above is acceptable. — Anthony Horne, May 09 '14 at 15:26
Agreed, but doesn't OP explicitly state "I don't need an answer, per se, but an explanatory method to know how to achieve the objective." ? — P. Camilleri, May 09 '14 at 15:30

SQL Join technique

5 Answers5

1

2 - yes, you need having