structured query language - computer science and …web.cse.ohio-state.edu/cse1111/august...

CS&E 1111/1112 PRE LAB 9

Download from your Lab 8 folder on Carmen: Research Papers.xlsx

ALL BEGINNING FILES MUST BE DOWNLOADED FROM Carmen OR THEY WILL NOT BE ACCEPTED.

CREATING QUERIES TO VIEW DATA IN YOUR DATABASE

The database has been created, and records have been added. Now it is time to actually use the information to keep track of your client’s charges and payments. We know that we can open up each table and look at the records, and that works fine with a small database. But, what if the database contains 1,000 clients? How can we keep track of who has paid and who has not. Maybe we want to send out bills to our clients, or flyers to previous clients informing them that our services are still available. We can do this by using queries to view subsets of our data. A query is a question you ask the computer. For example, you can ask the computer to show you all clients who owe you money. The computer will show you a view or subset of your information based on the question you ask it. In a query, we call this subset a dynaset. Remember, all the information you view from the query is coming from your database. You are just seeing a piece of it. Not only can you ask the computer to show a subset of your information, but you can also tell the computer to display only certain fields when displaying this dynaset.

Structured Query Language

So, how do you get the database to create this dynaset? You do this by speaking the database’s language. If someone asks you a question in Spanish, and you don’t know Spanish, you can’t very well answer them, can you? In the same vein, you can’t ask the database a question in English and expect an answer. The database doesn’t know English! The database only knows how to communicate using 0’s and 1’s. That’s it; a group of zeros and ones is the basis of the database’s alphabet. Thus, a language was created so we could have a friendly conversation with the database. This language is called Structured Query Language, or SQL. Therefore, in order to ask your database a question, you are going to have to learn the language called, Structure Query Language. Great, you say, I don’t have the time or the inclination to learn a new language just to communicate with a box of circuits and wires. Well, that is just fine because most databases have a way for you to communicate with them through a cute storyboard called a graphical user’s interface or GUI. You use the GUI to ask your questions, and the database management system or DBMS, creates the SQL for you.

MS Access 2010 and the QBE Grid

Each database has its own GUI that is used to create a query. MS Access 2010 uses the Query by Example grid (QBE grid) to allow us to talk to the database. Let’s use the QBE grid to ask the database some of our own questions, thus creating a database query.

1 | P a g eCopyright © 2013 Lori A. Rice

Suppose you want a listing of your Clients, but you only want the listing to show the ClientID, First Name, Last Name, and Phone Number. How could you create this list? You would write a query to show a view of your database that will show all your clients by the ClientID, First Name, Last Name, and Phone Number. We will create this query in MS Access 2010 by following the steps below.

1. Download the Research Papers.accdb you downloaded from your Lab 8 folder on Carmen.2. Open the Research Papers database by double clicking on the Research Papers.accdb file.3. Click on the Create Tab, and then click on the Query Design Icon. The Query By Example (QBE)

Grid now displays on the screen. This is where you will create your queries.


1. Click on the Create tab

2. Click on the Query Design icon

4. Double Click on the Client table selection, and then click the Close Button.


1. Double Click on the Client selection

2. Click on the Close button

5. Double click on the ClientID field, First Name field, Last Name field, and the Phone Number field.Click on the exclamation mark ( ! ) to Run (display) the query.


1. Double click on the following fields to add them to the grid.

a. ClientIDb. FirstNamec. LastNamed. HomePhone

2. Click on the exclamation mark ( ! )

The query displays the view of the database that you requested. You have limited your view to only the fields in the Client table that you added to the QBE grid. Remember, this is just a view of your database. You can make changes to any of these records in this view and it will make the changes in your database. Now we will close the query and save it. After the query is saved, it becomes a virtual table and this query can be used to create reports, and forms, or it can also be used as input to other queries.


Don’t worry about the order of the records in this dynaset.Just make sure all 16 records are displayed.

6. Click on the close button to close the query, click on the Yes button, type in the name Client Phone Numbers Query, and click the OK Button to save the query.



2. Click on the Yes button

3. Type the name, Client Phone Numbers Query

4. Click on the OK button

Now you can double click on the Client Phone Numbers Query you created and the dynaset showing the list you created will display. In addition, the list will show any new or updated records you have added to your database.

7. Double Click on the Client Phone Numbers Query, and then close the query.


1. Double Click on the Client Phone Numbers Query

NOTE: When completing this lab, don’t worry if your sidebar doesn’t look exactly like the display.

