Grupa O. Skibskiego
+3
marcinek
juho
Gricha
7 posters
Page 1 of 1
Grupa O. Skibskiego
Robiliście może zagnieżdżone zapytania? Niestety mam problem ze stworzeniem do 4.1 jakiegoś sprawnego zapytania działającego.
Gricha- Liczba postów : 425
Join date : 2010-10-12
Age : 32
Skąd : Myszków
Re: Grupa O. Skibskiego
Zamieszczam rozwiązania do pierwszych labów:
LAB1
ZAD2
3) SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL ORDER BY mgr DESC;
4) SELECT grade, (losal + hisal)/2 AS avgsal FROM salgrade;
5) SELECT ename, hiredate FROM emp WHERE job='ANALYST' OR (ename LIKE 'A%' AND sal>1500 AND sal<2000);
6) SELECT DISTINCT sin(3.14) FROM emp;
7) SELECT * FROM emp WHERE mgr IS NULL;
ZAD3
1) SELECT job, AVG(sal), COUNT(*) FROM emp GROUP BY job;
2) SELECT deptno, COUNT(comm) FROM emp GROUP BY deptno HAVING COUNT(comm)>0;
3) SELECT deptno, COUNT(comm) FROM emp GROUP BY deptno;
4) SELECT job, AVG(sal) FROM emp GROUP BY job HAVING COUNT(*)>2;
5) SELECT deptno FROM emp GROUP BY deptno, job HAVING MAX(sal)-MIN(sal)>300;
6) SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING COUNT(ename) > COUNT(mgr);
7) SELECT SUM(sal) FROM emp GROUP BY job HAVING COUNT(*)=1;
LAB1
ZAD2
3) SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL ORDER BY mgr DESC;
4) SELECT grade, (losal + hisal)/2 AS avgsal FROM salgrade;
5) SELECT ename, hiredate FROM emp WHERE job='ANALYST' OR (ename LIKE 'A%' AND sal>1500 AND sal<2000);
6) SELECT DISTINCT sin(3.14) FROM emp;
7) SELECT * FROM emp WHERE mgr IS NULL;
ZAD3
1) SELECT job, AVG(sal), COUNT(*) FROM emp GROUP BY job;
2) SELECT deptno, COUNT(comm) FROM emp GROUP BY deptno HAVING COUNT(comm)>0;
3) SELECT deptno, COUNT(comm) FROM emp GROUP BY deptno;
4) SELECT job, AVG(sal) FROM emp GROUP BY job HAVING COUNT(*)>2;
5) SELECT deptno FROM emp GROUP BY deptno, job HAVING MAX(sal)-MIN(sal)>300;
6) SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING COUNT(ename) > COUNT(mgr);
7) SELECT SUM(sal) FROM emp GROUP BY job HAVING COUNT(*)=1;
Last edited by juho on Mon Oct 10, 2011 5:52 pm; edited 1 time in total
Re: Grupa O. Skibskiego
mimuw.edu.pl/~oski/bd
Gricha- Liczba postów : 425
Join date : 2010-10-12
Age : 32
Skąd : Myszków
Re: Grupa O. Skibskiego
Częściowe rozwiązania do drugich labów:
LAB2:
ZAD1
1) SELECT X.empno, X.ename, (SELECT ename FROM emp WHERE empno=X.mgr) FROM emp X;
2) SELECT ename, hiredate FROM emp WHERE hiredate = (SELECT MIN(hiredate) FROM emp);
3) SELECT dname FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp GROUP BY deptno);
4) SELECT job, MAX(empcount) FROM (SELECT job, deptno, COUNT(ename) AS empcount FROM emp GROUP BY job, deptno) GROUP BY job;
5) SELECT SUM(deptjobmax) FROM (SELECT ename, sal, deptno, job, (SELECT maxsal FROM (SELECT deptno, job, MAX(sal) AS maxsal FROM emp GROUP BY deptno, job) WHERE deptno=emp.deptno AND JOB=emp.job) AS deptjobmax FROM emp);
ZAD2
1) SELECT DISTINCT loc FROM dept, emp WHERE emp.deptno = dept.deptno AND emp.job='SALESMAN';
2) SELECT worker.ename, boss.ename, loc FROM emp worker, emp boss, dept WHERE worker.mgr = boss.empno AND boss.deptno = dept.deptno;
2 lepiej) SELECT worker.ename, boss.ename, loc FROM emp worker LEFT JOIN emp boss ON worker.mgr = boss.empno LEFT JOIN dept ON boss.deptno = dept.deptno;
3) SELECT dept.deptno, COUNT(emp.ename) FROM emp, dept WHERE emp.deptno = dept.deptno GROUP BY dept.deptno;
3 lepiej) SELECT dept.deptno, COUNT(emp.ename) FROM emp RIGHT JOIN dept ON emp.deptno = dept.deptno GROUP BY dept.deptno;
LAB2:
ZAD1
1) SELECT X.empno, X.ename, (SELECT ename FROM emp WHERE empno=X.mgr) FROM emp X;
2) SELECT ename, hiredate FROM emp WHERE hiredate = (SELECT MIN(hiredate) FROM emp);
3) SELECT dname FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp GROUP BY deptno);
4) SELECT job, MAX(empcount) FROM (SELECT job, deptno, COUNT(ename) AS empcount FROM emp GROUP BY job, deptno) GROUP BY job;
5) SELECT SUM(deptjobmax) FROM (SELECT ename, sal, deptno, job, (SELECT maxsal FROM (SELECT deptno, job, MAX(sal) AS maxsal FROM emp GROUP BY deptno, job) WHERE deptno=emp.deptno AND JOB=emp.job) AS deptjobmax FROM emp);
ZAD2
1) SELECT DISTINCT loc FROM dept, emp WHERE emp.deptno = dept.deptno AND emp.job='SALESMAN';
2) SELECT worker.ename, boss.ename, loc FROM emp worker, emp boss, dept WHERE worker.mgr = boss.empno AND boss.deptno = dept.deptno;
2 lepiej) SELECT worker.ename, boss.ename, loc FROM emp worker LEFT JOIN emp boss ON worker.mgr = boss.empno LEFT JOIN dept ON boss.deptno = dept.deptno;
3) SELECT dept.deptno, COUNT(emp.ename) FROM emp, dept WHERE emp.deptno = dept.deptno GROUP BY dept.deptno;
3 lepiej) SELECT dept.deptno, COUNT(emp.ename) FROM emp RIGHT JOIN dept ON emp.deptno = dept.deptno GROUP BY dept.deptno;
Re: Grupa O. Skibskiego
Orientujecie się czy kolos będziemy pisać na kompach czy kartkach? Czy można mieć jakieś notatki?
Heniek- Liczba postów : 329
Join date : 2010-10-12
Re: Grupa O. Skibskiego
Wydaje mi się, że na komputerach. I że można korzystać z neta chyba
Gricha- Liczba postów : 425
Join date : 2010-10-12
Age : 32
Skąd : Myszków
Re: Grupa O. Skibskiego
juho wrote:LAB1
ZAD3
7) SELECT SUM(sal) FROM emp GROUP BY job HAVING COUNT(*)=1;
Niestety nie jest to dobra odpowiedź na to pytanie.
Poprawna, ale brzydka:
-- zad3.7 laby1
select deptno, job, avg(sal) from emp group by job, deptno having count(*)=1 order by deptno, job;
Marcin- Liczba postów : 10
Join date : 2011-06-07
Re: Grupa O. Skibskiego
Pytanie - czy komuś takie gówno wyskakiwało i wie jak temu zaradzić?
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-00054: resource busy and acquire with NOWAIT specified
Gricha- Liczba postów : 425
Join date : 2010-10-12
Age : 32
Skąd : Myszków
Re: Grupa O. Skibskiego
rathma wrote:
5) SELECT A.ename FROM emp A LEFT JOIN emp B ON A.hiredate < B.hiredate WHERE B.empno IS NULL;
Powinno być ">" zgodnie z treścią zadania.
ThomasJefferson- Liczba postów : 175
Join date : 2010-10-14
Re: Grupa O. Skibskiego
Racja. Dzięki.ThomasJefferson wrote:Powinno być ">" zgodnie z treścią zadania.
rathma- Liczba postów : 102
Join date : 2010-12-15
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum