CREATE TABLE osoby_zad20
(
id_osoby varchar(7) PRIMARY KEY
nazwisko varchar(30),
pensja_netto int
)
SELECT*FROM osoby_zad20

INSERT INTO osoby_zad20 VALUES
('id_1','Malinowska','4822'),
('id_3','Kowalski','3890'),
('id_6','Nowak','6700'),
('id_9','Nowicki','2787'),
('id_2','Zakrzewski','3480')
SELECT*FROM osoby_zad20

CREATE TABLE tabela_miasta
(
lp INT PRIMARY KEY IDENTITY,
nazwisko varchar(30),
miasto varchar(30)
)
SELECT*FROM tabela_miasta

INSERT INTO tabela_miasta VALUES
('Kowalski','Sopot'),
('Nowak','Gdańsk'),
('Malinowska','Gdańsk'),
('Kowalewski','Gdynia')
SELECT*FROM tabela_miasta


SELECT tabela_miasta.nazwisko, miasto, data1
FROM tabela_data, tabela_miasta
WHERE tabela_miasta.nazwisko=tabela_data.nazwisko
AND miasto='Gdańsk'

SELECT tabela_miasta.nazwisko, data1, miasto
FROM tabela_data, tabela_miasta
WHERE tabela_miasta.nazwisko=tabela_data.nazwisko
AND NOT miasto='Sopot'

ALTER TABLE tabela_data ADD Brutto float
SELECT*FROM tabela_data

UPDATE tabela_data SET Brutto='8120.34'
WHERE lp=1
UPDATE tabela_data SET Brutto='7890.77'
WHERE lp=2
UPDATE tabela_data SET Brutto='9880.80'
WHERE lp=3
UPDATE tabela_data SET Brutto='6788.80'
WHERE lp=4

SELECT*FROM tabela_data

SELECT MAX(Brutto) as 'Osoba zarabiajaca najwiecej'
FROM tabela_data

SELECT MIN(Brutto) as 'Osoba zarabiajaca najmniej'
FROM tabela_data

DECLARE @sr_brutto float
SET @sr_brutto=(SELECT AVG(Brutto) FROM tabela_data)

SELECT @sr_brutto as 'Srednia zarobkow'

SELECT tabela_data.nazwisko, tabela_miasta.miasto, tabela_data.Brutto FROM tabela_data, tabela_miasta
WHERE tabela_data.Brutto>@sr_brutto AND tabela_data.nazwisko=tabela_miasta.nazwisko

----------------------------------

DECLARE @sr_netto float
SET @sr_netto=(SELECT AVG(pensja_netto) FROM osoby_zad20)

SELECT @sr_netto as 'Srednia pensja netto'

SELECT osoby_zad20.nazwisko AS 'Nazwisko osoby', miasta_zad20.miasto, osoby_zad20.pensja_netto AS 'dochód NETTO' FROM osoby_zad20, miasta_zad20
WHERE osoby_zad20.pensja_netto>@sr_netto AND osoby_zad20.nazwisko=miasta_zad20.nazwisko

-----------------------------------------

SELECT*FROM osoby_zad20

DECLARE @min_brutto float
SET @min_brutto=(SELECT MIN(pensja_brutto) FROM osoby_zad20)

SELECT @min_brutto as 'min. pensja Brutto'

SELECT osoby_zad20.nazwisko AS 'Nazwisko osoby', miasta_zad20.miasto, osoby_zad20.pensja_brutto AS 'dochód BRUTTO' FROM osoby_zad20, miasta_zad20
WHERE osoby_zad20.pensja_netto>@min_brutto AND osoby_zad20.nazwisko=miasta_zad20.nazwisko

------------------------------------------

SELECT osoby_zad20.nazwisko AS 'Nazwisko osoby', miasta_zad20.miasto, osoby_zad20.pensja_brutto AS 'dochód MAX BRUTTO' FROM osoby_zad20, miasta_zad20 WHERE miasto='Gdansk' AND osoby_zad20.nazwisko=miasta_zad20.nazwisko
AND osoby_zad20.pensja_brutto = (SELECT MAX(pensja_brutto) FROM osoby_zad20)

SELECT osoby_zad20.nazwisko AS 'Nazwisko osoby', miasta_zad20.miasto, osoby_zad20.pensja_brutto AS 'dochód MIN BRUTTO' FROM osoby_zad20, miasta_zad20 WHERE miasto='Gdansk' AND osoby_zad20.nazwisko=miasta_zad20.nazwisko
AND osoby_zad20.pensja_brutto = (SELECT MIN(pensja_brutto) FROM osoby_zad20)

