如何编写SQL查询以获取每个国家/地区每个团队的玩家人数？

问题描述：

考虑这些SQL表。如何编写查询以获得 -如何编写SQL查询以获取每个国家/地区每个团队的玩家人数？

每个国家/地区的每个团队的玩家人数。
乡下聪明的高薪球员。
排名前两位的国家数量。
具有国家明智球员人数的国家的详细信息
球员详细信息以及在3月份加入球队的国家，国家和球队详细信息。
乡下聪明最有经验的球员。

这是我的表

/*///*********CREATING TABLES AND INSERTING DATA********///*/ 


CREATE TABLE OL_Player(
PLAYERID  NUMBER, 
PLAYERNAME   VARCHAR2(10), 
TEAMID   NUMBER, 
GENDER   VARCHAR2(1), 
SALARY   NUMBER,  
STATEID   NUMBER,       
DOJ    DATE);       

CREATE TABLE OL_State(
STATEID   NUMBER, 
STATENAME VARCHAR2(15), 
COUNTRYID NUMBER); 

CREATE TABLE OL_Country(
COUNTRYID  NUMBER, 
COUNTRYNAME  VARCHAR2(15)); 

CREATE TABLE OL_Team(
TEAMID  NUMBER, 
TEAMNAME VARCHAR2(25)); 

/*///**********INSERT INTO OL_Player**************///*/ 


INSERT ALL 

INTO OL_Player(PLAYERID,PLAYERNAME,TEAMID,GENDER,SALARY,STATEID,DOJ) 
VALUES (1,'SANDEEP',101,'M',22000,11,'02-MAY-13') 
INTO OL_Player(PLAYERID,PLAYERNAME,TEAMID,GENDER,SALARY,STATEID,DOJ) 
VALUES (2,'BITTU',101,'F',15000,11,'03-APR-16') 
INTO OL_Player(PLAYERID,PLAYERNAME,TEAMID,GENDER,SALARY,STATEID,DOJ) 
VALUES (3,'VINAY',102,'M',20000,12,'18-MAR-13') 
INTO OL_Player(PLAYERID,PLAYERNAME,TEAMID,GENDER,SALARY,STATEID,DOJ) 
VALUES (4,'PRIYANKA',102,'F',20000,12,'16-JUN-12') 
INTO OL_Player(PLAYERID,PLAYERNAME,TEAMID,GENDER,SALARY,STATEID,DOJ) 
VALUES (5,'PRAMOD',103,'M',25000,13,'07-JAN-11') 
INTO OL_Player(PLAYERID,PLAYERNAME,TEAMID,GENDER,SALARY,STATEID,DOJ) 
VALUES (6,'PAYAL',103,'F',20000,13,'09-FEB-14') 
INTO OL_Player(PLAYERID,PLAYERNAME,TEAMID,GENDER,SALARY,STATEID,DOJ) 
VALUES (7,'HEMANT',104,'M',28000,14,'15-JUL-09') 
INTO OL_Player(PLAYERID,PLAYERNAME,TEAMID,GENDER,SALARY,STATEID,DOJ) 
VALUES (8,'SONU',104,'F',21000,14,'20-AUG-11') 
INTO OL_Player(PLAYERID,PLAYERNAME,TEAMID,GENDER,SALARY,STATEID,DOJ) 
VALUES (9,'VINOD',105,'M',27000,15,'02-MAR-10') 
INTO OL_Player(PLAYERID,PLAYERNAME,TEAMID,GENDER,SALARY,STATEID,DOJ) 
VALUES (10,'DIPIKA',105,'F',20000,15,'25-SEP-14') 
INTO OL_Player(PLAYERID,PLAYERNAME,TEAMID,GENDER,SALARY,STATEID,DOJ) 
VALUES (11,'GOKU',106,'M',14000,16,'30-OCT-16') 
INTO OL_Player(PLAYERID,PLAYERNAME,TEAMID,GENDER,SALARY,STATEID,DOJ) 
VALUES (12,'TODI',106,'F',14000,16,'05-NOV-16') 
INTO OL_Player(PLAYERID,PLAYERNAME,TEAMID,GENDER,SALARY,STATEID,DOJ) 
VALUES (13,'KANNU',107,'M',16000,17,'10-NOV-15') 
INTO OL_Player(PLAYERID,PLAYERNAME,TEAMID,GENDER,SALARY,STATEID,DOJ) 
VALUES (14,'PRINCY',107,'F',17000,17,'15-DEC-15') 
INTO OL_Player(PLAYERID,PLAYERNAME,TEAMID,GENDER,SALARY,STATEID,DOJ) 
VALUES (15,'LUCKEY',108,'M',22000,18,'22-MAR-13') 
INTO OL_Player(PLAYERID,PLAYERNAME,TEAMID,GENDER,SALARY,STATEID,DOJ) 
VALUES (16,'PINKY',108,'F',20000,18,'20-SEP-13') 
INTO OL_Player(PLAYERID,PLAYERNAME,TEAMID,GENDER,SALARY,STATEID,DOJ) 
VALUES (17,'SHEYU',109,'M',21000,19,'03-JUN-14') 
INTO OL_Player(PLAYERID,PLAYERNAME,TEAMID,GENDER,SALARY,STATEID,DOJ) 
VALUES (18,'SALONI',109,'F',18000,19,'25-FEB-15') 
INTO OL_Player(PLAYERID,PLAYERNAME,TEAMID,GENDER,SALARY,STATEID,DOJ) 
VALUES (19,'RISHABH',110,'M',22000,20,'05-JUL-12') 
INTO OL_Player(PLAYERID,PLAYERNAME,TEAMID,GENDER,SALARY,STATEID,DOJ) 
VALUES (20,'TINA',110,'F',21000,20,'02-JAN-12') 

