sql_day_1

------查找库存量少于订单量的产品-----------
select *
from Products
where UnitsInStock < UnitsOnOrder

-------查找年龄最大的员工---------------
select top 1 *
from Employees
order by Birthdate

select EmployeeId
from Employees
where BirthDate =
(
 select min(BirthDate) from Employees
)

-----求各类产品的平均价------
select CategoryId, avg(UnitPrice) as avgPrice
from Products
group by CategoryId

select
(select CategoryName from Categories where Categories.CategoryId = Products.CategoryId) as CategoryName,
round(avg(UnitPrice),2) as avgPrice
from Products
group by CategoryId

-------求员工平均年龄-----------
select avg(datediff(yyyy,BirthDate,getDate()))
from Employees

------创建视图----
create view ViewAvgAge
as
select avg(year(getdate())-year(BirthDate)) as AvgAge
from Employees

select * from ViewAvgAge

-----创建视图来求单价最高的产品----
create view Top1Products
as
select top 1* from Products
order by UnitPrice desc

select * from Top1Products

------更新视图里面的数据会影响基表里面的数据---------
update Top1Products set UnitPrice = 63.5 where ProductId = 38