MS access homework

ITEC 200 PRACTICE LAB Database Queries 1

ITEC 200 Practice Lab Writing Database Queries

INTRODUCTION This assignment is a hands-on tutorial on how to prepare queries to retrieve the information you need from a database. You will be using only one Structured Query Language (SQL) command: SELECT. The SELECT command is the most useful SQL command to learn because it allows you to extract just about any information you may need from a database.

DUE BEFORE you walk into the lab- THIS is the PRE-LAB Skim all the instructions in this handout carefully before the lab session. Notice the Tour and the Technical Notes on Queries 1. Download database. You need to download the database from Bb. Put it on your G drive or your

flash drive. If you mess up later you can always download a clean copy from Bb.

2. It is useful to print these instructions including forms and tables.

3. Security. MS Access will likely open immediately and you must do two things before you attempt to work with the database. You will notice two warnings immediately below the Microsoft Access Ribbon. First, on the line that says “Security Warning”, click Enable, then click “Enable this Content” and “OK”. Second, where it says “Read Only” click “Save As” and save the file to your G Drive or flash.

4. Open. Once you download this database file, run MS Access in your computer. Click on the “File” Button, then select "Open", and then find this file in your MISClass folder in your G drive and open the database file.

5. Navigation. Select the top tab on your left task bar

to click on All Access Objects. This will help you easily navigate all queries, forms, reports, etc… that the Access file contain.

6. Inspect. Once you open the lab database file,

familiarize yourself with all the tables that contain the data. Under the “Tables” tab, open each table and inspect its contents.

WRITING A SIMPLE TEST QUERY

ITEC 200 PRACTICE LAB Database Queries 2

Before you work on the actual lab queries, let's write a simple test query to give you some practice and to show you how to write queries in MS Access. Select “Create”, this is where you will create new queries, forms, reports, etc

Getting into SQL: the workaround: For some strange reason, MS Access does not provide a direct option to write SQL queries. So, we have to do this the long way with a workaround.

Select "Query Design" option. This will place you in the visual query design facility provided by MS

Access to write queries using "Query by Example (QBE)". We won't be using this method, but if you were writing QBE queries you would select the tables you need here and then prepare your queries

visually. So, please close the "Show Table" dialog box by clicking on the "X" (to re-open this dialog box, right-click on the design screen and select "Show Table"). To open the SQL screen, you

can either right-click on the design screen and select "SQL View" or click on the "SQL" icon on the top left of the screen. If you don't see the "SQL" icon, click on the scrollable list on the top left and select

"SQL View".

Your first SQL, just one line:

Once you open the SQL screen, you will see the word “SELECT;” written there by default. Write a

little test query like this:

SELECT * FROM Clients;

 which will list all columns and all records in the Clients table. The * tells Access to show everything.

 the semi-colon tells Access that it is the end of the SQL command; ensure that the semi-colon is at the end of the SQL command; if you type anything to the right of the semi-colon Access will ignore it.

Close the SQL screen by clicking on the "X". MS Access will ask you if you want to save this query. Say

Yes and name it "Test". Once you have written the query, you can run the query in several ways: (1) you can double click on the query; (2) From SQL view, simply click on the red exclamation mark; or (3) from within the Query Design click on the scrollable list icon on the top left and select "Datasheet View".

ITEC 200 PRACTICE LAB Database Queries 3

Taking a Tour Of The Database That You Will Use Your queries will retrieve data from a simple order entry database that tracks all client order activities in a database. The database has 5 tables. Notice that a) table and field names are unique, so that you can reference them unambiguously, and b) tables and field names do not contain blank spaces). The actual data is small here and displayed on the next page.

Clients This table contains 1 record (row) for each client. It contains the following fields:

ClientID, ClientName, Address, City, State, ZipCode and Telephone. The ClientID field contains a client ID which uniquely identifies each client (i.e., each record). [Aside: the field(s) that uniquely

identify a record in a table is called the "primary key"]

Products This table contains one record (row) for each product that the company sells, which contains data about the product recorded in these fields: ProdID, ProdName, ProdDescription,

and Price. The ProdID field contains a product code that uniquely identifies each product. The data recorded in the remaining fields are self-explanatory.

Orders: This table contains one record (row) for every order placed by your customers. It contains the following fields: OrderNo, ClientID, OrderDate, OrderDescription, OrderStatus

