java之旅之习题大作战

最新推荐文章于 2024-07-19 12:37:36 发布

xuchou1990

最新推荐文章于 2024-07-19 12:37:36 发布

阅读量312

点赞数

分类专栏： JAVA基础文章标签： sql

本文链接：https://blog.csdn.net/xuchou1990/article/details/53818901

版权

JAVA基础专栏收录该内容

15 篇文章 0 订阅

订阅专栏

SQL查询语句：

#SELECT DISTINCT f_club FROM t_player;
#SELECT * FROM t_player WHERE f_dateOfBirth >='1997-1-1'
#AND f_dateOfBirth <='2000-1-1';
#SELECT *FROM t_player WHERE f_club ='拜仁慕尼黑' or f_club='曼联';
#SELECT * FROM t_player WHERE f_dateOfBirth BETWEEN '1990-1-1' and '1999-12-31';
#SELECT * FROM t_player WHERE f_club  in ('拜仁慕尼黑','曼联','AC米兰');
#SELECT * FROM t_player WHERE f_club not in ('拜仁慕尼黑','曼联','AC米兰');
#SELECT * FROM t_player WHERE f_name LIKE '__斯基';

#SELECT  bbc.contryName AS 国家名称 FROM bbc WHERE bbc.population >= 200000000;
#SELECT  bbc.contryName AS 国家名称 , bbc.gdp as 人均GDP FROM bbc WHERE bbc.population >= 200000000;
#SELECT bbc.contryName AS 国家名称, round(bbc.population/1000000) as 人口数 FROM bbc WHERE bbc.region ='South Asia';

#SELECT bbc.contryName AS 国家名称,bbc.population as 人口数   FROM bbc WHERE bbc.contryName ='france' OR bbc.contryName ='germany'
 #OR bbc.contryName ='italy';
#SELECT bbc.contryName AS 国家名称 FROM bbc WHERE bbc.contryName LIKE '%United%';

#SELECT DISTINCT bbc.region as 地区 FROM bbc ;
#SELECT bbc.contryName AS 国家名称,bbc.population AS 人口数 from bbc WHERE bbc.population >100000000 ORDER BY bbc.population DESC;

#SELECT nobel.winner AS 诺贝尔得主 FROM nobel WHERE nobel.yr='1950';

#SELECT nobel.winner AS 文学奖得主 FROM nobel WHERE nobel.yr='1962' AND nobel.`SUBJECT`='Literature';
#SELECT nobel.yr AS 年份,nobel.`SUBJECT` AS 奖项  FROM nobel WHERE nobel.winner = 'Albert Einstein';

#SELECT nobel.winner AS 和平奖得主 FROM nobel WHERE nobel.`SUBJECT`='peace' AND nobel.yr >='2000';

#SELECT * FROM nobel WHERE nobel.`SUBJECT`='Literature' BETWEEN '1980-1-1' and '1989-12-31';

#SELECT * FROM nobel WHERE nobel.winner in ('theodpre roosevelt','woodrow wilson','jed bartlet','jimmy carter');
#SELECT nobel.winner AS 得主 FROM nobel WHERE nobel.winner LIKE 'JOhn&';

#SELECT DISTINCT nobel.`SUBJECT` AS 奖项 FROM nobel;

#SELECT COUNT(DISTINCT f_place)FROM t_player;
#SELECT  COUNT (DISTINCT f_club) FROM t_player;
#SELECT  SUM(f_teamNum)  FROM t_team;
#SELECT   AVG( f_teamNum)   FROM t_team;
#SELECT  f_place,MIN(f_dateOfBirth),count( * ) FROM t_player  GROUP BY f_place  ;
#SELECT f_club,COUNT(*) as  球员总数 FROM t_player GROUP BY f_club ORDER BY 球员总数 DESC;
#SELECT  f_club, f_place ,COUNT(*) AS 球员总数  FROM t_player GROUP BY f_club,f_place ORDER BY 球员总数 DESC ;
#SELECT  f_club, COUNT(*) AS 球员人数  FROM t_player GROUP BY f_club HAVING 球员人数 > 5 ORDER BY 球员人数 DESC;
#SELECT f_club,COUNT(*) AS 球员人数 FROM t_player WHERE f_club IS NOT NULL GROUP BY f_club HAVING 球员人数 > 5 ORDER BY 球员人数 DESC;
SELECT  fk_teamid, COUNT(*) FROM t_player WHERE f_place='后卫' GROUP BY fk_teamid HAVING COUNT(*) > 8 ;


   #SELECT SUM(bbc.population)  FROM bbc;

 #SELECT DISTINCT bbc.region FROM bbc;

#SELECT bbc.contryName  FROM bbc WHERE bbc.gdp>(SELECT SUM(bbc.gdp) FROM bbc WHERE bbc.region='africa' ) ;

#SELECT bbc.region,count(*) AS 国家总数 FROM bbc GROUP BY bbc.region; 

#SELECT SUM(bbc.population),SUM(bbc.contryArea)FROM bbc GROUP BY bbc.region ORDER BY SUM(bbc.population) desc;

