Entity SQL – Quick Reference


 




























Literals


 


“abc”








Value


abc



‘abc’








Value


abc



1








Value


1



{2}








Value


2



{3, 4, 5}












Value


3


4


5


















Rows


 


ROW(1 AS i, ‘abc’ AS s)










i


s


1


abc



SELECT ROW(1 AS i, ‘abc’ AS s) AS Row


FROM {11, 12, 13};
















Row










i


s


1


abc










i


s


1


abc










i


s


1


abc



{ROW(1 AS i, ‘abc’ AS s)}


UNION ALL


{ROW(2, ‘xyz’)};













i


s


1


abc


2


xyz




 




















Entities


 


SELECT c


FROM AdventureWorks.Culture AS c


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




















c












CultureID


Name


ModifiedDate


en


English


6/1/1998 12:00:00 AM












CultureID


Name


ModifiedDate


es


Spanish


6/1/1998 12:00:00 AM












CultureID


Name


ModifiedDate


fr


French


6/1/1998 12:00:00 AM



SELECT VALUE c


FROM AdventureWorks.Culture AS c


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




















CultureID


Name


ModifiedDate


en


English


6/1/1998 12:00:00 AM


es


Spanish


6/1/1998 12:00:00 AM


fr


French


6/1/1998 12:00:00 AM



AdventureWorks.Department(


        CAST(100 AS Edm.Int16), ‘Dyn. Dept’,


        ‘Dyn. Group’, Edm.GetDate());














DepartmentID


Name


GroupName


ModifiedDate


100


Dyn. Dept


Dyn. Group


6/20/2007 3:52:47 PM



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;
























DepartmentID


Name


GroupName


ModifiedDate


101


Dyn. Dept 1


Dyn. Group


6/20/2007 3:59:43 PM


102


Dyn. Dept 2


Dyn. Group


6/20/2007 3:59:43 PM


103


Dyn. Dept 3


Dyn. Group


6/20/2007 3:59:43 PM



 












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;
























ContactID


FirstNameLength


LastNameLength


EmailAddressLength


10


6


5


27


11


6


8


27


12


5


7


26



 


















Keys/References


 


SELECT VALUE KEY(c)


FROM AdventureWorks.Culture AS c


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














CultureID


en


es


fr



SELECT VALUE REF(c)


FROM AdventureWorks.Culture AS c


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
















C1


CultureID


0


en


0


es


0


fr



SELECT VALUE DEREF(REF(c))


FROM AdventureWorks.Culture AS c


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




















CultureID


Name


ModifiedDate


en


English


6/1/1998 12:00:00 AM


es


Spanish


6/1/1998 12:00:00 AM


fr


French


6/1/1998 12:00:00 AM



 












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};



















EmployeeID


FirstName


LastName


EmployeePayHistory


4


Rob


Walters
















RateChangeDate


Rate


1/5/1998 12:00:00 AM


8.6200


7/1/2000 12:00:00 AM


23.7200


1/15/2002 12:00:00 AM


29.8462


6


David


Bradley
















RateChangeDate


Rate


1/20/1998 12:00:00 AM


24.0000


8/16/1999 12:00:00 AM


28.7500


6/1/2002 12:00:00 AM


37.5000



 














Paging/TOP


 


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


FROM AdventureWorks.Contact AS c


WHERE c.ContactID >= 10;




















ContactID


FirstName


LastName


10


Ronald


Adina


11


Samuel


Agcaoili


12


James


Aguilar



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


FROM AdventureWorks.Contact AS c


ORDER BY c.ContactID


SKIP 9 LIMIT 3;




















ContactID


FirstName


LastName


10


Ronald


Adina


11


Samuel


Agcaoili


12


James


Aguilar



 











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;




























































FirstName


LastName


PayChanges


Humberto


Acevedo


3


Frances


Adams


3


Sean


Jacobson


3


Adam


Barr


3


Mary


Billstrom


3


Cornelius


Brandon


3


Shirley


Bruner


3


Megan


Burke


3


Stephen


Burton


3


Jovita


Carmody


3


Matthew


Cavallari


3


Charles


Christensen


3


Bart


Duncan


3