数据库

   之前写了数据库的创建，还有单一数据库的增、删、改、查

   这里记录子查询和联接查询

  子查询：

-- 获得到和‘Brazil’同属一个地区的所有国家
SELECT * FROM bbc WHERE region=(
SELECT region FROM bbc WHERE contryName='Brazil'
);


-- 给出人口多于Russia的国家名称
SELECT * FROM bbc WHERE population>(
SELECT population FROM bbc WHERE contryName='Russia'
);


-- 给出'India','Iran'所在地区的所有国家信息
SELECT * FROM bbc WHERE region IN(
SELECT region FROM bbc WHERE contryName='India' OR contryName='Iran'
);


-- 给出人均gdp超过'United Kingdom'(英国)的欧州国家
SELECT * FROM bbc  WHERE region='Europe' AND gdp/population>(
SELECT gdp/population FROM bbc WHERE contryName='United Kingdom'
); 


-- 给出人口比'Canada'多但少于Algeria的国家的信息
SELECT * FROM bbc WHERE population>(
SELECT population FROM bbc WHERE contryName='Canada'
) AND population<(
SELECT population FROM bbc WHERE contryName='Algeria'
);


-- 给出gdp比任何欧州国家都多的国家
SELECT * FROM bbc WHERE gdp>(
SELECT MAX(gdp) FROM bbc WHERE region='Europe'
);


-- 给出每个地区人口最大的国家
SELECT * FROM bbc WHERE population IN(
SELECT MAX(population) FROM bbc GROUP BY region 
);


-- 给出地区中所有国家的人口总数为0的地区
SELECT region FROM bbc GROUP BY region HAVING SUM(population)=0;


-- 有些国家的人口数比她的周边国家要多三倍，请列出这些国家和地区。
SELECT * FROM bbc b WHERE b.population>4*(
SELECT MAX(bc.population) FROM bbc bc WHERE b.region=bc.region AND b.contryName!=bc.contryName
);

联接查询：

-- 显示电影id=1中演员名单
SELECT a.* FROM casting c,actor a WHERE a.id=c.actorid AND c.movieid=1;


-- 显示电影'Alien'中演员名单
SELECT a.NAME FROM movie m,casting c,actor a WHERE m.id=c.movieid AND a.id=c.actorid AND m.title='Alien';


-- 显示有'Harrison Ford'参演的电影名称
SELECT m.title FROM movie m,casting c,actor a WHERE m.id=c.movieid AND a.id=c.actorid AND a.NAME='Harrison Ford';


-- 显示有'Harrison Ford'参演的电影名称，但'Harrison Ford'角色排名不是第一
SELECT m.title FROM movie m,casting c,actor a WHERE m.id=c.movieid AND a.id=c.actorid AND a.NAME='Harrison Ford' AND c.movieord!=1;


-- 显示1962年发行的电影，及该影片的主角(排名第一)
SELECT m.title,a.NAME FROM movie m,casting c,actor a WHERE m.id=c.movieid AND a.id=c.actorid AND m.yr=1962 AND c.movieord=1;


-- 显示'John Travolta'每年出演的电影数量
SELECT m.yr,COUNT(m.title) FROM movie m,casting c,actor a WHERE m.id=c.movieid AND a.id=c.actorid AND a.NAME='John Travolta' GROUP BY m.yr;


-- 列出'Julie Andrews'参演的所有电影名称以及该片的主角
SELECT m.title,a.NAME FROM movie m,casting c,actor a WHERE m.id=c.movieid AND a.id=c.actorid AND m.id IN(
SELECT c1.movieid FROM casting c1 ,actor a1 WHERE a1.id=c1.actorid AND a1.NAME='Julie Andrews'
) AND c.movieord=1;


-- 列出在10部电影中出任过主角的演员
SELECT a.NAME FROM movie m,casting c,actor a WHERE m.id=c.movieid AND a.id=c.actorid AND c.movieord=1 AND c.movieord=1 GROUP BY a.NAME HAVING COUNT(m.title)=10 ;


-- 按演员人数的多少依次列出1978的电影以及演员数。
SELECT m.title,COUNT(a.NAME) FROM movie m,casting c,actor a WHERE m.id=c.movieid AND a.id=c.actorid AND m.yr=1978 GROUP BY m.title ORDER BY COUNT(a.NAME) DESC;


-- 列出所有和'Art Garfunkel'合作过的演员
SELECT DISTINCT a.NAME FROM movie m,casting c,actor a WHERE m.id=c.movieid AND a.id=c.actorid AND m.id IN(
SELECT c1.movieid FROM casting c1,actor a1 WHERE a1.id=c1.actorid AND a1.NAME='Art Garfunkel'
) AND a.NAME!='Art Garfunkel';