#SELECT count(bbc.contryName),SUM(bbc.population)FROM bbc GROUP BY bbc.region HAVING SUM(bbc.population)>=10000000;


#SELECT bbc.region, SUM(bbc.population)FROM bbc GROUP BY bbc.region HAVING sum(bbc.population)>=100000000;

#SELECT SUM(bbc.population),SUM(bbc.gdp) FROM bbc WHERE bbc.region='Europe';

#SELECT bbc.region,SUM(bbc.population),SUM(bbc.contryArea)FROM bbc GROUP BY bbc.region HAVING SUM(bbc.contryArea)>1000000 ORDER BY SUM(bbc.population) desc;

#SELECT count(DISTINCT nobel.winner)FROM nobel ;


#SELECT count(nobel.`SUBJECT`)FROM nobel WHERE nobel.`SUBJECT` ='physics';

#SELECT nobel.`SUBJECT`,count(nobel.`SUBJECT`)FROM nobel GROUP BY nobel.`SUBJECT`;

#SELECT nobel.`SUBJECT`,MIN(nobel.yr)FROM nobel GROUP BY nobel.`SUBJECT`;

#SELECT nobel.yr,nobel.`SUBJECT`,count(nobel.winner) FROM nobel WHERE nobel.yr='2000'GROUP BY nobel.`SUBJECT`;

#SELECT nobel.`SUBJECT`,count(DISTINCT nobel.winner`) FROM nobel GROUP BY nobel.`SUBJECT` ;

#SELECT nobel.`SUBJECT`,count(DISTINCT nobel.yr) FROM nobel GROUP BY nobel.`SUBJECT`;

#SELECT nobel.yr  FROM nobel WHERE nobel.`SUBJECT`='physics' GROUP BY nobel.yr HAVING count(*)=3;

#SELECT nobel.winner,COUNT(*) FROM nobel GROUP BY nobel.winner HAVING count(nobel.winner)>1;

#SELECT nobel.winner,COUNT(DISTINCT nobel.`SUBJECT`)AS cnt FROM nobel GROUP BY nobel.winner HAVING cnt>1;

#SELECT nobel.yr,nobel.`SUBJECT`,COUNT(*)FROM nobel WHERE nobel.yr>=2000 GROUP BY nobel.yr,nobel.`SUBJECT` HAVING COUNT(nobel.winner)=3 ;

#CREATE TABLE games (years INT ,city VARCHAR(20));
#INSERT INTO games( years,city )VALUES (2004,'雅典' );
#insert into games(years,city ) VALUES (2008,'北京') ;
#INSERT INTO games (years,city) VALUES (2000,'悉尼') ;
#INSERT INTO games SELECT years+12 ,city FROM games ;

#SELECT * INTO games_bak FROM games;
#DELETE FROM games WHERE years = 2016 or years = 2020;

#UPDATE games set city = '伦敦' WHERE years = 2012;

#ALTER TABLE games add country VARCHAR (50);
#UPDATE games SET country ='澳大利亚' WHERE years ='2000';
#UPDATE games SET country = '希腊' WHERE years ='2004';
#UPDATE games SET country ='中国' WHERE years = '2008';
#UPDATE games SEt country ='英国' WHERE years ='2012';

#UPDATE  games SET city = NULL,country = NULL WHERE years >'2012';
#UPDATE  games SET city = '待定',country = '待定' WHERE years >'2012';
#UPDATE  games SET country =CONCAT(country,'(australia)') WHERE country ='澳大利亚';
#UPDATE  games SET country =CONCAT(country,'(greece)') WHERE country = '希腊';
#UPDATE  games SET country =CONCAT(country,'(china)') WHERE country ='中国';
#UPDATE  games SEt country =CONCAT(country,'(britain)') WHERE country ='英国';

#DELETE FROM games WHERE years >'2012';
#TRUNCATE tABLE games_bak;

#获得和'Brazil'同属一个地区的所有国家
#select contryName from bbc where region = (select region from bbc where contryName='Brazil');

#给出人口多于'Russia'的国家名称
#select contryName 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 in ('India','Iran'));

#给出人均GDP超过'United Kindom'的欧洲国家
#select contryName 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 contryName from bbc where gdp > ALL (select gdp from bbc where region='Europe');

#给出每个地区人口最大的国家
#select contryName from bbc where population in (select  MAX(population) from bbc GROUP BY region);

#给出地区中所有国家的人口总数为0的地区
#select region,SUM(population) from bbc GROUP BY region having SUM(population) = 0;

#有些国家的人口数比周边国家要多三倍，列出这些国家和地区
select contryName,region from bbc b1 where population > 4*(select Max(population) from bbc b2 where b1.region=b2.region and b1.contryName <> b2.contryName);

#找出包含歌曲'Alison'的专辑和作者
#select title,artist from album join track on album=albumCode where song='Alison';

#哪个作者录制了歌曲'Exodus'
#select artist from album join track on album=albumCode where song='Exodus';

#列出所有属于‘Blur’专辑的歌曲
#select song from album join track on album=albumCode where title='Blur';

