Transcript DinaSaid-databases3-1

Week 3 Lab1
Review on Database
Dina A. Said
[email protected]
Next Week (T1 & T2 only)
 Next Monday
 Lab Quiz #2 on Databases
 Current Wednesday
 “Week 1 Lab 2” of Problem Solving
 Thanks for TA Paul for Today slides
Uploading Data from Excel
 Got to the "External Data" tab and then select from the
Import section Excel.
1/21/2009
CPSC203- Week2- Lab1
3
1/21/2009
CPSC203- Week2- Lab1
4
Review for Lab Quiz #2

Create a new database named Stuff.
Review for Lab Quiz #2

Create a table called Orders with the fields: OrderID,
ProductID, SalespersonName, SalesDate, Quantity and
SellingPricePerPiece.




What is the primary key?
Data type for ProductID?
Data type for SalespersonName?
Data type for SalesDate?
To Create a new Table
1. Choose Tab CreateTable
2. Write click on the table name to choose the design view
3. Start writing variables Names and types
 Notes:
 Choose the unique variable to be primary key
 To make a variable primary key: choose the variable, right
click primary key
 Primary key variable is the one with yellow key to its left
 If you are required to enter values for ID, make its type
number instead of autonumber
1/21/2009
CPSC203- Week2- Lab1
7
Review for Lab Quiz #2

Create a table called Products with the fields ProductID,
PName, PurchaseDate, Supplier and
PurchasePricePerPiece.

What relationships might be required?
To create a relationship
1. Choose tab Database Tools
2. Choose relationships
3. Choose both tables
4. To make a relationship between variable A and variable
B
Drag variable A from the first table to variable B in the
second table
II. A box will appear to you with the relationship variables
III. Make sure that variables names are correct and then press
ok
I.
1/21/2009
CPSC203- Week2- Lab1
9
Review for Lab Quiz #2


Set the default quantity in the Orders table to 0.
How?
1.
2.
3.
Open the Design view of the required table
Choose the variable that you want to set its default value
Set default value in the bottom box
Review for Lab Quiz #2

Add the following records to the products table:
 ProductID: 10089 , PName: Bath Salts, PurchaseDate:
10/20/2008, Supplier: Bath and Beyond,
PurchasePricePerPiece: $2
 ProductID: 10768 , PName: Shower Gel, PurchaseDate:
10/1/2008, Supplier: Lush, PurchasePricePerPiece: $4.50
 ProductID: 10769 , PName: Shampoo, PurchaseDate:
10/1/2007, Supplier: Lush, PurchasePricePerPiece: $8
Review for Lab Quiz #2
 Add the following data to the orders table:
orders
orderID
1/21/2009
productID
salesManNa
me
quantity
orderDate
sellingPriceP
erPiece
1
10089
John Smith
2
11/1/2009
5
2
10769
Lora Bill
1
12/1/2009
10
3
10768
Sandra smith
10
5/1/2009
9
4
10768
John Smith
5
2/1/2009
5
CPSC203- Week2- Lab1
12
Review for Lab Quiz #2


Create a query named query_1 that displays all the
information from both tables.
How?
1.
2.
3.
4.
5.
6.
Choose create tab Query Wizard
Choose simple Query wizard
Choose both tables
Choose which fields you want to be displayed using >
If you want to not show a certain field after choosing it,
use <
Rename the query as required
Review for Lab Quiz #2
 Create a query named query_2, using query_1, that
displays all fields in both tables, but limit the query to the
Supplier "Lush".
 How?
Creating the query using simple query wizard to select all
fields from query-1
2. Open the query in the design view
3. In the suppler field, set the criteria to “Lush”
4. You can check SQL by right-click and choose Show SQL
1.
Review for Lab Quiz #2
 Create an aggregate query named query_3. Group the
query using SalesPersonName and calculate the Average
quantity he/she sells
How to create Aggregate query?
1. Create the query using simple query wizard and choose
only salesPersonName and quantity
2. Open the query in the design view
3. Choose Totals
4. You will find a new row added
named total and the word GroupBy is written for both fields
5. For Quantity, change the word GroupBy to be Sum so
that the sum of Quantity is displayed
6. Don’t forget to check SQL
1/21/2009
CPSC203- Week2- Lab1
16
Review for Lab Quiz #2

Create a new query query_4, that displays all
information in table products. Then, create a new field
called RevenueAmount. This should be calculated by
Quantity*SellingPricePerPiece.
How?


Create the Query using the simple query wizard
Display SQL of query_4
Add to the select statement the following:



Select …., Quantity*SellingPricePerPiece as RevenueAmount
Review for Lab Quiz #2
 In query_4, create a new field called Status. This should
display "Profit" if
SellingPricePerPiece>PurchasePricePerPiece,
"Breakeven" if they were equal and "Loss" otherwise.
 First, write the expression in English statements
 if SellingPricePerPiece>PurchasePricePerPiece

Status=profit
 Else if SellingPricePerPiece=PurchasePricePerPiece

Status=equal
 Else

Status=loss
How to make If statement?
 Go to the design view of the query
 Add anthoer field as:
 Status:IIF(SellingPricePerPiece>PurchasePricePerPiece,
”Profit”, IIF(if SellingPricePerPiece=PurchasePricePerPiece,
“Equal”,”Loss))
1/21/2009
CPSC203- Week2- Lab1
19
Review for Lab Quiz #2
 Create a crosstab query from query_1. Let the rows show
the SalesPersonName, the columns show the product
name, and calculate the sum of Quantity.
 How?
 Choose Create Query WizardCrosstab query
 Choose query query1
 Select SalesPersonName in rows
 Select ProductName in column
 Select quantity as data and sum as function (on the right)
 Notes
 Take care which fields are required to be rows and which
fields are required to be columns
 Take care which function is required to be calculated; avg,
sum, max, etc…
1/21/2009
CPSC203- Week2- Lab1
21