and DeliveryDueDate. The OrderNo field is a sequential number that uniquely identifies an order (have you ever called a company to check the status of an order you placed? they probably asked you to give them an order number). The ClientID is the ID of the client who placed an order. A client ID listed in the orders table must exist in the Clients table [Aside: it does. When data in a field in one table needs to exist

in another table, we call it a "foreign key"]. Note that an order may be placed to purchase more than one item (e.g., when you order a computer from Dell you may also have included in the same order a flat monitor, a router, a scanner and a printer). So, we keep the corresponding line item details in a separate table below.

LineItems This table contains 1 record (row) for every line item of every order placed. Each line item specifies a product and quantity ordered. The same product cannot appear in more than 1 line item in the same order (i.e., if you buy 2 identical computers you will only see one line item with a quantity of

2). The table contains the following fields: OrderNo, LineItem, ProdID, and Qty. The OrderNo field contains the order number in which the line item belongs. The LineItem field is a sequential number

starting with 1 for the first line item in the order, 2 for the second, and so on. The OrderNo and

LineItem fields jointly and uniquely identify each line item recorded in every order

ClientComm This table keeps track of the history of client contact. It contains record in which salespeople record notes and comments about every contact t so all the client contact history is available to the entire company. It contains the following fields: ClientID, Date and Comment. Note that because clients can call many times, there may be many entries for a given client. Therefore, the ClientID and Date (includes a time stamp) fields jointly and uniquely identify each comment entered about every client [i.e., a dual primary key].

ITEC 200 PRACTICE LAB Database Queries 4

Clients

ClientID ClientName Address City State ZipCode Telephone

alberto Espinosa, Alberto Old Albe Road Bethesda MD 20817 3018851958

carmel Carmel, Erran Seven River Rd Washington DC 20016 3013334444

melander Melander, Nicole Rockville Pike Rockville MD 20776 3012223333

glee Lee, Gwanhoo Wizard Lane Fairfax VA 26777 2028851958

klein Klein, Jill Kleinhill Road Potomac MD 20088 7034445555

Orders

OrderNo ClientID OrderDate OrderDescription OrderStatus DeliveryDu eDate

990001 alberto 6/7/2007 Computer System Top Priority 6/10/2007

990002 carmel 6/7/2007 Another System Medium Priority 6/11/2007

990003 melander 6/7/2007 More Systems Low Priority 6/24/2007

990005 klein 6/7/2007 Monitors and Keyboards Top Priority 6/7/2007

990004 glee 6/7/2007 Only CPUs Medium Priority 6/11/2007

Products

ProdID ProdName ProdDescription Price

comp Computer Computer Box 1999.98

keyb Keyboard Ergonomic Keyboard 119.98

monit Monitor Computer Display 374.85

mou Mouse 3 Button Mouse 44.85

prt Printer Laser Printer 443.98

LineItems

OrderNo LineItem ProdID Qty

990001 1 comp 10

990001 2 monit 10

990001 3 keyb 12

990001 4 mou 12

990002 1 comp 20

990002 2 monit 22

990003 1 comp 15

990003 2 keyb 16

990004 1 comp 50

990005 1 monit 12

990005 2 keyb 12

ClientComm

ClientID Date Comments

alberto 6/7/2007 This client acts like he has a lot of money, but he's really broke

alberto 6/8/2007 Client called to complain about nothing. Ignore him next time, needs vacation

carmel 6/10/2007 Called again to order music, but we only sell computers. …probably teaching 2 much

klein 6/7/2007 Called to get specs on monitor. Please send out

glee 6/8/2007 This client acts like he is broke, but he keeps purchasing lots of stuff

ITEC 200 PRACTICE LAB Database Queries 5

TECHNICAL NOTES ABOUT WRITING QUERIES SQL. Once you learn SQL you can use it with any DBMS. For example, if you have invested many hours building queries for an MS Access database, and your company decides to change its database platform to Oracle, you can simply port and run the same queries in Oracle (because they are "standard").

Furthermore, many programming and web scripting languages allow you to embed SQL queries in programs or

web pages so that you can retrieve data from a database to display in dynamically generated web pages (we will

talk about this in class later on).

Queries in MS Access:

Once you start MS Access and retrieve the database file for this assignment, click on the “Create” button. Queries in MS Access are saved with a name for future use. This is very convenient because often times you need to run the same query over and over again. To create a new query click on the

