SQL cont. #

Aggregate functions cont. #

Include in weekly assignment.

List each employee(ssn, fname, lname) who has more than 3 daughters and the salary of the employee is less than 60000.

SELECT e.ssn, e.fname, e.lname
FROM employee AS e
WHERE e.salary < 60000 AND
      (SELECT COUNT(*)
       FROM dependent AS dep
       WHERE e.ssn=dep.essn AND
             dep.relationship='daughter') > 3;

GROUP BY clause #

A clause GROUP BY is used to create sub groups. This allows us to do some operations within a specific sub group.

The attributes being grouped by must appear in the select clause:

SELECT x, y
-- from ...
GROUP BY x, y

For each department, retrieve the department number, the number of employees in the department, and their average salary.

SELECT dno, COUNT(*), AVG(salary)
FROM employee
GROUP BY dno

HAVING clause #

Since the WHERE clause comes before the GROUP BY clause, if we want to filter the results of the grouping, we can use the HAVING clause. This allows us to filter the results of the grouping by making sure each sub group satisfies the condition after the HAVING clause.

• it is okay to have a GROUP BY clause without a HAVING
• it is not okay to have a HAVING clause without a GROUP BY

For each department which has more than two employees, retrieve the department number, the number of employees in the department, and their average salary.

SELECT dno, COUNT(*), AVG(salary)
FROM employee
GROUP BY dno
HAVING COUNT(*) > 2;

Summary of SQL queries #

The optional clauses are surrounded in square brackets [].

In class exercise #

Retrieve the department name if the lowest employee salary of the department is greater than 50000. List department name and lowest salary.

SELECT dname, MIN(salary)
FROM department, employee
WHERE dno=dnumber
GROUP BY dname
HAVING MIN(salary) > 50000;

Another solution to a previous problem #

Our previous problem can be solved using our new clauses:

List each employee(ssn, fname, lname) who has more than 3 daughters and the salary of the employee is less than 60000.

SELECT ssn, fname, lname
FROM employee, department
WHERE ssn=essn AND
      relationship='daughter' AND
      salary < 60000
GROUP BY ssn, fname, lname
HAVING COUNT(*) > 3;

Returning to earlier problems we skipped #

Before we skipped the delete statement with the subquery.

DELETE FROM employee
WHERE dno IN (SELECT dnumber
              FROM department
              WHERE dname='Research');

We can also use a subquery in an UPDATE:

UPDATE employee
SET salary=salary*1.1
WHERE dno IN (SELECT dnumber
              FROM department
              WHERE dname='Research');

We can use a subquery in an insert statement:

CREATE TABLE dept_info(
    dept_name VARCHAR(10),
    no_of_emps INTEGER,
    total_sal INTEGER
);

INSERT INTO dept_info (dept_name, no_of_emps, total_sal)
    SELECT dname, COUNT(*), SUM(salary)
    FROM department, employee
    WHERE dnumber=dno
    GROUP BY dname;

• the result of the subquery will be inserted into the dept_info table’s values.