Nested Queries CSED421: Database Systems Labs.

Presentation on theme: "Nested Queries CSED421: Database Systems Labs."— Presentation transcript:

1 Nested Queries CSED421: Database Systems Labs

2 Contents Set Operations Nested Subqueries Example Practice IN / NOT IN
ANY(SOME) / ALL EXISTS / NOT EXISTS UNIQUE / NOT UNIQUE Example Practice

3 Set Operations Find all customers who have a loan, an account, or both: (SELECT customer-name FROM depositor) UNION (SELECT customer-name FROM borrower) Find all customers who have both a loan and an account. (SELECT customer-name FROM depositor) INTERSECT (SELECT customer-name FROM borrower) Find all customers who have an account but no loan. (SELECT customer-name FROM depositor) MINUS (SELECT customer-name FROM borrower) Join은 테이블 setoperations은 쿼리의 결과

4 IN / NOT IN Find all customers who have both an account and a loan at the bank. SELECT DISTINCT customer-name FROM borrower WHERE customer-name IN (SELECT customer-name FROM depositor) Find all customers who have a loan at the bank but do not have an account at the bank SELECT DISTINCT customer-name FROM borrower WHERE customer-name NOT IN (SELECT customer-name FROM depositor)

5 ANY / ALL Comparison operator
>ANY() : Greater than the minimum value in ANY() >ALL() : Greater than the maximum value in ALL() =ANY() : Equivalent to IN operator <>ALL(): Equivalent to NOT IN operator Find the names of all branches that have greater assets than all branches located in Brooklyn. SELECT branch-name FROM branch WHERE assets > ALL (SELECT assets FROM branch WHERE branch-city = ‘Brooklyn’)

6 EXISTS / NOT EXISTS Find all customers who have both an account and a loan at the bank. SELECT customer-name FROM borrower B WHERE EXISTS ( SELECT customer-name FROM depositor D WHERE D.customer-name=B.customer-name)

7 UNIQUE / NOT UNIQUE Find all customers who have at most one account at the Granville branch. SELECT T.customer-name FROM depositor T WHERE UNIQUE ( SELECT R.customer-name FROM account A, depositor R WHERE T.customer-name = R.customer-name and R.account-number = A.account-number and A.branch-name = ‘Granville’)

8 Practice EMP table & DEPT table emp.sql EMPNO NUMBER(4) 직번 ENAME
VARCHAR2(10) 이름 JOB VARCHAR2(9) 직위 MGR 상사 직번 HIREDATE DATE 고용일 SAL NUMBER(7, 2) 급여 COMM 추가 급여 DEPTNO NUMBER(2) 부서번호 jobs 테이블 1. employees 테이블에서 각 job_id별로 몇 명의 사람이 있는지 검색하시오. DEPTNO NUMBER(2) 부서번호 DNAME VARCHAR2(14) 부서명 LOC VARCHAR2(13) 부서위치

9

10 Practice 1. ‘BLAKE’ 와 같은 부서에서 일하는 모든 사원의 이름과 고용일을 출력하시오.
2. 평균 급여 이상을 받는 사람들의 직번과 이름을 출력하시오. 급여의 내림차순으로 정렬하시오. @lab5_delete.sql @hr_main.sql

11 Practice 3. 부서 위치가 ‘DALLAS’인 부서에서 일하는 직원의 이름, 부서, 직위를 출력하시오.
4. 매니저가 King인 사원의 이름과 급여를 출력하시오.

12 Practice 5. 모든 SALESMAN보다 많은 급여를 받는 사원의 이름과 급여를 출력하시오.

13 Practice 6. 평균 급여보다 많은 급여를 받은 사원들의 부서별 현황을 출력하시오.