C, PHP, VB, .NET

Прилагам трите най-сложни задачи, които се паднаха на контролната работа на 21.05.2012г., заедно с примерни (бързи) решения. Във вариант 4 "трудната задача" беше от друго естество, затова по-нататък ще я разгледам отделно. Другите задачи от вариантите смятам за достатъчно лесни (решават се непосредствено чрез прилагане на заучени правила) и на тях няма да давам решения.

Вариант 1. Дадена е следната ER диаграма:

В таблица “Scores” ще се пази само по един (най-добрия постигнат) резултат на играч за дадена игра. Ако знаете, че полетата Players.username, Games.name и Games.vendor са от тип VARCHAR(100) NOT NULL, а всички останали са от тип INT, то решете следните задачи...

...

Задача 4. Изведете името на играча, който е постигнал най-добър резултат за играта „Heroes 5” и средния брой точки, които той е постигнал във всички игри, в които е играл.

Решение: При всички положения ще ни трябва да намерим "най-добрият резултат за играта Heroes 5", а от там да намерим и идентификационния номер на играча. Това е лесно, защото от условието знаем, че "се пази само по един (най-добрия постигнат) резултат на играч". Тоест не ни е нужно да групираме и да сумираме точки, а само трябва да намерим най-добрия резултат за въпросната игра и от там директно намираме кой е играча. Това може да стане като вземем редовете от Scores с играта "Heroes 5" (в scores нямаме нейното име, значи трябва да я намерим чрез нейното g_id), сортираме точките в низходящ ред и накрая вземем само първия резултат:

SELECT p_id
FROM scores
WHERE g_id =(
   SELECT games.id
   FROM games
   WHERE games.name = "Heroes 5"
)
ORDER BY points DESC
LIMIT 1;

Това е съвсем валидно решение на задачата "да намерим най-добрия играч, който е играл Heroes 5" и разбира се би се приело за напълно вярно (приемайки, че "играч" е в единствено число). Ако все пак искаме да сме перфектни, то би трябвало да отчетем и възможността да има повече от един играч на призовата позиция. Ако искаме да отчетем и него ще направим заявката по следния начин:

SELECT p_id
FROM scores
WHERE (g_id, points) = (
   SELECT g_id, MAX(points) AS tochki
   FROM scores
   WHERE g_id =(
      SELECT games.id
      FROM games
      WHERE games.name = "Heroes 5"
   )
);

Във вложената заявка връщаме идентификационния номер на играта, заедно с най-големия резултат, който е постигнат за нея. В най-външния SELECT използваме тези данни, за да намерим id на играчИТЕ, които са постигнали именно този най-висок резултат и то само за тази игра.

Дотук намерихме идентификационния номер на играча/ите с най-добър резултат в "Heroes 5". На нас обаче ще ни трябва неговото/тяхното потребителско име. Тук отново можем да използваме вложен SELECT:

SELECT username, id
FROM players
WHERE id IN (
   SELECT p_id
   FROM scores
   WHERE (g_id, points) = (
      SELECT g_id, MAX(points) AS tochki
      FROM scores
      WHERE g_id =(
        SELECT games.id
        FROM games
        WHERE games.name = "Heroes 5"
      )
   )
);

Сега към това потребителско име (или имена ако са повече от един) трябва да долепим и "средния брой точки, които той е постигнал във всички игри, в които е играл". Лесно можем да намерим средния брой точки за всички играчи:

SELECT scores.p_id, AVG(scores.points)
FROM scores
GROUP BY scoresp_id

Сега остава да направим връзка между резултатните таблици от двете заявки горе:

SELECT t1.username, t2.avgpoints
FROM (
SELECT username, id
FROM players
WHERE id IN (
   SELECT p_id
   FROM scores
   WHERE (g_id, points) = (
      SELECT g_id, MAX(points) AS tochki
      FROM scores
      WHERE g_id =(
        SELECT games.id
        FROM games
        WHERE games.name = "Heroes 5"
      )
   )
) ) AS t1
JOIN (SELECT scores.p_id, AVG(scores.points) AS avgpoints
      FROM scores
      GROUP BY scoresp_id
     ) AS t2
ON t1.id = t2.p_id;

Дали полученото решение е оптимално? На този етап се съмнявам, че е - виждаме много вложени заявки, част от които може би могат да се обединят. Опитайте се да оптимизирате. Но "по пътя на най-малкото съпротивление" и следването на непосредствената логика решението е такова.

Вариант 2. По същество началните условия на "Вариант 1" и "Вариант 2" са едни и същи - във "Вариант 1" базата от данни е зададена чрез ER диаграма, а във "Вариант 2" чрез самите таблици. Затова няма да даваме условието, а направо пристъпваме към задачата:

Задача 3. Изведете списък с имена на игри със съответни имена на техните разпространители (vendor), но само за игрите, които са играни от повече от 100 играчи. Получената таблица сортирайте в низходящ (DESC) ред по общ брой точки, които са получени за всяка игра.

Решение: Нека първо намерим игрите, които са играни от повече от 100 играчи. Тъй като за всеки играч пазим само по един резултат (най-добрия му), то ако просто преброим редовете в таблица "scores" с групиране по g_id ще можем да видим коя игра от колко играчи е играна. Накрая тази бройка можем да "отрежем" да е по-голяма от 100 чрез HAVING:

