Four basic table SQL statement exercises by Scott users in Oracle

--Select 30 of employees in the department
SELECT * from EMP where deptno=30;
--List all clerks ' names, departments, numbers
--Using the internal connection, that is, the equivalent link, is also the most commonly used links
SELECT ename,empno,dname from emp e INNER JOIN
Dept D on E.deptno=d.deptno where job= ' clerk ';
-Finding employees with higher commissions than salaries
SELECT * FROM EMP where comm>sal;
--Finding employees with commissions above 60% of salary
SELECT * FROM EMP where comm>sal*0.6;
--Find all the clerks in department 10 all managers and departments 20
SELECT * from EMP where (deptno=10 and Job=upper (' manager '))
or (Deptno=20 and job= ' clerk ');
--Find out all the clerks in department 10 all managers and departments 20, neither the manager nor the clerk,
--but the information of all employees whose salary >=2000
SELECT * from EMP where (deptno=10 and Job=upper (' manager '))
or (Deptno=20 and job= ' clerk ')
or (Job<>upper (' manager ') and Job<>upper (' clerk ') and sal>=2000);
--Find out the different jobs of employees who receive commissions
SELECT DISTINCT job from EMP where comm>0;
-Find employees who do not charge commissions or are less than 100 commission
SELECT * from emp where NVL (comm,0) <100;
--Find out all employees employed on the last day of each month
SELECT * from emp where hiredate=last_day (HireDate);
--Identify employees employed before 25
SELECT * FROM emp
where "Months_between" (Sysdate, HireDate)/12>25;
--Displays the names of all employees with only the first letter capitalized
--The "Initcap" (CH) function capitalizes the first letter of each word and the other letters into lowercase
Select ename from emp where ENAME=INITCAP (ename);
--Display the name of an employee who is exactly 6 characters
Select ename from emp where length (ename) = 6;
--show the names of employees without like
Select ename from emp where ename does like '%r% ';
--or write it like this.
Select Ename from emp where InStr (ename, ' R ') = 0;
--Displays the first three letters of all employees ' names
Select substr (ename,1,3) from EMP;
--show the names of all employees, replace a with a
Select Replace (ename, ' a ', ' a ') from EMP;
--Displays the names of all employees and the dates of service life of 10 years
Select Ename, Add_months (hiredate,12*10) as service life date from EMP;
--Show employee details, sorted by name
SELECT * from emp order by ename;
-Show the employee's name, according to their service life, the oldest employees in the front
Select ename from emp order BY hiredate Desc;
--Displays the names, jobs, and salaries of all employees, sorted in descending order of work, sorted in ascending order by salary
Select Ename,job,sal from emp order by job desc,sal ASC;
--Displays the names of all employees and the year and month of incorporation
-Rank items in the earliest year by the month in which the employee is employed
Select Ename, "To_char" (hiredate, ' yyyy ') as year, "To_char" (hiredate, ' mm ') as month
From EMP order by hiredate ASC;
--Shows the daily salary of all employees in the case of one months for 30 days
Select Sal/30 "Daily Salary" from EMP;
--Find out all employees employed in February in any year
Select Ename as employee name, hiredate as employment date
From EMP where TO_CHAR (hiredate, ' mm ') = ' 02 ';
--For each employee, show the number of days they joined the company
Select Ename,sysdate-hiredate from EMP;
--The name of the employee who appears with the letter A in the name
Select ename from emp where ename like '%a% ';
-OR
Select Ename from emp where InStr (ename, ' A ', 1) >0;
--View all tables under the current user
SELECT * from tab;

Four basic table SQL statement exercises by Scott users in Oracle