Content
KONSWERSJA DATE
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
select ROUND(24* (to_date((select czas_1 from events where event_id=5255089), 'hh24:mi:ss')
- to_date((select czas_1 from events where event_id=5253893), 'hh24:mi:ss')),2) AS diff_godzin
from dual;
SELECT USER_ID FROM EVENTS WHERE DATA LIKE '2020-09-%'
ORDER BY USER_ID
;
SELECT DISTINCT (DATA),EVENT_TYPE FROM EVENTS WHERE USER_ID = 101 AND EVENT_TYPE = 0 OR EVENT_TYPE = 16 AND DATA LIKE '2020-09-%'
ORDER BY DATA ASC
;
UPDATE EVENTS SET GROUP_ID=5 WHERE EVENT_ID>100 AND EVENT_ID<500000000;
CREATE OR REPLACE VIEW widok_hr AS
Select EVENTS.EVENT_ID, EVENTS.DATA, EVENTS.TIME, USERS.FIRSTNAME, USERS.LASTNAME, GROUPS.NAME, EVENTS.GROUP_ID
FROM USERS
inner join EVENTS
on users.userid = events.user_id
inner join GROUPS
on groups.groupid = events.group_id;
SELECT to_char(to_date(TIME,'HH24:MI:SS'),'HH24:MI:SS') AS Czas FROM EVENTS;
SELECT convert(TIME,DATE) FROM EVENTS;
INSERT INTO EVENTS (DATA_2)
SELECT to_date(CZAS_1,'HH24:MI:SS') AS Czas FROM EVENTS
WHERE EVENT_TYPE = 0 OR EVENT_TYPE = 16
;
INSERT INTO EVENTS (DATA_2) SELECT CZAS_1 FROM EVENTS;
update EVENTS set DATA_2 = to_date(CZAS_1, 'HH24:MI:SS');
SELECT EXTRACT(HOUR FROM data_2) || ':' ||EXTRACT(MINUTE FROM data_2)|| ':' ||EXTRACT(SECOND FROM data_2) AS Czas, USER_ID,EVENT_TYPE,DATA,FIRSTNAME,LASTNAME FROM events
inner join USERS
on EVENTS.USER_ID=USERS.USERID
WHERE EVENT_TYPE = 0 OR EVENT_TYPE = 16
ORDER BY FIRSTNAME ;
ALTER TABLE EVENTS ADD DATA_3 DATE
INSERT INTO EVENTS (DATA_3)
SELECT EXTRACT(HOUR FROM data_2) || ':' ||EXTRACT(MINUTE FROM data_2)|| ':' ||EXTRACT(SECOND FROM data_2) AS wyciag, USER_ID,EVENT_TYPE,DATA FROM events
;
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'HH24:MI:SS';
SELECT DATA_2, DATA FROM EVENTS WHERE EVENT_TYPE=0 AND USER_ID=294 AND EVENT_ID=32500;
SELECT DATA_2,DATA FROM EVENTS WHERE EVENT_TYPE=16 AND USER_ID=294 AND EVENT_ID=32501;
declare
wejscie294 VARCHAR2:='SELECT DATA_2 FROM EVENTS WHERE EVENT_TYPE=0 AND USER_ID=294 AND EVENT_ID=32500';
wyjscie294 VARCHAR2:='SELECT DATA_2 FROM EVENTS WHERE EVENT_TYPE=16 AND USER_ID=294 AND EVENT_ID=32501';
begin
null;
end;
UPDATE EVENTS SET EVENT_ID = 32500 WHERE EVENT_ID =5225756
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
14.09.2020
INSERT INTO EVENTS (DATA2)
SELECT to_char(to_date(TIME,'HH24:MI:SS'),'HH24:MI:SS') AS Czas FROM EVENTS
WHERE EVENT_TYPE = 0 OR EVENT_TYPE = 16
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
;
SELECT DISTINCT * FROM EMP WHERE SAL<=5000 OR SAL>=1500 ORDER BY SAL DESC
SELECT SUM (SAL) FROM EMP WHERE JOB='MANAGER'
SELECT SUM(SAL) AS pensja FROM EMP WHERE JOB LIKE '%GER' OR JOB LIKE '%ENT' OR JOB LIKE '%SMAN' OR JOB LIKE '%YST' GROUP BY JOB;
create or replace procedure wypisywacz
(imie varchar2, nazwisko varchar2, wzrost OUT number)
is
begin
dbms_output.put_line('WITAJ '||imie||' '||nazwisko);
wzrost:=178;
end;
CREATE VIEW widok_hr as select SUM(pensja) as pensja_dzialow, kod_departamentu
from si_pracownicy
WHERE kod_departamentu = 1 OR kod_departamentu = 2 OR kod_departamentu=3 OR kod_departamentu=4 OR kod_departamentu = 5
GROUP BY kod_departamentu
CREATE OR REPLACE VIEW WIDOK_CENY
AS SELECT ROUND(AVG(cena_sprzedazy),2) AS CENA_SREDNIA,typ_produktu
FROM SI_PRODUKTY
GROUP BY typ_produktu ;
TRIGGER
Create (or replace) trigger nazwa-triggera
before/after/instead of
insert/update/delete
on nazwa-tabel
(for each row)
(when...)
declare
...
begin
...
end;
++++++++++++++++NIGGER++++++
CREATE OR REPLACE TRIGGER INS_PRACOWNICY
BEFORE INSERT
ON si_pracownicy
FOR EACH ROW
DECLARE
czas date;
BEGIN
czas:= SYSDATE;
UPDATE SI_PRACOWNICY SET timestump_insert = czas;
NULL;
SELECT to_char(to_date(TIME,'HH24:MI:SS'),'HH24:MI:SS') FROM EVENTS;
END;
CREATE OR REPLACE VIEW widok_hr AS
Select EVENTS.ID_EVENT, EVENTS.DATA, EVENTS.TIME, USERS.FIRSTNAME, USERS.LASTNAME, GROUPS.NAME, EVENTS.GROUP_ID
FROM USERS
inner join EVENTS
on users.id_user = events.user_id
inner join GROUPS
on groups.groupid = events.group_id
UPDATE EVENTS SET GROUP_ID=1 WHERE ID_EVENT>1 AND ID_EVENT<100
UPDATE EVENTS SET GROUP_ID=5 WHERE ID_EVENT>100 AND ID_EVENT<500000000
SELECT convert(varchar(30),date(HH:MM:SS)), TIME FROM EVENTS;
SELECT to_char(to_date(TIME,'HH24:MI:SS'),'HH24:MI:SS') FROM EVENTS;
ALTER TABLE EVENTS ADD DATA2 DATE ;
UPDATE EVENTS SET DATA2=TIME ;
INSERT INTO EVENTS (DATA2)
SELECT (to_char(to_date(TIME,'HH24:MI:SS'),'HH24:MI:SS')) FROM EVENTS;
FROM EVENTS
INSERT INTO EVENTS SELECT