Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2011
    Posts
    8

    Unanswered: Some query questions

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2011
    Posts
    8
    Quote Originally Posted by r937 View Post
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Vancatu View Post
    SELECT d.name
    FROM department as d JOIN employee as e
    ON d.depnr=e.enr
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2011
    Posts
    8
    Quote Originally Posted by r937 View Post
    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

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I dunno
    why don't you try it yourself and see if it works
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Oct 2011
    Posts
    8
    Quote Originally Posted by healdem View Post
    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
    Last edited by Vancatu; 10-06-11 at 12:36.

  8. #8
    Join Date
    Oct 2011
    Posts
    8
    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.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Vancatu View Post
    SELECT dep.name
    FROM department d left outer join employee e
    ON depnr=dep
    WHERE dep is null
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Oct 2011
    Posts
    8
    Quote Originally Posted by r937 View Post
    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

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Vancatu View Post
    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...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Oct 2011
    Posts
    8
    Quote Originally Posted by r937 View Post
    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?

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Vancatu View Post
    For instance, do I need to use registration, employee and course?
    no, just two of those
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Oct 2011
    Posts
    8
    Quote Originally Posted by r937 View Post
    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?

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Vancatu View Post
    It doesn't work but am I in the right direction?
    no, you aren't

    wrong tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •