Provide the SQL queries for the following: The answers my prof provided were as

Question

Provide the SQL queries for the following:

The answers my prof provided were as follows...

I am confused how you can be selecting a row (for example, in A - Employee.name from the employee table and the Assignment table when that column does not exist in the assignment table. Can anyone clarify this?

MP YEE relation EMPLOYEE relation Empl ld Name Address 25X15 Joe E. Baker 33 Nowhere St. 34Y70 Cheryl H. Clark 23Y34 G. Jerry Smith 563 Downtown Ave 1555 Circle Dr. JOB relation Skill Code Job ld JobTitle S25X Secretary T5 S26Z Secretary T6 FM3 F5 Floor manager ASSIGNMENT relation Empl Id Job Id Start Date 23Y34 S25X 3-1-1999 34Y70 F5 10-1-2007 23Y34 S26Z 5-1-2006 a) Return the name of all currently working SSN employees 111223333 999009999 111005555 Dept Personnel b) Return a list of all jobs that Joe E. Baker Accounting has worked at in the company using his Sales Emplld. Term Date 4-30-2006

Explanation / Answer

Here the given question is

A) We have to return names of all currently working employees, so here is the query for this question... you can write it like this also but if you want to follow your professor way then you can...

SELECT E.Ename
FROM EMPLOYEE E, ASSIGNMENT A
WHERE( E.EMPID==A.EMPID) AND (A.TERMDATE=='*');

your question is how we are selecting a particular row from a table. Here actually Employee Name doesn`t exist in Assignment table actually it is not necessary to be present in both tables. Here we are selecting Employee Name from Employee table and we want currently working employees. so here in Assignment table currently working employees are represented with '*'. So we have to compare the two tables and write a where condition. for comparing two tables we have common attribute in both tables that is EmpId. Here EmpId acts as primary key in Employee table and foreign key in Assignment table. We compare both tables by writing E.EmpId==A.EmpId, this means we are taking the common employees in both tables. Here we also mentioned about A.TermDate=='*', this means we are currently selecting the employees with TermDate as *. So the complete WHERE condition represents, we are selecting Employee Names who are common in both Employee and Assignment tables having TermDate as '*'. This is the query. I think you understand the explanation if you have any further doubts regarding this you can comment below.

B) Here we have to display list of all jobs worked by joe E.Baker using his Employee Id. Here we have to compare three tables because here we have given Employee Name so we have to take the Employye Name from Employee table and we have select the jobs based on his employee Id, Here we can get Employee Id from Employee table. Inorder to get the Job title we need Job Id, so we have to look about both JobId and EmpId , here we have to join Job and Assignment table. So we are comparing three tables here and the query is...

SELECT J.JOBTITLE
FROM JOB J,ASSIGNMENT A,EMPLOYEE EMPID
   WHERE (E.EMPID=='25X15') AND (E.EMPID==A.EMPID) AND (A.JOBID==J.JOBID);
      

      
Here we are taking Employee Id from Employee table as we mentioned E.EmpId=='25X15', we are comparing two tables Employee and Assignment to establish relation between those two and finally we comparing Job and Assignment tables. So the meaning of this query is......

Selecting a Job title of Employee with Employee Id as 25X15.

Thank you and if you have any other doubts relating this question please comment below...

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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