I need to create a View in SQL by counting the number of tasks an employee has been given but only show an employee who has more than 3 tasks. The problem I got is showing the count in the view. The error I'm getting is "missing right parenthesis"
CREATE VIEW EMPLOYEE_TASKS (E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME,
T.TASK_ID)
AS SELECT COUNT T.TASK_ID
FROM EMPLOYEE E, TASK T
WHERE E.EMPLOYEE_ID = T.TASK_ID
AND COUNT(T.TASK_ID) > 3;
Your code just makes no sense. I suspect you want:
CREATE VIEW EMPLOYEE_TASKS AS
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, COUNT(*) as num_tasks
FROM EMPLOYEE E JOIN
TASK T
ON E.EMPLOYEE_ID = T.TASK_ID
GROUP BY E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME
HAVING COUNT(*) > 3;
When creating a view you can optionally provide a parenthetical list of column names just after the view name, however, that list should not be in alias.column_name format, you just provided the column names. Furthermore, if you do provide the optional list of column names, the projection (select list) of the query which defines the view should have the same number of column in its projection as in the column alias list.
Currently you have the following problems with your view definition:
E. and T.) in the column alias listCOUNT T.TASK_ID is not a valid column expression, count(t.task_id) is.COUNT(T.TASK_ID) in the WHERE clause, however, that's an aggregate function which isn't allowed in the WHEREclause."count" is a function. it needs arguments in parenthesis :
...
AS SELECT COUNT(T.TASK_ID)
...
