Question

Given a table with the structure: EMPLOYEE (EmpNo, Name, Salary, HireDate), which of the following is not a valid ANSI SQL command?

a) SELECT *
FROM EMPLOYEE
WHERE Name LIKE 'Ja%';
b) SELECT COUNT()
FROM EMPLOYEE
WHERE Salary < 30000;
c) SELECT COUNT(EmpNo)
FROM EMPLOYEE;
d) SELECT HireDate, COUNT()
FROM EMPLOYEE
WHERE Salary < 30000;
e) SELECT HireDate, COUNT(*)
FROM EMPLOYEE
GROUP BY HireDate;

Answer 1

Correct option is Option d) SELECT HireDate, COUNT() FROM EMPLOYEE WHERE Salary < 30000; is not a valid ANSI SQL command because the COUNT() function lacks an argument and there is no GROUP BY clause for the non-aggregated column HireDate.

Step-by-step explanation:

The student has posed a question regarding the validity of various ANSI SQL commands given a table with the structure: EMPLOYEE (EmpNo, Name, Salary, HireDate). After reviewing the options, the one that is not a valid ANSI SQL command is:

d) SELECT HireDate, COUNT()FROM EMPLOYEE
WHERE Salary < 30000;

This command is not valid because the COUNT() function is missing an argument, such as a column name or an asterisk (*). Additionally, when using aggregate functions like COUNT(), if there are other columns selected that are not part of an aggregate function, they must be included in a GROUP BY clause. The correct form of the command, assuming we want to count all employees with a Salary less than 30000 grouped by their HireDate, would be:

e) SELECT HireDate, COUNT(*)
FROM EMPLOYEE
GROUP BY HireDate;