1.  Utworzyć trzy tabele:
--Zadanie 1a
CREATE TABLE dom(
id_domu NUMERIC(5) PRIMARY KEY,
miasto VARCHAR(40) NOT NULL,
ulica VARCHAR(60) NOT NULL,
metraz NUMERIC(5,2) NOT NULL,
cena_proponowana NUMERIC (9,2));



--Zadanie 1b
CREATE TABLE klient(
id_klienta NUMERIC (5) PRIMARY KEY,
imie VARCHAR(16) NOT NULL,
nazwisko VARCHAR(40) NOT NULL,
miasto VARCHAR(40) NULL,
ulica VARCHAR(60) NULL);


--zadanie 1c
CREATE TABLE transakcja (
--identity(1,1) to autonumeracja od 1 co 1
id_transakcji NUMERIC(5) IDENTITY(1,1) PRIMARY KEY,
cena_uzyskana NUMERIC (9,2) NOT NULL,
data_transakcji DATE NOT NULL,
id_klienta_fk NUMERIC(5) FOREIGN KEY (id_klienta_fk)
REFERENCES klient(id_klienta),
id_domu_fk NUMERIC(5) FOREIGN KEY (id_domu_fK)
REFERENCES dom(id_domu));

--zadanie 2. wypelnic tabele po 5 wierszy
INSERT INTO dom VALUES
(1,'Sopot','Armii Krajowej 1',90.1,1200000),
(2,'Gdańsk','Grunwaldzka 10',17,200000),
(3,'Gdynia','Morska 13',70.2,NULL),
(4,'Warszawa','Marszałkowska 8',30.5,1000000),
(5,'Lądek Zdrój','Słowackiego 10',135.92,50000);

INSERT INTO klient VALUES
(1,'Jan','Nowak','Gdynia','Zwycięstwa 4'),
(2,'Marcin','Lewandowski','Radom','Kolejowa 1'),
(3,'Anna','Kwiatkowska','Łódź','Dworcowa 3'),
(4,'Piotr','Wiśniewski','Londyn','Victoria Street 5'),
(5,'Katarzyna','Ostrowska','Katowice','Górnicza 8');

INSERT INTO transakcja
(id_klienta_fk, id_domu_fk, cena_uzyskana, data_transakcji)
VALUES
(1,1,1100000, getdate()),
(1,2,180000, getdate()),
(2,3,300000,'2017-10-02'),
(3,4,900000,'2017-08-25'),
(3,5,110000,'2017-03-12');

SELECT COUNT(*) FROM dom;
SELECT COUNT(*) FROM klient;
SELECT COUNT(*) FROM transakcja;

--3.    Wyświetlić wszystkie domy.
SELECT *FROM dom;

--4.    Wyświetlić imiona i nazwiska klientów w porządku alfabetycznym rosnącym według nazwy miasta.

SELECT imie,nazwisko,miasto FROM klient
ORDER BY miasto;

--5.    Wyświetlić ulice i miasta dla domów  w porządku alfabetycznym malejącym według metrażu.
SELECT ulica, miasto,metraz FROM dom
ORDER BY metraz DESC;

--6.    Wyświetlić wszystkie domy o metrażu mniejszym niż 200 metrów.
SELECT *FROM dom WHERE metraz<100;

--7.    Wyświetlić wszystkie transakcje jakie miały miejsce, wyświetlając wszystkie dane o klientach, domach i transakcjach bez wartości kluczy obcych i głównych.
SELECT k.imie, k.nazwisko,k.miasto,k.ulica,
d.miasto,d.ulica,d.metraz,d.cena_proponowana,
t.cena_uzyskana,t.data_transakcji
FROM klient k, dom d, transakcja t
WHERE t.id_klienta_fk=k.id_klienta
AND t.id_domu_fk=d.id_domu;
-- !! POWYŻSZE TO ILOCZYN KARTEZJAŃSKI !! --

--8.    Wyświetlić miasta, nazwiska oraz imiona klientów, sortując według miasta malejąco i według nazwiska rosnąco oraz imienia malejąco.
SELECT miasto AS 'Miejscowośc',
nazwisko AS 'Nazwisko Klienta', imie AS 'Imię Klienta' , nazwisko + ' ' + imie AS 'Nazwisko i imię'
FROM klient
ORDER BY miasto DESC, nazwisko, imie DESC;

