Benutzer:MovGP0/SQL/Select

SELECT

[Database].[Schema].[Object]

USE: Connect to Database

USE AdventureWorks;

Retrieve specific Columns

SELECT LoginID, SureName, FamilyName
FROM HumanResources.Employee;

Retrive all Columns

SELECT *
FROM HumanResources.Employee;

Operators: Speficy the Rows to be returned

SELECT Title, FirstName, LastName
FROM HumanResources.Employee
WHERE Title = 'Ms.' 
AND  ( LastName = 'Miller' OR LastName = 'Smith' );

Operator	Description
!=	not equal
!>	not greater than (less or equal)
!<	not less than (greater or equal)
<	less than
<=	less than or equal
<>	not equal
=	equal
>	greater than
>=	greater than or equal

Rename Output Columns

SELECT BusinessEntityID AS "Employee ID", -- ISO Standard uses double quotes
       VacationHours "Vacation", -- AS can be omitted
       SickLeaveHours = [Sick Time] -- SQL Server also supports square brackets; AS can be replaced by =
FROM HumanResources.Employee;

Building Column from Expression

SELECT BusinessEntityID AS EmployeeID, 
       VacationHOurs + SickLeaceHours AS AvailableTimeOff -- omitting AS results in column without name
FROM HumanResources.Employee;

Providing Shorthand for Tables

SELECT E.BusinessEntityID AS "Employee ID", 
       E.VacationHours AS "Vacation"
       E.SickLeaveHours AS "Sick Time"
FROM HumanResources.Employee AS E;

NOT: Negating a Search Condition

SELECT Title, FirstName, LastName
FROM Person.Person
WHERE NOT (Title = 'Ms.' OR Title = 'Mrs.');

BETWEEN: Specify a Range of Values

SELECT SalesOrderID, ShipDate
FROM Sales.SalesOrderHeader
WHERE ShipDate BETWEEN '2005-07-23T00:00:00' AND '2005-07-24T23:59:59'

IS: Check for NULL

SELECT ProductID, Name, Weight
FROM Production.Product
WHERE Weight IS NULL;

IN: Provide a List of Values

SELECT ProductID, Name, Color
FROM Production.Poduct
WHERE Color IN ('Red', 'Blue', 'Yellow')

equal to:

SELECT ProductID, Name, Color
FROM Production.Poduct
WHERE Color = 'Red' OR Color = 'Blue' OR Color= 'Yellow')

LIKE: Wildcards

SELECT ProductID, Name
FROM Production.Product
WHERE Name LIKE '%B'

Wildcard	Description
*	any number of characters
%	0 or more characters
_	single character
[A-CF-F]	single character from a list
[^A-C]	single character not from list

ESCAPE: define Escape Character

SELECT ProductID, Name
FROM Production.Product
WHERE Description LIKE '\%' ESCAPE '\' -- defines \ as the escape character. searches for '%'

ORDER BY: Order Results

SELECT p.Name, h.EndData, h.ListPrice
FROM Production.Product AS p
   INNER JOIN Production.ProductListPriceHistory AS h
   ON p.ProductID = h.ProductID
ORDER BY p.Name, h.EndDate; -- sort by Name; if the name is equal, sort by EndDate

ASC/DESC: Specify Sort Order

SELECT p.Name, h.EndData, h.ListPrice
FROM Production.Product AS p
   INNER JOIN Production.ProductListPriceHistory AS h
   ON p.ProductID = h.ProductID
ORDER BY p.Name DESC, -- or: DESCENDING
         h.EndDate ASC; -- or: ASCENDING

Advanced Sort Order

SELECT p.ProductID, p.Name, p.Color
FROM Production.Product AS p
WHERE p.Color IS NOT NULL
ORDER BY CASE p.Color
   WHEN 'Red' THEN NULL -- 'Red' will be sorted as null (first), but not filted by WHERE
   ELSE p.Color
   END;

variant:

SELECT p.ProductID, p.Name, p.Color
FROM Production.Product AS p
WHERE p.Color IS NOT NULL
ORDER BY CASE LOWER(p.Color) -- 'Red', 'red' and 'RED' are handeled the same
   WHEN 'red' THEN ' '       -- 'red' ist ordered first
   ELSE LOWER(p.Color)       -- result column is still uppercase
   END;

OFFSET/FETCH Paging

make queries deterministic
- same query should result in same result later
- use order by
- isolate query with transaction when required
think about trading consistency for performance

SET TRANSACTION ISOLATION LEVEL SNAPSHOT; -- make sure that table does not change during transaction
BEGIN TRANSACTION;

SELECT ProductID, Name
FROM Production.Product
ORDER BY Name
OFFSET 8 ROWS            -- skip 8 rows
FETCH NEXT 10 ROWS ONLY; -- take 10 rows

COMMIT; -- commit transaction