“Query Design”. This will open MS Access' version of QBE (Access calls it "Design View"). You will

see how easy it is to move from/to Design View (i.e., QBE) to/from the SQL view. In fact, an easy way to start building your SQL queries is to first use the Design View and then switch to SQL view. Before you start with the assignment you may want to test a few QBE queries just to see how they work.

Entering, Saving and Running SQL Queries To type a query, first click on the ”Create” button on top; this is where you will create new queries,

forms, reports, etc… in the future. Under the ”Queries” tab on your left pane is where you can view and access your queries. Right now there should be no queries. Unfortunately, and strangely, MS Access doesn't have a direct way to bring you into the SQL query view, so we have to use a convoluted way through the Design View (but you will get used to it quickly).

Now open the Design View, by clicking on the “Query Design” button (with the little square ruler).

When you see a dialog box called "Show Table", click "Close". Now you will see the Design View (or QBE). You will notice a button on the left top corner labeled "SQL". Click on that button and the SQL editing box will open. If you want to go to Design view click on the little squared ruler. You can go back and forth. Now click on the little scrollable icon under SQL or under Design icon. You will notice that you can switch to a number of views. We are only interested in the SQL, Design and Datasheet views at this point.

To run (execute) a query, you can just double click on the actual queries (left pane, under the

“Queries” tab). If you are editing a query and you want to test it, simply click on the exclamation

mark ! icon or simply switch to “Datasheet View” by clicking on the little scrollable icon on the top left corner under SQL.

Once you are happy with your query, click on the X mark on the top right corner of the query editing

box. MS Access will prompt you for a query name. Enter the query name and click OK to save it.

we suggest that you first write your queries by hand using the form on the next page The challenge of SQL queries is figuring out what to write where.

ITEC 200 PRACTICE LAB Database Queries 6

From hereon is DUE AT THE END OF THE LAB session to be checked and graded.

You need to write 8 queries during the lab session: 4 simple queries, and 4 complex queries that join 2 tables. When you finish all your queries run each of the 8 queries and leave them open so that we can review them and check you off quickly.

Types of Queries

Queries can be simple (query one table) and complex with joins (query more than one table), or even more complex (queries with aggregates and grouping, queries within queries or nested queries; querying a table more than once in the same query, etc.). For this class, you will only be working with simple and complex queries with 2 tables. It is important to note that there can be more than one solution for each query.

RAM’s RULE #1 of database: query the MINIMUM number of tables possible for each query. In other words, if all your query results can come from a single table, you should not query 2 or more tables. You will only query 2 tables in our work in this class. But it is common to query 3 or more tables.

ITEC 200 PRACTICE LAB Database Queries 7

SQL SELECT Command Syntax:

SELECT FROM WHERE AND ORDER BY

The columns (and/or column expressions) you want The tables that contain the data Condition to filter the rows you want Another condition, if any The column you want to use for sorting

Example:

SELECT EmployeeName, Salary*1.20 AS NewSalary (resulting column name) FROM Employees WHERE CompanyID = “IBM” AND DeptID = “FIN” ORDER BY EmployeeName

SELECT FROM WHERE AND ORDER BY

SELECT FROM WHERE AND ORDER BY

SELECT FROM WHERE AND ORDER BY

ITEC 200 PRACTICE LAB Database Queries 8

Q 6

( 2

t ab

le s)

Find out how much money your computer orders are making. List all order numbers, line items, product names, quantities ordered, unit price (Price), and total price (Qty*Price) for the ProductID = "comp" (enclose comp in quotations here too). Note: you can use not only single fields in a query column, but also full expressions like Qty*Price. When you do this, MS Access will assign an ugly name for the corresponding column (e.g., expr1). However, you can give this column a more meaningful name using the "AS" clause (e.g., Qty*Price AS TotalPrice). Tip: you need to join two tables for this query (Products and LineItems) because part of the information is available the Products table only (e.g., Price) and other information is available in the LineItems table only (e.g., Qty).

Q 7

( 2

t ab

le s)

Run a similar query to query 3 above, but this time let's also list the Client Name (i.e., if you need to use this query for a management report you need to include client names, not just client IDs). So, please display the client ID, client name and comments entered for the client with an ID = "alberto". Tip: the difference between this query and item 3 above is that you need to join the ClientComm table with the Clients table (so that you can get the client name from the Clients table).

