SQL cont. #

Comparison operators #

To list all the names of employees whose salary is greater than the salary of all the employees in department 5:

SELECT e1.lname, e1.fname
FROM employee AS e1
WHERE e1.salary > ALL (SELECT e.salary
                       FROM employee AS e
                       WHERE e.dno=5);

• it is suggested to use aliases when using nested queries
• if instead our where clause was E1.SALARY > some (..., it doesn’t need to be greater than all items in bag, just some (1 is enough).

EXISTS #

Often we want to check whether the result of a query is empty.

Retrieve the name of each employee who has a dependent with the same first name as the employee.

SELECT e.fname, e.lname
FROM employee AS e
WHERE EXISTS (SELECT *
              FROM dependent AS d
              WHERE d.essn=e.ssn AND
                    d.dependent_name=e.fname);

• if there are any tuples returned by the inner query, the exists will return true and the outer query was select the current employee
• the inner query can use the outer query’s variables
• we select * in the inner query to get the entire table back

Include the next example in the weekly homework

Retrieve the names of employees who have no dependents.

SELECT e.fname, e.lname
FROM employee AS e
WHERE NOT EXISTS (SELECT *
                  FROM dependent AS d
                  WHERE d.essn=e.ssn);

Explicit sets #

SELECT DISTINCT essn
FROM works_on
WHERE pno IN (1, 2, 3);

• an easy way to explicitly declare a set

Renaming aliases #

Joined tables #

SELECT fname, lname, address
FROM employee JOIN department
    ON dnumber=dno
WHERE dname='Research';

• not all DBMS has support for JOIN, however MySQL does have support.

JOIN on its own is the same as INNER JOIN.

SELECT fname, lname, dname
FROM employee LEFT OUTER JOIN department
    ON ssn=mgrssn;

• INNER JOIN gets rid of the items that don’t match
• LEFT OUTER JOIN puts null values for items that don’t exist in the left table
• RIGHT OUTER JOIN puts null values for items that don’t exist in the right table
• the left and right tables are respective positions: left JOIN right

Aggregation functions #

Find the maximum salary, the minimum salary, and the average salary among all employees.

SELECT MAX(salary), MIN(salary), AVG(salary)
FROM employee

Include the next example in weekly homework

Find the maximum salary, the minimum salary, and the average salary among employees who work for the ‘Research’ department.

SELECT MAX(salary), MIN(salary), AVG(salary)
FROM employee, department
WHERE dno=dnumber AND
      dname='Research';

Retrieve the total number of employees in the company.

SELECT COUNT(*)
FROM employee;

Consider these 2 queries

-- amount of different salaries
SELECT COUNT(DISTINCT salary)
FROM employee;

-- amount of all employees with salary
SELECT COUNT(salary)
FROM employee;

Retrieve the number of employees in the ‘Research’ department

SELECT COUNT(*)
FROM employee, department
WHERE dno=dnumber AND
      dname='Research';

Retrieve the names of all employees who have two or more dependents.

SELECT e.lname, e.fname
FROM employee AS e
WHERE (SELECT COUNT(*)
       FROM dependent AS d
       WHERE e.ssn=d.essn) >= 2;

• when COUNT returns the value, it is a single row/column in a table. This is a special case where you can compare the table directly to the interger 2.
• you cannot use a comparison in a SELECT statement