CODEWITHSUNDEEP
sql
Mark
Mark

Reputation: 621

SQL set operators

This is done in sql oracle using ONLY set operators, NOT IN, IN, NOT EXISTS, EXISTS, ANY, ALL.

The database is:

Department(DeptNum, Descrip, Instname, DeptName, State, Postcode)
Academic(AcNum, DeptNum*, FamName, GiveName, Initials, Title)
Paper(PaNum, Title)
Author(PaNum*, AcNum*)
Field(FieldNum, ID, Title)
Interest(FieldNum*, AcNum*, Descrip)

The question is:

Find the deptnum, descrip and instname of departments in queensland (QLD or Qld) where no one has authored a paper.

My current attempt is to just figure out the departments where no one has authored a paper at all no matter what state they are from

SELECT department.deptNum, department.state, department.instname
FROM department, academic
WHERE department.deptnum = academic.deptnum
  AND NOT EXISTS
    (SELECT * 
    FROM author 
    WHERE acnum = academic.acnum);

but apparently it should only return 1 tuple. I get 3 1 of which is QLD. If I try to remove the other 2 by adding

  AND department.state = 'QLD' OR department.state = 'Qld'

between the WHERE clause and AND NOT EXISTS, I get too many results.

Anyone able to guide me to a solution?

Upvotes: 0

Views: 265

Answers (3)

Mark
Mark

Reputation: 621

I have figured it out how to get the other department.

I removed the natural join and broke it all down piece by piece.

First I get the attributes I want to display from the department table. Then I filter out the results to only show QLD/Qld. Then I used a "NOT IN" which I used as my link to the academic table and then nested another "IN" set operator to link to the author table. I'll post the full solution when the due date is past.

but pretty much the only thing that helped me was to do it bit by bit. THe natural join really confused my thinking of it all.

Upvotes: 0

Kamil
Kamil

Reputation: 13931

This is wrong:

WHERE department.deptnum = academic.deptnum 
AND department.state = 'QLD' 
OR department.state = 'Qld'

because it returns: 1. data filtered by first AND second condition 2. data filtered by third condition without "department.deptnum = academic.deptnum" condition.

Think about brackets.

You should do it like this:

WHERE department.deptnum = academic.deptnum 
AND (department.state = 'QLD' OR department.state = 'Qld')

Or like this:

WHERE department.deptnum = academic.deptnum 
AND upper(department.state) = 'QLD'

UPPER('string') is Oracle/PLSQL function compatibile with 8i and newer versions and it changes all characters to uppercase.

Last option is the best, because data may contain 'Qld', 'qld' and 'QLD' (any combination of upper and lowercase).

For better look of data you may also use upper('string') here:

SELECT department.deptNum, upper(department.state), department.deptname

EDIT - answer for comment #1.

"NOT EXISTS" is bad idea for diffrent data rows.

Your main select has 3 columns: department.deptNum, department.state, department.instname Your select used to filter by author has 2 column.

Try this solution:

SELECT 
    department.deptNum, 
    department.state, 
    department.instname

FROM 
    department, 
    academic
WHERE 
    department.deptnum = academic.deptnum AND 
    upper(department.state) = 'QLD' AND
    (SELECT count(*) FROM author WHERE acnum = academic.acnum) = 0;

Upvotes: 1

Mat
Mat

Reputation: 206689

AND binds tighter than OR, so

A and B or C

is interpreted as 

(A and B) or C

not

A and (B or C)

So you'll need to put parenthesis. Or you could use in:

AND department.state IN ('QLD', 'Qld')

Upvotes: 1

Related Questions