-
INNER JOIN (example)
- GO
- SELECT *
- FROM HumanResources.Employee AS e
- INNER JOIN Person.Person AS p
- ON e.BusinessEntityID = p.BusinessEntityID
-
LEFT OUTER JOIN (example)
- SELECT p.Name, pr.ProductReviewID
- FROM Production.Product p
- LEFT OUTER JOIN Production.ProductReview pr
- ON p.ProductID = pr.ProductID
-
RIGHT OUTER JOIN (example)
- SELECT st.Name AS Territory, sp.BusinessEntityID
- FROM Sales.SalesTerritory st
- RIGHT OUTER JOIN Sales.SalesPerson sp
- ON st.TerritoryID = sp.TerritoryID
-
FULL OUTER JOIN (example)
- -- The OUTER keyword following the FULL keyword is optional.
- SELECT p.Name, sod.SalesOrderID
- FROM Production.Product p
- FULL OUTER JOIN Sales.SalesOrderDetail sod
- ON p.ProductID = sod.ProductID
- WHERE p.ProductID IS NULL
- OR sod.ProductID IS NULL
-
ORDER BY (example)
- SELECT Name, ProductNumber, ListPrice AS Price
- FROM Production.Product
- ORDER BY Name ASC;
-
GROUP BY (example)
- SELECT Country, Region, SUM(sales) AS TotalSales
- FROM Sales
- GROUP BY Country, Region;
-
HAVING (example)
- SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
- FROM Sales.SalesOrderDetail
- GROUP BY SalesOrderID
- HAVING SUM(LineTotal) > 100000.00
- ORDER BY SalesOrderID
-
SUM(<attribute>) (example)
- SELECT Country, Region, SUM(sales) AS TotalSales
- FROM Sales
- GROUP BY Country, Region; <-- USE AGGREGATE
-
AVG(<attribute>) (AVERAGE)(example)
- SELECT AVG(VacationHours) AS 'Average vacation hours',
- SUM(SickLeaveHours) AS 'Total sick leave hours'
- FROM HumanResources.Employee
- WHERE JobTitle LIKE 'Vice President%';
-
UNION (example)
- SELECT ProductModelID, Name
- FROM Production.ProductModel
- WHERE ProductModelID NOT IN (3, 4)
- UNION
- SELECT ProductModelID, Name
- FROM dbo.Gloves
- ORDER BY Name;
|
|
