课堂随记

<pre name="code" class="sql">

SELECT sal ,sal+200 AS monthSal FROM emp
SELECT sal,sal*12 AS yearsal FROM emp
SELECT sal,comm,12*sal+comm FROM emp
SELECT 1+1 FROM emp
SELECT 1+NULL FROM emp
SELECT sal AS "salYear" FROM emp
SELECT * FROM emp
SELECT ename "员工姓名",sal "工资收入",comm "奖金收入",12*sal+6*sal*0.2 "总收入" FROM emp
SELECT DISTINCT job FROM emp
SELECT ename,deptno FROM emp WHERE deptno = 20
SELECT ename,deptno FROM emp WHERE deptno <> 20
SELECT ename,deptno,job FROM emp WHERE job = 'CLERK'
SELECT ename "员工姓名",job "职位",hiredate "入职日期" 
FROM emp
WHERE job='SALESMAN'


SELECT * FROM emp
SELECT ename "员工姓名",hiredate "入职日期" 
FROM emp
WHERE hiredate < '31-12月-85'
SELECT ename "员工姓名",empno "员工编号",deptno "部门号"
FROM emp
WHERE deptno <> 10


SELECT ename,sal 
FROM emp
WHERE sal BETWEEN 1000 AND 1500


SELECT ename,sal,hiredate 
FROM emp
WHERE sal BETWEEN 1000 AND 1500


SELECT ename,sal
FROM emp
WHERE ename LIKE 's%'


SELECT ename,sal
FROM emp
WHERE ename LIKE '_L%'


SELECT ename "员工姓名"
FROM emp
WHERE ename LIKE 'T__'


SELECT ename "员工姓名",comm "奖金"
FROM emp
WHERE comm IS NULL
SELECT * 
FROM emp
SELECT ename,deptno,job FROM emp
WHERE detpno = 10 AND job = 'CLERK'


SELECT ename,deptno,job FROM emp
WHERE detpno >= 10 OR job = 'CLERK'




SELECT ename,deptno,sal FROM emp
WHERE  deptno IN (10,20) 
AND (sal BETWEEN 3000 AND 5000)


SELECT * FROM emp


SELECT SUBSTR('hellorewwrryrrrrtyu',-2,2) FROM emp


SELECT ename FROM emp
WHERE Lower(ename) LIKE '%a%'


SELECT ename FROM emp
WHERE instr(ename,'A')>0


SELECT LPAD(empno,10,'*') || LPAD(ename,10,'*') || RPAD(sal,10,'*')
FROM emp


SELECT round(100.456,2),round(100.456,1),round(100.456,0)
FROM dual


SELECT TRUNC(100.456,2),TRUNC(100.456,1),TRUNC(100.456,0)
FROM dual


SELECT SYSDATE +10/24 FROM dual


SELECT MOD(10,35) FROM dual


SELECT months_between ('12-1月-83','12-1月-82') FROM dual


SELECT add_months('12-1月-83' , 10) FROM dual


SELECT next_day('12-1月-83','星期一') FROM dual


SELECT last_day('12-1月-83','星期一') FROM dual


SELECT round('12-1月-83',MONTH) FROM dual


SELECT extract( DAY FROM '12-1月-83') FROM dual