Examples: Select (interactive)

The following are Select (interactive) statement examples:

1. Find all employees who make more than their managers. This example illustrates the use of correlation names.

   select e.ename
   from employee e, dept, employee m
   where e.dept = dept.dno and dept.mgr = m.eno
   and e.salary > m.salary;

2. Select all information for employees that have salaries above the average salary.

   select * from employee
   where salary > (select avg(salary) from employee);

3. Select employee information sorted by department and, within department, by name.

   select e.ename, d.dname from employee e, dept d
   where e.dept = d.dno
   order by dname, ename;

4. Select lab samples analyzed by lab #12 from both production and archive tables.

   select * from samples s
   here s.lab = 12
   
   union
   select * from archived_samples s
   where s.lab = 12

5. Select the current user name.

   select dbmsinfo('username');

6. Display the day of the week that is three days from today.

   select dow(date('today') + date('3 days'));

7. Display employees whose salary is higher than the average for their department (using derived tables):

   select e.ename from employee e,

      (select avg(e1.salary), e1.dno from employee e1 group by e1.dno) e2(avgsal, dno)

      where e.dno = e2.dno and e.salary > e2.salary;

   This query can alternatively be coded as:

   select e.ename from employee e,

      (select avg(e1.salary) as avgsal, e1.dno from employee e1 group by e1.dno) e2

      where e.dno = e2.dno and e.salary > e2.salary;