Запрос данных из 2 таблиц по критериям

[vikonder]

Имеется 2 таблицы:

Table - Players
PlayerID PlayerName Balance UserAgentID
1054............QA1............10.. ........111
2055............QA2............20.. ........677
3056............QA3............55.. ........123
4067............Max............70.. ........123

Table - UsersAgents
UserAgentID Browser
111..............FireFox
123..............Chrome
444...............IE
677..............Safari

1. Write an SQL query that return players whose name doesn't contains QA and their Browser.
Solution: (possible)

PHP код:

SELECT p.PlayerName, u.Browser 
FROM Players AS p, UsersAgents AS u 
WHERE p.PlayerName NOT LIKE "%QA%" 


2. Write an SQL query that summarize the balance for all Chrome players whose name start with QA.
Solution: (possible)

PHP код:

SELECT SUM(p.Balance)
FROM Players AS p
JOIN UserAgents AS u ON p.UserAgentID=u.UserAgentID
WHERE (u.Browser = "Chrome" AND p.PlayerName LIKE "QA%") 


3. Write an SQL query that will summarize the number of users per browser.
Solution: (possible)

PHP код:

SELECT u.Browser, SUM(p.UserAgentID) AS "Number Of Users"
FROM Players AS p
JOIN UserAgents AS u ON p.UserAgentID=u.UserAgentID 


Попинайте, решения.... особенно последнее.
Другие варианты также приветствуются...
Спасибо

[Serge_Bliznykov]

А Вы запросы не проверяли? зря...

первый запрос с ошибкой - не указана связь между таблицами Players и UsersAgents

второй запрос ошибок не вижу (только скобки в where лишние. но это не ошибка).


третий запрос неправильный.
упущено GROUP BY

[vikonder]

x10

Сообщение от Serge_Bliznykov

А Вы запросы не проверяли? зря...

первый запрос с ошибкой - не указана связь между таблицами Players и UsersAgents

второй запрос ошибок не вижу (только скобки в where лишние. но это не ошибка).


третий запрос неправильный.
упущено GROUP BY

К сожалению, проверить негде...
По поводу первого, я правильно понимаю, что независимо от использования WHERE or JOIN, критерий по ключу будет выглядить одинаково --> p.UserAgentID=u.UserAgentID

PHP код:

SELECT p.PlayerName, u.Browser  
FROM Players AS p, UsersAgents AS u  
WHERE p.UserAgentID=u.UserAgentID AND p.PlayerName NOT LIKE "%QA%" 


По поводу третьего, .... GROUP BY u.Browser

PHP код:

SELECT u.Browser, SUM(p.UserAgentID) AS "Number Of Users" 
FROM Players AS p 
JOIN UserAgents AS u ON p.UserAgentID=u.UserAgentID  
GROUP BY u.Browser 


Но так как в условии не указан критерий сортировки, то это ТАКЖЕ может быть любой элемент таблицы или нет? (i.e. GROUP by u.UsersAgents)

[Serge_Bliznykov]

Сообщение от vikonder

К сожалению, проверить негде...

ну, можно поискать хостинг с MySQL
можно поставить на комп любую СУБД (благо их, как грязи, в том числе и бесплатных).
впрочем, это дело ваше
можно и в уме отлаживать.
у меня лично это не всегда получается.

Сообщение от vikonder

По поводу первого, я правильно понимаю, что независимо от использования WHERE or JOIN, критерий по ключу будет выглядить одинаково --> p.UserAgentID=u.UserAgentID

почти, но, не не совсем. если Вы указали во FROM обе таблицы, тогда их связь нужно прописать в WHERE.
или убрать вторую таблицу из FROM и добавить JOIN

Сообщение от vikonder

Но так как в условии не указан критерий сортировки, то это ТАКЖЕ может быть любой элемент таблицы или нет? (i.e. GROUP by u.UsersAgents)

во-первых, не "сортировки", а "группировки" - это совсем разные вещи.
А во-вторых, стандарт SQL (и много хороших СУБД) не позволят Вам выбрать в SELECT поля, которые не указаны в GROUP BY.
поэтому нужно именно GROUP BY u.Browser

ну и ещё одна ошибка.
а зачем Вы делаете SUM(p.UserAgentID) ? вы реально хотите получить 123 + 123 + .... ?
Вам же нужно КОЛИЧЕСТВО.
поэтому

