select * from Emp where sal > 2000 and comm > 200;
select * from Emp where sal > 2000 and job='Clerk';
select * from Emp where sal in (1250,1100,2850);
select * from Emp where sal between 1250 AND 2850;
SELECT * FROM Emp WHERE ename LIKE '%AS';
SELECT * FROM Emp WHERE job LIKE 'C%K';
/*
like is used for pattern matching,
% is a wild card characters,
_ is a wild card character represents a single character.
*/
select * from Emp where job like '__L%M__';
select * from Emp where sal not in (1250, 1100, 2850)
select * from Emp where sal not between 1250 AND 2850;
SELECT * FROM Emp WHERE job LIKE 'C_E%K';
SELECT * FROM Emp WHERE comm IS NULL;
select * from Emp where sal is NULL and ename like 'S%';
select * from Emp where job like '_____';
select * from Emp where job like '_____' and ename like 'A%';
________________________________________________________________________
select ename, sal, deptno from Emp where deptno in (20,30,40);
select empno, ename, sal+ifnull(comm, 0)+sal*0.10 as totalsal from Emp;
select ename, job from Emp where hiredate < '1980-01-01' and sal between 1200 and
2500;
select empno, ename, deptno from Emp where mgr = 10;
select ename, job, sal from Emp where dept in (10,30);
SELECT ename || ', ' || deptno AS Emp_info FROM Emp;
select * from Emp where mgr is NULL;
select ename, deptno, hiredate
from Emp
where hiredate between '1981-01-01' and '1983-03-31'
order by hiredate asc;
select *
from Emp
where job like '%AGE%';
SELECT *
FROM Emp
WHERE (ename LIKE 'A%' AND ename LIKE '%S')
OR (ename LIKE '_N%' OR ename LIKE '__N%')
AND (ename LIKE '%N' OR ename LIKE '%S');
SELECT ename
FROM Emp
WHERE ename LIKE '%[_]%';
________________________________________________________________________
SELECT deptno, MAX(sal) AS Maximum, MIN(sal) AS Minimum, SUM(sal) AS Total,
AVG(sal) AS Average
FROM Emp
GROUP BY deptno;
SELECT deptno, count(mgr) AS TotalNumberofManagers
FROM Emp
GROUP BY deptno;
SELECT deptno, SUM(sal) AS total_salary
FROM Emp
WHERE mgr IS NOT NULL
GROUP BY deptno
HAVING SUM(sal) > 20000;
