1) Develop a statement using 1992 syntax for a 2-table join. Show the results of your statement.
SELECT sales.DateSold, sales.Salesperson, sales.Color, sales.Year, customer.FirstName,
customer.LastName
FROM customer, Sales
WHERE customer.[CustomerID] = Sales.[CustomerID] AND sales.[CustomerID] = '2';
yields:
DateSold Salesperson Color Year FirstName LastName
10/20/2000 1 Green 0 Ferrin Rusty
10/17/2000 2 red 2000 Ferrin Rusty
10/17/2000 2 yellow 1999 Ferrin Rusty
10/19/2000 1 black 2000 Ferrin Rusty
It works.
2) Develop a statement using inner join syntax. Show the results of your statement.
SELECT [Sales].[DateSold], [Sales].[Type], [Sales].[SalespersonID], [Salesperson].[Salesperson]
FROM Salesperson INNER JOIN Sales ON [Sales].[SalespersonID]=[Salesperson].[SalespersonID]
WHERE [Sales.SalespersonID]='1';
Since my data types for my numbers are all text, i have to put single quotes around my 1. Others shouldn't have this problem. I realize, however, this will prevent me from doing calculations.
Anyway, I get:
DateSold Type SalespersonID Salesperson
10/20/2000 standard 1 Griffin
10/16/2000 Automatic 1 Griffin
10/19/2000 Automatic 1 Griffin
10/19/2000 Automatic 1 Griffin
3) In the previous lesson, I asked you to write a delete statement which would
affect a key value a second table. I also asked you to write a delete statement
for the second table which the statement would affect, but to not delete records
in the second table. Please write an update statement for the second table which
would change the field affected into a valid value, apply the update to your
database, and show the results.
Update Sales Set [SalespersonID] = '4' Where [SalespersonID] ='2'
InvoiceNumber Type Color Year Warranty? Type of Sale DateSold SalespersonID
CustomerID
1 standard green 2000 No credit 10/16/2000 4 4
2 standard red 2000 No cash 10/16/2000 3 4
3 Automatic red 2000 No credit 10/16/2000 1 5
4Automatic red 2000 No credit 10/17/2000 4 2
5Automatic yellow 1999 No credit 10/17/2000 4 2
6 Automatic black 1999 No credit 10/19/2000 1 1
7 Automatic black 2000 Yes credit 10/19/2000 3 1
8 standard red 2000 No cash 10/16/2000 4 5
9Automatic black 2000 Yes credit 10/19/2000 1 2
10standard Green 0 No cash 10/20/2000 11
11 Automatic Orange 2000 Yes Credit 10/19/2000 4 3
12 Standard Green 2000 Yes cash 10/20/2000 3 3
13Standard Green 2000 Yes Credit 10/19/2000 4 3
Worked.
4) Develop an update statement which would update two different fields in one
table. Show the results.
Update Salesperson, sales
SET Salesperson.SalespersonID = '4', Sales.SalespersonID = '4'
WHERE Salesperson.SalespersonID = Sales.SalespersonID
AND Sales.SalespersonID = '1'
Sales:
InvoiceNumber Type Color Year Warranty? Type of Sale DateSold SalespersonID
CustomerID
1 standard green 2000 No credit 10/16/2000 4 4
2 standard red 2000 No cash 10/16/2000 3 4
4 Automatic red 2000 No credit 10/16/2000 4 5
5 Automatic red 2000 No credit 10/17/2000 4 2
6 Automatic yellow 1999 No credit 10/17/2000 4 2
6 Automatic black 1999 No credit 10/19/2000 4 1
7 Automatic black 2000 Yes credit 10/19/2000 3 1
8 standard red 2000 No cash 10/16/2000 4 5
8 Automatic black 2000 Yes credit 10/19/2000 4 2
9 standard Green 0 No cash 10/20/2000 4 2
10 Automatic Orange 2000 Yes Credit 10/19/2000 4 3
11 Standard Green 2000 Yes cash 10/20/2000 3 3
12 Standard Green 2000 Yes Credit 10/19/2000 4 3
and the Salesperson table:
SalespersonID Salesperson
2 Jundy
3 Harris
4 Griffin
So no more 1s. Sigh.