Benutzer:MovGP0/SQL/Select

aus Wikipedia, der freien Enzyklopädie
Zur Navigation springen Zur Suche springen
[Database].[Schema].[Object]

USE: Connect to Database

[Bearbeiten | Quelltext bearbeiten]
USE AdventureWorks;

Retrieve specific Columns

[Bearbeiten | Quelltext bearbeiten]
SELECT LoginID, SureName, FamilyName
FROM HumanResources.Employee;

Retrive all Columns

[Bearbeiten | Quelltext bearbeiten]
SELECT *
FROM HumanResources.Employee;

Operators: Speficy the Rows to be returned

[Bearbeiten | Quelltext bearbeiten]
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

[Bearbeiten | Quelltext bearbeiten]
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

[Bearbeiten | Quelltext bearbeiten]
SELECT BusinessEntityID AS EmployeeID, 
       VacationHOurs + SickLeaceHours AS AvailableTimeOff -- omitting AS results in column without name
FROM HumanResources.Employee;

Providing Shorthand for Tables

[Bearbeiten | Quelltext bearbeiten]
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

[Bearbeiten | Quelltext bearbeiten]
SELECT Title, FirstName, LastName
FROM Person.Person
WHERE NOT (Title = 'Ms.' OR Title = 'Mrs.');

BETWEEN: Specify a Range of Values

[Bearbeiten | Quelltext bearbeiten]
SELECT SalesOrderID, ShipDate
FROM Sales.SalesOrderHeader
WHERE ShipDate BETWEEN '2005-07-23T00:00:00' AND '2005-07-24T23:59:59'

IS: Check for NULL

[Bearbeiten | Quelltext bearbeiten]
SELECT ProductID, Name, Weight
FROM Production.Product
WHERE Weight IS NULL;

IN: Provide a List of Values

[Bearbeiten | Quelltext bearbeiten]
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

[Bearbeiten | Quelltext bearbeiten]
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

[Bearbeiten | Quelltext bearbeiten]
SELECT ProductID, Name
FROM Production.Product
WHERE Description LIKE '\%' ESCAPE '\' -- defines \ as the escape character. searches for '%'

ORDER BY: Order Results

[Bearbeiten | Quelltext bearbeiten]
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

[Bearbeiten | Quelltext bearbeiten]
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

[Bearbeiten | Quelltext bearbeiten]
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

[Bearbeiten | Quelltext bearbeiten]
  • 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