----------------------------------------------------------------------
-- LABORATORIUM 1
----------------------------------------------------------------------
DESC student;
SELECT * FROM student WHERE rok=3;
SELECT nazwisko, imiona FROM student;
SELECT nazwisko, imiona FROM student WHERE rok=3;
SELECT count(*) FROM student WHERE rok=3;
SELECT nazwisko FROM student
WHERE nazwisko LIKE 'M%';
--WHERE nazwisko='M%';
SELECT imiona, nazwisko
FROM student
WHERE imiona LIKE '%A'
OR imiona LIKE '%E';
SELECT count(*)
FROM student
WHERE imiona LIKE '%A'
OR imiona LIKE '%E';
SELECT count(*)
FROM student
WHERE imiona NOT LIKE '%A'
AND imiona NOT LIKE '%E';
SELECT nazwisko, imiona, rok FROM student
WHERE rok=3 OR rok=4 OR rok=5
--WHERE rok IN (3,4,5)
--WHERE rok>=3
--WHERE rok>2
--WHERE rok>2 rok<=5
--WHERE rok BETWEEN 3 AND 5
ORDER BY 3,nazwisko;
SELECT nazwisko, imiona FROM student
WHERE imiona IN ('MAREK', 'MACIEJ', 'PIOTR'); SELECT nazwisko, imiona FROM student
WHERE imie_ojca IS NULL;
DESC pojazdy;
SELECT DISTINCT typ FROM pojazdy;
SELECT DISTINCT marka FROM pojazdy
WHERE typ LIKE '%CIEZ%';
SELECT rok, count(*) FROM student
GROUP BY rok HAVING rok IS NOT NULL;
----------------------------------------------------------------------
-- LABORATORIUM 2
----------------------------------------------------------------------
DESC student;
SELECT nazwisko, imiona, rok, data_urodzenia
FROM student
WHERE nazwisko LIKE 'NOWA%';
SELECT nazwisko, imiona, rok
FROM student
WHERE nazwisko LIKE 'BORKOWSK_';
SELECT nazwisko, imiona, rok
FROM student
WHERE Lower(nazwisko) LIKE 'borkowsk_';
SELECT imiona FROM student
WHERE imiona BETWEEN 'MAR%' AND 'MIR%';
SELECT count(DISTINCT imiona) AS "Różne imiona",
count(*) AS "Ogółem" FROM student;
SELECT Concat(nazwisko,imiona) FROM student;
SELECT nazwisko||' '||imiona osoba FROM student;
SELECT nazwisko||' '||imiona AS "dane osobowe" FROM student;
SELECT Substr(nazwisko,1,3) naz
FROM student
WHERE rok=3;
SELECT Substr(nazwisko,-2,2) naz
FROM student
WHERE rok=3;
SELECT Substr(nazwisko,length(nazwisko)-1,2) naz
FROM student
WHERE rok=3;
SELECT Concat(Upper(Substr(nazwisko,1,1)),
Lower(Substr(nazwisko,2,Length(nazwisko)-1))) nazwisko
FROM student
WHERE rok=3;
SELECT Initcap(nazwisko) nazwisko
FROM student
WHERE rok=3;
SELECT Ltrim('ciocia klocia','cio') FROM dual;
SELECT Rtrim('ciocia klocia','cioa') FROM dual;
SELECT DISTINCT nazwisko, Length(nazwisko) FROM student
WHERE rok=3 AND Length(nazwisko)>6
ORDER BY 2 DESC, 1;
SELECT Instr('abcjdjabcjkjkfffabc','abc',1,2) FROM dual;SELECT imiona, Instr(imiona,'A',1) FROM student;
SELECT nazwisko, Replace(nazwisko,'SKA','SKI') FROM student
WHERE nazwisko LIKE '%SKA';
SELECT Trim(TRAILING 'A' FROM nazwisko)||'I' FROM student
WHERE nazwisko LIKE '%SKA';
SELECT Rpad(Lpad(nazwisko,length(nazwisko)+5,'*'),
length(nazwisko)+10,'*') FROM student;;
SELECT DISTINCT imiona FROM student
WHERE imiona LIKE '__A%';
SELECT DISTINCT imiona FROM student
WHERE Substr(imiona,3,1) LIKE 'A';
SELECT DISTINCT imiona FROM student
WHERE Instr(imiona,'A',3)=3;
----------------------------------------------------------------------
-- LABORATORIUM 3
----------------------------------------------------------------------
----------------FUNKCJE MATEMATYCZNE--------------------------------
DESC pracownicy;
SELECT nazwisko, etat, Abs(placa_pod-2380,29)
FROM pracownicy ORDER BY 3;
SELECT nazwisko, etat, Round(Abs(placa_pod-2380,29),1)
FROM pracownicy ORDER BY 3;
SELECT nazwisko, etat, Trunc(Abs(placa_pod-2380,29),-1)
FROM pracownicy ORDER BY 3;
SELECT 125.34*16 FROM dual;
SELECT Sqrt(125.34*16) FROM dual;
SELECT Round(Sqrt(125.34*16)) FROM dual;
SELECT Trunc(Sqrt(125.34*16)) FROM dual;
SELECT Ceil(Sqrt(125.34*16)) FROM dual;
SELECT Floor(Sqrt(125.34*16)) FROM dual;
----------------FUNKCJE OPERUJACE NA DATACH-------------------------
SELECT Sysdate FROM dual;
SELECT Sysdate-100, Sysdate+100 FROM dual;
SELECT Systimestamp FROM dual;
SELECT Add_months(sysdate, 7) FROM dual;
SELECT Last_day(Sysdate) FROM dual;
SELECT Trunc(Sysdate,'mm') FROM dual;
SELECT Round(Sysdate,'mm') FROM dual;
SELECT nazwisko, imiona,
Trunc(Months_between(sysdate,data_urodzenia)/12)
FROM student
WHERE rok=3 AND gr_dziekan=2
ORDER BY 3,1;
---------------FUNKCJE KONWERTUJACE---------------------------------
SELECT To_char(12,'RM')||' małp' FROM dual;
SELECT To_char(Sysdate, 'dd~~RM~~yy') FROM dual;
SELECT To_char(Sysdate,'CC') FROM dual;
SELECT To_char(To_char(Sysdate,'CC'),'RN') FROM dual;
SELECT To_char(Sysdate, 'day dd month')||'a '||
To_char(Sysdate, 'yyyy')||' roku' FROM dual;
SELECT To_char(Last_day(Sysdate),'day') FROM dual;
SELECT Round(Months_between(Sysdate,To_date('20-10-04','dd-mm-yy'))) FROM dual;
SELECT To_char(To_date('01-09-1939','dd-mm-yyyy'),'day Q') FROM dual; SELECT nr_albumu, Trunc(To_number(nr_albumu),-2) FROM student
WHERE rok=3 AND gr_dziekan=2 AND nr_albumu IS NOT NULL;
SELECT count(*) FROM student
WHERE Lower(Trim(To_char(data_urodzenia,'day'))) LIKE 'niedziela';
SELECT nazwisko, imiona, data_urodzenia FROM student
WHERE To_char(data_urodzenia,'dd-mm') IN
(To_char(Sysdate,'dd-mm'), To_char(Sysdate+1,'dd-mm'));
----------------------------------------------------------------------
-- LABORATORIUM 4
----------------------------------------------------------------------
--------------POZOSTALE FUNKCJE---------------------------------------
SELECT nazwisko, etat, placa_pod, Nvl(placa_dod,0) FROM pracownicy;
SELECT User, Uid FROM dual;
SELECT nazwisko, etat, placa_pod, decode(placa_dod,NULL,0,placa_dod)
FROM pracownicy;
-------------------GRUPOWANIE I FUNKCJE AGREGUJACE-------------------
SELECT rok, Count(*) FROM student
GROUP BY rok;
SELECT typ, marka, Count(*) FROM pojazdy
GROUP BY typ, marka;
SELECT To_char(data_urodzenia, 'day'), Count(*) FROM student
GROUP BY To_char(data_urodzenia, 'day')
HAVING To_char(data_urodzenia, 'day') IS NOT NULL
ORDER BY 2 DESC;
SELECT data_urodzenia, count(*) FROM student
WHERE data_urodzenia IS NOT NULL
GROUP BY data_urodzenia
HAVING Count(*)>2
ORDER BY 2 DESC;
SELECT rok,Count(*) FROM student
WHERE To_char(data_urodzenia, 'mm') LIKE '05'
GROUP BY rok;
SELECT rok,To_char(data_urodzenia, 'month'), Count(*) FROM student
WHERE rok IS NOT NULL AND data_urodzenia IS NOT NULL
GROUP BY rok, To_char(data_urodzenia, 'month')
ORDER BY 3 DESC, 2, 1;
SELECT Substr(nazwisko,1,1), Count(*) FROM student
GROUP BY Substr(nazwisko,1,1)
ORDER BY 1;
SELECT rok, Min(data_urodzenia) FROM student
WHERE rok IS NOT NULL AND data_urodzenia IS NOT NULL
GROUP BY rok;
SELECT typ, Count(*), Max(pojemnosc), Avg(pojemnosc), Min(pojemnosc)
FROM pojazdy
GROUP BY typ
HAVING Avg(pojemnosc)>1500;
SELECT rok, gr_dziekan, Count(*) liczba
FROM student
WHERE kierunek LIKE 'INFORM%' AND rok IS NOT NULL
AND gr_dziekan IS NOT NULL
GROUP BY rok, gr_dziekan
HAVING Count(*)>30
ORDER BY 1,2,3;
SELECT rok, Max(Length(nazwisko)) najdl_nazwisko
FROM student
GROUP BY rok;
DESC pracownicy;
SELECT id_zesp, Count(*)
FROM pracownicy
GROUP BY id_zesp;
SELECT etat, Count(*)
FROM pracownicy
GROUP BY etat;
SELECT id_zesp, etat, Count(*)
FROM pracownicy
GROUP BY id_zesp, etat;
SELECT id_zesp, Sum(placa_pod + Nvl(placa_dod,0))
FROM pracownicy
GROUP BY id_zesp;
SELECT To_char(data_urodzenia, 'yyyy'), Count(*)
FROM student
GROUP BY To_char(data_urodzenia, 'yyyy')
HAVING Count(*)>99
ORDER BY 2 DESC,1;
SELECT To_char(data_urodzenia, 'yyyy-mm'), Count(*)
FROM student
WHERE data_urodzenia IS NOT NULL
GROUP BY To_char(data_urodzenia, 'yyyy-mm')
HAVING Count(*)>10
ORDER BY 1, 2;