Oracle SQL classical query expert Article 4

Oracle SQL classical query expert Article 4

Oracle SQL classical query expert Article 4

This article is the fourth article on typical query of OracleSQL. It is just the author's own opinion. If you have any questions, I hope you can provide suggestions or methods. At the same time, you are welcome to add that, if you have a classic query method, you can share it with us, grow together, and make progress together.

This computer uses Oracle11.2.0 and scott user login. The built-in table is used.

Table Structure:

Describe employees;

 

Describe lifecycle;

Describe locations;

 

Select * from employees; (due to space restrictions, all rows and columns in this table are not truncated, with a total of 107 rows and 11 columns)

 

Select * from orders; (due to space restrictions, all columns in this table are truncated, but not all rows are truncated. A total of 27 rows are truncated)

 

Select * from locations;

 

(Due to space restrictions, all rows and columns in this table are not truncated. There are 23 rows and 6 columns in total)

---- Hr user ----

1. The average, maximum, minimum salary, and number of people in each department are listed in ascending order by Department number.

2. The number of employees with salaries greater than 5000 in each department.

3. The average salary and number of people in each department are listed in ascending order by department name.

4. List the statistics of employees with the same salary in each department, and their department numbers, salaries, and numbers.

5. List the departments with more than 1000 employees in the same department. The Department name and region name are displayed.

6. Employees whose salaries are higher than the average salaries of the entire company list their names and salaries (in descending order ).

7. The salaries of employees are between the average salaries of Department 50 and department 80.

8. Names of employees whose salaries are higher than 5000 on average in their respective departments.

9. List the employees with the highest salaries in each department: name, Department number, and salary.

10. What is the average salary of the highest department.

 

-- 1. The average, maximum, minimum wage, and number of people in each department are listed in ascending order by Department number.

Select department_id, avg (salary), max (salary), min (salary), count (*) from employees group by department_id order

By department_id asc;

-- 2. Number of employees with a salary greater than 5000 in each department.

Select department_id, count (*) from employees where salary> 5000 group by department_id;

-- 3. The average salary and number of people in each department are listed in ascending order by department name.

Select d. department_name, avg (e. salary), count (*) from employees e, orders ments d where e. department_id = d. department_id group by d. department_name order by d. department_name;

-- 4. List the statistics of employees with the same salary in each department, and their department numbers, salaries, and numbers.

Select e1.department _ id, e1.salary, count (*) from employees e1, employees e2 where

E1.department _ id = e2.department _ id and e1.salary = e2.salary and e1.employee _ id <> e2.employee _ id group

E1.department _ id, e1.salary;

(Due to space restrictions, all rows and columns in this table are not truncated. There are 23 rows and 3 columns in total)

 

-- 5. List the departments with a salary higher than 1000 of the employees who have more than two employees in the same department. The Department name and region name are displayed.

(Subquery method)

Select * from (select d. department_name, l. city, count (*) cnumber from employees e, departments d, locations l

Where e. department_id = d. department_id and d. location_id = l. location_id and e. salary> 100

Group by d. department_name, l. city

) Where cnumber> 2;

(Having Keyword Method)

Select d. department_name, l. city, count (*) from employees e, departments d, locations l where

E. department_id = d. department_id and d. location_id = l. location_id and e. salary> 100

Group by d. department_name, l. city having count (*)> 2;

-- 6. Employees whose salaries are higher than the average salaries of the entire company list the names and salaries of employees (in descending order ).

Select e1.first _ name | ''| last_name as name, e1.salary from employees e1 where e1.salary> (select avg (salary) from

Employees );

(Due to space restrictions, all rows and columns in this table are not truncated, with a total of 51 rows and two columns)

 

-- 7. The salaries of employees are between the average salaries of departments 50 and 80.

Select e1.first _ name first_name, e1.last _ name last_name, e1.salary from employees e1 where e1.salary

(Select avg (salary) from employees where department_id = 50) and (select avg (salary) from employees where

Department_id = 80); (due to space restrictions, the rows and columns of this table are not truncated, a total of 43 rows, 3 columns)

 

-- 8. Names of employees with an average salary higher than 5000 in their respective departments.

Select e. first_name, e. last_name from employees e where department_id in (select

Department_id from employees group by department_id having avg (salary)> 5000); (due to space restrictions, the rows and columns of this table

All are not truncated, 54 rows in total, 2 columns)

 

-- 9. List the employees with the highest salaries in each department: name, Department number, and salary.

(Subquery method)

Select e1.first _ name first_name, e1.last _ namelast_name, e1.department _ id, e1.salary from employees e1, (select

Max (salary) maxsalary, department_id from employees e group by department_id) e2 where e1.salary = e2.max

Salary and e1.department _ id = e2.department _ id;

(In Keyword Method)

Select e1.first _ name first_name, e1.last _ namelast_name, e1.department _ id, e1.salary from employees e1 where

(E1.department _ id, salary) in (select department_id, max (salary) from employees e group by department_id );

 

-- 10. What is the average salary of the highest department.

(Subquery method)

Select e. sal from (select avg (salary) sal, department_id from employees group by department_id order by sal desc)

E where rownum = 1;

(Aggregate function mode)

Select max (e. sal) from (select avg (salary) sal from employees group by department_id) e;

 

The author's level is limited, and it is inevitable that there will be errors. I hope readers will criticize and correct me.