Zestaw szkoleniowy 2 – Select i złączenia (sql joins)

Wykonując zadania z zestawu szkoleniowego 1, można było się nauczyć podstaw zapytań SQL,
w zestawie drugim – ćwiczenia związane są z łączeniem danych z różnych tabel. By zachować maksymalną przejrzystość w przykładach zostaną użyte tylko 2 tabele.
Wykonanie poniższego skryptu utworzy odpowiednie tabele i wprowadzi odpowiednie dane, przed wykonaniem skryptu proszę się upewnić że takie struktury nie istnieją w bazie (jeśli wcześniej na tej bazie były wykonywane ćwiczenia z postu o kluczu obcym takie obiekty mogą już tam istnieć).

use zajecia
create table klienci
(klient_id int not null primary key identity(1,1),
klient_imie varchar(255),
klient_data datetime default getdate()
)

create table adresy (
adres_id int not null primary key identity(1,1),
adres_ulica varchar(255),
adres_miejscowosc varchar(255),
adres_klient_id int,
adres_typ int
)

insert into klienci (klient_imie) values ('Krzys');
insert into klienci (klient_imie) values ('Adam');
insert into klienci (klient_imie) values ('Andrzej');
insert into klienci (klient_imie) values ('Marek');
insert into klienci (klient_imie) values ('Ania');
insert into klienci (klient_imie) values ('Marysia');
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Poznan','Poznanska',1,1);
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Warszawa','Warszawska',2,1);
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Gniezno','Dworcowa',1,2);
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Krakow','Glowna',1,3);
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Gdansk','Bursztynowa',1,4)
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Olsztyn','Polska',1,5)
delete from klienci where klient_id = 5

Po wykonaniu tego skryptu proszę sprawdzić czy wyniki poniższych zapytań są zgodne:
select * from adresy;
select * from klienci

Jeśli wszystko się zgadza proszę wykonać poniższe ćwiczenia. W przypadku wątpliwości zapraszam do lektury:

Zadania

1) Pokaż w jakich miejscowościach mieszkają klienci (kolumny: klient_imie, adres_miejscowosc)
2) Pokaż w jakich miejscowościach klienci posiadają adresy typu 1 (kolumny: klient_imie, adres_miejscowosc)
3) Pokaż dla jakich adresów nie ma przypisanych klientów ( przygotuj min. 2 rozwiązania)
4) Pokaż dla jakich klientów nie ma przypisanych adresów ( przygotuj min. 2 rozwiązania)
5) Pokaż w jednej kolumnie imiona i nazwy miejscowości dla wszystkich klientów
6) Dlaczego klient z id=6 w poprzednim zadaniu wyświetlił się jako null? Jak to obejść?
7) Pokaż klientów którzy posiadają więcej niż 1 adres
8) pokaż klientów którzy posiadają tylko i wyłącznie 1 adres
9) Podziel klientów na grupy ze względu na pierwszą literę imienia i policz ile przypada adresów na każdą z grup.

Odpowiedzi:
1) Pokaż w jakich miejscowościach mieszkają klienci (kolumny: klient_imie, adres_miejscowosc)
select klient_imie,adres_miejscowosc from klienci
inner join adresy on
adres_klient_id=klient_id


Pozostaje jeszcze kwestia wyjaśnienia dlaczego Krzyś pokazał się dwa razy:
Dodając kolumny do zapytania:
select klient_imie,adres_miejscowosc,klient_id,adres_klient_id,adres_id from klienci
inner join adresy on
adres_klient_id=klient_id


Widać wyraźnie iż Krzys ma przypisane 2 różne adresy.

2) Pokaż w jakich miejscowościach klienci posiadają adresy typu 1 (kolumny: klient_imie, adres_miejscowosc)
select klient_imie,adres_miejscowosc from klienci
inner join adresy on
adres_klient_id=klient_id
where adres_typ = 1


Lub można też użyć innego zapisu:
select klient_imie,adres_miejscowosc from klienci
inner join adresy on
adres_klient_id=klient_id and adres_typ = 1

wynik zapytania będzie dokładnie taki sam.

3) Pokaż dla jakich adresów nie ma przypisanych klientów ( przygotuj min. 2 rozwiązania)
Poniżej rozwiązania korzystające ze złączeń:
select adres_miejscowosc,klient_id from adresy
left outer join klienci on
adres_klient_id = klient_id
where klient_id is null

Lub można na odwrót, korzystając ze złączenia prawostronnego:
select adres_miejscowosc,klient_id from klienci
right outer join adresy on
adres_klient_id = klient_id
where klient_id is null

Podane rozwiązania bazują na złączeniach, jednak proszę być świadomym że takie zadanie da się rozwiązać jeszcze inaczej.