SELECT 1 FROM DUAL; 

/*///**********INSERT INTO OL_State**************///*/ 

INSERT ALL 

INTO OL_State(STATEID,STATENAME,COUNTRYID) 
VALUES (11,'MP',1001) 
INTO OL_State(STATEID,STATENAME,COUNTRYID) 
VALUES (12,'UP',1001) 
INTO OL_State(STATEID,STATENAME,COUNTRYID) 
VALUES (13,'MH',1002) 
INTO OL_State(STATEID,STATENAME,COUNTRYID) 
VALUES (14,'HR',1002) 
INTO OL_State(STATEID,STATENAME,COUNTRYID) 
VALUES (15,'RJ',1003) 
INTO OL_State(STATEID,STATENAME,COUNTRYID) 
VALUES (16,'GJ',1003) 
INTO OL_State(STATEID,STATENAME,COUNTRYID) 
VALUES (17,'KL',1004) 
INTO OL_State(STATEID,STATENAME,COUNTRYID) 
VALUES (18,'KN',1004) 
INTO OL_State(STATEID,STATENAME,COUNTRYID) 
VALUES (19,'TN',1005) 
INTO OL_State(STATEID,STATENAME,COUNTRYID) 
VALUES (20,'AP',1005) 

SELECT 1 FROM DUAL; 


/*///**********INSERT INTO OL_Country**************///*/ 

INSERT ALL 

INTO OL_Country(COUNTRYID,COUNTRYNAME) 
VALUES (1001,'INDIA') 
INTO OL_Country(COUNTRYID,COUNTRYNAME) 
VALUES (1002,'USA') 
INTO OL_Country(COUNTRYID,COUNTRYNAME) 
VALUES (1003,'ENGLAND') 
INTO OL_Country(COUNTRYID,COUNTRYNAME) 
VALUES (1004,'AUSTRALIA') 
INTO OL_Country(COUNTRYID,COUNTRYNAME) 
VALUES (1005,'FRANCE') 

SELECT 1 FROM DUAL; 


/*///**********INSERT INTO OL_Team**************///*/ 

INSERT ALL 

INTO OL_Team(TEAMID,TEAMNAME) 
VALUES (101,'WARRIOR') 
INTO OL_Team(TEAMID,TEAMNAME) 
VALUES (102,'FIGHTER') 
INTO OL_Team(TEAMID,TEAMNAME) 
VALUES (102,'TIGER') 
INTO OL_Team(TEAMID,TEAMNAME) 
VALUES (104,'LION') 
INTO OL_Team(TEAMID,TEAMNAME) 
VALUES (105,'PANTHER') 
INTO OL_Team(TEAMID,TEAMNAME) 
VALUES (106,'KINGS') 
INTO OL_Team(TEAMID,TEAMNAME) 
VALUES (107,'ROYAL') 
INTO OL_Team(TEAMID,TEAMNAME) 
VALUES (108,'PIRATES') 
INTO OL_Team(TEAMID,TEAMNAME) 
VALUES (109,'DON') 
INTO OL_Team(TEAMID,TEAMNAME) 
VALUES (110,'HERO') 

SELECT 1 FROM DUAL;