Inner Joins in a Query

Ok, so that was easy, right? But, what if we want to display a list of each payment that has been made by our clients? The list should probably display the ClientID, First Name, Last Name, Payment Amount, and Payment Date. In order for us to display this information we need to use two different tables in our query; Client, and Payments. Ok, so let’s do it!

1. Click on the Create Tab, and then click on the Query Design Icon to display a new Query By Example (QBE) Grid.

2. Click the Client table selection, click the Add Button, click the Payments table selection, then click the Add Button. Finally, click the Close Button.






4. Double Click on the Payments selection

Let’s take a close look at this QBE grid. Notice there are two tables displaying with a line attaching them by ClientID. This line shows that there is a relationship already established between the Client table and the Payments table. This relationship was created by you when you set up the relationships for the database. You should remember two major points when creating queries.

Only show the tables on the grid that will be used in your query. Adding extra tables to the QBE grid may cause the dynaset to display incorrectly.

All tables shown in the query must either be directly or indirectly linked in order for the query to run correctly. Usually this is the case, but there are some scenarios where you will have to create the relationship on the QBE grid yourself.


3. Double click on the ClientID, FirstName, LastName, fields on the Client table to add them to the grid. Double click on the Amount and PaymentDate field to add them to the grid.

4. Finally, click on the Run Button to display the dynaset of the query.


1. Double click on the following fields to add them to the grid.ClientID, FirstName, LastName, Amount and PaymentDate

2. Click on the exclamation mark ( ! )

Notice the dynaset displayed below. The list shows the information for clients who have made a payment. A record is displayed for each payment made. Again, we can make changes to any field in the list and the changes will also update the database because the dynaset is just a view of the database. After we save this query, as long as we don’t delete it, we have created a new virtual record set that exists as long as the query exists.

You may have noticed the dynaset does not include all the records from the Client table. This is because the query we created will only show matching records from each table. For example, record J7525 Karen Day, is on the Client table, but there is no record for her on the Payments table. Therefore, her record will not display on the dynaset. When tables have a line joining them on a query, the tables are said to be joined by the field the line connects to on each table. By default, joins of this type are called Inner Joins. This simply means that the dynaset, like the one shown below, will only display matching records from the two tables listed on the QBE grid. What if we want all the records from the Client table, regardless if there is a matching record on the payments table? We would use an Outer Join when creating the query. Outer joins will be discussed in Chapter xx.



5. Click on the close button to close the query, click on the Yes button when asked to save the query, type in the name Client Payments Query, and click the OK Button to save the query.




3. Type the name, Client Payments Query


Thus far we have created queries that list all the information in our database based on the fields we add to our Query By Example grid. But, what if we want to create a list of information that is based on a certain set of criterion? We can do this by adding the criteria to the QBE grid. For example, if you wanted to make a list of all your Clients who live in Ohio the query would be created as follows.





2. Double click on the Client table selection and then click the Close Button.




3. Double click on the ClientID, FirstName, LastName, City, and State fields to add them to the grid.4. Add the criteria “OH” in the criteria row underneath State field on the QBE grid.5. Click the Run icon to run the query.


1. Double click on the following fields to add them to the grid.ClientID, FirstName, LastName, City and State.

3. Click on the Run icon

2. Type in “OH” as the Criteria

When typing in any non-numeric criteria, always surround the criteria with quotes.

The dynaset created from this query will show the ClientID, First Name, Last Name, City, and State for all clients in OH.

6. Close the query, name it Ohio Clients Query, and click the OK button




3. Type the name, Ohio Clients Query


Don’t worry about the order of the records in this dynaset. Just make sure 6 records are displayedJust make sure all 6 records are displayed.

Ask me about this

Practice using different Criterion in Queries

Adding criterion to the query allows us to view as much or as little information in our database as we need. This is the beauty of a query using different criterion. But remember, we have to talk to the database using its language; therefore there is a certain way we have to add the criterion in order for the database to understand. Here are some rules to follow when you are adding criterion to the QBE grid.


A. If the criterion is text or any special characters, you must surround it with quotes on the QBE grid. For example, if you want a list of all Clients from Washington, you must put “WA” in the criteria row as shown on the grid below.

Field ClientID FirstName LastName State ZipCodeTable Client Client Client Client ClientSortShow x x x x xCriteria “WA”OrOr

Now, let’s try it.


2. Double Click the Client table selection, and then click the Close Button.