4) Pokaż dla jakich klientów nie ma przypisanych adresów ( przygotuj min. 2 rozwiązania)
To zadanie jest analogiczne do poprzedniego.
select klient_imie,adres_miejscowosc from klienci
left outer join adresy on
adres_klient_id = klient_id
where adres_id is null

I tak samo jak poprzednie zadanie można je wykonać z pomocą złączenia prawostronnego:
select klient_imie,adres_miejscowosc from adresy
right outer join klienci on
adres_klient_id = klient_id
where adres_id is null

5) Pokaż w jednej kolumnie imiona i nazwy miejscowości dla wszystkich klientów
select adres_miejscowosc + ' ' + klient_imie from klienci
left outer join adresy on
adres_klient_id = klient_id

6) Dlaczego klient z id=6 w poprzednim zadaniu wyświetlił się jako null? Jak to obejść?
W poprzednich wynikach widać że ostatni wiersz wyświetlił się jako null, jest to spowodowane operacją konkatenacji łańcucha z wartością null (Imię + null) = null.

7) Pokaż klientów którzy posiadają więcej niż 1 adres
select klient_imie from klienci
inner join adresy on
adres_klient_id=klient_id
group by klient_imie
having count(*) > 1

8) pokaż klientów którzy posiadają tylko i wyłącznie 1 adres
select klient_imie from klienci
inner join adresy on
adres_klient_id=klient_id
group by klient_imie
having count(*) =1

9) Podziel klientów na grupy ze względu na pierwszą literę imienia i policz ile przypada adresów na każdą z grup.
select LEFT(klient_imie,1),COUNT(*) from klienci
inner join adresy on
adres_klient_id=klient_id
group by LEFT(klient_imie,1)

Zestaw szkoleniowy 1 – SQL – Podstawy SELECT

Postanowiłem udostępnić dość prosty zestaw szkoleniowy, który powinien pomóc w pierwszych chwilach z bazami danych. Zestaw jest przygotowany dla bazy SQL SERVER, a zadania są weryfikowane w środowisku SQL Server 2008R2.


create database zajecia;
use zajecia;
create table przyjaciele (
id int not null primary key identity(1,1), -- unikalny id - pole numeryczne
imie varchar(255), -- pole tekstowe
plec int, -- 0 dziewczyna 1 facet -- wartosc numeryczna
wiek int, -- wiek w latach -- wartosc liczbowa
zarobki int, -- zarobki w tysiacach -- wartosc liczbowa
kolor int) -- kolor skory 0 jasny 10 ciemny - skala -- wartosc liczbowa

--wstawienie danych do tabeli:
insert into przyjaciele (imie,plec,wiek,zarobki,kolor)
values ('Janek',1,19,2000,0);
insert into przyjaciele (imie,plec,wiek,zarobki,kolor)
values ('Ania',0,29,5000,0);
insert into przyjaciele (imie,plec,wiek,zarobki,kolor)
values ('Krysia',0,25,3000,0);
insert into przyjaciele (imie,plec,wiek,zarobki,kolor)
values ('Marek',1,25,3500,0);
insert into przyjaciele (imie,plec,wiek,zarobki,kolor)
values ('Krzysztof',1,32,1400,0);
insert into przyjaciele (imie,plec,wiek,zarobki,kolor)
values ('Marysia',0,21,1200,0);

Powyższy kod tworzy bazę zajecia, oraz dodaje do niej jedną tabelę o nazwie przyjaciele. Do utworzonej tabeli ładowane są przykładowe dane. Polecenia te można wkleić do okna zapytań w SSMS i wykonać (przycisk F5 lub execute).

po wykonaniu tych zapytań możemy otworzyć nowe query window (należy się upewnić czy korzystamy z bazy zajęcia) i można wykonać zapytanie:

select * from przyjaciele

Powyżej wynik zapytania.
Teraz lista zadań:
1) Pokaż tylko imiona.
2) Pokaż imiona zapisane Wielkimi literami
3) Pokaż 1 literę imienia i nadaj alias kolumnie pt PierwszaLitera
4) Pokaż pierwszą i ostatnią literę imienia
5) Pokaż imiona osób które zarabiają mniej niż 2000
6) Pokaż wszystkie kolumny w tabeli posortowane po zarobkach
7) Pokaż wiersze w których imiona kończą się na literę ‘a’
8) Pokaż wiersze w których imiona nie kończą się na literę ‘a’ oraz zarobki są mniejsze niż 2000
9) Pokaż minimalne zarobki, maksymalne, średnią.
10) pokaż średnie zarobki w rozbiciu na płeć.

