shX4A2Xt

1-- 3.15
2select nazwisko
3from Pracownicy
4where id not in  (select idPrac from Realizacje)
5
6-- 3.16
7--Podaj nazwisko najlepiej zarabiajÄ…cego pracownika (wykorzystaj ALL) 
8select nazwisko
9from Pracownicy
10where placa >= all (select placa from Pracownicy)
11
12-- skorelowane
13select *
14from Pracownicy p
15where placa >= all (select placa from Pracownicy where p.stanowisko = Pracownicy.stanowisko)
16
17-- 3.17 zapyranie skorelowane ( porownujemy dana wartosc np id)
18-- dla kazdego pracownika sprawdzam po kolei mozna tu uzywac * a w 3.15 nie
19select *
20from Pracownicy p
21where not exists (select idPrac from Realizacje where idPrac = p.id)
22
23-- agregujace
24min
25max
26avg
27sum
28count
29
30select min(placa)
31-- 3.21
32select nazwisko, max(placa)
33from Pracownicy
34where placa = (select max(placa) from Pracownicy)
35Group BY nazwisko
36
37
38-- 3.22 max i podzapytanie skorelowane
39-- Podaj nazwisko pracownika, który zarabia najwięcej (podzapytanie i funkcja agregująca) 
40-- nie mozna robic max(placa) ,nazwisko bez group by
41select nazwisko,stanowisko
42from Pracownicy p
43where placa = (select max(placa) from Pracownicy where p.stanowisko = stanowisko)
44
45-- 3.23 -- group by having join
46select TOP 1 nazwisko,stanowisko, count(distinct p2.idProj) 'ilosc projektow'
47from Pracownicy p
48INNER JOIN Realizacje p2 on p.id = p2.idPrac
49where stanowisko != 'profesor'
50Group BY nazwisko, stanowisko 
51Having count(p2.idProj) > 1
52ORDER BY count( distinct p2.idProj) DESC
53
54select nazwisko,stanowisko, COUNT(distinct p2.idProj) 'ilosc_projektow'
55from Pracownicy p
56INNER JOIN Realizacje p2 on p.id = p2.idPrac
57where stanowisko != 'profesor'
58Group BY nazwisko, stanowisko 
59Having count(distinct p2.idProj) > 1
60
61
62select nazwisko,stanowisko, COUNT(distinct p2.idProj) licz_proj
63from Pracownicy p
64INNER JOIN Realizacje p2 on p.id = p2.idPrac
65Group BY nazwisko, stanowisko 
66having count (idProj) = (
67
68	select max(licz_proj)
69	from (select nazwisko,stanowisko, COUNT(distinct p2.idProj) 'ilosc_projektow'
70	from Pracownicy p
71	INNER JOIN Realizacje p2 on p.id = p2.idPrac)
72
73)
74
75IF OBJECT_ID('Uczestnicy','U') IS NULL 
76CREATE TABLE Uczestnicy(
77	PESEL char(11) primary key,
78	nazwisko nvarchar(max) not null,
79	miasto varchar(30)  default 'Poznań'
80)
81
82IF OBJECT_ID('Kursy','U') IS NULL 
83CREATE TABLE Kursy(
84	Kod char(5) primary key,
85	nazwa varchar(100) unique ,
86	-- liczba_dni smallint  CHECK(liczba_dni IN(1,2,3,4,5)),
87	liczba_dni smallint  CHECK(liczba_dni between 1 and 5),
88	cena AS liczba_dni * 1000 
89)
90
91IF OBJECT_ID('Uczestnictwo','U') IS NULL 
92CREATE TABLE Uczestnictwo(
93	uczestnik char(11)  REFERENCES Uczestnicy(PESEL),
94	kurs char(5)  REFERENCES Kursy(Kod),
95	data_od date,
96	data_do date,
97	status nvarchar(10)  CHECK (status in ('w trakcie', 'ukonczony', 'nie ukonczony')),
98	CONSTRAINT chk_data_do 
99    CHECK (data_do > data_od)
100	-- primary key (uczestnik,kurs) -- tworzy klucz podstawowy z kluczy obcych
101)
102
103
104-- zamiast check status lepiej utworzyć tabele słownikową ( zawsze w niej możemy dodać opcje i nie zmieniamy kodu od tworzenia tabeli)
105-- char alokuje pamiec na tyle ile potrzebuje 
106-- varchar zawsze bedzie mial np 30 nawet jesli podamy mniej
107-- nvarchar - obsługuje znaki UNICODE
108
109select *
110from INFORMATION_SCHEMA.TABLES
111WHERE TABLE_SCHEMA = SCHEMA_NAME()
112
113select *
114From Kursy
115
116select *
117from sys.tables
118
119select name
120from sys.columns
121WHERE object_id = OBJECT_ID('Uczestnicy','U')
122
123select name
124from sys.check_constraints
125WHERE parent_object_id = OBJECT_ID('Uczestnictwo','U')
126
127ALTER TABLE Uczestnictwo 
128DROP constraint chk_data_do
129
130ALTER TABLE Uczestnictwo 
131ADD constraint 	chk_data_do CHECK (data_do > data_od)
132
133-- 'U' -> tabela użytkownika
134
135IF OBJECT_ID('Kursy','U') IS NOT NULL 
136DROP TABLE KURSY
137
138IF OBJECT_ID('Kursy','U') IS NOT NULL 
139DROP TABLE Uczestnictwo
140
141IF OBJECT_ID('Kursy','U') IS NOT NULL 
142DROP TABLE Uczestnicy
143
144insert into Uczestnicy values
145('75010106222', 'Tomasz','Górski'),
146('88060623235', 'Karol',  default)
147
148
149insert into Kursy values
150(100, 'Administracja MySQL' ,   3),
151(200, 'Analiza danych' ,        3),
152(300, 'MS Access (zaawansowany)' ,      2),
153(400, 'MySQL dla programistów' ,        2),
154(500, 'Programowanie VBA w Accessie' ,  1)
155
156insert into Uczestnictwo values
157('75010106222', 100,'2015-05-12','2016-05-12','ukonczony')
158
159
160select *
161from Kursy
162
163
164select *
165from Uczestnictwo
166
167select *
168from Uczestnicy
169
170
171
172
173
174
175
176-- sprawdz czy sa pracownicy na tym samym stanowisku
177select nazwisko, count(nazwisko) 'ilosc powtorzen nazwisk'
178from Pracownicy p
179Group BY nazwisko
180Having count(nazwisko) > 1 
181
182
183
184select * from Stanowiska
185select * from Pracownicy
186select * from Projekty
187select * from Realizacje