SQLZOO刷题记录-3

列出1962年首影的電影， [顯示 id, title]。

SELECT  id,title
 FROM movie
 WHERE yr=1962；

電影大國民 ‘Citizen Kane’ 的首影年份。

select yr from movie
  where title='Citizen Kane';

列出全部Star Trek星空奇遇記系列的電影，包括id, title 和 yr(此系統電影都以Star Trek為電影名稱的開首)。按年份順序排列。

select id,title,yr from movie
  where title like 'Star Trek%';

id是 11768, 11955, 21191 的電影是什麼名稱?

select title from movie
  where id in ('11768','11955','21191');

女演員’Glenn Close’的編號 id是什麼?

select id from actor 
  where name = 'Glenn Close';

電影北非諜影’Casablanca’ 的編號 id是什麼?

select id from movie
  where title='Casablanca';

列出電影北非諜影 'Casablanca’的演員名單。使用 movieid=11768, 這是你上一題得到的結果。

select name from movie,actor,casting
  where actor.id=casting.actorid and movie.id=casting.movieid and title='Casablanca';

顯示電影異型’Alien’ 的演員清單。

select name from actor,casting
  where actor.id=casting.actorid and movieid = (select id from movie where title = 'Alien');

列出演員夏里遜福 ‘Harrison Ford’ 曾演出的電影。

select title from movie,actor,casting
  where movie.id=casting.movieid and actor.id=casting.actorid and actor.name='Harrison Ford';

列出演員夏里遜福 ‘Harrison Ford’ 曾演出的電影，但他不是第1主角。

select movie.title from casting,actor,movie
  where casting.actorid=actor.id and movie.id=casting.movieid and actor.name='Harrison Ford' and ord!=1;

列出1962年首影的電影及它的第1主角。

select movie.title,actor.name from movie,actor,casting
  where movie.yr=1962 and movie.id=casting.movieid and casting.ord=1 and casting.actorid=actor.id;

列出演員茱莉·安德絲’Julie Andrews’曾參與的電影名稱及其第1主角。她於1980再參與此電影Little Miss Marker. 原作於1934年,她也有參與。電影名稱不是獨一的。在子查詢中使用電影編號。

select title,name from (casting join actor on casting.actorid=actor.id)
                                join movie on movie.id=casting.movieid
  where movieid in(select movieid from casting join actor on casting.actorid=actor.id
                     where name='Julie Andrews') and ord=1;

列出按字母順序，列出哪一演員曾作30次第1主角。

select name,count(ord) from actor join casting on actor.id = casting.actorid
  where ord=1
group by name
having count(ord)=30;

列出1978年首影的電影名稱及角色數目，按此數目由多至少排列。

select title,count(ord) from movie join casting on casting.movieid=movie.id
  where yr=1978
group by title
order by count(ord) desc;

列出曾與演員亞特·葛芬柯’Art Garfunkel’合作過的演員姓名。

select distinct name from (casting join actor on actor.id=casting.actorid)
                                   join movie on movie.id=casting.movieid
  where movieid in (select movieid from casting join actor on actor.id=casting.actorid
  where name='Art Garfunkel') and name!='Art Garfunkel';

列出學系department是NULL值的老師。

select name from teacher
  where dept is NULL;

使用不同的JOIN(外連接)，來列出全部老師。

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

使用不同的JOIN(外連接)，來列出全部老師。

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

使用不同的JOIN(外連接)，來列出全部學系。

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

Use COALESCE to print the mobile number. Use the number ‘07986 444 2266’ if there is no number given. Show teacher name and mobile number or ‘07986 444 2266’.

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

Use the COALESCE function and a LEFT JOIN to print the teacher name and department name. Use the string ‘None’ where there is no department.

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

使用COUNT來數算老師和流動電話數目。

select count(name),count(mobile) from teacher;

使用COUNT 和 GROUP BY 來顯示每一學系的老師數目。使用 RIGHT JOIN 以確保工程系Engineering 是在當中。

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

Use CASE to show the name of each teacher followed by ‘Sci’ if the teacher is in dept 1 or 2 and ‘Art’ otherwise.

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

Use CASE to show the name of each teacher followed by ‘Sci’ if the teacher is in dept 1 or 2, show ‘Art’ if the teacher’s dept is 3 and ‘None’ otherwise.

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

How many stops are in the database.

select count(id) from stops;

Find the id value for the stop ‘Craiglockhart’.

SELECT id FROM stops 
  WHERE name = 'Craiglockhart';

Give the id and the name for the stops on the ‘4’ ‘LRT’ service.

SELECT id, name FROM stops JOIN route on id = stop 
  WHERE num = '4' AND company = 'LRT';

The query shown gives the number of routes that visit either London Road (149) or Craiglockhart (53). Run the query and notice the two services that link these stops have a count of 2. Add a HAVING clause to restrict the output to these two routes.

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 gives all the places you can get to from Craiglockhart, without changing routes. Change the query so that it shows the services from Craiglockhart to London Road.

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

The query shown is similar to the previous one, however by joining two copies of the stops table we can refer to stops by name rather than by number. Change the query so that the services between ‘Craiglockhart’ and ‘London Road’ are shown. If you are tired of these places try ‘Fairmilehead’ against ‘Tollcross’.

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';

Give a list of all the services which connect stops 115 and 137 (‘Haymarket’ and ‘Leith’).

SELECT distinct 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='Haymarket' and stopb.name='Leith';

Give a list of the services which connect the stops ‘Craiglockhart’ and ‘Tollcross’.

SELECT distinct 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';

Give a distinct list of the stops which may be reached from ‘Craiglockhart’ by taking one bus, including ‘Craiglockhart’ itself, offered by the LRT company. Include the company and bus no. of the relevant services.

select name,company,num from stops join route on id = stop 
  where company='LRT' and (name='Craiglockhart' or num in (select num from stops join route on id = stop 
  where company='LRT' and name='Craiglockhart'));

Find the routes involving two buses that can go from Craiglockhart to Lochend.
Show the bus no. and company for the first bus, the name of the stop for the transfer,
and the bus no. and company for the second bus.

SELECT DISTINCT x.num,x.company,name,y.num,y.company
FROM(SELECT a.num,a.company,b.stop
     FROM route a JOIN route b
     ON (a.num = b.num AND a.company = b.company)
     AND a.stop != b.stop
     WHERE a.stop = (SELECT id FROM stops WHERE name ='Craiglockhart')) AS x
JOIN(SELECT c.num,c.company,c.stop
     FROM route c JOIN route d 
     ON (c.num = d.num and c.company = d.company)
     AND c.stop != d.stop
     WHERE d.stop =(SELECT id FROM stops WHERE name = 'Lochend'))AS y
ON x.stop = y.stop
JOIN stops ON x.stop = stops.id
ORDER BY x.num,stops.name,y.num

秒客网

SQLZOO刷题记录-3

相关文章