#每张专辑的名字和包含的曲目数
#select title,count(song) from album left join track on album=albumCode GROUP BY title ORDER BY count(*);

#显示每张专辑的名称以及曲目名称中包含'Heart'的曲目总数
#SELECT title,count(*) from album join track on album=albumCode where song like '%Heart%' GROUP BY title;

#找出歌名与专辑名相同的曲目名
#select song from album join track on album=albumCode where song=title;

#找出专辑名与作者名相同的
#select title from album where artist=title;

#找出在两张以上专辑中出现过的歌曲，同时包含出现的次数
#select song,count(*) from album join track on album=albumCode group BY song HAVING count(*) > 2;


#一张物超所值的专辑就是专辑中每首歌曲的定价低于50，找出
#所有这种专辑，显示专辑名字，价格和曲目总数
#select title,price,count(song) from album join track on album=albumCode GROUP BY albumCode HAVING (price / count(song)) < 0.5 ;

select title, COUNT(song) from album join track on album=albumCode 
GROUP BY albumCode ORDER BY COUNT(song) DESC;

#显示id=1的电影中演员（角色）名单
#select actor.`NAME`,casting.movieord from (movie join casting on movie.id=casting.movieid) JOIN actor on(actor.id=casting.actorid) where movie.id=1;

#显示电影Alien的演员名单
#select actor.`NAME`,casting.movieord from (movie join casting on movie.id=casting.movieid) JOIN actor on(actor.id=casting.actorid) where movie.title='Alien';

#显示有Harrison Ford参演的电影名称
#select movie.title from (movie join casting on movie.id=casting.movieid) JOIN actor on(actor.id=casting.actorid) where actor.`NAME`='Harrison Ford';

#显示有Harrison Ford参演，但不是主角的电影名称
#select movie.title from (movie join casting on movie.id=casting.movieid) JOIN actor on(actor.id=casting.actorid) where actor.`NAME`='Harrison Ford' and casting.movieord <> 1;

#显示1962年发行的电影以及该影片中主角
#select movie.title,actor.`NAME` from (movie join casting on movie.id=casting.movieid) JOIN actor on(actor.id=casting.actorid) where movie.yr=1962 and casting.movieord = 1;

#'John Travolta'哪一年最忙？显示出他每年的出演电影数量
#select yr,count(movie.title) from (movie join casting on movie.id=casting.movieid) JOIN actor on(actor.id=casting.actorid) where actor.`NAME`='John Travolta' GROUP BY movie.yr;

#列出有‘Julie Andrews’参演的所有电影名称以及该片的主角
#select movie.title,(select actor.`NAME` from actor where actor.id = (select actorid from casting where casting.movieid=movie.id and casting.movieord = 1)) from (movie join casting on movie.id=casting.movieid) JOIN actor on actor.id=casting.actorid where actor.`NAME`='Julie Andrews';

#列出那些起码在10部电影中出演过主角的演员
#select actor.`NAME` from (movie join casting on movie.id=casting.movieid) JOIN actor on actor.id=casting.actorid where casting.movieord=1 GROUP BY actor.id HAVING count(*) >= 10;

#按演员人数的多少依次列出1978的电影以及演员数
#select movie.title,count(*) from movie join casting on movie.id=casting.movieid where movie.yr=1978 GROUP BY movie.id ORDER BY count(*) DESC;


#列出所有和'Art Garfunkel'合作过的演员
select actor.`NAME` from casting JOIN actor on casting.actorid=actor.id where actor.`NAME` <> 'Art Garfunkel' and casting.movieid in (select casting.movieid from casting where casting.actorid = (select actor.id from actor where actor.`NAME`='Art Garfunkel'));

#一个议员被开除党，看看他是谁
#select msp.mspName from msp where msp.party is null;

#列出所有政党和领导者
#select * from party 

#列出所有有领导者的政党
#select party.partyName from party where party.leader is not null;

#列出至于有一个议员的政党
#select party.partyName from party where party.partyCode in (select DISTINCT msp.party from msp)

#列出所有议员的名字和政党名称，没有政党也要显示
#select msp.mspName,party.partyName from party right join msp on party.partyCode=msp.party


#列出包含议员的政党，包含每个政党的人数
#select party.partyName,count(*) from party join msp on party.partyCode=msp.party GROUP BY party.partyCode;

#列出所有政党和每个政党的人数，包括没有议员的政党
#select party.partyName,count(*) from party join msp on party.partyCode=msp.party GROUP BY party.partyCode
#union
#select party.partyName,0 from party where party.partyCode not in (select DISTINCT msp.party from msp where msp.party is not null);

select party.partyName,count(msp.party) from party left join msp on party.partyCode=msp.party GROUP BY party.partyCode

xuchou1990

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
java之旅之习题大作战

SQL查询语句：#SELECT DISTINCT f_club FROM t_player;#SELECT * FROM t_player WHERE f_dateOfBirth >='1997-1-1'#AND f_dateOfBirth <='2000-1-1';#SELECT *FROM t_player WHERE f_club ='拜仁慕尼黑' or f_club='曼联';#SE
复制链接

扫一扫

专栏目录