100 likes | 187 Views
Create relationships and queries in Access database, defining primary keys, establishing relationships, enforcing referential integrity, and executing specific queries.
E N D
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
Creating Relationship Assignment: • Establish relationship based on common fields. • And enforce referential integrity (apply both options) among three imported tables
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.
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.
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.
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.
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.
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
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