SQL query Topics

--1 and enquiries about employees who entered the company in 1981
SELECT *
From EMP
where hiredate between ' January-January-1981 '
and ' 3 January-December-1981 ';

SELECT *
From EMP
where HireDate >= ' January-January-1981 '
and HireDate <= ' 3 January-December-1981 ';
--2, query Manager number not 7902 of employees
SELECT *
From EMP
where mgr! = 7902;

SELECT * FROM emp
where Mgr <> 7902;

--3, Fuzzy query
--Query employee information for the first letter in the name is S
--% characters 0 to any bit
1 bits in the--_ character
--like
SELECT * FROM emp
where ename like ' s% ';
--Query employee information for the second letter L
SELECT * FROM emp
where ename like ' _l% ';
--Query employee information with s in the name
SELECT * FROM emp
where ename like '%s% ';

--4, Query 7698, 7788, 7902 management of employee information
SELECT * FROM emp
where mgr = 7698 or mgr = 7788 or mgr = 7902;

SELECT * FROM emp
where Mgr in (7698,7788,7902);

--5 employee information that pays better than Jones's salary
SELECT * FROM emp
where Sal >
(Select Sal from emp
where ename = ' JONES ');

--6, the number of employees in the employee table, the total wage, the maximum wage, the minimum wage, the average wage
Select COUNT (*), sum (SAL), Max (Sal), Min (sal), avg (SAL)
from EMP;
--7, find information about employees and your department
--Descartes Product
SELECT * FROM Emp,dept
where Emp.deptno = Dept.deptno;

--8, query employee information and the department's information, request to have no staff of the department also to show out
SELECT * FROM Emp,dept
where Emp.deptno (+) = Dept.deptno

--9, query employee information and manager information
SELECT * FROM emp a,emp b
where A.mgr=b.empno (+);

SQL query Topics