siddpro siddpro - 1 year ago 51
SQL Question

finding the names that contains letters 'psy' and setting default values in outer join in postgresql

i am stuck with the two problems.

1 - how to find or extract all the departments that contains letters 'psy'

so far i used this

select dept_name
from department
where dept_name like '%P%';

which gives me result - 'Physics' which is not correct.

and my department list is like this

"Comp. Sci."
"Elec. Eng."

2 - my second question is

I wrote a query that gives me instructor name, instructor id from instructor table and section id from teaches table. I used outer join to get the required result but i left with few blank spaces where i want to put the default value '0'
How can i do that?

my query

from instructor
full outer join teaches
on =;

from 'kim' rest of the rows has not secid where i want to put 0
my result

id name secid
"76766";"Crick"; "1"
"76766";"Crick"; "1"
"83821";"Brandt"; "1"
"83821";"Brandt"; "2"
"83821";"Brandt"; 2"
"98345";"Kim"; "1"
76543";"Singh"; ""

instructor table

id name department
"10101"; "Srinivasan";"Comp. Sci."
"12121"; "Wu"; "Finance"
"15151" ;"Mozart"; "Music"
"22222"; "Einstein"; "Physics"

takes table

id subject section
"00128"; "CS-101"; "1"
"00128"; "CS-347"; "1"
"12345"; "CS-101"; "1"
"12345"; "CS-190"; "2"
"12345"; "CS-315"; "1"
"12345"; "CS-347"; "1"

since data is too large i couldn't put whole table here but its enough to give idea.

Answer Source
  1. Use '%Psy%' in your like instead of just '%P%'. If you want just those that start with Psy, then you want 'Psy%'

  2. Use COALESCE : COALESCE(teaches.sec_id, 0) AS sec_id in your SELECT statement.