· 7 years ago · Dec 15, 2018, 10:10 PM
1db2stop force
2db2icrt SR2
3db2idrop SR2
4db2ilist
5db2 get instance
6set db2instance=<>
7db2 create database mydb1
8db2start
9db2 list db directory
10db2 connect to mydb1
11db2 create table t1(a1 int)
12db2 insert into t1 values(1),(2),(3)
13db2 select * from t1 (order by a1 desc)
14
15db2 connect reset
16db2sampl -?
17db2sampl -name test1 -sql -xml
18db2licm -l
19db2 get dbm cfg
20db2 update dbm cfg using FEDERATED yes
21db2 get db cfg for test1
22db2 update db cfg for test1 using cur_commit off
23db2set -all
24db2set -lr
25db2set db2comm=tcpip
26
27select * from EMPLOYEE;
28
29select upper('Henryk') from sysibm.dual;
30
31--1. Podaæ nazwisko i imiona pracowników zaczynaj¹ce siê na literê od c do s.
32select emp.firstnme, emp.lastname
33from EMPLOYEE emp
34where substring (upper(emp.LASTNAME) from 1 for 1) between 'C' and 'S'
35order by emp.LASTNAME;
36
37--2. Podaæ nazwisko i imiona pracowników zaczynaj¹ce siê na literê od c do s trzecia nie l.
38select emp.firstnme, emp.lastname
39from EMPLOYEE emp
40where substring (upper(emp.LASTNAME) from 1 for 1) between 'C' and 'S'
41and (substring(upper(emp.LASTNAME) from 3 for 1) between 'A' and 'K'
42or substring(upper(emp.LASTNAME) from 3 for 1) between 'M' and 'Z')
43order by emp.LASTNAME;
44
45select emp.firstnme, emp.lastname
46from EMPLOYEE emp
47where substring (upper(emp.LASTNAME) from 1 for 1) between 'C' and 'S'
48and substring(upper(emp.LASTNAME) from 3 for 1) not like 'L'
49order by emp.LASTNAME;
50
51
52--3. Podaæ pracowników, których pensja jest > od 30 000 z³.
53select * from employee;
54select emp.firstnme, emp.lastname, emp.salary
55from EMPLOYEE emp
56where emp.salary>15000
57order by emp.LASTNAME;
58
59--4. Podaæ pracowników, których pensja z dodaniem prowizji jest > od 50 000 z³ i s¹ kobietami.
60select * from employee;
61select emp.firstnme, emp.lastname, emp.salary+emp.bonus, emp.sex
62from EMPLOYEE emp
63where emp.salary+emp.bonus > 50000
64and UPPER(emp.sex) = 'F'
65order by emp.LASTNAME;
66
67--5. Wyœwietliæ pensjê pracownika netto, policzyæ 18% podatek i podaæ pensjê z podatkiem.
68select * from employee;
69select emp.firstnme, emp.lastname, emp.salary, emp.salary*1.18 as "FULL_SAL"
70from EMPLOYEE emp
71order by emp.LASTNAME;
72
73--6. Podaæ czas z zegara systemowego i datê zegara systemowego.
74select to_char(SYSDATE) from sysibm.dual;
75
76--7. Podaæ którzy z pracowników s¹ w wieku z przedzia³u 30 do 50 lat.
77select * from employee;
78select emp.firstnme, emp.lastname, emp.birthdate
79from EMPLOYEE emp
80where substring(emp.BIRTHDATE from 1 for 4) between '1968' and '1988'
81order by emp.lastname;
82
83--8. Podaæ w jakim dziale pracuje pracownik.
84select * from employee;
85select * from department;
86
87select emp.firstnme, emp.lastname, dept.deptname
88from EMPLOYEE emp, DEPARTMENT dept
89where emp.workdept = dept.deptno
90order by dept.deptname, emp.lastname;
91
92--9. Podaj w jakim dziale pracuje pracownik i uporz¹dkuj malej¹co po dziale i rosn¹co po nazwisku.
93select emp.firstnme, emp.lastname, dept.deptname
94from EMPLOYEE emp, DEPARTMENT dept
95where emp.workdept = dept.deptno
96order by dept.deptname desc, emp.lastname;
97
98--10. Podaj w jakim dziale pracuje pracownik i uporz¹dkuj malej¹co po dziale i rosn¹co po nazwisku ograniczaj¹c liczbê krotek do dzia³ów zaczynaj¹cych siê na literê a lub s.
99select emp.firstnme, emp.lastname, dept.deptname
100from EMPLOYEE emp, DEPARTMENT dept
101where emp.workdept = dept.deptno
102and (substring(upper(dept.deptname) from 1 for 1) = 'A'
103or substring(upper(dept.deptname) from 1 for 1) = 'S')
104order by dept.deptname;
105
106--11. Czy jest dzia³ firmy w którym nikt nie pracuje.
107select * from employee;
108select * from department;
109
110select * from employee
111where employee.workdept = 'B01';
112
113select dept.deptname
114from DEPARTMENT dept, EMPLOYEE emp
115where dept.deptno = emp.workdept
116and (select count(*)
117from EMPLOYEE emp
118where emp.workdept = dept.deptno) = 0;
119-- to jest zle
120
121--11a. Za pomoc¹ EXISTS znaleŸæ departament w którym nikt nie pracuje.
122
123select dept.deptname
124from DEPARTMENT dept
125where not exists
126(select * from EMPLOYEE emp
127where emp.workdept = dept.deptno
128)order by dept.deptname;
129
130--12. Podaæ w jakim dziale jakiego mamy szefa.
131select emp.firstnme, emp.lastname, dept.deptname
132from employee emp, department dept
133where emp.workdept = dept.deptno
134and emp.job = 'MANAGER'
135
13613. Wyœwietliæ pary pracowników pracuj¹cych na tym samym stanowisku.
137select emp1.firstnme, emp1.lastname, emp2.firstnme, emp2.lastname from employee emp1, employee emp2 where emp1.job = emp2.job
138
13914. Wyœwietliæ pracowników i obok podaæ kto jest szefem (szefa danego departamentu)
140select emp1.firstnme, emp1.lastname, (select emp.lastname from employee emp where emp.workdept = emp1.workdept and emp.job='MANAGER')
141from employee emp1, department dept
142where emp1.workdept=dept.deptno
143
144--15. Zliczyæ ilu pracowników pracuje w danym dziale.
145select count (*) from employee where employee.workdept = 'E11'
146
147--15a. Zliczyæ ile jest kobiet pracowników.
148select count (*) from employee where employee.sex = 'F'
149
15015b. Podaæ jako jeden zestaw rekordów ile jest kobiet pracowników oraz ile jest mê¿czyzn pracowników.
151 W dodatkowej kolumnie okreœliæ czy liczba tyczy siê kobiet czy mê¿czyzn
152select 'F', count(*) from employee emp where emp.sex = 'F' union all select 'M', count(*) from employee emp where emp.sex = 'M'
153
154
155--another
156select d.deptname, e.salary, e.lastname
157from department as d join employee as e on d.deptno = e.workdept
158where e.salary in (
159select max(e1.salary)
160from employee as e1
161where e.workdept = e1.workdept
162)
163order by d.deptname asc;
164
165
166--lab3
167--16. Podaj Jaki pracownik ma pensjê wiêksz¹ od œredniej pensji liczonej ze wszystkich pracowników.
168--17. Podaj jaki pracownik pracuje w departamencie na literê od c do p.
169
170--18. Wybaraæ wszystkich pracowników którzy zarabiaj¹ wiêcej ni¿ ktokolwiek w departamencie A00
171select emp.lastname, emp.workdept, emp.salary
172from employee emp
173order by emp.salary desc;
174
175with
176max1 as (select max(emp.salary) as value1
177from employee as emp
178where emp.workdept = 'A00')
179select emp.lastname, emp.salary
180from employee emp
181where emp.salary > (select * from max1);
182
183
184--19. Wybaraæ wszystkich pracowników którzy zarabiaj¹ wiêcej od wszystkich w departamencie A00
185with
186sum1 as (select sum(emp.salary) as value1
187from employee emp
188where emp.workdept = 'A00')
189select emp.lastname, emp.salary
190from employee emp
191where emp.salary > (select * from sum1);
192
193--20. Wybraæ zawody w których œrednia p³aca jest wiêksza in¿ œrednia p³aca na stanowisku manager
194with avgmanager as (select avg(emp.salary) as value1
195from employee emp
196where emp.job = 'MANAGER')
197select avg(emp.salary) as avg1, emp.job
198from employee emp
199where avg1 in employee
200group by emp.job;
201
202select * from dept;
203drop table A2.Wojewodztwa;
204-- Tworzenie tabeli wojewodztwa
205CREATE TABLE A2.Wojewodztwa
206( WojewodztwoID INTEGER NOT NULL,
207Nazwa VARCHAR(20) NOT NULL,
208Aktywne INTEGER WITH DEFAULT 1,
209constraint check_aktywne check (Aktywne in ('0','1')),
210constraint pk_wojewodztwa primary key (WojewodztwoID));
211
212drop table A2.Miasta;
213-- Tworzenie tabeli miasta
214CREATE TABLE A2.Miasta
215( MiastoID INTEGER NOT NULL,
216Nazwa VARCHAR(30) NOT NULL,
217WojewodztwoID INTEGER,
218constraint pk_miasta primary key (MiastoId),
219constraint fk_wojewodztwo foreign key (WojewodztwoId)
220 references A2.Wojewodztwa (WojewodztwoId));
221
222-- <ScriptOptions statementTerminator=";" />
223select date(current timestamp) from sysibm.sysdummy1;
224drop table a2.klienci;
225
226CREATE TABLE A2.Klienci (
227KlientID INTEGER NOT NULL,
228Nazwisko VARCHAR(30) NOT NULL,
229Imie VARCHAR(30) NOT NULL,
230Pesel CHAR(11) UNIQUE NOT NULL WITH DEFAULT 0,
231Data_ur DATE WITH DEFAULT NULL,
232Data_zatr DATE WITH DEFAULT NULL,
233Pensja DECIMAL(5,0) WITH DEFAULT 0 NOT NULL,
234Pensja_roczna DECIMAL(5,0) NOT NULL GENERATED ALWAYS AS ( Pensja*12 ),
235Ulica VARCHAR(30) WITH DEFAULT NULL,
236Numer VARCHAR(30) WITH DEFAULT NULL,
237Mieszkania INTEGER WITH DEFAULT NULL,
238Miasto_ID INTEGER,
239constraint pk_klienci primary key (KlientID),
240constraint fk_miasto foreign key (Miasto_ID)
241 references A2.Miasta (MiastoID),
242constraint check_dates check (Data_ur >= Data_zatr),
243constraint check_pensja check (Pensja >= 0) );
244
245--dodawanie danych
246insert into a2.wojewodztwa (wojewodztwoid, nazwa) values
247(1, 'Malopolskie'),
248(2, 'Lodzkie'),
249(3, 'Swietokrzyskie');
250
251insert into a2.miasta (miastoid, nazwa, wojewodztwoid) values
252(1, 'Lodz', 2),
253(2, 'Zgierz', 2),
254(3, 'Krakow', 1);
255
256insert into a2.klienci (klientid, nazwisko, imie, miastoid) values
257(1, 'Kowalski', 'Jan', 1),
258(2, 'Buciora', 'Marcin', 2);
259
260select * from a2.wojewodztwa;
261select * from a2.miasta;
262select * from a2.klienci;
263
264--trzeba zmienic w 'sprawdzanie poprawnosci' znak zakonczenia instrukcji na @
265
266select * from emp@
267
268CREATE SCHEMA A1N@
269
270values DAYOFWEEK(current date)@
271
272--drop table
273drop table a1n.cl_sched @
274--create table
275create table a1n.cl_sched
276(id int not null generated always as identity,
277 starting time null,
278 ending time null,
279 DAY int null
280) @
281--create trigger
282CREATE or replace TRIGGER a1n.validate_sched
283NO CASCADE BEFORE INSERT ON a1n.cl_sched
284REFERENCING NEW AS n
285FOR EACH ROW
286MODE DB2SQL
287BEGIN ATOMIC
288IF (n.DAY IS NULL) THEN
289set n.DAY = DAYOFWEEK(current_date);
290END IF;
291-- supply default value for ending time if null
292IF (n.ending IS NULL) THEN
293SET n.ending = n.starting + 1 HOUR;
294END IF;
295-- ensure that class does not end beyond 9pm
296IF (n.ending > '21:00') THEN
297SIGNAL SQLSTATE '80000'
298SET MESSAGE_TEXT='class ending time is beyond 9pm';
299ELSEIF (n.DAY=1 or n.DAY=7) THEN
300SIGNAL SQLSTATE '80001'
301SET MESSAGE_TEXT='class cannot be scheduled on a weekend';
302END IF;
303END@
304
305select * from a1n.cl_sched@
306
307insert into a1n.cl_sched(starting, ending) values ('20:50',NULL)@ --No
308insert into a1n.cl_sched(starting, ending, day) values ('19:50','21:00',1)@ --No
309insert into a1n.cl_sched(starting, ending, day) values ('19:50','21:01',NULL)@ --No
310insert into a1n.cl_sched(starting, ending, day) values ('19:50','21:00',NULL)@ --Ok
311insert into a1n.cl_sched(starting, ending) values ('18:50',NULL)@ --Ok
312
313
314
315-------------------------------------------------------------------------------------------------------
316
317create schema a1n@
318
319CREATE TABLE a1n.DEPARTAMENT (
320ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ,
321NAZWA VARCHAR(20) NOT NULL,
322PRIMARY KEY ( ID ) )@
323
324CREATE TABLE a1n.EMPLOYEES (
325ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ,
326IMIE VARCHAR(50) NOT NULL,
327NAZWISKO VARCHAR(50) NOT NULL,
328PRIMARY KEY ( ID ) )@
329
330CREATE TABLE a1n.WORKS (
331ID_DEPARTAMENT INTEGER NOT NULL,
332ID_EMPLOYEE INTEGER NOT NULL,
333DATE_FROM DATE NOT NULL,
334DATE_TO DATE NOT NULL )@
335
336CREATE TABLE a1n.HISTORICAL (
337ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ,
338ID_EMPLOYEE INTEGER NOT NULL,
339ID_DEPARTAMENT INTEGER NOT NULL,
340DATE_FROM DATE NOT NULL,
341DATE_TO DATE NOT NULL,
342PRIMARY KEY ( ID ) )@
343
344CREATE TRIGGER a1n.HISTORICAL_INSERT_TRIGGER
345 AFTER DELETE ON a1n.WORKS
346 REFERENCING OLD AS OLD
347 FOR EACH ROW MODE DB2SQL
348 BEGIN ATOMIC
349 INSERT INTO a1n.HISTORICAL (ID_EMPLOYEE, ID_DEPARTAMENT, DATE_FROM, DATE_TO) VALUES (OLD.ID_EMPLOYEE, OLD.ID_DEPARTAMENT, OLD.DATE_FROM, OLD.DATE_TO);
350 END@
351
352INSERT INTO a1n.DEPARTAMENT (NAZWA) VALUES ('IT')@
353INSERT INTO a1n.DEPARTAMENT (NAZWA) VALUES ('ZARZADZANIE')@
354INSERT INTO a1n.DEPARTAMENT (NAZWA) VALUES ('HR')@
355
356INSERT INTO a1n.EMPLOYEES (IMIE,NAZWISKO) VALUES ('ADAM','KOWALSKI')@
357INSERT INTO a1n.EMPLOYEES (IMIE,NAZWISKO) VALUES ('ANDRZEJ','KOWAL')@
358INSERT INTO a1n.EMPLOYEES (IMIE,NAZWISKO) VALUES ('MICHAL', 'SZPAK')@
359
360INSERT INTO a1n.WORKS (ID_EMPLOYEE, ID_DEPARTAMENT, DATE_FROM, DATE_TO) VALUES (1,2, CURRENT_DATE, CURRENT_DATE - 1 DAY)@
361INSERT INTO a1n.WORKS (ID_EMPLOYEE, ID_DEPARTAMENT, DATE_FROM, DATE_TO) VALUES (2,3, CURRENT_DATE - 1 MONTH, CURRENT_DATE)@
362
363DELETE FROM a1n.WORKS@
364
365select * from a1n.works@
366SELECT * FROM a1n.HISTORICAL@
367
368------------------------------------------------------------------------------------------------------
369
370
371drop table a1n.Persons@
372
373create table a1n.Persons (id int not null primary key, nazwa VARCHAR(20))@
374
375create or replace trigger a1n.autoiterator
376no cascade before insert on a1n.Persons
377referencing new as n
378for each row
379mode db2sql
380begin atomic
381if (n.id is null) then
382set n.id=(select count(*) from a1n.Persons);
383end if;
384set n.nazwa=UPPER(n.nazwa);
385end@
386
387
388
389insert into a1n.Persons(id, nazwa) values (null, 'Dawid')@
390insert into a1n.Persons(id, nazwa) values (null, 'Adrian')@
391insert into a1n.Persons(id, nazwa) values (null, 'Kacper')@
392insert into a1n.Persons(id, nazwa) values (null, 'Piotr')@
393
394select * from a1n.Persons@
395
396create table a1n.Persons_hist (
397id int not null primary key,
398nazwa VARCHAR(20),
399username VARCHAR(20),
400del_date DATE,
401del_time time,
402oper_type VARCHAR(20)
403)@
404
405create or replace trigger a1n.historydelete
406after delete on a1n.Persons
407referencing old as old
408for each row
409mode db2sql
410begin atomic
411insert into a1n.Persons_hist(id, nazwa, username, del_date, del_time, oper_type) values (old.id, old.nazwa, current_user, DATE(current timestamp), time(current timestamp), 'delete' );
412end@
413
414create or replace trigger a1n.historymodify
415after update on a1n.Persons
416referencing old as old
417for each row
418mode db2sql
419begin atomic
420insert into a1n.Persons_hist(id, nazwa, username, del_date, del_time, oper_type) values (old.id, old.nazwa, current_user, DATE(current timestamp), time(current timestamp), 'update' );
421end@
422
423--nie dziala
424create or replace trigger a1n.historymanager
425after update or delete on a1n.Persons
426referencing old as old
427for each row
428mode db2sql
429begin atomic
430if deleting then
431insert into a1n.Persons_hist(id, nazwa, username, del_date, del_time, oper_type) values (old.id, old.nazwa, current_user, DATE(current timestamp), time(current timestamp), 'delete' );
432elseif updating then
433insert into a1n.Persons_hist(id, nazwa, username, del_date, del_time, oper_type) values (old.id, old.nazwa, current_user, DATE(current timestamp), time(current timestamp), 'update' );
434endif;
435end@
436
437select * from a1n.Persons@
438select * from a1n.Persons_hist@
439delete from a1n.Persons where id=0@
440
441update a1n.Persons
442set nazwa='Piotr'
443where id=3
444
445select * from emp@
446
447create procedure sum( in p_a integer, in p_b integer, out p_s integer)
448language sql
449specific sum_ab
450begin
451set p_s = p_a + p_b;
452end@
453
454create procedure sum( in p_a integer, in p_b integer, in p_c integer, out p_s integer)
455language sql
456specific sum_abc
457begin
458set p_s = p_a + p_b + p_c;
459end@
460
461call sum(2,3,5,?)@
462
463create or replace procedure
464wynik(out p_s integer)
465begin
466 declare x int;
467 call sum(10,10,10,x);
468 call sum(x,30,p_s);
469end@
470
471create or replace procedure
472employee_count(out p_s integer)
473begin
474select count(*) into p_s from employee;
475end@
476
477create or replace procedure
478get_dept_emp_count(in deptName1 varchar(36), out emp_count integer)
479begin
480select count(*) into emp_count from employee emp, department dept
481where dept.deptname = deptName1
482and emp.workdept = dept.deptno;
483end@
484
485-- inny sposob zapytania select count(*)
486--select count(*) into emp_count from
487--Employee e inner join Department d
488--on e.workdept = d.deptno
489--where d.deptname = deptName1;
490
491select * from department@
492select * from employee@
493
494CREATE OR REPLACE PROCEDURE PROCEDURE1 ()
495 DYNAMIC RESULT SETS 1
496P1: BEGIN
497 -- #######################################################################
498 -- # Returns all tables created by DB2ADMIN
499 -- #######################################################################
500 -- Declare cursor
501 DECLARE cursor1 CURSOR WITH RETURN for
502 SELECT lastname, salary FROM EMPLOYEE;
503
504 -- Cursor left open for client application
505 OPEN cursor1;
506
507 --CLOSE cursor1;
508
509END P1@
510
511SELECT NAME FROM SYSIBM.SYSTABLES
512WHERE CREATOR = 'DB2ADMIN' ORDER BY NAME@
513
514SELECT lastname, salary FROM EMP@
515
516
517CREATE OR REPLACE FUNCTION FUNCTION1 ()
518 RETURNS INTEGER
519 NO EXTERNAL ACTION
520F1: BEGIN ATOMIC
521 RETURN
522 -- #######################################################################
523 -- # Replace the SQL statement with your statement.
524 -- # Note: Be sure to end statements with the terminator character (usually ';')
525 -- #
526 -- # The example SQL statement SELECT COUNT(*) FROM SYSIBM.SYSTABLES
527 -- # returns the count of tables in SYSIBM.SYSTABLES
528 -- ######################################################################
529 SELECT COUNT(*) FROM SYSIBM.SYSTABLES;
530END@
531
532select FUNCTION1() from sysibm.dual@
533values FUNCTION1()@
534
535create or replace function trim_both (p_var VARCHAR(100))
536returns varchar(100)
537language sql
538specific trim
539return ltrim(rtrim(p_var))@
540
541select length(' abc ') from sysibm.dual@
542select length(trim_both(' abc ')) from sysibm.dual@
543
544create function getEnumEmployee(p_dept VARCHAR(3))
545returns table
546 (empno char(6),
547 lastname VARCHAR(15),
548 firstname VARCHAR(15))
549specific getEnumEmployee
550return
551 select e.empno, e.lastname, e.firstnme
552 from employee e
553 where e.workdept = p_dept@
554
555select * from table (getEnumEmployee('A00')) T @