sql part 2 multiple tables n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SQL – Part 2 Multiple Tables PowerPoint Presentation
Download Presentation
SQL – Part 2 Multiple Tables

Loading in 2 Seconds...

play fullscreen
1 / 8

SQL – Part 2 Multiple Tables - PowerPoint PPT Presentation


  • 112 Views
  • Uploaded on

SQL – Part 2 Multiple Tables. CIS 324 – Chapter 5. Sub-Queries. Example – We need the names of all employees (not their employee number) for all employees who worked more than 40 hours on an assignment.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SQL – Part 2 Multiple Tables' - arleen


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
sql part 2 multiple tables

SQL – Part 2 Multiple Tables

CIS 324 – Chapter 5

sub queries
Sub-Queries

Example – We need the names of all employees (not their employee number) for

all employees who worked more than 40 hours on an assignment.

Sub-Queries – Nested Queries (Effective when the results ultimately comes from ONE table)

SELECT Name

FROM EMPLOYEE

WHERE EmployeeNumber IN

(SELECT DISTINCT EmployeeNum

FROM ASSIGNMENT

WHERE HoursWorked > 40);

sub queries con t
Sub Queries (con’t)

Multiple nesting is allowed: What if we only need Accounting projects from the

above example:

SELECT Name

FROM EMPLOYEE

WHERE EmployeeNumber IN

(SELECT DISTINCT EmployeeNum

FROM ASSIGNMENT

WHERE ProjectID IN

( SELECT ProjectID

FROM PROJECT

WHERE Department = ‘Accounting’));

joins
Joins

When we need to display data from 2 or more tables

SELECT Name, HoursWorked

FROM EMPLOYEE, ASSIGNMENT

WHERE EmployeeNumber = EmployeNum;

This creates a new table with Name from the EMPLOYEE table and

HoursWorked from the ASSIGNMENT table when the condition of matching employee numbers occurs.

A join is just another table so all earlier SQL statements are available for use.

(Group BY, WHERE, etc.)

multiple table joins
Multiple Table Joins

We can join more than 2 tables together:

SELECT Project.Name, HoursWorked, EMPLOYEE.Name

FROM PROJECT, ASSIGNMENT, EMPLOYEE

WHERE PROJECT.ProjectID = ASSIGNMENT.ProjectID

AND EMPLOYEE.EmployeeNumber = ASSIGNMENT.EmployeeNum;

joins con t
Joins (con’t)

If two columns in separate tables have the same column name you will need to indicate the table name and column name in the WHERE statement:

SELECT Name, HoursWorked

FROM PROJECT, ASSIGNMENT

WHERE PROJECT.ProjectID = ASSIGNMENT.ProjectID;

If not all rows in both tables have a match in the join condition – these rows will not appear in the join table.

outer joins
Outer Joins

OUTER JOIN – Not part of SQL Standard but supported by

most DBMS – Solution to dropping data in standard join

An outer join appends the rows in the select statement onto the existing table on either the left or right side:

SELECT Name, HoursWorked

FROM PROJECT LEFT JOIN ASSIGNMENT

WHERE PROJECT.ProjectID = ASSIGNMENT.ProjectID;

sample outer join
Sample Outer Join

This appends the name of the project to the left side of the assignment table. The unmatched rows will receive a null value:

Q3 Portfolio Analysis 17.50

Q3 Portfolio Analysis 12.50

Q3 Portfolio Analysis 8.00

Q3 Portfolio Analysis 20.25

Q3 Tax Prep 45.75

Q3 Tax Prep 70.50

Q3 Tax Prep 40.50

Q4 Product Plan 75.00

Q4 Product Plan 20.25

Q4 Product Plan 25.25

Q4 Portfolio Analysis null