SQL special cases - Today's Code - Special Cases Motes...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
Today's Code - Special Cases Mote’s Notes 3/9/2011 -- Correlation names SELECT E.FirstName + ' ' + E.LastName AS Employee, P.PayDate, P.NetAmount FROM tblEmployee E INNER JOIN tblPaycheck P ON E.EID = P.EID ORDER BY E.LastName, E.FirstName, P.PayDate DESC -- Employees and their supervisors SELECT Staff.FirstName + ' ' + Staff.LastName AS Employee, Boss.FirstName + ' ' + Boss.LastName AS Supervisor FROM tblEmployee Staff INNER JOIN tblEmployee Boss ON Staff.Supervisor = Boss.EID ORDER BY Staff.LastName, Staff.FirstName
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
-- EIDs paid in 2009 CREATE VIEW qryPaidIn2009 AS SELECT DISTINCT EID FROM tblPaycheck WHERE YEAR(PayDate) = 2009 -- EIDs paid in 2010 CREATE VIEW qryPaidIn2010 AS SELECT DISTINCT EID FROM tblPaycheck WHERE YEAR(PayDate) = 2010 -- EIDs paid in 2009, but not in 2010 CREATE VIEW qryPaidIn2009ButNotIn2010 AS SELECT qryPaidIn2009.EID FROM qryPaidIn2009 LEFT OUTER JOIN qryPaidIn2010 ON qryPaidIn2009.EID = qryPaidIn2010.EID WHERE qryPaidIn2010.EID IS NULL -- Employees paid in 2009, but not in 2010
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 05/26/2011 for the course MIS 325 taught by Professor Mote during the Spring '08 term at University of Texas at Austin.

Page1 / 4

SQL special cases - Today's Code - Special Cases Motes...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online