Zlatko Michailov

Entities

SELECT c

FROM AdventureWorks.Culture AS c

WHERE c.CultureID IN {'en', 'es', 'fr'};

SELECT VALUE c

FROM AdventureWorks.Culture AS c

WHERE c.CultureID IN {'en', 'es', 'fr'};

AdventureWorks.Department(

        CAST(100 AS Edm.Int16), 'Dyn. Dept',

        'Dyn. Group', Edm.GetDate());

SELECT VALUE AdventureWorks.Department(

        rs.ID, rs.Name, rs.[Group], rs.[Date])

FROM

       ({ROW(CAST(101 AS Edm.Int16) AS ID,

                   'Dyn. Dept 1' AS Name,

                   'Dyn. Group' AS [Group],

                   Edm.GetDate() AS [Date])}

         UNION ALL

         {ROW(CAST(102 AS Edm.Int16),

                    'Dyn. Dept 2', 'Dyn. Group',

                    Edm.GetDate())}

         UNION ALL

         {ROW(CAST(103 AS Edm.Int16),

                    'Dyn. Dept 3', 'Dyn. Group',

                    Edm.GetDate())}

) AS rs;

Functions

SELECT c.ContactID,

       -- Canonical:

       Length(c.FirstName) AS FirstNameLength,

       -- Canonical:

       Edm.Length(c.LastName) AS LastNameLength,

       -- Provider-specific:

       SqlServer.LEN(c.EmailAddress) AS EmailAddressLength

FROM AdventureWorks.Contact AS c

WHERE c.ContactID BETWEEN 10 AND 12;

Keys/References

SELECT VALUE KEY(c)

FROM AdventureWorks.Culture AS c

WHERE c.CultureID IN {'en', 'es', 'fr'};

SELECT VALUE REF(c)

FROM AdventureWorks.Culture AS c

WHERE c.CultureID IN {'en', 'es', 'fr'};

SELECT VALUE DEREF(REF(c))

FROM AdventureWorks.Culture AS c

WHERE c.CultureID IN {'en', 'es', 'fr'};

Navigation + Nesting

SELECT e.EmployeeID,

             -- to 1:

             e.Contact.FirstName, e.Contact.LastName,

             -- to many:

            (SELECT eph.RateChangeDate, eph.Rate

              FROM e.EmployeePayHistory AS eph) AS

                                                            PayHistory

FROM AdventureWorks.Employee AS e

WHERE e.EmployeeID IN {4, 6};

Paging/TOP

SELECT TOP(3) c.ContactID, c.FirstName, c.LastName

FROM AdventureWorks.Contact AS c

WHERE c.ContactID >= 10;

SELECT c.ContactID, c.FirstName, c.LastName

FROM AdventureWorks.Contact AS c

ORDER BY c.ContactID

SKIP 9 LIMIT 3;

Grouping

SELECT c.FirstName, c.LastName, epc.PayChanges

FROM

         (SELECT eph.EmployeeID,

                      Count(eph.EmployeeID) AS PayChanges

          FROM AdventureWorks.EmployeePayHistory AS eph

          GROUP BY eph.EmployeeID

          HAVING Count(eph.EmployeeID)

                        >= 3) AS epc

         JOIN AdventureWorks.Contact AS c

              ON epc.EmployeeID = c.ContactID;