--zad24

DECLARE @sr_brutto float
SET @sr_brutto=(SELECT AVG(pensja_brutto) FROM osoby_zad20)

SELECT @sr_brutto as 'Srednia pensja netto'

SELECT osoby_zad20.nazwisko AS 'Nazwisko osoby', miasta_zad20.miasto, osoby_zad20.pensja_brutto AS 'dochód BRUTTO' FROM osoby_zad20, miasta_zad20
WHERE osoby_zad20.pensja_brutto>@sr_brutto AND osoby_zad20.nazwisko=miasta_zad20.nazwisko

------------

--zad25

DECLARE @sr_brutto float
SET @sr_brutto=(SELECT AVG(pensja_brutto) FROM osoby_zad20)

SELECT @sr_brutto as 'Srednia pensja netto'

SELECT osoby_zad20.nazwisko AS 'Nazwisko osoby', miasta_zad20.miasto, osoby_zad20.pensja_brutto AS 'dochód BRUTTO' FROM osoby_zad20, miasta_zad20
WHERE osoby_zad20.pensja_brutto>@sr_brutto AND miasta_zad20.miasto != 'Sopot' AND osoby_zad20.nazwisko=miasta_zad20.nazwisko

-------------------------------------------------------
LAB07
---------------------------------------------------------
ZAD26

CREATE TABLE tab1
(
lp INT PRIMARY KEY IDENTITY,
id_prod varchar(15),
cena_netto float(2)
)
SELECT*FROM tab1

CREATE TABLE tab2
(
lp INT PRIMARY KEY IDENTITY,
id_prod varchar(15),
id_kat varchar(15)
)
SELECT*FROM tab2

CREATE TABLE tab3
(
lp INT PRIMARY KEY IDENTITY,
id_kat varchar(15),
opis_kat varchar(20)
)
SELECT*FROM tab3

INSERT INTO tab1 VALUES

('id_a_02','45.99'),
('id_b_03','58.99'),
('id_b_05','30.50'),
('id_a_03','12.50'),
('id_c_05','69.50')

SELECT*FROM tab1

INSERT INTO tab2 VALUES

('id_b_03','id_kat2'),
('id_c_05','id_kat3'),
('id_b_05','id_kat2'),
('id_a_03','id_kat1'),
('id_a_02','id_kat1')

SELECT*FROM tab2

INSERT INTO tab3 VALUES

('id_kat2','opis 1'),
('id_kat1','opis 2'),
('id_kat3','opis 3')

SELECT*FROM tab3

-------------------------------------------------
ZAD27


SELECT MAX(cena_netto*1.23) as 'max z kat1' FROM tab1, tab2, tab3
WHERE tab1.id_prod=tab2.id_prod AND tab2.id_kat='id_kat1'

SELECT MAX(cena_netto*1.23) as 'max z kat2' FROM tab1, tab2, tab3
WHERE tab1.id_prod=tab2.id_prod AND tab2.id_kat='id_kat2'

SELECT MAX(cena_netto*1.23) as 'max z kat3' FROM tab1, tab2, tab3
WHERE tab1.id_prod=tab2.id_prod AND tab2.id_kat='id_kat3'

------------------------------------------------------------------------
ZAD28


SELECT COUNT(tab1.id_prod) as 'ile z kat1' FROM tab1, tab2, tab3
WHERE tab1.id_prod=tab2.id_prod AND tab2.id_kat='id_kat1'

SELECT COUNT(tab1.id_prod) as 'ile z kat2' FROM tab1, tab2, tab3
WHERE tab1.id_prod=tab2.id_prod AND tab2.id_kat='id_kat2'

SELECT COUNT(tab1.id_prod) as 'ile z kat3' FROM tab1, tab2, tab3
WHERE tab1.id_prod=tab2.id_prod AND tab2.id_kat='id_kat3'

--------------------------------------------------------------------
ZAD29

UPDATE tab1 SET cena_netto='100'
WHERE lp=2
UPDATE tab1 SET cena_netto='100'
WHERE lp=3
SELECT*FROM tab1

---------------------------------------------------
ZAD30

SELECT cena_netto*1.23 AS 'cena_brutto' from tab1

DECLARE @sr_brutto float
SET @sr_brutto=(SELECT AVG(cena_netto) FROM tab1)*1.23

SELECT @sr_brutto AS 'Średnia brutto'

DECLARE @vat VARCHAR
SET @vat='23%'

