WARD 직원보다comm이 적은 직원 이름과 comm검색

SELECT ename, comm FROM emp 
WHERE comm < (SELECT comm FROM emp WHERE ename = 'WARD');

평균 급여보다 적은 급여를 받는 직원 이름과 급여

검색를 급여순으로 검색

SELECT ename, sal FROM emp 
WHERE sal < (SELECT avg(sal) FROM emp) ORDER BY sal;

-- 9:30
SELECT ename, comm FROM emp 
WHERE comm < (SELECT comm FROM emp WHERE ename = 'WARD');
    
-- 9:33
SELECT ename, sal FROM emp 
WHERE sal < (SELECT avg(sal) FROM emp) ORDER BY sal;

-- 9:43
SELECT name FROM student;

Anthony Hopkins 학생과1전공(deptno1)이 동일한학생 들의이름출력

SELECT name FROM student s 
WHERE s.deptno1 LIKE (
	SELECT deptno1 FROM student 
	WHERE name = 'Anthony Hopkins'
);

예1-2: Anthony Hopkins 학생과1전공(deptno1)이 동일한학생 들의이름과1전공이름출력

SELECT name, dname FROM student s 
join department d on s.deptno1 = d.deptno
WHERE d.deptno LIKE (
	SELECT deptno1 FROM student 
	WHERE name = 'Anthony Hopkins'
);

예2-1: Meg Ryan교수보다 나중에 입사한 교수이름, 입사일을 입사한 순서대로 검색

SELECT name, hiredate FROM professor
WHERE hiredate > (
	SELECT hiredate FROM professor 
	WHERE name = 'Meg Ryan') 
ORDER BY name;

예2-2: Meg Ryan교수보다 나중에입사한교수이름, 입사일, 학과명을입사한순서대로검색

SELECT name, hiredate, dname FROM professor p
join department d on p.deptno = d.deptno
WHERE hiredate > (
	SELECT hiredate FROM professor 
	WHERE name = 'Meg Ryan') 
ORDER BY hiredate;