Deleting Records - VII

The delete statement is used to delete records or rows from the table.

delete from "tablename"

where "columnname" 
  OPERATOR "value" 
[and|or "column" 
  OPERATOR "value"];

[ ] = optional

[The above example was line wrapped for better viewing on this Web page.]

Examples:

delete from employee;

Note: if you leave off the where clause, all records will be deleted!

delete from employee
  where lastname = 'May';

delete from employee
  where firstname = 'Mike' or firstname = 'Eric';

To delete an entire record/row from a table, enter "delete from" followed by the table name, followed by the where clause which contains the conditions to delete. If you leave off the where clause, all records will be deleted.

Updating Records - VI

The update statement is used to update or change records that match a specified criteria. This is accomplished by carefully constructing a where clause.

update "tablename"
set "columnname" = 
    "newvalue"
 [,"nextcolumn" = 
   "newvalue2"...]
where "columnname" 
  OPERATOR "value" 
 [and|or "column" 
  OPERATOR "value"];

 [] = optional

[The above example was line wrapped for better viewing on this Web page.]

Examples:

update phone_book
  set area_code = 623
  where prefix = 979;

update phone_book
  set last_name = 'Smith', prefix=555, suffix=9292
  where last_name = 'Jones';

update employee
  set age = age+1
  where first_name='Mary' and last_name='Williams';

Inserting into a Table - V

The insert statement is used to insert or add a row of data into the table.

To insert records into a table, enter the key words insert into followed by the table name, followed by an open parenthesis, followed by a list of column names separated by commas, followed by a closing parenthesis, followed by the keyword values, followed by the list of values enclosed in parenthesis. The values that you enter will be held in the rows and they will match up with the column names that you specify. Strings should be enclosed in single quotes, and numbers should not.

insert into "tablename"
 (first_column,...last_column)
  values (first_value,...last_value);

In the example below, the column name first will match up with the value 'Luke', and the column name state will match up with the value 'Georgia'.

Example:

insert into employee
  (first, last, age, address, city, state)
  values ('Luke', 'Duke', 45, '2130 Boars Nest', 
          'Hazard Co', 'Georgia');

Note: All strings should be enclosed between single quotes: 'string'

Insert statement exercises

It is time to insert data into your new employee table.

Your first three employees are the following:

Jonie Weber, Secretary, 28, 19500.00
Potsy Weber, Programmer, 32, 45300.00
Dirk Smith, Programmer II, 45, 75020.00

Enter these employees into your table first, and then insert at least 5 more of your own list of employees in the table.

Creating Tables - IV

The create table statement is used to create a new table. Here is the format of a simple create table statement:

create table "tablename"
("column1" "data type",
 "column2" "data type",
 "column3" "data type");

Format of create table if you were to use optional constraints:

create table "tablename"
("column1" "data type" 
         [constraint],
 "column2" "data type" 
         [constraint],
 "column3" "data type" 
        [constraint]);
 [ ] = optional

Note: You may have as many columns as you'd like, and the constraints are optional.

Example:

create table employee
(first varchar(15),
 last varchar(20),
 age number(3),
 address varchar(30),
 city varchar(20),
 state varchar(20));

To create a new table, enter the keywords create table followed by the table name, followed by an open parenthesis, followed by the first column name, followed by the data type for that column, followed by any optional constraints, and followed by a closing parenthesis. It is important to make sure you use an open parenthesis before the beginning table, and a closing parenthesis after the end of the last column definition. Make sure you seperate each column definition with a comma. All SQL statements should end with a ";".

The table and column names must start with a letter and can be followed by letters, numbers, or underscores - not to exceed a total of 30 characters in length. Do not use any SQL reserved keywords as names for tables or column names (such as "select", "create", "insert", etc).

Data types specify what the type of data can be for that particular column. If a column called "Last_Name", is to be used to hold names, then that particular column should have a "varchar" (variable-length character) data type.

Here are the most common Data types:

