Transcript FROM - WVU CS101

DAY 21:
MICROSOFT ACCESS – CHAPTER 5
MICROSOFT ACCESS – CHAPTER 6
MICROSOFT ACCESS – CHAPTER 7
Akhila Kondai
[email protected]
October 30, 2013
ANNOUNCEMENTS
• Homework # 4 is due on 11/01/2013
• MyITLab Lesson D is due on 11/04/2013
• Homework # 5 is now posted and available at
http://cs101.wvu.edu/instructors/kondai/assignments/.
It would be due for 11/08/2013
• Start working on them right away!
• Email me or visit open lab for assistance in home works
or MyITLab.
• Data Validation Techniques
• Importing and Exporting Data
• Using SQL in Access
DATA VALIDATION TECHNIQUES
• Data validation is a set of constraints or
rules that help control how data is entered
into a field.
• Those are Field Properties.
• Each Data type has its own properties.
• Let us see some frequently used
Constraints or properties.
ESTABLISHING DATA VALIDATION
• Establish REQUIRED fields.
• Set DEFAULT VALUE for fields.
• Set VALIDATION RULE and generate
VALIDATION TEXT.
• Use INPUT MASK wizard.
• Look up Fields. ( already done in
homework's )
ESTABLISH REQUIRED FIELDS
•
•
•
•
•
The required fields must not be left blank.
Lets try
Open blank table in design view
Field 1 -> Student ID : Number
Field 2 -> Student Name : Text and in Properties set
“REQUIRED” to “YES”
• Switch to data sheet view and try to enter a record with
out giving Student Name, you will see an error
SET DEFAULT VALUE FOR
FIELDS.
•
•
•
•
•
Delete the records first
Switch to design view:
Field 3 -> Score : Number
Set DEFAULT VALUE property to 30
Observe records.
SET VALIDATION RULE AND
GENERATE VALIDATION TEXT.
• Validation rule is designed to restrict the data values that
can be entered into a field.
• Validation text informs users that the validation rule has
been broken, and how to correct it.
• Lets try : Set validation rule and text for SCORE FIELD
• Delete all the records first and remove the default value
set.
• Validation rule: [Score] <= 60
• Validation Text: You can’t enter a value greater than 60.
Please enter correct value.
• Now try to enter 90 in Score field for a record and
observe the warning message.
USE INPUT MASK WIZARD.
• Input Mask Wizard generates an input mask for a field
based on your answer to a few questions.
• Lets try: Delete all records
• Switch to Design view:
• Add a new field Phone Number: Text
• Note: works with Text/Date fields only
• Now click on Input Mask Wizard Option in Properties
• Choose Phone Number and go on…….
• Switch to Data Sheet view and try to add phone number
and see what's happening.
• Data Validation Techniques
• Importing and Exporting Data
• Using SQL in Access
IMPORTING DATA INTO ACCESS
• We can import data from Access / Excel /
Text / XML file and more…
• Note for TEXT file : comma to next column
and enter to next row. ( similarly to EXCEL)
EXPORTING DATA FROM
ACCESS
• You can export the data from number of
records in access to Excel / text / XML /
PDF / Access files and more ……
• Try those ……
• Data Validation Techniques
• Importing and Exporting Data
• Using SQL in Access
STRUCTURED QUERY LANGUAGE
(SQL)
• Standard computer language for retrieving
and updating data within database
systems. Database applications like MS
Access, DB2, MS SQL, Oracle, Sybase,
MySQL, etc. use SQL.
• We have been using graphical tools to
develop and manipulate database objects
thus far…
SQL CONTINUED…
• Access has written the SQL statements
behind the scenes for us.
• The SQL language can be separated into
two sub sets: Data Manipulation Language
(DML) and the Data Definition Language
(DDL).
• We will look mostly at DML aspects.
SQL KEYWORDS
16
SELECT STATEMENT
• Retrieves all of the data in the fields
specified from the specified database
table.
• Syntax is:
SELECT <columns> FROM <tables>
– <columns> is a comma separated list of
column names to be returned
– <tables> is the tables where the <columns>
are located…
SELECT STATEMENT
• The <columns> can be specified as *,
which will return all columns that are in the
<tables>.
• <columns> can also be aggregate
functions (i.e sum, count, etc)
• Let’s try it!
LET’S TRY IT THE OLD WAY
FIRST…
Obtain and open Customer_orders.accdb
Create a query in design view
Add the Customer table only
Add all fields
Run the query
Click “View” and change to “SQL View” to
see what was done behind the scenes…
SELECT EXERCISE
Close the Query without saving changes
Create a new query in design view.
Don’t add any tables
Change to “SQL View”
Type SELECT * FROM customer
Run the query by clicking “!”…
 Words in capital are “reserved words”
SELECT EXERCISE
Now, let’s revise our SQL SELECT…
Go back to SQL View and make it read:
SELECT first_name, last_name FROM
customer
Run the Query
We are shown only the fields we requested
from the table we told it to use…
ONE MORE…
Say we wanted to have it ALIAS a column for
us which combined first and last name field
values and separated them with a space…
 SELECT (first_name + “ ” +last_name) AS
[Full Name] FROM customer
 Run it!
We are presented a field that does not actually
exist in the table as we gave it as alias.
WHERE CLAUSE FOR CRITERIA
• This allows us to specify criteria at the
command line so that only things matching
will be returned.
• The following are legal operators:
=
<>
<
>
<=
>=
Between
Like
WHERE CLAUSE
Let’s modify our last select to add criteria
to it…
SELECT (first_name + " " + last_name) AS
[Full Name] FROM customer WHERE
last_name=“doe”
Run it.
Have a look in regular Design View and
widen out the first field.
See the alias name and the criteria
SQL CONTINUED
• DML – Data Manipulation Language
• DDL – Data Definition Language
• JOIN – Forms relationships between
tables
DATA MANIPULATION
LANGUAGE (DML)
• The DML is SQL Queries (or commands) that
will manipulate the data within a database:
– SELECT– extends or “selects” data from a
database table(s)
– UPDATE– changes or “updates” data from a
database table
– DELETE – removes or “deletes” row(s) from a
database table
– INSERT INTO – adds or “inserts” row(s) of data
into a database table
DATA DEFINITION LANGUAGE
(DDL)
• The DDL are the SQL commands that
define the structure of a database:
– CREATE TABLE – creates a database table
– DROP TABLE – deletes a database table
– ALTER TABLE – modifies a database table
JOIN CLAUSE
• Links tables together to form relationships
through the tables’ primary and foreign
keys
• There are three main types of joins:
– INNER JOIN
– LEFT JOIN
– RIGHT JOIN
INNER JOIN
• Returns all rows from both tables where
there is a match and will exclude the rows
where a match is NOT made
• We will now do an INNER JOIN for
customer and address tables.
• These tables are relational based on
primary and foreign keys.
INNER JOIN EXERCISE
• We will use the syntax as table.field to
specify what tables to pull field values
from.
Let’s look at the customer and address tables
to see the fields called “address_id” in each. .
.
 “address_id” is a primary key to address
table and foreign key to customer table.
INNER JOIN EXERCISE
• Let’s say we wanted to join the customers
with their addresses to make a multi-table
query . . .
• We would like the following fields
– [Customer] table
• first_name
• last_name
– [Address] table
• Street
• zipcode
INNER JOIN EXERCISE
• We need to create an inner join on the
customer and address tables on the
address_id common field
• When expressing the Join, the syntax will
include “customer.address_id” and
“address.address_id”
...
CREATE THE SQL COMMAND
• Create a query without adding any tables
• Flip to SQL view and create this
command:
SELECT customer.first_name,
customer.last_name,
address.street, address.zipcode
FROM customer INNER JOIN address
ON Customer.address_id =
address.address_id;
LEFT JOIN
• Will return all the rows from the left, or
first, table and only the matching rows
from the right or second, table.
RIGHT JOIN
• Returns all of the rows from the right, or
second, table and only the matching rows
from the left, or first, table.