SELECT u.Browser, count(*) AS "Number Of Users" 
FROM Players AS p 
JOIN UserAgents AS u ON p.UserAgentID=u.UserAgentID  
GROUP BY u.Browser

[vikonder]

Попытался создать в Oracle 2 таблицы:

CREATE TABLE players
(   PlayerID     numeric(5)     not null,
    PlayerName   varchar2(10)   not null,
    Balance      numeric(5),
    UserAgentID  numeric(5)     not null,	
    CONSTRAINT players_pk PRIMARY KEY (UserAgentID)
);


CREATE TABLE useragents
(   UserAgentID  numeric(5)     not null,
    Browser     numeric(10)     not null,
    CONSTRAINT fk_players
    FOREIGN KEY (UserAgentID)
    REFERENCES players(UserAgentID)
);

Но что-то здесь намудрил... со связями, т.к. при заполнении таблицы получаю ошибку для 4 INSERT

INSERT INTO "SYSTEM"."PLAYERS" (PLAYERID, PLAYERNAME, BALANCE, USERAGENTID) VALUES ('1054', 'QA1', '10', '111');
INSERT INTO "SYSTEM"."PLAYERS" (PLAYERID, PLAYERNAME, BALANCE, USERAGENTID) VALUES ('2055', 'QA2', '20', '677');
INSERT INTO "SYSTEM"."PLAYERS" (PLAYERID, PLAYERNAME, BALANCE, USERAGENTID) VALUES ('3056', 'QA3', '55', '123');
INSERT INTO "SYSTEM"."PLAYERS" (PLAYERID, PLAYERNAME, BALANCE, USERAGENTID) VALUES ('4067', 'Max', '70', '123');

....unique constraint (SYSTEM.PLAYERS_PK) violated

[Serge_Bliznykov]

CREATE TABLE players
(   PlayerID     numeric(5)     not null,
    PlayerName   varchar2(10)   not null,
    Balance      numeric(5),
    UserAgentID  numeric(5)     not null,	
    CONSTRAINT players_pk PRIMARY KEY (UserAgentID)
);

и FOREIGN KEY должен быть в таблице players
а в таблице useragents поле UserAgentID - это первичный ключ.

[Serge_Bliznykov]

попробуйте так:

CREATE TABLE players
(   PlayerID     numeric(5)     not null,
    PlayerName   varchar2(10)   not null,
    Balance      numeric(5),
    UserAgentID  numeric(5)     not null,	
    CONSTRAINT players_pk PRIMARY KEY (PlayerID),
    CONSTRAINT fk_useragents
    FOREIGN KEY (UserAgentID)
    REFERENCES useragents(UserAgentID)

);


CREATE TABLE useragents
(   UserAgentID  numeric(5)     not null,
    Browser     numeric(10)     not null,
    CONSTRAINT useragents_pk PRIMARY KEY (UserAgentID)
);

[vikonder]

Таблицы создались нормально (вторая, затем первая)
но данные не вставляются ни в Players, ни в Useragents

INSERT INTO "SYSTEM"."PLAYERS" (PLAYERID, PLAYERNAME, BALANCE, USERAGENTID) VALUES ('1054', 'QA1', '10', '111')
ORA-02291: integrity constraint (SYSTEM.FK_USERAGENTS) violated - parent key not found
ORA-06512: at line 1

INSERT INTO "SYSTEM"."USERAGENTS" (USERAGENTID, BROWSER) VALUES ('111', 'FireFox')
ORA-01722: invalid number
ORA-06512: at line 1

[Serge_Bliznykov]

вставлять надо сначала в таблицу справочник (это useragents)

Сообщение от vikonder

ORA-01722: invalid number

Browser numeric(10) not null,

может быть, потому что, поле Browser числовое?
если оно числовое, то зачем Вы в него строку записать пытаетесь?
ну и тоже самое касается поля UserAgentID

INSERT INTO "SYSTEM"."USERAGENTS" (USERAGENTID, BROWSER) VALUES (111, 1)

[vikonder]

Пардон.
Скопировал из вашего примера, а там Number (10)

Сделал, как положено, начав со второй таблицы.
Теперь, все данные в таблицах, но вот незадача,
при попытке запустить любое корректное query из трёх выше
выскакивает Syntax Error - SQL command not properly ended
(и вручную чуруз sqlplus, и через sql developer)
i.e.