char(size)	Fixed-length character string. Size is specified in parenthesis. Max 255 bytes.
varchar(size)	Variable-length character string. Max size is specified in parenthesis.
number(size)	Number value with a max number of column digits specified in parenthesis.
date	Date value
number(size,d)	Number value with a maximum number of digits of "size" total, with a maximum number of "d" digits to the right of the decimal.

What are constraints? When tables are created, it is common for one or more columns to have constraints associated with them. A constraint is basically a rule associated with a column that the data entered into that column must follow. For example, a "unique" constraint specifies that no two records can have the same value in a particular column. They must all be unique. The other two most popular constraints are "not null" which specifies that a column can't be left blank, and "primary key". A "primary key" constraint defines a unique identification of each record (or row) in a table. All of these and more will be covered in the future Advanced release of this Tutorial. Constraints can be entered in this SQL interpreter, however, they are not supported in this Intro to SQL tutorial & interpreter. They will be covered and supported in the future release of the Advanced SQL tutorial - that is, if "response" is good.

It's now time for you to design and create your own table. You will use this table throughout the rest of the tutorial. If you decide to change or redesign the table, you can either drop it and recreate it or you can create a completely different one. The SQL statement drop will be covered later.

Selecting Data - III

The select statement is used to query the database and retrieve selected data that match the criteria that you specify. Here is the format of a simple select statement:

select "column1"
  [,"column2",etc] 
  from "tablename"
  [where "condition"];
  [] = optional

The column names that follow the select keyword determine which columns will be returned in the results. You can select as many column names that you'd like, or you can use a "*" to select all columns.

The table name that follows the keyword from specifies the table that will be queried to retrieve the desired results.

The where clause (optional) specifies which data values or rows will be returned or displayed, based on the criteria described after the keyword where.

Conditional selections used in the where clause:

=	Equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
<>	Not equal to
LIKE	*See note below

The LIKE pattern matching operator can also be used in the conditional selection of the where clause. Like is a very powerful operator that allows you to select only rows that are "like" what you specify. The percent sign "%" can be used as a wild card to match any possible character that might appear before or after the characters specified. For example:

select first, last, city
   from empinfo
   where first LIKE 'Er%';

This SQL statement will match any first names that start with 'Er'. Strings must be in single quotes.

Or you can specify,

select first, last
   from empinfo
   where last LIKE '%s';

This statement will match any last names that end in a 's'.

select * from empinfo
   where first = 'Eric';

This will only select rows where the first name equals 'Eric' exactly.

Sample Table: empinfo
first	last	id	age	city	state
John	Jones	99980	45	Payson	Arizona
Mary	Jones	99982	25	Payson	Arizona
Eric	Edwards	88232	32	San Diego	California
Mary Ann	Edwards	88233	32	Phoenix	Arizona
Ginger	Howell	98002	42	Cottonwood	Arizona
Sebastian	Smith	92001	23	Gila Bend	Arizona
Gus	Gray	22322	35	Bagdad	Arizona
Mary Ann	May	32326	52	Tucson	Arizona
Erica	Williams	32327	60	Show Low	Arizona
Leroy	Brown	32380	22	Pinetop	Arizona
Elroy	Cleaver	32382	22	Globe	Arizona

Enter the following sample select statements in the SQL Interpreter Form at the bottom of this page. Before you press "submit", write down your expected results. Press "submit", and compare the results.

select first, last, city from empinfo; 

select last, city, age from empinfo
       where age > 30; 

select first, last, city, state from empinfo
       where first LIKE 'J%'; 

select * from empinfo; 

select first, last, from empinfo
       where last LIKE '%s'; 

select first, last, age from empinfo
       where last LIKE '%illia%'; 

select * from empinfo where first = 'Eric';

Table basics - II

A relational database system contains one or more objects called tables. The data or information for the database are stored in these tables. Tables are uniquely identified by their names and are comprised of columns and rows. Columns contain the column name, data type, and any other attributes for the column. Rows contain the records or data for the columns. Here is a sample table called "weather".

