Advanced Query - ADVANCED QUERIES PRACTICE FOR JOINS(INNER OUTER GROUP BY HAVING EXCEPT INTERSECT UNION Tables in Chapter 7 Database Queries 5-17

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

View Full Document Right Arrow Icon
ADVANCED QUERIES: PRACTICE FOR JOINS (INNER, OUTER) GROUP BY HAVING EXCEPT, INTERSECT, UNION Tables in Chapter 7 Database: Queries 5-17 use these tables. Query Editor in SQL Server: Query Editor Window
Background image of page 1

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

View Full DocumentRight Arrow Icon
For Queries 1-4, use AdventureWorks and the QUERY EDITOR IN SQL SERVER (Pictured above) 1. Note Alias. INNER JOIN returns only those rows that have matches in both tables. SELECT Person.Contact.FirstName + ' ' + Person.Contact.LastName AS [Full Name] FROM Person.Contact INNER JOIN HumanResources.Employee ON Person.Contact.ContactID = HumanResources.Employee.ContactID 2. Note that Title is NULL for some employees. SELECT Person.Contact.Title, Person.Contact.FirstName, Person.Contact.LastName , Person.Contact.Phone, HumanResources.Employee.HireDate FROM Person.Contact INNER JOIN HumanResources.Employee ON Person.Contact.ContactID = HumanResources.Employee.ContactID WHERE (HumanResources.Employee.HireDate > CONVERT(DATETIME, '2002-01-01 00:00:00', 102)) ORDER BY HumanResources.Employee.HireDate 3. Get NULL when concatenate title with name fields since title field contains some Nulls! SELECT Person.Contact.Title + ' ' + Person.Contact.FirstName+ ' ' + Person.Contact.LastName as [Full Name], Person.Contact.Phone, HumanResources.Employee.HireDate FROM Person.Contact INNER JOIN HumanResources.Employee ON Person.Contact.ContactID = HumanResources.Employee.ContactID WHERE (HumanResources.Employee.HireDate > CONVERT(DATETIME, '2002-01-01 00:00:00', 102)) ORDER BY HumanResources.Employee.HireDate 4. When need data from 3 tables, need 2 sets of join conditions. Need to join first two tables and join result to third table. SELECT HumanResources.Employee.EmployeeID, Person.Contact.FirstName, Person.Contact.LastName,
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 09/21/2011 for the course CGS 2545 taught by Professor Markllewellyn during the Summer '10 term at University of Central Florida.

Page1 / 4

Advanced Query - ADVANCED QUERIES PRACTICE FOR JOINS(INNER OUTER GROUP BY HAVING EXCEPT INTERSECT UNION Tables in Chapter 7 Database Queries 5-17

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