作业一

1.Select *from stuinfo; select *from stumarks;
2.Select STUNAME from stuinfo where STUSEX='男';
3.Select STUNAME，STUNO，STUSEX，STUAGE，STUADDRESS from stumarks where WRITTENEXAM>=75 and WRITTENEXAM<=100 inner join stumarks on stuinfo.STUNO=stumarks.STUNO;
4.Update stuinfo set STUADDRESS=‘地址不详’ where STUADDRESS is null;
5.Select avg(WRITTENEXAM),avg(LABEXAM) from stuinfo inner join stumarks on stuinfo.STUNO=stumarks.STUNO; 6.Select count(WRITTENEXAM) from stuinfo inner join stumarks on stuinfo.STUNO=stumarks.STUNO;
7.Select count * from stuinfo inner join stumarks on stuinfo.STUNO=stumarks.STUNO where WRITTENEXAM<60 or LABEXAM<60;
8.Select STUNAME,stuinfo.STUNO,WRITTENEXAM,LABEXAM,((WRITTENEXAM+LABEXAM)/2) from stuinfo inner join stumarks on stuinfo.STUNO=stumarks.STUNO;
9.Select stuinfo.STUNO,((WRITTENEXAM+LABEXAM)/2) from stuinfo inner join stumarks on stuinfo.STUNO=stumarks.STUNO order by ((WRITTENEXAM+LABEXAM)/2) desc;
10.Update stumarks set LABEXAM= LABEXAM+5;
11.Update stumarks set LABEXAM=100 whereLABEXAM>=95;
Update stumarks set LABEXAM=LABEXAM+5 where LABEXAM<95;
12.Update stumarks set WRITTENEXAM=60 where WRITTENEXAM<60; Update stumarks set WRITTENEXAM=WRITTENEXAM+5 where WRITTENEXAM>=60 and WRITTENEXAM<=90; Update stumarks set WRITTENEXAM=WRITTENEXAM+3 where WRITTENEXAM>90;

作业二

1.Select ename,job,mgr,hiredate,sal,comm,deptno from emp where deptno=30;
2.Select ename,empno,deptno from emp where job=’CLERK’;
3.Select * from emp where comm>sal;
4.Select * from emp where comm>sal*0.6;
5.Select empno ,ename, job,hiredate,sal,comm,deptno from emp where( job='MANAGER' and deptno=10) or ( job='CLERK' and deptno=20);
6.Select empno,ename,job, hiredate,sal, deptno from emp where ( job='MANAGER' and deptno=10) or ( job='CLERK' and deptno=20) or ((job <>'MANAGER' and job<>'CLERK') and sal>=2000);
7.Select distinct job from emp where NVL(comm,0)>0;
8.Select ename,comm from emp where NVL(comm,0)<100;
9.Select ename, job,last_day(hiredate) from emp; Select ename, job,hiredate from emp where hiredate=last_day(hiredate);
11.Select initcap(ename) name ,job, hiredate from emp;
12.Select ename,job,hiredate from emp where length(ename)=6;
13.Select ename, job from emp where ename not like '%R%;
14.Select substr(ename, 1,3) name,job,hiredate from emp；
15.Select replace(ename, 'A','a') name,job,hiredate from emp;
17.Select * from emp order by ename;
18.Select ename, hiredate from emp order by hiredate desc;
19.Select ename,job, sal from emp order by job desc, sal asc;
21.Select ename, sal/30 dailysal from emp;
22.Select ename, hiredate from emp where to_char(hiredate,'mm')='02';
23.Select ename,sysdate-hiredate from emp;
24.Select ename from emp where instr(ename,'A',1)>0;
25.Select ename, to_char(hiredate,'yyyy/mm/dd') from emp；