sql子查询

CREATE table  if not EXISTS customers
(
    `cust_id`        int(200),
    `cust_name`      char(20),
	`cust_address`   char(20),
	`cust_city`      char(20),
	`cust_state`     char(20),
	`cust_zip`       int,
	`cust_country`   char(20),
	`cust_contact`   char(20),
	`cust_email`     char(20)
);
CREATE table  if not EXISTS orderitems
(
    `order_num`        int,
	`order_item`       int,
	`prod_id`          char(20),
	`quantity`         char(10),
	`item_price`       int
);

CREATE table  if not EXISTS orders
(
    `order_num`        int,
	`order_date`       char(20),
	`cust_id`          int
);

insert into customers(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email)
     values
	(10001,'xiaotaoCoyote Inc.','200 Maple Lane','Detroit','MI',44444,'USA','Y Lee','ylee@coyote.com'),
    (10002,'Mouse House','333 Fromage Lane','Columbus','OH',43333,'USA','Jerry Mouse','NULL'),
	(10003,'Wascals','1 Sunny Place','Muncie','IN',42222,'USA','Jim Jones','rabbit@coyote.com'),
	(10004,'Yosemite Place','829 Riverside Drive','Phonix','AZ',88888,'USA','Y sam','sam@163.com'),
    (10005,'E Fudd','4545 53rd Street','Chicago','IL',54545,'USA','E Fudd',NULL);
    
insert into orders(order_num,order_date,cust_id)
     values
		 (20005,'2005-09-01',10001),
		 (20006,'2005-09-12',10003),
		 (20007,'2005-09-30',10004),
		 (20008,'2005-10-03',10005),
		 (20009,'2005-10-08',10001);
insert into orderitems(order_num,order_item,prod_id,quantity,item_price)
        values
		(20005,1,'ANV01',10,5.99),
		(20005,2,'ANV02',3,9.99),
		(20005,3,'TNT2',5,10.00),
		(20005,4,'FB',1,10.00),
		(20006,1,'JP2000',1,55.00),
		(20008,1,'FC',50,2.5),
		
		(20009,3,'FB',1,10.00),
		(20009,3,'OL1',1,9.99),
		(20009,3,'SLING',1,4.49),
		(20009,4,'ANV03',1,14.99);

#子查询训练 查找买了商品’TNT2’的顾客信息
#1.在orderitems里找出TNT2的订单号；
#2.在orders里找出第一步找出的订单号的id
#3.在customers中找出第二部对应客户的所有信息

#select order_num FROM orderitems WHERE prod_id='TNT2';

#select cust_id FROM orders WHERE order_num IN(select order_num FROM orderitems WHERE prod_id='TNT2');

#select DISTINCT * from customers WHERE cust_id IN(select cust_id FROM orders WHERE order_num IN(select #order_num FROM orderitems WHERE prod_id='TNT2'));

#子查询用于计算字段 查询每个客户的订单数

#select count(*) as orders1 FROM orders WHERE cust_id = 10001;

#select cust_name,cust_state,(SELECT COUNT(*) FROM orders WHERE orders.cust_id=customers.cust_id) AS ordernums #FROM customers ORDER BY cust_name;