One company\'s database contents are as the following three tables: DEPT, SALARY

Question

One company's database contents are as the following three tables: DEPT, SALARYRANK, and EMP.

DEPT Dept number Dept Name LOC

SALARYRANK Rank LowEnd High_End

EMP Enumber Ename Job MGR_Num Hire_On Salary Dept_Num

Write the SQL statements for the queries specified in the following problems.

(a) Retrieve a list of names and salaries of all analysts, sorted by salary, the max. first.

(b) Find the total number of salesmen and their average salary.

(c) Find names and salaries of all employees whose manager is Jones.

(d) Find names and dates they were hired of all employees whose salary has rank1.

(e) Find the highest, lowest, and the average salary paid.

(f) Find names and job titles of all employees that are managed by some employee

whose job title is not manager.

Explanation / Answer

a) SELECT Ename,Salary from EMP where Dept_NUM=(SELECT Dept_Number from DEPT where Dept_Name="analyst") order by salary DESC;

b) SELECT AVG(Salary) as AverageSalary, COUNT(Salary) as TotalSalesman from EMP where job="Salesman";

c) SELECT Ename,Salary from EMP where MGR_NUM=(SELECT Enumber from EMP where Ename ="jones");

d) SELECT LowEnd as minSalary,High_End as maxSalary from SALARYRANK where Rank=1
SELECT Ename,Hire_On from EMP where Salary<minSalary && Salary < maxSalary;

e)SELECT MIN(Salary) as MinSalary,MAX(Salary) as MaxSalary,AVG(Salary) from EMP;

f)SELECT Name,Job from EMP where MGR_NUM =(SELECT Enumber from EMP where Job <> "Manager")

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

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