SELECT g_id, COUNT(*) AS players_count
FROM scores
GROUP BY g_id
HAVING players_count>100;

В условието обаче се иска и "сортиране по общ брой точки, които са получени за всяка игра". Ние в предишната заявка тъй или иначе прелистваме таблицата scores и групираме по g_id, т.е. можем да се възползваме от това и да "подобрим" предишната заявка така, че да връща и общата сума точки за всяка игра:

SELECT g_id, COUNT(*) AS players_count,
       SUM(points) AS total_points
FROM scores
GROUP BY g_id
HAVING players_count>100;

Сега от получения списък g_id трябва да бъде "заменено" с имената на игрите и имената на техните разпространители. Тези данни се пазят в отделна таблица "Games", която ще присъединим с JOIN (и накрая не забравяме да сортираме резултата):

SELECT games.name, games.vendor, t1.total_points
FROM(
      SELECT g_id, COUNT(*) AS players_count,
             SUM(points) AS total_points
      FROM scores
      GROUP BY g_id
      HAVING players_count>100
    ) AS t1
JOIN games ON games.id = t1.g_id
ORDER BY t1.total_points DESC;

С това решението на задачата е завършено.

Вариант 3.

Дадена е база от данни с две таблици:

CREATE TABLE users(
  id SMALLINT UNSIGNED PRIMARY KEY,
  username VARCHAR(30) NOT NULL UNIQUE
)ENGINE=InnoDB;

CREATE TABLE posts(
  id INT UNSIGNED PRIMARY KEY,
  author SMALLINT UNSIGNED NOT NULL,
  FOREIGN KEY(author) REFERENCES users(id),
  title VARCHAR(255) NULL DEFAULT NULL,
  data TEXT NOT NULL,
  created DATETIME NOT NULL,
  reply_to INT UNSIGNED NULL DEFAULT NULL,
  FOREIGN KEY(reply_to) REFERENCES posts(id)
)ENGINE=InnoDB;

В таблицата “posts” се записват два вида статии:

• Ако reply_to не е попълнено (NULL), то това е заглавна статия;
• Ако reply_to е попълнено, то това е „отговор“ на статия.

Ако знаете, че приложението не допуска „отговор на отговор“, то решете задачите:

...

Задача 4. Изведете списък с имената на тримата автори, които имат най-много ЗАГЛАВНИ статии в системата и към тях добавете имената на тримата автори, които имат най-много ОТГОВОРИ в системата. Резултатът от заявката би трябвало да изглежда като следната примерна таблица:

+----------------+-------------+---------------+
| Author         | Count       | Type          |
+----------------+-------------+---------------+
| Ivan           | 120         | Glavni        |
| Petar          | 90          | Glavni        |
| Maria          | 89          | Glavni        |
| Maria          | 560         | Otgovori      |
| Todor          | 499         | Otgovori      |
| Ivan           | 360         | Otgovori      |
+----------------+-------------+---------------+

Решение: Тук очевидно имаме две различни заявки, които трябва да изпълним - от една страна трябва да намерим тримата най-активни автори на заглавни статии, а от друга тримата най-активни автори на статии-отговори. Двете заявки всъщност са почти еднакви. Нека първоначално изведем само идентификационните номера на потребителите и броя статии:

-- за заглавните статии
SELECT author, COUNT(*) AS count
FROM posts
WHERE reply_to IS NOT NULL
GROUP BY author
ORDER BY count DESC
LIMIT 3;

-- за статиите-отговори
SELECT author, COUNT(*) AS count
FROM posts
WHERE reply_to IS NULL
GROUP BY author
ORDER BY count DESC
LIMIT 3;

Сега трябва да направим обединение (UNION) между двете резултатни таблици и да добавим колоната за "тип":

( SELECT author, COUNT(*) AS count,
         'Glavni' AS Type
  FROM posts
  WHERE reply_to IS NOT NULL
  GROUP BY author
  ORDER BY count DESC
  LIMIT 3
)
UNION ALL
(  SELECT author, COUNT(*) AS count,
          'Otgovori'
   FROM posts
   WHERE reply_to IS NULL
   GROUP BY author
   ORDER BY count DESC
   LIMIT 3
);

Получената таблица е почти това, което търсим. Остана да "заменим" идентификационния номер на автора (колоната author) с неговото име. Понеже името се пази в таблицата "users", то не ни остава друго, освен да я присъединим с JOIN:

( SELECT users.username AS author,
         t1.count AS count,
         t1.type AS type
  FROM(
     SELECT author, COUNT(*) AS count,
            'Glavni' AS Type
     FROM posts
     WHERE reply_to IS NOT NULL
     GROUP BY author
     ORDER BY count DESC
     LIMIT 3
  ) AS t1 
  JOIN users ON users.id = t1.author
)
UNION ALL
( SELECT users.username AS author,
         t1.count AS count,
         t1.type AS type
  FROM(
     SELECT author, COUNT(*) AS count,
            'Glavni' AS Type
     FROM posts
     WHERE reply_to IS NULL
     GROUP BY author
     ORDER BY count DESC
     LIMIT 3
  ) AS t1 
  JOIN users ON users.id = t1.author
)

С това решението на задачата е завършено.

П.П. Не съм убеден, че заявките са написани безгрешно - възможно е да има грешки, които да не позволят да се изпълнят. Не са изпробвани в реална среда с примерни данни. Когато имам възможност ще ги изпробвам и ще премахна евентуални неточности.