DB: Chapter 7(Complex Queries)

#Comparisons Involving NULL & Three-Valued Logic

          > NULL : unknown value, not applicable attribute

          > Three-valued Logic

          > IS [NOT] NULL

          ex) Retrieve the names of all employees who do not have supervisors

                    SELECT Fname, Lname

                    FROM EMPLOYEE

                    WHERE Super_ssn IS NULL

          > IN 연산

                    

위 두 query 는 같다

          > 중첩 질의 (Nested Query)

          > PROJECT 테이블에서 Dnum 이 5 인요소들의 Pnumber 를 골라 해당하는 Pname 을 출력             

 

         

SELECT Pname
FROM PROJECT
WHERE Plocation='Huston' OR Dnum = 4;

SELECT Pname
FROM PROJECT
WHERE Pnumber IN(3,20) OR Pnumber IN (10,30);

SELECT Pname
FROM PROJECT 
WHERE Pnumber IN
		(SELECT Pnumber FROM PROJECT
        WHERE Plocation='Huston')
     OR
     Pnumber IN
     	(SELECT Pnumber FROM PROJECT
        WHERE Dnum=4);

위 SQL 질의의 결과 는 모두 같다

 

> QUERY) Make a list of all project numbers for projects that involve an employee whose last name is 'Smith', either as a worker or as a manager of the department

SELECT DISTINCT Pnumber
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum = Dnumber AND Mgr_ssn = SSN AND Lname='Smith'
UNION
SELECT DISTINCT Pno
FROM WORKS_ON, EMPLOYEE
WHERE Essn=Ssn AND Lname='Smith';



SELECT DISTINCT Pnumber
FROM PROJECT
WHERE Pnumber IN
	(SELECT DISTINCT Pnumber
    FROM PROJECT, DEPARTMENT, EMPLOYEE
    WHERE Dnum=Dnumber AND Mgr_ssn=Ssn AND Lname='Smith')
    OR Pnumber IN
    (SELECT DISTINCT Pno
    FROM WORKS_ON,EMPLOYEE
    WHERE Essn=Ssn AND Lname='Smith');

 

>QUERY) Retrieve the name of each employee who has a dependent with the same first name and same sex as the employee.

SELECT E.Fname, E.Lname
FROM EMPLOYEE AS E 
WHERE E.Ssn IN (
	SELECT Essn
    FROM DEPENDENT
    WHERE E.Fname = Dependent_name AND E.Sex=Sex);

          

#EXISTS and UNIQUE Functions

          > Exists는 Correlated nested query 가 비어있는지 아닌지 확인

          > 즉, true or false 반환

          > UNIQUE는 만약 튜플이 유일하면 true 아니면 false

>QUERY) Retrieve the names of employees who have no dependents

SELECT Fname, Lname
FROM EMPLOYEES
WHERE NOT EXISTS
	(SELECT *
    FROM DEPENDENT
    WHERE Ssn=Essn);

SELECT Fname,Lname
FROM EMPLOYEE
WHERE UNIQUE 
	( SELECT Sex
      FROM DEPENDENT
      WHERE Essn=EMPLOYEE.Ssn);

> QUERY) List the names of managers who have at least one dependent

SELECT Fname, Lname
FROM EMPLOYEE
WHERE EXISTS (SELECT *
     		 FROM DEPENDENT
    		 WHERE Ssn=Essn)
     AND
     EXISTS(SELECT *
     		FROM DEPARTMENT
            WHERE Ssn=Mgr_ssn);

 

#Explicit Sets and Renaming Attributes

SELECT E.Lname AS Emp_Name, S.Lname AS Spvsr_Name
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.Super_ssn=S.Ssn;

 

#Joined Table

SELECT Fname, Lname, Address
FROM (EMPLOYEE JOIN DEPARMENT ON Dno=Dnumber)
WHERE Dname='Research';

          > JOIN 의 결과물

                    >> Dno 와 Dnumber 이 같은 튜플끼리 묶는것

          >JOIN 의 종류: NATURAL JOIN, OUTER JOIN

                    NATURAL JOIN 에서

                    > join condition 이 없다

