Oracle常用SQL查詢

標籤：

建表資訊

1.表結構Emp----員工資訊表Ename varchar2(30), --姓名Empno number(5),  --編號Deptno number(5), --所在部門Job varchar2(20), --工種(人員類別)，如：manager 經理，clerk 辦事員Hiredate  Date  --僱用日期Hiredate Date, --僱傭日期Comm number(6,2),  --傭金Sal  number(6,2)  --薪金Dept-----部門表Dname varchar2(30),  --部門名Deptno number(5), --部門號Loc varchar2(50)  --位置2.準備資料：create table emp –建立員工資訊表(Ename varchar2(30), --姓名Empno number(5),  --編號Deptno number(5), --所在部門Job varchar2(20), --工種(人員類別)，如：manager 經理，clerk 辦事員Hiredate  Date  --僱用日期Hiredate Date --僱傭日期Comm number(6,2),  --傭金Sal  number(6,2)  --薪金)insert into emp(Ename,Empno,Deptno,Job,Comm,Sal) values(‘劉濤‘,10001,10,‘辦事員‘,500,2000)insert into emp(Ename,Empno,Deptno,Job,Comm,Sal) values(‘吳昊‘,10002,10,‘辦事員‘,650,2200)insert into emp(Ename,Empno,Deptno,Job,Comm,Sal) values(‘唐丹丹‘,10002,20,‘辦事員‘,650,2200)insert into emp(Ename,Empno,Deptno,Job,Comm,Sal) values(‘李陽楊‘,20001,20,‘經理‘,980,3200)create table dept  --部門表(Dname varchar2(30),  --部門名Deptno number(5), --部門號Loc varchar2(50)  --位置)insert into dept(Dname,Deptno,Loc) values(‘市場部‘,10,‘遼寧大連‘)insert into dept(Dname,Deptno,Loc) values(‘公關部‘,20,‘遼寧瀋陽‘)select * from dept;

一、簡單查詢操作

　1、選擇部門30中的僱員     select * from emp e where e.deptno=‘30‘　2、列出所有辦事員的姓名、編號和部門    select e.ename, e.empno, d.dname from emp e, dept d where e.deptno=d.deptno　3、找出傭金高於薪金的僱員    select * from emp e where e.comm>e.sal4、找出傭金高於薪金60%的僱員select * from emp e where e.comm>e.sal * 0.65、找出部門10中所有經理和部門20中的所有辦事員的詳細資料select * from emp e where (e.job=‘?-àí‘ and e.deptno=‘10‘) or (e.deptno=‘20‘ and e.job=‘°ìê??±‘)6、找出部門10中所有經理、部門20中所有辦事員，既不是經理又不是辦事員但其薪金>=2000的所有僱員的詳細資料select * from emp e where (e.job=‘?-àí‘ and e.deptno=‘10‘) or (e.deptno=‘20‘ and e.job=‘°ìê??±‘) or (e.job!=‘?-àí‘ and e.job!=‘°ìê??±‘ and e.sal>=2000)　7、找出收取傭金的僱員的不同工作    select distinct job from emp where nvl(comm, 0)>0　8、找出不收取傭金或收取的傭金低於100的僱員    select  * from emp where nvl(comm, 0)>100　9、找出各月最後一天受雇的所有僱員select * from emp where hiredate=last_day(hiredate)　10、顯示只有首字母大寫的所有僱員的姓名select ename from emp where initcap(ename)=ename　11、顯示正好為6個字元的僱員姓名select ename from emp where length(ename)=6　12、顯示不帶有‘R‘的僱員姓名select ename from emp where ename not like ‘%r%‘　13、顯示所有僱員的姓名的前三個字元    select substr(ename, 1, 3) from emp14、顯示所有僱員的姓名，用a替換所有‘A‘select replace(ename, ‘a‘, ‘A‘) from emp　15、顯示所有僱員的姓名以及滿10年服務年限的日期    select ename, add_months(hiredate, 120) from emp　16、顯示僱員的詳細資料，按姓名排序    select * from emp e, dept d where e.deptno=d.deptno  order by e.ename　17、顯示僱員姓名，根據其服務年限，將最老的僱員排在最前面    select ename, hiredate from emp order by hiredate　18、顯示所有僱員的姓名、工作和薪金，按工作的降序順序排序，而工作相同時按薪金升序select ename, hiredate from emp order by hiredate　19、顯示所有僱員的姓名和加入公司的年份和月份，按僱員受雇日所在月排序，將最早年份的項目排在最前面select ename, to_char(hiredate, ‘yyyy‘), to_char(hiredate, ‘mm‘) from emp order by hiredate　20、顯示在一個月為30天的情況下所有僱員的日薪金    select ename, round(sal/30,2) from emp　21、找出在（任何年份的）2月受聘的所有僱員    select * from emp where to_char(hiredate, ‘mm‘)=‘02‘　22、對於每個僱員，顯示其加入公司的天數    select ename, trunc(sysdate-hiredate) from emp　23、顯示姓名欄位的任何位置，包含 "A" 的所有僱員的姓名    select * from emp where ename like ‘%a%‘　24、以年、月和日顯示所有僱員的服務年限    select ename, trunc(months_between(sysdate, hiredate)/12), trunc(months_between(sysdate, hiredate)), trunc(sysdate-hiredate) from emp

