sql

@Modifed BY Kasia

===================================================Zadania z zestawu 3=========13====
1. SELECT TABLE_NAME FROM user_tables;

2. DESC DEPT;
   DESC EMP;
   DESC SALGRADE;

3. SELECT UNIQUE 432*432 FROM user_tables;

            lub

   SELECT 432*432 FROM dual;


4. SELECT UNIQUE 'Wojciech Balawender' FROM dept;

            lub

   SELECT 'Wojciech Balawender' FROM dual;

5. SELECT 'Wojciech Balawender' AS "Moje imię i nazwisko:" FROM dual;

6. SELECT ename, sal FROM emp;

7. SELECT ename, sal*12 FROM emp;

8. SELECT ename AS "Pracownik:" ,sal*12 AS "Zarobki:" FROM emp;

9. SELECT job FROM emp;

10. SELECT UNIQUE job FROM emp;

11. SELECT 323*9 FROM emp;

12. SELECT substr(hiredate+1,7,2) FROM emp;

    --Uwaga: substr(hiredate,7,2)+1 nie jest dobre (DATA!)

13. SELECT job FROM emp WHERE job LIKE '%R';

14. SELECT ename, sal FROM emp WHERE sal = 1250;

15. SELECT ename, sal FROM emp WHERE sal BETWEEN 3000 AND 5000;

16. SELECT ename, sal FROM emp WHERE job = 'MANAGER' AND sal BETWEEN 2450 AND 2900;

            lub

    SELECT ename, sal FROM emp WHERE job = 'MANAGER' AND sal < 2450 AND sal < 2900;

17. SELECT ename, sal FROM emp WHERE ename='SMITH'

18. SELECT ename, sal FROM emp WHERE ename LIKE 'S%'

19. SELECT SUBSTR( 'qwertyqwertyqwertyBazyDanych',
                   INSTR('qwertyqwertyqwertyBazyDanych','BazyDanych'),
                   LENGTH('BazyDanych')
                  ) FROM dual;

            lub

    SELECT SUBSTR('qwertyqwertyqwertyBazyDanych', 19,10) FROM dual;

            lub nawet

    SELECT SUBSTR('qwertyqwertyqwertyBazyDanych', 19) FROM dual;

20. SELECT INSTR('qwertyuiopasdfghjklzxcvbnm','g') FROM dual;

21. SELECT ename || ' zarabia $' || sal AS "Pracownik i jego zarobki" FROM emp;
22. SELECT ename, deptno, DECODE(deptno,'10','Dziesiaty','20','Dwudziesty','Trzydziesty') FROM emp;

--Co wypada znac jeszcze z tego rodziału:

--Aliasy w cudzyslowach - nie w apostrofach!!!!!!
SELECT sysdate FROM dual;
SELECT sal FROM emp WHERE sal IN (1000,2000,3000)
SELECT LOWER(ename) FROM EMP
INITCAP - pierwsza litera wielka
ROUND - zaokrąglenie
% - ciąg znaków
_ - pojedynczy znak

TO_CHAR Konwersja na napis

SELECT TO_CHAR(sysdate, 'DD.MM.YYYY HH24:MI:SS') FROM dual;

Trzy litery D oznaczają ilość dni, który upłynęły od początku roku
SELECT TO_NUMBER('17.74', '99.99') FROM dual;
ORDER BY - porządkuje rosnąco, DESC - malejąco
COUNT - zlicza wystąpienia
SELECT COUNT(ename), job FROM emp GROUP BY job HAVING SUM(sal)>=6000;


===================================================Zadania z zestawu 4=========24====

1. SELECT deptno, MIN(sal) FROM emp GROUP BY deptno;

2. SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) > 1600;

3. SELECT ename FROM emp WHERE ename LIKE 'N%';

4. SELECT ename FROM emp WHERE ename LIKE 'SMITH';

            lub

   SELECT ename FROM emp WHERE ename = 'SMITH';

5. SELECT ename,job,sal FROM emp WHERE sal BETWEEN 1000 AND 5000 ORDER BY sal;

6. SELECT ename,job,sal FROM emp WHERE sal BETWEEN 1000 AND 5000 AND JOB = 'SALESMAN' ORDER BY sal;

7. SELECT sysdate FROM dual;

            lub

    SELECT UNIQUE sysdate FROM emp;