退房'GROUP BY'，用'COUNT（*）'和其它集合函数！（学校工作？） – jarlh

通常在这个帖子中包含单个问题。你有一个完整的列表。 – scaisEdge

懒得做功课，甚至懒得问个别的问题... – jarlh

答

应该sometheing这样;

select count(*) numberofPlayers, c.countryName, t.teamName 
from OL_Player p, 
    OL_State s, 
    OL_Country c, 
    OL_Team t 
where p.teamId = t.teamId 
    and p.stateID = s.STATEID 
    and s.countryId = c.countryId 
group by c.countryName, t.teamName

您应该使用外键连接您的表，然后对它们进行分组。

切换到现代，明确的'JOIN'语法。易于编写（没有错误），更易于阅读和维护，并且更容易转换为外部联接 - 如果需要的话。 – jarlh

有外键可以确保数据的一致性。（与连接无关。） – jarlh

谢谢你！努力吧！ –

答

SELECT count(*),oc.COUNTRYNAME,ot.TEAMNAME FROM OL_PLAYER op 
    INNER JOIN OL_STATE os ON(op.STATEID=os.STATEID) 
    INNER JOIN OL_COUNTRY oc ON(os.COUNTRYID=oc.COUNTRYID) 
    INNER JOIN OL_TEAM ot ON(op.TEAMID=ot.TEAMID) group by oc.COUNTRYNAME,ot.TEAMNAME;

SELECT r.HIGHEST_SALARY,op.PLAYERNAME,r.COUNTRYNAME FROM(
SELECT MAX(op.SALARY) HIGHEST_SALARY, oc.COUNTRYNAME FROM OL_PLAYER op 
INNER JOIN OL_STATE os ON(op.STATEID=os.STATEID) 
INNER JOIN OL_COUNTRY oc ON(os.COUNTRYID=oc.COUNTRYID) 
INNER JOIN OL_TEAM ot ON(op.TEAMID=ot.TEAMID) group by oc.COUNTRYNAME) r 
INNER JOIN OL_PLAYER op ON r.HIGHEST_SALARY=op.SALARY;

SELECT * FROM(
SELECT count(DISTINCT ot.TEAMNAME) NO_OF_TEAMS, oc.COUNTRYNAME FROM OL_PLAYER op 
INNER JOIN OL_STATE os ON(op.STATEID=os.STATEID) 
INNER JOIN OL_COUNTRY oc ON(os.COUNTRYID=oc.COUNTRYID) 
INNER JOIN OL_TEAM ot ON(op.TEAMID=ot.TEAMID) group by oc.COUNTRYNAME order by oc.COUNTRYNAME) where ROWNUM<=2;

SELECT * from(
SELECT count(*) NO_OF_PLAYERS,oc.COUNTRYNAME FROM OL_PLAYER op 
INNER JOIN OL_STATE os ON(op.STATEID=os.STATEID) 
INNER JOIN OL_COUNTRY oc ON(os.COUNTRYID=oc.COUNTRYID) 
INNER JOIN OL_TEAM ot ON(op.TEAMID=ot.TEAMID) group by oc.COUNTRYNAME) 
where NO_OF_PLAYERS<2;

5。

SELECT op.PLAYERID,op.PLAYERNAME,op.TEAMID,op.GENDER,op.SALARY,op.DOJ, os.STATENAME, oc.COUNTRYNAME, ot.TEAMNAME FROM OL_PLAYER op 
INNER JOIN OL_STATE os ON(op.STATEID=os.STATEID) 
INNER JOIN OL_COUNTRY oc ON(os.COUNTRYID=oc.COUNTRYID) 
INNER JOIN OL_TEAM ot ON(op.TEAMID=ot.TEAMID) where EXTRACT(month from op.DOJ)=3;

SELECT r.EXPERIENCED_PLAYER,op.PLAYERNAME,r.COUNTRYNAME FROM(
SELECT MIN(op.DOJ) EXPERIENCED_PLAYER, oc.COUNTRYNAME FROM OL_PLAYER op 
INNER JOIN OL_STATE os ON(op.STATEID=os.STATEID) 
INNER JOIN OL_COUNTRY oc ON(os.COUNTRYID=oc.COUNTRYID) 
INNER JOIN OL_TEAM ot ON(op.TEAMID=ot.TEAMID) group by oc.COUNTRYNAME) r 
INNER JOIN OL_PLAYER op ON(r.EXPERIENCED_PLAYER=op.DOJ);

如何编写SQL查询以获取每个国家/地区每个团队的玩家人数？

相关推荐