1 / 10

Relationship and Query Assignment

Create relationships and queries in Access database, defining primary keys, establishing relationships, enforcing referential integrity, and executing specific queries.

yvon
Download Presentation

Relationship and Query Assignment

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Relationship and Query Assignment

  2. Creating Relationship Assignment: • Create a blank database (use any names you like) And then, import the three Excel Worksheets (Course, Instructor, and Membership) from the class web site into your Access database. • Define each imported table’s primary key using information below: • Course table: Class_Number • Instructor table: Employee_Number • Membership table: Member_Number

  3. Creating Relationship Assignment: • Establish relationship based on common fields. • And enforce referential integrity (apply both options) among three imported tables

  4. Query Assignment • Download a database: “Roadrunner” • I would like to have a query that includes following fields; Employee Number, Instructor Last Name, Instructor First Name, Course Name and Time. • SaveasYour Course Info. • I would like to have a query that ONLY includes the male members who live in Burbank using following fields; First Name, Last Name, Gender, City, and State. • Save as Males in Burbank.

  5. Query Assignment • I would like to know a list of all members whose last name begins with P using following fields; First Name, Last Name, City, and State. • Save as Last Names begin with P.

  6. Query Assignment • I would like to know ONLY the Employee Number, Instructor Last name, Instructor First name, and the Salary Amount for only those employees who make between $39,500 and $55,000. In addition, the resulting salaries must also be placed in order from high salary to low salaries. • Save as Between Salaries.

  7. Query Assignment • I would like to know the Class Number, Last Name, and Member Number for each member who has a class number greater than or equal to 8900. In addition, sort last names in ascending order. • Save as Greater than or equal to 8900.

  8. Query Assignment • I would like to know a list of all members whose last name that begins withAor a first name that begins with R including the following fields: First Name, Last Name, City, and State. • Save as Names with A or R.

  9. Query Assignment • I would like to know an instructor who teaches handball and students who are taking handball course. Following fields must be appear on the query: Instructor First Name, Instructor Last Name, Course Name, Member Last Name and Member First Name. In addition, the member last names should be sorted in alphabetical order. • Save as Handball Listing

  10. Query Assignment • I would like to know a whole list of the Instructor Last name, Instructor First name, and the Salary Amount.Since instructors have worked hard, I would like to increase their salary by 15%. So, I’d like to calculate 15% salary increase for each instructor. • Save as Salaries Increase

More Related