sql习题1

现有一个商店的数据库，记录顾客及其购物情况，由下面三个表组成：

  1. 商品product（商品号productid int(7)]，
    商品名productname [varchar(128)]，
    单价unitprice int(11,2)，
    商品类别category[varchar(28)]，
    供应商provider [varchar(48)]）；
    主键：productid
  2. 顾客customer（顾客号customerid int(7)]，
    姓名name[varchar(48)]，
    住址location[varchar(128)]）；
    主键：customerid
  3. 购买purcase（顾客号customerid，商品号productid，
    购买数量quantity[int(7)]）；
    外键：customerid 引用 客户表的 customerid
    外键：productid 引用 产品表的 productid
    主键：(customerid, productid) –> 联合主键

任务一:使用DDL语言创建上面的表,并且设置必要的主键约束和外键约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
drop table product;
create table product(
productid int(7) primary key,
productname varchar(128),
unitprice double(11,2),
catagory varchar(28),
provider varchar(48)
);


drop table customer;
create table customer(
customerid int(7) primary key,
name varchar(48),
location varchar(128)
);

drop table purcase;
create table purcase(
customerid int(7),
productid  int(7),
quantity int(7),
constraint purcase_id_pk primary key(customerid,productid),
constraint purcase_id1_fk foreign key(productid) references product(productid),
constraint purcase_id2_fk foreign key(customerid) references customer(customerid)   
);

insert into product values(1,'佳洁士',8.00,'牙膏','宝洁'),(2,'高露洁',6.50,'牙膏','高露洁'),
(3,'洁诺',5.00,'牙膏','联合利华'),
(4,'舒肤佳',3.00,'香皂','宝洁'),
(5,'夏士莲',5.00,'香皂','联合利华'),
(6,'雕牌',2.50,'洗衣粉','纳爱斯'),
(7,'中华',3.50,'牙膏','联合利华'),
(8,'汰渍',3.00,'洗衣粉','宝洁'),
(9,'碧浪',4.00,'洗衣粉','宝洁');

insert into customer values(1,'Dennis','黄浦区'),
(2,'John','徐家汇'),
(3,'Tom','闸北'),
(4,'Jenny','静安'),
(5,'Rick','浦东');

insert into purcase values(1,1,3),     
(1,5,2),
(1,8,2) ,   
(2,2,5),
(2,6,4) ,    
(3,1,1),
(3,5,1 ),    
(3,6,3),
(3,8,1 ),    
(4,3,7),
(4,4,3 ),    
(5,6,2),
(5,7,8);

任务二: 向对应的表中插入如下数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
商品（1，佳洁士，8.00，牙膏，宝洁；
   2，高露洁，6.50，牙膏，高露洁；
   3，洁诺，5.00，牙膏，联合利华；
   4，舒肤佳，3.00，香皂，宝洁；
   5，夏士莲，5.00，香皂，联合利华；
   6，雕牌，2.50，洗衣粉，纳爱斯
   7，中华，3.50，牙膏，联合利华；
   8，汰渍，3.00，洗衣粉，宝洁；
   9，碧浪，4.00，洗衣粉，宝洁；）
顾客（1，Dennis，黄浦区；
   2，John，徐家汇；
   3，Tom，闸北；
   4，Jenny，静安；
   5，Rick，浦东；） 
购买(1，1，3；     
  1，5，2；
  1，8，2；     
  2，2，5；
  2，6，4；     
  3，1，1；
  3，5，1；     
  3，6，3；
  3，8，1；     
  4，3，7；
  4，4，3；     
  5，6，2；
  5，7，8；）

任务三: 完成如下语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
（1）求购买了供应商"宝洁"产品的所有顾客；
select distinct c.customerid,c.name from customer c join purcase p on p.customerid=c.customerid where p.productid  in (select pr.productid from product pr where pr.provider='宝洁');
+------------+--------+
| customerid | name   |
+------------+--------+
|          1 | Dennis |
|          3 | Tom    |
|          4 | Jenny  |
+------------+--------+
（2）求购买的商品包含了顾客"Dennis"所购买的所有商品的顾客（姓名）
select p2.customerid from purcase p2 where p2.productid in (select p.productid from customer c join purcase  p on c.customerid=p.customerid where c.name='Dennis') and customerid<>(select distinct p4.customerid from customer c4 join purcase p4 on p4.customerid=c4.customerid where c4.name="Dennis") group by p2.customerid having count(*)>=(select count(*) from customer c2 join purcase p3  on c2.customerid=p3.customerid where c2.name='Dennis');
+------------+
| customerid |
+------------+
|          3 |
+------------+
（3）求牙膏卖出数量最多的供应商。
select pr.provider,sum(pu1.quantity) from product pr join purcase pu1 on pu1.productid=pr.productid and pr.catagory='牙膏' GROUP BY pr.provider order by sum(pu1.quantity) desc limit 1;
+--------------+-------------------+
| provider     | sum(pu1.quantity) |
+--------------+-------------------+
| 联合利华     |                15 |
+--------------+-------------------+

(4)将所有的牙膏商品单价增加10%。
update product set unitprice=unitprice*1.1 where catagory='牙膏';

(5)删除从未被购买的商品记录。
delete from product where productid  not in (select pu.productid from purcase pu);