ACCESS WORKBENCH EXERCISES Complete A through L under AW.3.1 using either the QBE or SQL querying method. Your choice.

Name queries using 'qry_ItemLetter'. For example, for item A. on page 239 use qry_A.

Check your answers: Be sure the tables used in the query statement have the correct set relationships (joins) between tables. This is especially true for query E, F, G, and H.

As a general rule, the tables used in the query statements need to be related (joined).

qry H = 4 records

qry_I = 2 qry_J = 270 qry_K = 135 qry_L = Accounting 1, Finance 2, Production 2, 'Sales and Marketing' 2

12 queries (A through L).

240 Part 1 Database Fundamentals K. Whai is the average MaxHours of projes being run by the Sales and Markering department Be sure to assign an appropriate colunn naume to the computed FIGURB Databa resulrs L. How many projects are being u by each departmen? Be sure to display cach DepartmentName and to assigi an appropriate column name to the computed Colu Scria Mak AW.3.2 Using Access QBE, create and run new queries to answer the questions in exer cise AW.3.1. Save each query using the query name for,uat QBEQuery-AWE-3 !-## where the HH sign is replacod by the letler designator of the question. For example, the first query Mod Proo Proc Mair Disk will be saved as QBEQuery AWF-3-1-A. AW.3.3 WP has decided to keep track ol computers used by the employces. In order to do this, two new tabbles will be added to the database. The schema ior these tables, as related to dhe existing EMPLOYEE table, is inote that we are purposely excluding the recursive relationship in EMPLOYEE at this timc): FIGUR WP Da Seria EMPLOYEE (EmployceNumber, First Namc, Last.Name, Departmest, Position, Sapervisor, OfficePhone, EmailAddress) MainMemory, DiskSize) DateReassigned) COMPUTER (SerialNunber, Make, Model, ProcessorType, ProcessorSpeed COMPUTER ASSIGNMENT SerialNumber, EmploveeNumber, DateAssigned. T'he referential integrity constraints are Serial Number in COMPUTER ASSIGNMENT must exist in SerialNumber EmployeeNumber in COMPUTER ASSIGNMENT must exist in 9871 987 987 in COMPUTER EmployeeNumber in EMPLOYEE ErmployeeNnmber is a surrogate key and never changes. Employee records are never delet- ed fron the database. SerialNumber is not a siurrogate key beause it is not generated by the database. However, a computer's SerialNumber never changes, and, herefore, there is no eed to cascade updatcs. When a computer is at its end of life, the record in COMPUTER for that computer and all associated records in COMPl ITER ASSIGNMENT are delted from the database. A. Figure 3-32 shows the colurmn charactetistics ior the WP COMPUTER table. ising the columi charsctcristics, use Access SQL to create the COMPUTER tabic and its associatcd constraints in the Waccd database. Are there any table characteristics that cannot be created in SQL II so, what are they? L'se the Access GUI to finish setiu table characteristics, it nccessary B. The data lor the COMPUTER table are in Figure 3-33. Use Access SQL to euter these data into your COMPUTER table, C. ligurc 34 shows the column characteristics for the WP COMPU TER ASSIGNMENT table. Using the column characteristics, use Aecess SQL to crcate thc COMPUTER ASSIGNMENT rable and the associated constraints in the Wacedb databasc. Are there ay table ot relationship settings or charactcristics that cannot be crcated iti SOL? If so, what are they? Use the Acvess GUI to finish seiting Lable characteristics and relationship settings, if necessary D. The data for the COMPUTER_ASSIGNMENT table are in Figure 3-35, Use Access SOL to ctiter thesc data into your COMPUTER ASSIGNMENT table. Chapter 3 Structured Query Language 239 ACCESS WORKBENCH Key Terms Allow Zero Length field property Decimal Places field property Default Value field property Field Size ficld property Format field property Indexed field property parameterized query Validation Rule field property Yes (Duplicates OK) Yes (No Duplicates) Exercises In the "Access Woribench Exercises" in Chapters 1 and 2, you creatcd a database lor Wedgewood Pacific (WP) of Scattle, Washington. In this set of exercises, yor1: . Create and run queries egainst the datahase by using Accoss SOL. . Create and run queries against the database by using Access QBE. .Create tables and relationships by using Accuss SQ Populate tables by usiug Access SOL AW.3.1 Uising Acess SQL, create and run queries to ansver the questions that follow. save each query lising the ytery name format SQLQuerv. AWE-3-lm whcre he ## sign is replaced by the leter desiguatot of the question. For cxample, the first query will be saved us SQIQuery-AWE-3-1-A. A. What projecrs are in the PROJECI table Show all iniornmarion for each project Wbat are the ProjectID, ProjectName, StartDate, and EndDaie valucs of projects iu the PROJECT table? B. C. What projects io the PROJECT table siaced before August 1, 2017? Show all tbe D. What projects in the PROJECT uable have not beei complered? Show all the in E. Who the cmployces assigned to each project? Show ProjectID F. Who are the employees assigaed to each projt Show ProjectIlD, ProjectNane, and G. ho arc the einployees assigned to cach project? Sbow ProjeccID, ProjectNane, iniormiation for each project foruation for each project, EmployceNumber, Last.Naume, FirstNane, and OfficePhone. Departrmen!. Show EmployeeNun:ber, LastNatne, First. Name, and OffcePaone. Department, and Departunen Phonc. Show EmployeeNumber, FistNamc, aud OfficePhone. Sort by ProjectlD in ascending ordet. Last Name wlo ne the employees assigned to projects run by the Sales and Marketing de- partinent? Show ProjectlD, ProjectName, Department, and DepartmentPhone. Show EtmployeeNumber, LastNanie, FirsiName, and OfficePhone. Sort by ProjectID in ascending order. . I. How many nrojects are being run by rihe Sales and Marketing department? Be sure to assig an appropriate colunn name to the comiputed results. What is the total Maxl lours of projects hcing run by the Salcs and Marketiug de parunent? Be sure to assign an appropriate colunn nanze to the cotnputed resuls J. Costinued)