siddpro siddpro - 4 months ago 11
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

"Biology"
"Comp. Sci."
"Elec. Eng."
"Finance"
"History"
"Music"
"Physics"


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

select instructor.id,instructor.name,teaches.sec_id
from instructor
full outer join teaches
on instructor.id = teaches.id;


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"
"33456";"Gold";
"58583";"Califieri";""
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
  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.

Comments