【SQLZOO学习打卡】Tutorial：Section D

Part 8. Using NULL

Selecting null values

Sometimes NULL values are given in tables, this might be because the data is unknown or is inappropriate. We can use the phrase IS NULL to pick out fields. We can use IS NOT NULL similarly. 

@ Teacher, dept  table:

1. NULL 

select name from teacher
where dept is null

2. INNER JOIN: list all values 

select teacher.name, dept.name from 
teacher inner join dept on teacher.dept=dept.id

3. LEFT JOIN: list letf table values 

select teacher.name, dept.name from
teacher left join dept on teacher.dept=dept.id

4. RIGHT JOIN: list right table values

select teacher.name, dept.name from
teacher right join dept on teacher.dept=dept.id

select dept.name, count(teacher.name) from
teacher right join dept on teacher.dept=dept.id
group by dept.name

5. COALESCE: takes any number of arguments and returns the first value that is not null

COALESCE(x,y,z) = x if x is not NULL
COALESCE(x,y,z) = y if x is NULL and y is not NULL
COALESCE(x,y,z) = z if x and y are NULL but z is not NULL
COALESCE(x,y,z) = NULL if x and y and z are all NULL
COALESCE can be useful when you want to replace a NULL value with some other value

select name, coalesce(mobile,'07986 444 2266') from teacher

select teacher.name, coalesce(dept.name,'None') from
teacher left join dept on teacher.dept=dept.id

6. CASE: return different values under different conditions, If there no conditions match (and there is not ELSE) then NULL is returned

CASE WHEN condition1 THEN value1 
       WHEN condition2 THEN value2  
       ELSE def_value 
  END 

select name, 
case when(dept=1 or dept=2) then 'Sci'
else 'Art'
end
from teacher

select name, 
case when dept in(1,2) then 'Sci'
when dept=3 then 'Art'
else 'None'
end
from teacher

 7. NVL: takes 2 arguments and returns the first value that is not null

NVL(x,y) = x if x is not NULL
   NVL(x,y) = y if x is NULL
NVL can be useful when you want to replace a NULL value with some other value.

SELECT name, party
      ,COALESCE(party,'None') AS aff
  FROM msp WHERE name LIKE 'C%'

Part 9. SELF JOIN 

 @ stops(id, name) ; @route(num, company, pos, stop)

select count(name) from stops

select stops.id, stops.name from 
stops join route on stops.id=route.stop
where route.num='4' and route.company= 'LRT'

SELECT company, num, COUNT(*) FROM route 
WHERE stop=149 OR stop=53
GROUP BY company, num
having count(*)=2

Execute the self join shown and observe that b.stop gives all the places you can get to from Craiglockhart, without changing routes. 

1. Using a SELF JOIN

A table may be joined with itself. When this happens we need some mechanism for distinguishing the instances of the table. Labels may be introduced in the FROM clause - a dot is used to separate the label and attribute name when used elsewhere in the statement.

• self join of route on (num, company) 

         SELECT * FROM route R1, route R2
                    WHERE R1.num=R2.num AND R1.company=R2.company

• self join of route on stop

        SELECT * FROM route R1, route R2

                   WHERE R1.stop=R2.stop

 2. Single Self JOIN

select a.company, a.num, a.stop, b.stop from 
route a join route b on (a.company=b.company and a.num=b.num)
where a.stop=53

select a.company, a.num, a.stop, b.stop from 
route a join route b on (a.company=b.company and a.num=b.num)
where a.stop=53 and b.stop=149

3. Multiple Self JOIN

select a.company, a.num, stopa.name, stopb.name from
route a JOIN route b ON
  (a.company=b.company and a.num=b.num)
  join stops stopa on (a.stop=stopa.id)
  join stops stopb on (b.stop=stopb.id)
where stopa.name='Craiglockhart'

select a.company, a.num, stopa.name, stopb.name from
route a JOIN route b ON
  (a.company=b.company and a.num=b.num)
  join stops stopa on (a.stop=stopa.id)
  join stops stopb on (b.stop=stopb.id)
where stopa.name='Craiglockhart' and stopb.name='London Road' 

select distinct a.company, a.num from
route a join route b on (a.company=b.company and a.num = b.num)
where a.stop=115 and b.stop=137

select a.company, a.num from
route a JOIN route b ON
  (a.company=b.company and a.num=b.num)
  join stops stopa on (a.stop=stopa.id)
  join stops stopb on (b.stop=stopb.id)
where stopa.name= 'Craiglockhart'  and stopb.name='Tollcross'

select y.name, a.company, a.num from 
route a join route b on (a.company=b.company and a.num=b.num)
join stops x on x.id=a.stop 
join stops y on y.id=b.stop 
where x.name='Craiglockhart'

! 可以绕晕一天的题目！​​​​​​​

select v1.r1num, v1.r1com, v1.trans1, v2.r4num, v2.r4com
from(
	select s1.name as begin, r1.num as r1num, r1.company as r1com, s2.name as trans1
	from route r1 join route r2 on r1.company = r2.company and r1.num = r2.num
	join stops s1 on s1.id = r1.stop
	join stops s2 on s2.id = r2.stop
	where s1.name = 'Craiglockhart'
) as v1

join(
	select s3.name as trans2, r4.num as r4num, r4.company as r4com, s4.name as final
	from route r3 join route r4 on r3.company = r4.company and r3.num = r4.num
	join stops s3 on s3.id = r3.stop
	join stops s4 on s4.id = r4.stop
	where s4.name = 'Lochend'
) as v2

on v1.trans1 = v2.trans2
order by r1num, trans1, r4num