Using multiple nested queries in Oracle DBMS

Question

I want to select the contact info (email and phonenumber) of all employees working between 2 time ranges in shops of type market and have currently more than 2 employees working together

What I want to do: I have an entity EMPLOYEE that is the owner entity of another entity SCHEDULE, and I have a "SHOP" entity with a "shopn" attribute as foreign key of EMPLOYEE

My attempt:

SELECT Email, Phonenumber  
FROM EMPLOYEE  
WHERE COUNT(  
        ID = (SELECT ID_Num  
            FROM SCHEDULE     
            WHERE Start_Time <= 12:07 AND End_Time >= 12:07)  
    AND Shopn = (SELECT Shopname  
            FROM SHOP  
            WHERE Shoptype = ‘market’)
    ) > 2

I am getting ORA-00934 error Any help would be appreciated.

Answer 1

Your query:

SELECT Email, Phonenumber
FROM EMPLOYEE
WHERE COUNT(
ID = (SELECT ID_Num
FROM SCHEDULE
WHERE Start_Time <= 12:07 AND End_Time >= 12:07)
AND Shopn = (SELECT Shopname
FROM SHOP
WHERE Shoptype = ‘market’) ) > 2

Issues :

1. It seems you are trying to select id using "=" operator which will fail in case of multiple entries returned by inner query.
2. You cannot add aggregate operators in the where clause.

Probable Answer:

select Email, Phonenumber
FROM EMPLOYEE
WHERE ID in (SELECT ID_Num
FROM SCHEDULE
WHERE Start_Time <= '12:07' AND End_Time >= '12:07')
AND Shopn in (SELECT Shopname
FROM SHOP
WHERE Shoptype = 'market')
and (  
select count(distinct id)
FROM EMPLOYEE
WHERE ID in (SELECT ID_Num
FROM SCHEDULE
WHERE Start_Time <= '12:07' AND End_Time >= '12:07')
AND Shopn in (SELECT Shopname
FROM SHOP
WHERE Shoptype = 'market')) > 2;