Assignment
1) Set up 3 different order by queries like below:
Select ItemDescription from Item Order By Cost
Select ItemDescription from Item Order By Cost DESC
Select ItemDescription, Cost from Item Order By ItemDescription, Cost
Display and analyze your results.

SELECT *
FROM Sales order by datesold;

InvoiceNumber Type Color Year Warranty? Type of Sale DateSold Salesperson Customer ID
10 Green 2000 No
9 standard Green 0 No
0 Automatic Green 1999 Yes Cash 10/13/2000 Griffin 1
3 Automatic White 2000 Yes Credit 10/15/2000 Griffin 4
4 Standard Teal 2001 Yes Cash 10/16/2000 Harris 5
2 Automatic Dark Red 2000 No Cash 10/16/2000 Jundy 3
1 Standard Red 1999 Yes Credit 10/16/2000 Jundy 2
8 standard black 2001 No credit 10/18/2000 Jundy 5
5 Standard Teal 2000 No Cash 10/18/2000 Harris 5
7 Standard Black 2001 Yes Credit 10/19/2000 4
6 Automatic Green 1999 Yes Credit 10/19/2000 Jundy 1

that worked.

SELECT color, datesold, type
FROM Sales order by datesold desc;

color datesold type
Black 10/19/2000 Standard
Green 10/19/2000 Automatic
black 10/18/2000 standard
Teal 10/18/2000 Standard
Teal 10/16/2000 Standard
Dark Red 10/16/2000 Automatic
Red 10/16/2000 Standard
White 10/15/2000 Automatic
Green 10/13/2000 Automatic
Green
Green standard

that worked.

SELECT color, datesold from sales Order By color, datesold;

color datesold
black 10/18/2000
Black 10/19/2000
Dark Red 10/16/2000
Green
Green
Green 10/13/2000
Green 10/19/2000
Red 10/16/2000
Teal 10/16/2000
Teal 10/18/2000
White 10/15/2000

that seemed to work.

2) Set up two different group by statements, one select a count(*), the other a sum({Field}), with no WHERE clause. Use the AS keyword to rename the count(*) and sum({Field}) columns in your output results. Display and analyze your results.

SELECT Sales.Salesperson, Count(*)
FROM Sales
GROUP BY Sales.Salesperson;

Salesperson Expr1001
3
Griffin 2
Harris 2
Jundy 4

I guess there were three with no salesperson, so that seems right.

Select Sum(InvoiceNumber) from sales;

gives me:

Expr1000
75

totally useless i know, but it was the only field that had "number" specified on a data type, and i was getting a data mismatch error trying to tabulate the number of red cars sold. (Access walked me through the error) I guess you can't sum those the way I have it now.

3) Set up a delete statement, that would also result in either changing or deleting a record from another table. Apply this first delete against your database. List the other resulting delete statement(s) (taking care of the foreign key record[s]) but do not apply the delete(s) against your database.

DELETE *
FROM Sales
WHERE DateSold<#10/19/2001#;

yielded a big goof....that's what happens when you don't read the directions.....

InvoiceNumber Type Color Year Warranty? Type of Sale DateSold Salesperson Customer ID
9 standard Green 0 No
10 Green 2000 No
10 Green 2000 No
10 Green 2000 No .