SQL Week 2

Assignment
1) Write select statements using the like and not like clauses. Show & analyze your results.

SELECT [year]
FROM Sales
WHERE year not like "2000";

yields:

year
1999
1999
2001
1999
2001
2001

Notice I did not use any other fields, so all I got showing was simply the years and not any other revealing information. Not very interesting.

SELECT [year]
FROM Sales
WHERE year not like "2000";

yields:

year
1999
1999
2001
1999
2001
2001

Again, no other good info, but the query did work because none of the returns are "2000," so that's what I wanted.

2) Write select statements using the in and not in clauses. Show & analyze your results.

SELECT [color]
FROM Sales
WHERE color In ('Red','Teal');

yields:

color
Red
Teal
Teal

the three cars that were red or teal. Fine.

SELECT [year], [Color]
FROM Sales
WHERE year not in (2000);

year Color
1999 Green
1999 Red
2001 Teal
1999 Green
2001 Black
2001 black
0 Green

All these cars sold were not 2000 models, and I displayed their colors too.

3) Use the between clause for a date search on your database. Show & analyze your results.

SELECT [color], [year]
FROM Sales
WHERE datesold between #10/17/2000# and #10/20/2000#;

yields:

color year
Teal 2000
Green 1999
Black 2001
black 2001

all these cars were sold within these dates.

4) Write 2 select statements: one which connects at least two where conditions with an AND, the other which connects at least two where conditions with an OR. Show & analyze your results.

SELECT [datesold], [color]
FROM Sales
WHERE datesold=#10/18/2000# and color='red';

yielded nothing. There where only two cars sold on 10/18, and they were teal and black models. Observe the "sales" table:

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

SELECT Sales.Type, Sales.DateSold
FROM Sales
WHERE (((Sales.Type)='standard')) OR (((Sales.DateSold)=#10/18/2000#));

I didn't put in all those parens. I guess Access did that? But it worked:

Type DateSold
Standard 10/16/2000
Standard 10/16/2000
Standard 10/18/2000
Standard 10/19/2000
standard 10/18/2000
standard

I was looking for both cars with standard transmission and cars sold on 10/18, so both cars should have returned, and they did. Note two of the cars met both criteria.

5) Write 2 insert statements for your database: the first with all of the fields receiving a value, and the second with only select fields receiving a value.

Insert some:

INSERT INTO sales ( InvoiceNumber, [year], color )
VALUES (10, 2000, 'Green');

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

Insert All

INSERT INTO sales
VALUES (8, 'standard', 'black', 2001, 'false', 'credit', #10/18/2000#, 'Jundy', 5);

didn't work. I took the primary key out of the table and everything. Hmmmm. Got a key violation error.

However, after changing the table to allow duplicates and removing the quotes around false, i get:

i ran it a few times because i was excited:)