7.29 Write an SQL statement to join EMPLOYEE and ASSIGNMENT and include all rows

Question

7.29 Write an SQL statement to join EMPLOYEE and ASSIGNMENT and include all rows of EMPLYEE in your answer, regardless of whether they have an ASSIGNMENT. Run this statement

7.33 Write an SQL statement to create a view named FinanceEmployeePhoneView that shows the values of EMPLOYEE_LastName, and EMPLOYEE.Phone as EmployeePhone. Run this statement, and then test the view with an SQL SELECT statement.

7.35 Write an SQL statement to create a view named EmployeeProjectAssignmentView that shows the values of EMPLOYEE.LastName as EmployeeLastName, EMPLOYEE.FirstName as EmployeeFirstName, EMPLOYEE.Phone as EmployeePhone and PROJECT.Name as ProjectName. Run this statement, and then test the view with an SQL SELECT statement.

Thanks a million

Explanation / Answer

Step 1: Select the Queries/New/Design option . Step 2: Select the View/SQL View option . Selecting this option opens the Query by Example (QBE) screen . Step 3: Click on the Close button to close the Show Table window. (Note that the Show Table window shows all of the available tables. If you select the Queries tab, you will see all available queries. Remember, the objective is to let your students write standard SQL code, so you don’t want them to use the QBE option to let MS Access generate the MS Access version of SQL.) This selection generates the screen . Step 4: Select the View/SQL View sequence shown in Figure Q7.1S4 to generate the screen . Step 5: Start typing your SQL code. INSERT INTO EMP_1 VALUES (‘101’, ‘News’, ‘John’, ‘G’, ’08-Nov-00’, ‘502’); INSERT INTO EMP_1 VALUES (‘102’, ‘Senior’, ‘David’, ‘H’, ’12-Jul-89’, ‘501’); Assuming that the data shown in the EMP_1 table have been entered, write the SQL code that will list all attributes for a job code of 502. SELECT * FROM EMP_1 WHERE JOB_CODE = ‘502’; Write the SQL code that will save the changes made to the EMP_1 table. COMMIT; Write the SQL code to change the job code to 501 for the person whose personnel number is 107. After you have completed the task, examine the results, and then reset the job code to its original value. UPDATE EMP_1 SET JOB_CODE = ‘501’ WHERE EMP_NUM = ‘107’; To see the changes: SELECT * FROM EMP_1 WHERE EMP_NUM = ‘107’; To reset, use ROLLBACK; DELETE FROM EMP_1 WHERE EMP_LNAME = 'Smithfield' AND EMP_FNAME = 'William' AND EMP_HIREDATE = '22-June-04' AND JOB_CODE = '500';

Related Questions

Navigate

• Browse (All)
• Browse 7
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.