============================================================
1、创建表myemp和emp表具有相同的结构和记录。
create table myemp as select * from emp;
2、给myemp的empno列添加主建约束。
alter table myemp add constraint pk primary key(empno);
3、给myemp添加一条记录。
insert into myemp values(1,'a','ab',999999,to_date('2008-9-9','yyyy-mm-dd'),5000,1000,30);
4、给myemp添加一条记录只有empno,ename,mgr,sal,deptno有值,其他列为空。
insert into myemp(empno,ename,mgr,sal,deptno) values(20,'aa',30,999999,20);
5、显示所有薪金高于公司均薪金的人。
select ename 姓名, sal 薪金
from emp
where sal >
(select avg(sal) from emp);
6、显示所有薪金高于各自部门平均薪金的人。 关联子查询*******
select e.ename , e.deptno, e.sal from emp e where e.sal>(select avg(sal) from emp p where e.deptno=p.deptno);
7、给所有10部门的经理(MANAGER)和20部门的职员(CLERK),增加薪金10%。
update emp set sal=sal*(1+0.1) where (deptno=10 and upper(job)='MANAGER') or(deptno=20 and upper(job)='CLERK' )
8、使用EMP,DEPT表显示所有销售部'SALES'的员工。
select * from emp,dept
where emp.deptno=dept.deptno and upper(emp.job)='SALES';
9、删除DEPT中没有员工的部门。
delete from dept where deptno not in( select deptno from emp);
10、显示所有的部门信息和人数。******
//说明:当部门里没有人时也要显示,即把所有的部门显示。所有在emp端加上‘+’
select dept.* , (select count(ename) from emp where emp.deptno(+)=dept.deptno) 人数 from dept;
11、删除雇佣年限低于20年的员工。
delete from emp where where trunc(sysdate-hiredate)<365*20;
12、显示各部门员工薪金最高的前2名//?????????????????
select b.ename,b.deptno,b.sal from emp b
where sal >= (
select a.sal from ( select ename,deptno,sal from emp order by deptno,sal desc) a
where a.deptno=b.deptno and rownum <=2
minus
select a.sal from ( select ename,deptno,sal from emp order by deptno,sal desc) a
where a.deptno=b.deptno and rownum <=1
)
order by b.deptno,b.sal desc;
13、显示薪金最高的3位员工
select ename,sal ,rownum
from (select ename,sal from emp order by sal desc)
where rownum<=3;
14、为EMP的各字段建立必要的索引,使得如下查询能使用上索引
select * from emp where ename like 'S%';
创建索引: create index I_NAME on table emp(ename);
---------------------------------------------------------------------------------------
select * from emp where job='MANAGER';
创建索引: create index I_JOB on table emp(job);
---------------------------------------------------------------------------------------
select * from emp where hiredate>to_date('1982','yyyy');
创建索引: create index I_HIREDATE on table emphiredate);
---------------------------------------------------------------------------------------
============================================================
现有一个商店的数据库,记录顾客及其购物情况,由下面三个表组成:
商品product(商品号productid,商品名productname,单价unitprice,商品类别category,供应商provider,上架日期startdate);
顾客customer(顾客号customerid,姓名name,住址location);
购买purchase(顾客号customerid,商品号productid,购买数量quantity);
试用SQL语言完成下列功能:
1.建表:在定义中要求声明:
(1)每个表的主外码;
(2)顾客的姓名和商品名不能为空值;
(3)单价必须大于0,购买数量必须再0到20之间;
create table rxy_product
(
product_id varchar2(10) not null primary key,
product_name varchar2(30) not null,
unitprice number(8,2) default 0.00,
category varchar2(20),
provider varchar2(30),
startdate date,
constraint RXY_UNITPRICE check ( unitprice>0 )
);
/
create table rxy_customer
(
customer_id varchar2(10) not null primary key,
customer_name varchar2(12) not null,
location varchar2(50)
);
/
create table rxy_purchase
(
customer_id varchar2(10) not null,
product_id varchar2(10) not null,
quantity number(4,0) default 0,
constraint FK_PRODUCT foreign key(product_id) references rxy_product,
constraint FK_CUSTOMER foreign key(customer_id) references rxy_customer,
constraint RXY_QUANTITY check ( quantity>=0 and quantity<=20 )
);
2.往表中插入数据:
insert into rxy_product (product_id,product_name,unitprice,category,provider) values( 'M01','佳洁士',8.00,'牙膏','宝洁' );
insert into rxy_product (product_id,product_name,unitprice,category,provider) values( 'M02','高露洁',6.50,'牙膏','高露洁' );
insert into rxy_product (product_id,product_name,unitprice,category,provider) values( 'M03','洁诺',5.00,'牙膏','联合利华' );
insert into rxy_product (product_id,product_name,unitprice,category,provider) values( 'M04','舒肤佳',3.00,'香皂','宝洁' );
insert into rxy_product (product_id,product_name,unitprice,category,provider) values( 'M05','夏士莲',5.00,'香皂','联合利华' );
insert into rxy_product (product_id,product_name,unitprice,category,provider) values( 'M06','雕牌',2.50,'洗衣粉','纳爱斯' );
insert into rxy_product (product_id,product_name,unitprice,category,provider) values( 'M07','中华',3.50,'牙膏','联合利华' );
insert into rxy_product (product_id,product_name,unitprice,category,provider) values( 'M08','汰渍',3.00,'洗衣粉','宝洁' );
insert into rxy_product (product_id,product_name,unitprice,category,provider) values( 'M09','碧浪',4.00,'洗衣粉','宝洁' );
insert into rxy_customer values( 'C01','Dennis','海淀' );
insert into rxy_customer values( 'C02','John','朝阳' );
insert into rxy_customer values( 'C03','Tom','东城' );
insert into rxy_customer values( 'C04','Jenny','东城' );
insert into rxy_customer values( 'C05','Rick','西城' );
insert into rxy_purchase values( 'C01','M01',3 );
insert into rxy_purchase values( 'C01','M05',2 );
insert into rxy_purchase values( 'C01','M08',2 );
insert into rxy_purchase values( 'C02','M02',5 );
insert into rxy_purchase values( 'C02','M06',4 );
insert into rxy_purchase values( 'C03','M01',1 );
insert into rxy_purchase values( 'C03','M05',1 );
insert into rxy_purchase values( 'C03','M06',3 );
insert into rxy_purchase values( 'C03','M08',1 );
insert into rxy_purchase values( 'C04','M03',7 );
insert into rxy_purchase values( 'C04','M04',3 );
insert into rxy_purchase values( 'C05','M06',2 );
insert into rxy_purchase values( 'C05','M07',8 );
/
3.用SQL语句完成下列查询:
(1)求购买了供应商"宝洁"产品的所有顾客;
select customer_name
from rxy_product p,rxy_customer c,rxy_purchase pc
where p.product_id=pc.product_id
and pc.customer_id=c.customer_id
and p.provider='宝洁';
注:查了好多资料,都是用多个insert语句插入多条记录,要是用一个insert语句就能插入多条记录就比较方便了.
???
(2)求购买的商品包含了顾客"Dennis"所购买的所有商品的顾客(姓名);
select customer_name
from rxy_customer ct
where (
select pc.product_id from rxy_purchase pc,rxy_customer c
where pc.customer_id=c.customer_id
and lower(c.customer_name)='dennis'
minus
select pc.product_id from rxy_purchase pc
where pc.customer_id=ct.customer_id
) is null;
注:这道题让我很头疼,有点不想去想的感觉......
(3)求牙膏卖出数量最多的供应商。
select provider , NUM
from (
select distinct pd.provider , ( select sum(SUM_NUM) from (
select p.provider pp, ( select sum(quantity) from rxy_purchase pc
where pc.product_id=p.product_id
) "SUM_NUM"
from rxy_product p
where category='牙膏'
)
where pp=pd.provider
) "NUM"
from rxy_product pd
where category='牙膏'
order by NUM desc
)
where rownum=1;
注:这道题肯定有更简单的方法,但是我还是没想出来,可惜啊!!!
4 将所有的牙膏商品单价增加10%。
update rxy_product
set unitprice=unitprice*1.1
where category='牙膏';
5 删除从未被购买的商品记录。
delete from rxy_product
where product_id in (
select p.product_id from rxy_product p
minus
select pc.product_id from rxy_purchase pc
);
1、创建表myemp和emp表具有相同的结构和记录。
create table myemp as select * from emp;
2、给myemp的empno列添加主建约束。
alter table myemp add constraint pk primary key(empno);
3、给myemp添加一条记录。
insert into myemp values(1,'a','ab',999999,to_date('2008-9-9','yyyy-mm-dd'),5000,1000,30);
4、给myemp添加一条记录只有empno,ename,mgr,sal,deptno有值,其他列为空。
insert into myemp(empno,ename,mgr,sal,deptno) values(20,'aa',30,999999,20);
5、显示所有薪金高于公司均薪金的人。
select ename 姓名, sal 薪金
from emp
where sal >
(select avg(sal) from emp);
6、显示所有薪金高于各自部门平均薪金的人。 关联子查询*******
select e.ename , e.deptno, e.sal from emp e where e.sal>(select avg(sal) from emp p where e.deptno=p.deptno);
7、给所有10部门的经理(MANAGER)和20部门的职员(CLERK),增加薪金10%。
update emp set sal=sal*(1+0.1) where (deptno=10 and upper(job)='MANAGER') or(deptno=20 and upper(job)='CLERK' )
8、使用EMP,DEPT表显示所有销售部'SALES'的员工。
select * from emp,dept
where emp.deptno=dept.deptno and upper(emp.job)='SALES';
9、删除DEPT中没有员工的部门。
delete from dept where deptno not in( select deptno from emp);
10、显示所有的部门信息和人数。******
//说明:当部门里没有人时也要显示,即把所有的部门显示。所有在emp端加上‘+’
select dept.* , (select count(ename) from emp where emp.deptno(+)=dept.deptno) 人数 from dept;
11、删除雇佣年限低于20年的员工。
delete from emp where where trunc(sysdate-hiredate)<365*20;
12、显示各部门员工薪金最高的前2名//?????????????????
select b.ename,b.deptno,b.sal from emp b
where sal >= (
select a.sal from ( select ename,deptno,sal from emp order by deptno,sal desc) a
where a.deptno=b.deptno and rownum <=2
minus
select a.sal from ( select ename,deptno,sal from emp order by deptno,sal desc) a
where a.deptno=b.deptno and rownum <=1
)
order by b.deptno,b.sal desc;
13、显示薪金最高的3位员工
select ename,sal ,rownum
from (select ename,sal from emp order by sal desc)
where rownum<=3;
14、为EMP的各字段建立必要的索引,使得如下查询能使用上索引
select * from emp where ename like 'S%';
创建索引: create index I_NAME on table emp(ename);
---------------------------------------------------------------------------------------
select * from emp where job='MANAGER';
创建索引: create index I_JOB on table emp(job);
---------------------------------------------------------------------------------------
select * from emp where hiredate>to_date('1982','yyyy');
创建索引: create index I_HIREDATE on table emphiredate);
---------------------------------------------------------------------------------------
============================================================
现有一个商店的数据库,记录顾客及其购物情况,由下面三个表组成:
商品product(商品号productid,商品名productname,单价unitprice,商品类别category,供应商provider,上架日期startdate);
顾客customer(顾客号customerid,姓名name,住址location);
购买purchase(顾客号customerid,商品号productid,购买数量quantity);
试用SQL语言完成下列功能:
1.建表:在定义中要求声明:
(1)每个表的主外码;
(2)顾客的姓名和商品名不能为空值;
(3)单价必须大于0,购买数量必须再0到20之间;
create table rxy_product
(
product_id varchar2(10) not null primary key,
product_name varchar2(30) not null,
unitprice number(8,2) default 0.00,
category varchar2(20),
provider varchar2(30),
startdate date,
constraint RXY_UNITPRICE check ( unitprice>0 )
);
/
create table rxy_customer
(
customer_id varchar2(10) not null primary key,
customer_name varchar2(12) not null,
location varchar2(50)
);
/
create table rxy_purchase
(
customer_id varchar2(10) not null,
product_id varchar2(10) not null,
quantity number(4,0) default 0,
constraint FK_PRODUCT foreign key(product_id) references rxy_product,
constraint FK_CUSTOMER foreign key(customer_id) references rxy_customer,
constraint RXY_QUANTITY check ( quantity>=0 and quantity<=20 )
);
2.往表中插入数据:
insert into rxy_product (product_id,product_name,unitprice,category,provider) values( 'M01','佳洁士',8.00,'牙膏','宝洁' );
insert into rxy_product (product_id,product_name,unitprice,category,provider) values( 'M02','高露洁',6.50,'牙膏','高露洁' );
insert into rxy_product (product_id,product_name,unitprice,category,provider) values( 'M03','洁诺',5.00,'牙膏','联合利华' );
insert into rxy_product (product_id,product_name,unitprice,category,provider) values( 'M04','舒肤佳',3.00,'香皂','宝洁' );
insert into rxy_product (product_id,product_name,unitprice,category,provider) values( 'M05','夏士莲',5.00,'香皂','联合利华' );
insert into rxy_product (product_id,product_name,unitprice,category,provider) values( 'M06','雕牌',2.50,'洗衣粉','纳爱斯' );
insert into rxy_product (product_id,product_name,unitprice,category,provider) values( 'M07','中华',3.50,'牙膏','联合利华' );
insert into rxy_product (product_id,product_name,unitprice,category,provider) values( 'M08','汰渍',3.00,'洗衣粉','宝洁' );
insert into rxy_product (product_id,product_name,unitprice,category,provider) values( 'M09','碧浪',4.00,'洗衣粉','宝洁' );
insert into rxy_customer values( 'C01','Dennis','海淀' );
insert into rxy_customer values( 'C02','John','朝阳' );
insert into rxy_customer values( 'C03','Tom','东城' );
insert into rxy_customer values( 'C04','Jenny','东城' );
insert into rxy_customer values( 'C05','Rick','西城' );
insert into rxy_purchase values( 'C01','M01',3 );
insert into rxy_purchase values( 'C01','M05',2 );
insert into rxy_purchase values( 'C01','M08',2 );
insert into rxy_purchase values( 'C02','M02',5 );
insert into rxy_purchase values( 'C02','M06',4 );
insert into rxy_purchase values( 'C03','M01',1 );
insert into rxy_purchase values( 'C03','M05',1 );
insert into rxy_purchase values( 'C03','M06',3 );
insert into rxy_purchase values( 'C03','M08',1 );
insert into rxy_purchase values( 'C04','M03',7 );
insert into rxy_purchase values( 'C04','M04',3 );
insert into rxy_purchase values( 'C05','M06',2 );
insert into rxy_purchase values( 'C05','M07',8 );
/
3.用SQL语句完成下列查询:
(1)求购买了供应商"宝洁"产品的所有顾客;
select customer_name
from rxy_product p,rxy_customer c,rxy_purchase pc
where p.product_id=pc.product_id
and pc.customer_id=c.customer_id
and p.provider='宝洁';
注:查了好多资料,都是用多个insert语句插入多条记录,要是用一个insert语句就能插入多条记录就比较方便了.
???
(2)求购买的商品包含了顾客"Dennis"所购买的所有商品的顾客(姓名);
select customer_name
from rxy_customer ct
where (
select pc.product_id from rxy_purchase pc,rxy_customer c
where pc.customer_id=c.customer_id
and lower(c.customer_name)='dennis'
minus
select pc.product_id from rxy_purchase pc
where pc.customer_id=ct.customer_id
) is null;
注:这道题让我很头疼,有点不想去想的感觉......
(3)求牙膏卖出数量最多的供应商。
select provider , NUM
from (
select distinct pd.provider , ( select sum(SUM_NUM) from (
select p.provider pp, ( select sum(quantity) from rxy_purchase pc
where pc.product_id=p.product_id
) "SUM_NUM"
from rxy_product p
where category='牙膏'
)
where pp=pd.provider
) "NUM"
from rxy_product pd
where category='牙膏'
order by NUM desc
)
where rownum=1;
注:这道题肯定有更简单的方法,但是我还是没想出来,可惜啊!!!
4 将所有的牙膏商品单价增加10%。
update rxy_product
set unitprice=unitprice*1.1
where category='牙膏';
5 删除从未被购买的商品记录。
delete from rxy_product
where product_id in (
select p.product_id from rxy_product p
minus
select pc.product_id from rxy_purchase pc
);