city, state, high, and low are the columns. The rows contain the data for this table:

Weather
city	state	high	low
Phoenix	Arizona	105	90
Tucson	Arizona	101	92
Flagstaff	Arizona	88	69
San Diego	California	77	60
Albuquerque	New Mexico	80	72

What is SQL? - I

SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system. However, the standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database. This tutorial will provide you with the instruction on the basics of each of these commands as well as allow you to put them to practice using the SQL Interpreter.

Printing Reports - XIV

After you have generated a report, you can print the report.  To print the report:

Print a Report
To print a report:

• Select the report you wish to print
• Right-click on the Report Name
• Click Print Preview
• Choose the appropriate layout, margins, and paper size in the Page Layout group

• Click Print
• Click OK

Generating Reports - XIII

Reports are a means to view and analyze large amounts of data.    You can use the Report Wizard or create a custom report that meets your specific needs.

Report Views
Reports can be displayed in four views:

Design View	This view provides you with the structure of your report.  You can add, modify or delete components of the report but you cannot manipulate the data in the tables associated with the report.
Report View	This view allows you to view the data from the table but not to change any layout of the report.
Layout View	This view allows you to see data from the table and add, modify, and delete components of the report.
Print Preview	This view allows you to see what your report will look like when it is printed.

To change report views:

• Click the View button on the Home tab

Create a  Report
To create a blank report:

• Click the Blank Report button on the Create tab

• Click the Add Existing Fields button
• From the field list, Click and drag the fields to the report

Report Wizard
To create a report using the report wizard:

• On the Create tab, click the Report Wizard button

• Choose the Tables/Queries that you wish to have on the form
• Choose the fields you wish to have on the forms
• Click Next

 

• Choose the sort order for your report

• Chose the layout for the form
• Click Next

• Choose a style
• Click Next

• Create a title for the form
• Choose whether you want to open the form to view it or modify the form’s design
• Click Finish

Custom Calculated Fields
You can create reports that include calculated fields.  These reports will display information that you wish to report with an arithmetic calculation. To add a custom calculated field to a report:

• Open the Report that you wish to add to
• Click the View button
• Click Design View

• Click the Design tab
• Click the Text Box button

• Click the section on the report where you would like to locate the textbox
• Click the Property Sheet Pane

• Click the Data tab
• Click the three dots next to Control Source

• Insert the fields you wish to include in the calculation and the mathematical operations.
• Click OK

Designing Forms - XII

« Calculated Fields Designing Forms Generating Reports » Forms allow you to control the look and feel of the screen for the input of data and the reports generated.  Form Views There are three ways to view forms in Access: Design View Allows you to design a form that includes a header, a footer, and details in the form.  You can also add images and control which fields appear on the form. Form View This is a dynamic page which allows the user to enter and edit data or navigate through data in a field.  Layout View This view allows you to design the form and manipulate data. Create a Form You can create a form from a table or a query.  To create a form: Click the Create tab Click the Form Design button Click the Format tab Click the Add Existing Fields Button In the Field List box on the right, click and drag the fields you would like on the form To change the colors and fonts, click the Property Sheet button on the Arrange tab Insert pic of property sheet button Choose the Section you wish to modify Choose the properties you wish to modify To preview the form: Click the Views button on the Home tab Click the Form View button Form Wizard You can create forms with the help of the Form Wizard.  To use the form wizard: On the create tab, click the More Forms down arrow Click Form Wizard Choose the Tables/Queries that you wish to have on the form Choose the fields you wish to have on the forms Click Next Chose the layout for the form Click Next Choose a style Click Next Create a title for the form Choose whether you want to open the form to view it or modify the form’s design Click Finish

Calculated Fields - XI

A calculated field is a field that gets its information from the calculations performed on other fields.  You can build calculated fields in the Query screen by using the addition (+), subtraction (-), multiplication (*) and division (/) operators. 

