Hello fellows,

I'm new into SQL query's and I'm currently performing some query exercises and I can need some help with it.

I'm using an ERD in MYSQL Workbench.

Now I want to know via a guery which departments don't have employees.

I also want to know which courses are plannend for which no one is registered. I want to display the code of that particular course.

Furthermore I want to know which emplyees don't act as teachers. I want to display the name of those employees.

This is the ERD:

The relations are head INT (head of department) with enr INT (employee number), head INT with dep INT (department), enr INT with student INT, chief INT with dep INT, enr INT with teacher INT, code with course, and course and startdate with course and start date.

http://i1097.photobucket.com/albums/...ncatu/eer2.jpg

sorry, we will not do your homework assignment for you

please try the queries yourself, and post only specific questions that you might have about a query (and don't forget to post your query attempt too)

Most of the questions I did myself. These are the ones I'm stuck with.

For the first one I tried this query:

SELECT d.name
FROM department as d JOIN employee as e
ON d.depnr=e.enr

I think I need to specify a WHERE but I'm not sure how.

this query will return departments which ~do~ have employees

for departments which don't have employees, you'll need a LEFT OUTER JOIN with an IS NULL check, or else use a NOT EXISTS subquery

Something like this?

SELECT d.name
FROM department as a left outer join employee as e
ON d.depnr=e.enr
WHERE d.depnr is null

I dunno
why don't you try it yourself and see if it works

It doesn't work, it says 0 rows are selected. But that figures I guess. Sorry, I'm a newbie when it comes to SQL

I've got it.

SELECT dep.name
FROM department d left outer join employee e
ON depnr=dep
WHERE dep is null

Thanks for the help guys.

ignoring the fact that there is no table called "dep" in this query (you probably meant d.name)...

you qualified only one column with its table name -- dep.name (or d.name) -- and this is likely because "name" is a column name that is common to both tables (not qualifying it gives an "ambiguous column" error)

the other columns mentioned in the query -- depnr and dep -- are unqualified

here's a tip that will save you cumulative weeks and months of time --

in any query involving more than one table, qualify ~all~ column references in the query with their table names (or table aliases)

you will thank me later

Yes I meanth d.name. Actually, I'm from the Netherlands and use the original ERD in dutch words. I translated it in english and made a screenshot so you guys would understand.

With qualifying all column names I guess you mean this?:

ON d.depnr=e.dep
WHERE e.dep is null

yes, just like that

when you have to debug a query, when it stops working, even if you wrote it knowing which columns are in which tables, if you qualify the columns when you write the query then you don't have to try to figure out the table design first before understanding what ON depnr=dep WHERE dep is null is supposed to be doing...

Okay, Ill keep that in mind. I've adjusted the res of the query's on you're advice.

I've also done the third question as that was almost the same as the first.

But I'm still stuck with the second one:

Which courses are plannend for which no one is registered. I want to display the code of that particular course.

Any tips?

For instance, do I need to use registration, employee and course?

no, just two of those

SELECT r.course
FROM registration r left outer join employee e
ON r.course=r.start date
WHERE r.student is null

It doesn't work but am I in the right direction?

no, you aren't

wrong tables