SQL Queries to Develop SQL statements. Develop SQL statements using the EMP and
ID: 3920729 • Letter: S
Question
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
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
Explanation / Answer
please find the below queries
==========================================
Please find below all the SQL statements.
1.List all department names and dept numbers greater than 20
--> select dname, deptno from dept where deptno>20 ;
2.List the salesman that have a monthly salary greater than $1300
-->select ename,job from emp where sal>1300 and job='SALESMAN' ;
3.List all employee names whose names begin with W
-->SELECT ENAME FROM EMP WHERE ENAME LIKE 'W%';
4.Find all employees who are not salesman
-->SELECT ENAME,JOB FROM EMP WHERE JOB<>'SALESMAN';
5.List all employees and their jobs who have no commission
-->SELECT ENAME,JOB FROM EMP WHERE COMM IS NULL;
6.What is the average salary for each job? (Hint: Use Group by)
-->SELECT JOB,AVG(SAL) FROM EMP GROUP BY JOB;
7.What is the average salary for each job by location? (Hint: Sort by location, then by job)
-->SELECT LOC,JOB,AVG(SAL) FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO GROUP BY LOC,JOB;
8.Find all the employees who have a salary of at least $2500 and are not managers
-->SELECT ENAME,SAL FROM EMP WHERE SAL>=2500 AND JOB<>'MANAGER';
9.In what City does Allen work? (Hint: its just one City)
-->SELECT ENAME,LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND ENAME='ALLEN';
10.What are the names of the employees in Chicago that have a salary greater than $1000 but less than $1575? (Hint: Use Between)
-->SELECT ENAME,SAL FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND LOC='CHICAGO' AND SAL BETWEEN 1000 AND 1575;
11.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?)
-->SELECT DEPT.DEPTNO,DNAME,LOC,ENAME,JOB FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND DEPT.DEPTNO IN (30,40);
12.How many employees are in each job category by department? (Hint: Use group by and have multiple fields in your group by clause).
-->SELECT DEPTNO,JOB,COUNT(*) FROM EMP GROUP BY DEPTNO,JOB;
13.List the employees whose annual salary ranges from 22000 to 45000 (Hint: 12*SAL gives you an annual salary)
-->SELECT ENAME,12*SAL FROM EMP WHERE 12*SAL BETWEEN 22000 AND 45000;
14.List the employee names that start with an 's' and contain only five characters
-->SELECT ENAME FROM EMP WHERE ENAME LIKE 'S%' AND LENGTH(ENAME)=5;
15.List the employee names that have only 4 characters and the third character must be an 'R'
-->SELECT ENAME FROM EMP WHERE ENAME LIKE '__R%' AND LENGTH(ENAME)=4;
16.List the details of the employees who job is the same as ALLEN
-->SELECT * FROM EMP WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME='ALLEN');
17.List the jobs in Department Number 10 that are not found in Department Number 20
->SELECT JOB,DEPTNO FROM EMP WHERE DEPTNO=10 AND JOB NOT IN (SELECT JOB FROM EMP WHERE DEPTNO=20);
18.What is the total annual salaries of all managers combined?
-->SELECT SUM(12*SAL) FROM EMP WHERE JOB='MANAGER';
19.What is the average monthly salary of a clerk?
-->SELECT AVG(SAL) FROM EMP WHERE JOB='CLERK';
20.List the grand total of all annual salaries by job
-->SELECT JOB,SUM(12*SAL) FROM EMP GROUP BY JOB;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.