CREATE OR REPLACE FUNCTION F_YEAR( I_YEAR NUMBER ) RETURN VARCHAR2 IS BEGIN IF MOD(I_YEAR,2)0 AND MOD(I_YEAR,100)<>0 OR MOD(I_YEAR,400)0 THEN RETURN 闰年; ELSE RETURN 平年; END IF; END F_YEAR;
--基础题 1.查询20号部门的员工姓名、岗位、薪资 SELECT ENAME,JOB,SAL/*,DEPTNO*/ FROM EMP WHERE DEPTNO 20; 2.查询工资超过3000的员工的姓名、薪资 SELECT ENAME,SAL FROM EMP WHERE SAL >3000; 3.查询10号部门以外的员工的所有信息 SELECT * FROM EMP WHERE DE…
1.查询员工编号、姓名、薪资,按薪资升序排列 SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL; 2.查询员工编号、姓名、薪资、部门编号,按部门升序及薪资降序排列 SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP ORDER BY DEPTNO,SAL DESC; 3.查询除20号部门…
1.去除字符串“ aadde gf ”两边的空格 SELECT TRIM( aadde gf ) FROM DUAL; 2.显示所有员工姓名的前三个字符。 SELECT /*ENAME,*/SUBSTR(ENAME,1,3) FROM EMP; 3.显示正好为5个字符的员工的姓名。 SELECT * FROM EMP WHERE ENAME LIKE _____; SELECT * FROM EMP WHERE LENGT…
1.找出工资高于SMITH的员工姓名,工资 1)查询工资高于XXX的员工姓名和薪资 SELECT ENAME,SAL FROM EMP WHERE SAL > XXX; 2)查询SMITH的薪资XXX SELECT SAL FROM EMP WHERE ENAME SMITH; 3) 拼接 SELECT ENAME,SAL FROM EMP WHERE SAL &…