SQL Queries to Develop SQL statements.

Develop SQL statements using the EMP and DEPT tables explained in the lecture notes and available in Oracle APEX. Use Oracle APEX to try out and test each SQL statement. Note that SAL is a month salary. Send in all your SQL Statements in one text file for grading (don't send in the results of your query).

List all department names and dept numbers greater than 20

List the salesman that have a monthly salary greater than $1300

List all employee names whose names begin with W

Find all employees who are not salesman

List all employees and their jobs who have no commission

What is the average salary for each job? (Hint: Use Group by)

What is the average salary for each job by location? (Hint: Sort by location, then by job)

Find all the employees who have a salary of at least $2500 and are not managers

In what City does Allen work? (Hint: its just one City)

What are the names of the employees in Chicago that have a salary greater than $1000 but less than $1575? (Hint: Use Between)

List all department information as well as the employee name and job for all employees living in Chicago or Boston. Do not use the dept.loc field in your WHERE clause. (Hint: What other field in the dept table could you use to get this information?)

How many employees are in each job category by department? (Hint: Use group by and have multiple fields in your group by clause).

List the employees whose annual salary ranges from 22000 to 45000 (Hint: 12*SAL gives you an annual salary)

List the employee names that start with an 's' and contain only five characters

List the employee names that have only 4 characters and the third character must be an 'R'

List the details of the employees who job is the same as ALLEN

List the jobs in Department Number 10 that are not found in Department Number 20

What is the total annual salaries of all managers combined?

What is the average monthly salary of a clerk?

List the grand total of all annual salaries by job

Data

1. The EMP table

The table EMP is used to store information about employees.

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7839	KING	PRESIDENT	-	11/17/1981	5000	-	10
7698	BLAKE	MANAGER	7839	05/01/1981	2850	-	30
7782	CLARK	MANAGER	7839	06/09/1981	2450	-	10
7566	JONES	MANAGER	7839	04/02/1981	2975	-	20
7788	SCOTT	ANALYST	7566	12/09/1982	3000	-	20
7902	FORD	ANALYST	7566	12/03/1981	3000	-	20
7369	SMITH	CLERK	7902	12/17/1980	800	-	20
7499	ALLEN	SALESMAN	7698	02/20/1981	1600	300	30
7521	WARD	SALESMAN	7698	02/22/1981	1250	500	30
7654	MARTIN	SALESMAN	7698	09/28/1981	1250	1400	30
7844	TURNER	SALESMAN	7698	09/08/1981	1500	0	30
7876	ADAMS	CLERK	7788	01/12/1983	1100	-	20
7900	JAMES	CLERK	7698	12/03/1981	950	-	30
7934	MILLER	CLERK	7782	01/23/1982	1300	-	10

2. The DEPT Table

The DEPT table contains information about departments that each employee works for.

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

3. The SALGRADE Table

Another table commonly used is SALGRADE which contains the minimum and maximum range for various salary grades.

GRADE	LOSAL	HISAL
1	700	1200
2	1201	1400
3	2001	3000
4	3001	9999