Databases Reference
In-Depth Information
3.
Execute the same query, as mentioned earlier, using the
UNION
ALL
operator:
SELECT
CUST_LAST_NAME AS LastName, CUST_FIRST_NAME AS FirstName
FROM sh.CUSTOMERS
WHERE CUST_CREDIT_LIMIT > 13000
UNION ALL
SELECT
LAST_NAME, FIRST_NAME
FROM sh.MY_EMPLOYEES
WHERE SALARY > 10000;
4.
Use the
INTERSECT
operator to retrieve the last names in common between
customers and employees:
SELECT CUST_LAST_NAME AS LastName FROM sh.CUSTOMERS
INTERSECT
SELECT LAST_NAME FROM sh.MY_EMPLOYEES;
5.
Write a query which returns the same results of the previous query using
a
JOIN
instead of the
INTERSECT
operator:
SELECT DISTINCT
C.CUST_LAST_NAME AS LastName
FROM sh.CUSTOMERS C
INNER JOIN sh.MY_EMPLOYEES E
ON C.CUST_LAST_NAME = E.LAST_NAME;
6.
Use the
MINUS
operator to retrieve the last name of the customers that are
not present in the
EMPLOYEES
table:
SELECT C.CUST_LAST_NAME AS LastName FROM sh.CUSTOMERS C
MINUS
SELECT E.LAST_NAME FROM sh.MY_EMPLOYEES E;
7.
Write a query that returns the same result of the previous query using an
ANTI-JOIN
instead of the
MINUS
operator:
SELECT DISTINCT
C.CUST_LAST_NAME AS LastName
FROM sh.CUSTOMERS C
WHERE C.CUST_LAST_NAME NOT IN (
SELECT E.LAST_NAME FROM sh.MY_EMPLOYEES E);