SELECT tab1.id_prod as 'id_prod', tab1.cena_netto as 'cena_netto', tab1.cena_netto*1.23 as 'cena_brutto',@vat as 'VAT', tab2.id_kat as 'id_kat', tab3.opis_kat as 'opis_kat'
FROM tab1, tab2, tab3
WHERE tab1.id_prod=tab2.id_prod AND tab2.id_kat='id_kat1' AND tab1.cena_netto*1.23>@sr_brutto

-----------------------------------


ZAD31

CREATE TABLE tab1
(
lp INT PRIMARY KEY IDENTITY,
Nazwisko varchar(20),
"Data" date
)
SELECT*FROM tab1

CREATE TABLE tab2
(
lp INT PRIMARY KEY IDENTITY,
Nazwisko varchar(20),
Miasto varchar(15)
)
SELECT*FROM tab2

INSERT INTO tab1 VALUES

('Kowalski','1998-09-01'),
('Malinowska','1991-10-09'),
('Nowak','1993-02-09'),
('Kowalewski','1992-03-12')

SELECT*FROM tab1

INSERT INTO tab2 VALUES

('Kowalski','Sopot'),
('Nowak','Gdańsk'),
('Malinowska','Gdańsk'),
('Kowalewski','Gdynia')

SELECT*FROM tab2


--------------------------------------

ZAD32

SELECT tab1.Nazwisko, tab1."Data",(2019-YEAR(tab1.Data)) as 'Wiek', tab2.Miasto FROM tab1, tab2
WHERE tab1.Nazwisko=tab2.Nazwisko
ORDER BY tab2.Miasto

--------------------------
ZAD33

DECLARE @min_data date
SET @min_data=(SELECT MIN(Data) FROM tab1)

DECLARE @min_rok int
SET @min_rok = YEAR(@min_data)

DECLARE @max_data date
SET @max_data=(SELECT MAX(Data) FROM tab1)

DECLARE @max_rok int
SET @max_rok = YEAR(@max_data)

SELECT tab1.Nazwisko as 'Nazwisko osoby najstarszej', (2019-@min_rok) as 'Wiek osoby najstarszej' FROM tab1, tab2
WHERE tab1.Nazwisko=tab2.Nazwisko AND tab1.Data=@min_data

SELECT tab1.Nazwisko as 'Nazwisko osoby najmlodszej', (2019-@max_rok) as 'Wiek osoby najmlodszej' FROM tab1, tab2
WHERE tab1.Nazwisko=tab2.Nazwisko AND tab1.Data=@max_data

-----------------------------------
ZAD34


drop function if exists dbo.f1
go
create function f1()
returns int
begin
DECLARE @min_data date
SET @min_data=(SELECT MIN(Data) FROM tab1)
DECLARE @min_rok int
SET @min_rok = YEAR(@min_data)
return @min_rok
end
go

drop function if exists dbo.f2
go
create function f2()
returns int
begin
DECLARE @max_data date
SET @max_data=(SELECT MAX(Data) FROM tab1)
DECLARE @max_rok int
SET @max_rok = YEAR(@max_data)
return @max_rok
end
go

DECLARE @min date
SET @min=(SELECT MIN(Data) FROM tab1)
DECLARE @max date
SET @max=(SELECT MAX(Data) FROM tab1)

SELECT tab1.Nazwisko as 'Nazwisko osoby najstarszej', (2019-dbo.f1()) as 'Wiek osoby najstarszej' FROM tab1, tab2
WHERE tab1.Nazwisko=tab2.Nazwisko AND tab1.Data=@min

SELECT tab1.Nazwisko as 'Nazwisko osoby najmlodszej', (2019-dbo.f2()) as 'Wiek osoby najmlodszej' FROM tab1, tab2
WHERE tab1.Nazwisko=tab2.Nazwisko AND tab1.Data=@max

------------------------------------------------------------
ZAD35


drop function if exists dbo.f1
go
create function f1()
returns int
begin
DECLARE @zmienna int
SET @zmienna=(SELECT COUNT(tab1.Nazwisko) FROM tab1, tab2
WHERE YEAR(tab1.Data) > 1991 AND tab1.Nazwisko=tab2.Nazwisko AND tab2.Miasto='Gdańsk')
return @zmienna
end
go

SELECT dbo.f1() as 'Gdańsk - po 1991'

-------------------------------------------------------
ZAD36

ALTER TABLE tab1 ADD Brutto float
SELECT*FROM tab1

UPDATE tab1 SET Brutto='8120.34'
WHERE lp=1
UPDATE tab1 SET Brutto='7890.77'
WHERE lp=2
UPDATE tab1 SET Brutto='9880.80'
WHERE lp=3
UPDATE tab1 SET Brutto='6788.80'
WHERE lp=4

