13. More JOIN operations
1.List the films where the yr is 1962 [Show id, title]
select id,title from movie where yr = '1962';
2.Give year of 'Citizen Kane'.
select yr from movie where title = 'Citizen Kane';
3. List all of the Star Trek movies, include the id, title and yr (all of these movies include the words Star Trek in the title). Order results by year
select id,title,yr from movie where title like '%Star Trek%' order by yr;
4.What id number does the actor 'Glenn Close' have?
select id from actor where name = 'Glenn Close';
5.What is the id of the film 'Casablanca'
select id from movie where title = 'Casablanca';
6.Obtain the cast list for 'Casablanca'.
(该题存疑，参照知乎上的答案无法实现)
select name from actor JOIN casting on id = actorid where movieid = 11768;
7.Obtain the cast list for the film 'Alien'
select name from casting join actor on actor.id = actorid join movie on movie.id = movieid where title = 'Alien';
输出的结果为：
Correct answer
name
Sigourney Weaver
Ian Holm
Harry Dean Stanton
Tom Skerritt
John Hurt
Veronica Cartwright
Yaphet Kotto
该题的难点在于对多张表的合并处理.
8.List the films in which 'Harrison Ford' has appeared
select title from casting join actor on actor.id = actorid join movie on movie.id = movieid where name = 'Harrison Ford';
9.List the films where 'Harrison Ford' has appeared - but not in the starring role. [Note: the ord field of casting gives the position of the actor. If ord=1 then this actor is in the starring role]
select title from casting join actor on actor.id = actorid join movie on movie.id = movieid where name = 'Harrison Ford' and ord != 1;
10.List the films together with the leading star for all 1962 films.
select title,name
from casting
join actor on actor.id = actorid
join movie on movie.id = movieid
where yr = 1962
and ord = 1;
上述7,8,9,10的题目主要考察的是将数据表进行合并，然后进行数据筛选。
11.Which were the busiest years for 'Rock Hudson', show the year and the number of movies he made each year for any year in which he made more than 2 movies.
12.List the film title and the leading actor for all of the films 'Julie Andrews' played in.
SELECT title, name
FROM movie JOIN casting ON movie.id = movieid
JOIN actor ON actorid = actor.id
WHERE movieid IN (SELECT movieid
FROM movie JOIN casting ON movie.id = movieid
JOIN actor ON actorid = actor.id
WHERE name = 'Julie Andrews')
AND ord = 1;
解释的网址> www.youtube.com/embed/BcNIDK5qYx8?
13.Obtain a list, in alphabetical order, of actors who've had at least 15 starring roles.
select name
from movie join casting on movie.id = movieid
join actor on actorid = actor.id
where ord = 1
group by name
having count(title) >= 15
order by name;
14.List the films released in the year 1978 ordered by the number of actors in the cast, then by title.
SELECT title, COUNT(name) actors_number
FROM movie JOIN casting ON movie.id = movieid
JOIN actor ON actorid = actor.id
WHERE yr = 1978
GROUP BY title
ORDER BY COUNT(name) DESC, title;
15.List all the people who have worked with 'Art Garfunkel'.
SELECT name
FROM movie JOIN casting ON movie.id = movieid
JOIN actor ON actorid = actor.id
WHERE movieid IN (SELECT movieid
FROM movie JOIN casting ON movie.id = movieid
JOIN actor ON actorid = actor.id
WHERE name = 'Art Garfunkel')
AND name != 'Art Garfunkel';
14.JOIN Quiz 2
1. Select the statement which lists the unfortunate directors of the movies which have caused financial loses (gross < budget)
select name
from actor inner join movie on actor.id = director
where gross <budget
2. Select the correct example of JOINing three tables
select * from actor join casting on actor.id = actorid join movie on movie.id = movieid
### 3. Select the statement that shows the list of actors called 'John' by order of number of movies in which they acted
```sql
select name,count(movieid)
from casting join actor
where name like 'John %'
group by name order by count(movieid) desc;
正确的写法:
select name,count(movieid)
from casting join actor on actorid = actor.id
where name like 'John %'
group by name order by 2 desc;
4. Select the result that would be obtained from the following code:
SELECT title
FROM movie JOIN casting ON (movieid=movie.id)
JOIN actor ON (actorid=actor.id)
WHERE name='Paul Hogan' AND ord = 1
输出的结果为:
Table-B
"Crocodile" Dundee
Crocodile Dundee in Los Angeles
Flipper
Lightning Jack
5. Select the statement that lists all the actors that starred in movies directed by Ridley Scott who has id 351
select name
from movie join casting on movie.id = movieid
join actor on actor.id = actorid
where ord = 1 and director = 351
director = 351
直接对应题目中导演编号是351
6. There are two sensible ways to connect movie and actor. They are:
link the director column in movies with the primary key in actor
connect the primary keys of movie and actor via the casting table
7.Select the result that would be obtained from the following code:
SELECT title, yr
FROM movie, casting, actor
WHERE name='Robert De Niro' AND movieid=movie.id AND actorid=actor.id AND ord = 3
输出结果为:
Table-B
A Bronx Tale 1993
Bang the Drum Slowly 1973
Limitless 2011
15.Using Null
teacher
id dept name phone mobile
101 1 Shrivell 2753 07986 555 1234
102 1 Throd 2754 07122 555 1920
103 1 Splint 2293
104 Spiregrain 3287
105 2 Cutflower 3212 07996 555 6574
106 Deadyawn 3345
...
**dept**
```sql
id name
1 Computing
2 Design
3 Engineering
...
### 1.List the teachers who have NULL for their department.
```sql
select name from teacher where dept is null
该题需要注意的是空值的写法，比如dept is null
; 而不是 dept = null
2.Note the INNER JOIN misses the teachers with no department and the departments with no teacher.
select teacher.name,dept.name from teacher join dept on teacher.dept = dept.id;
输出的结果为:
Correct answer
name name
Shrivell Computing
Throd Computing
Splint Computing
Cutflower Design
3.Use a different JOIN so that all teachers are listed.
select teacher.name,dept.name from teacher left join on teacher.dept = dept.id;
4.Use a different JOIN so that all departments are listed.
SELECT teacher.name,dept.name
FROM teacher
RIGHT JOIN dept ON teacher.dept = dept.id;
5.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'
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
select teacher.name,
coalsece(mobile,'07986 444 2266')
from teacher;
6.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 teacher.dept = dept.id;
输出结果为:
Correct answer
name
Shrivell Computing
Throd Computing
Splint Computing
Spiregrain None
Cutflower Design
Deadyawn None
7.Use COUNT to show the number of teachers and the number of mobile phones.
select count(name),count(mobile) from teacher;
8.Use COUNT and GROUP BY dept.name to show each department and the number of staff. Use a RIGHT JOIN to ensure that the Engineering department is listed.
select dept.name,count(teacher.name)
from teacher
right join dept on teacher.dept = dept.id
groupy by dept.name;
本题是考查根据部门查看每一个部门有多少老师。
9.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 as dept
from teacher;
输出的结果为:
Correct answer
name dept
Shrivell Sci
Throd Sci
Splint Sci
Spiregrain Art
Cutflower Sci
Deadyawn Art
Case when
的题目比较多，需要集中做一下总结
10. 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'
when dept = 3 then 'Art'
else 'None' end as dept
from teacher;
输出结果为:
Correct answer
name dept
Shrivell Sci
Throd Sci
Splint Sci
Spiregrain None
Cutflower Sci
Deadyawn None
16.Using Null Quiz
1.Select the code which uses an outer join correctly.
select teacher.name,dept.name from teacher left outer join dept on (teacher.dept = dept.id)
2. Select the correct statement that shows the name of department which employs Cutflower -
select dept.name
from teacher
join dept on (dept.id = teacher.id)
where teacher.name = 'Cutflower -'
3. Select out of following the code which uses a JOIN to show a list of all the departments and number of employed teachers
SELECT dept.name, COUNT(teacher.name) FROM teacher RIGHT JOIN dept ON dept.id = teacher.dept GROUP BY dept.name
4.Using SELECT name, dept, COALESCE(dept, 0) AS result FROM teacher on teacher table will:
display 0 in result column for all teachers without department
5.Query:
SELECT name,
CASE WHEN phone = 2752 THEN 'two'
WHEN phone = 2753 THEN 'three'
WHEN phone = 2754 THEN 'four'
END AS digit
FROM teacher
shows following 'digit':
'four' for Throd
6.Select the result that would be obtained from the following code:
SELECT name,
CASE
WHEN dept
IN (1)
THEN 'Computing'
ELSE 'Other'
END
FROM teacher
输出结果为:
Table-A
Shrivell Computing
Throd Computing
Splint Computing
Spiregrain Other
Cutflower Other
Deadyawn Other
17.Self join
stop表s
id name
1 Aberlady
2 Abington
3 Amisfield Park
4 Ancrum
num company pos stop
1 LRT 1 137
1 LRT 2 99
1 LRT 3 59
1. How many stops are in the database.
select count(*) from stops;
2.Find the id value for the stop 'Craiglockhart'
select id from stops where name = 'Craiglockhart';
3.Give the id and the name for the stops on the '4' 'LRT' service.
select id,name
from stops join route on stops.id = route.stop
where num = '4' and company = 'LRT';
输出结果为:
id name
19 Bingham
177 Northfield
149 London Road
194 Princes Street
115 Haymarket
53 Craiglockhart
179 Oxgangs
....
4.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;
输出结果为:
Correct answer
company num
LRT 4 2
LRT 45 2
5.Execute the self join shown and observe that b.stop 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;
6.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';
7.Give a list of all the services which connect stops 115 and 137 ('Haymarket' and 'Leith')
select 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
group by a.company,a.num;
8. 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 stopa.id=a.stop
join stops stopb on stopb.id=b.stop
where stopa.name='Craiglockhart' and stopb.name='Tollcross';
9. 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 distinct s1.name,r1.company,r1.num
from stops s1,stops s2,route r1,route r2
where s2.id=r2.stop
and s2.name='Craiglockhart'
and r2.company=r1.company
and r2.num=r1.num
and r1.stop=s1.id
10.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 bus1.num, bus1.company, name, bus2.num, bus2.company from
(select start1.num, start1.company, stop1.stop from route start1
join route stop1 on start1.num=stop1.num and start1.company=stop1.company and start1.stop!=stop1.stop and start1.stop=(select id from stops where name='Craiglockhart')) bus1
join (select start2.num, start2.company, start2.stop from route start2
join route stop2 on start2.num=stop2.num and start2.company=stop2.company and start2.stop!=stop2.stop and stop2.stop=(select id from stops where name='Lochend')) bus2
on bus1.stop=bus2.stop
join stops on stops.id=bus1.stop;
18 Self join Quiz
1.Select the code that would show it is possible to get from Craiglockhart to Haymarket
SELECT DISTINCT a.name, b.name
FROM stops a JOIN route z ON a.id=z.stop
JOIN route y ON y.num = z.num
JOIN stops b ON y.stop=b.id
WHERE a.name='Craiglockhart' AND b.name ='Haymarket'
2. Select the code that shows the stops that are on route.num '2A' which can be reached with one bus from Haymarket?
SELECT S2.id, S2.name, R2.company, R2.num
FROM stops S1, stops S2, route R1, route R2
WHERE S1.name='Haymarket' AND S1.id=R1.stop
AND R1.company=R2.company AND R1.num=R2.num
AND R2.stop=S2.id AND R2.num='2A'
3. Select the code that shows the services available from 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='Tollcross'
(完)
文章转载自牛谈琴，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
