SQL基本表クエリ

--1，    30    
SELECT *
FROM EMP
WHERE DEPTNO = 30;

--2,          ，     
SELECT ENAME,EMPNO,DEPTNO 
FROM EMP
WHERE JOB = 'CLERK';

--3，           
SELECT *
FROM EMP
WHERE NVL(COMM,0) > SAL;

--4，         60%   
SELECT * 
FROM EMP 
WHERE NVL(COMM,0) > SAL * 0.6;

--5，    10        20           
SELECT * 
FROM EMP
WHERE (DEPTNO = 10 AND JOB = 'MANAGER')
OR (DEPTNO = 20 AND JOB = 'CLERK');
--
SELECT * 
FROM EMP 
WHERE DEPTNO = 10
AND JOB = 'MANAGER'
UNION
SELECT *
FROM EMP
WHERE DEPTNO = 20
AND JOB = 'CLERK';  

--6，    10     ，  20      
--                      2000
--          
SELECT *
FROM EMP
WHERE (DEPTNO = 10 AND JOB = 'MANAGER')
OR (DEPTNO = 20 AND JOB = 'CLERK')
OR (JOB NOT IN ('MANAGER','CLERK') 
AND SAL >= 2000);
--
SELECT * 
FROM EMP
WHERE DEPTNO = 10 
AND JOB = 'MANAGER'
UNION
SELECT *
FROM EMP
WHERE DEPTNO = 20
AND JOB = 'CLERK'
UNION
SELECT *
FROM EMP
WHERE JOB != 'MANAGER'
AND JOB != 'CLERK'
AND SAL >= 2000;

--7，              
SELECT DISTINCT JOB
FROM EMP
WHERE NVL(COMM,0) > 0;

--8，               100   
SELECT * 
FROM EMP
WHERE NVL(COMM,0) < 100
OR COMM IS NULL;

--9，                
SELECT *
FROM EMP
WHERE HIREDATE = LAST_DAY(HIREDATE) -2;

--10，    12       
SELECT *
FROM EMP
WHERE HIREDATE < ADD_MONTHS(SYSDATE,-12*12);

--11，                  
SELECT INITCAP(ENAME) AS   
FROM EMP;

--12，     5         
SELECT ENAME 
FROM EMP
WHERE LENGTH(ENAME) = 5;
--
SELECT ENAME
FROM EMP
WHERE ENAME LIKE '_____';

--13，    ‘R’      
SELECT ENAME 
FROM EMP
WHERE ENAME NOT LIKE '%R%'; 

--14，              
SELECT SUBSTR(ENAME,1,3) AS ENAME
FROM EMP;

--15，         ， a    ‘A’
SELECT REPLACE(ENAME,'A','a') AS ENAME
FROM EMP;
--
SELECT TRANSLATE(ENAME,'A','a') AS ENAME
FROM EMP;

--16，   10                
SELECT ENAME,HIREDATE
FROM EMP
WHERE MONTHS_BETWEEN(SYSDATE,HIREDATE) > 120;--!      
--
SELECT ENAME,HIREDATE 
FROM EMP
WHERE SYSDATE > ADD_MONTHS(HIREDATE,12 * 10);

--17,         ，     
SELECT * 
FROM EMP
ORDER BY ENAME;

--18，            ，       ，
--           
SELECT ENAME,HIREDATE
FROM EMP
ORDER BY HIREDATE;

--19，         、     ，    
--    ，           
SELECT ENAME,JOB,SAL
FROM EMP
ORDER BY JOB DESC,SAL;

--20，         、          ，
--          ，         
--          
SELECT ENAME,TO_CHAR(HIREDATE,'YYYY" "MM" "')
FROM EMP 
ORDER BY TO_CHAR(HIREDATE,'MM'),TO_CHAR(HIREDATE,'YYYY')

--21，       30            ，
--    
SELECT ENAME,TRUNC(SAL/30)
FROM EMP;

--22,   （     ）2        
SELECT * 
FROM EMP
WHERE TO_CHAR(HIREDATE,'MM') = '02';

--23，      ，          
SELECT ENAME,FLOOR(SYSDATE - HIREDATE)
FROM EMP;

--24，             ‘A’        
SELECT *
FROM EMP
WHERE ENAME LIKE '%A%';

--25，                  （  ）
SELECT EMPNO,ENAME,
(
       '  '||TRUNC((MONTHS_BETWEEN(SYSDATE,HIREDATE)/12))||' '||
       TRUNC(MOD((MONTHS_BETWEEN(SYSDATE,HIREDATE)),12))||'  '||
       ROUND(SYSDATE-(ADD_MONTHS(HIREDATE,MONTHS_BETWEEN
       (SYSDATE,HIREDATE))))||' '       
)
FROM EMP;