SELECT*FROM tab1

------------------------------------------------------
ZAD37

DECLARE @sr_brutto float
SET @sr_brutto=(SELECT AVG(Brutto) FROM tab1)

SELECT @sr_brutto AS 'Średnia brutto'

DECLARE @sr_wiek float
SET @sr_wiek=(SELECT AVG(Brutto) FROM tab1)

SELECT @sr_wiek AS 'Średnia brutto'

SELECT tab1.Nazwisko as 'Nazwisko', (2019-YEAR(tab1.Date)) as 'Wiek', tab2.Miasto, tab1.brutto FROM tab1, tab2
WHERE tab1.Nazwisko=tab2.Nazwisko AND 2019-YEAR(tab1.Date) >  AND tab1.Brutto>@sr_brutto


-----------------

ZAD39

CREATE TABLE tabelka1
(
Lp INT PRIMARY KEY IDENTITY,
"Data" date,
Nazwisko varchar(15)
)
SELECT*FROM tabelka1

CREATE TABLE tabelka2
(
Lp INT PRIMARY KEY IDENTITY,
Nazwisko varchar(15),
Miasto varchar(15)
)
SELECT*FROM tabelka2

CREATE TABLE tabelka3
(
Lp INT PRIMARY KEY IDENTITY,
Nazwisko varchar(15),
Brutto int
)
SELECT*FROM tabelka3

INSERT INTO tabelka1 VALUES

('1998-09-01','Kowalski'),
('1991-10-09','Malinowska'),
('2001-02-09','Nowak'),
('2002-03-12','Kowalewski')

SELECT*FROM tabelka1

INSERT INTO tabelka2 VALUES

('Kowalski','Sopot'),
('Nowak','Gdańsk'),
('Malinowska','Gdańsk'),
('Kowalewski','Gdynia')


SELECT*FROM tabelka2

INSERT INTO tabelka3 VALUES

('Kowalski','8120'),
('Malinowska','7891'),
('Nowak','9882'),
('Kowalewski','6789')

SELECT*FROM tabelka3

1
DECLARE @sr0 int
SET @sr0 = (SELECT MIN(tabelka3.Brutto*0.813) FROM tabelka3, tabelka2 WHERE tabelka2.Miasto='Gdańsk')

SELECT tabelka2.Nazwisko, tabelka3.Brutto*0.813 as 'Netto', tabelka3.Brutto FROM tabelka2, tabelka3

WHERE tabelka2.Nazwisko=tabelka3.Nazwisko AND tabelka3.Brutto*0.813>@sr0 AND tabelka2.Miasto='Gdańsk'

2
DECLARE @max int
SET @max = (SELECT MAX(Brutto) FROM tabelka3)

SELECT tabelka2.Nazwisko, tabelka3.Brutto*0.813 as 'Netto', tabelka3.Brutto FROM tabelka2, tabelka3

WHERE tabelka2.Nazwisko=tabelka3.Nazwisko AND tabelka3.Brutto=@max

3
DECLARE @sr1 int
SET @sr1 = (SELECT AVG(Brutto) FROM tabelka3, tabelka2 WHERE tabelka2.Miasto='Gdańsk')

DECLARE @sr2 int
SET @sr2 = @sr1*0.813


SELECT tabelka2.Miasto, @sr2 as 'Netto', @sr1 as 'Brutto' FROM tabelka2, tabelka3
WHERE tabelka2.Nazwisko=tabelka3.Nazwisko AND tabelka2.Miasto='Gdańsk'
------------------------------

ZAD40

drop function if exists dbo.fun1
go
create function fun1()

begin

DROP TABLE if exists imionka1
DROP TABLE if exists wzrosty
DROP TABLE if exists razem

CREATE TABLE imionka1
(
imię varchar(10) PRIMARY KEY,
nazwisko varchar(30),
)
SELECT*FROM imionka1

CREATE TABLE wzrosty
(
nazwisko varchar(30) PRIMARY KEY,
wzrost int,
)
SELECT*FROM imionka1

CREATE TABLE razem
(
miasto varchar(10) PRIMARY KEY,
nazwisko varchar(30),
netto float
)
SELECT*FROM razem

INSERT INTO wzrosty VALUES

('Kowalski','190'),
('Malicka','195'),
('Malinowski','185'),
('Nowicki','188')

SELECT*FROM wzrosty

INSERT INTO imionka1 VALUES

