在Northwind数据库中
1、从产品表Products当中查询列产品名称(ProductName),产品库存价值,(UnitPrice*UnitsInStock),并且使用汉字作为查询列的别名。
Select ProductName as 产品名称,UnitPrice*UnitsInStock as 产品库存价值
From Products
2、查询表Orders当中,定单日期(OrderDate)为7月份的定单。
Select * from Orders where Month(OrderDate) =7
3、查询表Employees中员工的LastName、年龄和工龄。
Select LastName,year(getdate())-year(BirthDate) as 年龄,
year(getdate())-year(HireDate) as 工龄
from Employees
4、查询表Employees中员工的LastName以’an’结束,FisrtName以’s’开头的员工信息。
Select *
From employees
Where LastName like '%an' and FirstName like 's%'
5、查询表Employees中员工的FisrtName以N或S开头的员工信息。
Select *
From employees
where Firstname like '[N,S]%'
6、查询表Customers中City值为London或Berlin或Madrid的客户信息。
Select *
From Customers
Where City in ('Berlin','London','Madrid')
7、查询表Customers中Region列为Null的客户公司名称(CompanyName), 联系人名称(ContactName),客户地址(Address)。
Select CompanyName,ContactName,Address
From customers
Where Region is null
8、查询表Employees的姓名(由LastName和FirstName组成),所在城市(City), 年龄和工龄, 并且要求员工的年龄按降序排列,员工的工龄按升序排列。
Select LastName+FirstName as 姓名,City as 城市,
year(GetDate())-year(BirthDate) as 年龄,
year(GetDate())-year(HireDate) as 工龄
From employees
Order by 年龄 desc,工龄 asc
9、检索单价在10.00到21.00之间所有产品的产品名称(ProductName)和单价(UnitPrice)。
Select ProductName,UnitPrice
From Products
Where UnitPrice between 10.00 and 21.00
10、products表存储的是产品的信息。
要求:查询products表中所有产品单价的平均值。
Select Avg(UnitPrice)
From Products
11、在Order Details表查询定购总数量多于1200的产品ID(ProductID)和定购数量(Quantity)。
Select ProductID,sum(Quantity)
From [order details]
Group by ProductID
Having sum(Quantity)>1200
12、查询products表中每类产品的单价平均值,要求查看类别ID,单价平均值,并只筛选出单价平均值超过30的数据。
Select CategoryID,avg(UnitPrice) as 单价平均值
From Products
Group by CategoryID
Having avg(UnitPrice)>30
1、从产品表Products当中查询列产品名称(ProductName),产品库存价值,(UnitPrice*UnitsInStock),并且使用汉字作为查询列的别名。
Select ProductName as 产品名称,UnitPrice*UnitsInStock as 产品库存价值
From Products
2、查询表Orders当中,定单日期(OrderDate)为7月份的定单。
Select * from Orders where Month(OrderDate) =7
3、查询表Employees中员工的LastName、年龄和工龄。
Select LastName,year(getdate())-year(BirthDate) as 年龄,
year(getdate())-year(HireDate) as 工龄
from Employees
4、查询表Employees中员工的LastName以’an’结束,FisrtName以’s’开头的员工信息。
Select *
From employees
Where LastName like '%an' and FirstName like 's%'
5、查询表Employees中员工的FisrtName以N或S开头的员工信息。
Select *
From employees
where Firstname like '[N,S]%'
6、查询表Customers中City值为London或Berlin或Madrid的客户信息。
Select *
From Customers
Where City in ('Berlin','London','Madrid')
7、查询表Customers中Region列为Null的客户公司名称(CompanyName), 联系人名称(ContactName),客户地址(Address)。
Select CompanyName,ContactName,Address
From customers
Where Region is null
8、查询表Employees的姓名(由LastName和FirstName组成),所在城市(City), 年龄和工龄, 并且要求员工的年龄按降序排列,员工的工龄按升序排列。
Select LastName+FirstName as 姓名,City as 城市,
year(GetDate())-year(BirthDate) as 年龄,
year(GetDate())-year(HireDate) as 工龄
From employees
Order by 年龄 desc,工龄 asc
9、检索单价在10.00到21.00之间所有产品的产品名称(ProductName)和单价(UnitPrice)。
Select ProductName,UnitPrice
From Products
Where UnitPrice between 10.00 and 21.00
10、products表存储的是产品的信息。
要求:查询products表中所有产品单价的平均值。
Select Avg(UnitPrice)
From Products
11、在Order Details表查询定购总数量多于1200的产品ID(ProductID)和定购数量(Quantity)。
Select ProductID,sum(Quantity)
From [order details]
Group by ProductID
Having sum(Quantity)>1200
12、查询products表中每类产品的单价平均值,要求查看类别ID,单价平均值,并只筛选出单价平均值超过30的数据。
Select CategoryID,avg(UnitPrice) as 单价平均值
From Products
Group by CategoryID
Having avg(UnitPrice)>30