BP-Lab4

Zad 1
SELECT NAME, SURNAME, TELEPHONE_NUMBER
FROM EMPLOYEE, EMPLOYEE_NUMBERS
WHERE SSN=EMPLOYEE_ID

Zad 2
SELECT *
FROM EMPLOYEE, EMPLOYEE_NUMBERS
WHERE SSN=EMPLOYEE_ID AND (TELEPHONE_NUMBER LIKE '070______' OR TELEPHONE_NUMBER LIKE '071______' OR TELEPHONE_NUMBER LIKE '072______')

Zad 3
SELECT NAME,SURNAME,CITY,STREET,ADDRESS_NUMBER,EMAIL
FROM EMPLOYEE E,SELLER S
WHERE E.SSN=S.SSN
MINUS
SELECT NAME,SURNAME,CITY,STREET,ADDRESS_NUMBER,EMAIL
FROM EMPLOYEE,EMPLOYEE_NUMBERS
WHERE SSN=EMPLOYEE_ID AND (TELEPHONE_NUMBER LIKE '075%' OR TELEPHONE_NUMBER LIKE '076%' OR TELEPHONE_NUMBER LIKE '077%' OR TELEPHONE_NUMBER LIKE '078%')

Zad 4
SELECT SSN
FROM AGENT_MANAGER
INTERSECT
SELECT SSN
FROM AGENT_SELLER

Zad 5
SELECT SSN
FROM AGENT_MANAGER
UNION
SELECT SSN
FROM AGENT_SELLER

Zad 6
SELECT C.SSN,C.NAME,C.SURNAME,E.SSN AS AGENT_SSN,E.NAME AS AGENT_NAME,E.SURNAME AS AGENT_SURNAME
FROM CUSTOMER C,AGENT_SELLER A,EMPLOYEE E
WHERE ASSIGNED_AGENT_SSN=A.SSN AND A.SSN=E.SSN AND C.SSN LIKE '7________'

Zad 7
SELECT PRODUCT_COMMENT
FROM PRODUCT_REVIEW
WHERE GRADE>2

Zad 8
SELECT STORE_COMMENT
FROM STORE_REVIEW
WHERE GRADE>2

Zad 9
SELECT COUNT(*)
FROM SCHEDULED_TALKS
WHERE AGENT_SELLER_SSN LIKE '1________' AND DURATION>30

Zad 10
SELECT COUNT(*)
FROM (SELECT SSN
      FROM AGENT_MANAGER
      INTERSECT
      SELECT SSN
      FROM AGENT_SELLER)

Zad 11
SELECT COUNT(*)
FROM (SELECT SSN
      FROM AGENT_MANAGER
      UNION
      SELECT SSN
      FROM AGENT_SELLER)