OracleSQL classic query trainer Article 3

OracleSQL classic query trainer Article 3

Oracle SQL classic query trainer Article 3

This article is the third article about Oracle SQL classic query. It is just the author's 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.

Oracle 11.2.0 is used on this computer, and scott is used for logon. 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. Make SELECTTO_CHAR (SALARY, 'l99, 100') from hr. employees where rownum <5. The currency units of the output result are $ and $.

2. List the names of the first five employees. The salaries and post-salary increases (an increase of 8%) are rounded off in Yuan.

3. Find out who is the top leader and display the name in uppercase.

4. Find out the name of the direct supervisor whose First_Name is David and whose Last_Name is Austin.

5. First_Name is Alexander, and Last_Name is Hunold. (Who reports to David ).

6. which employees have higher salaries than their direct superiors, and list their names and salaries, and their names and salaries.

7. which employees are in the same department as Chen (LAST_NAME.

8. Which employees do the same job as De Haan (LAST_NAME.

9. which employees are not in the same department as Hall (LAST_NAME.

10. Which employees do different jobs with William (FIRST_NAME) and Smith (LAST_NAME.

11. display the information of employees with commissions: name, commission, Department name, and Region name.

12. display the positions in the Executive department.

13. What is the difference between the highest wage and the minimum wage in the company.

14. Number of people whose commission is greater than 0.

15. display the maximum wage, minimum wage, total wage, and average wage of the entire company to the whole digit.

16. How many leaders are there in the company.

17. List employees who have joined the same Department on a late date but have higher salaries than other colleagues: name, salary, and employment date.

-- 1. Let SELECTTO_CHAR (SALARY, 'l99, 100') from hr. employees where rownum <5. The currency units of the output result are $ and $.

SELECT TO_CHAR (SALARY, 'l99, 999.99 ') from employees where rownum <5;

 

SELECT TO_CHAR (SALARY, '$99,999.99') from employees where rownum <5;

 

-- Note: For the '$99,999.99' format OPERATOR:

-- L: indicates that the local currency symbol is forcibly displayed.

-- $: Displays the dollar sign.

-- 9: indicates a number.

-- 0: Force 0 display

--.: Indicates a decimal point.

--, Indicates a thousands separator.

-- 2. List the names of the first five employees, whose salaries and post-salary increases (an increase of 8%) are rounded off in Yuan.

(1) select first_name, salary, round (salary * 1.08) sal from employees where rownum <6;

(2) select * from (select first_name, salary, round (salary * 1.08) sal from employees order by salarydesc) e whererownum <6;

(Note: The answer may be different from the answer provided by other blogs due to the Oracle version or the tool I used. Readers can select the answer based on their actual situation, I chose the second one)

 

-- 3. Find out who is the top leader and display the name in uppercase.

Select upper (first_name | ''| last_name) namefrom employees where manager_idis null;

 

-- 4. Find the name of the direct supervisor whose First_Name is David and whose Last_Name is Austin.

Select first_name, last_name from employeeswhere employee_id = (select manager_id from employeeswhere first_name = 'David' and last_name = 'austin ');

 

-- 5. The First_Name is Alexander, and the Last_Name is Hunold. (Who reports to David ).

Select upper (first_name | ''| last_name) name from employees where manager_idin (select employee_id from employeeswhere first_name = 'alexander 'and last_name = 'hunold ');

 

-- 6. which employees have higher salaries than their direct superiors, and list their names and salaries, and their names and salaries.

Select e1.first _ name, e1.salary, e2.first _ name, e2.salary from employees e1, employees e2 where e1.manager _ id = e2.employee _ id and e1.salary> e2.salary;

 

-- 7. which employees are in the same department as Chen (LAST_NAME.

Select * from employeeswhere department_id = (select department_id from employeeswhere last_name = 'chen'); (due to space restrictions, incomplete, 6 rows in total, 11 columns)

 

-- 8. Which employees do the same job as De Haan (LAST_NAME.

Select * from employeeswhere job_id = (select job_id from employeeswhere last_name = 'de haanc') and last_name <> 'desta'; (due to space restrictions, incomplete, 1 row in total, 11 columns)

 

-- 9. which employees are not in the same department as Hall (LAST_NAME.

Select * from employeeswhere department_id <> (select department_id from employeeswhere last_name = 'Hall '); (due to space restrictions, incomplete, 72 rows in total, 11 columns)

 

-- 10. Which employees do different jobs with William (FIRST_NAME) and Smith (LAST_NAME.

Select * from employeeswhere job_id <> (select job_id from employeeswhere first_name = 'william' and last_name = 'Smith '); (due to space restrictions, incomplete, a total of 77 rows and 11 columns)

 

-- 11. display the information of employees with commissions: name, commission, Department name, and Region name.

Select e. first_name, e. last_name, e. commission_pct, d. department_name, l. city from employees e, departments d, locations l where e. department_id = d. department_id and d. location_id = l. location_id

And e. commission_pct isnotnull; (due to space restrictions, incomplete, 34 rows in total, 5 columns)

 

-- 12. display the positions in the Executive department.

Select job_id from employees e, orders ments d where e. department_id = d. department_id and d. department_name = 'executive ';

 

-- 13. What is the difference between the highest wage and the minimum wage in the company.

Select max (salary)-min (salary) from employees;

 

-- 14. Number of people whose commission is greater than 0.

Select count (*) from employeeswhere commission_pct> 0;

 

-- 15. The maximum wage, minimum wage, total wage, and average wage of the entire company are kept to the whole digit.

Select max (nvl (salary, 0) as highestsal, min (nvl (salary, 0) as lowestsal, sum (nvl (salary, 0) as sumsal, round (avg (nvl (salary, 0) as avgsal from employees;

-- 16. How many leaders are there in the company.

Select count (distinct (manager_id) allLeaders from employees where manager_id isnotnull;

 

-- 17. List employees who have joined the same Department on a late date but whose salaries are higher than those of other colleagues: name, salary, and employment date.

Select distinct e1.first _ name | ''| e1.last _ name, e1.salary, e1.hire _ date from employees e1, employees e2 where e1.department _ id = e2.department _ id and e1.hire _ date> e2.hire _ dateand e1.salary> e2.salary; (due to space restrictions, incomplete, 65 rows in total, 3 columns)