Which of the following queries will result in an error message? Why? (Uses tables created in the Chapter 4 Lab Activity.)

Question

1. SELECT UPPER(FirstName || ‘ ‘ || LastName) FROM student;
2. SELECT DeptId, COUNT (*) FROM employee;
3. INSERT INTO DEPT VALUES (90, RESEARCH, NULL, NULL);
4. SELECT DeptId, SUM (Salary)
FROM employee
WHERE SUM (Salary) > 200000
GROUP BY DeptId;
5. SELECT SUM(EmployeeId)
FROM employee;

Answer 1

1.
SQL> SELECT UPPER(FirstName || ' ' || LastName) FROM student;
SELECT UPPER(FirstName || ' ' || LastName) FROM student
*
ERROR at line 1:
ORA-00904: "LASTNAME": invalid identifier
(Invalid column names)
2.
SQL> SELECT DeptId, COUNT (*) FROM employee;
SELECT DeptId, COUNT (*) FROM employee
*
ERROR at line 1:
ORA-00937: not a single-group group function
(Missing GROUP BY DeptId)
3.
SQL> INSERT INTO DEPT VALUES (90, RESEARCH, NULL, NULL);
INSERT INTO DEPT VALUES (90, RESEARCH, NULL, NULL)
*
ERROR at line 1:
ORA-00984: column not allowed here
(RESEARCH should be enclosed within single quotes)
4.
SQL> SELECT DeptId, SUM (Salary)
2 FROM employee
3 WHERE SUM (Salary) > 200000
4 GROUP BY DeptId;
WHERE SUM (Salary) > 200000
*
ERROR at line 3:
ORA-00934: group function is not allowed here
(WHERE should be replaced by HAVING clause)
5.
SQL> SELECT SUM(EmployeeId)
2 FROM employee;
SUM(EMPLOYEEID)
---------------
2013
(No error, but sum of EmployeeId does not make sense)