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

本文主要是介绍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