Databasteknik: Solutions to Exercise 4

1. List all employees, that is, all the rows in the table employee.

   select * from employee;
   

2. List all the department names, that is, the column name for all the rows in the table dept.

   select name from dept;
   

3. Which parts are not available in our store, that is, which parts have a qoh ("quantity on hand") of 0?

   select * from parts where qoh = 0;
   

4. Which employees have a salary between 9000 and 10000?

   select * from employee
   where salary between 9000 and 10000;
   

   or

   select * from employee
   where salary >= 9000 and salary <= 10000;
   

5. How old was each employee when he or she started working here?

   select number, name, startdate - birthdate as age
   from employee;
   

6. Which employees have a last name that ends with "son"?

   select * from employee where name like '%son, %';
   

7. Which items have been supplied by a supplier named "Playskool"?

   select number, name
   from item
   where item.supplier = (select number from supplier where name = 'Playskool');
   

   or

   select item.number, item.name
   from item, supplier
   where item.supplier = supplier.number
   and supplier.name = 'Playskool';
   

8. Which names, and what colour, have the parts that are heavier than a black tape drive? Write this query with a sub-query in the where clause. The query mustn't contain the weight as a constant.

   select pname, color
   from parts
   where weight > (select weight
                   from parts
   		where pname = 'tape drive'
                   and color = 'black');
   

9. Write the same query, but without a sub-query. The query still mustn't contain the weight as a constant.

   select heavy.pname, heavy.color
   from parts heavy, parts tapedrive
   where heavy.weight > tapedrive.weight
   and tapedrive.pname = 'tape drive'
   and tapedrive.color = 'black';
   

10. What is the average weight of black parts?

    select avg(weight)
    from parts
    where color = 'black';