SQL语言基础练习---数据库server 2008（二）

--找出不同的商店信息的商店名字
Select DISTINCT store_name 
FROM Store_Information


--找出表中营业额大于1,000的商店名字


select stroe_name
FROM Store_Information
where Sale>1000


--找出表中营业额大于1,000或者在500和275之间的


select stroe_name
FROM Stroe_Information
where Sale>1000 
OR(Sale<500 AND Sale>275)




--在表中找出Los Angelse 或 San Diego的资料


select *
FROM Stroe_Information
Where stroe_name IN ('Los Angelse','San Diego')


--找出表中january 6,1999 及 january 10,1999之间的资料
select *
FROM Stroe_Information
WHERE DATE BETWEEN 'january 6,1999' AND 'january 10,1999'


--通配符的用法
select *
FROM Store_Information
--输出：AN两边都可以延展的字符
WHERE stroe_name LIKE '%AN%'
--或者：A和Z之间添加一个字符。
WHERE store_name LIKE 'A_Z'
--或者：在acc 之前添加字符
WHERE store_name LIKE '%acc'
--或者：在acc 之后添加字符
WHERE store_name LIKE 'acc%'
 
--排序
SELECT store_name, Sale,Date
FROM Store_Information
ORDER BY Sales DESC




--或者


SELECT store_name,Sale,Date
FROM Store_Information
ORDER BY 2 DESC


--函数


--SUM
select SUM(SALE)
FROM store_Information




--count


select count(store_name)
FROM　store_Information
where store_name is not NULL


--或者


select count(DISTINCT store_name)
FROM Store_Information




--SUM和GROUP BY综合
select store_name,sum(sale)
FROM store_Information
GROUP BY store_name


--having
select store_name,SUM（sales）
FROM　store_Information
Group BY store_name
having SUM(sales)>1500


--替换
select A1.store_name Store,SUM(A1.Sale)"Total Sales"
FROM Store_Information A1
Group BY A1.store_name


--表格链接


--内部链接(左联接)
select A1.region_name REGION,SUM(A2.Sale)Sales
--第一行取得是别名,如果没有去别名的话是
--select Geography.region_name,SUM(store_Information.Sales)Sales太复杂
From Geography A1,store_Information A2
where A1.store_name=A2.store_name
Group By A1.region_name


--外部链接
select A1.store_name,sum(A2.Sale)Sales
From Geography A1,store_Information A2
where A1.store_name=A2.store_name(+)
group BY A1.store_name