SQL SERVER Example

  1. INNER JOIN (example)
    • GO
    • SELECT *
    • FROM HumanResources.Employee AS e
    •     INNER JOIN Person.Person AS p
    •     ON e.BusinessEntityID = p.BusinessEntityID
  2. LEFT OUTER JOIN (example)
    • SELECT p.Name, pr.ProductReviewID
    • FROM Production.Product p
    • LEFT OUTER JOIN Production.ProductReview pr
    • ON p.ProductID = pr.ProductID
  3. 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
  4. 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
  5. ORDER BY (example)
    • SELECT Name, ProductNumber, ListPrice AS Price
    • FROM Production.Product
    • ORDER BY Name ASC;
  6. GROUP BY (example)
    • SELECT Country, Region, SUM(sales) AS TotalSales
    • FROM Sales
    • GROUP BY Country, Region;
  7. HAVING (example)
    • SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
    • FROM Sales.SalesOrderDetail
    • GROUP BY SalesOrderID
    • HAVING SUM(LineTotal) > 100000.00
    • ORDER BY SalesOrderID
  8. SUM(<attribute>) (example)
    • SELECT Country, Region, SUM(sales) AS TotalSales
    • FROM Sales
    • GROUP BY Country, Region; <-- USE AGGREGATE
  9. 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%';
  10. UNION  (example)
    • SELECT ProductModelID, Name  
    • FROM Production.ProductModel  
    • WHERE ProductModelID NOT IN (3, 4)  
    • UNION  
    • SELECT ProductModelID, Name  
    • FROM dbo.Gloves  
    • ORDER BY Name;
Author
geschw66
ID
335515
Card Set
SQL SERVER Example
Description
SQL Examples
Updated