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