create table
EMP AS SELECT * FROM SCOTT.EMP;
SELECT * FROM EMP;
SELECT ENAME,JOB,DEPTNO
FROM EMP;
SELECT *
FROM EMP
WHERE DEPTNO IN (10,20);
SELECT *
FROM EMP
-- < ,> ,>=,> OR =,< OR =,<=,<>,!=
SELECT *
FROM EMP
WHERE COMM IS NULL;
SELECT *
FROM EMP
WHERE COMM IS NOT NULL;
SELECT *
FROM EMP
WHERE JOB NOT IN('MANAGER')
SELECT *
FROM EMP
WHERE JOB IN('MANAGER')
----SORTING (ASC,DESC)
SELECT *
FROM EMP
WHERE JOB = 'MANAGER' ORDER BY HIREDATE DESC;
----MAX,MIN,COUNT,SUM,AVG
SELECT SUM(SAL),DEPTNO
FROM EMP
GROUP BY DEPTNO;
SELECT AVG(COMM),SUM(SAL),DEPTNO
FROM EMP
GROUP BY DEPTNO;
SELECT * FROM EMP;
---FOR NON NULL VALUES COLUMNS
SELECT COUNT(JOB) AS COUNT,JOB
FROM EMP
GROUP BY JOB
ORDER BY 2;
SELECT COUNT(*) AS COUNT,JOB
FROM EMP
GROUP BY JOB
ORDER BY 2;
----FILTERING (WHERE,HAVING)
SELECT COUNT(*) AS COUNT,JOB ----4
FROM EMP ----1
WHERE JOB <> 'PRESIDENT' --------2
GROUP BY JOB --------3
ORDER BY 2; ---------5
SELECT MAX(SAL),ENAME
FROM EMP
GROUP BY ENAME
----COUNT OF EMPLOYEES IN EACH DEPARTMENT
SELECT COUNT(JOB),DEPTNO
FROM EMP
GROUP BY DEPTNO
--HOW MANY JOBS IN EACH DEPT
SELECT COUNT(*),DEPTNO,JOB
FROM EMP
GROUP BY DEPTNO,JOB ORDER BY DEPTNO
---HOW MANY EMPLOYEES IN EACH JOB
SELECT COUNT(*)
FROM EMP
GROUP BY JOB;
--HOW MANY JOBS IN EACH DEPART
SELECT COUNT(*),DEPTNO,JOB
FROM EMP
GROUP BY DEPTNO,JOB
---CREATING TABLES
Comments
Post a Comment