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;
Last modified date: 08/29/2024