数据库练习题

select * from emp;
select * from emp where deptno=30;
select empno,ename from emp where job='MANAGER';
select * from emp where sal>300;
select * from emp where comm>sal;
select * from emp where deptno=10 and job not in ('MANAGER','CLERK') and sal>2000;
select distinct job from emp where comm is not null and comm >0;
select * from emp where comm is null or comm <500;
select ename,hiredate from emp order by hiredate desc;
select initcap(ename) from emp;
select length(ename) from emp;
select concat(ename,hiredate) from emp;
select replace(ename,'a','A') from emp;
select lpad(ENAME,6,'*') from emp;
select * from emp where ename='SMITH';
SELECT TRIM('Mr SMITH') from emp;
select empno, lpad(initcap(trim(ename)),10,'') name,job,sal from emp;
select * from emp where ename=upper('smith');
select round(sysdate,'month') from emp;
select round(sysdate,'year') from emp;
select to_char(sal,'9900.99') from emp;

SELECT TO_CHAR(sal, '$99,999.000') SAL123
FROM emp
WHERE ename = 'JONES';

select to_number(to_char(sal,'9900.99')) SAL from emp;
SELECT add_months(sysdate,2) from dual;
select last_day(sysdate) from dual;
select to_char(sysdate,'yyyy') from dual;
select to_char(sysdate,'fmyyyy-mm-dd') from dual;
select to_date('20170813','yyyymmdd') from dual;
SELECT substr('abcdefg',length('abcdefg')-2) from dual;
SELECT substr('abcdefg',-1,1) from dual;
select instr('Hello World','r') from dual;
select round(412,3) from dual;
select trunc(412.123,2) from dual;
select mod(412.214,3) from dual;
select nvl(comm,0) from emp;
select to_char(sysdate,'d') from dual;
select count(*) from emp;
select avg(sal) from emp;
select avg(comm) from emp;
select sum(comm) from emp;
select avg(nvl(comm,0)) from emp;
select deptno,job,avg(sal) from emp where hiredate >=to_date('1981-05-12','yyyy-mm-dd') group by deptno,job having avg(sal)>1200 order by deptno,job;
select sal from emp order by sal desc;