love
lovee--1
select distinct firstname
from person.Person
order by firstname asc
--2
select * from Production.product
where name like 'F%'
order by ProductID desc
--3
Select proIn.quantity, plo.Name, plo.LocationID, datediff(day, proIn.ModifiedDate, getdate()) as [days since last modification]
from Production.productInventory proIn join Production.location plo on proIn.LocationID=plo.LocationID
where datediff(day, proIn.ModifiedDate, getdate()) between 1600 and 2700 order by datediff(day, proIn.ModifiedDate, getdate()) asc
--4
select color, count(name) as [Number of products], sum(ListPrice) as [total ListPrice]
from Production.Product
group by color
having color is not null
order by count(name) desc
--5
Select he.businessEntityID as [Employee Id],
pp.FirstName + ‘ ’ + pp.LastName as Name,
he.VacationHours,
avg(he.VacationHours) over () as AverageVacationHours
from HumanResource.Employee he
join Person.person pp
on he.BusinessEntityID = pp.BusinessEntityID
where he.VacationHours > (select avg(VacationHours) from HumanResource.Employee)
order by he.VacationHours desc
--6
select sum(ss.SubTotal) as SumSubTotal, avg(ss.TaxAmt) as AVGTAXAMT, min(ss.Freight) as MinFreight, st.Name as Territory
from Sales.SalesOrderHeader ss join Sales.SalesTerritory st on ss.TerritoryID = st.TerritoryID
where ss.OrderDate>'2014-03-29' group by st.Name having sum(ss.SubTotal) >1100000;
--7
Create View View_Name As
Select *
From employee
Update View_Name
Set firstName=’Michael’
Where LastName =”Jackson”;
--8
SELECT hre.NationalIDNumber [National ID], pp.FirstName [First Name], pp.LastName [Last Name],
hre.JobTitle [Job
Title], hrd.Name [Department]
FROM Person.Person
pp
JOIN
HumanResources.Employee hre
ON
pp.BusinessEntityID = hre.BusinessEntityID
JOIN
HumanResources.Department hrd
ON
pp.BusinessEntityID = hrd.DepartmentID
WHERE
hre.BusinessEntityID <>All(select BusinessEntityID from Sales.SalesPerson)