('Gdańsk','Kowalski','Kowalski'),
('Gdynia','Nowicki','Kowalski'),
('Gdańsk','Malinowski','Kowalski'),
('Gdynia','Malicka','Kowalski')

SELECT*FROM imionka1



end
go

dbo.fun1

----------------------------------------------------------------------------------------

ZAD49

DROP TABLE IF EXISTS pacjenci
DROP TABLE IF EXISTS lekarze
DROP TABLE IF EXISTS miasta
DROP TABLE IF EXISTS wizyty

CREATE TABLE pacjenci
(
id_pacjenta varchar(6) PRIMARY KEY,
nazwisko varchar(20),
data_ur date
)
SELECT*FROM pacjenci

CREATE TABLE lekarze
(
id_lekarza varchar(4) PRIMARY KEY,
id_specjalizacji varchar(2),
nr_gab int
)
SELECT*FROM lekarze

CREATE TABLE miasta
(
nazwisko varchar(20) PRIMARY KEY,
miasto varchar(10)
)
SELECT*FROM miasta

CREATE TABLE wizyty
(
id_pacjenta varchar(6) PRIMARY KEY,
data_wizyty datetime2,
id_lekarza varchar(4)

)
SELECT*FROM wizyty



INSERT INTO pacjenci VALUES

('A_1234','Kowalska','1989/02/01'),
('A_3456','Nowak','1991/03/03 '),
('B_2111','Malicki','1993/05/05')

SELECT*FROM pacjenci

INSERT INTO lekarze VALUES

('P_12','p1','23'),
('O_34','o1','31'),
('S_90','s1','40')

SELECT*FROM lekarze

INSERT INTO miasta VALUES

('Nowak','Gdańsk'),
('Kowalska','Gdańsk'),
('Malicki','Sopot')

SELECT*FROM miasta

INSERT INTO wizyty VALUES

('A_3456','2019-04-21 18:00','S_90'),
('A_1234','2019-05-13 14:50','O_34'),
('B_2111','2019-06-03 15:00','S_90')

SELECT*FROM wizyty

SELECT pacjenci.nazwisko, pacjenci.data_ur, YEAR(GETDATE()) - YEAR(pacjenci.data_ur) as 'wiek' FROM pacjenci

SELECT pacjenci.nazwisko as 'nazwisko osoby najstarszej' FROM pacjenci
WHERE pacjenci.data_ur = (SELECT MIN(pacjenci.data_ur) FROM pacjenci)

SELECT miasta.miasto, wizyty.data_wizyty, pacjenci.nazwisko FROM pacjenci, wizyty, miasta
WHERE pacjenci.nazwisko = miasta.nazwisko AND pacjenci.id_pacjenta = wizyty.id_pacjenta

SELECT miasta.miasto, wizyty.data_wizyty, pacjenci.nazwisko, lekarze.id_lekarza, lekarze.id_specjalizacji FROM pacjenci, wizyty, miasta, lekarze
WHERE pacjenci.nazwisko = miasta.nazwisko AND pacjenci.id_pacjenta = wizyty.id_pacjenta AND lekarze.id_lekarza = wizyty.id_lekarza

SELECT miasta.miasto, wizyty.data_wizyty, pacjenci.nazwisko, lekarze.id_lekarza, lekarze.id_specjalizacji FROM pacjenci, wizyty, miasta, lekarze
WHERE pacjenci.nazwisko = miasta.nazwisko AND pacjenci.id_pacjenta = wizyty.id_pacjenta AND lekarze.id_lekarza = wizyty.id_lekarza AND miasta.miasto != 'Gdańsk'

SELECT miasta.miasto, wizyty.data_wizyty, pacjenci.nazwisko, lekarze.id_lekarza, lekarze.id_specjalizacji FROM pacjenci, wizyty, miasta, lekarze
WHERE pacjenci.nazwisko = miasta.nazwisko AND pacjenci.id_pacjenta = wizyty.id_pacjenta AND lekarze.id_lekarza = wizyty.id_lekarza AND pacjenci.nazwisko = 'Nowak'

SELECT miasta.miasto, wizyty.data_wizyty, pacjenci.nazwisko, lekarze.id_lekarza, lekarze.id_specjalizacji FROM pacjenci, wizyty, miasta, lekarze
WHERE pacjenci.nazwisko = miasta.nazwisko AND pacjenci.id_pacjenta = wizyty.id_pacjenta AND lekarze.id_lekarza = wizyty.id_lekarza AND DAY(wizyty.data_wizyty) = DAY(GETDATE())

----------------------------------------------------------------------------------------------