Poniżej odpowiedzi:

1) Pokaż tylko imiona.
select imie from przyjaciele

2) Pokaż imiona zapisane Wielkimi literami

select UPPER(imie) from przyjaciele


3) Pokaż 1 literę imienia i nadaj alias kolumnie pt PierwszaLitera
select LEFT(imie,1) PierwszaLitera from przyjaciele
lub
select LEFT(imie,1) as PierwszaLitera from przyjaciele
lub
select SUBSTRING(imie,1,1) as PierwszaLitera from przyjaciele

4) Pokaż pierwszą i ostatnią literę imienia

select LEFT(imie,1) + RIGHT(imie,1) from przyjaciele

5) Pokaż imiona osób które zarabiają mniej niż 2000
select imie from przyjaciele where zarobki < 2000

6) Pokaż wszystkie kolumny w tabeli posortowane po zarobkach
select * from przyjaciele order by zarobki

7) Pokaż wiersze w których imiona kończą się na literę ‘a’
select * from przyjaciele where imie like '%a'
lub
select * from przyjaciele where right(imie,1) = 'a'

8) Pokaż wiersze w których imiona nie kończą się na literę ‘a’ oraz zarobki są mniejsze niż 2000
select * from przyjaciele where imie not like '%a' and zarobki < 2000
lub
select * from przyjaciele where RIGHT(imie,1)!='a' and zarobki < 2000

9) Pokaż minimalne zarobki, maksymalne, średnią.
select MIN(zarobki),MAX(zarobki), AVG(zarobki) from przyjaciele

10) pokaż średnie zarobki w rozbiciu na płeć.
select AVG(zarobki),plec from przyjaciele
group by plec

weryfikacja numeru identyfikacji podatkowej – NIP

W wielu bazach danych stajemy przed problemem weryfikacji różnych numerów używanych w administracji, opisałem już jak weryfikować PESEL.
Poniżej opis algorytmu weryfikacji NIP (numer identyfikacji podatkowej). Podstawowe informacje o weryfikacji numeru można znaleźć w wikipedii.
Dodam jednak kilka słów od siebie.
Swego czasu NIP’y podawało się z kreskami(myślnikami), nie wnoszą one kompletnie nic do procesu walidacji, więc funkcja je usuwa ze sprawdzanego ciągu znaków. Oczywiście NIP musi być ciągiem numerycznym i zawierać odpowiednią ilość znaków. W dodatku warto uczulić się na wartości skompromitowane które mogą mimo wszystko przechodzić przez walidację, ale mamy 100% pewność że nie istnieją. Są to numery które zaczynają się na 0 bądź posiadają 2 cyfrę w numerze = 0 i pierwszą różną od 1. Czyli NIP zaczynający się 20 jest na pewno niepoprawny mimo iż suma kontrolna może być poprawna.
Suma kontrolna w numerze NIP to suma mnożników dzielona przez 11.

Funkcja zwraca:
1 gdy NIP jest poprawny,
0 w przypadku złej sumy kontrolnej
-2 gdy wartość jest skompromitowana
-3 gdy wartość nie jest numeryczna
-4 gdy długość ciągu jest różna od 10
-5 gdy ciąg jest nieokreślony (null)

CREATE FUNCTION [dbo].[is_nip]
(
— Add the parameters for the function here
@nip varchar(255)
)
RETURNS int
AS
BEGIN

if @nip is null begin return -5 end

set @nip = replace(@nip,’-‘,”)
— zla dlugosc numeru nip
if len(@nip) != 10 begin return -4 end

if ISNUMERIC(@nip) = 0 begin return -3 end

— zwroc wartosci skompromitowane
if @nip=’0000000000′ begin return -2 end
if @nip=’1234567891′ begin return -2 end
if @nip=’1111111111′ begin return -2 end
if @nip=’1111111112′ begin return -2 end
if @nip=’9999999999’ begin return -2 end
if @nip like ‘0%’ begin return -2 end
if @nip like ‘_0%’ and @nip not like ‘1%’ begin return -2 end

if (
( ( cast(substring(@nip,1,1) as bigint)*6)
+(cast(substring(@nip,2,1) as bigint)*5)
+(cast(substring(@nip,3,1) as bigint)*7)
+(cast(substring(@nip,4,1) as bigint)*2)
+(cast(substring(@nip,5,1) as bigint)*3)
+(cast(substring(@nip,6,1) as bigint)*4)
+(cast(substring(@nip,7,1) as bigint)*5)
+(cast(substring(@nip,8,1) as bigint)*6)
+(cast(substring(@nip,9,1) as bigint)*7)
) % 11
= right(@nip,1) )
begin
return 1;
end

return 0;

END