1學生表studentS#學號，sname姓名，difdate出生日期，   年級grade2課程表 coursec#課程號 ，名字cname3成績單scores#學號 c#課程號 成績scorecreate table student(       s#學號 varchar2(30),       sname姓名 varchar2(30),       difdate出生日期 date,       grade年級 varchar2(30))create table course(       c#課程號 varchar2(30),       cname名字 varchar2(30))create table score(       s#學號 varchar2(30),       c#課程號 varchar2(30),       score成績 varchar2(30))

二、條件查詢

1.查詢學生表中姓李並且是2007級的學生select * from student where sname姓名 like ‘李%‘ and grade年級=’2007’2.查詢 姓王的學生的物理課的成績select s.sname姓名,c.cname名字,t.score成績 from student s,course c,score t where s.s#學號=t.s#學號 and c.c#課程號=t.c#課程號 and s.sname姓名 like ‘王%‘ and t.c#課程號=‘4‘3.統計每個年級有多少人select grade年級，count(grade年級) from student  group by grade年級4.2007級的各學生的平均成績 沒有成績的為0；select t.s#學號,s.sname姓名,avg(t.score成績) from student s,score t where s.s#學號=t.s#學號 and s.grade年級=‘2007‘ group by (s.sname姓名,t.s#學號)5. 2007級學生每科的   平均成績和最高成績 最低成績 保留2位小數點 四捨五入select c.cname名字,t.c#課程號,round(avg(t.score成績),2),max(t.score成績¨),min(t.score成績)from course c,score t where c.c#課程號=t.c#課程號 and group by (t.c#課程號,c.cname名字6.給2007級學生的 數學成績加5分select s.sname姓名,c.cname名字,t.score成績+5 from student s,course c,score t where s.s#學號=t.s#學號 and c.c#課程號=t.c#課程號 and s.grade年級=‘2007‘ and t.c#課程號=‘2‘7.求同月出生的 人數select count(to_char(difdate出生日期,‘mm‘)) from student group by to_char(difdate出生日期,‘mm‘)8.刪除 姓名是張三 的大學語文 成績delete from score where s#學號=‘2008020177‘ and c#課程號=‘3‘9.將數學替換成高等數學update course set cname名字=‘高等數學‘ where c#課程號=‘2‘10.格式化 ，顯示 將學號修改成S開頭 不足12位左邊補0；elect ‘S‘||lpad(s#學號, 11, ‘0‘) from student s

Oracle常用SQL查詢