--9. Wyświetlić wszystkich klientów, którzy mieszkają w Gdyni.
SELECT * FROM klient WHERE miasto = 'Gdynia'

--10. Wyświetlić cenę proponowaną wszystkich domów znajdujących się w Gdańsku.
SELECT cena_proponowana, miasto
FROM dom
WHERE miasto = 'gdańsk'

--11. Wyswietlić ceny proponowane domów, które znajdują się na ulicach Grunwaldzkiej i Toruńskiej w Gdańsku.
SELECT cena_proponowana, miasto, ulica
FROM dom
WHERE (ulica LIKE 'Grunwaldzka%' OR ulica LIKE 'Toruńska%')
AND miasto ='Gdańsk'

--12. Wyświetlić ceny proponowane wszystkich domów w miastach Gdańsk i Gdynia, w porządku malejącym według ceny proponowanej.
SELECT cena_proponowana, miasto
FROM dom
--where miasto='Gdańsk' or miasto='Gdynia'
WHERE miasto IN('Gdańsk','Gdynia')
ORDER BY cena_proponowana DESC;

--13. Wyświetlić wszystkie miasta w których znajdują się domy dostępne w sprzedaży, nie duplikując nazw miast.
SELECT DISTINCT miasto
FROM dom
WHERE id_domu NOT IN (SELECT id_domu_fk FROM transakcja);

INSERT INTO dom VALUES
(7,'Wejherowo','Pomorska',30,160000)

--14. Wyświetlić wszystkie transakcje domów, których cena uzyskana kształtuje się w zakresie pomiędzy 200000 a 500000 złotych.
SELECT * FROM transakcja
WHERE cena_uzyskana BETWEEN 200000 AND 500000

--15. Wyświetlić wszystkie transakcje dla których identyfikator domu jest mniejszy niż 15.
SELECT * FROM transakcja
WHERE id_domu_fk<15;

--16. Wyświetlić wszystkie transakcje dla których identyfikator domu jest równy 4, 13 lub 19.
SELECT * FROM transakcja
WHERE id_domu_fk IN(4,13,19);

--17. Wyświetlić wszystkie domy dla których nie podano ceny proponowanej.
SELECT * FROM dom
WHERE cena_proponowana IS NULL;

--18. Wyświetlić wszystkie domy dla identyfikatora powyżej 2, których nazwa ulicy zawiera literę w.
SELECT * FROM dom
WHERE id_domu>2 AND ulica LIKE '%w%';

--19. Wyświetlić nazwiska wszystkich osób, które dokonały transakcji zakupu domu.
SELECT nazwisko FROM klient
WHERE id_klienta IN (SELECT id_klienta_fk FROM transakcja)

--20. Zakładając dodatkowy podatek na zakup nieruchomości w wysokości 15% wyświetlić listę proponowanych cen powiększoną o tę wartość.
SELECT *, cena_proponowana*1.15 AS 'cena po podatku' FROM dom

--21. Rozbudować poprzednie zapytanie wyświetlając trzy kolumny – cena bez podatku, cena z podatkiem, wartość podatku.
SELECT cena_proponowana,cena_proponowana*1.15 AS 'cena po podatku', cena_proponowana*0.15 AS 'wartosc podatku' FROM dom

--22. Obliczyć średnią cenę proponowaną metra kwadratowego domu.
SELECT CAST(round(avg(cena_proponowana/metraz),2)AS money) AS 'srednia cena m2' FROM dom

--23. Wyświetlić tylko te transakcje, które miały miejsce w 2017 roku, podając nazwę miasta, imię i nazwisko klienta oraz cenę proponowaną i uzyskaną.
SELECT k.imie, k.nazwisko, d.miasto, d.cena_proponowana, t.cena_uzyskana, t.data_transakcji
FROM dom d, klient k, transakcja t
WHERE d.id_domu=t.id_domu_fk AND k.id_klienta=t.id_klienta_fk AND YEAR(t.data_transakcji)=2017

