3. (10 points) This problem is based on Elmasri\'s company database, In each of

Question

3. (10 points) This problem is based on Elmasri's company database, In each of these SQL queries, there is a mistake. . Explain clearly and briefly what the mistake is. Please note that I am not looking for a minor SQL syntax error here, but rather you have to find a significant semantic (logic) error . Give the correct SQL for specifying the query (a) Find the names of the employees who do not work on any project SELECT FNAME, LNAME FROM EMPLOYEE) EXCEPT (SELECT FNAME, LNAME FROM EMPLOYEE, WORKS_ON WHERE EMPLOYEE. SSN = WORKS-ON, ESSN ); (b) Find the social security number of employee with the lowest salary SELECT SSN FROM EMPLOYEE WHERE SALARY

Explanation / Answer

Answer:-

3.)

(a) Here, we can take NOT IN clause instead of EXCEPT operator, because except filter the duplicates records thats why the it will give output as distinct employee who works in company, while NOT IN operator will give result all employee whether it may be repeated..

Except operator expect the same number of columns in both the queries, where as NOT IN, compares a single column from the outer query with a single column from the sub-query.

So query should be like this-

SELECT FNAME , LNAME , FROM EMPLOYEE NOT IN (SELECT FNAME, LNAME, WORKS_ON WHERE EMPLOYEE.SSN= WORKS_ON.SSN)

b.) The given query in the question is wrong because its using ANY set comparison operator. when we run the query it will give the salary of any of the employee whose less than any of the salary. that means it won't return the lowest salary. Here we should use ALL operator which will compare to each salary of the table and will return the lowest salary. The difference between ANY and ALL is

ANY- Compare value to each value in the list.

ALL- compare value to every value in the list.

so right query will be SELECT SSN FROM EMPLOYEE WHERE SALARY <= ALL (SELECT SALARY FROM EMPLOYEE);

Related Questions

Navigate

• Browse (All)
• Browse 3
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.