A114/I111 Intro to Databases (1.5 cr.)

Fall Semester 2002

Lecture Notes Three: Querying your database. A structured query language (SQL).

1. Introduction

The basic idea behind a table is simple. Conceptually, a database table is comprised of rows and columns. Columns represent a description of the contents of the table (the entity that the table stands for). The rows contain the actual data in the in the table.

2. Designing Tables

Databases are usually composed of multiple tables. Each table is designed to contain a specific type of object or entity. For example the Marina table contains instances of marinas (encoded as rows), as abstracted by our application. Instances of technicians are recorded in the Technician table.

2.1 Simple Design

The design of a table comprises:

a list of field names, with
their data types

So far we have looked at:

designing
defining
populating
querying a database

Today we will look at ways in which we can express our queries.

Tomorrow in lab you will be focusing mostly on issues related to database maintenance.

2.2 Primary Keys

A key in a database system is used to give you access to a particular record in a database. Given a key value the database management system should return a unique record.

In a relational database management system, a key usually consists of a set of attributes (columns) of a table that uniquely determines a record (row) of that table. In other words, no two rows in the table can have exactly the same values for the key columns. A simple example is the social security number in an employee database.

Sometimes a combination of columns is used as a key, otherwise we use a calculated key: most database systems support this capability through some sort of auto-numbering function. In this case, a unique number is assigned to each row of data added to the table. The two tables in our database application have keys which are similar in meaning to a calculated key (although the keys are determined by the user of the database).

In Access one can define a multi-column key by

selecting the columns then
pressing the primary key button

in the design view of a table.

3. Simple Queries

Database systems are excellent tools for storing and retrieving information. in a RDBMS data is stored in tables, specifically designed for the application. In addition, database systems provide mechanisms for retrieving information: certain commands, called queries, are used to retrieve information from the database. It is a property of a RDBMS to present data only in tables. Thus queries can only produce tables. The entire system is thus quite uniform.

There are many different interface for specifying and executing queries. As a generalization in this class the Query Construction Method (QCM) as a technique for developing database queries.

3.1 The Query Construction Method

Providing the ability to retrieve the information from a database is an essential function of a relational database system. QCM provides a simplified technique for creating database queries.

Learning to use the QCM is as easy as filling in the following table:

Answering the first two questions, DATA and LOCATION is straightforward. You just need to name the appropriate columns and tables. The third section, CONDITION, turns out to be where database queries become really interesting. We can avoid using this section in only the simplest types of queries. The fourth section deals with the FORMAT of the results. This depends, by and large, on the intended recipient of the query results.

We'll use SQL to record here all the queries that you have encountered in Project Two.

3.2 Simple Queries

In this section we will use QCM to create simple queries.

Use the tables below as a workbook. We'll work these queries out completely in lecture and labs.

Warm-up: what's this query asking for?

SELECT Marina.[Marina Number]
FROM   Marina;

Answer: List all Marina Numbers from table Marina.

Now the queries.

1. List all

marina numbers and
marina names, as well as
the technician numbers of the technicians that service that specific marina

(from the Marina table).

SELECT Marina.[Marina Number], Marina.name, Marina[Tech Number]
FROM   Marina;

2. List the entire information in the Marina table.

SELECT Marina.*
FROM Marina;

3. List the

marina number,
name,
warranty and
non-warranty amounts

for the marina whose number is EL25.

SELECT Marina.[Marina Number], 
       Marina.Name, 
       Marina.Warranty, 
       Marina.[Non-warranty]
FROM   Marina
WHERE  Marina.[Marina Number] = "EL25";

4. List the

marina number,
name,
warranty and
non-warranty amounts

for the marinas whose names start with Fe.

SELECT Marina.[Marina Number], 
       Marina.Name, 
       Marina.Warranty, 
       Marina.[Non-warranty]
FROM   Marina
WHERE  Marina.[Marina Name] LIKE "Fe*";

5. List the

marina number,
name,
address,
warranty and
non-warranty amounts

for the marinas located in Burton.

SELECT Marina.[Marina Number], 
       Marina.Name, 
       Marina.Address, 
       Marina.Warranty, 
       Marina.[Non-warranty]
FROM   Marina
WHERE  Marina.City = "Burton";

6. List the

marina number,
name,
warranty and
non-warranty amounts

for those marinas whose non-warranty amounts are 0 (zero).

