Random

1.
Select "name", "surname", "telephone_number" from "Employee"
join "Employee_Numbers" on "ssn" = "employee_id";

2.
Select "name", "surname", "telephone_number" from "Employee"
join "Employee_Numbers" on "ssn" = "employee_id"
where "telephone_number" like '071%' OR "telephone_number" like '070%' OR "telephone_number" like '072%';

2.1
select e.*
from employee e
where e.e# IN
    (select en.n_e#
     from employee_numbers en
     where en.phone_number LIKE '070%' OR en.phone_number LIKE '071%' OR en.phone_number LIKE '072%')


3.
Select "name", "surname" from "Employee"
join "Seller" on "Seller"."ssn" = "Employee"."ssn"
where "Employee"."ssn" not in (
    Select "employee_id" as "ssn" from "Employee_Numbers" where REGEXP_LIKE ("telephone_number", '07[56789].*')
);

3.1
select e.*
from employee e
where e.e# NOT IN
    (select en.n_e#
     from employee_numbers en
     where en.phone_number LIKE '075%' OR en.phone_number LIKE '076%' OR en.phone_number LIKE '077%'
    OR en.phone_number LIKE '078%')
    AND e.e# IN
    (select seller#
    from seller)


4.
Select "Agent_Seller"."ssn" from "Agent_Seller"
join "Agent_Manager" on "Agent_Manager"."ssn" = "Agent_Seller"."ssn";

4.1.
SELECT a.a#
FROM agent a
WHERE a.a# IN
    (SELECT a_seller#
    from agent_seller)
    AND a.a# IN
    (SELECT a_manager#
    from agent_manager)


5.
Select "ssn" from "Agent_Seller"
where "Agent_Seller"."ssn" not in
(
    Select "Agent_Seller"."ssn" from "Agent_Seller"
    join "Agent_Manager" on "Agent_Manager"."ssn" = "Agent_Seller"."ssn"
)
UNION
Select "ssn" from "Agent_Manager"
where "Agent_Manager"."ssn" not in
(
    Select "Agent_Seller"."ssn" from "Agent_Seller"
    join "Agent_Manager" on "Agent_Manager"."ssn" = "Agent_Seller"."ssn"
);

5.1
SELECT e.e#
FROM employee e
WHERE e.e# IN
    (SELECT a_seller#
    from agent_seller)
    OR e.e# IN
    (SELECT a_manager#
    from agent_manager)



6.
Select "Customer"."ssn", "Customer"."name", "Customer"."surname", "Employee"."ssn" as essn, "Employee"."name" as ename, "Employee"."surname" as esurname from "Customer"
join "Employee" on "Customer"."assigned_agent_ssn" = "Employee"."ssn"
 where "Customer"."ssn" like '7%';

7.
Select "comment" from "Product_Review"
where "grade" > 2 and "comment" is not null;

8.
Select "comment" from "Store_Review"
where "grade" > 2 and "comment" is not null;

9.
Select count(*) as "count" from "Scheduled_Talks" where "duration" > 30 and "agent_seller_ssn" like '1%';

10.
Select count(*) as "count" from "Agent_Seller"
join "Agent_Manager" on "Agent_Manager"."ssn" = "Agent_Seller"."ssn";

10.1
select count(*)
from employee e
where e.e# IN
    (SELECT a_seller#
    FROM agent_seller)
    AND e.e# IN
    (SELECT a_manager#
    FROM agent_manager)

11.
Select count(*) as "count" from (
    Select "ssn" from "Agent_Seller"
    where "Agent_Seller"."ssn" not in
    (
        Select "Agent_Seller"."ssn" from "Agent_Seller"
        join "Agent_Manager" on "Agent_Manager"."ssn" = "Agent_Seller"."ssn"
    )

    UNION

    Select "ssn" from "Agent_Manager"
    where "Agent_Manager"."ssn" not in
    (
        Select "Agent_Seller"."ssn" from "Agent_Seller"
        join "Agent_Manager" on "Agent_Manager"."ssn" = "Agent_Seller"."ssn"
    )
);

11.1
select count(*)
from employee e
where e.e# IN
    (SELECT a_seller#
    FROM agent_seller)
    OR e.e# IN
    (SELECT a_manager#
    FROM agent_manager)