HAVING clause - XII

The HAVING clause allows you to specify conditions on the rows for each group - in other words, which rows should be selected will be based on the conditions you specify. The HAVING clause should follow the GROUP BY clause if you are going to use it.

HAVING clause syntax:

SELECT column1, 
SUM(column2)

FROM "list-of-tables"

GROUP BY "column-list"

HAVING "condition";

HAVING can best be described by example. Let's say you have an employee table containing the employee's name, department, salary, and age. If you would like to select the average salary for each employee in each department, you could enter:

SELECT dept, avg(salary)


FROM employee

GROUP BY dept;

But, let's say that you want to ONLY calculate & display the average if their salary is over 20000:

SELECT dept, avg(salary)

FROM employee

GROUP BY dept

HAVING avg(salary) > 20000;

GROUP BY clause - XI

The GROUP BY clause will gather all of the rows together that contain data in the specified column(s) and will allow aggregate functions to be performed on the one or more columns. This can best be explained by an example:

GROUP BY clause syntax:

 
SELECT column1, 
SUM(column2)
 
FROM "list-of-tables"
 
GROUP BY "column-list";

Let's say you would like to retrieve a list of the highest paid salaries in each dept:

 
SELECT max(salary), dept
 
FROM employee 
 
GROUP BY dept;

This statement will select the maximum salary for the people in each unique department. Basically, the salary for the person who makes the most in each department will be displayed. Their, salary and their department will be returned.

Multiple Grouping Columns

What if you ALSO want to display their lastname for the query below:

SELECT max(salary), dept
FROM employee
GROUP BY dept;

What you'll need to do is:

SELECT lastname, max(salary), dept
FROM employee
GROUP BY dept, lastname;

This is a called "multiple grouping columns".

For example, take a look at the items_ordered table. Let's say you want to group everything of quantity 1 together, everything of quantity 2 together, everything of quantity 3 together, etc. If you would like to determine what the largest cost item is for each grouped quantity (all quantity 1's, all quantity 2's, all quantity 3's, etc.), you would enter:

 
SELECT quantity, max(price)
 
FROM items_ordered
 
GROUP BY quantity;

Enter the statement in above, and take a look at the results to see if it returned what you were expecting. Verify that the maximum price in each Quantity Group is really the maximum price.

Aggregate Functions - X

 

MIN	returns the smallest value in a given column
MAX	returns the largest value in a given column
SUM	returns the sum of the numeric values in a given column
AVG	returns the average value of a given column
COUNT	returns the total number of values in a given column
COUNT(*)	returns the number of rows in a table

Aggregate functions are used to compute against a "returned column of numeric data" from your SELECT statement. They basically summarize the results of a particular column of selected data. We are covering these here since they are required by the next topic, "GROUP BY". Although they are required for the "GROUP BY" clause, these functions can be used without the "GROUP BY" clause. For example:

SELECT AVG(salary)

FROM employee;

This statement will return a single result which contains the average value of everything returned in the salary column from the employee table.

Another example:

SELECT AVG(salary)


FROM employee;

WHERE title = 'Programmer';

This statement will return the average salary for all employees whose title is equal to 'Programmer'

Example:

SELECT Count(*)

FROM employees;

This particular statement is slightly different from the other aggregate functions since there isn't a column supplied to the count function. This statement will return the number of rows in the employees table.

SELECT Statement - IX

The SELECT statement is used to query the database and retrieve selected data that match the criteria that you specify.

The SELECT statement has five main clauses to choose from, although, FROM is the only required clause. Each of the clauses have a vast selection of options, parameters, etc. The clauses will be listed below, but each of them will be covered in more detail later in the tutorial.

Here is the format of the SELECT statement:

 
SELECT [ALL | DISTINCT] column1[,column2]
 
FROM table1[,table2]
 
[WHERE "conditions"]
 
[GROUP BY "column-list"]
 
[HAVING "conditions]
 
[ORDER BY "column-list" [ASC | DESC] ]

FROM & WHERE clause

SELECT first_column_name, second_column_name

FROM table_name
WHERE first_column_name > 1000;

*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 order they are specified will be the order that they are returned in your query results.

*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.

Example:

 
SELECT name, age, salary
 
FROM employee
 
WHERE age > 50;

The above statement will select all of the values in the name, age, and salary columns from the employee table whose age is greater than 50.

Note: Remember to put a semicolon at the end of your SQL statements. The ; indicates that your SQL statment is complete and is ready to be interpreted.

Comparison Operators

=	Equal
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
<> or !=	Not equal to
LIKE	String comparison test

Note about LIKE

SELECT first_column_name, second_column_name
FROM table_name
WHERE first_column_name LIKE 'Mc%';

The LIKE pattern matching operator can also be used in the conditional selection of the where clause. Like is a very powerful character string comparison 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 'Mc%';

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

 
Example:

SELECT name, title, dept FROM employee WHERE title LIKE 'Pro%';

The above statement will select all of the rows/values in the name, title, and dept columns from the employee table whose title starts with 'Pro'. This may return job titles including Programmer or Pro-wrestler.

ALL and DISTINCT are keywords used to select either ALL (default) or the "distinct" or unique records in your query results. If you would like to retrieve just the unique records in specified columns, you can use the "DISTINCT" keyword. DISTINCT will discard the duplicate records for the columns you specified after the "SELECT" statement: For example:

 
SELECT DISTINCT age 
 
FROM employee_info;

This statement will return all of the unique ages in the employee_info table.

ALL will display "all" of the specified columns including all of the duplicates. The ALL keyword is the default if nothing is specified.

Drop Table - VIII

The drop table command is used to delete a table and all rows in the table.

To delete an entire table including all of its rows, issue the drop table command followed by the tablename. drop table is different from deleting all of the records in the table. Deleting all of the records in the table leaves the table including column and constraint information. Dropping the table removes the table definition as well as all of its rows.

drop table "tablename"

Example:

drop table myemployees_ts0211;

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