3. Double click on the ClientID, FirstName, LastName, City, and State fields to add them to the grid.4. Add the criteria “WA” in the criteria row underneath State field on the QBE grid.5. Click the Run icon to run the query.


1. Double click on the following fields to add them to the grid.ClientID, FIrstName, LastName, City and State.


2. Type in “WA” as the Criteria

6. Close the query, name it Washington Clients Query, and click the OK button




3. Type the name Washington Clients Query



Now, you do this one!

Write a query that lists the ClientID, First Name, Last Name, City, and State for all clients who live in Chicago. Name the query, Chicago Clients Query. If your query is correct, you will see the following dynaset.


Don’t worry about the order of the records in this dynaset. Just make sure all 3 records are displayed.Just make sure all 6 records are displayed.

B. If the criterion is numeric, you type the number only on the QBE grid. For example, if you want a list of all Clients who made a payment of $200, you will type in 200 on the grid in the criteria row.

Field ClientID FirstName LastName AmountTable Client Client Client PaymentsSortShow x x x xCriteria 200OrOr



2. Double click the Client table selection, and the Payments table selection, and then click the Close Button.







3. Double click on the ClientID, FirstName, LastName, and Amount fields to add them to the grid.4. Add the criteria 200 in the criteria row underneath Amount field on the QBE grid.5. Click the Run icon to run the query.


1. Double click on the following fields to add them to the grid.ClientID, FirstName, LastName, and Amount.


2. Type in 200 as the Criteria

When typing numeric criteria no quotes are needed.

6. Close the query, name it Payments of $200 Query, and click the OK button




3. Type the name Payments of $200 Query


Don’t worry about the order of the records in this dynaset.Just make sure 2 records are displayed.

