In SQL I currently have the two following tables: CREATE TABLE EMPLOYEE( employe

Question

In SQL I currently have the two following tables:

CREATE TABLE EMPLOYEE(
employee_id DECIMAL(6)   NOT NULL,
first_name VARCHAR(20)   NOT NULL,
last_name VARCHAR(25)   NOT NULL,
email VARCHAR(25)   NULL,
phone_number VARCHAR(20)   NULL,
hire_date DATE       NOT NULL,
job_title VARCHAR(35)   NOT NULL,
salary DECIMAL(8,2)   NULL,
commission_pct DECIMAL(2,2)   NULL,
supervisor_id DECIMAL(6)   NULL,
department_name VARCHAR(30)   NULL

-------------------------------------------------------------------

CREATE TABLE JOBHISTORY(
employee_id DECIMAL(6)   NOT NULL,
start_date DATE       NOT NULL,
end_date DATE       NULL,
job_title VARCHAR(35)   NOT NULL,
department_name VARCHAR(30)   NULL

------------------------------------------------------------------

My task is to create a VIEW that retrieves: employee_id, first_name, last_name, email from EMPLOYEE table and then a count of finished jobs, so far I have the following:

CREATE VIEW EMPJOBS(employee_id, first_name, last_name, email, end_date) AS (SELECT e.employee_id, e.first_name, e.last_name, e.email, j.end_date FROM EMPLOYEE e LEFT JOIN JOBHISTORY j ON e.employee_id=j.employee_id)

Which creates the table below when I run:

SELECT employee_id, first_name, last_name, email, end_date FROM EMPJOBS WHERE end_date != 'NULL';

and it continues down, BUT I would like to have JOBSFINISHED where it has end_date.

employee id I first name I last name I email I end date I 101 I Neena I Kochhar NKOCHHAR I 1993-10-27 101 I Neena I Kochhar NKOCHHAR I 1997-03-15 l l De Haan LDEHAAN I 1998-07-24 l 102 Lex

Explanation / Answer

Solution:

CREATE VIEW EMPJOBS(employee_id, first_name, last_name, email, end_date) AS (SELECT e.employee_id, e.first_name, e.last_name, e.email, j.end_date FROM EMPLOYEE e LEFT JOIN JOBHISTORY j ON e.employee_id=j.employee_id)

This statement which you have mentioned above will display the above view of the table. But, if you want to view JOBSFINISHED information;you have to maintain a job_finished field in the JobHistory table. So that you can yield the desired output.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

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