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 DocumentRight Arrow Icon

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

View Full DocumentRight Arrow Icon

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

View Full DocumentRight Arrow Icon

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

View Full DocumentRight Arrow Icon

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

View Full DocumentRight 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 Todays 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

This note was uploaded on 12/17/2010 for the course MIS 03955 taught by Professor Mote during the Fall '10 term at University of Texas at Austin.

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
Ask a homework question - tutors are online