第一篇：SQL练习题及答案1
SQL练习题：商品销售数据库
商品销售数据库
Article(商品号 char(4)，商品名char(16)，单价 Numeric(8,2)，库存量 int)Customer(顾客号char(4)，顾客名 char(8)，性别 char(2)，年龄 int)OrderItem(顾客号 char(4)，商品号 char(4)，数量 int, 日期 date)
1.用SQL建立三个表，须指出该表的实体完整性和参照完整性，对性别和年龄指出用户自定义的约束条件。（性别分成男女，年龄从10到100）。顾客表的数据用插入语句输入数据，其它两表可用任意方式输入数据。
create table OrderItem(顾客号 char(4),商品号 char(4),日期 datetime,数量 smallint，primary key(顾客号,商品号,日期)，foreign key(商品号)references Article(商品号), foreign key(顾客号)references Custommer(顾客号));
2.检索定购商品号为„0001‟的顾客号和顾客名。
select distinct 顾客号,顾客名from OrderItem where 商品号='0001'
3.检索定购商品号为„0001‟或„0002‟的顾客号。
select distinct 顾客号 from OrderItem where 商品号='0001' or 商品号='0002';
4.检索至少定购商品号为„0001‟和„0002‟的顾客号。
select 顾客号 from OrderItem where 商品号='0001' and 顾客号 in(select 顾客号 from OrderItem where 商品号='0002');
5.检索至少定购商品号为„0001‟和„0002‟的顾客号。(用自表连接方法)
select X.顾客号 from OrderItem X,OrderItem Y
where X.顾客号=Y.顾客号 and X.商品号='0001' and Y.商品号='0002';
6.检索没定购商品的顾客号和顾客名。
select 顾客号,顾客名 from Custommer where 顾客号 not in(select 顾客号 from OrderItem);
7.检索一次定购商品号„0001‟商品数量最多的顾客号和顾客名。
select 顾客号,顾客名 from Custommer where 顾客号 in(select 顾客号 from OrderItem where 商品号='0001'and 数量=(select MAX(数量)from OrderItem where 商品号='0001'));
8.检索男顾客的人数和平均年龄。
select count(*)人数,avg(年龄)平均年龄 from Custommer where 性别='男';
9.检索至少订购了一种商品的顾客数。
select count(distinct 顾客号)from OrderItem;
10.检索订购了商品的人次数。select count(顾客号)from OrderItem;select count(distinct 顾客号)from OrderItem;11.检索顾客张三订购商品的总数量及每次购买最多数量和最少数量之差。
select sum(数量),MAX(数量)-MIN(数量)from OrderItem,Custommer where OrderItem.顾客号=Custommer.顾客号 and 顾客名='张三';.检索至少订购了3单商品的顾客号和顾客名及他们定购的商品次数和商品总数量，并按商品总数量降序排序。
select Custommer.顾客号,顾客名,count(*),Sum(数量)from OrderItem,Custommer where OrderItem.顾客号=Custommer.顾客号 group by Custommer.顾客号,顾客名 having count(*)>3 order by 4 desc;
13.检索年龄在30至40岁的顾客所购买的商品名及商品单价。
select 商品名,单价 from Custommer,Article,OrderItem where Custommer.顾客号=OrderItem.顾客号 and Article.商品号=OrderItem.商品号 and 年龄 between 30 and 40;
14.创建一个视图GM，字段包括：顾客号，顾客名和定购的商品名，日期和金额（金额=数量*单价）。指定用内连接方式做。
create view GM as select Custommer.顾客号,顾客名,商品名,日期,单价*数量 as 金额 from Custommer,Article,OrderItem where Custommer.顾客号=OrderItem.顾客号 and Article.商品号=OrderItem.商品号
create view GM1 as select Custommer.顾客号,顾客名,商品名,日期,单价*数量 as 金额
from(Custommer inner join OrderItem on Custommer.顾客号=OrderItem.顾客号)inner join Article on Article.商品号=OrderItem.商品号
15.检索购买的商品的单价至少有一次高于或等于1000元的顾客号和顾客名。
select Custommer.顾客号,顾客名 from Custommer,OrderItem,Article
where Custommer.顾客号=OrderItem.顾客号 and Article.商品号=OrderItem.商品号 and 单价>1000
16.检索购买的购买价都高于或等于1000元的顾客号和顾客名。
select Custommer.顾客号,顾客名 from Custommer where 顾客号 in(select 顾客号 from OrderItem where 顾客号 not in(select 顾客号 from OrderItem,Article
where OrderItem.商品号=Article.商品号 and 单价<=1000))
17.检索女顾客购买的商品号，商品名和数量合计。
select Article.商品号,商品名,sum(数量)from Custommer,Article,OrderItem where OrderItem.顾客号=Custommer.顾客号 and OrderItem.商品号=Article.商品号
and 性别='女' group by Article.商品号,商品名
18.检索所有的顾客号和顾客名以及它们所购买的商品号。（包括没买商品的顾客）
select Custommer.顾客号,顾客名,商品号
from Custommer left join OrderItem on Custommer.顾客号=OrderItem.顾客号 18.检索所有的顾客号和顾客名以及它们所购买的商品号。（包括没买商品的顾客）
select Custommer.顾客号,顾客名 from Custommer where not exists(select * from Article where not exists(select * from OrderItem
where OrderItem.顾客号=Custommer.顾客号 and OrderItem.商品号=Article.商品号))
20.检索这样的顾客号，他们至少订购了顾客号为“0002”所订购的所有商品（除法）
select distinct 顾客号 from OrderItem X where not exists(select * from OrderItem Y where 顾客号='0002' and not exists(select * from OrderItem Z where Z.顾客号=X.顾客号 and Z.商品号=Y.商品号))
21.向Article表插入一条纪录。删除无人购买的商品。（检验一下刚插入的记录是否已被删除）
delete from Article where 商品号 not in(select 商品号 from OrderItem)
22.降低已售出的数量总合超过10件的商品单价为原价的95%。
update Article set 单价=单价*0.95 where 商品号 in
(select 商品号 from OrderItem group by 商品号 having sum(数量)>10)
23.建立断言：顾客的年龄必须大于18岁。
Create ASSERTION A1 check(not exists(select * from Custommer where 年龄<=18))
24.把修改商品单价的权限授给用户Wang, 用户Wang可以转授该权限。
Grant update(单价)on Article to Wang with grant option
25.把修改商品单价的权限用户Wang收回，转授出去的也级联收回。
revoke update(单价)on Article from Wang cascade
第二篇：数据库sql课后练习题及答案解析
先创建下面三个表：
（book表）
（borrow表）
（reader表）
1)找出姓李的读者姓名(NAME)和所在单位(COMPANY)。
2)列出图书库中所有藏书的书名(BOOK_NAME)及出版单位(OUTPUT)。3)查找“高等教育出版社”的所有图书名称(BOOK_NAME)及单价(PRICE)，结果按单价降序排序。
4)查找价格介于10元和20元之间的图书种类(SORT)，结果按出版单位(OUTPUT)和单价(PRICE)升序排序。
5)查找书名以”计算机”开头的所有图书和作者(WRITER)。
6)检索同时借阅了总编号(BOOK_ID)为112266和449901两本书的借书证号(READER_ID)。
##7)* 查找所有借了书的读者的姓名(NAME)及所在单位(COMPANY)。8)* 找出李某所借所有图书的书名及借书日期(BORROW_DATE)。
9)* 无重复地查询2006年10月以后借书的读者借书证号(READER_ID)、姓名和单位。
##10)* 找出借阅了
11)找出与”赵正义”在同一天借书的读者姓名、所在单位及借书日期。12)查询2006年7月以后没有借书的读者借书证号、姓名及单位。#13)求”科学出版社”图书的最高单价、最低单价、平均单价。##14)* 求”信息系”当前借阅图书的读者人次数。
#15)求出各个出版社图书的最高价格、最低价格和总册数。#16)分别找出各单位当前借阅图书的读者人数及所在单位。
17)* 找出当前至少借阅了2本图书(大于等于2本)的读者姓名及其所在单位。18)分别找出借书人次数多于1人次的单位及人次数。
19)找出藏书中各个出版单位的名称、每个出版社的书籍的总册数(每种可能有多册)、书的价值总额。
20)查询经济系是否还清所有图书。如果已经还清，显示该系所有读者的姓名、所在单位和职称。
附录：建表语句
创建图书管理库的图书、读者和借阅三个基本表的表结构： 创建BOOK:（图书表）
CREATE TABLE BOOK(BOOK_ID int, SORT VARCHAR(10), BOOK_NAME VARCHAR(50), WRITER VARCHAR(10), OUTPUT VARCHAR(50), PRICE int);
创建READER:（读者表）
CREATE TABLE READER(READER_ID int, COMPANY VARCHAR(10), NAME VARCHAR(10), SEX VARCHAR(2), GRADE VARCHAR(10), ADDR VARCHAR(50));
创建BORROW:（借阅表）
CREATE TABLE BORROW(READER_ID int, BOOK_ID int, BORROW_DATE datetime)
插入数据: BOOK表: insert into BOOK values(445501,'TP3/12','数据库导论','王强','科学出版社',17.90);insert into BOOK values(445502,'TP3/12','数据库导论','王强','科学出版社',17.90);insert into BOOK values(445503,'TP3/12','数据库导论','王强','科学出版社',17.90);insert into BOOK values(332211,'TP5/10','计算机基础','李伟','高等教育出版社',18.00);insert into BOOK values(112266,'TP3/12','FoxBASE','张三','电子工业出版社',23.60);insert into BOOK values(665544,'TS7/21','高等数学','刘明','高等教育出版社',20.00);insert into BOOK values(114455,'TR9/12','线性代数','孙业','北京大学出版社',20.80);insert into BOOK values(113388,'TR7/90','大学英语','胡玲','清华大学出版社',12.50);insert into BOOK values(446601,'TP4/13','数据库基础','马凌云','人民邮电出版社',22.50);insert into BOOK values(446602,'TP4/13','数据库基础','马凌云','人民邮电出版社',22.50);insert into BOOK values(446603,'TP4/13','数据库基础','马凌云','人民邮电出版社',22.50);insert into BOOK values(449901,'TP4/14','FoxPro大全','周虹','科学出版社',32.70);insert into BOOK values(449902,'TP4/14','FoxPro大全','周虹','科学出版社',32.70);insert into BOOK values(118801,'TP4/15','计算机网络','黄力钧','高等教育出版社',21.80);insert into BOOK values(118802,'TP4/15','计算机网络','黄力钧','高等教育出版社',21.80);
READER表: insert into reader values(111,'信息系','王维利','女','教授','1号楼424');insert into reader values(112,'财会系','李 立','男','副教授','2号楼316');insert into reader values(113,'经济系','张 三','男','讲师','3号楼105');insert into reader values(114,'信息系','周华发','男','讲师','1号楼316');insert into reader values(115,'信息系','赵正义','男','工程师','1号楼224');insert into reader values(116,'信息系','李 明','男','副教授','1号楼318');insert into reader values(117,'计算机系','李小峰','男','助教','1号楼214');insert into reader values(118,'计算机系','许鹏飞','男','助工','1号楼216');insert into reader values(119,'计算机系','刘大龙','男','教授','1号楼318');insert into reader values(120,'国际贸易','李 雪','男','副教授','4号楼506');insert into reader values(121,'国际贸易','李 爽','女','讲师','4号楼510');insert into reader values(122,'国际贸易','王 纯','女','讲师','4号楼512');insert into reader values(123,'财会系','沈小霞','女','助教','2号楼202');insert into reader values(124,'财会系','朱 海','男','讲师','2号楼210');insert into reader values(125,'财会系','马英明','男','副教授','2号楼212');
BORROW表: insert into borrow values(112,445501,'3-19-2006');insert into borrow values(125,332211,'2-12-2006');insert into borrow values(111,445503,'8-21-2006');insert into borrow values(112,112266,'3-14-2006');insert into borrow values(114,665544,'10-21-2006');insert into borrow values(120,114455,'11-2-2006');insert into borrow values(120,118801,'10-18-2006');insert into borrow values(119,446603,'11-12-2006');insert into borrow values(112,449901,'10-23-2006');insert into borrow values(115,449902,'8-21-2006');insert into borrow values(118,118801,'9-10-2006');
现有关系数据库如下：
数据库名：图书借阅管理系统
读者表(读者编号 char(6)，姓名，性别，年龄，单位，身份证号，职称)图书表(图书编号char(6)，图书名称，出版社，作者)借阅表(读者编号，图书编号，借阅时间)用SQL语言实现下列功能的sql语句代码。（1）创建数据表book；
book表(图书编号 char(6)，图书名称，出版社，作者)要求使用：主键(图书编号)、非空(图书名称)，非空（作者）（2）创建数据表reader表；
学生信息表(reader编号 char(8)，姓名，性别，年龄，工作单位，身份证号，职称)要求使用：主键(reader编号)、默认(职称)、非空(工作单位，姓名)、唯一(身份证号)、检查(性别)，检查（年龄）（3）创建借书表borrow；
borrow(ID,读者编号，图书编号，借书日期)要求使用：外键(学号，课号)（4）将下列课程信息添加到book表的代码
图书编号 名称
100101 数据库原理 100102 数据结构
修改 课号为100102的图书名称：数据结构与算法 删除 课号为100101的图书信息（5）创建视图读者借书信息的代码；
读者借书信息视图(读者编号，姓名，图书编号，图书名称，借书日期)（6）从读者表book中查询姓李的女读者的情况：姓名、性别、工作单位。（7）查询统计出借书量超过5本的单位平均年龄
（8）创建带参数的存储过程[借某图书的读者高低均年龄]、执行该过程的代码
存储过程功能：查询借阅某图书的的读者最高年龄、最低年龄、平均年龄；
执行该过程，查询所有借阅’数据库基础’这本书的读者的最高年龄、最低年龄、平均年龄；
1)找出姓李的读者姓名(NAME)和所在单位(COMPANY)。
SELECT NAME,COMPANY FROM READER WHERE NAME LIKE '李%'
2)列出图书库中所有藏书的书名(BOOK_NAME)及出版单位(OUTPUT)。
SELECT BOOK_NAME, OUTPUT FROM BOOK
3)查找“高等教育出版社”的所有图书名称(BOOK_NAME)及单价(PRICE)，结果按单价降序排序。
SELECT BOOK_NAME,PRICE FROM BOOK WHERE OUTPUT='高等教育出版社' ORDER BY PRICE DESC 4)查找价格介于10元和20元之间的图书种类(SORT)，结果按出版单位(OUTPUT)和单价(PRICE)升序排序。
SELECT SORT,OUTPUT,PRICE FROM BOOK WHERE PRICE BETWEEN 10 AND 20 ORDER BY OUTPUT ,PRICE
5)查找书名以”计算机”开头的所有图书和作者(WRITER)。
SELECT BOOK_NAME,WRITER FROM BOOK WHERE BOOK_NAME LIKE '计算机%' 6)检索同时借阅了总编号(BOOK_ID)为112266和449901两本书的借书证号(READER_ID)。
SELECT A.READER_ID FROM BORROW A,BORROW B WHERE A.BOOK_ID='112266' AND B.BOOK_ID='449901' AND A.READER_ID=B.READER_ID
##7)* 查找所有借了书的读者的姓名(NAME)及所在单位(COMPANY)。
SELECT DISTINCT NAME,COMPANY FROM READER,BORROW WHERE BORROW.READER_ID=READER.READER_ID
8)* 找出李某所借所有图书的书名及借书日期(BORROW_DATE)。
SELECT BOOK.BOOK_NAME,BORROW.BORROW_DATE FROM BOOK,BORROW,READER WHERE BOOK.BOOK_ID=BORROW.BOOK_ID AND
READER.READER_ID=BORROW.READER_ID AND READER.NAME LIKE '李%'
9)* 无重复地查询2006年10月以后借书的读者借书证号(READER_ID)、姓名和单位。
SELECT READER_ID,NAME,COMPANY FROM READER WHERE READER_ID IN(SELECT READER_ID FROM BORROW WHERE BORROW_DATE >='2006-10-1')
##10)* 找出借阅了
SELECT DISTINCT READER_ID FROM BORROW,BOOK WHERE BORROW.BOOK_ID=BOOK.BOOK_ID AND BOOK.BOOK_NAME LIKE 'FoxPro大全'
11)找出与”赵正义”在同一天借书的读者姓名、所在单位及借书日期。
SELECT NAME,COMPANY,BORROW_DATE FROM READER,BORROW WHERE BORROW.READER_ID=READER.READER_ID AND BORROW_DATE IN(SELECT BORROW_DATE FROM BORROW,READER WHERE BORROW.READER_ID=READER.READER_ID AND NAME='赵正义')
12)查询2006年7月以后没有借书的读者借书证号、姓名及单位。
SELECT READER_ID,NAME,COMPANY FROM READER WHERE READER_ID NOT IN(SELECT READER_ID FROM BORROW WHERE BORROW_DATE>='2006-7-1')
#13)求”科学出版社”图书的最高单价、最低单价、平均单价。
SELECT MAX(PRICE)最高单价,MIN(PRICE)最低单价,AVG(PRICE)平均单价 FROM BOOK WHERE OUTPUT='科学出版社'
##14)* 求”信息系”当前借阅图书的读者人次数。
SELECT COUNT(DISTINCT READER_ID)当前借阅图书的读者人次数FROM BORROW WHERE READER_ID IN(SELECT READER_ID FROM READER WHERE COMPANY IN('信息系'))
#15)求出各个出版社图书的最高价格、最低价格和总册数。
SELECT MAX(PRICE)最高价格,MIN(PRICE)最低价格,COUNT(PRICE)总册数FROM BOOK GROUP BY OUTPUT
#16)分别找出各单位当前借阅图书的读者人数及所在单位。
SELECT COUNT(COMPANY)读者人数,COMPANY FROM BORROW,READER WHERE BORROW.READER_ID=READER.READER_ID GROUP BY COMPANY
17)* 找出当前至少借阅了2本图书(大于等于2本)的读者姓名及其所在单位。
SELECT NAME 读者姓名,COMPANY 单位FROM READER WHERE READER_ID IN(SELECT READER_ID FROM BORROW GROUP BY READER_ID HAVING COUNT(*)>=2)
18)分别找出借书人次数多于1人次的单位及人次数。
SELECT COMPANY,COUNT(*)人次数 FROM READER,BORROW WHERE BORROW.READER_ID=READER.READER_ID GROUP BY COMPANY HAVING COUNT(*)>1
19)找出藏书中各个出版单位的名称、每个出版社的书籍的总册数(每种可能有多册)、书的价值总额。
SELECT OUTPUT,COUNT(*)书籍的总册数,SUM(PRICE)书的价值总额 FROM BOOK GROUP BY OUTPUT
20)查询经济系是否还清所有图书。如果已经还清，显示该系所有读者的姓名、所在单位和职称。
SELECT NAME,COMPANY FROM READER WHERE NOT EXISTS(SELECT* FROM READER,BORROW WHERE READER.READER_ID=BORROW.READER_ID AND COMPANY='经济系')
第三篇：SQL练习题3
1查询陈刚和高宏的完整的销售记录，要求按照销售员分类汇总，有商品名称，卖出总价格，卖出总数量，商品库存量，筛选出卖出总价格大于10000的数据，按照销售员排序。
2在商品一览表中按货名分类统计同类商品的总数量及平均价格。
SELECT 总数量=SUM(库存量),平均价格=AVG(参考价格),货名 FROM 商品一览表 GROUP BY 货名
3在“进货表”中按“供货商ID”分类统计从各厂家进货的次数、总数量及进货总价格。SELECT 供货商ID,COUNT(供货商ID),SUM(数量),SUM(数量*进价)FROM 进货表 GROUP BY 供货商ID
4按货号分类统计“销售表”中各种商品的销售总数量、平均价格、最高价、最低价以及销售总金额。
SELECT 销售总数量=SUM(数量),平均价格=AVG(单价),最高价=MAX(单价),最低价=MIN(单价),销售总金额=SUM(金额),货号 FROM 销售表 GROUP BY 货号
5按货名分类统计“销售表”中不包括计算机整机的各种商品的销售总数量、平均价格以及销售总金额。
SELECT销售总数量=SUM(数量),平均价格=AVG(单价),销售总金额=SUM(金额),货名 FROM 销售表WHERE 货名 <>计算机 GROUP BY 货名
6按货号分类统计“销售表”中销售总量大于10的商品销售总数量、平均价格以及销售总金额。
7在“销售表”中按客户名称分类统计各客户的购货总数量、单笔最大量、单笔最小量、平均价格及购货总金额。
8在“销售表”中按客户名称和所购商品种类组合分类，统计各客户同一类商品的购货总数量、平均价、最高价、最低价及购货总金额。
9在“销售表”中按日期分类统计每天的商品日销售量、单笔最大金额、单笔最小金额和每日总销售额
10在“销售表”中分类统计各销售员的业绩：销售数量、单笔最大数量、单笔最大金额、三个月平均日 营业额、总营业额，并按总营业额降序排序。
11在“进货表”中按“货号”“进价”分类统计相同货号不同价格的进货次数和进货数量。
12求所卖出商品的毛利润。（销售总金额-总成本），成本可以理解为平均进价*数量。
13求每个销售员所创造的利润.14查询每种货物的进价小于平均进价的供货商信息。
SELECT * FROM 供货商表 WHERE 供货商ID=(SELECT 供货商ID FROM 进货表 S JOIN 商品一览表 XON S.货号=X.货号 WHERE 进价<平均进价)
第四篇：sql查询练习题含答案
--（1）查询20号部门的所有员工信息。select * from emp e where e.deptno=20;
--（2）查询奖金（COMM）高于工资（SAL）的员工信息。select * from emp where comm>sal;--（3）查询奖金高于工资的20%的员工信息。select * from emp where comm>sal*0.2;--（4）查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息。select * from emp e
where(e.deptno=10 and e.job='MANAGER')or(e.deptno=20 and e.job='CLERK')--（5）查询所有工种不是MANAGER和CLERK，--且工资大于或等于2000的员工的详细信息。select * from emp
where job not in('MANAGER','CLERK')and sal>=2000;
--（6）查询有奖金的员工的不同工种。select * from emp where comm is not null;--（7）查询所有员工工资和奖金的和。select(e.sal+nvl(e.comm,0))from emp e;--（8）查询没有奖金或奖金低于100的员工信息。select * from emp where comm is null or comm<100;--（9）查询员工工龄大于或等于10年的员工信息。
select * from emp where(sysdate-hiredate)/365>=10;
--（10）查询员工信息，要求以首字母大写的方式显示所有员工的姓名。select initcap(ename)from emp;select upper(substr(ename,1,1))||lower(substr(ename,2))from emp;
--（11）显示所有员工的姓名、入职的年份和月份，按入职日期所在的月份排序，--若月份相同则按入职的年份排序。
select ename,to_char(hiredate,'yyyy')year,to_char(hiredate,'MM')month from emp order by month,year;--（12）查询在2月份入职的所有员工信息。select * from emp where to_char(hiredate,'MM')='02'--（13）查询所有员工入职以来的工作期限，用“**年**月**日”的形式表示。
select e.ename,floor((sysdate-e.hiredate)/365)||'年' ||floor(mod((sysdate-e.hiredate),365)/30)||'月' ||floor(mod(mod((sysdate-e.hiredate),365),30))||'日' from emp e;--（14）查询从事同一种工作但不属于同一部门的员工信息。select a.ename,a.job,a.deptno,b.ename,b.job,b.deptno from emp a,emp b where a.job=b.job and a.deptno<>b.deptno;
--（15）查询各个部门的详细信息以及部门人数、部门平均工资。select d.deptno,count(e.empno),avg(e.sal),d.dname,d.loc from emp e ,dept d where e.deptno=d.deptno group by d.deptno,d.dname,d.loc
--（16）查询10号部门员工以及领导的信息。select * from emp where empno in(select mgr from emp where deptno=10)or deptno=10;
--（17）查询工资为某个部门平均工资的员工信息。select * from emp where sal in(select avg(sal)from emp group by deptno);
--（18）查询工资高于本部门平均工资的员工的信息。select * from emp e1
where sal >(select avg(sal)from emp e2 where e2.deptno=e1.deptno);
--（19）查询工资高于本部门平均工资的员工的信息及其部门的平均工资。select e.*,a.avgsal from emp e,(select deptno,avg(sal)as avgsal from emp group by deptno)a where a.deptno=e.deptno and e.sal>a.avgsal;
--（20）统计各个工种的人数与平均工资。
select count(*),e.job,avg(e.sal)from emp e group by e.job
--（21）统计每个部门中各个工种的人数与平均工资。select deptno,job,count(empno),avg(sal)from emp e group by e.deptno,e.job--（22）查询所有员工工资都大于1000的部门的信息。select * from dept where deptno in(select deptno from emp where deptno not in
(select distinct deptno from emp where sal<1000));
--（23）查询所有员工工资都大于1000的部门的信息及其员工信息。select * from emp e join dept d on d.deptno in(select deptno from emp where deptno not in
(select distinct deptno from emp where sal<1000))and d.deptno=e.deptno;
--（24）查询所有员工工资都在900~3000之间的部门的信息。select * from dept where deptno not in(select deptno from emp
where sal not between 900 and 3000);--（25）查询所有工资都在900~3000之间的员工所在部门的员工信息。select * from emp a where a.deptno in(select distinct e.deptno from emp e where e.sal between 900 and 3000);
--（26）查询每个员工的领导所在部门的信息。select d.* from dept d where d.deptno in(select distinct e2.deptno from emp e1,emp e2 where e1.empno=e2.mgr);--（27）查询人数最多的部门信息。select * from dept where deptno in(select deptno from(select count(*)count,deptno from emp group by deptno)where count in(select max(count)
from(select count(*)count ,deptno from emp group by deptno)));
--（28）查询30号部门中工资排序前3名的员工信息。
select * from
(select sal from emp where deptno=30 order by sal desc)e where rownum<4
--（29）查询'JONES'员工及所有其直接、间接下属员工的信息。select e.* from emp e start with ename='JONES' connect by prior empno=mgr;
---（30）查询SCOTT员工及其直接、间接上级员工的信息。select e.* from emp e start with ename='SCOTT' connect by prior mgr=empno;
--（31）以树状结构查询所有员工与领导之间的层次关系。select substr(sys_connect_by_path(ename,'->'),3),level from emp start with mgr is null connect by prior empno=mgr;
--（32）向emp表中插入一条记录，员工号为1357，员工名字为oracle，--工资为2050元，部门号为20，入职日期为2002年5月10日。
--（33）将各部门员工的工资修改为该员工所在部门平均工资加1000。update emp e set sal= 1000+(select avg(sal)from emp where deptno=e.deptno);
--(34)查询工作等级为2级，1985年以后入职的工作地点为DALLAS的员工编号、--姓名和工资。
select e.ename,e.empno,e.sal from emp e,salgrade s,dept d where(e.sal between s.losal and s.hisal)and(s.grade=2)
and to_char(e.hiredate,'yyyy')>1985 and e.deptno=d.deptno and d.loc='DALLAS';
--35.部门平均薪水最高的部门编号
select * from（select avg(sal)avgsal,deptno
from emp group by deptno order by avgsal desc)
where rownum=1;
select deptno,avg(sal)from emp group by deptno having avg(sal)=（select max(avg(sal))avgsal
from emp group by deptno)
--36,部门平均薪水最高的部门名称
select d.* from dept d where deptno in（select deptno from emp group by deptno having avg(sal)=（select max(avg(sal))avgsal
from emp group by deptno))
--37.平均薪水最低的部门的部门名称
select d.* from dept d where deptno in（select deptno from emp group by deptno having avg(sal)=（select min(avg(sal))avgsal
from emp group by deptno))
--38.平均薪水等级最低的部门的部门名称 select d.dname from dept d where d.deptno in(select a.deptno from
(select e.deptno from emp e,salgrade s where(e.sal between s.losal and s.hisal)group by e.deptno order by avg(s.grade))a where rownum=1);
--39.部门经理人中，薪水最低的部门名称 select dname from dept where deptno=(select deptno from
(select deptno from emp where job='MANAGER' group by deptno order by min(sal))where rownum=1)
--40.比普通员工的最高薪水还要高的经理人名称 select ename from emp where sal>(select max(sal)from emp where job not in('MANAGER','PRESIDENT'))and job='MANAGER' or job='PRESIDENT'
--41.删除重复部门，但是留下一项
insert into dept values(70,'RESEARCH','DALLAS')select deptno,dname,rowid from dept
delete from dept d where rowid<>(select min(rowid)from dept where dname=d.dname and d.loc=loc)
--42.更新员工工资为他的主管的工资,奖金
update emp e set sal=(select sal from emp where empno=e.mgr), comm=(select comm from emp where empno=e.mgr)
update emp e set(sal,comm)=(select sal,comm from emp where empno=e.mgr)rollback;select * from emp;
第五篇：sql作业题答案
1.检索报名人数大于平均报名人数的课程名称和教师
selectcouname,teacher from course where willnum<(select avg(willnum)from course)2.检索班级号为20000003的学生的学号、姓名、班级代码、班级名称 selectstuno,stuname,student.classno,classname from student join class on class.classno=student.classno wherestudent.classno='20000003' 3.检索学生选课为第1志愿（WILLORDER）的学号、姓名、课程号、课程名称 selectstucou.stuno,stuname,stucou.couno,couname from student join stucou on student.stuno=stucou.stuno join course on stucou.couno=course.couno wherewillorder=1 4.按班级显示学生信息，并计算每班学生人数。显示要求格式如下：
select '班级编码'=student.classno,'班级名称'=classname,'学号'=stuno,'姓名'=stuname,'密码'=pwd from student join class on student.classno=class.classno order by student.classno compute count(student.classno)by student.classno 5.显示“00建筑管理”班级的系部、班级和学生信息，并计算班级人数。要求显示格式如下：
select class.departno as '系部编码',departname as '系部名称',student.classno as '班级编码',classname as '班级名称', student.stuno as '学号',stuname as '姓名',pwd as '选课密码' from student join class on class.classno=student.classno join department on department.departno=class.departno where class.classname='00建筑管理' compute count(student.classno)6.检索陈金菊的个人信息，包括她选修的课程。select student.*,course.*
from student join stucou on student.stuno=stucou.stuno join course on course.couno=stucou.couno where stuname='陈金菊' 7.检索“00建筑管理”班有哪些学生。select student.*,classname from student join class on student.classno=class.classno where classname='00建筑管理' 8.显示计算机应用工程系的学生信息和系别名称，并统计计算机应用工程系有多少学生。select student.*,departname from student join class on student.classno=class.classno join department on department.departno=class.departno where departname='计算机应用工程系' compute count(stuno)
点击咨询 