SELECT Marina.[Marina Number],
       Marina.Name, 
       Marina.Warranty,
       Marina.[Non-warranty]
FROM   Marina
WHERE  Marina.[Non-warranty] = 0;

7. List the

marina number,
name,
warranty and
non-warranty amounts

for those marinas that have a warranty amount greater than 1,000 dollars.

SELECT Marina.[Marina Number],
       Marina.Name, 
       Marina.Warranty,
       Marina.[Non-warranty]
FROM   Marina
WHERE  Marina.[Warranty] > 1000;

8. List the

marina number,
name,
warranty and
non-warranty amounts, as well as the
technician number

for those marinas whose

warranty amounts are greater than 1,000 and
whose technician number is 36.

SELECT Marina.[Marina Number],
       Marina.Name, 
       Marina.Warranty,
       Marina.[Non-warranty],
       Marina.[Tech Number]
FROM   Marina
WHERE  Marina.[Warranty] > 1000 AND
       Marina.[Tech Number] = "36";

9. List the

marina number,
name,
warranty and
non-warranty amounts, as well as the
technician number

for those marinas whose

warranty amounts are greater than 1,000 or
whose technician number is 36.

SELECT Marina.[Marina Number],
       Marina.Name, 
       Marina.Warranty,
       Marina.[Non-warranty],
       Marina.[Tech Number]
FROM   Marina
WHERE  Marina.[Warranty] > 1000 OR
       Marina.[Tech Number] = "36";

10. List

all the cities that have a marina serviced by Bavant Marine Services,

sorted in alphabetical (ascending) order.

SELECT   Marina.City
FROM     Marina
ORDER BY Marina.City;

11. List the

marina number,
name,
tech number, and
warranty amount

for all marinas, sorted by technician number (ascending).

Within the collection of marinas

having the same technician,

the marinas should be

sorted by descending warranty amount.

SELECT   Marina.[Marina Number],
         Marina.Name,
         Marina.[Tech Number],
         Marina.Warranty
FROM     Marina
ORDER BY Marina.[Tech Number],
         Marina.[Warranty] DESC;

3. Elementary SQL

The SQL standard defines a formal syntax for queries. The syntax includes the structure of commands and keywords. The basic syntax of an SQL query is as follows:

This syntax is closely matched with the QCM.

The select query window in Access (that you will be using in lab) is offering a QBE (Query By Example) interface. You will see that for each query specified in QBE there is an SQL equivalent that Access can display for you if you want to.

Project two also covers:

joining tables.
restricting records in a join.
using calculated fields in a query.
calculating statistics.
using criteria in calculating statistics.
grouping.

Here are the remaining queries in SQL.

Page A2.29:

SELECT DISTINCT Marina.City
FROM            Marina 
ORDER BY        Marina.City;

Page A2.33:

SELECT Marina.[Marina Number]
       Marina.Name,
       Marina.[Tech Number],
       Technician.[Last Name],
       Technician.[First Name]
FROM   Marina INNER JOIN Technician ON 
       Marina.[Tech Number] = Technician.[Tech Number];

Page A2.34:

SELECT Marina.[Marina Number],
       Marina.Name,
       Marina.[Tech Number],
       Technician.[Last Name],
       Technician.[First Name],
       Marina.Warranty
FROM   Marina INNER JOIN Technician ON 
       Marina.[Tech Number] = Technician.[Tech Number];

Page A2.36:

SELECT Marina.[Marina Number],
       Marina.Name, 
       [Warranty] + [Non-warranty] AS [Total Amount]
FROM   Marina;

Page A2.39:

SELECT Avg(Marina.Warranty) AS AvgOfWarranty
FROM   Marina;

Page A2.40:

SELECT   Avg(Marina.Warranty) AS AvgOfWarranty
FROM     Marina
GROUP BY Marina.[Tech Number]
HAVING   Marina.[Tech Number] = "36";

Page A2.41:

SELECT   Marina.[Tech Number],
         Avg(Marina.Warranty] AS AvgOfWarranty
FROM     Marina
GROUP BY Marina.[Tech Number];

Last updated: Sep 9, 2002 by Adrian German for A114/I111

DATA	What pieces of information are required?
LOCATION	Where is the information located?
CONDITION	Which rows of data should be retrieved?
FORMAT	How should the results be presented?

SELECT	field list
FROM	table list
WHERE	condition