1) Prepare a 3-table join not using aliases, using both 92 and inner join syntax. Show the results.
SELECT Sales.SalespersonID, Salesperson.Salesperson, Sales.Year, Sales.Color,
Sales.DateSold, customer.Firstname, customer.LastName FROM Sales, customer,
Salesperson WHERE Sales.SalespersonID=Salesperson.SalespersonID AND Sales.CustomerID=customer.CustomerID;
yeilds this:
SalespersonID Salesperson Year Color DateSold Firstname LastName
4 Griffin 0 Green 10/20/2000 Ferrin Rusty
4 Griffin 2000 Orange 10/19/2000 Gennifer James
3 Harris 2000 Green 10/20/2000 Gennifer James
4 Griffin 2000 Green 10/19/2000 Gennifer James
4 Griffin 2000 green 10/16/2000 Spartacus Vargas
3 Harris 2000 red 10/16/2000 Spartacus Vargas
4 Griffin 2000 red 10/16/2000 Herrald Hanis
4 Griffin 2000 red 10/16/2000 Herrald Hanis
4 Griffin 2000 red 10/17/2000 Ferrin Rusty
4 Griffin 1999 yellow 10/17/2000 Ferrin Rusty
4 Griffin 1999 black 10/19/2000 Suzanne Richards
3 Harris 2000 black 10/19/2000 Suzanne Richards
4 Griffin 2000 black 10/19/2000 Ferrin Rusty
Cool!
Now an inner join using three tables:
Select Sales.InvoiceNumber, Sales.Type, Sales.Year, Salesperson.Salesperson,
Customer.FirstName, Customer.LastName
FROM (Sales INNER JOIN Salesperson ON Sales.SalespersonID = Salesperson.SalespersonID)
INNER JOIN Customer ON Sales.CustomerID = Customer.CustomerID
yields:
InvoiceNumber Type Year Salesperson FirstName LastName
11 standard 0 Griffin Ferrin Rusty
12 Automatic 2000 Griffin Gennifer James
13 Standard 2000 Harris Gennifer James
14 Standard 2000 Griffin Gennifer James
1 standard 2000 Griffin Spartacus Vargas
2 standard 2000 Harris Spartacus Vargas
9 standard 2000 Griffin Herrald Hanis
4 Automatic 2000 Griffin Herrald Hanis
5 Automatic 2000 Griffin Ferrin Rusty
6 Automatic 1999 Griffin Ferrin Rusty
7 Automatic 1999 Griffin Suzanne Richards
8 Automatic 2000 Harris Suzanne Richards
10 Automatic 2000 Griffin Ferrin Rusty
2) If necessary, add some records to your tables which have no matching keys
in the other tables. Please display any new records added to your tables.Prepare
a left outer join.
I added these:
InvoiceNumber Type Color Year Warranty? Type of Sale DateSold SalespersonID
CustomerID
13 Standard Red 2001 Yes Credit 10/19/2001 1
14 Automatic Red 2001 Yes Cash 10/20/2001 1
these have no Salesperson ID number in the sales table.
SELECT Sales.InvoiceNumber, Sales.DateSold, Salesperson.SalespersonID
FROM salesperson LEFT JOIN Sales ON Sales.SalespersonID = Salesperson.SalespersonID;
InvoiceNumber DateSold SalespersonID
8 10/19/2000 4
6 10/19/2000 4
6 10/17/2000 4
5 10/17/2000 4
4 10/16/2000 4
8 10/16/2000 4
1 10/16/2000 4
12 10/19/2000 4
10 10/19/2000 4
9 10/20/2000 4
2
7 10/19/2000 3
2 10/16/2000 3
11 10/20/2000 3
Since I got rid of all the 1s in the Salesperson table in the last week's exercise, this seems to work--it's not displaying anything that was sold by a 1.
, right outer join
Now a right outer join. OK. Behold my three table example.
SELECT Sales.DateSold, Sales.Type, Sales.SalespersonID, Customer.FirstName,
Customer.LastName
FROM (Salesperson RIGHT JOIN Sales ON Salesperson.SalespersonID = Sales.SalespersonID)
INNER JOIN Customer ON Sales.CustomerID = Customer.CustomerID;
yields:
DateSold Type SalespersonID FirstName LastName
10/20/2000 standard 4 Ferrin Rusty
10/19/2000 Automatic 4 Gennifer James
10/20/2000 Standard 3 Gennifer James
10/19/2000 Standard 4 Gennifer James
10/16/2000 standard 4 Spartacus Vargas
10/16/2000 standard 3 Spartacus Vargas
10/16/2000 standard 4 Herrald Hanis
10/16/2000 Automatic 4 Herrald Hanis
10/17/2000 Automatic 4 Ferrin Rusty
10/17/2000 Automatic 4 Ferrin Rusty
10/19/2000 Automatic 4 Suzanne Richards
10/19/2000 Automatic 3 Suzanne Richards
10/19/2000 Automatic 4 Ferrin Rusty
10/19/2001 Standard Suzanne Richards
10/20/2001 Automatic Suzanne Richards
There is no Salesperson ID for the last two.
and full outer join.
Here I go using full outer join. Kids, this requires adult supervision.
SELECT Sales.DateSold, Salesperson.SalespersonID
FROM Sales LEFT JOIN Salesperson ON Sales.SalespersonID = Salesperson.SalespersonID
UNION SELECT Sales.DateSold, Salesperson.SalespersonID
FROM Sales RIGHT JOIN Salesperson ON Salesperson.SalespersonID = Sales.SalespersonID;
yields:
DateSold SalespersonID
2
10/16/2000 3
10/16/2000 4
10/17/2000 4
10/19/2000 3
10/19/2000 4
10/20/2000 3
10/20/2000 4
10/19/2001
10/20/2001