--24. Zakładając, że domy poniżej 50 metrów będą traktowane jako dwupokojowe, do 100 metrów trzypokojowe a powyżej czteropokojowe, dodać kolumnę liczba pokoi, która obok pełnych informacji o domu poda jego wielkość w pokojach (odpowiednio dwupokojowy, trzypokojowy, czteropokojowy).
SELECT *,
CASE
WHEN metraz<50 THEN 'dwupokojowe'
WHEN metraz<100 THEN 'trzypokojowe'
ELSE 'czteropokojowe'
END AS 'Liczba pokoi'
FROM dom;

--25. Wyświetlić wszystkie transakcje sprzedaży domów, które miały miejsce poza Trójmiastem.
SELECT * FROM transakcja t, dom d
WHERE d.id_domu=t.id_domu_fk
AND d.miasto NOT IN ('Gdańsk','Gdynia','Sopot')

--26. Wyświetlić w dwóch kolumnach – w pierwszej identyfikatory oraz w drugiej kolumnie imiona i nazwiska klientów.
SELECT id_klienta AS 'Identyfikator', imie+ ' ' + nazwisko AS 'Imię i Nazwisko'
FROM klient

--27. Wyświetlić identyfikatory wszystkich niesprzedanych domów.
SELECT id_domu FROM dom d
WHERE id_domu NOT IN (SELECT id_domu_fk FROM transakcja)

/*insert into transakcja values
(380000,getdate(),6,6)
select * from dom where id_domu not in (select id_domu_fk from transakcja)
select * from transakcja*/

--28. Wyświetlić transakcje klientów, których nazwiska rozpoczynają się na literę A, B, C, D, E, F lub G.
--Zadanie 28. SOLUTION ONE
SELECT * FROM transakcja t, klient k
WHERE t.id_klienta_fk=k.id_klienta AND
(nazwisko LIKE 'A%'
OR nazwisko LIKE 'B%'
OR nazwisko LIKE 'C%'
OR nazwisko LIKE 'D%'
OR nazwisko LIKE 'E%'
OR nazwisko LIKE 'F%'
OR nazwisko LIKE 'G%'
)

--ZADANIE 28 SOLUTION TWO
SELECT * FROM transakcja t, klient k
WHERE t.id_klienta_fk=id_klienta AND LEFT(nazwisko,1) BETWEEN 'A' AND 'G'

--29. Wyświetlić w kolumnie o nazwie wartość różnicę w postaci wartości bezwzględnej, pomiędzy ceną proponowaną a uzyskaną, dodając kolumny zawierające nazwy miast domów, ich identyfikatory oraz ulice.
SELECT d.miasto,d.id_domu, d.ulica, abs(d.cena_proponowana-t.cena_uzyskana) AS 'Wartość' FROM transakcja t, dom d
WHERE d.id_domu=t.id_domu_fk

--ZADANIE 30
SELECT * FROM transakcja t, dom d
WHERE d.id_domu=t.id_domu_fk AND
cena_uzyskana-cena_proponowana>0

--ZADANIE 31
SELECT data_transakcji AS 'Data transakcji', CAST(getdate() AS DATE) AS 'Dzisiaj' , datediff(MM,data_transakcji,getdate()) AS 'Różnica w miesiącach' FROM transakcja

--ZADATE 32
SELECT COUNT(id_transakcji) AS 'Liczba transakcji' FROM transakcja

--ZADANIE 33
SELECT CAST(cena_proponowana AS VARCHAR) AS 'Wartość' FROM dom

--ZADANIE 34
SELECT SUM(cena_uzyskana) AS 'Suma transakcji' FROM transakcja

--ZADANIE 35
SELECT MIN(cena_uzyskana) AS 'Cena minimalna' FROM transakcja

--ZADANIE 36
SELECT MAX(cena_uzyskana) AS 'Cena maksymalna' FROM transakcja

--ZADANIE 37
SELECT miasto, ulica, CAST(round(MAX(t.cena_uzyskana/d.metraz),2) AS money) AS 'Cena maksymalna m2' FROM transakcja t, dom d
WHERE d.id_domu=t.id_domu_fk
GROUP BY miasto,ulica

--ZADANIE 38
SELECT miasto,  CAST(round(avg(cena_proponowana),2)AS money) FROM dom
GROUP BY miasto

--ZADANIE 39
SELECT datename(dy,data_transakcji) FROM transakcja

--ZADANIE 40

