Oracle classic query trainer Article 3

This article will discuss and share with you some common classic queries of oracle SQL. You are welcome to add them. You can also share some classic queries. In this article, if you think there is any better solution to every problem, you are welcome to raise it in time. Communication and sharing can make progress together. Thank you!

Next two articles:

[Recommended] oracle SQL: html "> first article on classical Query

[Recommended] oracle SQL: the second article of classical query practitioners

This article is more difficult than the previous two articles. Continue to work hard. Through the series that I set up for you, as long as you test each article and every question and practice it carefully. I believe that you are familiar with common and classic SQL statements!

--------------------------------------------------------------------------------

 

This document uses the HR instance data that comes with ORACLE. The table structure used in this article is as follows:
The HR. EMPLOYEES employee table structure is as follows:

 

The structure of the HR. DEPARTMENTS table is as follows:

 

The HR. REGIONS table structure is as follows:

 

SQL> DESC HR. REGIONS;
Name Type Nullable Default Comments
----------------------------------------------
REGION_ID NUMBER
REGION_NAME VARCHAR2 (25) Y
 

 

 

--------------------------------------------------------------------------------

Use SQL to complete the following problem list:

 

1. Let SELECT TO_CHAR (SALARY, L99, 999.99) from hr. employees where rownum <5 the monetary unit of the output result is $ 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.
 

The answers to each question are as follows (You are welcome to point out different methods or suggestions !) :

/* -------- 1. Change the value of NLS_LANG to make SELECT TO_CHAR (SALARY, L99, 999.99) from hr. employees where rownum <5. The currency units of the output result are $ and $. ---------*/
----- Without setting NLS_LANG:

SQL> SELECT TO_CHAR (SALARY, l99, 999.99)
2 from hr. EMPLOYEES
3 where rownum <5;

TO_CHAR (SALARY, l99, 999.99)
----------------------------
￥24,000.00
￥20,000.00
￥20,000.00
￥9,000.00

SQL> SELECT TO_CHAR (SALARY, $99,999.99)
2 from hr. EMPLOYEES
3 where rownum <5;
 
TO_CHAR (SALARY, $99,999.99)
----------------------------
$24,000.00
$20,000.00
$20,000.00
$9,000.00

/* -- Description: For $99,999.99 format characters:
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.
,: Represents a thousands Separator
--------------*/

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

SQL> SELECT FIRST_NAME, SALARY, ROUND (SALARY * 1.08) FROM HR. EMPLOYEES
2 where rownum <= 5;
 
FIRST_NAME salary round (SALARY * 1.08)
------------------------------------------------
Steven 24000.00 25920
Neena 20000.00 21600
Lex 20000.00 21600
Alexander 9000.00 9720
Bruce 6000.00 6480

/* -------- 3. Find out who is the top leader and display the name in uppercase. ---------*/
SQL> SELECT UPPER (FIRST_NAME | LAST_NAME) AS NAME
2 from hr. EMPLOYEES
3 WHERE MANAGER_ID is null;
 
NAME
----------------------------------------------
STEVEN KING

/* -------- 4. Find out the name of David's direct leader. ---------*/
SQL> SELECT UPPER (FIRST_NAME | LAST_NAME) AS NAME
2 from hr. EMPLOYEES
3 WHERE EMPLOYEE_ID IN (
4 SELECT MANAGER_ID from hr. EMPLOYEES
5 WHERE FIRST_NAME = David AND LAST_NAME = Austin );
 
NAME
----------------------------------------------
ALEXANDER HUNOLD

-- Or use the following method:

SQL> SELECT UPPER (EMP1.FIRST _ NAME | EMP1.LAST _ NAME) AS NAME
2 from hr. EMPLOYEES EMP1, HR. EMPLOYEES EMP2
3 WHERE EMP1.EMPLOYEE _ ID = EMP2.MANAGER _ ID
4 AND EMP2.FIRST _ NAME = David AND EMP2.LAST _ NAME = Austin;

NAME
----------------------------------------------
ALEXANDER HUNOLD
 
/* -------- 5. The First_Name is Alexander, and the LAST_NAME is Hunold. (Who reports to David ). ---------*/
SQL> SELECT UPPER (FIRST_NAME | LAST_NAME) AS NAME
2 from hr. EMPLOYEES
3 WHERE MANAGER_ID IN (
4 SELECT EMPLOYEE_ID from hr. EMPLOYEES
5 WHERE FIRST_NAME = Alexander AND LAST_NAME = Hunold );
 
NAME
----------------------------------------------
BRUCE ERNST
DAVID AUSTIN
VALLI PATABALLA
DIANA LORENTZ

-- Or use the following method:

SQL> SELECT UPPER (EMP1.FIRST _ NAME | EMP1.LAST _ NAME) AS NAME
2 from hr. EMPLOYEES EMP1, HR. EMPLOYEES EMP2
3 WHERE EMP1.MANAGER _ ID = EMP2.EMPLOYEE _ ID
4 AND EMP2.FIRST _ NAME = Alexander AND EMP2.LAST _ NAME = Hunold;
 
NAME
----------------------------------------------
BRUCE ERNST
DAVID AUSTIN
VALLI PATABALLA
DIANA LORENTZ

/* -------- 6. Employees who have higher salaries than their direct superiors