Lecture26 sections 9.19.7

1
Lecture-26 (sections 9.1-9.7)

• CSCI130-03A
• Instructor Dr. Imad Rahal

2
Queries

• A function
• selects certain fields and records from a table
  according to certain criteria displays results
  in a new table for a report
• Record filter
• Update, delete, insert data into a table
• Select Queries are very common
• The produced table is a subset of the database
• Limited by selected fields and matching records
• Used to summarize or do simple calculations on
  the data in the database

3
Queries

• From Database window of the database
• Create query in design View
• Choose the tables you wish to include in the
  query
• Choose fields
• to be displayed in the query or
• that participate in the selection criteria
• Not limited to the given fields
• Derive others

4
Queries

• For each field we can decide
• Show it
• Criteria to be met
• 25
• lt 10
• Between 5 and 10
• Management
• Sales or Design
• Not Sales
• In (Sales, Design, Production)
• Like S
• lt 1-May-99

5
Sample Queries on Gargoyle DB

• 01 Accounting Employees (simple selection query)
• Select first name, last name department
• Accounting Dept
• Run it
• 02a Management Fulltime Employees (uses AND)
• First Name, Last name and Salary
• Management and Fulltime
• Same Criteria Row
• 03 Sales Management Employees (uses OR)
• First Name, Last name, Department fulltime
• Management OR Sales

6
Sample Queries on Gargoyle DB

• 02b Management or Fulltime Employees (uses OR for
  different fields)
• First Name, Last name and Salary
• Management OR Fulltime
• Different Criteria rows
• 04 Employees by Department (parameter query)
• First name, last name
• User inputs department
• Parameter Queries
• Substitute an input box for a criterion specified
  by the user each time the query is run
• Good when criterion may vary but with the same
  selected fields
• 05 First Names beginning with M
• First names
• Like M

7
Sample Queries on Gargoyle DB

• 06 First Names with 'a' as the second letter
  (wildcard char)
• First names
• Like ?a
• ? Any character
• Any set of characters
• 07 Names (uses Concatenation)
• First name last name, Department, Salary
• Reference fields in expressions between square
  brackets
• Expressions have names that appear in table
• Not limited to table fields
• Sorting on one attribute (department)
• 08 Salaries of employees hired in 1999 (uses a
  date function)
• Lastname, firstname, salary
• Year, Month, Day

8
Sample Queries on Gargoyle DB

• 09 Salary with Bonus (Calculated Field)
• Lastname, Firstname , salary bonus
• Sorting on one attribute (Full name)
• 10 Salary Data (uses several aggregate functions)
• Sum, Avg, max, Min salary
• Notice the new Total row (3rd)
• Can be added by clicking on the Totals icon in
  toolbar (SIGMA)
• 11 Salary Averages (uses grouping and a aggregate
  function)
• Average salary by department

9
Sample Queries on Gargoyle DB

• 12 Employee And Manager List (relationship query)
• Fields from more than 1 table
• Must be related
• Department, manager, employee full name and
  Employee number
• 18 Orders within the last year (use of Now())
• OrderDate gt Now()-365
• Or Now()-OrderDatelt365

10
Database Exercise HW

• Email
• Weights (percentile)
• Format as percentage
• Default 50
• Date of Birth
• Format
• Validation Rules (must be older than 15 years
  old)
• gt 1/1/1991
• Set the validation text to Too young to have a
  phone number!

• Create a new phonebook database
• Table Phonebook
• Name
• Required
• Phone number
• Use an input mask format as ()--
• Required
• Street Address
• City
• Default St Cloud
• State
• Default MN
• Zip
• Input mask format as

11
Database Exercise HW

• What should be the primary key?
• Set the primary key
• Go to datasheet view and input (or insert) 3
  records
• Observe fields with
• Input masks
• Formatting
• Default values
• Enter wrong data into fields with
• Input masks (e.g. letters instead of numbers)
• Validation rules (e.g. younger than 15 years old)
• Try to update some field
• Try to delete some record
• Select record, right click, then select delete
• Try to sort on any one field
• Place cursor anywhere in field, right click, then
  go to Sort Ascending or Sort Ascending
• Save you database before exiting

12
Use Gargoyles Do the Following Queries - HW

• First name, last name and department of all
  employees who work in the Management or Sales
  departments sort by department
• Full names of all fulltime employees who earn
  more than 20000
• Full names of all employees who either fulltime
  or who earn more than 20000
• Department names starting with the letter P
• Use Like operator with a
• Departments having P as their third letter
• Use Like operator with a and ?

13
Use Gargoyles Do the Following Queries - HW

• Find names of departments in a given building
  (parameter query)
• Find the purchase order number, item number and
  order date of orders made in the month of October
• Use Month function
• Month (some field) 11
• Find the purchase order number, item number and
  order date of orders made in the month of October
  or 2004
• Use Month and Year functions

14
Use Gargoyles Do the Following Queries - HW

• Find the purchase order number, item number and
  order date of orders made more than 2 years ago
• Use Now() function
• Find the number of employees and average salary
  in every department sorted by department in
  ascending order
• Use Count, Avg and group by functions
• Find the purchase order number and the total
  costs of all orders (i.e. quantityprice but
  display field as cost) made by employees with
  lastnameMiller
• Include both (Employee and Purchase Order tables)