Oracle learning(4)

Department table:

Employee table:


where subquery

Query employee information in the sales department

select * from emp where deptno = (select deptno from dept where dname = 'SALES');

Query the information of employees whose salary grade is 2

select * from emp where sal between (select losal from salgrade where grade = 2 ) and (select hisal from salgrade where grade = 2 );


concat() connection string

Display the employee’s name and type of work in one column

select concat(ename,job) from emp;

instr(x, str, start, n) Find the starting position of str start in x

select ename, instr(ename,'S') from emp;

View the name of each employee consists of several characters

select ename,length(ename) from emp;

Query the names of employees in 10 departments and sort them in descending order according to the length of the name characters

select ename,deptno from emp where deptno = 20 order by length(ename) desc ;

Query employee information in 20 departments, and change all employee names to lower (uppercase [upper]), and sort them in descending order of salary

select ename,sal,lower(ename) from emp order by sal desc;

Remove the left space, remove the right space, remove the spaces on both sides

select rtrim(ltrim(' abcd ')) from dual;

Replace the a in'a123b' with-

select replace('a123b','a','-') from dual;

Get the first character of the employee's name

select ename, substr(ename,1,1) from emp;

Get the date of the current system

select sysdate from emp;

Get the date one day after the current system

select last_day(sysdate) from dual;

Query the employee’s name entry date and correction date [entry date + 3 months]

select ename,hiredate,add_months(hiredate,3) from emp;

Query the total number of months from the employee's entry to today

select ename,hiredate,months_between(sysdate,hiredate) as monthsTotal from emp;

Convert the current system time to character type

select to_char(sysdate,'YYYY-MM-DD') from dual;

Convert string to time type

select to_date('2020-01-01','YYYY-MM-DD') from dual;

Group function [seeking a result of a whole]

Query the average salary of company employees

select avg(sal) from emp;

Query the total number of employees

select count(empno) from emp;

Query the highest and lowest wages of employees

select min(sal), max(sal) from emp;

Query how much salary needs to be paid to employees in 20 departments each month

select sum(sal) from emp where deptno = 20;