SELECT Fname,Lname, Address
FROM (EMPLOYEE NATURAL JOIN 
		(DEPARTMENT AS DEPT(Dname, Dno, Mssn, Msdate)))
WHERE Dname='Research';

위 query 의 결과물

         > INNER JOIN : 두 집합에 모두 있는 열만 남음

         > LEFT OUTER JOIN: A 와 B 테이블이 있다면 A의 모든열 + B에 있는 공통 부분

         > RIGHT OUTER JOIN: B의 모든열+ A에 있는 공통 부분

         > FULL OUTER JOIN: A와 B의 합집합

 

#MULTIWAY JOIN

SELECT Pnumber, Dnum, Lname, Address, Bdate
FROM ((PROJECT JOIN DEPARTMENT ON Dnum=Dnumber JOIN EMPLOYEE ON Mgr_ssn=Ssn)
WHERE Plocation='Stafford';

 

#Aggregate Function

총 5가지 aggregate functions.

-> COUNT, SUM, MAX, MIN, AVG

>Query) Find the sume of the salaries of all employees, the maximum salary, the minimum salary and the avg salary

SELECT SUM(Salary), MAX(Salary), MIN(Salary), AVG(Salary)
FROM EMPLOYEE

> Query) Find the sum of the salaries of all employees of the 'Research' department, as well as the max salary , the min salary, and the avg salary in this department.

SELECT SUM(Salary), MAX(Salary), MIN(Salary), AVG(Salary)
FROM(EMPLOYEE JOIN DEPARMENT ON Dno=Dnumber)
WHERE Dname='Research';

> Query) Retrieve the total number of employees in the compary and the number of employees in the 'Research' department

SELECT COUNT(*) FROM EMPLOYEE;



SELECT COUNT(*)
FROM EMPLOYEE, DEPARMENT
WHERE Dno=Dnumber AND Dname='Research';

 

> Query) Count the number of distinct salary values in the db

SELECT COUNT(DISTINCT Salary)
FROM EMPLOYEE;

> Query) Retrieve the names of all employees who have two or more dependents

SELECT Fname, Lname
FROM EMPLOYEE
WHERE ( SELECT COUNT(*) FROM DEPENDENT WHERE Ssn=Essn) >=2;

 

#Grouping: GROUP BY and HAVING

> Query) For each department, retrieve the deparment number, the number of employees in the department, and their avg salary

SELECT Dno, COUNT(*), AVG(Salary)
FROM EMPLOYEE
GROUP BY Dno;

> Query) For each project, retrieve the project number, the project name and the number of employees who work on that project

SELECT Pnumber, Pname, COUNT(*)
FROM PROJECT, WORKS_ON
WHERE Pnumber = Pno
GROUP BY Pnumber, Pname;

 

>Query) For each projcet, retrieve the project number, the project name, and the number of employees from department 5 who work on the project

SELECT Pnumber, Pname, COUNT(*)
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE Pnumber=Pno AND Ssn=Essn AND Dno = 5
GROUP BY Pnumber, Pname;

>Query) Count the total number of employees whose salaries exceed $40,000 in each department but only for department wher more than 3 employees work

SELECT Dno, COUNT(*)
FROM EMPLOYEE
WHERE Salary>40000
GROUP BY Dno
HAVING COUNT(*)>3;

>Query) For each department that has more than 3 employees, retrieve the department number and the number of its employees who are making more than $40,000

SELECT Dno, COUNT(*)
FROM EMPLOYEE
WHERE Salary>40000 AND Dno IN (
                    SELECT Dno
                    FROM EMPLOYEE
                    GROUP BY Dno
                    HAVING COUNT(*) > 3)
GROUP BY Dno;

 

#WITH and CASE

>Query) For each department that has more than 3 employees, retrieve the department number and the number of its employees who are making more than $40,000

WITH BIGDEPTS(Dno) AS(
	SELECT Dno
    FROM EMPLOYEE
    GROUP BY Dno
    HAVING COUNT(*) > 3)
SELECT Dno, COUNT(*)
FROM EMPLOYEE
WHERE Salary>40000 AND Dno IN BIGDEPTS
GROUP BY Dno;

CASE)