8.6

Consider the relational schema shown in Figure 8.8.

  1. Give a schema definition in SQL corresponding to the relational schema but using references to express foreign-key relationships.

  2. Write each of the following queries on the schema, using SQL.

  3. Find the company with the most employees.

  1. Find the company with the smallest payroll.

  2. Find those companies whose employees earn a higher salary, on average, than the average salary at First Bank Corporation.

  1. Give a schema definition in SQL corresponding to the relational schema but using references to express foreign-key relationships.

The schema definition is given below.

CREATE TYPE Employee ( 
    person_name VARCHAR(30), 
    street VARCHAR(15), 
    city VARCHAR(15)
);

CREATE TYPE Company ( 
    company_name VARCHAR(15), 
    city VARCHAR(15)
);

CREATE TABLE employee OF Employee;
CREATE TABLE company  OF Company;

CREATE TYPE Works ( 
    person REF(Employee) SCOPE employee, 
    comp REF(Company) SCOPE company, 
    salary INT
);

CREATE TABLE works OF Works;
CREATE TYPE Manages ( 
    person REF(Employee) SCOPE employee, 
    manager REF(Employee) SCOPE employee
);
CREATE TABLE manages OF Manages;

  1. Write each of the following queries on the schema, using SQL.

  2. Find the company with the most employees.

SELECT comp->name
FROM works
GROUP BY comp
HAVING COUNT(person) >= ALL(
    SELECT COUNT(person)
    FROM works
    GROUP BY comp
);
  1. Find the company with the smallest payroll.
SELECT comp->name
FROM works
GROUP BY comp
HAVING SUM(salary) <= ALL(
    SELECT SUM(salary)
    FROM works
    GROUP BY comp
);
  1. Find those companies whose employees earn a higher salary, on average, than the average salary at First Bank Corporation.
SELECT comp->name
FROM works
GROUP BY comp
HAVING AVG(salary) > (
    SELECT AVG(salary)
    FROM works
    WHERE comp->company_name = 'First Bank Corporation'
);