c90KuhEy

1https://www.dropbox.com/sh/d8b2c836n9acpf0/AABerCwDYJIbqpg9zsHmyYZMa?dl=0
2
3https://www.w3schools.com/sql – stąd wiedze czerpać
4https://www.w3schools.com/sql/sql_primarykey.asp
5https://www.w3schools.com/sql/sql_alter.asp – aktualizacja tabeli
6
7• Zaloguj się jako użytkownik posiadający uprawnienia do tworzenia baz danych
8mysql -u root -p
9
10• Wyświetl nazwę użytkownika bazy danych i aktualną datę
11select user(), current_date;
12
13• Wyświetl wszystkie dostępne bazy danych
14show databases;
15
16• Jeśli istnieje baza wspa to ja usuń
17drop database;
18
19• Utwórz nową bazę danych - wspa
20create database wspa;
21
22• Sprawdź czy istnieje
23show databases;
24
25#ustawienie domyslnej bazy danych na ktorej praucmemy
26use nazwabazydanych;
27
28• Sprawdź czy zawiera jakieś tabele
29show tables;
30
31• W bazie wspa utwórz tabelę: student
32create table student (id int, firstname varchar(15), country varchar(255));
33
34• Wstaw etykiety tabeli jak na zrzucie poniżej
35insert into student(id, firstname, country)
36    -> values
37    -> (1, 'jack', 'Poland'),
38    -> (2, 'mary', 'Great Britain'),
39(3, 'nichole', 'Indie'),
40(4, 'angie', 'Ukraina'),
41(5, 'jones', 'Germany'),
42(6, 'newperson', 'null'),
43(7, 'ewa', 'Poland'),
44(8, 'ed', 'Germany'),
45(9, 'bob', 'Poland');
46(10, 'nichole', 'Indie'),
47(11, 'angie', 'Ukraina'),
48(12, 'jones', 'Germany'),
49(13, 'newperson', 'null'),
50(14, 'ewa', 'Poland'),
51(15, 'ed', 'Germany'),
52(16, 'bob', 'Poland'),
53(17, 'ewa', 'Poland'),
54(18, 'ed', 'Germany'),
55(19, 'bob', 'Poland'),
56(20, 'ole', 'Jusbekistan'),
57(21, 'gangie', 'Ukraina'),
58(22, 'janusz', 'Germany'),
59(23, 'babanusz', 'Germany'),
60(24, 'ababusz', 'Poland'),
61(25, 'ted', 'Germany'),
62(26, 'boban', 'Liban'),
63(27, 'lola', 'USA');
64
65• Wyświetl tabelę
66select * from student;
67
68• Policz ilu jest studentów z Polski (twoja ściągawka https://www.w3schools.com/sql/
69sql_count_avg_sum.asp)
70
71select country, count(firstname)
72    -> from student;
73
74• Policz ilu jest studentów z każdego kraju (twoja ściągawka https://www.w3schools.com/sql/
75sql_groupby.asp). Tabela powinna zawierać nazwę kraju i ilość studentów w poszczególnych
76krajach
77
78select country, count(firstname) as Ile_pajaców from student group by country;
79
80• Policz ilu jest studentów z każdego kraju. Wyniki posortuj malejąco (twoja ściągawka https://
81www.w3schools.com/sql/sql_orderby.asp)
82
83select country, count(firstname) as Ile_pajaców from  student group by country order by Ile_pajaców desc;
84
85
86ZAD 4
87• Utwórz tabelę actor z polami: actor_id, first_name, last_name, last_update
88
89CREATE TABLE actor (
90actor_id int not null,
91first_name VARCHAR(45) NOT NULL,
92last_name VARCHAR(45) NOT NULL,
93last_update TIMESTAMP,
94PRIMARY KEY (actor_id)
95)ENGINE=InnoDB;
96
97insert into actor
98values
99(1,'PENELOPE','GUINESS','2006-02-15 04:34:33'),
100(2,'NICK','JAGGER','2006-02-15 04:34:33'),
101(3,'ED','CHASE','2006-02-15 04:34:33'),
102(4,'JANNIFER','DAVIS','2006-02-15 04:34:33'),
103(5,'JOHNNY','LOLLOBRIGIDA','2006-02-15 04:34:33'),
104(6,'BETTE','NICHOLSON','2006-02-15 04:34:33');
105
106# Wyświetl opis tabeli
107select * from actor
108describe country;
109
110• Wyświetl rekordy z imieniem Jan (patrz https://dev.mysql.com/doc/refman/8.0/en/selectingrows.
111html)
112select * from actor where first_name='ED';
113
114• Zaproponuj inne zapytania ograniczajace ilość danych korzystając z treści rozdziału
1153.3.4.2 Selecting Particular Rows
116select * from actor where (first_name='ED' and last_name='chase') or first_name='johnny';
117
118• Wyświetl imię i nazwisko wszystkich aktorów (patrz https://dev.mysql.com/doc/refman/8.0/en/
119selecting-columns.html)
120select first_name, last_name from actor;
121
122# Wyświetl imiona aktorów. Zadbaj by nie powtarzały się
123#
124select first_name from actor group by first_name;
125
126# Utwórz tabelę country z polami: country, last_update
127#
128 create table country (
129    -> country varchar(50) NOT NULL,
130    -> last_update TIMESTAMP NOT NULL
131    -> )ENGINE=InnoDB;
132
133# Wyświetl opis tabeli
134describe country;
135
136# w tabeli country dodaj pole Id (modyfikacja tabeli)
137# Zadeklaruj je jako klucz główny
138#
139alter table country
140    -> add country_id int,
141    -> add primary key(country_id);
142
143# Wstaw 6 rekordów do tabeli
144#
145insert into country
146values
147('afghanistan','2006-02-15 04:44:00',1),
148('algreia','2006-02-15 04:44:00',2),
149('ameriacn s','2006-02-15 04:44:00',3),
150('angola','2006-02-15 04:44:00',4),
151('anguilla','2006-02-15 04:44:00',5),
152('argentina','2006-02-15 04:44:00',6);
153
154# Wyświetl tabelę
155#
156select * from country;
157
158# Wyświetl nazwy krajów sortując je na różne sposoby
159#
160select country from country order by country desc; ##asc
161
162=========================================
163
1645 - Zapytania z użyciem Create
165Ćwiczenie 5.1
166# A. Utwórz tabelę Salary, musi się w niej znaleźć kolumna z kwotą pensji zapisywaną co do
167grosza.
168use wspa;
169
170create table Salary (salary decimal(8));
171
172# B. Utwórz tabelę COUNTRY, w tym kolumny country_id, country_name i region_id.
173
174create table COUNTRY (country_id int, country_name varchar(40), region_id decimal(10,0));
175
176
177# C. Utwórz tablę dup_countries, która będzie wzorowana na tabeli countries. Użyj ‚like’
178
179CREATE TABLE IF NOT EXISTS dup_countries
180
181LIKE country; 
182
183
184# D. Wywołaj zapytanie desc countries. Do czego Ci posłużyło?
185opis kolumn i typów danych
186
187# E. Napisz instrukcję tworzącą tabelę mycouries, wykorzystaj ograniczenie NOT NULL.
188create table mycountries (country_id int NOT NULL, country_name varchar(40) NOT NULL);
189
190
191# F. Utwórz instrukcję SQL, aby powstała tabela z kolumnami job_id, job_title, min_salary, max_salary tak by max_salary nie przekraczała górnego limitu 25000. Podpowiedź - funkcja
192CHECK()
193
194create table IF NOT EXISTS jobs (job_id int not null, job_title varchar(60) not null, min_salary decimal(6,2), max_salary decimal(6,2), CHECK(MAX_SALARY<=25000));
195
196
197# G. Napisz instrukcję tworzące tabelę krajów, w tym kolumny country_id, country_name i
198region_id, i upewnij się, że w tabeli nie zostaną podane żadne kraje z wyjątkiem Polski,
199Szwajcarii, Słowacji. MySql przestał wspierać ograniczenia przygotowane przez funkcję
200check().
201
202create table IF NOT EXISTS 
203countries (country_id varchar(2), 
204
205country_name varchar(40)
206
207check(country_name IN('polska','Szwajcaria','Słowacja')),
208
209region_id decimal(10,0));
210
211# H. Utwórz tabelę o nazwie job_histry, w tym kolumny employee_id, start_date, end_date, job_id i
212department_id i upewnij się, że wartość kolumny end_date zostanie wprowadzona w formacie
213’- / - / - -’ MySql przestał wspierać ograniczenia przygotowane przez funkcję check().
214
215create table if not exists job_histry 
216
217(employee_id decimal(6,0) not null,
218
219start_date date NOT NULL,
220
221end_date date NOT NULL
222
223check(end_date like '--/--/----'),
224
225job_id varchar(10) not null,
226
227department_id varchar(2) not null);
228
229
230# I. Napisz sql kreujące tabelę krajów, w tym kolumny country_id, country_name i region_id, i
231upewnij się, że nie będą dozwolone żadne duplikaty danych w kolumnie country_name. Użyj
232UNIQUE
233
234CREATE TABLE IF NOT EXISTS 
235countries (
236COUNTRY_ID varchar(2) NOT NULL,
237
238COUNTRY_NAME varchar(40) NOT NULL,
239
240REGION_ID decimal(10,0) NOT NULL,
241UNIQUE(COUNTRY_ID)
242);
243
244# J. Utwórz tabelę o kolumnach job_id, job_title, min_salary i max_salary, i upewnij się, że
245domyślna wartość dla job_title jest pusta, a min_salary to 8000, a max_salary ma wartość
246NULL i zostanie wpisana automatycznie w momencie wstawienia, jeśli żadna wartość nie jest
247przypisana do określonych kolumn.
248
249create table if not exists jobs2 
250
251(job_id int not null UNIQUE, 
252
253job_title varchar(60) not null DEFAULT ' ',
254
255min_salary decimal(6,2) DEFAULT 8000, 
256
257max_salary decimal(6,2) DEFAULT NULL);
258
259# K. Napisz instrukcję SQL, aby utworzyć tabelę Kraje, kolumny country_id, country_name i
260region_id, i upewnij się, że kolumna country_id będzie unikatowa i automatycznie tworzy
261inkrementowaną wartość
262
263CREATE TABLE IF NOT EXISTS countries3 
264(
265COUNTRY_ID integer NOT NULL UNIQUE AUTO_INCREMENT PRIMARY KEY,
266
267COUNTRY_NAME varchar(40) NOT NULL,
268
269REGION_ID decimal(10,0) NOT NULL
270); 
271
272
273Ćwiczenie 5.2
274• Napisz instrukcję SQL, aby utworzyć tabelę job_history zawierającą kolumny employee_id, start_date, end_date, job_id i upewnij się, że kolumna employee_id nie zawiera żadnych
275duplikatów w momencie wstawienia, a kolumna klucza obcego zawiera tylko te wartości, które istnieją w tabeli jobs
276
277CREATE TABLE job_history 
278(
279EMPLOYEE_ID decimal(6,0) NOT NULL UNIQUE PRIMARY KEY,
280
281START_DATE date NOT NULL,
282END_DATE date NOT NULL,
283
284JOB_ID int NOT NULL,
285
286DEPARTMENT_ID decimal(4,0) DEFAULT NULL,
287
288FOREIGN KEY (job_id) REFERENCES jobs2(job_id)
289); 
290
291
2926 - Zapytania z użyciem Update
293Ćwiczenie 6.1
294#A. Napisz instrukcję SQL, aby zmienić kolumnę e-mail tabeli pracowników na "niedostępne" dla wszystkich pracowników.
295
296UPDATE employees SET email='not available’; 
297
298
299#B. Napisz instrukcję SQL, aby zmienić kolumnę email i commission_pct w tabeli pracowników na "niedostępne" i 0.10 dla wszystkich pracowników.
300
301create table if not exists employees (
302employee_id varchar(3) NOT NULL,
303email varchar(50),
304commission_pct decimal(4,2),
305department_id decimal(4,0));
306
307set sql_safe_updates = 0;
308
309
310UPDATE employees SET email='not available',
311
312commission_pct=0.10; 
313
314
315#C. Napisz instrukcję SQL, aby zmienić kolumnę email i commission_pct tabeli pracowników na
316"dostępne" i 0.10 dla pracowników, których identyfikator departamentu wynosi 110.
317
318UPDATE employees
319SET email='available',
320commission_pct=0.10
321WHERE department_id=110; 
322
323#D. Napisz instrukcję SQL, aby zmienić kolumnę poczty e-mail w tabeli pracowników na
324"dostępne" dla tych pracowników, których numer działu wynosi 80, a prowizja jest mniejsza
325niż .20%
326
327UPDATE employees
328
329SET email='available'
330
331WHERE department_id=80 AND commission_pct<.20;
332
333#E. Napisz instrukcję SQL, aby zmienić wynagrodzenie pracownika na 8000, którego ID to 105,
334jeśli obecne wynagrodzenie jest mniejsze niż 5000.
335
336UPDATE employees 
337SET SALARY = 8000 WHERE employee_id = 105 AND salary<5000;
338
339
340
3417 - Zapytania z użyciem Alter
342Ćwiczenie 7.1
343A. Napisz instrukcję SQL, by dodać kolumnę region_id do tabeli location.
344
345alter table locations
346
347add region_id int
348
349
350B. Napisz instrukcję SQL, aby dodać identyfikator kolumny jako pierwszą kolumnę pozycji tabeli location
351
352alter table locations
353
354add id int first;
355
356
357C. Napisz instrukcję SQL, aby zmienić nagłówek kolumny nazwy krajów tabeli na country_new.
358
359alter table locations
360
361change country country_new varchar(35);
362
363
364D. Napisz instrukcję SQL, aby dodać kolumnę region_id po adresie state_province do tabeli location
365
366alter table locations
367
368add region_id int
369
370after state_province;
371
372
373E. Napisz instrukcję SQL by zmienić typ danych kolumny country_id na inny, np int w tabeli location.
374
375alter table locations
376
377modify country_id int;
378
379
380F. Usuń jakąś kolumnę w tabeli location
381
382alter table locations
383
384drop country_id;
385
386
387G. W dowolnej tabeli instrukcją ALTER wstaw kolumnę z kluczem głównym
388
389alter table locations
390
391add location_id integer NOT NULL UNIQUE AUTO_INCREMENT PRIMARY KEY;
392
393///albo: ADD PRIMARY KEY(location_id); 
394
395
396H. Napisz instrukcję SQL, aby dodać klucz obcy job_id w tabeli job_history, odwołując się do klucza podstawowego job_id tabeli jobs.
397
398ALTER TABLE job_history
399
400ADD FOREIGN KEY(job_id)
401
402REFERENCES jobs(job_id); 
403
404
405I. Napisz instrukcję SQL, aby usunąć istniejący klucz obcy fk_job_id z tabeli job_history w
406kolumnie job_id, która odwołuje się do job_id tabeli jobs.
407
408ALTER TABLE job_history
409
410DROP FOREIGN KEY fk_job_id; 
411
412
41310 Podzapytania
414Ćwiczenie 10.1
415A. Uruchom skrypt bazy HR
416B. Napisz zapytanie, wyszukujące nazwisko oraz wynagrodzenie pracowników, którzy mają wyższą pensję niż pracownik, którego last_name = „Bull". Do tego ćwiczenia dołączam rozwiązanie. Do dalszych już nie.
417
418select LAST_NAME, SALARY
419
420FROM employees
421
422WHERE SALARY > (SELECT salary from employees where last_name = 'Bull');
423
424
425C. Napisz zapytanie, wyszukujące nazwisko (imię, nazwisko) wszystkich pracowników, którzy pracują w dziale IT.
426
427
428
429
430D. Napisz zapytanie, wyszukujące nazwisko (imię, nazwisko) pracowników, którzy mają
431menedżera i pracowali w departamencie opartym na USA.
432
433
434
435
436E. Napisz zapytanie, wyszukujące nazwisko (imię, nazwisko) i wynagrodzenie pracowników,
437których wynagrodzenie jest wyższe niż średnie wynagrodzenie.
438
439
440
441
442F. Napisz zapytanie, wyszukujące nazwisko (imię, nazwisko) oraz wynagrodzenie pracowników,
443których wynagrodzenie jest równe minimalnemu wynagrodzeniu na ich stopniu zatrudnienia.
444
445
446
447
448G. Napisz zapytanie, wyszukujące nazwisko (imię, nazwisko) i wynagrodzenie pracowników,
449którzy zarabiają więcej niż przeciętne wynagrodzenie i pracują w jakimkolwiek dziale IT.
450
451
452
453
454H. Napisz zapytanie, wyszukujące nazwisko (imię, nazwisko) i wynagrodzenie pracowników,
455którzy zarabiają więcej niż wynoszą zarobki Pana Bella.
456
457
458
459
460I. Napisz zapytanie, wyszukujące imię i nazwisko (first_name, last_name) oraz wynagrodzenie
461pracownik
462
463
464
465
466/// graficznie relacje itd: database> reverse engineer... database> forward engineer...
467/// do projektu i do pracy dyplomowej!