-3

Tables:

employee(eno,ename)

works_on(eno,pno,ename)

project(pno, project)

select ename from employee where eno in(
select eno from works_on where pno =(select * from project))

what is the output ?

a)Employee who works on all project.

b)Employee who works one project .

c) name of employee who works on more than one project.

  • What have you concluded so far? Nothing wrong with posting homework, but you need to put some effort into the problem – Lennart Nov 19 '19 at 18:56
  • @Lennart I tried to run it on MySQL workbench created all the table with primary and foreign key reference. But that query didn't worked, the error says "Operand should contain 1 column(s)", until I changed " = " to " in " inside the first select as suggested by #DKramer – Sicario Nov 19 '19 at 19:44
1

As it stands this query:

select ename from employee where eno in(
select eno from works_on where pno =(select * from project))

Will return a 116 Error in SQL Server.

Msg 116, Level 16, State 1. Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Due to having a subquery with SELECT *, in the where clause. If you ammend this by selecting the PNO column from project.

select ename from employee where eno in(
select eno from works_on where pno = (select pno from project))

You will still (potentially) get an error depending on your actual dataset.

Msg 512, Level 16, State 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Assuming you intend to use the query as followed:

select ename from employee where eno in(
select eno from works_on where pno in (select pno from project))

You will get a list of employees that work on (a) project(s).

Not the answer you're looking for? Browse other questions tagged or ask your own question.