8. decode(job,'CLERK','Urzędnik','SALESMAN','Sprzedawca','MANAGER','Menedżer','ANALYST','Analityk','Prezydent') AS Spolszczona FROM emp;

9. SELECT ename, job FROM emp WHERE job = 'CLERK' OR job = 'MANAGER';

10. SELECT SUM(sal) FROM emp;

11. SELECT avg(sal) FROM emp WHERE job = 'SALESMAN';

12. SELECT COUNT(ENAME) FROM emp;

13. SELECT COUNT(ENAME) FROM emp WHERE JOB = 'MANAGER';

14. SELECT SUM(sal), job FROM emp GROUP BY job;

15. SELECT COUNT(ename) FROM emp WHERE deptno = 20

16. SELECT TO_CHAR(AVG(sal),'999,999.99') FROM emp WHERE JOB!='president';

17. SELECT deptno, COUNT(deptno) FROM emp GROUP BY deptno HAVING COUNT(deptno) > 4;

18. SELECT 'SA UNIKALNE' FROM EMP HAVING COUNT(UNIQUE sal) = COUNT(sal);

19.
    -- Maksymalne zarobki mniejsze niz 1300 wybrane z zarobkow+:

    SELECT MAX(sal), job FROM emp  WHERE sal < 1300 GROUP BY job;

    -- Maksymalne zarobki mniejsze niz 1300 wybrane z maksymalnych zarobkow:

    SELECT JOB, MAX(sal) FROM EMP GROUP BY job HAVING MAX(sal) <= 1300;

20. SELECT COUNT(ename), job, deptno FROM emp GROUP BY job,deptno;

21. SELECT job,avg(sal) FROM emp GROUP BY job HAVING avg(sal) > 1000;

22. SELECT substr(HIREDATE,4,2) FROM emp GROUP BY substr(HIREDATE,4,2)  HAVING COUNT(substr(HIREDATE,4,2)) >= 2 ;

23. SELECT deptno, substr(hiredate,0,2) rok, COUNT(ENAME) FROM emp GROUP BY substr(hiredate,0,2), deptno ;

24. SELECT B.ename, COUNT(B.ename), B.deptno FROM emp A, emp B WHERE A.mgr = B.empno AND B.job = 'MANAGER' GROUP BY B.ename, B.deptno  HAVING COUNT(B.ename) > 2

25. SELECT ename FROM emp WHERE substr(hiredate,7,2)='01';

26. SELECT ename pracownik, substr(HIREDATE,7,2) || ' ' || decode(substr(HIREDATE,4,2),
    '01','Styczeń',
    '02','Luty',
    '03','Marzec',
    '04','Kwiecień',
    '05','Maj','06','Czerwiec','07','Lipiec','08','Sierpień','09','Wrzesień','10','Październik','11','Listopad','Grudzień') || ' 19' || substr(hiredate,0,2) FROM emp;

    --Prościej (By Kasia):

    SELECT ename Pracownik,to_char(hiredate,'dd month yyyy') Zatrudniony FROM emp;


27. SELECT ename FROM emp WHERE ename LIKE '%R%'

28. SELECT LENGTH(ename) FROM emp ORDER BY LENGTH(ename)

29. SELECT SUM(sal), job FROM emp WHERE JOB = 'MANAGER' OR JOB = 'PRESIDENT' GROUP BY job;

            --Jeśli razem, to:

    SELECT SUM(sal) FROM emp WHERE JOB = 'MANAGER' OR JOB = 'PRESIDENT'

30. -- Na literę J (nie wiem, skąd mi się to wzięło):

        SELECT SUM(sal) FROM emp WHERE ename LIKE 'J%';

    -- Prawidłowo(by Kasia):

        SELECT substr(ename,1,1), SUM(sal) FROM emp GROUP BY substr(ename,1,1);

31. SELECT avg(sal) FROM emp; -- nie mozna!

        więc:

    SELECT SUM(sal)/COUNT(ename) FROM emp;

32. SELECT ename || CASE WHEN sal = 3000 THEN ' rowno' WHEN sal < 1000 THEN ' mniej' WHEN sal > 1000 THEN ' wiecej'  END FROM emp;


===================================================Zadania z zestawu 5========="Przykładowe zadania na złączenia"====

1. SELECT ename, dname FROM emp A,dept B WHERE A.deptno = B.deptno;

