I am using Oracle SQL Developer and only have assignments work when I use identi

Question

I am using Oracle SQL Developer and only have assignments work when I use identifiers such as "emp" or "sal". I need to complete the following assignment, but all the guides I am finding use "employee" or "salary" instead which gives me errors when I try to run the script. Do I need to download a database in order to get this to work or can I accomplish this using the identifiers I have used in the past? If so, how do I go about doing that?

Lesson 5

Display the highest, lowest, sum, and average salary of all employees. Label the columns Maximum, Minimum, Sum, and Average, respectively. Round your results to the nearest whole number.

Modify the previous SQL statement to display the minimum, maximum, sum and average salary for each job type.

Write a query to display the number of people with the same job. Example:
Analyst 2
Clerk 4

Determine the number of managers without listing them. Label the column Number of Managers.

Write a query that will display the difference between the highest and lowest salaries. Label the column DIFFERENCE.

Display the manager number and the salary of the lowest paid employee for that manager. Exclude anyone whose manager is not known. Exclude any group where the minimum salary is less than $1000. Sort the output in descending order of salary.

Write a query to display the department name, location name, number of employees, and the average salary for all employees in that department. Label the columns DName, Loc, Number of People, and Salary, respectively. Round the average salary to two decimal places.

Create a query that will display the total number of employees and of that total the number who were hired in 1980, 1981, 1982, and 1983. Use appropriate column heading.

Create a matrix query to display the job, the salary for that job based on department number, and the total salary for that job for all departments, giving each column an appropriate heading.

Explanation / Answer

If you have any doubts, please give me comment...

It must match with respective table name or column name, whatever you use name that it doesn't matter. Once you design schema, you have to stick to that only...

SELECT MAX(SAL) Maxmimum, MIN(SAL) Minimum, SUM(SAL) Sum, ROUND(AVG(SAL), 0) Average

FROM EMP;

SELECT JOB, MAX(SAL) Maxmimum, MIN(SAL) Minimum, SUM(SAL) Sum, ROUND(AVG(SAL), 0) Average

FROM EMP

GROUP BY JOB;

SELECT JOB, COUNT(*)

FROM EMP

GROUP BY JOB;

SELECT COUNT(MGR) AS 'Number of Managers'

FROM EMP;

SELECT (MAX(SAL) - MIN(SAL)) DIFFERENCE

FROM EMP;

SELECT MGR, MIN(SAL) AS MinSalary

FROM EMP E, EMP M

WHERE E.EMPNO = M.MGR AND MGR IS NOT NULL

GROUP BY MGR

HAVING MIN(SAL)>1000

ORDER BY MinSalary;

SELECT DNAME, LOC, COUNT(*) AS 'Number of People', ROUND(AVG(SAL),2) Salary

FROM DEPT D, EMP E

WHERE D.DEPTNO = E.DEPTNO;

SELECT DNAME, LOC, COUNT(EMPNO) AS 'Number of Employees', Hired

FROM DEPT D, EMP E, (

SELECT DEPTNO, COUNT(*) Hired

FROM EMP

WHERE YEAR(HIREDATE) IN (1980, 1981, 1982, 1983) GROUP BY DEPTNO

) AS T

WHERE D.DEPTNO = E.DEPTNO AND D.DEPTNO = T.DEPTNO

GROUP BY DNAME, LOC;

SELECT JOB, SUM(SAL) 'Total Salary'

FROM EMP

GROUP BY JOB;

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.