USE N1233
GO

-- zadanie 40
SELECT d.miasto, d.ulica, k.nazwisko
FROM dom d, transakcje t, klient k
WHERE d.id_domu = t.id_domu
AND k.id_klienta = t.id_klienta
AND k.id_klienta IN (
    SELECT id_klienta FROM TRANSAKCJE
    GROUP BY id_klienta HAVING COUNT(*)>=2
)

-- zadanie 41
SELECT d.miasto, d.ulica, k.nazwisko
FROM dom d, transakcje t, klient k
WHERE d.id_domu = t.id_domu
AND k.id_klienta = t.id_klienta
AND k.id_klienta IN (
    SELECT id_klienta FROM TRANSAKCJE
    GROUP BY id_klienta HAVING COUNT(*)=2
)

-- zadanie 42
SELECT * FROM TRANSAKCJE
WHERE cena_uzyskana > (
    SELECT AVG(cena_uzyskana) FROM TRANSAKCJE
)

-- zadanie 43
SELECT * FROM DOM
WHERE id_domu < (
    SELECT MIN(id_domu) FROM DOM WHERE miasto='Sopot'
)

-- zadanie 44
SELECT datename(dw, data_transakcji) AS 'Dzień tygodnia' FROM TRANSAKCJE

-- zadanie 45 ( 2 domy )
SELECT d.miasto, t.id_transakcji,
datename(dw, t.data_transakcji) AS 'Dzień tygodnia'
FROM DOM d, TRANSAKCJE t
WHERE d.id_domu = t.id_domu
AND d.miasto IN (
    SELECT miasto
    FROM DOM d, TRANSAKCJE t
    WHERE d.id_domu = t.id_domu
    GROUP BY miasto
    HAVING COUNT(*)>=2
)

-- zadanie 46
SELECT k1.nazwisko, k1.imie, k2.miasto, k2.ulica
FROM klient k1 LEFT JOIN klient k2
ON k1.id_klienta = k2.id_klienta
AND LEFT(k2.miasto, 1) = LEFT(k2.ulica, 1)

-- zadanie 47
SELECT datename(mm, data_transakcji), COUNT(*) FROM transakcje
GROUP BY datename(mm, data_transakcji)

-- dodatkowe
SELECT miasto, AVG(cena_proponowana) FROM dom
GROUP BY miasto

-- zadanie 48
SELECT CAST((cena_uzyskana * 0.9845) AS NUMERIC(9, 2)) AS 'Cena bez prowizji' FROM transakcje
WHERE YEAR(data_transakcji) = 2019

-- zadanie 49
SELECT nazwisko FROM KLIENT
WHERE LEFT(nazwisko, 1) = SUBSTRING(nazwisko, 3, 1)
AND LEN(nazwisko) = 5

-- zadanie 50
SELECT nazwisko FROM KLIENT WHERE id_klienta IN (
    SELECT TOP 3 id_klienta FROM TRANSAKCJE
)

-- zadanie 51
SELECT d1.id_domu, d2.miasto
FROM dom d1 LEFT JOIN dom d2
ON d1.id_domu = d2.id_domu
AND d2.cena_proponowana / d2.metraz > 5000

-- zadanie 52
SELECT nazwisko FROM KLIENT
WHERE nazwisko LIKE '%ski'
AND klient.id_klienta IN (
    SELECT id_klienta FROM TRANSAKCJE
)

-- zadanie 53
UPDATE DOM SET cena_proponowana = cena_proponowana * 1.05

-- zadanie 54
UPDATE DOM SET cena_proponowana = cena_proponowana - 5000
WHERE cena_proponowana = (
    SELECT MAX(cena_proponowana) FROM DOM
)

-- zadanie 55
UPDATE DOM SET cena_proponowana = cena_proponowana * 0.9
WHERE id_domu NOT IN (
    SELECT id_domu FROM TRANSAKCJE
)

-- zadanie 56
DELETE FROM KLIENT WHERE nazwisko LIKE 'A%'

-- zadanie 57
DELETE FROM DOM
WHERE id_domu NOT IN (
    SELECT id_domu FROM TRANSAKCJE
)

-- zadanie 58
CREATE VIEW zadanie58 AS
SELECT * FROM KLIENT
WHERE id_klienta NOT IN (
    SELECT id_klienta FROM TRANSAKCJE
)

SELECT * FROM zadanie58

-- zadanie 59
UPDATE zadanie58 SET id_klienta += 100

-- zadanie 60
INSERT INTO DOM VALUES
(100, 'Łódź', 'Krótka 10', 200, 950000),
(101, 'Hel', 'Focza 1', 100, 550000)


SELECT * FROM DOM