Simple query and limited query, simple limitation

Simple query and limited query, simple limitation

Use a table under scott,

 

1. -- Query all the table content
SELECT * FROM emp;
2. -- Query employee ID, name, and basic salary
SELECT e. empno, e. ename, e. sal
FROM emp e;
3. -- Query positions and remove duplicates
Select distinct e. job
FROM emp e;
4. -- Query employee ID, name, basic salary, monthly salary, and annual salary
SELECT e. empno number, e. ename name, e. sal monthly salary, e. sal * 12 | 'yuan' annual salary, ROUND (e. sal/30, 2) daily salary, '$' currency
FROM emp e;
5. -- query the number of records in the table
Select count (*) FROM emp;

6. -- Query Information about all employees whose basic salary is higher than 1500
SELECT *
FROM emp e
WHERE e. sal> 1500;

7. -- Query Information about all employees whose basic salary is less than or equal to 2000
SELECT *
FROM emp e
WHERE e. sal <= 2000;
8. -- Query employee information whose name is smith
SELECT *
FROM emp e
WHERE e. ename = UPPER ('Smith ');

9. -- Query Information about employees whose position is clerk
SELECT *
FROM emp e
WHERE e. job = 'cler ';
10 -- Query Information about employees whose positions are not clerk

SELECT *
FROM emp e
Where not e. job = 'cler ';

SELECT *
FROM emp e
WHERE e. job <> 'cler ';

SELECT *
FROM emp e
WHERE e. job! = 'Cler ';
11. -- query the salary range from 1500 ~ 3000 contains information about 1500 and 3000 of employees
SELECT *
FROM emp e
WHERE e. sal BETWEEN 1500 AND 3000;

12. -- Query Information about employees whose positions are salesman and whose salaries are higher than 1200.
SELECT *
FROM emp e
WHERE e. job = 'salesman' AND e. sal> 1200;
13. -- Query managers in 10 departments or sales personnel in 20 departments
SELECT *
FROM emp e
WHERE (e. deptno = 10 AND e. job = 'manager ')
OR (e. deptno = 20 AND e. job = 'cler ')
14. -- Query employees who are not sales personnel and whose salaries are greater than 2000
SELECT *
FROM emp e
WHERE e. job <> 'cler' AND e. sal> 2000;
15. -- Query employees who joined the company in 1981
SELECT *
FROM emp e
WHERE to_char (e. hiredate, 'yyyy') = '20140901 ';
-- Determines whether the content is null, is not null
16. -- Query Information of all employees who receive funds
SELECT *
FROM emp e
WHERE e. comm is not null;
17. -- Query Information of all employees who do not receive funds
SELECT *
FROM emp e
WHERE e. comm is null;
18. -- Query Information of all employees who do not receive funds and their salaries exceed 2000.
SELECT *
FROM emp e
WHERE e. comm is null and e. sal> 2000;
19. -- Query employees who do not receive commission or receive commission less than 100 yuan
SELECT *
FROM emp e
WHERE e. comm is null or e. comm <100;

20. -- find jobs for different positions of employees who receive commissions
-- First find the JOB for the employee who receives the Commission
SELECT e. job
FROM emp e
WHERE e. comm is not null;
-- Duplicate use of distinct
Select distinct e. job
FROM emp e
WHERE e. comm is not null;

-- Query in and not in by column range. Multiple conditions are fixed before use.
-21.-query the employee information whose employee number is 7566
-- Can be written using OR.
SELECT *
FROM emp e
WHERE e. empno = 7369 OR e. empno = 7788 OR e. empno = 7566
-- Using in to write can save a lot of code
SELECT *
FROM emp e
WHERE e. empno IN (7566 );

22. -- query the information of employees whose employee numbers are not 7566, and
SELECT *
FROM emp e
WHERE e. empno not in (7566 );

-- Fuzzy query like not like
-- % Matches 0, 1, or multiple characters
-- _ Underline can only match one character
23. -- query the employee information starting with "S"
SELECT *
FROM emp e
WHERE e. ename LIKE's % ';
24. -- query the employee information whose name contains 2nd letters (M)
SELECT *
FROM emp e
WHERE e. ename LIKE '_ M % ';
25. -- query the employee information whose name contains F
-- Analyze F first and use % to match any position in the name.
SELECT *
FROM emp e
WHERE e. ename LIKE '% F %'

26. -- query the employee information whose name length is 6 or more than 6
SELECT *
FROM emp e
Where length (e. ename)> = 6;
-- You can also use an underscore to match 6 underlines.
SELECT *
FROM emp e
WHERE e. ename LIKE '______ % ';

27. -- Query employees whose basic salary includes one or 81 years of employment
SELECT *
FROM emp e
WHERE e. sal LIKE '% 100' OR to_char (e. hiredate, 'yyyy') = '123 ';
-- Change to AND
SELECT *
FROM emp e
WHERE e. sal LIKE '% 100' AND to_char (e. hiredate, 'yyyy') = '123 ';

28. -- Query all managers in department 10 and all salesmen in department 20,
-- All employees who are neither a manager nor a salesman but whose salaries are greater than 2000 Yuan
-- The employee name also contains S or K
SELECT *
FROM emp e
WHERE (e. deptno = 10 AND e. job = 'manager ')
OR (e. deptno = 20 AND e. job = 'cler ')
OR (e. job not in ('manager', 'cler') AND e. sal> 2000 ))
AND (e. ename LIKE '% S %' OR e. ename LIKE '% K % ');

-- Order by sorting ASC can be omitted, ascending DESC descending 
29. -- Query all employees and sort by salary from high to low
SELECT *
FROM emp e
Order by e. sal DESC;
30. -- Query all employees and sort by salary from low to high
SELECT *
FROM emp e
Order by e. sal ASC;
-- ASC can be left empty
SELECT *
FROM emp e
Order by e. sal;
31. -- query the detailed information of all salesmen and sort the basic salary from low to high.
SELECT *
FROM emp e
WHERE e. job = 'cler'
Order by e. sal ASC;

32. -- Query Information of all employees. The basic salary should be sorted from high to low. If the salary is the same, the basic salary should be sorted by early employment and late time.
SELECT *
FROM emp e
Order by e. sal DESC, e. hiredate ASC;