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 COUNT(*)
FROM EMPLOYEE
WHERE Salary < 30000;

b) SELECT *
FROM EMPLOYEE
WHERE Name LIKE 'Ja%';

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

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

Answer 1

The invalid ANSI SQL command among the options is c) SELECT HireDate, COUNT(*) FROM EMPLOYEE WHERE Salary < 30000; because it is required to use a GROUP BY clause when using an aggregate function with other columns.

Step-by-step explanation:

The student's question revolves around identifying which command is not a valid ANSI SQL command when given a table with the structure EMPLOYEE (EmpNo, Name, Salary, HireDate). Let's examine the provided commandsSELECT COUNT(*) FROM EMPLOYEE WHERE Salary < 30000; - This is a valid SQL command that counts all employees whose salary is less than 30000.SELECT * FROM EMPLOYEE WHERE Name LIKE 'Ja%'; - This is a valid SQL command that selects all records from EMPLOYEE where the Name starts with 'Ja'.SELECT HireDate, COUNT(*) FROM EMPLOYEE WHERE Salary < 30000; - This command is not valid because it attempts to select both the HireDate and a count of rows without using a GROUP BY clause to aggregate results by HireDate.SELECT HireDate, COUNT(*) FROM EMPLOYEE GROUP BY HireDate; -

This is a valid command that groups the result set by HireDate and counts the employees for each HireDate.The correct answer is c) SELECT HireDate, COUNT(*) FROM EMPLOYEE WHERE Salary < 30000; it should use a GROUP BY clause when combining aggregate functions like COUNT(*) with other columns.Option c) SELECT HireDate, COUNT(*)FROM EMPLOYEEWHERE Salary < 30000; is not a valid ANSI SQL command.The correct ANSI SQL command should have a GROUP BY clause when using the COUNT(*) function alongside othercolumns such as HireDate. In this case, option d) SELECT HireDate, COUNT(*)FROM EMPLOYEEGROUP BY HireDate; is the correct command as it groups the result by HireDateThe primary topic of this question is SQL commands and their syntax.