哈工大 数据库 实验1 实验2 sql语句
/************** creat database operation *********************/
create database CAP /* 創建數據庫 */
on primary(
name = 'CAP' , /* 邏輯名 */
filename = 'F:\SQL文件\CAP.mdf'
)
log on( /* 創建日誌 */
name = 'CAP_log' ,
filename = 'F:\SQL文件\CAP.mdl'
);
exec sp_helpdb; /* 查詢數據庫信息 */
exec sp_spaceused; /* 查詢CAP佔用空間 */
/************** creat table operation *********************/
create table Agents( aid char(5) primary key , aname varchar(10) , city varchar(10) ,
percnt float(2) );
create table Customers( cid char(5) primary key , cname varchar(10) , city varchar(10) , discnt float(1) );
create table Products( pid char(5),pname varchar(10),city varchar(10) , quanity integer , price float(2) );
create table Orders(
ordno char(5) primary key , months char(3) , cid char(5) , aid char(5) , pid char(5) ,
qty integer , dollars integer ,
foreign key(cid) references Customers,
foreign key(aid) references Agents
);
/************** insert operation *********************/
/*create table Agents( aid char(5) primary key , aname char(10) , city char(10) , percnt float(2) );*/
insert into Agents
values( 'a01' , 'BigBang' , 'Dallas' , 0.1 );
insert into Agents
values( 'a02' , 'Luis' , 'Kyoto' , 0.15 );
insert into Agents
values( 'a03' , 'Nero' , 'Duluth' , 0.1 );
insert into Agents
values( 'a04' , 'Zaker' , 'Newark' , 0.2 );
/*create table Customers( cid char(5) primary key , cname char(10) , city char(10) , discnt float(1) );*/
insert into Customers
values( 'c001' , 'John', 'Dallas' , 0.9 );
insert into Customers
values( 'c002' , 'Mary', 'Kyoto' , 1 );
insert into Customers
values( 'c003' , 'Nicole', 'Duluth' , 1 );
insert into Customers
values( 'c004' , 'Nancy', 'Newark' , 1 );
insert into Customers
values( 'c005' , 'Peter', 'Newark' , 1 );
/*create table Products( pid char(5) , pname char(10) , city char(10) , quanity integer , price float(2) );*/
insert into Products
values( 'p01' , 'pencil' , 'Dallas' , 10000 , 0.1 );
insert into Products
values( 'p01' , 'pencil' , 'Kyoto' , 5000 , 0.1 );
insert into Products
values( 'p01' , 'pencil' , 'Duluth' , 3000 , 0.15 );
insert into Products
values( 'p02' , 'pen' , 'Newark' , 15000 , 1 );
insert into Products
values( 'p03' , 'rubber' , 'Dallas' , 800 , 0.2 );
insert into Products
values( 'p04' , 'penknife' , 'Duluth' , 550 , 0.6 );
insert into Products
values( 'p05' , 'triangle' , 'Newark' , 2000 , 1 );
insert into Products
values( 'p06' , 'crayon' , 'Kyoto' , 30000 , 0.5 );
insert into Products
values( 'p06' , 'crayon' , 'Dallas' , 12000 , 0.6 );
insert into Products
values( 'p07' , 'schoolbag' , 'Duluth' , 600 , 5 );
/*create table Oders( ordno char(5) primary key , months char(3) , cid char(4) , aid char(4) , pid char(4) , qty integer , dollars integer );*/
insert into Orders
values( '0d001' , 'feb' , 'c001' , 'a01' , 'p01' , 1000 , 90 );
insert into Orders
values( '0d002' , 'feb' , 'c002' , 'a04' , 'p05' , 1000 , 1000 );
insert into Orders
values( '0d003' , 'mar' , 'c002' , 'a03' , 'p04' , 1000 , 600 );
insert into Orders
values( '0d004' , 'mar' , 'c001' , 'a02' , 'p06' , 600 , 300 );
insert into Orders
values( '0d005' , 'mar' , 'c002' , 'a02' , 'p06' , 1000 , 500 );
insert into Orders
values( '0d006' , 'mar' , 'c002' , 'a03' , 'p07' , 200 , 1000 );
/* 創建視圖 */
create view view_tt( vid,vname,vcity,vquanity,vprice )
as(
select P.pid , P.pname , P.city , P.quanity , P.price from Products P
where P.city = 'Duluth'
);
select vid , vname from view_tt
where vquanity < 1000
/*********** 練習語句 **********************************/
/** update **/
update Products
set price = price * 100
update Products
set quanity = 1
where pid = 'p02';
/* select **/
select C.cid , A.aid , P.pid , C.city , A.city , P.city from Customers C , Agents A , Products P
where C.city = A.city and C.city = P.city;
Select P.pname from Products P , Customers C , Agents A , Orders O
where O.cid = C.cid and O.aid = A.aid and O.pid = p.pid
and C.city = 'Kyoto' and A.city = 'Kyoto';
/* delete **/
delete from Products where city = 'Duluth';
delete from Products where price = 0.1;
基于OrderDB数据库，完成以下SQL语句：
查询职工工资按高低排序的前20%的职工编号、职工姓名和工资。
查询业务科或财务科的职工姓名、性别和所在部门，仅显示前面5位职工。
查询1973年9月份出生且为职员的员工编号、姓名、出生日期以及所在部门，并按出生日期的降序输出。
查询所有姓张的职工姓名、部门和性别（要求性别以中文显示）。
查询姓张且全名为两个字的职工姓名。
查询不同性别员工的人数
查询全体职工的姓名、年龄、所属部门，并且用汉语显示表头信息。
查询工资在3000以下，3000至5000，以及5000以上的员工人数
查询薪水在2000到3000之间的职工编号、姓名、所在部门以及薪水。
查询1991年被雇佣的职工号、姓名、性别、电话号码、出生日期以及年龄（如果电话号码为空，显示“未知”，出生日期按yyyy-mm-dd显示）。
查询每个客户的订单编号、客户名称、订单金额。
查询住址在上海的员工所做的订单，输出员工编号、姓名、住址、订单编号、客户编号和订单日期，并按客户编号排序输出。
查找与“吴浮萍”不在同一个部门工作的员工姓名、所属部门、性别和出生日期，并按所属部门排序输出。
查找订购了“32M DRAM”的商品的客户编号、客户名称、订单编号、订货数量和订货金额，并按客户编号排序输出。
查询销售总量大于4的商品编号、商品名称以及销售数量。
查询员工“张小娟”所做的订单编号，客户名称，订单总金额以及发票号。
查询没有订购商品的且在北京地区的客户编号、客户名称和邮政编码，并按邮政编码降序排序。
查询订购了“32M DRAM”商品的订单编号、订货数量和订货单价。
查询与员工编号E2008005在同一部门的员工编号、姓名、性别、所属部门。
查询既订购了P20050001又订购了P20050003商品的客户编号及订单号。
查询没有订购“52倍速光驱”或“17寸显示器”的客户编号、客户名称。
查询订单金额最高的订单编号、客户姓名、销售员姓名和相应的订单金额。
查询商品“52倍速光驱”的订购数量、订购平均价和订购总金额。
查询所有业务员的订单数量（给出业务员编号，姓名，及订单数量）。
统计在业务科工作且在1973年出生的员工人数和平均工资。
统计每种商品的销售数量和销售总金额。（给出商品编号及名称，并按金额的升序排序）。
统计每个客户的订单数、订货总额和平均订货金额。
查询每个客户订购的商品编号、商品所属类别、同类别商品数量及对应的订货金额。（结果显示客户名称、商品所属类别、商品数量及订货金额，并按客户编号升序和按订货金额的降序排序输出）
查找至少有2次销售的业务员名单和销售日期。
查找销售总额少于5000元的销售员编号、姓名和销售额。
计算每一商品每月的销售总金额。（给出商品编号、商品名称以及销售总额，并将结果首先按销售月份升序然后按总金额降序排序输出）
查询没有订购“键盘”商品的客户编号及名称（要求使用存在量词not esists）。
查询没有订购过任何商品的客户编号和客户名称（要求使用存在量词not esists）。
由于网上很多对于诸如：“查询每个销售员的销售额”时，没有输出销售额为0的情况，这显然不对。。。
另外，其实我是过来贴代码的。。。
经跟几个同学对照了一下查询结果，没发现有什么问题，如果有的话，请指出。。。
/* 1 查询职工工资按高低排序的前20%的职工编号、职工姓名和工资。 ok */
select top 20 percent employeeNo , employeeName , salary
from Employee
order by salary DESC
/* 2 查询 业务科 或 财务科 的 职工姓名、性别和所在部门 ，仅显示前面5位职工。 ok */
select top 5 employeeName , gender , department from Employee
where department = '业务科' or department = '财务科'
/* 3 查询1973年9月份出生且为职员的员工编号、姓名、出生日期以及所在部门，并按出生日期的降序输出。ok */
select employeeNo , employeeName , birthday , department from Employee
where year(birthday) = 1973 and month(birthday) = 9 and headShip = '职员'
order by birthday desc
/* 4 查询所有姓张的职工姓名、部门和性别（要求性别以中文显示）。ok */
select employeeName , department ,
case when gender = 'M' then '男' when gender = 'F' then '女'
end gender from Employee
where employeeName like '张%'
/* 5 查询姓张且全名为两个字的职工姓名。ok */
select employeeName from Employee
where employeeName like '张_'
/* 6 查询不同性别员工的人数 ok */
select
case when gender = 'M' then '男' when gender = 'F' then '女'
end gender, count(gender) as '數目' from Employee
group by gender
/* 7 查询全体职工的姓名、年龄、所属部门，并且用汉语显示表头信息。 ok */
select employeeName 姓名 ,datediff(year,Birthday,getdate()) 年龄, department 所属部门
from Employee
/* 8 查询工资在3000以下，3000至5000，以及5000以上的员工人数 ok */
select
(
case when salary<3000 then '3000以下'
when salary>=3000 and salary<=5000 then '3000至5000'
when salary>5000 then '5000以上'
end
) 工资 , count(*) as 员工人数
from Employee
group by
case when salary<3000 then '3000以下'
when salary>=3000 and salary<=5000 then '3000至5000'
when salary>5000 then '5000以上'
end
/* 9 查询薪水在2000到3000之间的职工编号、姓名、所在部门以及薪水。ok */
/* 职工编号 姓名 所在部门 薪水 */
select employeeNo , employeeName , department , salary from Employee
where salary between 2000 and 3000
/* 10 查询1991年被雇佣的职工号、姓名、性别、电话号码、出生日期以及年龄
（如果电话号码为空，显示“未知”，出生日期按yyyy-mm-dd显示）。ok */
/* 职工号 姓名 性别 电话号码 出生日期 年龄 */
select employeeNo , employeeName , gender , isnull(telephone,'未知') as 'telephone' ,
convert(char(10),birthday,120) as 出生日期 ,
datediff(year,Birthday,getdate()) as 年龄 from Employee
where year(hireDate) = 1991
/* 11 查询每个客户的订单编号、客户名称、订单金额。ok */
select o.orderNo , c.customerName , o.orderSum from OrderMaster o , Customer c
where c.customerNo = o.customerNo
/* 12 查询住址在上海的员工所做的订单，输出员工编号、姓名、住址、订单编号、客户编号
和订单日期，并按客户编号排序输出。ok */
/* 员工编号 姓名 住址 订单编号 客户编号 订单日期 */
select e.employeeNo , e.employeeName , e.address , o.orderNo , o.customerNo , o.orderDate
from Employee e , OrderMaster o
where e.address like '上海%' and e.employeeNo = o.employeeNo
order by o.customerNo
/* 13 查找与“吴浮萍”不在同一个部门工作的员工姓名、所属部门、性别和出生日期，并按所属部门排序输出。ok */
/* 员工姓名 所属部门 性别 出生日期 */
select employeeName , department , gender , birthday from Employee
where department not in(
select department from Employee
where employeeName = '吴浮萍'
)
order by department
/* 14 查找订购了“32M DRAM”的商品的客户编号、客户名称、订单编号、订货数量和订货金额，
并按客户编号排序输出 ok */
/* 客户编号 客户名称 订单编号 订货数量 订货金额 */
select om.customerNo , c.customerName , od.orderNo , od.quantity , od.price*od.quantity 订货金额
from OrderDetail od , OrderMaster om , Customer c , Product p
where om.customerNo = c.customerNo and om.orderNo = od.orderNo and od.productNo = p.productNo
and p.productName = '32M DRAM'
order by om.customerNo
/* 15 查询销售总量大于4的商品编号、商品名称以及销售数量。 ok */
/* 商品编号 商品名称 销售数量 */
select P.productNo , P.productName , X.sales as '销售数量' from Product P , (
select Y.productNo , Y.sales from(
select OD.productNo , sum(OD.quantity) as sales from OrderDetail OD
group by OD.productNo
)Y
where Y.sales > 4
)X
where X.productNo = P.productNo
/* 16 查询员工“张小娟”所做的 订单编号，客户名称，订单总金额以及发票号。 ok */
/* 订单编号 客户名称 订单总金额 发票号 */
select om.orderNo , c.customerName , om.orderSum , om.invoiceNo
from OrderMaster om , Customer c , Employee e
where om.employeeNo = e.employeeNo and c.customerNo = om.customerNo and e.employeeName = '张小娟'
/* 17 查询没有订购商品的且在北京地区的客户编号、客户名称和邮政编码，并按邮政编码降序排序。ok */
/* 客户编号 客户名称 邮政编码 */
select customerNo , customerName , zip from Customer
where address = '北京市' and customerNo not in(
select customerNo from OrderMaster
)
order by zip DESC
/* 18 查询订购了“32M DRAM”商品的订单编号、订货数量和订货单价。 ok */
/* 订单编号 订货数量 订货单价 */
select od.orderNo , od.quantity , od.price from OrderDetail od , Product p
where p.productNo = od.productNo and p.productName = '32M DRAM'
/* 19 查询与员工编号E2008005在同一部门的员工编号、姓名、性别、所属部门。ok */
/* 员工编号 姓名 性别 所属部门 */
select employeeNo , employeeName , gender , department from Employee
where employeeNo <> 'E2008005' and department in(
select department from Employee where employeeNo = 'E2008005'
)
/* 20 查询既订购了P20050001又订购了P20050003商品的客户编号及订单号。 */
/* 按照不同的理解，这条语句应该有两种答案 */
/* 客户编号 订单号 */ /* 同一个客户在同一份订单中购买了两份商品 */
select OM.customerNo , OD.orderNo from OrderDetail OD , OrderMaster OM
where OM.orderNo = OD.orderNo and OD.productNo = 'P20050001'
and OM.customerNo in(
select OM_1.customerNo from OrderDetail OD_1 , OrderMaster OM_1
where OD_1.orderNo = OM_1.orderNo and OD_1.orderNo = OD.orderNo
and OD_1.productNo = 'P20050003'
)
/* 客户编号 订单号 */ /* 同一个客户允许在不同订单中购买了该两件商品 */
select OM_1.customerNo , OM_1.orderNo
from OrderMaster OM_1 , OrderDetail OD_1 , OrderMaster OM_2 , OrderDetail OD_2
where OD_1.orderNo = OM_1.orderNo and OD_2.orderNo = OM_2.orderNo and OM_1.customerNo = OM_2.customerNo
and OD_1.productNo = 'P20050001' and OD_2.productNo = 'P20050003'
union
select OM_1.customerNo , OM_1.orderNo
from OrderMaster OM_1 , OrderDetail OD_1 , OrderMaster OM_2 , OrderDetail OD_2
where OD_1.orderNo = OM_1.orderNo and OD_2.orderNo = OM_2.orderNo and OM_1.customerNo = OM_2.customerNo
and OD_1.productNo = 'P20050003' and OD_2.productNo = 'P20050001'
/* 21 查询没有订购“52倍速光驱”或“17寸显示器”的客户编号、客户名称。 ok */
/* 客户编号 客户名称*/
select customerNo , customerName from Customer
where customerNo not in(
select OM.customerNo from OrderMaster OM
where OM.orderNo in(
select OD.orderNo from OrderDetail OD , Product P
where P.productNo = OD.productNo and (P.productName = '52倍速光驱' or P.productName = '17寸显示器' )
)
)
/* 22 查询订单金额最高的订单编号、客户姓名、销售员姓名和相应的订单金额。ok */
/* 订单编号 客户姓名 销售员姓名 订单金额 */
select om.orderNo , c.customerName , e.employeeName , om.orderSum
from OrderMaster om , Customer c , Employee e
where c.customerNo = om.customerNo and om.employeeNo = e.employeeNo
and om.orderSum >= all(
select om1.orderSum from OrderMaster om1
)
/* 23 查询商品“52倍速光驱”的订购数量、订购平均价和订购总金额。ok */
/* 订购数量 订购平均价 订购总金额 */
select sum(OD.quantity) as '订购数量', avg(OD.price) as '订购平均价', sum(OM.orderSum) as '订购总金额'
from OrderMaster OM , OrderDetail OD , Product P
where P.productNo = OD.productNo and OD.orderNo = OM.orderNo and P.productName = '52倍速光驱'
group by P.productNo
/* 24 查询所有业务员的订单数量（给出 业务员编号，姓名，及订单数量）。ok */
/* 业务员编号 姓名 订单数量*/
select EM.employeeNo 业务员编号 , EM.employeeName 姓名 ,
isnull((
select count(*) from OrderMaster OM
where OM.employeeNo = EM.employeeNo
group by OM.employeeNo
),0) 订单数量
from Employee EM
where EM.department = '业务科'
/* 25 统计在业务科工作且在1973年出生的员工人数和平均工资。 ok */
/* 员工人数 平均工资 */
select count(*) as '员工人数' , avg(salary) as '平均工资' from Employee
where year(birthday) = 1973 and department = '业务科'
/* 26 统计每种商品的销售数量和销售总金额。（给出商品编号及名称，并按金额的升序排序）。ok */
/* 商品编号 名称 销售数量 销售总金额 */
select P.productNo 商品编号 , P.productName 名称,
isnull ( (
select sum(OD_1.quantity) from OrderDetail OD_1
where OD_1.productNo = P.productNo
) , 0) 销售数量 ,
isnull ( (
select sum(OD_1.quantity* OD_1.price) from OrderDetail OD_1
where OD_1.productNo = P.productNo
) , 0) 销售总金额
from Product P
order by 销售总金额
/* 27 统计每个客户的订单数、订货总额和平均订货金额。 */
select C.customerNo , C.customerName ,
count(OM.orderNo) 订单数 ,
isnull(sum(OM.orderSum),0) 订货总额 ,
isnull(avg(OM.orderSum),0) 平均订货金额
from Customer C left join OrderMaster OM on C.customerNo = OM.customerNo
group by C.customerNo , C.customerName
/* 28 查询每个客户订购的 商品所属类别、同类别商品数量及对应的订货金额。
（结果显示客户名称、商品所属类别、商品数量及订货金额，并按客户编号升序和按订货金额的降序排序输出） */
/* 客户名称 商品所属类别 同类别商品数量 订货金额 */
select C.customerName , X.productClass , X.num 同类别商品数量 , X.tot 订货金额
from Customer C , (
select Y.productClass , Y.customerNo , Y.num , Y.tot from (
select P.productClass , OM.customerNo , sum(OD.quantity) num , sum(OD.quantity*OD.price) tot
from OrderDetail OD , OrderMaster OM , Product P
where OD.orderNo = OM.orderNo and P.productNo = OD.productNo
group by P.productClass , OM.customerNo
)Y
)X
where X.customerNo = C.customerNo
/* 29 查找至少有2次销售的业务员名单和销售日期。ok */
/* 业务员名单 销售日期 */
select EM.employeeNo , EM.employeeName , OM.orderDate
from Employee EM , OrderMaster OM , (
select employeeNo from OrderMaster
group by employeeNo
having count(employeeNo) >= 2
)X
where EM.employeeNo = OM.employeeNo and EM.employeeNo = X.employeeNo
/* 30 查找销售总额少于5000元的销售员编号、姓名和销售额。ok */
/* 销售员编号 姓名 销售额*/
select EM.employeeNo , EM.employeeName , isnull( sum(OM.orderSum) , 0 ) 销售额
from Employee EM left join OrderMaster OM on EM.employeeNo = OM.employeeNo
where EM.department = '业务科'
group by EM.employeeNo , EM.employeeName
having isnull( sum(OM.orderSum),0 )<5000
/* 31 计算每一商品每月的销售总金额。（给出商品编号、商品名称以及销售总额，并将结果首先按销售月份升序
然后按总金额降序排序输出） */
/* 商品编号 商品名称 月份 销售总额 */
select P.productNo ,
P.productName ,
isnull(month(OM.orderDate),0) 月份 ,
isnull( sum(OD.price*OD.quantity) , 0 ) 销售总额
from Product P left join OrderDetail OD on P.productNo = OD.productNo
left join OrderMaster OM ON OM.orderNo = OD.orderNo
group by P.productNo , P.productName , isnull( month(OM.orderDate) , 0)
order by isnull( month(OM.orderDate) , 0) , isnull( sum(OD.price*OD.quantity) , 0 ) desc
/* 32 查询没有订购“键盘”商品的客户编号及名称（要求使用存在量词not exists）。ok */
/* 客户编号 名称*/
select c.customerNo , c.customerName from Customer c
where not exists(
select * from OrderDetail OD , OrderMaster OM , Product P
where OD.productNo = P.productNo and OD.orderNo = OM.orderNo and P.productName = '键盘'
and c.customerNo = OM.customerNo
)
/* 33 查询没有订购过任何商品的客户编号和客户名称（要求使用存在量词not exists）。ok */
/* 客户编号 客户名称 */
select c0.customerNo , c0.customerName from Customer c0
where not exists(
select * from Customer c , OrderMaster om
where om.customerNo = c.customerNo and c.customerNo = c0.customerNo
)
