(转)SQL例句.doc

001 emp员工表使用scott/tiger用户下的emp表完成下列练习，表的结构说明如下emp员工表 字段内容如下:empno员工号ename员工姓名job工作mgr上级编号hiredate受雇日期sal薪金comm佣金deptno部门编号4.找出佣金高于薪金的60%的员工? select ename,comm,sal,sal*0.6 from emp where nvl(comm,0) sal*0.6; select ename,empno from mm_emp where commsal+sal*0.06; /error 1.66.找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK), 既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料. select ename,job ,deptno,sal from emp where deptno=10 and job=MANAGER or deptno=20 and job=CLERK or sal =2000 and job not in(MANAGER,CLERK);7.找出收取佣金的员工的不同工作. select distinct job from emp where comm is not null;9.找出各月倒数第3天受雇的所有员工. select ename from emp where to_char(hiredate,DD) = to_char(last_day(hiredate),DD)-3;where hiredate=last_day(hiredate)-3; /error10.找出早于12年前受雇的员工. select ename from emp where (select to_char(sysdate,YYYY) from dual)-to_char(hiredate,YYYY) =12; select empno,hiredate from emp where months_between(sysdate,hiredate)12*12; select * from emp where to_char(hiredate,yyyy)=12 /error11.以首字母大写的方式显示所有员工的姓名. select initcap(ename) ename from emp; 12.显示正好为5个字符的员工的姓名. select ename from emp where length(ename)=5; select ename from emp where ename like _; 13.显示不带有R的员工的姓名. select ename from emp where instr(ename,R,1,1)=0; select ename from emp where ename not like%R%;14.显示所有员工姓名的前三个字符. select ename,substr(ename,1,3) from emp;15.显示所有员工的姓名,用a替换所有A select ename,replace(ename,A,a) from emp;16.显示满10年服务年限的员工的姓名和受雇日期. select ename,to_char(hiredate,YYYY mm dd) r, to_char(sysdate,YYYY mm dd) x from emp where (select to_char(sysdate,YYYY) from dual)-to_char(hiredate,YYYY) =10 select ename,hiredate from emp where hiredateadd_months(sysdate,10*12); /error select ename,hiredate from emp where add_months(hiredate,10*12) sysdate;18.显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面. select ename,to_char(hiredate,YYYY) from emp order by to_char(hiredate,YYYY);20.显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面. select ename,to_char(hiredate,YYYY-mm) from emp order by to_char(hiredate,mm),to_char(hiredate,YYYY) ;21.显示在一个月为30天的情况所有员工的日薪金,忽略余数. select trunc(sal/30) from emp; / sal/360 error select round(sal/30) from emp where to_char(hiredate,mm)=6; /error22.找出在(任何年份的)2月受聘的所有员工. select ename,hiredate from emp where to_char(hiredate,mm) =02; select ename from emp where trunc(hiredate,month)=feb /error select ename,hiredate from emp where to_char(hiredate,MON) =FEB;23.对于每个员工,显示其加入公司的天数. select ename,floor(sysdate - hiredate) from emp; select ename,ceil(sysdate - trunc(hiredate, year) from emp; /24.显示姓名字段的任何位置包含A的所有员工的姓名. select ename from emp where instr(ename,A,1,1) 0 ; select ename from emp where ename like %A%;25.以年月日的方式显示所有员工的服务年限. (大概) select hiredate,sysdate,years,months, abs(trunc(sysdate-add_months(hiredate,years*12+months) days from ( select hiredate,sysdate, trunc(months_between(sysdate, hiredate)/12) years, mod(trunc(months_between( sysdate, hiredate ),12) months from emp )DROP TABLE DEPT;CREATE TABLE DEPT (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,DNAME VARCHAR2(14) ,LOC VARCHAR2(13) ) ;DROP TABLE EMP;CREATE TABLE EMP (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,ENAME VARCHAR2(10),JOB VARCHAR2(9),MGR NUMBER(4),HIREDATE DATE,SAL NUMBER(7,2),COMM NUMBER(7,2),DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);INSERT INTO DEPT VALUES(10,ACCOUNTING,NEW YORK);INSERT INTO DEPT VALUES (20,RESEARCH,DALLAS);INSERT INTO DEPT VALUES(30,SALES,CHICAGO);INSERT INTO DEPT VALUES(40,OPERATIONS,BOSTON);INSERT INTO EMP VALUES(7369,SMITH,CLERK,7902,to_date(17-12-1980,dd-mm-yyyy),800,NULL,20);INSERT INTO EMP VALUES(7499,ALLEN,SALESMAN,7698,to_date(20-2-1981,dd-mm-yyyy),1600,300,30);INSERT INTO EMP VALUES(7521,WARD,SALESMAN,7698,to_date(22-2-1981,dd-mm-yyyy),1250,500,30);INSERT INTO EMP VALUES(7566,JONES,MANAGER,7839,to_date(2-4-1981,dd-mm-yyyy),2975,NULL,20);INSERT INTO EMP VALUES(7654,MARTIN,SALESMAN,7698,to_date(28-9-1981,dd-mm-yyyy),1250,1400,30);INSERT INTO EMP VALUES(7698,BLAKE,MANAGER,7839,to_date(1-5-1981,dd-mm-yyyy),2850,NULL,30);INSERT INTO EMP VALUES(7782,CLARK,MANAGER,7839,to_date(9-6-1981,dd-mm-yyyy),2450,NULL,10);INSERT INTO EMP VALUES(7788,SCOTT,ANALYST,7566,to_date(13-JUL-87)-85,3000,NULL,20);INSERT INTO EMP VALUES(7839,KING,PRESIDENT,NULL,to_date(17-11-1981,dd-mm-yyyy),5000,NULL,10);INSERT INTO EMP VALUES(7844,TURNER,SALESMAN,7698,to_date(8-9-1981,dd-mm-yyyy),1500,0,30);INSERT INTO EMP VALUES(7876,ADAMS,CLERK,7788,to_date(13-JUL-87)-51,1100,NULL,20);INSERT INTO EMP VALUES(7900,JAMES,CLERK,7698,to_date(3-12-1981,dd-mm-yyyy),950,NULL,30);INSERT INTO EMP VALUES(7902,FORD,ANALYST,7566,to_date(3-12-1981,dd-mm-yyyy),3000,NULL,20);INSERT INTO EMP VALUES(7934,MILLER,CLERK,7782,to_date(23-1-1982,dd-mm-yyyy),1300,NULL,10);001SELECT 纳税表.nsrid, 纳税表.nsid, 纳税表.slid, 纳税表.je, 纳税人类型表.lsm, 纳税人基本信息表.nsrid, 纳税人基本信息表.khh, 纳税人基本信息表.tel, 纳税人基本信息表.tel, 纳税人基本信息表.telFROM 纳税人类型表 INNER JOIN (纳税人基本信息表 INNER JOIN 纳税表 ON 纳税人基本信息表.nsrid = 纳税表.nsrid) ON 纳税人类型表.lsid = 纳税人基本信息表.lsidORDER BY 纳税人类型表.lsm, 纳税人基本信息表.nsrid, 纳税人基本信息表.khh DESC , 纳税人基本信息表.tel DESC , 纳税人基本信息表.tel, 纳税人基本信息表.tel DESC;SELECT 纳税人基本信息表.nsrid, 纳税人基本信息表.lsid, 纳税人基本信息表.addFROM 纳税人基本信息表WHERE (nsrid)000001111100006);1建立Part CREATE TABLE Part ( ID smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED, Color varchar(20), Name varchar(20) NOT NULL, Weight int DEFAULT 0, Intro text );2建立Provider CREATE TABLE Provider ( ID smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED, Name varchar(20) NOT NULL, Password varchar(8) NOT NULL, Address varchar(30), Tel varchar(20), Intro text ) ; 3 建立Customer表 CREATE TABLE Customer ( ID smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED, Name varchar(20) NOT NULL, Address varchar(30), Tel varchar(20), ) ; 4 建立Supply表 CREATE TABLE Supply ( PartID smallint, ProviderID smallint, Price int, Quantity int, CONSTRAINT PK_SUPPLY PRIMARY KEY CLUSTERED (PartID,ProviderID) CONSTRAINT FK_SUPPLY_PARTID FOREIGN KEY(PartID) REFERENCES Part(ID), CONSTRAINT FK_SUPPLY_PROVIDERID FOREIGN KEY(ProviderID) REFERENCES Provider(ID) ) ; 5 建立After表 CREATE TABLE After ( CustomerID smallint, PartID smallint, Price int, Quantity int, CONSTRAINT PK_AFTER PRIMARY KEY CLUSTERED (CustomerID,PartID), CONSTRAINT FK_AFTER_CUSTOMERID FOREIGN KEY(CustomerID) REFERENCES Customer(ID), CONSTRAINT FK_AFTER_PARTID FOREIGN KEY(PartID) REFERENCES Part(ID) ) ; 6建立Business表 CREATE TABLE Business ( CustomerID smallint, ProviderID smallint, PartID smallint, Price int, Quantity int, CONSTRAINT PK_BUSINESS PRIMARY KEY CLUSTERED (CustomerID,ProviderID,PartID), CONSTRAINT FK_BUSINESS_CUSTOMERID FOREIGN KEY(CustomerID) REFERENCES Customer(ID), CONSTRAINT FK_BUSINESS_PROVIDERID FOREIGN KEY(ProviderID) REFERENCES Provider(ID), CONSTRAINT FK_BUSINESS_PARTID FOREIGN KEY(PartID) REFERENCES Part(ID), ) ; 7供应商操作 1注册（Register） INSERT INTO Provider(Name,Address,Tel,Intro) VALUES(#Name,#Address,#Tel,#Intro) 2 UnRegister DELETE Provider WHERE (ID=#ID); 3 Update UPDATE Provider Set(Name=#Name,Address=#Address,Tel=#Tel,Intro=#Intro) WHERE(ID=#ID); 4 Add_Supply_Item INSERT INTO Supply (PartID,ProviderID,Price,Quantity) VALUES(#PartID,#ProviderID,#Price,#Quantity); 5 Delete_Supply_Item DELETER Supply WHERE(PartID=#PartID AND ProviderID=#ProviderID); 6 Update_Supply_Item UPDATE Supply SET(Price=#Price,Quantity=#Quantity) WHERE(PartID=#PartID AND ProviderID=#ProviderID);8 顾客 1 Register INSERT INTO Customer(Name,Address,Tel) VALUES (#Name,#Address,#Tel); 2 UnREgister DELETER Customer WHERE (ID=#ID); 3 Update UPDATE Customer Set(Name=#Name,Address=#Address,Tel=#Tel) WHERE (ID=#ID); 4 Add_After_Item INSERT INTO After(PartID,CustomerID,Price,Quantity) VALUES (#PartID,#CustomerID,#Price,#Quantity); 5 Delete_After_Item DELETE After WHERE (PartID=#PartID AND CustomerID=#CustomerID); 6 Update_After_Item UPDATE After SET(Price=#Price,Quantity=#Quantity) WHERE (PartID=#PartID AND CustomerID=#CustomerID);9 交易员 CREATE TABLE Agreement ( CustomerID smallint, ProviderID smallint, PartID smallint, Price int, Quantity int, CustomerSign int, ProviderSign int, CONSTRAINT PK_AGREEMENT PRIMARY KEY CLUSTERED(CustomerID,ProviderID,PartID),CONSTRAINT FK_AGREEMENT_CUSTOMERID FOREIGN KEY (CustomerID) REFERENCES Customer(ID) , CONSTRAINT FK_AGREEMENT_PROVIDERID FOREIGN KEY (ProviderID) REFERENCES Provider(ID),CONSTRAINT FK_AGREEMENT_PARTID FOREIGN KEY (PartID) REFERENCES Part(ID) ); CREATE PROC PASS_AGREEMENT