SQL 必知必会第十二课 联结表

SQL 必知必会第十二课 联结表

## 创建链接
select vend_name, prod_name, Prod_price
from vendors, products
where vendors.vend_id = Products.vend_id; -- 此处的where就是作为联结的工具，将两个表配对

select vend_name, prod_name, Prod_price
from vendors
inner join products on vendors.vend_id = products.vend_id;

##连接多个表
select prod_name, vend_name, prod_price, quantity
from orderitems, products, vendors
where products.vend_id = vendors.vend_id
and orderitems.prod_id = products.prod_id
and order_num = 20007;

select * from orderitems;
## challenges

select cust_name, order_num
from customers
inner join orders on customers.cust_id = orders.cust_id
order by order_num ; -- 1

select cust_name, orders.order_num, (quantity * item_price) as OrderTotal
from customers, orders, orderitems
where customers.cust_id = orders.cust_id
and orders.order_num = orderitems.order_num
order by order_num ; -- 2

select cust_id, order_date
from orders, orderitems
where orders.order_num = orderitems.order_num
and prod_id = 'BR01'; -- 3

select cust_email
from customers 
inner join orders on customers.cust_id = orders.cust_id
where customers.cust_id in(select orders.cust_id 
				        from orders 
				        inner join OrderItems on orders.order_num and OrderItems.order_num
						where prod_id = 'BR01')
group by cust_email; -- 4

select cust_name
from customers 
inner join orders on customers.cust_id = orders.cust_id
where customers.cust_id in(select orders.cust_id 
				        from orders 
				        inner join OrderItems on orders.order_num and OrderItems.order_num
						where (orderitems.quantity * OrderItems.item_price) >= 1000)
group by cust_name
order by cust_name; -- 5, 用having的做不出来。。

注意点：

1. 运用关系数据库的可伸缩性比非关系数据要好。

可伸缩性：能够适应不断增加的工作量而不失败。

2. 当引用出现歧义时，必须要用完全限定语句。