SQL

Będziesz korzystać z bazy danych publications. 

publications (1).sql Pobierz publications (1).sql 

Utwórz plik solutions.sql zawierający wszystkie komentarze potrzebne do zrozumienia Twojego kodu.

Link do zewnętrznej strony.Wyzwanie 1 - Najbardziej dochodowi autorzy

W tym wyzwaniu dowiesz się, kto jest top 3 najbardziej dochodowych autorów w bazie danych publications. Krok po kroku przewodniki pomogą Ci rozwinąć umiejętność rozwiązywania problemów.

Aby rozwiązać ten problem, ważne jest, abyś pamiętał o następujących punktach:

• W tabeli sales tytuł może pojawić się wielokrotnie. Należy obliczyć tantiemy dla każdej sprzedaży.

• Pomimo że tytuł może mieć wiele rekordów sales, zaliczka musi być obliczona tylko raz dla każdego tytułu.

• W ostatecznym rozwiązaniu musisz zsumować następujące zyski dla każdego autora:

  • Wszystkie zaliczki, które są obliczane dokładnie raz dla każdego tytułu.
  • Wszystkie tantiemy z każdej sprzedaży.

Dlatego nie będziesz mógł osiągnąć celu za pomocą pojedynczego zapytania SELECT, musisz użyć podzapytań. Zamiast tego, musisz wykonać kilka kroków, aby osiągnąć rozwiązanie. Poniżej znajduje się przegląd kroków:

1. Oblicz tantiemy z każdej sprzedaży dla każdego autora oraz zaliczkę dla każdego autora i publikacji.

2. Używając wyników z Kroku 1 jako podzapytania, zagreguj łączne tantiemy dla każdego tytułu i autora.

3. Używając wyników z Kroku 2 jako podzapytania, oblicz łączne zyski każdego autora, zagregowując zaliczki i łączne tantiemy każdego tytułu.

Poniżej przeprowadzimy Cię przez każdy krok. W pliku solutions.sql dołącz zapytania SELECT dla każdego kroku, aby Twój TA mógł przejrzeć Twój proces rozwiązywania problemu.

Krok 1: Oblicz tantiemy z każdej sprzedaży dla każdego autora oraz zaliczkę dla każdego autora i publikacji

Napisz zapytanie SELECT, które uzyska następujący wynik:

• ID tytułu
• ID autora
• Zaliczka dla każdego tytułu i autora
  • Wzór to:

    advance = titles.advance * titleauthor.royaltyper / 100
    
    

• Tantiemy z każdej sprzedaży
  • Wzór to:

    sales_royalty = titles.price * sales.qty * titles.royalty / 100 * titleauthor.royaltyper / 100
    
    

• Zwróć uwagę, że titles.royalty i titleauthor.royaltyper są podzielone przez 100, ponieważ są to wartości procentowe, a nie liczby zmiennoprzecinkowe.

W wynikach tego kroku, każdy tytuł może pojawić się więcej niż raz dla każdego autora. Wynika to z faktu, że tytuł może mieć więcej niż jedną sprzedaż.

Link do zewnętrznej strony.Krok 2: Zagreguj łączne tantiemy dla każdego tytułu i autora

Używając wyników z Kroku 1, napisz zapytanie zawierające podzapytanie, aby uzyskać następujący wynik:

• ID tytułu
• ID autora
• Zagregowane tantiemy każdego tytułu dla każdego autora
  • Podpowiedź: użyj funkcji SUM i grupuj po obu au_id i title_id

W wynikach tego kroku, każdy tytuł powinien pojawić się tylko raz dla każdego autora.

Link do zewnętrznej strony.Krok 3: Oblicz łączne zyski każdego autora

Teraz, gdy każdy tytuł ma dokładnie jeden wiersz dla każdego autora, w którym dostępne są zaliczki i tantiemy, jesteśmy gotowi uzyskać ostateczny wynik. Używając wyników z Kroku 2, napisz zapytanie zawierające dwa podzapytania, aby uzyskać następujący wynik:

• ID autora
• Zyski każdego autora, zagregowane z zaliczek i łącznych tantiem każdego tytułu

Posortuj wynik według łącznych zysków od najwyższych do najniższych i ogranicz liczbę wierszy do 3.

Link do zewnętrznej strony.Wyzwanie 2 - Alternatywne rozwiązanie

W poprzednim wyzwaniu opracowałeś rozwiązanie w następujący sposób:

• Użycie tabel pochodnych (podzapytań).(zobacz odnośnik do zewnętrznej strony)

Chcemy, abyś spróbował innego sposobu:

• Tworzenie tymczasowych tabel MySQL i wykonywanie zapytań na tych tabelach w kolejnych krokach.

Dołącz swoje alternatywne rozwiązanie do pliku solutions.sql.

Link do zewnętrznej strony.Dodatkowa nauka

W kontekście tego zadania, możesz używać zarówno tabel pochodnych, jak i tabel tymczasowych, aby opracować rozwiązanie. Możesz uznać, że pierwszy sposób jest wygodniejszy, ale oba mają swoje zastosowania. Tabele pochodne są przechowywane w pamięci podczas działania zapytania i znikają po jego zakończeniu. Tabele tymczasowe są fizycznie przechowywane w MySQL, choć tymczasowo. Dopóki sesja użytkownika nie wygasa, masz dostęp do danych w tabelach tymczasowych.

Jeśli dane w Twojej bazie często się zmieniają, korzystanie z tabel pochodnych może skutkować różnymi wynikami za każdym razem. Tabele tymczasowe, raz utworzone, przechowują dane do momentu ich usunięcia lub zamknięcia sesji. Jeśli zależy Ci na aktualności wyników, używaj tabel pochodnych. Jeśli jednak Twoje dane są duże i zapytania skomplikowane, korzystanie z tabel tymczasowych może znacznie poprawić wydajność, ponieważ kosztowne obliczenia wykonują się tylko raz, a wyniki są trwałe. Przy wielokrotnym odwoływaniu się do tabel tymczasowych, unikasz powtarzania kosztownych transakcji.

Link do zewnętrznej strony.Wyzwanie 3

Rozwijając rozwiązanie z Wyzwania 1 i 2, utwórz tabelę trwałą o nazwie most_profiting_authors, która będzie przechowywać dane o najbardziej dochodowych autorach. Tabela powinna mieć 2 kolumny:

• au_id - ID autora
• profits - Zyski autora, zagregowane z zaliczek i tantiem

Dołącz swoje rozwiązanie do pliku solutions.sql.

Link do zewnętrznej strony.Dodatkowa nauka

Aby zrównoważyć wydajność transakcji bazodanowych i aktualność danych, inżynierowie oprogramowania/danych często planują automatyczne skrypty do okresowego zapytywania danych i zapisywania wyników w trwałych tabelach podsumowujących. W ten sposób, gdy jest to potrzebne, można pobrać dane z tych tabel, zamiast powtarzać kosztowne transakcje. Wyniki mogą być nieco nieaktualne, ale dostęp do nich jest natychmiastowy.