Expressions
Expressions a combination of functions, field names, numbers, text, and the operators listed above. 

To build an expression to create a calculated field:

• Open an existing query or start a new query
• Click on the View Button
• Click on Design View

• In the Query Pane, right-click on the field where you would like to create the calculation
• Click Build

• Choose the tables that you wish to build the calculation from
• Double-click the field that you want to include in the calculation
• Click the operator that you wish to include in the calculation
• Click the second field you wish to include in the calculation
• Click OK

• Click Run

Zoom
The Zoom Dialog Box allows you to view an entire expression at one time.  To view the Zoom Dialog Box:

• In Design View, right click on the field you want to display
• Click Zoom

Querying a Database - X

A query allows you to select and filter data from multiple tables.  Queries can be saved and utilized as often as you need them.

Query Wizard
The Query Wizard walks you through the steps to set up a query.  To run a query using the query wizard:

• Click the Create tab
• Click the Query Wizard button

• Choose the type of query you wish to run
• Click OK

• Choose the fields you wish to include from each table
• To select fields from different tables, click the Tables/Queries down arrow
• Click Next

Insert pic of query wizard

• Type in a title for the query
• Click Finish
• The query will display

To switch between tables and queries:

• Open the Navigation Pane
• Double click the name of the table or query you wish to view

Query Design Feature
You can also design a query with the  Query Design Button.  To design a query using the Query Design Button:

• Click the Query Design Button on the Create tab

• Select the tables that you would like to query
• Click Add

• Double click the name of the field you would like to query
• Repeat this process for as many fields as you would like in the query

• Click Run

Query Criteria
Query criteria are search conditions used in a query to retrieve specific data.  You can set query criteria to be a specific number or data set, or you can set the criteria to be a range of data.

“value”	Will only display items that are that exact value (replace the word value with what you want to search by)
=	Is equal to
<	Less than
<=	Less than or equal to
>	Greater than
>=	Greater than or equal to
<>	Not equal to
Between  X  And Y	Within a range (replace X & Y with values)
Is Null	Null values
And	True only if both conditions exist
Or	True if either condition exists
Not	True if the single instance is not true

To specify search criteria:

• Click the query that you wish to add conditions
• Type in the appropriate query criteria in the Criteria Box

Managing Data - IX

Add Records to a Table
To add a new record to a table:

• Open the table in Datasheet View
• Click the New Cell
• Type in your new record

Find and Replace
To find data:

• Click the Find button on the Home tab

To find and replace data:

• Click the Replace button on the Home tab

When you are searching for data for a find, replace or go to, you have several options in the Find Dialog Box.  These options are:

Find What Text Box	Type the text you wish to find
Link in Drop Drop-Down List	Use the drop-down list to specify a table or a column to search
Match Drop-Down List	Use the drop-down list to narrow down the search to a field or the beginning of a field
Search Drop-Down List	Use this drop-down to specify the direction to search.
Match Case Check Box	Use this check box to specify whether to search by the same upper and lower case letters.

Totals
The totals button provides you the opportunity to add a totals row to your database.  The total can be the sum, average, a count, minimum, maximum, standard deviation, or the variance.  To set up a totals row:

• Click the Totals button on the Home tab

• Click the down arrow of the cell where you want the totals
• Click the appropriate choice

Sort Records
You can sort records in a datasheet by a single column or by two adjacent columns.  To sort records by a single column:

• Select the field you wish to sort
• Click the Sort Ascending or Sort Descending button

To sort two columns:

• Move the columns to they are adjacent to each other
• Select the desired columns for sorting by holding the shift key and clicking the columns
• Click the Sort Ascending or Sort Descending button

To clear the sort:

• Click the Clear Sort button

Filter
You can filter records to include only records that you want to display.  To filter by a column:

• Open the database in Datasheet View
• Click the down arrow in the field label
• Choose the appropriate filter criteria
• Click OK

To remove a filter:

• Click the filter button on the field label
• Click Clear Filter
• Click OK