1. Query the information of the employee whose third letter of ename is a (using two methods).
analysis:Either fuzzy query, or accurately locate the location and then judge
SELECT * FROM EMP WHERE ENAME LIKE '__A%'; SELECT * FROM EMP WHERE SUBSTR(ENAME,3,1) = 'A';
2. Inquire how many months the employees of department 10 and 20 have worked as of January 1, 2000
analysis:How long have you been working with months_ Between function, the first parameter minus the second parameter returns the number of months of difference.
SELECT DEPTNO,sum(MONTHS_ Between (date'2000-01-01 ', hiredate)) as month from EMP WHERE DEPTNO = 10 OR DEPTNO = 20 GROUP BY DEPTNO;
3. Display name, hiredate and what day is the employee’s start working day?
analysis:TO_ Char function parameter day, directly returns the day of the week
SELECT ename,hiredate,TO_CHAR(HIREDATE,'DAY') FROM EMP;
4. Query all employees whose initial letters are j, a or m, display the full name of employees with uppercase and other lowercase letters, and display the length of the first name, and sort the query results in ascending order according to the full name of employees.
analysis:As above, interception judgment can be fuzzy query or accurate query. Multiple conditional judgment can use in or or.
SELECT E.*, Concat (upper (substr (ename, 1,1)), lower (substr (ename, 2))) as name, Length (ename) as length FROM (SELECT * FROM EMP WHERE ENAME LIKE 'J%' OR ENAME LIKE 'A%' OR ENAME LIKE 'M%') E Name of order by;
SELECT UPPER(SUBSTR(ENAME,1,1))||LOWER(SUBSTR(ENAME,2,LENGTH(ENAME)-1)) AS NEW_NAME FROM EMP WHERE SUBSTR(ENAME,1,1) IN ('J','A','M') ORDER BY NEW_NAME;
5. Query the employee’s name whose department number is 10 or 20, the entry date is after May 1, 81, and the name contains the capital letter A, and the employee’s name length
SELECT * FROM EMP WHERE DEPTNO IN (10,20) AND INSTR(ENAME,'A',1)>0 AND HIREDATE > DATE'1981-05-01';
6. Display the employee’s salary in the following format: 123234.00 RMB.
analysis:To_ In the char function, FM can be used as RMB, and the numbers are divided by commas, which is equivalent to the accounting special under Ctrl + 1.
SELECT TO_ Char (SAL, 'fm9999999999.00') |'rmb 'as employee salary from EMP;
7. Query the employee’s salary in the employee table and calculate the tax payable: if the wage is less than 1000, the tax rate is 0; if the wage is greater than or equal to 1000 and less than 2000, the tax rate is 10%; if the wage is greater than or equal to 2000 and less than 3000, the tax rate is 15%; if the wage is greater than or equal to 3000, the tax rate is 20%.
analysis:Use case when to judge multiple conditions, and then create a new column
SELECT E.SAL, CASE WHEN E.SAL>= 3000 THEN 0.2 WHEN E.SAL>2000 THEN 0.15 WHEN E.SAL>1000 THEN 0.10 Else 0 end as FROM EMP E;
analysis:Connect the two tables, and then judge that the minimum wage should not be less than 2000 after grouping by employee number
SELECT E2.EMPNO,MIN(E1.SAL)FROM EMP E1 LEFT JOIN EMP E2 ON E1.MGR = E2.EMPNO GROUP BY E2.EMPNO HAVING E2.EMPNO IS NOT NULL AND MIN(E1.SAL)>2000;