8. SQL Statements : SELECT (interactive) : SELECT (interactive) Examples
 
Share this page                  
SELECT (interactive) 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
WHERE 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.avgsal;
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.avgsal;