4.18SQL练习

---4月18日高级查询--
---多表查询
---不加条件，产生笛卡儿积--
USE pubs
GO
SELECT title_id,au_fname
FROM titles,authors
GO

USE pubs
GO
SELECT titles.title_id,au_fname
FROM titles,authors,titleauthor
WHERE titleauthor.au_id=authors.au_id AND titles.title_id=titleauthor.title_id
GO

--为了有效避免笛卡儿乘积的出现，对所定义的查询连接条件的数目是有规定的：如果有N个表格出现在FROM后面，那么，所定义
--的连接条件不得少于N-1个。

USE pubs
GO
SELECT authors.*
FROM titles,authors
GO

--商品对应的供应商信息---
USE Northwind
GO
SELECT ProductName,CompanyName
FROM Products,Suppliers
WHERE Products.SupplierID=Suppliers.SupplierID
GO

--使用UNION子句把查询结果集连接起来，ALL关键字显示全部结果，默认是删除重复行--
USE pubs
GO
SELECT state
FROM authors
UNION ALL
SELECT state
FROM publishers
ORDER BY state
GO

--使用GROUP BY分类统计---
USE pubs
GO
SELECT type,sum(price) "avg_price",avg(price) "avg_price",count(*)
FROM titles
WHERE type in ('business','mod_cook','trad_cook','popular_comp')
GROUP BY type
GO

--使用COMPUTE子句--
USE pubs
GO
SELECT type,price,advance
FROM titles
ORDER BY type
COMPUTE SUM(price),SUM(advance)
GO

--使用COMPUTE BY子句--
USE pubs
GO
SELECT type,price,advance
FROM titles
ORDER BY type
COMPUTE SUM(price),SUM(advance) BY type
GO

--多层嵌套---
USE pubs
GO
SELECT type,title_id,title
FROM titles
WHERE titles.title_id in(SELECT title_id
                            FROM sales
                            WHERE qty>(SELECT avg(qty)
                                          FROM sales)    )
GO