Datenbanken 1 – Uebung12 – Georg Kuschk – MI-10



##############  EMP-DEPT-Datenbank  ####################


1.)	SELECT DISTINCT A.ENAME FROM EMP A , EMP B
	WHERE (A.DEPTNO=B.DEPTNO) AND (A.HIREDATE<B.HIREDATE) AND (A.SAL<B.SAL);

	bzw.

	SELECT DISTINCT A.ENAME FROM EMP A , EMP B
	WHERE EXISTS (SELECT * FROM EMP B WHERE
	 (A.DEPTNO=B.DEPTNO) AND (A.HIREDATE<B.HIREDATE) AND (A.SAL<B.SAL));


	5 Ergebnistupel




2.)
	SELECT ENAME AS "Name" , SAL AS "Gehalt" ,
	CASE WHEN (NOT EXISTS (SELECT * FROM EMP E2 WHERE E2.MGR=E1.EMPNO))
	THEN 'kein Chef' ELSE 'Chef' END AS "Status"
	FROM EMP E1 ORDER BY SAL DESC;


	14 Ergebnistupel (6*'Chef' , 8*'kein Chef')





3.)	SELECT DISTINCT ENAME,SAL,DEPTNO FROM EMP E1
	WHERE SAL<0.5*(SELECT AVG(SAL) FROM EMP E2 WHERE E1.DEPTNO=E2.DEPTNO);


	2 Ergebnistupel




4.)	
	SELECT DEPTNO,MIN(SAL) FROM EMP
	GROUP BY DEPTNO
	HAVING MIN(SAL)>(SELECT MIN(SAL) FROM EMP WHERE DEPTNO=20);


	2 Ergebnistupel




5.)
	SELECT E.EMPNO,E.ENAME,E.JOB,E.SAL FROM EMP E WHERE
	E.SAL>(SELECT MAX(X.AVGS) FROM (SELECT AVG(SAL) AS AVGS
					 FROM EMP GROUP BY DEPTNO) X);


	4 Ergebnistupel




##############  CD-Datenbank  ####################




1.)
	SELECT DISTINCT K.NAME,K.VORNAME FROM KOMPONIST K,STUECK S1,STUECK S2 WHERE
	(S1.OPUS IS NULL) AND (S2.OPUS IS NOT NULL) AND (S1.SNR<>S2.SNR) AND
	(S1.KNR=K.KNR) AND (S2.KNR=K.KNR);


	7 Ergebnistupel



2.)
	SELECT DISTINCT K.NAME,K.VORNAME FROM KOMPONIST K, STUECK S WHERE
	(S.KNR=K.KNR) AND ((S.TITEL LIKE 'Sin%') OR (S.TITEL LIKE 'Sym%'));


	11 Ergebnistupel




3.)
	SELECT DISTINCT K.NAME,K.VORNAME,K.GEBOREN FROM
	(SELECT * FROM KOMPONIST ORDER BY GEBOREN ASC) K WHERE ROWNUM<6;


	5 Ergebnistupel



4.)
	SELECT DISTINCT S.SNR,S.TITEL,"kein Solist","kein Solist"
	FROM STUECK S,AUFNAHME A WHERE
	NOT EXISTS(SELECT * FROM SOLIST SOL WHERE SOL.SNR=S.SNR)
	UNION
	SELECT DISTINCT S.SNR,S.TITEL,SOL.NAME,SOL.INSTRUMENT
	FROM STUECK S,AUFNAHME A,SOLIST SOL WHERE
	(SOL.SNR=S.SNR) AND (A.SNR=S.SNR);


	190 Ergebnistupel (62 mit Solisten , 128 ohne Solisten)