Zapytanie SQL

pytanie zadane 13 lipca 2018 w SQL, bazy danych przez Zero Dyskutant (8,210 p.)
edycja 13 lipca 2018 przez Zero

Cześć, siedzę juz pół dnia nad zapytaniami, nie bardzo wiem jak to ugryźć.

Mam takie tabele

Create table Wiezienie ( Id int not null primary key Identity(1,1), Nazwa Varchar(32));
Create table Wiezien ( Id int not null primary key Identity(1,1), Pesel Varchar(11) not null,
                        Imie Nvarchar(128), WiezienieId int,
                        foreign key (WiezienieId) References Wiezienie(Id));
Create table ProbaUcieczki ( Id int not null primary key Identity(1,1), Data DateTime2);
Create table WiezienProbaUcieczki (WiezienPesel Varchar(11) not null,
                                    ProbaUcieczkiId int not null, IsSuccess BIT,                                    
                                    foreign key (ProbaUcieczkiId) references ProbaUcieczki(Id));

Insert into Wiezienie Values ( 'Shawshank'), ('BootCamp');
Insert into Wiezien Values ('88987656786', 'John Doe', 1), ('71234545678', 'Will Smith', 1),
                    ('57487656723', 'Barack Obama', 2), ('56234545667', 'George Washington', 2);
Insert into ProbaUcieczki Values ('2016-11-11 22:21:23'), ('2017-01-01 3:32:12');
Insert into WiezienProbaUcieczki Values ('88987656786', 1, 1), ('57487656723', 2, 0);

Select * from Wiezienie
Select * from Wiezien
Select * from ProbaUcieczki
Select * from WiezienProbaUcieczki

Chce napisać zapytanie które zwróci liczbę ucieczek zakończonych sukcesem na każde wiezienie oraz
drugie zwracajace średnia liczbe prób ucieczek na wieźnia.
Czy mogę prosić o wytłumaczenie jak podchodzić do takich wielotabelowych selectów?

komentarz 13 lipca 2018 przez Mariusz08 Maniak (62,300 p.)

Kod bez bloczka

komentarz 13 lipca 2018 przez Zero Dyskutant (8,210 p.)

Dzięki za uwagę

2 odpowiedzi

odpowiedź 13 lipca 2018 przez Zero Dyskutant (8,210 p.)
edycja 13 lipca 2018 przez Zero

Czy to zapytanie jest poprawne?

Select Nazwa As Wiezienie, IsSuccess As UdaneUcieczki from Wiezienie, Wiezien, WiezienProbaUcieczki where
Wiezienie.Id=Wiezien.WiezienieId and Wiezien.Pesel = WiezienProbaUcieczki.WiezienPesel

lub

Select Nazwa As Wiezienie, IsSuccess As UdaneUcieczki from Wiezienie
inner join Wiezien on Wiezienie.Id=Wiezien.WiezienieId
inner join WiezienProbaUcieczki on Wiezien.Pesel = WiezienProbaUcieczki.WiezienPesel

Daje oczekiwany wynik, ale może jest lepsza metoda.

odpowiedź 15 lipca 2018 przez Catalonya1992 Mądrala (5,440 p.)

Pisałem z "palca" bez tych obiektów, ale powinno działać:

Pytanie 1:
Select w2.Nazwa nazwa_wiezienia, count(*) ilosc_ucieczek
from WiezienProbaUcieczki wpu
inner join Wiezien w on w.Pesel = wpu.WiezienPesel
inner join Wiezienie w2 on w2.Id= W.WiezienieId
where wpu.IsSuccess=1
group by w2.nazwa

Pytanie2:

Generalnie podstawą jest odpowiednie zaprojektowanie bazy. Jeśli coś zostanie skopane na etapie tworzenia modelu bazy to ciężko to później naprawić. Co do samych złączeń to polecam obejrzeć ten kurs:
https://www.youtube.com/watch?v=qlEBJE8UN-Q&list=PL8g9gILZp1OJpjlu7AuR9Ysu4z5Rff_AQ&index=7

komentarz 15 lipca 2018 przez Zero Dyskutant (8,210 p.)

Wielkie dzięki, już oglądam filmik. Z tego co rozumiem na temat projektowania baz danych to żeby dane się nie powtarzały trzeba tworzyć dużo tabel, co prowadzi do takich skomplikowanych Selectów.

komentarz 15 lipca 2018 przez Catalonya1992 Mądrala (5,440 p.)

Tak, ale weź pod uwagę, że może się zdarzyć tak, że bardziej optymalne, tzn. wydajniejsze będzie pisanie selecta z dużej tabeli niż łączenie jej joinami i wtedy rozdzielanie na tabele w bazie działa na niekorzyść, bo koszt złączenia tabel jest większy niż select z powtórzonych danych, np.

W tabeli pracownicy imie pracownika przechowujesz jako kolumnę imie a nie oddzielną tabelę z listą imion, tzn. akceptujesz fakt, że imiona mogą się powtarzać a mimo to nie tworzysz kodu imion jako kolumny i słownika (tabeli z lista imion).

Jeśli temat interesuje Cię bardziej to poczytaj o normalizacji i denormalizacji. Na newsletterze mówię o tym więcej :)