Transcript MySql - Creating donorof database offline

MySQL - Creating donorof
database offline
Please use speaker notes for
additional information.
First, I am creating the database called donorof and then I am creating a table within the
database called donorofl. Note the use which selects the correct database before
creating the table.
The SQL command desc donorofl shows a description of the table that I just
created. Note that idno is the primary key.
I have just inserted a record into the table donorof1. After inserting the record, I
did a select all from the table to show me the record I just inserted.
I have now inserted 3 more records into the table. Note that Carl Hersey has no yrgoal
so I entered null - another option would have been to enter an empty string.
A quote from the MySql manual “Both statements insert a value into the phone column, but the first
inserts a NULL value and the second inserts an empty string. The meaning of the first can be regarded
as ``phone number is not known'' and the meaning of the second can be regarded as ``she has no phone''.”
I am updating the donorofl table and setting the yrgoal equal to the empty string on
the record that has an idno of 22222. The where is critical here, without it I would
have changed the yrgoal on all of the records - not a good plan!
Results of the update.
The select is only showing 4 columns: idno, name, stadr and city.
The where means it is only showing the records/rows where the city is
equal to Fall River.
In this example, I want the five specified
fields to show for all records/rows where the
yrgoal is either greater than or equal to 100.
One of these queries uses the and and one of these queries
uses the or.
N
Y
yrgoal > 75
N
N
datefst >
2001-01-01
city =
Fall River
Y
Select
Y
Select
We have now added the fifth record.
From
donationofl
From
driveofl
From Oracle:
SQL> select donor.idno, name, yrgoal, contamt, donationone.driveno, drivename, lastyear
2 from donor, donationone, driveone
3 where donor.idno = donationone.idno and donationone.driveno = driveone.driveno;
IDNO
----11111
23456
12121
33333
NAME
YRGOAL
CONTAMT DRI DRIVENAME
LASTYEAR
--------------- --------- --------- --- --------------- --------Stephen Daniels
500
25 100 Kids Shelter
10000
Susan Ash
100
20 100 Kids Shelter
10000
Jennifer Ames
400
40 200 Animal Home
5000
Nancy Taylor
50
10 300 Health Aid
7000