哈工大 数据库 实验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
)