2. SELECT ename, dname, loc FROM emp A,dept B WHERE A.deptno = B.deptno AND A.sal < 1500 ORDER BY A.DEPTNO DESC;

3. SELECT SUM(sal), loc FROM emp A,dept B WHERE A.deptno = B.deptno GROUP BY loc;

4.  -- Na skróty (20 jest w DALLAS):

    SELECT MAX(sal) FROM emp WHERE deptno = 20 AND job='SALESMAN' ;

    -- Prawidłowo (ale uwaga - nie ma zadnych sprzedawców w Dallas):

    SELECT MAX(sal), loc FROM emp A,dept B WHERE A.deptno = B.deptno AND B.loc = 'DALLAS' AND A.job='SALESMAN' GROUP BY loc;

5. SELECT A.ename, B.ename FROM emp A, emp B WHERE A.mgr = B.empno;

6. SELECT A.ename FROM emp A, emp B WHERE A.hiredate < B.hiredate AND A.mgr = B.empno

7. SELECT MIN(sal),dname FROM emp A,dept B WHERE A.deptno = B.deptno GROUP BY dname;

8. SELECT dname, COUNT(ename) FROM emp A,dept B WHERE A.deptno(+) = B.deptno GROUP BY dname;


===================================================Zadania z zestawu 5========="Przykładowe zadania na zagnieżdżenia SELECT"====

1. SELECT empno, ename FROM emp WHERE (SELECT MIN(sal) FROM emp) = sal;
2.
    -- By Kasia:
    SELECT MAX(sal),deptno FROM emp GROUP BY deptno HAVING avg(sal)>(SELECT MIN(sal) FROM emp WHERE job='CLERK');

    -- By Kasia:
3. SELECT MAX(sal),deptno FROM emp GROUP BY deptno HAVING avg(sal)>(SELECT MAX(sal) FROM emp WHERE deptno=30);

    -- By Kasia:
4. SELECT ename,sal FROM emp WHERE sal IN(SELECT avg(sal) FROM emp GROUP BY job)

    -- By Kasia:
5. SELECT empno,ename,sal FROM emp WHERE sal<(SELECT avg(emp1.sal) FROM emp emp1 WHERE deptno=emp1.deptno);

6. SELECT ename -FROM emp WHERE sal IN (SELECT avg(sal) FROM emp GROUP BY JOB);
    --Nie do końca poprawnie, bo in działa tu na oślep, ale średnie zarobki są unikalne
7. SELECT ename FROM emp WHERE sal > (SELECT MIN(sal) FROM emp WHERE deptno = 10)

===================================================Zadania z zestawu 5========="RÓŻNE"====
1. SELECT TABLE_NAME FROM user_tables;

   DESC DEPT;
   DESC EMP;
   DESC SALGRADE;

   -- By Kasia:
2. SELECT ename,sal FROM emp WHERE sal IN(SELECT MAX(sal) FROM emp);

3. SELECT A.ename, A.sal FROM emp A, (SELECT sal FROM emp WHERE ename LIKE 'S%') B WHERE A.sal = B.sal;

4. SELECT ename,sal FROM emp WHERE sal IN(SELECT MIN(sal) FROM emp GROUP BY deptno);


5. SELECT ename, sal,deptno FROM emp WHERE (SELECT MIN(sal) FROM emp WHERE deptno = 20) < sal ORDER BY sal DESC;

    -- Drugi sposób?

6. SELECT ename, sal,deptno FROM emp WHERE (SELECT MAX(sal) FROM emp WHERE deptno = 20) < sal ORDER BY sal DESC;

    -- Drugi sposób?

7. SELECT ename,sal,empno FROM emp WHERE sal<(SELECT avg(emp2.sal) FROM emp emp2 WHERE emp.deptno=emp2.deptno);

8.
    -- Konkatenacja z nazwami zawodów:
    SELECT ename || ' ' || job "Nazwiska i nazwy zawodów" FROM emp;

    -- Ale miała być z DZIAŁAMI (by Kasia):

    SELECT ename||' '||dname FROM emp,dept WHERE emp.deptno=dept.deptno;

9. SELECT ename, job, comm FROM emp WHERE job = 'SALESMAN' AND comm = '0'

10. SELECT ename, CASE WHEN comm IS NULL THEN 0 ELSE comm END FROM emp;

--CROSS JOIN wybadaj.