OracleSQL classic query trainer Article 2

OracleSQL classic query trainer Article 2

Oracle SQL classic query trainer Article 2

This article shares the second article on typical Oracle SQL queries. 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.

Oracle 11.2.0 is used on this computer, and scott is used for logon. The built-in table is used.

Table Structure:

Describe emp;

Describe dept;

Select * from emp;

 

Select * from dept;

 

---- Scott user ----

1. Find out the name (ENAME) of the employee whose third letter is A in the EMP table.

2. Find the names of employees whose names contain A and N in the EMP table.

3. Find all employees with commissions, and list their names, salaries, and commissions. The results show that the salaries are from small to large.

4. list all positions numbered 20.

5. List departments that do not belong to SALES.

6. display the information of employees whose salaries are not between 1000 and 1500: name, salary, sorted by salary from large to small.

7. Information about employees with an annual salary of between 15000 and 20000 is displayed as MANAGER and SALESMAN: name, position, and annual salary.

8. Explain the output results of the following two SQL statements:

Select empno, comm from emp where comm isnull;

Select empno, comm from emp where comm = NULL ;.

9. is an error reported in the SELECTENAME, sal from emp where sal> '123' statement?

10. Find the employees with the lowest salaries in each department

 

-- 1. Find out the name (ENAME) of the employee whose third letter is A in the EMP table.

Select enamefrom empwhere enamelike '_ A % ';

-- 2. Find the names of employees whose names contain A and N in the EMP table.

Select enamefrom empwhere enamelike '% A %' and ename like '% N % ';

 

-- 3. Find all employees with commissions, and list their names, salaries, and commissions. The results show that their salaries increase from small to large.

Select ename, sal + nvl (comm, 0) salary, comm from emporderby salarydesc;

-- 4. list all positions numbered 20.

Select jobfrom empwhere deptno = 20;

-- 5. List departments that do not belong to SALES.

Selectdistinct * from deptwhere dept. dname <> 'sales ';

-- 6. display the information of employees whose salaries are not between 1000 and 1500: Name and salary, sorted by salary from large to small.

Select ename, salfrom empwhere salnotbetween1000 and1500orderby saldesc;

-- 7. Information about employees with an annual salary of between 15000 and 20000 is displayed as MANAGER and SALESMAN: name, position, and annual salary.

Select ename, job, (sal + nvl (comm, 0) * 12 salaryfrom emp where (sal + nvl (comm, 0) * 12 between15000and20000 and jobin ('manager ', 'salesman ');

-- 8. Explain the output results of the following two SQL statements:

-- Select empno, comm from emp where comm isnull;

-- Select empno, comm from emp where comm = NULL ;.

-- Solution: the first sentence can output records with comm null, but the second sentence cannot.

-- Note: is null is used to determine whether a field is null. null is not equivalent to a NULL string or a number 0;

-- And = NULL is to judge whether a value is equal to NULL. NULL = NULL and NULL <> NULL are both FALSE.

-- 9. Does the SELECTENAME, sal from emp where sal> '20160301' report an error?

-- No

Select ename, salfrom empwhere sal> '123 ';

 

-- 10. Find the employees with the lowest salaries in each department

Select deptno, min (sal + nvl (comm, 0) minsal from emp groupby deptno;

 

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