{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

Part 4- Special Cases

Part 4- Special Cases - SQL Select(Part 4 Special Cases...

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

View Full Document Right Arrow Icon

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

View Full Document Right Arrow Icon

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

View Full Document Right Arrow Icon

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

View Full Document Right Arrow Icon

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

View Full Document Right Arrow Icon

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

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: SQL Select (Part 4) Special Cases Today’s Agenda Correlation Names (Table Nicknames) Self Joins (Unary Relationships) Linked Queries (Multi-Step Tasks) Subqueries (Multi-Step Tasks) Unions, Intersections and Exceptions Correlation Names SELECT FROM WHERE ORDER BY fields tblX AS nicknameX , tblY AS nicknameY row condition fields Correlation Names SELECT FROM WHERE ORDER BY SELECT FROM ON WHERE ORDER BY fields tblX nicknameX , tblY nicknameY row condition fields fields tblX nickX INNER JOIN tblY nickY fieldX = fieldY row condition fields Correlation Names SELECT tblEmployee.FirstName + ' ' + tblEmployee.LastName AS Employee, tblPaycheck.PayDate, tblPaycheck.NetAmount FROM tblEmployee INNER JOIN tblPaycheck ON tblEmployee.EID = tblPaycheck.EID ORDER BY tblEmployee.LastName, tblEmployee.FirstName, tblPaycheck.PayDate DESC 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 Self Join Join a table to itself Employees and their supervisors EID- primary key of employee Supervisor- foreign key (pointing to “parent” boss) E mployees and their S upervisors SELECT E.FirstName + ' ' + E.LastName AS Employee, S.FirstName + ' ' + S.LastName AS Supervisor FROM tblEmployee E INNER JOIN tblEmployee S ON E.Supervisor = S.EID ORDER BY E.LastName, E.FirstName “Bosses” with no staff Employees with no boss Employee and Boss pairs E S Employees and their Supervisors SELECT Staff.FirstName + ' ' + Staff.LastName AS E mployee , B oss .FirstName + ' ' + B oss .LastName AS Supervisor FROM tblEmployee Staff INNER JOIN tblEmployee Boss ON Staff.Supervisor = B oss .EID ORDER BY Staff.LastName, Staff.FirstName “Bosses” with no staff Employees with no boss Employee and Boss pairs Staff Boss Employees and their Supervisors...
View Full Document

{[ snackBarMessage ]}

Page1 / 32

Part 4- Special Cases - SQL Select(Part 4 Special Cases...

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

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