Laboratorium 3 z Baz Danych

Na drugim laboratorium powtórzymy proces importowania bazy danych oraz przećwiczymy tworzenie zapytań w SQL

  1. Zasady zaliczania laboratorium
  2. Powtórka - Importowania bazy danych do mysql-a
  3. Dwa przykład zapytań -

Zasady zaliczania

Ocena składa się z dwóch elementów

  1. Projektu wykorzystującego bazy danych (4 tygodnie) (50%)
  • pomysł na bazę (sklep, usos, sieć społecznościowa, firma)
  • zaprojektowanie bazy danych (jakie tabele, klucze)
  • zaprojektowanie tego, co będzie udostępniane użytkownikowi
  • stworzenie graficznego interfejsu (np. w Python-ie, eRze lub w OpenOffice)
  1. Zadania do rozwiązania w trakcie laboratoriów (50%)
Punkty Ocena
<50% 2
50-60% 3
60-70% 3.5
70-80% 4
80-90% 4.5
>90% 5

Importowanie bazy w trybie wsadowym (Batch mode)

  1. Ściągamy plik ratings.sql z ze strony im.pwr.wroc.pl~sobczyk
  2. Uruchamiamy konsolę i poleceniem cd przechodzimy do katalogu, gdzie znajduje się plik z bazą

  3. Importujemy ją do mysql

    "scieżka_do_pliku\mysql.exe" -t < ratings.sql

  4. Proszę sobie przypomnieć dlaczego powyższe polecenie nie działa. Jaki jest błąd? Jak to naprawic?

Zadania do wykonania (w DataGrip)

Powtarzamy procedurę z zeszłego tygodnia. Wybieramy new source, typ bazy to mysql

Dane do połączenia

  • host:localhost
  • database: ratings
  • user: nazwa_uzytkownika
  • password: hasło_uzytkownika

Jak wygląda nasza baza danych? Jeśli otworzymy panel z bazami (Alt-1) to możemy kliknąć prawym przyciskiem myszy na naszą bazę i wybrać Diagram. Alternatywnie możemy użyć skrótu klawiszowego Ctrl-Alt-Shift-U.

Przykład

Znajdź wszystkie lata, z których pochodzą filmy, które otrzymały co najmniej jedną ocenę 4 lub 5, i posortuj je rosnąco

Dzielimy zapytanie na mniejsze części

SELECT * FROM Rating
    WHERE stars BETWEEN 4 AND 5;

Wynik tego zapytania jest tabelą. Aby połączyć ją z Movie, trzeba ją zaliasować (nadać nazwę). Podobnie robiliśmy wcześniej z kolumnami

SELECT * FROM (Movie INNER JOIN
  (SELECT * FROM Rating
    WHERE stars BETWEEN 4 AND 5) AS HighRating
    ON Movie.mID = HighRating.mID)

Później łatwo jest już wybrać lata (bez duplikatów) i uszeregować je.

SELECT DISTINCT year FROM (Movie INNER JOIN
  (SELECT * FROM Rating
    WHERE stars BETWEEN 4 AND 5) AS HighRating
    ON Movie.mID = HighRating.mID)
ORDER BY year;

Przykład

Ratingi wystawione plus/minus dwa dni od 20 stycznia 2011

SELECT * FROM Rating
              WHERE ABS(DATEDIFF(ratingDate, MAKEDATE(2011,20)))<=2;

Przykład

Wszystkie osoby, które oceniły film Gone with the Wind

SELECT DISTINCT Reviewer.name FROM (Reviewer INNER JOIN
  (SELECT rID FROM Movie INNER JOIN Rating ON Movie.mID=Rating.mID
    WHERE Movie.title="Gone with the Wind") AS RatingGWTW ON Reviewer.rID = RatingGWTW.rID);

Proszę skonstruować zapytania w SQL odpowiadające na następujące pytania w języku polskim.

Jako rozwiązanie proszę przesłać (do końca dzisiejszych zajęć) plik o tytule: {numer_indeksu}_laboratorium3.sql np. 271828_laboratorium3.sql

Plik ma być w następującym formacie

#zadanie 1
ZAPYTANIE SQL1

#zadanie2
ZAPYTANIE SQL2

...

#zadanie6
ZAPYTANIE SQL6

Państwa zapytania będą wykonywane w sposób automatyczny. Jeśli nie będą Państwo przestrzegali powyższego schematu to będą Państwu odejmowane punkty. W szczególności źle nazwany plik nie będzie sprawdzany. Przykład poprawnego pliku

Zadanie 1 (1 pkt)

Znajdź tytuły wszystkich filmów reżyserowanych przez Steven Spielberga i wypisz je małymi literami.

Zadanie 2 (2 pkt)

Znajdź filmy, dla których średnia liczba gwiazdek jest większa niż \(3.5\). W wyniku podaj nazwę filmu i średnią liczbę gwiazdek posortowane rosnąco ze względu na liczbę gwiazdek.

Zadanie 3 (2 pkt)

Znajdź maksymalną liczbę gwiazdek jakie otrzymały poszczególne filmy. W wyniku podaj nazwę filmu i maksymalną liczbę gwiazdek. Wyniki posortuj po nazwie filmu. Nie uwzględniaj filmów, które nie były ani razu ocenianie.

Zadanie 4 (3 pkt)

Znajdz imiona i nazwiska (name) oceniających (reviewers), dla których istnieje ocena (krotka w Rating) bez daty jej wystawienia

Zadanie 5 (2 pkt)

Napisz zapytanie, które zwróci ratingi w bardzie czytelnej postaci, tj w następującym formacie: nazwa oceniającego, nazwa filmu, ocena, data oceny Posortuj dane po nazwie oceniającego, dalej po nazwie filmu i na końcu po ocenie.

Zadanie 6 (2 pkt)

Znajdź nazwy filmów, które nie mają żadnego ratingu