[pl / SQL] basic exercises 1-8 (1)

Time:2020-8-12

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.

Methods 1

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;

Method 2

  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;

8. Query the minimum wage of employees under each manager. The minimum wage should not be less than 2000. Employees without managers are not included

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;