SELECT datename(DW,data_transakcji) AS 'dzien tygodnia'
INTO #dzien_tygodnia
FROM transakcja
SELECT * FROM #dzien_tygodnia

--ZADANIE 41
SELECT [Dzien tygodnia], COUNT(*) AS 'liczba transakcji'
FROM #dzien_tygodnia
GROUP BY [dzien tygodnia];

--ZADANIE 42
SELECT k.id_klienta, SUM(t.cena_uzyskana) AS 'cena sum uzyskanych'
FROM klient k,transakcja t
WHERE k.id_klienta=t.id_klienta_fk
GROUP BY k.id_klienta

--ZADANIE 43
SELECT miasto,
round(AVG(cena_uzyskana/metraz),5) AS 'Średnia'
FROM transakcja, dom
GROUP BY miasto
--ZADANIE 43
SELECT d1.miasto,
round(AVG(d1.cena_proponowana/d1.metraz),-4) AS 'Średnia',
d2.miasto,
round(AVG(d2.cena_proponowana/d2.metraz),-4) AS 'Średnia'
FROM dom d1, dom d2
GROUP BY d1.miasto, d2.miasto
HAVING round(AVG(d1.cena_proponowana/d1.metraz),-4) <> round(AVG(d2.cena_proponowana/d2.metraz),-4) AND d1.miasto<d2.miasto

--ZADANIE 44

SELECT miasto, nazwisko
FROM klient
WHERE miasto LIKE 'Gd%';

--ZADANIE 45
SELECT nazwisko, data_transakcji, metraz
FROM klient LEFT JOIN transakcja ON klient.id_klienta=transakcja.id_klienta_fk
LEFT JOIN dom ON transakcja.id_domu_fk=dom.id_domu

SELECT nazwisko, data_transakcji, metraz
FROM klient RIGHT JOIN transakcja ON klient.id_klienta=transakcja.id_klienta_fk
LEFT JOIN dom ON transakcja.id_domu_fk=dom.id_domu

SELECT nazwisko, data_transakcji, metraz
FROM klient JOIN transakcja ON klient.id_klienta=transakcja.id_klienta_fk
LEFT JOIN dom ON transakcja.id_domu_fk=dom.id_domu

--ZADANIE 46 v1
SELECT DISTINCT d1.miasto, d2.miasto
FROM dom d1, dom d2
WHERE LEFT(d1.miasto,2)=LEFT(d2.miasto,2)
AND d1.miasto<d2.miasto

--ZADANIE 46 v2
SELECT DISTINCT d1.miasto, d2.miasto
FROM dom d1, dom d2
WHERE SUBSTRING(d1.miasto,1,2)=SUBSTRING(d2.miasto,1,2)
AND d1.miasto<d2.miasto


--ZADANIE 47
SELECT * FROM dom
WHERE metraz> ALL
(SELECT metraz FROM dom WHERE miasto = 'Sopot' );

--ZADANIE 48
SELECT d.*, k.nazwisko
FROM dom d, klient k, transakcja t
WHERE k.id_klienta=t.id_klienta_fk
AND d.id_domu=t.id_domu_fk
AND k.id_klienta IN
(SELECT id_klienta FROM dom d, klient k, transakcja t
WHERE k.id_klienta=t.id_klienta_fk AND d.id_domu=t.id_domu_fk
GROUP BY id_klienta
HAVING COUNT(*)>=2)

--ZADANIE 49
SELECT d.*, k.*
FROM dom d, klient k, transakcja t
WHERE k.id_klienta=t.id_klienta_fk
AND d.id_domu=t.id_domu_fk
AND k.id_klienta IN
(SELECT id_klienta FROM dom d, klient k, transakcja t
WHERE k.id_klienta=t.id_klienta_fk AND d.id_domu=t.id_domu_fk
GROUP BY id_klienta
HAVING COUNT(*)=2)

--ZADANIE 50
SELECT * FROM transakcja
WHERE cena_uzyskana>(SELECT avg(cena_uzyskana) FROM transakcja)

--ZADANIE 50
SELECT * FROM dom
WHERE id_domu<(SELECT MIN(id_domu) FROM dom WHERE miasto='Sopot')

--ZADANIE 51
SELECT datename(dw,data_transakcji) AS 'Dzień tygodnia' FROM transakcja