Q 8

( 2

ta b

le s)

Since we keep track of orders and the line items for these orders in separate tables, lets build a query that gives us a complete view of all orders and their respective line items. List all orders, client ID, line item numbers, product ID and quantities ordered in all line items in all orders. Tip: you need to join the Orders and the LineItems tables.

Queries that you will write in the lab session

Q1 Retrieve client information for Jill Klein. List the client ID, client name, state and zip code for the client with an ID of "klein" (note: you need to enclose "klein" in quotations for queries like this that involve text strings).

Q2 Get a list of our highly priced products. Display the product ID, product name and price for all products priced over $200.

Q3 Find out which notes and comments have been entered for Alberto Espinosa. Display the client ID and all the comments entered for a client with an ID of "alberto".

Q4 Show all the transactions (meaning lines) where a computer was purchased (“comp”). In this query show me Order#, ProdID, Qty

Q 5

(2 t

ab le

s) Same as Q4 but this time make more elegant by accessing full descriptions. Show these

fields : order#, prodID, product description, price, quantity

ITEC 200 PRACTICE LAB Database Queries 9

SQL Simple Query Syntax (columns can be retrieved from a single table)

Note: the keywords or conditions in brackets [ ] are optional

SELECT [*] [DISTICT] [TOP n] [BOTTOM n] field1, field2, field formula [AS name], etc.

FROM tablename

[WHERE condition1]

[AND condition 2, etc.]

[ORDER BY field1, field2 [DESC][ASC]]

The (mandatory) SELECT line has a list of fields or columns to display in the results query (e.g., Qty, Price) but it can also contain formulas combining columns (e.g. Qty * Price).

 Use the AS keyword if you want to give a specific name to the formula (e.g. Qty * Price AS TotalPrice)

 Use the optional * wildcard if you want to list all fields in the tables you are querying (reduces your typing)

 Use DISTINCT to eliminate duplicate rows from your query results, if any

 Use TOP in combination with ORDER (when you sort queries) to list only the top n records from the result (e.g., SELECT TOP 5 EmployeeName, Salary FROM

Employees ORDER BY Salary DESC)

The (mandatory) FROM line has the name of the table that contains the SELECT fields you want to retrieve in your query

The (optional) WHERE line contains the condition that allows you to filter which records get displayed

(e.g., WHERE Salary>100000). If you have more than condition you can use the AND keyword to add more conditions (e.g., WHERE Salary>100000 AND State="NY").

The (optional) ORDER BY line contains the fields you want to use for sorting. By default, the query

will sort in ascending order, but you can include the DESC keyword if you want it sorted in descending order (e.g. SELECT EmployeeName, Salary FROM Employees ORDER BY Salary DESC). You

can use more than one field to sort (e.g. SELECT CompanyID, EmployeeName FROM Employees

ORDER BY CompanyID, EmployeeName -- will list employees sorted by their company ID's and by their name, within each company ID)

ITEC 200 PRACTICE LAB Database Queries 10

SQL Complex Query Syntax (columns need to be retrieved from MORE THAN 1 TABLE)

SELECT [*] [DISTICT] [TOP n] [BOTTOM n] field1, field2, field formula [AS name], etc.

FROM tablename1, tablename2, etc.

WHERE tablename1.commonfield = tablename2.commonfield --- this is the JOIN condition

[AND otherconditions, etc.]

[ORDER BY field1, field2 [DESC][ASC]]

This works just like the simple queries, except that:

You MUST specify the JOIN condition in the WHERE line. For example, if the Employees table needs to be joined with the Companies table, and the common field in these two tables is

CompanyID, the line WHERE should read:

WHERE Employees.CompanyID = Companies.CompanyID.

WARNING: if you omit this join condition you will obtain a TABLE PRODUCT, which is NOT what you want. A table product will combine every single row of tablename1 with every single row of tablename2.

If any of the fields in the SELECT line exist in both tables listed in the FROM line, you must

resolve this ambiguity by specifying the table from which the data will be retrieved by prefixing the

field name with the table name and a period (e.g., SELECT EmployeeName, CompanyName, Companies.CompanyID FROM Employees WHERE Employees.CompanyID =

Companies.CompanyID).