C. If the criterion is a date, you enclose the date with pound signs (#) on the QBE grid. For example, if you want a list of all Clients who made payments on 3/8/2008, you type in #3/8/2008# on the grid in the criteria row.

Field ClientID FirstName LastName Amount PaymentDateTable Client Client Client Payments PaymentsSortShow x x x x xCriteria #3/8/2008#OrOr










3. Double click on the ClientID, FirstName, LastName, Amount, and PaymentDate fields to add them to the grid.

4. Add the criteria #3/8/2008# in the criteria row underneath PaymentDate field on the QBE grid.5. Click the Run icon to run the query.


1. Double click on the following fields to add them to the grid.ClientID, FirstName, LastName, Amount, and PaymentDate.


2. Type in #3/8/2008# as the Criteria

6. Close the query, name it Payments Made on 3-8-2008 Query, and click the OK button




3. Type the name Payments Made on 3-8-2008 Query




Write a query that lists the ClientID, First Name, Last Name, Amount and PaymentDate for all payments made on 1/16/2008. Name the query, Payments Made on 1-16-2008 Query. If your query is correct, you will see the following dynaset.



D. If the criterion is a range, you use the keywords Between/And on the QBE grid. For example, if you want a list of all Clients who made payments between 1/1/2008 and 3/8/2008, you type in Between #1/1/2008# And #3/8/2008# on the grid in the criteria row.

Field ClientID FirstName LastName Amount PaymentDateTable Client Client Client Payments PaymentsSortShow x x x x xCriteria Between #1/1/2008#

And #3/8/2008#OrOr











4. Add the criteria Between #1/1/2008# And #3/8/2008# in the criteria row underneath the PaymentDate field on the QBE grid.

5. Click the Run icon to run the query.


1. Double click on the following fields to add them to the grid.ClientID, FirstName, LastName, Amount, and PaymentDate.


2. Type in Between #1/1/2008# And #3/8/2008# as the Criteria

6. Close the query, name it Payments Between 1-1 And 3-8 Query, and click the OK button




3. Type the name Payments Between 1-1 And 3-8 Query




Write a query that lists the ClientID, First Name, Last Name, Amount and PaymentDate for all payments between 100-200. (The criteria under the Amount field should be, Between 100 and 200)Name the query, Payments Between $100-$200 Query. If your query is correct, you will see the following dynaset.



E. So far the when we have input a criterion, we have assumed we are looking for information that is equal to the criterion, but what if we are looking for information that is either greater than or less than the criterion? We would specify this in on the QBE grid. For example, we would like a list of all Clients that paid $200 or more. The QBE grid would look as follows.

Field ClientID FirstName LastName Amount PaymentDateTable Client Client Client Payments PaymentsSortShow x x x x xCriteria >=200OrOr

Now, let’s try it.1. Click on the Create Tab, and then click on the Query Design Icon to display a new Query By

Example (QBE) Grid.2. Double click the Client table selection, and the Payments table selection, and then click the

Close Button.



4. Add the criteria >=200 in the criteria row underneath Amount field on the QBE grid.5. Click the Run icon to run the query.








1. Double click on the following fields to add them to the grid.ClientID, FIrstName, LastName, Amount, and PaymentDate.


2. Type in >= 200 as the Criteria

6. Close the query, name it name Payments >= $200 Query, and click the OK button




3. Type the name Payments >= $200 Query




Write a query that lists the ClientID, First Name, Last Name, Amount and ChargeDate for all charges on or after 5/1/2008. Name the query, Charges On or After 5-1-2008 Query. (The criteria under the ChargeDate field will be >= #5/1/2008#) If your query is correct, you will see the following dynaset.



F. We can also specify multiple criteria on the QBE grid. Let’s say, we would like a list of all Clients who made payments of $250 or more, OR made payments of less than $100. The QBE grid would look as follows. Notice the criterion is in separate rows. This indicates an OR scenario.

Field ClientID FirstName LastName Amount PaymentDateTable Client Client Client Payments PaymentsSortShow x x x X xCriteria >=250Or <100Or

The grid could also use the keyword OR.

Field ClientID FirstName LastName Amount PaymentDateTable Client Client Client Payments PaymentsSortShow x x x X xCriteria >=250 or <100OrOr



2. Double click the Client table selection, and the Payments table selection, and then click the Close Button



4. Add the criteria >=250 or <100 in the criteria row underneath Amount field on the QBE grid.5. Click the Run icon to run the query.


1. Double click on the following fields to add them to the grid.ClientID, FIrstName, LastName, Amount, and PaymentDate.


2. Type in >= 250 or < 100 as the Criteria

6. Close the query, name it Payments >= $250 or < $100 Query, and click the OK button




3. Type the name Payments >= $250 or < $100 Query




Write a query that lists the ClientID, First Name, Last Name, City, and State field for all clients who live in either in Columbus or Cincinnati. Name the query, Clients in Columbus OR Cincinnati Query. If your query is correct, you will see the following dynaset.



G. Let’s say we want a list of all Clients that paid $100 or more after 3/3/2008. The QBE grid would look as follows. Notice the criterion is in the same row. This indicates an AND scenario.

Field ClientID FirstName LastName Amount PaymentDateTable Client Client Client Payments PaymentsSortShow x x x X xCriteria >=100 > #/3/3/2008#OrOr





4. Add the criteria >=100 in the criteria row underneath Amount field on the QBE grid.5. Add the criteria >#3/3/2008# in the criteria row underneath PaymentDate field on the QBE grid.6. Click the Run icon to run the query.


7. If your query is correct, you will see the following dynaset. Name the query, Payments >= $100 And Payment Date > 3-3 Query.



Write a query that lists the ClientID, First Name, Last Name, Amount and PaymentDate field for all clients who have payments greater than or equal to $200 with a payment date after 3/3/2008. Name the query, Payments >= $200 And Payment Date > 3-3 Query. If your query is correct, you will see the following dynaset.


H. We can also specify compound criteria on the QBE grid. Let’s say, we would like a list of all Clients that made payments of $200 or more, on or after 2/1/2008 or all Clients that made payments less than $150 before 3/1/2008. The QBE grid would look as follows.

Field ClientID FirstName LastName Amount DateTable Client Client Client Payments PaymentsSortShow x x x X xCriteria >= 200 >= #2/1/2008#Or <150 < #3/1/2008#Or





4. Add the criteria >=200 in the criteria row underneath Amount field on the QBE grid.5. Add the criteria <150 in the OR row underneath Amount field on the QBE grid.6. Add the criteria >=#2/1/2008# in the criteria row underneath PaymentDate field on the QBE

grid.7. Add the criteria <#3/1/2008# in the OR row underneath PaymentDate field on the QBE grid.8. Click the Run icon to run the query.


9. If your query is correct, you will see the following dynaset. Name the query, Payment Compound Criteria Query.




Write a query that lists the ClientID, First Name, Last Name, Amount and ChargeDate field for all clients who have charges equal to $100 and the charge date is after 5/15/2008 OR have charges equal to $250 and the charge date is on or before 3/15/2008 . Name the query, Charges Compound Criteria Query. If your query is correct you will see the following dynaset.



I. We can also create a query with a fuzzy search by using wild cards, (An * is considered a wildcard character in a database). Let’s say, we would like a list of all Clients whose last name begins with S or D. We would use the keyword Like and * to create this query.

Field FirstName LastNameTable Client ClientSortShow x xCriteria Like “S*”Or Like “D*”Or

You query could also look as follows:

Field FirstName LastNameTable Client ClientSortShow x xCriteria Like “S*” or Like “D*”OrOr



2. Double click the Client table selection and then click the Close Button3. Double click on the FirstName, LastName fields to add them to the grid.4. Add the criteria Like “S*” in the criteria row underneath Last Name field on the QBE grid.5. Add the criteria Like “D*” in the OR row underneath LastName field on the QBE grid.6. Click the Run icon to run the query.


7. If your query is correct, you will see the following dynaset. Name the query, Last Name Wildcard Query.




Write a query that lists the ClientID, First Name, Last Name, for all clients whose Last Name begins with Da . (The criteria will be Like “Da*”) Name the query, Last Name DA Wild Card Query. If your query is correct you will see the following dynaset.



J. You can also sort the information you display on your list. For example, you can sort the list in ascending order by state as shown in this QBE grid.

Field ClientID FirstName LastName City StateTable Client Client Client Client ClientSort AscendingShow x x x x xCriteriaOrOr



2. Double click the Client table selection and then click the Close Button3. Double click on the ClientID, FirstName, LastName, City, and State fields to add them to the grid.4. Add Ascending in the Sort row underneath State field on the QBE grid.5. Click the Run icon to run the query


6. If your query is correct, you will see the following dynaset. Name the query, State Sort Query.


The dynaset is sorted in State order.

K. You can also sort the information you display on your list using two fields for the sort order. For example, you can sort the list in ascending order by city, and then by state. The main sort has to be to the left of the minor sort. Therefore, because we want to sort by State first and then by City, we must show the sort in the order below. Notice, we are only sorting with the second City field. We are not showing that City field in our list.

Field ClientID FirstName LastName City State CityTable Client Client Client Client ClientSort Ascending AscendingShow x x x x xCriteriaOrOr



2. Double click the Client table selection and then click the Close Button3. Double click on the ClientID, FirstName, LastName, City, State, and then add the City field again

to add them to the grid.4. Add Ascending in the sort row underneath State field on the QBE grid.5. Add Ascending in the sort row underneath the second city Field on the QBE grid.6. Take off the show checkmark on the last City field on the QBE grid.7. Click the Run icon to run the query.


8. If your query is correct, you will see the following dynaset. Name the query, State City Sort Query.


The dynaset is sorted first by State, then by City.


Write a query that lists the ClientID, Last Name, and First Name. Sort the query alphabetically by Last Name, and then by First name. Name the query, Last First Name Sort Query. If your query is correct you will see the following dynaset.


The dynaset should look exactly like the one shown.

The dynaset is sorted alphabetically by Last Name, and then by First Name.

Notice the sort order of the 2 records with the last name of Smith.

L. You can add a calculated field to your database. Suppose you have decided to give everyone a 10% break on their charges. Let’s write a query to show the Charge amount without discount, the discount amount and the Charge Amount after the discount.

Field FirstName

LastName Amount Discount:[Amount]*.10 New Charge:[Amount]-[Discount]

Table Client Client ChargesSortShow x x x x xCriteriaOrOr




2. Double click the Client table selection and the Charges selection and then click the Close Button3. Double click on the FirstName, LastName and Amount field to add them to the grid.4. Type in the following on the first open top cell on the QBE grid.

a. Discount:[Amount] * .105. Type in the following on the first open top cell on the QBE grid.

a. New Charge:[Amount]-[Discount]6. Notice there will be no table under these two selections on the QBE grid.7. Click the Run icon to run the query.


You QBE grid should look as shown below.

8. If your query is correct, you will see the following dynaset. Name the query, New Charge Query.




Write a query that creates a new calculated field to your database called Revised Charge that adds $10 to each Client Charge. The query should show the client Last Name, First Name, Old Charge Amount and the Revised Charge Amount. Name the query, Revised Charges Query. If your query is correct you will see the following dynaset.



M. If the criterion is a checkbox field, you use the words yes or no, (without “ “), on the QBE grid

EXAMPLE NOT SHOWN

I WILL SHOW YOU AN EXAMPLE OF THIS DURING THE LECTURE.


Pre Lab 9 Submission

1. Research Papers.accdb

structured query language - computer science and …web.cse.ohio-state.edu/cse1111/august...

Documents