3_Structured_queries_didactic
Оценка 5

3_Structured_queries_didactic

Оценка 5
pdf
09.05.2020
3_Structured_queries_didactic
3_Structured_queries_didactic.pdf

 

Exercise 4

Create Queries

Queries are generally used to extract information from the tables and present it in a non-formal format or create formal reports. Queries can be created from one table (single table queries) or multiple tables (multi-table queries).

 

Examples of single table queries:

       List all products that has Quantity on hand less than 100

       List all customers that live in Washington

 

Example of multi-table queries:

       List the company names (Customer table) and the product description (Stocks table) the companies purchased for each order (Orders table). 

 

In this class, we are primarily concerned with constructing queries using QBE (Query By Example) where you use a graphical interface to select the tables and conditions to be queried.

 The other form of query construction is through SQL (Structured Query Language) where you instruct the computer to select certain fields and records by writing SQL statements.

 

Before you construct your query, ask yourself two questions.

1. What fields do you want to display?

2) Where can I get the data? (Which Table(s)) 

 

For example: If you want to list the Customers’ Company Names and Titles, you need to display  “CompanyName” and “Title” for every customer, which are all located in the same table: Customers Table.

 

Example: List the company names and the product description for the stock items the customers purchased. 

In this case,Company” comes from the Customers Table and “Description” comes from the Stocks Table

 

For Multi-table queries, you will need at least two tables and possibly more because we need to link (traverse) the tables before we can get from one field to another. We will leave this for now and come back to it later.

 

Steps to create single table queries

Query: For each customer, we want to list the customer ID, company name and title.

 

1.             Select the Queries Button / Tab on the Database Window, and select Create query in Design view.

 

Figure 1: Database window

 

 

Alternative way: Click  on the icon bar at top right corner (you might have to use the pull down list to get to queries) From the available selections, select the ‘Design View’ option and click OK.

 

3)       Add the tables you will need to access the fields you want.

               In this case, we only want to add the Customers table.  Select “Customers”, Click Add and Close.  

 Figure 2: Select tables

 

4)       Select the necessary fields.  There are three methods of selecting fields:

-          Drag the fields from the tables into each column 

-          Double click the field you want 

-          Click in the field that you want to set and use the pull down list to select one   When you have more than one table, be sure to select the table and the field

 

 

Figure 3: Query designer

 

5)       To execute this simple query, which lists the “CustomerID”, “Company” and “Title” for every customer, we click the execute button .

 

 

 

6)       You will see the list created in an informal Datasheet format.

 

7)       Select View/Query Design or click  to go back to the design mode.

 

 

Though we didn’t use all of them, each of the rows in the Query Designer has a purpose:

 

FIELD

The fields that you want to use.

TABLE

The table where the field is taken from. This comes really handy when you are creating multi-table queries.

SORT

This field has a drop down list to select how you want the lists to be sorted. If you choose ascending Order for the Company. Then the records will be sorted according to alphabetical order with ‘a’ first.

SHOW

If you want the field to be shown or displayed in Datasheet view, make sure that it is marked with Υ.

CRITERIA

This is most useful to trim the list such that the list will satisfy a certain conditions by only including certain records.

 

MINI-EXERCISE

I.              For every Customer, list the Company in alphabetical order, the CustomerID and the Title.

 

 

Mathematical & Logical Operators

The following are mathematical operators that you can use:

>

greater than

>=

greater than or equal to

<

less than

<=

less than or equal to 

=

equal

<>

not equal

 

The following are logical operators you can use:

NOT, OR, AND

 

Examples:

>=10005 inside the CustomerID criteria field:

This means only customers that have a CustomerID greater or equal to 1005 will be displayed in the query.

 

>=10005 and <=10010 inside the CustomerID criteria field:

Only customers that have a CustomerID between 10005 and 10010 (inclusive) will be displayed.

 

Like Operator 

You can use the Like operator to find the values in a field that match the pattern you specify

 

Examples:

1.  Like “WA” in the State criteria field:

Only customers that live in Washington will be displayed. Note that for non-numeric fields we use double quotes (“”).

 

 

 

 

2.  Like “*Smith*” in the Customer Company field

In this case, we add asterisk (*) in the quotes in front and back to allow for “wild cards” i.e. customers like Ann Smith or Simthson or Smith will be selected. 

 

3.  Like “[R-V]*” in the Customer Company field

In this case, all companies that have a name starting with R to V will be selected. Note that we use [    ] to indicate the range for wild cards.

 

4.  Like “1/*/96”in say a field denoted by date

In this case, only records whose date falls in January will be selected.

 

IS NULL Operator

eg.: put the IS NULL as a criteria in Company and only  records with a NULL (nothing) in the company field will be displayed.

 

MINI-EXERCISE

II.                  List only customers that are located in WA.

III.                List only customers that are located in WA and whose ratings is higher than fair (Remember that we denote 0 for No ratings, 1 for poor, 2 for fair, 3 for acceptable, 4 for good, 5 for excellent)

                                Close the query and name your query “simple”.  

 

Create multi-table queries.

Query: For each order, list the company names and the product descriptions of the stock items the companies purchased.

 

1.       Follow the same steps as we did for single table queries. When you have to select the tables, you can select as many           tables as you need. 

 

2.       Add the Customers table (to get company names) and the Stocks table (to get the product description). 

 

3.       You will notice that the two tables are not linked. In order to get the product that the customer has ordered, we need to get the orders that the customer places and from those orders, the products that were purchased. To get the orders the customer has placed,  we need the Orders table. To get the individual product ordered in that particular order, we need the OrderDetails table. Once we add the two tables, all the tables are linked. 

 

NOTE THERE IS NO STAND-ALONE TABLE IN A MULTI-TABLE QUERY.

If so, either the table is redundant, that means we don’t need any fields from it. Or we need another table to help linking the tables together

 

Figure 4: multi-table Query designer

 

 

If you want to list only the customers that purchased products in January, type Like “1/*/96” in the Criteria row of “Date”  as follows:

 

 

Figure 5: multi-table Query designer with criteria on Date

 

Note that I did not put a Υ mark in the Show row of Date, therefore a “Date” column will not be displayed.

 

MINI-EXERCISE

IV.          List all the companies located in Washington that purchased only Software items in the month of February.      

              Display the CustomerID, Company name, Product Description. You can close the query and save it as complex.

 

Exercise 4 Create Queries

Exercise 4 Create Queries

Figure 1: Database window

Figure 1: Database window

Figure 3: Query designer 3)

Figure 3: Query designer 3)

Though we didn’t use all of them, each of the rows in the

Though we didn’t use all of them, each of the rows in the

Like “*Smith*” in the Customer

Like “*Smith*” in the Customer

Figure 4: multi-table Query designer

Figure 4: multi-table Query designer
Скачать файл