Как написать SQL-запрос, чтобы получить количество игроков в каждой команде в каждой стране?
Рассмотрим эти таблицы SQL. Как написать запрос, чтобы получить
- Количество игроков в каждой команде в каждой стране.
- Страна мудрый высокооплачиваемый игрок.
- Топ 2 страны по количеству команд.
- Подробная информация о тех странах, в которых количество игроков
- Игрок детализирует вместе с государством, страной и командой детали, кто присоединился к команде в марте месяце.
- Кантри самый опытный игрок.
Это мои столы
/*///*********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;
2 ответа
Это должно быть нечто подобное;
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
Вы должны объединить свои таблицы, используя внешние ключи, а затем сгруппировать их.
1.
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;
2.
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;
3.
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;
4.
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;
6.
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);